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

Microsoft Excel 2016 Intermediate Course

Click a link below to get started

Lesson 1: Charts / Macros

 

 

  1. Chart create
  2. Chart create #2
  3. Chart move a
  4. Chart create #3
  5. Chart larger or smaller
  6. Chart add labels call outs
  7. Chart change the look
  8. Chart change type
  9. Chart change background
  10. Chart change text colors
  11. Chart - chart elements
  12. Chart chart styles
  13. Chart more chart styles
  14. Chart layouts
  15. Trend lines - add to a chart
  16. Create a macro
  17. Macro - permission to run
  18. Macro - use it or "run a macro"
  19. Macro - assign a button
  20. Macro - delete a macro

Lesson 2: Audit / Data Analysis / Headers/Footer

 

 

  1. Header insert a header in your sheet
  2. Header change it
  3. Footer insert - insert a header in your sheet
  4. Footer Page x of y - insert into your sheet
  5. Footer file name / path
  6. Normal view from Header view
  7. Header / footer delete it
  8. Trace precedent
  9. Trace dependent
  10. Formulas - display on screen
  11. Formula - check for errors
  12. Watch window
  13. Watch window show / hide
  14. Data validation error check
  15. Data validation remove
  16. Duplicate entries find
  17. Separate first / last names
  18. Separate dates in a column
  19. Duplicates remove

 

 

 

Lesson 3: Formatting / Data Validation

 

 

  1. Conditional formats text
  2. Conditional formats numbers
  3. Conditional formats modify
  4. Conditional formats - delete
  5. Conditional formats - top 5
  6. Conditional formats - remove
  7. Conditional formats databars
  8. Conditional formats - icons
  9. Data Validation - explained
  10. Data validation - stop message
  11. Data validation - warning message
  12. Data validation - information (notice)
  13. Data validation - limit dollar amounts
  14. Data validation - limit # of text characters
  15. Data validation - limit the date
  16. Data validation - remove
  17. Data validation - remove #2
  18. Data validation - drop down list
  19. Create a Custom list
  20. Drop down list remove it
  21. Custom list create #2
  22. Custom list edit it
  23. Custom list remove

 

 

 

Lesson 4: Comments / Watermarks / Templates / Hyperlinks

 

 

  1. Comments insert
  2. Comment show one
  3. Comments show all
  4. Ink - show it
  5. Comment find next comment
  6. Comment delete a
  7. Hyperlink - to a webpage
  8. Hyperlink to a file
  9. Hyperlink to a named range
  10. Hyperlink to an email address
  11. Gridlines show / hide
  12. Gridlines - print
  13. Column headings show / hide
  14. Gridlines - print / no print #2
  15. Watermark - insert
  16. Watermark - remove
  17. Template #1 by Microsoft
  18. Template #2 by Microsoft
  19. Save a template
  20. Open a saved template
  21. Microsoft template (more)
  22. Excel - change personal information
  23. Comment - change / modify
  24. Hyperlink - change / modify
  25. Print - don't print headings row or column
  26. Hyperlink - delete

 

 

 

Lesson 5: Formulas - Count, Sum and Average

 

 

  1. COUNT - counts cells with numbers
  2. COUNTIF - one condition
  3. COUNTIFS - two conditions
  4. SUM - add numbers
  5. SUMIF - one condition
  6. SUMIFS - two conditions
  7. COUNT - second example
  8. SUM - second example
  9. Sum - find subtotal
  10. COUNTBLANK - count empty cells
  11. COUNTA - count non-empty cells
  12. COUNT - counts cells with numbers
  13. AVERAGE - find the average answer of your data
  14. AVERAGEIF - one condition
  15. AVERAGEIFS - find the average of two criteria
  16. SUMIF - sum your data using only one criteria
  17. SUMIFS - sum your data using multiple criteria
  18. COUNTIF - count data using once criteria
  19. COUNTIFS - count data using multiple criteria

 

 

 

