Excel Formulas and Functions Basics

Learning Objectives

• Participants will learn to create well-structured formulas using relative and absolute cell references and named ranges
• Participants will learn to understand and use functions in Excel formulas

Spreadsheet

• A program that displays data (text and numbers) in a table called a worksheet

Uses of Spreadsheet

• Prepare budgets
• Create financial statements
• Manage inventory
• Create charts

Worksheet

• A grid with columns and rows
• Often used interchangeably with the term spreadsheet

Cell

• The intersection of a row and column

Cell Contents

• Labels: Words or letters
• Values: Numbers
• Formulas: Statements that perform a calculation

Cell Range

• A group of cells that are closely together

Columns

• Vertical arrangement of cells
• Identified by letters

Rows

• Horizontal arrangement of cells
• Identified by numbers

Worksheet Tab

• Identifies each open worksheet in a spreadsheet program
• Located in the lower left corner of the screen

Formulas

• The foundation of Excel
• Used to calculate cells, numbers, and other data in a workbook

Precedence of Operations

• Order of operations in which Excel performs calculations
• Excel follows the standard order of operations: Parentheses, Exponents, Multiplication and Division (from left to right), Addition and Subtraction (from left to right)

Cell Reference

• Refers to a specific cell in a worksheet
• Uses the column letter and row number (e.g., A1, B2)

Functions

• Built-in algorithms used in formulas
• Usually appear as prefixes in formulas
• Perform a variety of calculations

Foundation Functions & Formulas

• Functions are pre-built algorithms embedded in Excel that automatically perform calculations and data manipulation
• Formulas utilize these functions to define calculations and actions
• Formulas provide instructions for Excel to perform calculations, manipulations, and data retrieval

Function Argument

• Function arguments are the inputs or values that a function takes to perform its operation
• Arguments can be cell references, numbers, text, or other functions

Basic Function Argument Example (Product)

• Demonstrates how to use the PRODUCT function to multiply numbers from different cells
• Uses the PRODUCT function to multiply the values in cells B3, B5, and B7.

Advanced Formulas and Functions

• SUMIF Function: Adds cells that meet specific criteria.
  • Example: =SUMIF(A2:A5,">160000",B2:B5) adds values in Column B where Column A values are greater than $160,000.
• SUMIFS Function: Allows multiple criteria for summing.
  • Example: =SUMIFS(G4:G13,B4:B13,"Bumpers",C4:C13,"Continental") sums values in Column G where Column B values are "Bumpers" and Column C values are "Continental."
• SUMIF Formula: SUMIF(range, criteria, [sum_range])
• SUMIFS Formula: SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
• Arrays: Perform functions on a set of values.
  • Indicated by curly braces {}.
  • Example: {=SUM(E4:E13*G4:G13)} sums the product of values in ranges E4:E13 and G4:G13.
• CONCATENATE Function: Joins up to 30 text items.
  • Example: =CONCATENATE(text1, text2, [text3],...) combines text1, text2, and optional subsequent text values into a single string.

Date Formulas & Functions

• TODAY Function: Returns the current date.
  • =TODAY()
• NOW Function: Returns the current date and time.
  • =NOW()
• Calculate Future Date: Add days to the current date.
  • =TODAY()+7 returns the date seven days from today.

Error Handling & Debugging Formulas

• Use Evaluate Formula (Alt + TUF) to step through a formula and see each calculation.

VLOOKUP Function

• VLOOKUP (Vertical Lookup): Searches the first column of a table for a value and returns a value from the same row in another column.
• Key Elements:
  • Lookup_value: The value to search for.
  • Table_array: Range containing the lookup table, converted to absolute values (e.g., AAA3:CCC7).
  • Col_index_num: Relative column number of the target value in the table array.
  • Range_lookup: TRUE (approximate match) or FALSE (exact match).

HLOOKUP Function

• HLOOKUP (Horizontal Lookup): Similar to VLOOKUP, but searches across the first row of a range.
• Example: =HLOOKUP(B2,F1:K3,3) searches for the value in cell B2 in the first row of the range F1:K3 and returns a value from the third row.

This quiz covers the fundamentals of creating Excel formulas, including the use of relative and absolute cell references and named ranges. Participants will also learn about functions and how they are used within spreadsheets to perform calculations. Test your knowledge and improve your spreadsheet skills!