Quick Reference clips for Word, Excel, Outlook and PowerPoint.
Welcome Guest (.|.)

 


 

Microsoft Excel 2016 Advanced Course

Click a link below to get started

(click to go back)

 

Lesson 1: Formulas - Text

 

 

  1. Join 2 columns - no space
  2. LEFT formula - extract data from the left
  3. RIGHT - formula - extract data from the right
  4. MID - formula - extract data from the middle
  5. LEN - formula
  6. FIND formula - find specific data
  7. SUBSTITUTE
  8. TRIM - remove spaces
  9. Text to columns - last first
  10. Text to columns - first last
  11. Join 2 columns - with a space
  12. Text to columns - / slash
  13. Text to columns - @
  14. LOWER case
  15. UPPER case
  16. PROPER case
  17. EXACT - compare for exact
  18. CLEAN or TRIM
  19. Join 2 columns with a space
  20. Join 2 columns without a space

 

 

Lesson 2: Formulas - Lookup and Reference

 

 

  1. Vertical - VLookup - false
  2. Vertical - VLookup - false 2nd example
  3. Vertical - VLookup - false 3rd example
  4. Vertical - VLookup - false 4th example
  5. Vertical - VLookup - true
  6. Horizontal - HLookup - false
  7. Horizontal - HLookup - false 2nd example
  8. Horizontal - HLookup - false 4th example
  9. Horizontal - HLookup - true
  10. MATCH - look up a value; return the answer
  11. INDEX - look up a value; return the answer
  12. LOOKUP - look up a value; return the answer

 

 

Lesson 3: Formulas - Min / Max/ Round/ Error Msgs

 

 

  1. Maximum - find it
  2. Minimum - find it
  3. Average
  4. AVERAGEIF
  5. AVERAGE(IFS)
  6. ROUND
  7. ROUNDUP
  8. ROUND problem
  9. ROUND down
  10. ROUND problem #2
  11. ROUND stop rounding
  12. Error Message - Division
  13. Error Message - #### signs
  14. Error Message - name error message
  15. Error Message - reference message
  16. Error Message - value error
  17. Error Message - array formula

 

 

