Excel | Step By STEM

top of page

Excel

Excel Info.png

Formulas

Operators: Used to perform operations.

Absolute References.png

Absolute Reference: The row and column references are locked by the dollar symbol.

Referencing Cells in Other Worksheets

-Same Workbook

=SheetName!CellAddress

-Different Workbook

=[WorkbookName]SheetName!CellAddress

LET Function.png

LET Function 2.png

LET: Used as a container for calculations.

Lookup Formulas

Function VLOOKUP.png

Function VLOOKUP 2.png

Function VLOOKUP 3.png

Function VLOOKUP 4.png

VLOOKUP: Vertical Lookup. (Row Searched, Table Searched, Column Searched, True (Approximate) False (Exact))

Function HLOOKUP.png

Function HLOOKUP 2.png

Function HLOOKUP 3.png

Function HLOOKUP 4.png

Function HLOOKUP 5.png

HLOOKUP: Horizontal Lookup. (Column Searched, Table Searched, Row Searched, True (Approximate) False (Exact))

Match Function 1.png

Match Function 2.png

Match Function 3.png

Match Function 4.png

MATCH : Gives the relative position of an item in a range. (Item Searched, Row or Column Searched, 1 (Less Than) 0 (Exact) -1 (Greater Than).

Index Match 1.png

Index Match 2.png

Index Match 3.png

Index Match 4.png

Index Match 5.png

Index Match 6.png

INDEX: Returns a value within a table or range. (Table Searched, MATCH, Column)

Pivot Table

Pivot Table: A dynamic summary report produced from a database. A Pivot Table can transform data into a meaningful presentation. When the source data changes, the Pivot Table must be refreshed. The data used for a Pivot Table must be in a rectangular table.

Summarizing (Database): The process of arranging the data in an order that answers questions about it.

Pivot 1.png

Table Fields:

(1) Data: Contains a value

(2) Category: Describes the data

Normalized: Each row contains information that describes the data in that row and doesn’t describe data in any other row. A normalized database is appropriate for a Pivot Table.

Pivot 2.png

Recommended Pivot Tables can be generated from the data table.

Pivot 3.png

Pivot Tables can be created manually.

Value Field Setting.png

Value Field Setting 2.png

You can edit the values shown using the Value Field Setting.

Pivot Chart.png

You can create charts from PivotTable data.

Errors

Visual Basic for Applications

Developer Settings.png

Developer Settings Excel.png

To use Visual Basic for Applications, turn on the Developer tab in the ribbon settings.

Record Macro.png

Record Macro 2.png

Record Macro 3.png

You can record actions and turn them into a shortcut using the record macro button.

Relative References.png

Relative Reference: Does not link the action to specific cells.

Conditional Formulas

IF Function.png

IF Function 2.png

AND Function.png

AND Function 2.png

AND Function 3.png

AND Function 4.png

OR Function.png

OR Function 2.png

Conditional Analysis: A formula that returns TRUE or FALSE.

LEFT, RIGHT, MID Functions.png

LEFT, RIGHT, MID Functions 2.png

The LEFT, RIGHT, and MID Functions can check whether certain characters are present.

SUMIF Function.png

SUMIF Function 2.png

AVERAGEIF 2.png

SUMIF (A).png

SUM IF (A2).png

The SUMIF, COUNTIF, AVERAGEIF formulas can be used to add criteria to certain calculations.

bottom of page