Excel 2007: Advanced

Excel 2007: Advanced


Total Run Time: 6 hrs

Now that you have 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.


  1. Learn how to sort and filter data faster in Excel 2007. You can even sort or filter by color.
  2. Master pivot tables to summarize a million rows of transactional data into a flexible summary without writing a single formula.
  3. Reduce repetitive tasks to a single mouse click by creating a macro. Plus, find out why most recorded macros don't work and the one setting to change to allow many recorded macros to work.
  4. See how to use the three new charting ribbon tabs to quickly format a chart or how to use the dialog boxes to coax charts out of Excel that don't look like Excel charts. You'll discover how to make charts in your company's colors with one keystroke.
  5. Create a report for every customer or department with a couple of mouse clicks. Learn five ways to summarize data and when to use each method.


This title is available in the
Total Training All-Access library.

Subscribe Now

SKU: tt_M1XL07 Categories: , ,


Sit back and discover how to unleash the power of Excel with this in-depth video series, Total Training for Microsoft Excel 2007: Advanced. 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.

Sample clip

Course outline

Chapter 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

  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
Chapter 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
Chapter 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

  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
Chapter 6: RECORDING MACROS (18 min)

  1. Displaying the Developer Tab
  2. Recording a Simple Macro
  3. Simplifying the Macro
  4. Recording Using Relative References
Chapter 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

Presenter Info

Bill JelenBill Jelen is the host of MrExcel.com and the author of sixteen books about Microsoft® Excel® including Special Edition Using Excel 2007, Excel 2007 Miracles Made Easy, Pivot Table Data Crunching for Excel 2007, VBA & Macros for Microsoft Excel, Excel for Marketing Managers and Guerilla Data Analysis Using Microsoft Excel. He has made over 50 guest appearances on TV’s Call for Help with Leo Laporte and was voted guest of the year on the Computer America radio show.

His daily video podcast “Learn Excel from MrExcel” is a top 50 tech video podcast on iTunes. Bill will entertain you while showing you the powerful tricks in Excel.

Before founding MrExcel.com in 1998, Jelen spent twelve years “in the trenches”, as a financial analyst for the accounting, finance, marketing, and operations departments of a publicly held company. Since then, his company automates Excel reports for hundreds of clients around the world. The website answers over 30,000 questions a year – for free – for readers all over the world. Jelen joins us from Akron, Ohio.