Lesson 4: Sort Your Data

 

 

  1. Sort A to Z
  2. Sort A to Z (#2)
  3. Sort A to Z (#3)
  4. Sort Z to A
  5. Sort Z to A (#2)
  6. Sort A to A (#3)
  7. Sort low to high
  8. Sort low to high (#2)
  9. Sort low to high (#3)
  10. Sort large to small
  11. Sort large to small (#2)
  12. Sort large to small (#3)
  13. Sort by cell color
  14. Sort by font color
  15. Sort multiple columns

 

 

Lesson 5: Filter Your Data

 

 

  1. Filter one column
  2. Filtering turn off
  3. Filter two columns
  4. Filter using a cell value
  5. Filter multiple cell values
  6. Filter multiple columns
  7. Filter 2 items
  8. Filters using subtotal formula
  9. Filter using average forumula
  10. Filter largest figures
  11. Filter smallest figures

 

 

Lesson 6: Charts

 

 

  1. Chart - create a chart
  2. Chart - change the type
  3. Chart - add labels
  4. Chart - add callouts
  5. Chart - change colors
  6. Chart - add a title
  7. Chart - add a legend
  8. Chart - rename chart sheet
  9. Chart - change default type
  10. Chart - show data on chart
  11. Chart - switch data
  12. Chart - quick layout
  13. Chart - contextual tab
  14. Chart - on same sheet as data
  15. Chart - change background colors
  16. Chart - change chart funnel
  17. Gantt Chart

 

 

Lesson 7: Formulas - Financial_

 

 

  1. PMT - find pmt of a loan
  2. PPMT - find principal of a loan
  3. IPMT - find interest of a loan
  4. Balance - find balance of loan
  5. RATE - find rate of interest
  6. PV - Present value
  7. NPER - find total number of payments
  8. Investment - find future value
  9. Annuity - present value
  10. Total Paid on Loan
  11. Compound Interest - how to find
  12. Loan Payments - different durations
  13. NPV - net present value
  14. IRR - Internal rate of return
  15. FV - future value
  16. FV - future value (2nd example)
  17. Loan Amortization Table - create it
  18. Percentage - Total Grade (school)
  19. Percentage - Price with percent discount
  20. Percentage - earnings difference in percent

 

 

Lesson 8: Headers / Footers

 

 

  1. Header - insert header
  2. Header - delete header
  3. Headers - use the Microsoft created
  4. File name and path in header
  5. Insert filename in header
  6. Insert picture in your header
  7. Insert sheet name in header
  8. Insert current time in header
  9. Insert date in your header
  10. Insert page number in your header
  11. Insert page x of y in header
  12. Insert file name and path into footer
  13. Insert page x of y in footer
  14. Insert customized footer
  15. Insert Microsoft created footers
  16. Different header footer first page

 

 

Lesson 9: Inserting Comments

 

 

  1. Comments - insert a
  2. Comments - red dots
  3. Comments - show comments
  4. Comments - show / don't show
  5. Comments - show all the time
  6. Comments - print them
  7. Comments - print them #2
  8. Comments - delete
  9. Comments - delete #2
  10. Comments - find them
  11. Comments - show all comments
  12. Comments - change / edit
  13. Comments - background colors
  14. Comments - print don't
  15. Comments - inspect for comments

 

Lesson 10: Excel 2016 New Features

 

 

  1. Opening screen
  2. Blank document
  3. The Ribbon
  4. Quick access toolbar
  5. Protect workbook
  6. Check for issues
  7. Microsoft Templates
  8. New ways to open workbooks
  9. New ways to save / save as
  10. New way to print
  11. New way to share / email
  12. Account and Excel options
  13. Screen clipping and shot
  14. Slicers
  15. Tables
  16. Spark lines
  17. Smart Art

 

Lesson 11: Excel 2016 Tips and Tricks Vol. 1

 

 

  1. Copy a formula on a worksheet to multiple columns
  2. Microsoft Excel golden rule - type numbers first a
  3. Navigational arrow - right click the arrows to sho
  4. Alt+= to add
  5. Copy a sheet - use the CTRL key and right drag to
  6. Trim - remove extra spaces from the left side of t
  7. Multiple lines of text in one cell
  8. F11 - to create a chart on the same worksheet
  9. Put chart on same page - ALT+F1 to create chart on
  10. Add chart data quickly - use simple copy and paste
  11. Format painter - to copy formatting from one place
  12. Move a chart
  13. Change default chart type
  14. Quick Access Toolbar trick
  15. Hide the ribbon
  16. Create a custom list
  17. Make it larger with the wheel
  18. Zoom in
  19. Switch between windows
  20. Use the Watch Window
  21. View side by side
  22. Trace precendent
  23. Now formula
  24. Today formula
  25. Transpose the data

 

Lesson 12: Excel 2016 Tips and Tricks Vol. 2

 

 

  1. Show the formulas
  2. Repeat months
  3. Repeat days
  4. Repeat years
  5. Excel templates by Microsoft
  6. Find duplicates
  7. Find duplicates in multiple columns
  8. Sort duplicates by color
  9. Find unique values
  10. Remove duplicates
  11. Upper case formula
  12. Lower case formula
  13. Proper case formula
  14. Text to columns - put text from one column into tw
  15. Text to columns with a /
  16. Join two columns - concatenate - with no space
  17. Join two columns - concatenate - with space
  18. Join 3 columns - concatenate - and change the orde
  19. Paste as a value
  20. Create a data form
  21. Create a drop down list
  22. Find the top 3
  23. Phone number auto format
  24. Zip code plus 4 auto format
  25. Soc. Security # auto format