Module 1 – Getting Started

What you’ll learn in this training
What is a Pivot Table?
Selecting appropriate data for a Pivot Table

Module 2 – Creating & Deleting

Creating a Pivot Table
Selecting various data sources
Quick Analysis Tool
Recommended Pivot Table feature
Classic Pivot Table layout in the grid
Select different source data
Delete a Pivot Table

Module 3 – Custom Appearance & Layout

Add and remove field from a pivot table
Add and use the page field
Change the data area number format
Change how empty cells are displayed
Format a Pivot Table
Sort a Pivot Table
View data detail for an item
View detail for a data cell
Exclude row or column items from the data summary
Group Pivot Table by dates

Module 4 – Printing Options & Copying a Pivot Table

Printing a Pivot Table with column and row field
Insert page breaks after each row item
Copy a Pivot Table

Module 5 – Summarizing with Totals & Formulas

Display grand totals
Change the data summary function
Create a calculated field
Insert a calculated item
List the formulas used in a Pivot Table

Module 6 – Refreshing Data

Refreshing Pivot Table data
Refreshing data at specific time intervals

Module 7 – Pivot Charts

Create a Pivot Chart from an existing table
Create a Pivot Chart from scratch
Change the layout of a Pivot Chart
Convert a Pivot chart to a static chart

Module 8 – Sparklines & Slicers

Use sparklines and slicers
Create sparklines
Change the sparkline type
Format sparklines
View the parts of a slicer
Create a slicer
Format a slicer

Module 1 – Getting StartedWhat you’ll learn in this training
Excel spreadsheet structure overview
Creating and saving an Excel file
Interface overviewModule 2 – Work with Data and DatasetsEnter data into a cell
Create a dataset
Auto-fill data
Create simple calculations
Referencing cells
Auto-fill cell references
Format cell data
Cut and paste dataModule 3 – Create FormulasCreate multiple calculation formulas
Use AutoSum
Create an averaging formula
Retrieve the min and max value
Retrieve the number count
Create a correlation
Access and search for built in formulasModule 4 – Manage Data and Datasets

Trace precedents
Trace dependents
Sort data
Changing text to columns
Setup data validation
Delete and insert columns and rows

Module 5 – Format Your Table

Merge cells
Label data
Change cell background color
Change table format
Freeze panes
Create conditional formatting
Insert images and objects

Module 6 – Work with Sheets

Name and add sheets
Reference cells in other sheets

Module 7 – Work with Charts

Insert a chart
Edit a chart
Change the chart type
Change chart data

Module 8 – Share and Secure Data

Protect a sheet
Hide and unhide columns and rows
Add comments to a cell
Save to OneDrive®
Invite people to share

Module 9 – Import and Export Data

Import data from text
Import data from Access®
Import data from XML file
Export Excel as CSV
Export spreadsheets to PDF
Export data as tab delimited text
Printing spreadsheets

Module 1 – Getting StartedWhat you’ll learn
Overview of Charts
Identify elements of a chart
Select chart data in adjacent cells
Select chart data in nonadjacent cells
Charting using the chart group buttons
Quick analysis toolModule 2 – Chart Tabs and RibbonsView a sample chart
Create column and bar charts
Create line charts
Create pie and doughnut charts
Create scatter and bubble charts
Create area charts
Create radar charts
Create surface charts
Create stock charts
Create a chart template
Create a combo chart
Create a trendline for a chartModule 3 – Format Your ChartAdd titles and labels
Change the chart type
Format the chart area
Add a border and/or patterns to the plot area
Format the axis properties
Set format options for titles and labels
Set the placement and font for the legend
Change the data series
Change the data series pattern or marker
Change the shape of a 3D data marker
Change the plot order
Resize and reposition
Show or hide the data table
Change the gap and overlap values
Change the elevation and rotation
Pulling out slices
Use a graphic or pictureModule 4 – Present Your Chart

Place a chart on a separate chart sheet
Print a chart sheet
Print embedded charts
Insert a static copy of a chart
Insert a linked copy of a chart
Save your chart as a Web page

Module 5 – Work with Sparklines

