1 / 10

Pivot Tables to the Rescue!

Learn how to use pivot tables in Excel to easily analyze and summarize your financial aid data. This interactive session will guide you through filtering, freezing panes, using formulas, VLOOKUP, and the power of pivot tables. Get ready to save time and improve your data analysis skills!

blair
Télécharger la présentation

Pivot Tables to the Rescue!

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

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

  2. Training materials This session is interactive and will be guided by your participation. Access to internet, laptop, and excel is advised for this presentation. Please download files ahead of time online from:2017 ILASFAA FALL WORKSHOP Google Drive folder https://drive.google.com/drive/folders/0B-d3oWAvb9rsV3VjOGtoRENwVWs?usp=sharing

  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

More Related