Presented below are some of the more advanced concepts, topics and bullet points I like to cover in my Advanced excel courses.
1. E-Mail Merge from Excel
a. Demonstrate
2. Validation
a. Drop Down List
b. Dates, Whole Numbers, Decimals
c. Comments
Also:
a. Color of Data Input Cells
b. =TODAY
c. =VLOOKUP
d. Macro & Macro Buttons
3. Macros
a. Create “Page Setup” Macro
a. Simply turn on macro recording, press keys, turn off macro recording
b. No Spaces allowed in macro name
c. Assign macro to icon or object for easy access
b. Record in workbook vs. personal macro workbook
c. Absolute vs. relative reference
d. Create an “Erase” Macro
e. Create a “Print” Macro
f. Create Macro Buttons
g. Show Developer Tab
h. Introduction to VBA (Not too deep)
i. Insert VBA elements into Excel – Combo Box
j. Displays the Macro dialog box - ALT+F8
k. Displays the Visual Basic Editor - ALT+F11
4. Hyperlinks
a. Text
b. Objects
c. Text Box
d. Icons
e. To Web Sites
f. To E-mail Addresses
g. To Bookmarks
h. To Other Files
5. Administrative Page
a. Title, Company, Date, Notes, Review Notes, Etc.
b. Table of Contents (Linked to worksheets, named ranges and other documents)
c. Macro Buttons
6. Protection
a. Locked Cells
b. Hidden Cells
c. Protect Sheet (Review Ribbon)
d. Protect Sheet Options
7. Encryption (Password Protection)
a. Save As, Tools, General Options (In Excel 2003)
b. 40 Bit vs 128 Bit (in 2003 Only)
c. Explaining Bits and Encryption
8. Formula Auditing
a. CTRL + ~
b. Formula Auditing Tool Bar
c. Precedents & D
d. Dependents
e. Links to other worksheets or workbooks
9. Gantt Chart
|
a. Start by creating a stacked bar chart |
|
b. Remove the Data Series by right mouse clicking and choosing Select Data Source |
|
c. Add a new Data Source Named Starting Date, and point to the range of start dates for the values |
|
d. Add another new data source named Duration (Days), and point to the range of duration days for the values |
|
e. Add Category Axis Labels and point to the Task names in Column A |
|
f. Remove legend by selecting legend and pressing the Delete key |
|
g. Click on the beginning series and set the Fill and Borders to None |
|
h. Right mouse click on the task labels, choose format Axis, and check the Categories in Reverse Order box (if needed) |
|
i. In two blank cells, write a formula referencing the start and end dates, convert these dates to numbers with formatting |
|
j. Use the resulting numbers to set the scale of the Gantt Chart (perhaps use a slightly larger range of dates) |
|
k. Right mouse click on the date range, set the minimum and maximums to fixed using the numbers acquired in the above step |
|
l. Format the date range to show a short date |
|
m. Format the remaining data bars to display a 3-D bevel |
|
n. For added touch, search Google images for a nice picture of a house, save it to your hard drive. |
|
o. Set the background plot area to picture, and wash out the picture enough so that the chart is still readable. |
|
p. Add a title or text boxes as needed to complete the description of the Gantt Chart |
10. Web Queries
a. Stock Portfolio Example
b. Link to Ticker Symbols
c. Link Results to Portfolio
d. Refresh
e. Refresh All
11. Precision as Displayed
a. Example
b. Worst Dialog Box
c. Auto Rounding and Truncating
12. Linear Regression Analysis
a. Simple Example
b. Linear Regression Explained
c. More Complex Example
13. Tabs
a. Rename
b. Color
c. Reorder
d. Select Multiple
e. Duplicate with CTRL + Drag
14. Excel 2007
a. Three Categories of Improvements
a. Larger Capacity
b. New menus
c. Presentation Quality Output
b. Demonstrate:
a. Recent Documents
b. Push Pins
c. Data Bar Formatting
d. Traffic Light Formatting
e. Picture Support
f. Chart Improvements
g. Animate Excel Charts in PPT by Series
h. Smart Art
i. New Headers & Footers Controls
j. Contextual Menus
k. Quick Access Tool Bar
l. PDF versus XPS formats
m. Watch Window
15. Set up Options
a. Always show full menus
b. Uncheck move on enter
c. Turn on transition keys so home key takes you home
16. Fill in Missing Data
a. By copying formula to blank cells
b. Simple Example
c. QuickBooks Example
17. OLE Object Lining an Embedding (OLE)
a. Simple Example – Organizational Chart
b. Simple Example – Wave Sound
c. Simple Example – Video Clip
d. Excel embedded into Word
e. Word Embedded into Excel
18. File Linking
a. Copy paste
b. Copy paste Link
c. Copy paste Link as Picture
d. Copy paste as Object
19. SUMIF
20. VLOOKUP Example
21. Loan Amortization Schedule example
22. Consolidate Similar Budgets Example
23. Consolidate Dis -Similar Budgets Example
24. Scenario Manager
25. Solver
26. Get Excel 2007 for $299 - Action pack
27. Combo Charts