Excel 2011 – Advanced


In this Excel 2011 – Advanced training course, you'll learn complex techniques such as how to link and consolidate data within a single workbook or between multiple workbooks, use Goal Seek and Scenarios, protect your data, and share Excel data with Microsoft Word.

By the time you've completed this course, you'll even have a mastery of data validation, auditing and error checking.

Platform: Mac

Length: 2 hours 3 min

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

Subscribe Now


Created by Beth Bruestle and Flip Crummer, the Microsoft Excel 2011 Advanced online training series provides several ways to enhance the data in a workbook. You’ll learn how to create custom date, time and number formats, use data validation rules, create advanced sorting and filtering options, use outlines, and more!

This training course is for Microsoft Office Excel 2011 for Mac.

Sample clip

A. Templates
Creating custom templates
Creating a workbook from a template
Modifying a template file
Deleting a template

B. Working with Dates and Times
Entering dates into a cell
Formatting date entries
Displaying the current date
Generating a series of dates
Using date related functions
Entering times into a cell
Formatting time entries
Displaying the current time

C. Custom Number Formats
Formatting numbers automatically
Understanding custom number formatting
Creating a custom number format
Creating custom date and time formats
Hiding the contents of a cell using a custom format
Deleting a custom format

D. Advanced Charting
Selecting elements in a chart
Formatting chart elements
Modifying the chart area
Modifying the plot area
Modifying the legend
Modifying the value axis
Modifying the category axis
Deleting a data series from a chart
Adding a data series to a chart pt. 1
Adding a data series to a chart pt. 2
Adding a trendline to a chart pt. 1
Adding a trendline to a chart pt. 2
Creating a combination chart
Creating picture charts

E. Data Validation
Specifying data validation criteria
Creating data validation rule messages
Identifying invalid data within a data validation range
Creating a data validation drop-down list
Finding cells that contain data validation rules
Clearing existing data validation rules

F. Sorting and Filtering
Sorting data in a worksheet
Sorting by multiple columns or criteria
Filtering data in a worksheet
Clearing a filter in a worksheet
Creating and/or statements
Creating a criteria range
Defining criteria using the criteria range
Creating and/or statements using the criteria range

G. Outlines
Creating an outline automatically
Creating a manual outline
Displaying levels in an outline
Hiding the outline symbols
Clearing an outline
Adding subtotals to a worksheet pt. 1
Adding subtotals to a worksheet pt. 2

H. Linking and Consolidating Worksheets
Creating a link formula within the same workbook
Creating a link formula to another workbook
Consolidating worksheets using formulas pt. 1
Consolidating worksheets using formulas pt. 2
Consolidating worksheets using formulas pt. 3
Using the Consolidate command pt. 1
Using the Consolidate command pt. 2
Opening a workbook with external reference formulas
Breaking links to external references

I. What-if Analysis
Creating scenarios pt. 1
Creating scenarios pt. 2
Displaying scenarios
Modifying scenarios
Generating a scenario report
Deleting a scenario
Using Goal Seek

J. Workbook Protection
Protecting a worksheet
Unprotecting a worksheet
Using a password to protect a worksheet
Protecting a workbook pt. 1
Protecting a workbook pt. 2
Unprotecting a workbook

K. Auditing and Error Checking
Displaying formulas
Identifying precedent cells
Identifying dependent cells
Checking for errors

L. Sharing Data with Word
Copying and pasting static information
Copying and pasting as a link
Importing a text file into Excel