Video description
In this Advanced Excel 2010 training video, expert author Guy Vaccaro follows up his bestselling Beginners Excel 2010 tutorial by delving even deeper into this powerful spreadsheet software.
Microsoft Excel 2010 is much more than a quick way to add up numbers. In this video based tutorial, you will learn advanced features such as nesting IF statements, how to calculate the Mean of a group of numbers using AVERAGEIF, SUMIF and more. You will learn about performing lookups with HLOOKUP and VLOOKUP. This tutorial also covers Sparklines, and goes in-depth with Pivot Tables and Charts. Finally, you will learn how to create and record your own Macros.
This advanced tutorial video is not for beginners, and only if you have a firm grasp of the basics should you proceed with this video training. By the conclusion of this advanced computer software tutorial for Microsoft Excel 2010, you will have mastered the advanced features and functions of this software. Working files are included to allow you to follow along with the same files the author trains you with.
Table of contents
- Getting Started
-
The IF Function
- The Syntax Of IF
- Nesting The IF Statement
- Use The AND Operator To Reduce Quantity Of Nested IFs
- Use The OR Operator To Reduce Quantity Of Nested IFs
- The NOT Operator Within AND And OR Statements
- SUMIF For Selective Adding Up
- COUNTIF For Selective Counting
- AVERAGEIF For The Mean Of Cells That Meet Our Criteria
- Multiple Criteria Within The Same SUM AVERAGE And COUNT Functions
- Performing Lookups
- Data Functions
- Math Functions
- Arrays
-
Functions For Working With Text
- LEN And TRIM Two Very Useful Text Functions
- Using LEFT And RIGHT For String Extraction
- FIND And MID Working Together To Extract Parts Of Strings
- Build Strings From Multiple Cells
- Changing The Case Of Text In Cells
- REPLACE And SUBSTITUTE In Action
- Formatting Numeric Values With A Text String Using TEXT
- Extracting The Values From The Text Functions We Have Used
- Other Useful Functions
- Sparklines
- Outlining
- Custom Views
- Scenario
- Auditing And Troubleshooting Formulas
-
Pivot Tables
- What Is A Pivot Table
- Steps To Create A Pivot Table
- Rearranging Fields In A Pivot Table
- Changing The Math Of The Data Summary
- Number Format Control Of The Summary Area
- Creating A Second (Or More) Pivot Table On The Same Data
- Moving A Pivot Table
- Removing A Pivot Table
- Making Use Of The Report Filter Option
- Sorting A Pivot Tables Columns
- Displaying Values As A Percentage
- Refreshing A Pivot Table Manually Or Semi-Automatically
- Drilling Down Behind The Pivot Table Summaries
- Applying Pivot Table Styles
- Creating Your Own Custom Pivot Table Style
- Copying A Pivot Table Style Between Workbooks
- Using More Than One Field In Row And Column Headings
- Disabling And Enabling Grand And Sub Totals
- Filtering Columns And Rows Within A Pivot Table
- Dealing With Empty (NULL) Cells
- Exploring The Additional Pivot Table Options
- Introducing The Slicer Tool
- Managing Your Slices
- Formatting Your Slices
- Connecting A Pivot Table To SQL Server
- External Connection Refresh Rate And Password Saving
- Pivot Charts
- Goal Seek And Solver
-
Macros
- What Is A Macro
- Creating Storing And Running Your First Macro
- Using Relative Or Absolute Referencing During Recording
- Saving Workbooks With Macros Issues
- Opening Files Containing Macros
- The PERSONAL Workbook
- How To Delete Macros
- Use A Macro For Formatting
- Trigger A Macro With A Keyboard Shortcut
- Using Form Buttons To Trigger Macros
- Customizing The Form Buttons
- Assigning Macros To Ribbon Icons
- Create Your Own Ribbon
- Remove Options From Ribbons
- View And Edit Macro Code
- Add A Confirmation Dialog Box To Macros
Product information
- Title: Advanced Microsoft Excel 2010
- Author(s):
- Release date: February 2011
- Publisher(s): Infinite Skills
- ISBN: 9781926873367
You might also like
video
Microsoft Excel 2010
In this Microsoft Excel 2010 tutorial video, veteran Infinite Skills trainer Guy Vaccaro introduces you to …
video
Microsoft Excel - Charts In Detail
In this Microsoft Excel – Charts in Detail training course, expert author Guy Vaccaro takes you …
video
Using Microsoft Excel 2010
Using Microsoft Excel 2010brings you over 45 media elements detailing everything from getting familiar with the …
video
Advanced Microsoft Excel 2013
In this advanced training course for Microsoft Excel 2013, expert author Guy Vaccaro takes you beyond …