Lesson 6: More Excel Fun #2

 

 

  1. ctrl+; will input todays date
  2. NOW formula and TODAY formula
  3. Trace Precedents
  4. Transpose
  5. Arrange multiple workbooks
  6. Watch Window
  7. Switch between workbooks
  8. Zoom to selection
  9. Mouse Wheel to make your data larger on screen
  10. Data Validation - Create a custom lists
  11. Ribbon - show it or hide it
  12. Add Icons to the Quick Access Toolbar
  13. Data Validation - Drop Down List
  14. Golden Rule
  15. Format Painter - to copy formatting
  16. Shortcut Keys
  17. Go To Shortcuts
  18. Navigate Arrow Shortcuts
  19. Separate text into multiple columns
  20. Max formula (largest number)
  21. Min formula - to find the smallest number
  22. Average - formula to find average
  23. Solver - forecast with constraints

 

 

 

Lesson 7: Sort / Filter / Find Duplicates

 

 

  1. Sort data (date not in a table)
  2. Filter data (data not in a table)
  3. Months - Repeat the months without typing
  4. Days - repeat the days without typing
  5. Years - repeat the years without typing
  6. Days Sequential
  7. Numbers - auto type numbers in a sequence
  8. Budget - create one
  9. Time Sheet - create one
  10. Find Duplicates - same column
  11. Find Duplicates - multiple columns
  12. Find Duplicates - different sheets
  13. Find Duplicates - different workbooks
  14. Find Unique Values
  15. Remove Duplicates
  16. Separate text into multiple columns
  17. Paste as a value
  18. alt+= ...use this to quickly add numbers
  19. Multiple lines of text in one cell
  20. Copy a worksheet
  21. Create a chart = F11
  22. Copy / Paste to Add to a Chart
  23. F12 = save as
  24. Sort multiple columns

 

 

 

Lesson 8: Financial Formulas

 

 

  1. NPV - net present value
  2. IRR - Internal rate of return
  3. FV - future value
  4. Future Value (2nd example)
  5. PV - Present value
  6. DDB - double declining balance
  7. ISPMT - interest paid for an investment
  8. PMT - find pmt of a loan
  9. PMT - find pmt of loan (example 2)
  10. VDB - variable declining balance
  11. NPER - find total number of payments
  12. RATE - find rate of interest
  13. PPMT - find principal of a loan
  14. IPMT - find interest of a loan
  15. Accrued Interest Function
  16. Import txt file into Excel
  17. PMT - find pmt of a loan
  18. PPMT - find principal of a loan
  19. IPMT - find interest of a loan
  20. Balance - find balance of loan
  21. Compound Interest - how to find
  22. Loan Payments - different durations
  23. Investment - find future value
  24. Annuity - present value
  25. Total Paid on Loan

 

 

 

Lesson 9: Formulas Date Time / Logic / Macros

 

 

  1. Addition formula
  2. Division
  3. Multiplication
  4. Subtraction
  5. Find number of work days
  6. Days between two dates
  7. Current date (shortcut key)
  8. Current date plus ? days
  9. NOW formula
  10. TODAY formula
  11. AND formula
  12. IF formula
  13. OR formula
  14. Macros - create a macro
  15. Macros - give permission
  16. Macros - run a macro
  17. Macros - create a button
  18. Macros - delete a macro

 

 

 

Lesson 10: Margins / Print Area / Gridlines

 

 

  1. Margins: change them
  2. Margins: custom made
  3. Center worksheet on page
  4. Center sheet vertically on page
  5. Header Footer change margin
  6. Page orientation Portrait to landscape
  7. Paper size change it
  8. Print area: set it
  9. Page breaks insert
  10. Place picture in background
  11. Columns: print repeat the columns
  12. Scale to fit
  13. Gridline: print your gridlines
  14. Print row and column headings

 

 

 

Lesson 11: Conditional Formatting

 

 

  1. Conditional Formatting - find the top 3 figures
  2. Conditional Formatting - Find the top 5 figures
  3. Conditional Formatting - Find the lowest 3 figures
  4. Find the lowest 5 figures
  5. Find unique values
  6. Find duplicate values
  7. Conditional formatting with text
  8. Conditional formatting equal to a figure
  9. Conditional formatting remove it
  10. Remove duplicates
  11. Multiple conditional formatting rules
  12. Remove conditional formatting
  13. Change conditional formatting

(click to go back)