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
•  Finding table-like information within a function using the CHOOSE function
•  Save Finding table-like information within a function using the CHOOSE function
• Using the SWITCH function for formula-embedded selection
• Save Using the SWITCH function for formula-embedded selection
• Locating data with the MATCH function
• Save Locating data with the MATCH function
• Retrieving information by location
• Save Retrieving information by location
• Using MATCH and INDEX functions together
• Save Using MATCH and INDEX functions together

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

•  Finding the middle value with MEDIAN and most common value with MODE
• Save Finding the middle value with MEDIAN and most common value with MODE
• Ranking data without sorting with RANK and RANK.EQ
• Save Ranking data without sorting with RANK and RANK.EQ
• Finding the largest and smallest values with the LARGE and SMALL functions
• Save Finding the largest and smallest values with the LARGE and SMALL 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

Math Functions

• Working with the ROUND, ROUNDUP, and ROUNDDOWN functions
• Save Working with the ROUND, ROUNDUP, and ROUNDDOWN functions
• Working with MROUND, CEILING, and FLOOR for specialized rounding
• Save Working with MROUND, CEILING, and FLOOR for specialized rounding
• Using the INT and TRUNC functions to extract integer data
• Save Using the INT and TRUNC functions to extract integer data
• Finding the remainder with MOD and using MOD with conditional formatting
• Save Finding the remainder with MOD and using MOD with conditional formatting
• Exploring practical uses for the RAND and RANDBETWEEN functions
• Save Exploring practical uses for the RAND and RANDBETWEEN functions
• Converting a value between measurement systems with CONVERT
• Save Converting a value between measurement systems with CONVERT
• Using the powerful AGGREGATE function to bypass errors and hidden data
• Save Using the powerful AGGREGATE function to bypass errors and hidden data
• Using the ROMAN and ARABIC functions to display a different number of systems
• Save Using the ROMAN and ARABIC functions to display a different number of systems

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

Array Formulas and Functions

• Extending formula capabilities with array formulas
• Save Extending formula capabilities with array formulas
• Counting unique entries in a range with an array formula
• Save Counting unique entries in a range with an array formula
• Determining frequency distributions with the FREQUENCY function
• Save Determining frequency distributions with the FREQUENCY function
• Flipping row and column orientation with TRANSPOSE
• Save Flipping row and column orientation with TRANSPOSE
• Building analysis via regression techniques with TREND and GROWTH
• Save Building analysis via regression techniques with TREND and GROWTH
• Using array formulas and the MATCH function for complex lookups
• Save Using array formulas and the MATCH function for complex lookups

Reference Functions

• Getting data from remote cells with the OFFSET function
• Save Getting data from remote cells with the OFFSET function
• 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

Text Functions

• Locating and extracting data with the FIND, SEARCH, and MID functions
• Save Locating and extracting data with the FIND, SEARCH, and MID functions
• Extracting specific data with the LEFT and RIGHT functions
• Save Extracting specific data with the LEFT and RIGHT functions
• Using the TRIM function to remove unwanted spaces in a cell
• Save Using the TRIM function to remove unwanted spaces in a cell
• Using ampersands and CONCATENATE to combine data from different cells
• Save Using ampersands and CONCATENATE to combine data from different cells
• Using the CONCAT and TEXTJOIN to combine data from different cells
• Save Using the CONCAT and TEXTJOIN to combine data from different cells
• Adjusting alphabetic case with the UPPER, LOWER, and PROPER functions
• Save Adjusting alphabetic case with the UPPER, LOWER, and PROPER functions
• Adjusting character content with the REPLACE and SUBSTITUTE functions
• Save Adjusting character content with the REPLACE and SUBSTITUTE functions
• Using the utility text functions: TEXT, REPT, and LEN
• Save Using the utility text functions: TEXT, REPT, and LEN

Information Functions

• Extracting information with the CELL and INFO functions
• Save Extracting information with the CELL and INFO functions
• Using ISBLANK, ISODD, ISEVEN, ISTEXT, ISNONTEXT, and ISNUMBER
• Save Using ISBLANK, ISODD, ISEVEN, ISTEXT, ISNONTEXT, and ISNUMBER
• Using the ISERR, ISERROR, IFERROR, and ISNA error-checking functions
• Save Using the ISERR, ISERROR, IFERROR, and ISNA error-checking functions
• Tracking and highlighting formula cells with the ISFORMULA function
• Save Tracking and highlighting formula cells with the ISFORMULA function

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

Manipulating PivotTables

• Record and review an Excel macro
• Save Record and review an Excel macro
• Run an Excel macro
• Save Run an Excel macro
• Create a simple PivotTable presentation kit
• Save Create a simple PivotTable presentation kit
• Change PivotTables using keyboard shortcuts
• Save Change PivotTables using keyboard shortcuts

Enabling and Adding Tables to the Data Model

• Create relationships between tables
• Save Create relationships between tables
• Create a PivotTable using the data model
• Save Create a PivotTable using the data model
• Edit table relationships
• Save Edit table relationships
• Deactivate and delete table relationships

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

Other Specialized Excel Charts

• Create combination charts
• Save Create combination charts
• Create Gantt charts
• Save Create Gantt charts
• Create dynamic charts with filtering
• Save Create dynamic charts with filtering
• New Chart Types in Excel 2016
• Overview of new chart types
• Save Overview of new chart types
• Tree Map chart
• Save TreeMap chart
• Sunburst chart
• Save Sunburst chart
• Waterfall chart
• Save Waterfall chart
• Histogram chart
• Save Histogram chart
• Pareto chart
• Save Pareto chart
• Box and Whisker chart
• Save Box and Whisker chart
• Funnel chart
• Save Funnel chart
• Chapter Quiz
• Change a Chart’s Data Source
• Print and Share Charts
• Macros
• What is a Macro
• Creating and running your first macro
• Saving workbooks with Macros
• Macro security settings for Workbooks with Macros
• The Personal Macro Workbook
• Deleting Macros
• Use of relative or absolute referencing
• Trigger a Macro with a Keyboard Shortcut
• Formatting with a Macro
• Switch Scenarios and Views with Macros
• Use of worksheet buttons to Trigger Macros
• Customizing form buttons and other shape triggers
• Assigning Macros to Ribbon Icons
• Create your own ribbon
• View and edit Macro code

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