Pivot Tables to the Rescue!

# Pivot Tables to the Rescue!

## Pivot Tables to the Rescue!

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
##### Presentation Transcript

1. Pivot Tables to the Rescue! Excel Tips & Tricks for FAA’s ILASFAA Fall Workshops: Saving the Day with Financial Aid November 2017

3. Filters are your Friends • Good for data with Column headers • Quick Counts • Easy Sorts • Be wary of over filtering and mixing up your data

4. Freeze Panes & Duplicate Values • Use Freeze Panes to Review Data easily • Identify and Remove Duplicate Values • Conditional Formatting for easy identification

5. Formulas are only logical • Use formulas to not only do simple math, but identify issues with your data • “IF” logic can be very useful in comparing data • Copy and paste as value to make it usable for other tools such as Pivot Tables • For fixed Data Points, click on value in formula and hit “F4”

6. The Power of the VLOOKUP =VLOOKUP(lookup value, Table Array, Column Index Number, Range Lookup) Handy when comparing data The sheet where you have the data that is being “looked up” will need to have the identifier (usually your student ID) in the first column (Column A) Use ISNA formula to omit any NA’s Copy and paste as values once done to make useable for other formulas and tools

7. Pivot Tables are where the MAGIC Happens Select sheet containing data Under Insert in the Menu Ribbon, Click on ‘Insert Pivot Tables’ Make sure that New Worksheet is selected in the pop up and click ‘OK’ Select Pivot Table Fields you want to report on

8. Garbage in, Garbage out • Your data is only as good as how well you maintain it • When working with a large amount of data and complex formulas, block off time on your calendar to concentrate • Watch out for sorting/filtering issues, and check the math!

9. When in doubt… GOOGLE IT! If you think there is an easier way to do it, chances are, there is! Google is one of the best resources for finding formulas and methods to make your life easier.

10. Questions??? Michelle Ortiz Wortel Assistant Director Chicago Office of Financial Aid Northwestern University michelle.wortel@northwestern.edu