Title: Microsoft Excel 2007 Advanced Category: Communication & Office Tools Presenter: Bill Jelen Platform: Windows Format: DVD-ROM Running Time: 6 Hrs, 1 DVD Level: Intermediate to Advanced Project Files: Included
Now that you got your feet wet learning the new features in Microsoft Excel 2007 in the first video series, Total Training for Microsoft Excel 2007: Essentials, you're ready to dive into the advanced capabilities. While creating pivot tables and macros seems daunting, you'll be at ease learning some new techniques that will streamline your next data analysis project. Sit back and discover how to unleash the power of Excel with this in-depth video series.
Lesson 1: SUMMARIZING DATA (58 min) 1. Advanced Subtotals 2. Formatting the Subtotal Rows 3. Adding and Copying with Subtotals 4. Consolidating Data 5. Introducing the SUMIF Command 6. Working with the SUMIF Command 7. Using Array Formulas 8. Looking at AutoSum Tricks 9. Utilizing Fill Handle Tricks
Lesson 2: WORKING WITH PIVOT TABLES (52 min) 1. Creating a Pivot Table 2. Rearranging Fields in a Pivot Table 3. Explaining the Report Layout Options 4. Using the Report Filters Feature 5. Using Top 10 & Date Filters 6. Handling Blank Cells 7. Drilling Down in the Pivot Table 8. Sorting a Pivot Table 9. Formatting a Pivot Table 10. Creating Custom Formats 11. Explaining the Grouping Options 12. Adding Formulas to a Pivot Table 13. Changing a Calculation in a Pivot Table 14. Replicating a Pivot Table 15. Counting with a Pivot Table 16. Using Pivot Charts
Lesson 3: CHARTING IN EXCEL (75 min) 1. Charts Refresher 2. Moving, Sizing & Copying Charts 3. Formatting Charts 4. Formatting a Series 5. Exploring the Home & Format Ribbons 6. Deciding What Chart Format to Use 7. Show a Time Series with Column or Line Charts 8. Using Combination Charts 9. Using Line Chart Accessories 10. Using Bar Charts to Show Comparisons 11. Using Component Charts 12. Using Correlation Charts 13. Exploring Other Charts 14. Chart Lies Revealed & Advanced Chart Types 15. Creating a Custom Layout
Lesson 4: GETTING VISUAL (28 min) 1. Using Conditional Formatting 2. Using Advanced Conditional Formatting 3. Highlighting, Sorting & Filtering Columns 4. Using SmartArt Functions 5. Using Different SmartArt Graphics 6. Embedding a Formula into a Shape
Lesson 5: CREATING POWERFUL FORMULAS (85 min) 1. Using Goal Seek to Find an Answer 2. Auditing Formulas 3. Shrinking the Formula Bar 4. Understanding Errors 5. Joining Text Columns 6. Using Custom Formatting Codes 7. Pasting Values 8. Using Paste Special 9. Breaking Apart Text 10. Converting Text to Numbers 11. Using Date Functions 12. Using Bizarre Date Functions 13. Using Exponents 14. Using the VLOOKUP Function 15. Using Index Match 16. Using the Offset Function 17. Using the Convert Function 18. Looking at Interesting but Obscure Functions 19. Using the Rand Function 20. Working with Regression 21. Ranking & Sorting Formulas
Lesson 6: RECORDING MACROS (18 min) 1. Displaying the Developer Tab 2. Recording a Simple Macro 3. Simplifying the Macro 4. Recording Using Relative References
Lesson 7: TIPS & TRICKS (28 min) 1. Showing Off New Tips in Excel 2007 2. Tips for Handling Worksheets 3. Using Excel as a Word Processor 4. Miscellaneous Tips 5. Validating Data 6. Changing Text Entry Direction 7. Using Speak Cells 8. Final Comments & Credits
