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

Microsoft Excel 2010 Advanced Course

Click a link below to get started

(click to go back)

 

Lesson 1: Protect Workbook / Shortcuts

 

 

  1. Protect your worksheet from people making changes
  2. Un protect your worksheet - to make changes
  3. Protect - how to protec only certain cells
  4. Un protect certain cells
  5. Protect ENTIRE workbook...not just the sheet
  6. Un protect your workbook
  7. Share your workbook
  8. Protect and share your workbook
  9. Work book un protect
  10. Stop sharing
  11. Allow macros to run
  12. Password - assign a password to open the workbook
  13. Remove password to open
  14. Turn on track changes
  15. Golden Rule of Excel
  16. Chart; change default type
  17. Copy formats to other areas
  18. Short cut key strokes
  19. "Go To" shortcuts
  20. Navigate arrows shortcuts
  21. F12 = Save as
  22. Move a chart to a difference location
  23. CTRL Copy add to a chart
  24. 2 lines of text in one cell
  25. Join 2 columns no space
  26. Join 2 columns with space

 

 

Lesson 2: Analyze the Data

 

 

  1. Trace Cell Precedents
  2. Trace Cell Dependents
  3. Error checking
  4. Data Validation - error checking
  5. Watch window - watch specific cells in an area
  6. Duplicates remove from two different books
  7. Sort data (no table)
  8. DAVERAGE - Database formula to find average
  9. Filter data (no table)
  10. DCOUNT - database formula to find the count
  11. DSUM - database formula to find the total
  12. Evaluate a Formula
  13. Export Excel to a Text File
  14. Goal Seek
  15. Outline Group Structure
  16. Hyperlink (same work book)
  17. MATCH (with text)
  18. Trend lines - add to a chart
  19. Solver
  20. Import txt file into Excel
  21. Scenario Manager
  22. Sort multiple columns

 

 

Lesson 3: Formulas # 3 of 5 / Charts and Graphs

 

 

  1. Chart - create a chart by selecting your data
  2. Create - create a chart with the shorcut key F11
  3. Chart - add labels so your audience can know what
  4. Chart - put the chart and data on same sheet
  5. Axis = add titles to your x and y axis
  6. Colors - change chart colors
  7. Chart change type
  8. Colors change chart
  9. Context tabs chart
  10. Font chart color change
  11. Title - give a chart a
  12. Title chart horizontal
  13. Chart move chart to new page
  14. Legend move legend from one area to another
  15. IPMT
  16. ACCRINT
  17. INDEX
  18. LOOKUP
  19. UPPER
  20. PROPER
  21. LOWER
  22. TRIM
  23. Join two columns

 

 

Lesson 4: Financial Formulas (continued)

 

  1. Calculate monthly payment
  2. Monthly payment
  3. Monthly payment (example)
  4. Amount of interest paid
  5. Double declining balance
  6. Variable declining balance
  7. Net present Value
  8. Internal Rate of Return
  9. Future Value
  10. Future value (2nd example)
  11. Present Value
  12. Balance owed on a loan
  13. NPER - total # of payments
  14. Rate
  15. PPMT
  16. Accrued Interest
  17. IPMT
  18. Concatenate
  19. PMT
  20. PPMT
  21. IPMT
  22. Compound Interest
  23. Loan Payments - diff. durations
  24. Investment
  25. PV of annuity
  26. Total Paid - principal and interest

 

Lesson 5: Advanced Formulas

 

 

  1. Order of operations
  2. Relative cell reference
  3. SUMIF formula
  4. Text to columns
  5. Text to columns 2
  6. TODAY formula
  7. Insert Function box
  8. Function Box insert
  9. Formulas - multiple worksheet
  10. NOW
  11. OR formula
  12. IF formula
  13. Formula - mulitple workbook
  14. COUNTIF Formula
  15. AND formula
  16. NPV
  17. IRR
  18. FV
  19. FV (2nd example)
  20. PV
  21. NPER
  22. RATE
  23. PPMT
  24. Subtotal Formula while filtering
  25. Network Days - how many work days

Lesson 6: Excel 2010 Tips and Tricks Vol. 1.

 

 

  1. Copy a formula
  2. Excel golden rule
  3. Navigational arrow
  4. Trim extra spaces
  5. Multiple lines of text in one cell
  6. Copy a sheet
  7. F11 to create a chart
  8. Chart on same page
  9. Add data to already created chart quickly
  10. Move a chart
  11. Change default chart type
  12. Quick Access Toolbar - add icons
  13. Ribbon
  14. Custom list
  15. Larger text with the wheel
  16. Switch between windows
  17. Watch window
  18. View side by side
  19. Transpose the data
  20. Trace precendents
  21. Now formula
  22. Today formula
  23. Show the formulas
  24. Repeat months

 

 

Lesson 7: Excel 2010 Tips and Tricks Vol. 2.

 

 

  1. Repeat days
  2. Repeat years
  3. Repeat numbers
  4. Templates
  5. Find duplicates
  6. Find duplicates in multiple columns
  7. Sort duplicates by color
  8. Find unique values
  9. Remove duplicates
  10. Upper case formula
  11. Lower case formula
  12. Proper case formula
  13. Text to columns
  14. Join two columns - concatenate - with no space
  15. Text to columns with a /
  16. Join two columns - concatenate - with space
  17. Join 3 columns - concatenate - and change the orde
  18. Paste as a value
  19. Data Entry Form
  20. Drop down list
  21. Find the top 3 or 5 or 10
  22. Phone number auto format
  23. Zip code plus 4 auto format
  24. Soc. Security # auto format

 

Lesson 8: Excel 2010 New Features

 

 

  1. The Ribbon - the three parts explained in detail
  2. Quick access toolbar - it is in the top left corne
  3. Windows Frame - Status Bar
  4. Excel templates by Microsoft - how to use the temp
  5. Picture - special effects
  6. Tables
  7. Screen clipping
  8. Screen shot
  9. Word Art
  10. Smart Art
  11. Spark lines
  12. Recent workbooks
  13. Recent places
  14. Close / Save / Save As
  15. Mark as final
  16. Encrypt with password
  17. Protect worksheet
  18. Protect worksheet
  19. Digital Signature
  20. New blank
  21. Templates
  22. Print
  23. Save and Send
  24. Help
  25. Options
  26. Close application


Lesson 9: Formulas: Basics / Logic/ # of days / %

 

 

  1. Formula - addition
  2. Formula - subtraction
  3. Formula - multiplication
  4. Formula - division
  5. Formula - IF = 1 variable true
  6. Formula - AND (2 variables true)
  7. Formula - OR (1 of 2 variables must be true)
  8. Formula - NOW (date and time)
  9. Formula - TODAY (date)
  10. Formula - # of days between 2 dates
  11. Formula - insert current date
  12. Formula - current date plus # of days
  13. Formula - # of working days
  14. Formula - Percent of change
  15. Formula - Percent = grade change
  16. Formula - percent = total price with discount

 

Lesson 10: 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 condition
  7. COUNT - second example
  8. SUM - second example
  9. SUM - find subtotal
  10. COUNTBLANK - empty cells
  11. COUNTA - NON-EMPTY cells
  12. COUNT - cells with numbers
  13. AVERAGE - find the average
  14. AVERAGEIF - one condition
  15. AVERAGEIFS - two conditions
  16. SUMIF - scenario
  17. SUMIFS - scenario
  18. COUNTIF - scenarios
  19. COUNTIFS - scenario
  20. ISERROR