|
 Welcome Guest (.|.)


Microsoft Excel 2016 Advanced Course

Click a link below to get started

(click to go back)

 

Lesson 1: Conditional 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 2: Comments / Watermarks / Templates / Hyp

  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 3: 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 4: 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 5: 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 6: 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 7: 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 8: 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 9: 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

 

 

Lesson 10: 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