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

Microsoft Excel 2013 Intermediate Course

Click a link below to get started

(click to go back)

 

 

 

Lesson 1: Tables

 

 

  1. Table create a
  2. Table tab
  3. Table drop down arrow
  4. Table filter arrows
  5. Table banded row
  6. Table header row
  7. Table quick styles
  8. Table total row
  9. Sharepoint -export table to
  10. Table filter arrows disabled
  11. Table to no table
  12. Table create table
  13. Table insert row column
  14. Table filtering
  15. Table turn off filtering
  16. Table sort your data
  17. Table top 10 answers
  18. Table filters off
  19. Table multiple columns

 

 

Lesson 2: 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
  21. Gantt Chart

 

Lesson 3: Audit / Data Analysis / Headers and Footers

 

 

  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 4: 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 5: Comments / Watermarks / 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 6: Formulas - Count, Sum and Average

 

 

  1. COUNT - counts cells with numbers
  2. COUNTIF - count the numbers if one condition
  3. COUNTIFS - count the numbers if 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 the number of 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 - find answer if 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 7: 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 8: 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 9: 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 10: Formulas - Logic / Basics /

Understanding Error Messages

 

 

  1. IF formula (one variable must be true)
  2. AND formula (two variables must be true)
  3. OR formula (one of two variables must be true)
  4. Addition formula (add numbers)
  5. Division (divide your numbers)
  6. Multiplication (multiply numbers)
  7. Subtraction
  8. NOW formula (current time on your sheet)
  9. TODAY formula ( current date on your sheet)
  10. # OF DAYS between 2 dates
  11. Keystroke to insert current date
  12. Current date plus ? days
  13. NETWORK days (how many work days)
  14. Minimum - find it
  15. Maximum - find it
  16. Average
  17. Average if
  18. Average if(s)
  19. Error Message - Division
  20. Error Message - #### signs
  21. Error Message - name error message
  22. Error Message - reference message
  23. Error Message - value error
  24. Array Formula