This course is designed for students who are interested in going beyond basic skills and need to chart and format using Microsoft® Office Excel® 2013/2016.

Formula and Function Tips and Shortcuts

• Displaying and highlighting formulas
• Save Displaying and highlighting formulas
• Using Auditing tools
• Save Using Auditing tools
• Using entire row and column references
• Save Using entire row and column references
• Copying column formulas instantly
• Save Copying column formulas instantly
• Converting formulas to values with a drag
• Save Converting formulas to values with a drag
• Updating values without formulas
• Save Updating values without formulas
• Debugging formulas with the F9 key
• Save Debugging formulas with the F9 key
• Enhancing readability with range names
• Save Enhancing readability with range names
• Tabulating data from multiple sheets
• Save Tabulating data from multiple sheets

Formula and Function Tools

• Hierarchy of operations in formulas
• Save the Hierarchy of operations in formulas
• Formulas tab for locating functions
• Save the Formulas tab for locating functions
• Insert Function for learning about unfamiliar functions
• Save Insert Function for learning about unfamiliar functions
• Extending the capabilities of AutoSum
• Save Extending the capabilities of AutoSum
• Absolute and relative references
• Save Absolute and relative references
• Using mixed references in formulas
• Save Using mixed references in formulas
• Using auto calculate in the status bar
• Save Using auto calculate in the status bar

IF and Related Functions

• IF logical tests
• Save IF logical tests
• Expanding nested IF statements
• Save Expanding nested IF statements
• AND, OR, and NOT functions with IF
• Save AND, OR, and NOT functions with IF
• Using IFS for multiple conditions
• Save Using IFS for multiple conditions

Lookup and Reference Functions

• Looking up information with VLOOKUP and HLOOKUP
• Save Looking up information with VLOOKUP and HLOOKUPFinding
• approximate matches with VLOOKUP and HLOOKUP
• Save Finding approximate matches with VLOOKUP and HLOOKUP
• Finding exact matches with VLOOKUP
• Save Finding exact matches with VLOOKUP
• Nesting lookup functions
• Save Nesting lookup functions
• Using VLOOKUP with large tables
•  Save Using VLOOKUP with large tables

Power Functions

• Tabulating data using a single criterion
• Save Tabulating data using a single criterion
• Tabulating data using multiple criteria
• Save Tabulating data using multiple criteria
• Using MAXIFS and MINIFS
• Save Using MAXIFS and MINIFS
• Preventing double counting
• Save Preventing double counting

Statistical Functions

• Tabulating blank cells with the COUNTBLANK function
• Save Tabulating blank cells with the COUNTBLANK function
• Using COUNT, COUNTA, and the status bar
• Save Using COUNT, COUNTA, and the status bar

Date and Time Functions

• Understanding Excel date and time capabilities in formulas
• Save Understanding Excel date and time capabilities in formulas
• Using the DATE, YEAR, MONTH, DAY, TIME, HOUR, MINUTE, and SECOND functions
• Save Using the DATE, YEAR, MONTH, DAY, TIME, HOUR, MINUTE, and SECOND
• functions
• Using the TODAY and NOW functions for dynamic date and time entry
• Save Using the TODAY and NOW functions for dynamic date and time entry
• Identifying the day of the week with the WEEKDAY function
• Save Identifying the day of the week with the WEEKDAY function
• Counting working days with the NETWORKDAYS function
• Save Counting working days with the NETWORKDAYS function
• Determining a completion date with the WORKDAY function
• Save Determining a completion date with the WORKDAY function
• Tabulating date differences with the DATEDIF function
• Save Tabulating date differences with the DATEDIF function
• Calculating end-of-month and future and past dates with EDATE and EOMONTH
• Save Calculating end-of-month and future and past dates with EDATE and EOMONTH

Reference Functions

• Returning references with the INDIRECT function
• Save Returning references with the INDIRECT function
• Using INDIRECT with Data Validation for two-tiered pick list scenarios
• Save Using INDIRECT with Data Validation for two-tiered pick list scenarios

Auditing and troubleshooting formulas

• What are tracer arrows
• Adding and removing tracer arrows
• Auditing tools: Error checking and tracing
• Step by step formula processing
• Using the watch window in Troubleshooting

Goal Seek and Solver

