Excel Formula Basics

Formulas and Functions

Formula Basics

• A formula in Excel starts with an equals sign (=)
• Formula can be used to perform arithmetic, comparison, and logical operations
• Formula can reference cells, ranges, or other formulas

Arithmetic Operators

• + (addition)
• - (subtraction)
• * (multiplication)
• / (division)
• % (percentage)
• ^ (exponentiation)

Functions

• SUM(range): returns the sum of a range of cells
• AVERAGE(range): returns the average of a range of cells
• COUNT(range): returns the count of cells in a range
• IF(logical_test, [value_if_true], [value_if_false]): tests a condition and returns a value based on the result
• VLOOKUP(lookup_value, table_array, col_index, [range_lookup]): looks up a value in a table and returns a value from a specified column

Formula Errors

• #N/A (value not available)
• #NAME? (invalid name or formula)
• #NUM! (invalid numerical value)
• #REF! (invalid reference)
• #VALUE! (invalid value)

Shortcuts and Productivity

Navigation

• Ctrl + Home: goes to the beginning of the worksheet
• Ctrl + End: goes to the end of the worksheet
• Ctrl + Page Up and Ctrl + Page Down: navigates between worksheets

Selection

• Ctrl + A: selects all cells in the worksheet
• Ctrl + Click: selects multiple cells or ranges
• Shift + Click: selects a range of cells

Editing

• Ctrl + C: copies the selected cells
• Ctrl + X: cuts the selected cells
• Ctrl + V: pastes the copied or cut cells
• Ctrl + Z: undoes the last action
• Ctrl + Y: redoes the last action

Formatting

• Ctrl + 1: formats the selected cells as a number
• Ctrl + Shift + ~: formats the selected cells as a general format
• Ctrl + Shift + %: formats the selected cells as a percentage
• Ctrl + Shift + $: formats the selected cells as currency

Formula Entry

• =: starts a formula
• F2: edits the active cell and puts the cursor at the end of the formula
• Alt + =: automatically enters the SUM formula for a selected range

Formulas and Functions

Formula Basics

• Formulas in Excel start with an equals sign (=) and can be used to perform arithmetic, comparison, and logical operations
• Formulas can reference cells, ranges, or other formulas

Arithmetic Operators

• The + operator is used for addition
• The - operator is used for subtraction
• The * operator is used for multiplication
• The / operator is used for division
• The % operator is used to find percentages
• The ^ operator is used for exponentiation

Functions

• The SUM(range) function returns the sum of a range of cells
• The AVERAGE(range) function returns the average of a range of cells
• The COUNT(range) function returns the count of cells in a range
• The IF(logical_test, [value_if_true], [value_if_false]) function tests a condition and returns a value based on the result
• The VLOOKUP(lookup_value, table_array, col_index, [range_lookup]) function looks up a value in a table and returns a value from a specified column

Formula Errors

• The #N/A error occurs when a value is not available
• The #NAME? error occurs when a name or formula is invalid
• The #NUM! error occurs when a numerical value is invalid
• The #REF! error occurs when a reference is invalid
• The #VALUE! error occurs when a value is invalid

Shortcuts and Productivity

Navigation

• Pressing Ctrl + Home takes you to the beginning of the worksheet
• Pressing Ctrl + End takes you to the end of the worksheet
• Pressing Ctrl + Page Up and Ctrl + Page Down navigates between worksheets

Selection

• Pressing Ctrl + A selects all cells in the worksheet
• Pressing Ctrl + Click selects multiple cells or ranges
• Pressing Shift + Click selects a range of cells

Editing

• Pressing Ctrl + C copies the selected cells
• Pressing Ctrl + X cuts the selected cells
• Pressing Ctrl + V pastes the copied or cut cells
• Pressing Ctrl + Z undoes the last action
• Pressing Ctrl + Y redoes the last action

Formatting

• Pressing Ctrl + 1 formats the selected cells as a number
• Pressing Ctrl + Shift + ~ formats the selected cells as a general format
• Pressing Ctrl + Shift + % formats the selected cells as a percentage
• Pressing Ctrl + Shift + $ formats the selected cells as currency

Formula Entry

• Pressing = starts a formula
• Pressing F2 edits the active cell and puts the cursor at the end of the formula
• Pressing Alt + = automatically enters the SUM formula for a selected range

Navigation Shortcuts

• Use Ctrl + Page Up/Page Down to move to previous/next worksheet
• Press Ctrl + Home to move to cell A1
• Press Ctrl + End to move to last cell with data

Cell Navigation

• Use Ctrl + Arrow Keys to move to edge of data region
• Use Ctrl + Shift + Arrow Keys to select to edge of data region
• Press Ctrl + Backspace to move to previous active cell

Workbook Navigation

• Use Ctrl + Tab to move to next workbook
• Use Ctrl + Shift + Tab to move to previous workbook

Formatting Shortcuts

Font and Alignment

• Press Ctrl + Shift + F to open font dialog box
• Use Ctrl + Shift + > or < to increase/decrease font size