1 / 30

Essential Excel Tools, Tips & Tricks

Essential Excel Tools, Tips & Tricks. Nicole Soer Loras College. Why. Example 1 (demographic). Example 2 (financial). Quick Bar. Find a quick average, count or sum of selected cells. Also find maximum and minimum. Printing Column Headings on Each Page.

chuong
Download Presentation

Essential Excel Tools, Tips & Tricks

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. Essential Excel Tools, Tips & Tricks Nicole Soer Loras College

  2. Why

  3. Example 1 (demographic)

  4. Example 2 (financial)

  5. Quick Bar Find a quick average, count or sum of selected cells. Also find maximum and minimum.

  6. Printing Column Headings on Each Page PageLayout tab > Print Titles button

  7. Tables Tables provide a quick way to look at certain attributes like 90 or more credits…

  8. Tables Students registered as graduate students…

  9. Tables Students who are NOT graduate students but ARE registered for less than 12 credits…

  10. Pivot Tables • Tool with reporting functionality that helps summarize and analyze data

  11. Pivot Tables Tool that helps dynamically summarize and analyze large amounts of data

  12. Pivot Tables Pivot tables are a powerful tool that gives important information about our data like…

  13. Pivot Tables Cohort counts and outliers

  14. Pivot Tables Breakdown cohort by state

  15. Pivot Tables Detail in on 09/FA cohort who have SR status and are from MN

  16. Pivot Tables Determine sum and total average of student loan amounts and count of students

  17. Duplicate Entries Excel can color code duplicate entries with Conditional Formatting.

  18. Custom Sorts Home tab > Sort & Filter > Custom Sort Sort by cell color

  19. Custom Sorts Sort by multiple fields

  20. Removing Duplicates Take care with this! Data tab > Remove Duplicates

  21. Function Basics To use functions, you must have = sign, the function (SUM in this case)and the arguments (F2:L2)

  22. Function Basics To enter a function, type it in the cell…

  23. Function Basics or click the fx button to use the wizard

  24. Filling in Functions Click and drag OR double click the box in the corner of the cell.

  25. Absolute vs. Relative Cell References Absolute: the cell address stays same when filling Relative: the cell address changes when filling

  26. VLOOKUP Looks up a set of data from another spreadsheet • Grade book • 90 – 100 = A • 80-89 = B • Financial Aid Calculator • GPA 3.5 – 4.0 = 20,000 • GPA 3.3 – 3.49 = 15,000 • ID look up • Combine information from demographic sheet to financial sheet

  27. VLOOKUP

  28. VLOOKUP

  29. IF • Usually compares 2 things • If something is true, do a calculation or show text • If it’s not, do this calculation or show text

  30. CONCATENATE Combine cells, text or numbers

More Related