• What if analysis using Goal Seek
• Activating the solver add-in
• Using Solver to Complete a what if
• Adding constraints to the solver

Creating and Pivoting PivotTables

• Introducing PivotTables
• Save Introducing PivotTables
• Format data for use in a PivotTable
• Save Format data for use in a PivotTable
• Create a PivotTable
• Save Create a PivotTable
• Pivot a PivotTable
• Save Pivot a PivotTable
• Configure a PivotTable
• Save Configure a PivotTable
• Connect to an external data source
• Save Connect to an external data source
• Consolidate data from multiple sources
• Save Consolidate data from multiple sources
• Manage PivotTables
• Save Manage PivotTables
• Define a default PivotTable layout
• Save Define a default PivotTable layout

Summarizing PivotTable Data

• Manage subtotals and grand totals
• Save Manage subtotals and grand totals
• Change the data field summary operation
• Save Change the data field summary operation
• Summarize more than one data field
• Save Summarize more than one data field
• Manage dates in a PivotTable
• Save Manage dates in a PivotTable
• Create a calculated field
• Save Create a calculated field
• Use PivotTable data in a formula
• Save Use PivotTable data in a formula
• Drill down to the underlying data
• Save Drill down to the underlying data

Sorting and Filtering PivotTable Data

• Sort PivotTable data
• Save Sort PivotTable data
• Create a custom sort order
• Save Create a custom sort order
• Filter a PivotTable field by selection
• Save Filter a PivotTable field by selection
• Filter a PivotTable by rule
• Save Filter a PivotTable by rule
• Filter a PivotTable using a search filter
• Save Filter a PivotTable using a search filter
• Filter a PivotTable using slicers
• Save Filter a PivotTable using slicers
• Format slicers
• Save Format slicers
• Filter with report filter fields
• Save Filter with report filter fields
• Clear and reapply PivotTable filters
• Save Clear and reapply PivotTable filters
• Filter a PivotTable using a timeline
• Save Filter a PivotTable using a timeline

Formatting PivotTables

• Apply a PivotTable style
• Save Apply a PivotTable style
• Create a PivotTable style
• Save Create a PivotTable style
• Change the PivotTable layout
• Save Change the PivotTable layout
• Change the data field number format
• Save Change the data field number format

Applying Conditional Formats to PivotTables

• Highlight cells by applying a rule
• Save Highlight cells by applying a rule
• Highlight the top or bottom values in a PivotTable
• Save Highlight the top or bottom values in a PivotTable
• Format cells using data bars
• Save Format cells using data bars
• Format cells using color scales
• Save Format cells using color scales
• Format cells using icon sets
• Save Format cells using icon sets
• Edit a conditional formatting rule
• Save Edit a conditional formatting rule
• Control how multiple rules are applied
• Save Control how multiple rules are applied
• Delete a conditional formatting rule
• Save Delete a conditional formatting rule

Creating and Formatting Pivot Charts

• Create a PivotChart
• Save Create a PivotChart
• Pivot a PivotChart
• Save Pivot a PivotChart
• Filter a PivotChart
• Save Filter a PivotChart
• Format a PivotChart
• Save Format a PivotChart
• Change the layout of a PivotChart
• Save Change the layout of a PivotChart
• Change the chart type of a PivotChart
• Save Change the chart type of a PivotChart
• Add a trendline to a PivotChart
• Save Add a trendline to a PivotChart

Printing PivotTables

• Print a PivotTable
• Save Print a PivotTable
• Set PivotTable print options
• Save Set PivotTable print options
• Print each item on its own page
• Save Print each item on its own page
• Print a PivotChart
• Save Print a PivotChart

Review the Basic Elements of an Excel Chart

• Create Basic Charts
• Style Your Charts with the Design Tab
• Fine-Tune Your Chart Elements
• Format Tab: Insert Pictures, Shapes, and Text Boxes
• Adjusting Specific Chart Types
• Customize column and bar charts
• Save Customize column and bar charts
• Customize line charts
• Save Customize line charts
• Customize pie charts
• Save Customize pie charts
• Customize area, stock, and XY charts
• Save Customize area, stock, and XY charts
• Customize doughnut, bubble, and radar charts
• Save Customize doughnut, bubble, and radar charts

Attendees will get a training certificate after the completion of the course.