Excel Level II: Intermediate

Worksheet Management

Navigation

• Keyboard shortcuts that facilitate quick and easy navigation within cells

Formula Review

• Review various methods for completing calculations

Working with Text

Splitting Text

• Use Text to Columns to split text into multiple cells

Joining Text

• Using Concat and the & (ampersand) to combine cells

Cell Ranges

Paste Special

• Apply formats and perform calculations on selected cells

Paste Special Values

• Hardcode the answer to a formula or function

Named Ranges

• Assign a name to a range of cells to make it easier to reference those ranges in calculations

Database Functions

VLOOKUP & XLOOKUP

• Use VLOOKUP and XLOOKUP to find information in cell range and return information from another cell range

Sort & Filter

• Use Sort & Filter to find and organize data in large databases

Pivot Tables

Pivot Tables

• Create Pivot Tables to quickly summarize large databases

Pivot Tables & Grouping

• Group within Pivot Tables

Multiple Pivot Tables

• Create multiple Pivot Tables on a single worksheet

Logical Functions

IF statements

• Use IF statements to return output based on the contents of another cell

AND, OR

• Tests to see whether multiple conditions are true

Math Functions

SUBTOTAL

• Use SUBTOTAL function to sum/average/count values based on what is not filtered

Statistical Functions

SUMIFS

• Use SUMIFS function to sum cells based on one or more conditions

COUNTIFS

• Use COUNTIFS function to count cells based on one or more conditions

Improve Data Quality

Data Validation

• Restrict the type of data that can be allowed in a cell

Remove Duplicates

• Eliminate duplicate row data

End of Class Project

Project

• End of class project to review key concepts from the class