Intermediate Microsoft Excel

Worksheet Management

Navigation

• Keyboard shortcuts that facilitate quick and easy navigation around the worksheet

Paste Special

• Use Paste Special to apply formats, as well as perform calculations on selected cells

Working with Text

Splitting Text

• Use Text to Columns to split text into multiple cells

Joining Text

• Join text from separate cells

Named Ranges

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

Data Validation

• Create a drop-down menu to make data entry quicker and more efficient

Sort & Filter

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

Remove Duplicates

• Use Remove Duplicates to eliminate duplicate data

Database Functions

VLOOKUP

• Use Vlookup to find information in one column and return information from another column

HLOOKUP

• Use Hlookup to find information in one row and return information from a second row

VLOOKUP - Closet Match

• If an "Exact Match" cannot be found, Vlookup can still be used to find the closest match

Logical Functions

AND, OR

• Tests to see whether multiple conditions are true

IF statements

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

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

Statistical Functions

Ranking

• Use Rank.eq and Rank.avg functions to determine a cell's position within a larger array

COUNTIFS

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

SUMIFS

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

Advanced Charts

Combo Charts

• Combine two or more charts into a single chart, with the option of adding a secondary axis

End of Class Project

Project

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