Create sparklines
Group and ungroup sparklines
Edit the sparkline data
Change the sparkline type
Show sparkline data points
Format the sparkline
Change axis settings
Change hiddine and empty cell settings
Clear sparklines

Module 6 – New Chart Types

Creating a histogram chart and pareto lines
Creating a waterfall chart
Creating box and whisker plots
Creating tree maps and sunburst maps

Module 1 – Getting StartedWhat you’ll learn

Module 2 – Data Validation

Specify data validation criteria
Create data validation rule messages
Identify invalid data within a data validation range
Create a data validation drop-down list
Find cells that contain Data Validation rules
Clear existing Data Validation rules

Module 3 – Conditional Formatting

Understand Conditional Formatting
Apply Greater Than Conditional Formatting
Apply other conditional operators
Checking for duplicate values
Apply Top/Bottom rules
Apply data bars
Apply color scales
Apply icon sets
Create a Conditional Formatting rule
Manage Conditional Formatting rules
Clear Conditional Formatting rules

Module 1 – Getting Started & What’s NewWhat you’ll learn
TEXTJOIN
IFS
MAXIFS and MINIFSModule 2 – FormulasSUM function
AVERAGE function
COUNT function
DATE and NOW functions
DAYS function
MATCH function
TRANSPOSE function
ROUND function
AND function
OR function
IF function
NESTED function
COUNTIF function
SUMIF function
VLOOKUP function
HLOOKUP function
CONCATENATE (CONCAT) function
Text to columnsModule 3 – Linking and Consolidating WorksheetsCreate a link formula within the same workbook
Create a link formula to another workbook
Consolidate values from multiple workbooks
Consolidate worksheet values within a function
Absolute links vs. relative links
Consolidate command with multiple sheets
Consolidate command with multiple workbooks
Open a workbook with external reference formulas
Break links to external referencesModule 4 – Auditing & Error Checking

Display formulas
Identify precedent cells
Identify dependent cells
Check for errors
Evaluate a formula

Module 1 – Protecting Your SpreadsheetProtecting a worksheet
Unprotecting a workbook
Using a password to protect a worksheet
Unprotecting a worksheet
Locking and unlocking specific cells
Protecting the structure of a workbook
Marking workbook as final

Module 1 – Describing DataWhat you’ll learn in this training
Finding the arithmetic mean
AVERAGEA function
AVERAGEIF function
AVERAGEIFS function
Find the geometric mean
Find the median
Find the mode
Find the standard deviation
Use the STDEVA functions
Find the variance
Use the VARA functions
Find the kurtosis of a distribution
Find the skewness of a distribution
Find the confidence interval
Use the FREQUENCY function
Find the quartile of a data set
Ranking values
Finding a value’s standard scoreModule 2 – Comparing VariablesFind the bivariate correlation coefficient
Find the values associated with a linear regression
Find linear slope
Find the y-intercept of a linear regression
Predict a future value using an existing value
TREND function
Find the values associated with an exponential regression
Predicting values along an exponential curve using existing valuesModule 3 – Tests of SignificanceFinding the probability of a one-tailed Z-Test
Finding the probability associated with a Student’s T-Test for paired samples
Finding the critical value of the Student’s T-Test for paired samples
Examining data for homogeneity of variance using F-TEST function
Finding the critical value of the F-test
Finding the probability associated with a student’s T-Test for independent samples
Finding the critical value of the student’s T-Test for independent samples
Finding the probability associated with a chi-squared test for independence
Finding the critical value of the chi-squared test for independenceModule 4 – Printing Options & Copying a Pivot Table

Printing a Pivot Table with column and row field
Insert page breaks after each row item
Copy a Pivot Table

Module 5 – Data Analysis ToolPak

Activating the data analysis ToolPak
Finding descriptive statistics
Create a histogram
Performing a T-test for paired or related samples
Examining data for homogeneity of variance
Performing a T-test for independent or unrelated samples
Performing a one-way analysis of variance (ANOVA)
Performing a factorial analysis of variance (ANOVA)
Performing a repeated measures analysis of variance (ANOVA)
Finding the correlation of two or more variables
Performing a linear regression