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.
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.
Learn how to sort and filter data faster in Excel 2007. You can even sort or filter by color.
Master pivot tables to summarize a million rows of transactional data into a flexible summary without writing a single formula.
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.
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.
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.
Chapter I: SUMMARIZING DATA (58 min)
Formatting the Subtotal Rows
Adding and Copying with Subtotals
Introducing the SUMIF Command
Working with the SUMIF Command
Using Array Formulas
Looking at AutoSum Tricks
Utilizing Fill Handle Tricks
Chapter II: WORKING WITH PIVOT TABLES (52 min)
Creating a Pivot Table
Rearranging Fields in a Pivot Table
Explaining the Report Layout Options
Using the Report Filters Feature
Using Top 10 & Date Filters
Handling Blank Cells
Drilling Down in the Pivot Table
Sorting a Pivot Table
Formatting a Pivot Table
Creating Custom Formats
Explaining the Grouping Options
Adding Formulas to a Pivot Table
Changing a Calculation in a Pivot Table
Replicating a Pivot Table
Counting with a Pivot Table
Using Pivot Charts
Chapter III: CHARTING IN EXCEL (75 min)
Moving, Sizing & Copying Charts
Formatting a Series
Exploring the Home & Format Ribbons
Deciding What Chart Format to Use
Show a Time Series with Column or Line Charts
Using Combination Charts
Using Line Chart Accessories
Using Bar Charts to Show Comparisons
Using Component Charts
Using Correlation Charts
Exploring Other Charts
Chart Lies Revealed & Advanced Chart Types
Creating a Custom Layout
Chapter IV: GETTING VISUAL (28 min)
Using Conditional Formatting
Using Advanced Conditional Formatting
Highlighting, Sorting & Filtering Columns
Using SmartArt Functions
Using Different SmartArt Graphics
Embedding a Formula into a Shape
Chapter V: CREATING POWERFUL FORMULAS (85 min)
Using Goal Seek to Find an Answer
Shrinking the Formula Bar
Joining Text Columns
Using Custom Formatting Codes
Using Paste Special
Breaking Apart Text
Converting Text to Numbers
Using Date Functions
Using Bizarre Date Functions
Using the VLOOKUP Function
Using Index Match
Using the Offset Function
Using the Convert Function
Looking at Interesting but Obscure Functions
Using the Rand Function
Working with Regression
Ranking & Sorting Formulas
Chapter VI: RECORDING MACROS (18 min)
Displaying the Developer Tab
Recording a Simple Macro
Simplifying the Macro
Recording Using Relative References
Chapter VII: TIPS & TRICKS (28 min)
Showing Off New Tips in Excel 2007
Tips for Handling Worksheets
Using Excel as a Word Processor
Changing Text Entry Direction
Using Speak Cells
Final Comments & Credits
Total Run Time: 6 hrs
Bill 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.
Frequently Asked Questions
Total Training is a pioneer in innovative online and DVD-based training for leading creative design, digital video, and office productivity software programs.
Ranging from casual hobbyists to the most seasoned professionals, our users quickly learn new software applications and broaden their knowledge of programs currently used. Narrated in an entertaining format by industry experts and leading authors, our video-based software training titles are critical tools for anyone wanting to learn tips, techniques, and best practices from the most respected names in the business.
We currently offer affordable subscriptions to our All-Access Library, which contains hundreds of courses, thousands of clips, and project files so users can follow along.