Advanced MS Excel & Pivot Tables

Formula Basics

Displaying and highlighting formulas, Auditing tools, Using entire row/column references, Copying column formulas instantly, Converting formulas to values with a simple drag, Updating values without formulas, Simplifying debugging formulas, Enhancing readability with range names, Creating 3D formulas to gather data from multiple sheets.

Formula and Function Tools

Understanding the hierarchy of operations in Excel formulas, Using the Formulas tab on the Ribbon for locating functions, Using the Insert Function button for guidance with unfamiliar functions, Using and extending AutoSum button capabilities, Using absolute and relative references in formulas, Using mixed references in formulas

IF and Related Functions

Exploring IF logical tests and using relational operators, Creating and expanding the use of nested IF statements, Using the AND and OR functions with IF to create compound logical tests

Lookup and Reference Functions

Looking up information with VLOOKUP and HLOOKUP, Finding approximate matches with VLOOKUP, Finding exact matches with VLOOKUP, Nesting lookup functions, Using VLOOKUP with large tables, Finding Second Match using VLOOKUP, Using Wildcard Characters in VLOOKUP, Using VLOOKUP for Merging Different Sheets and Workbooks, Using VLOOKUP for comparing Data and Separate inconsistent fields, Finding table-like information within a function with CHOOSE, Locating data with MATCH, Retrieving information by location with INDEX, Using MATCH and INDEX together, Using INDEX to build dynamic Ranges

Power Functions

Tabulating information using a single criterion with COUNTIF, SUMIF, and AVERAGEIF, Tabulating information using multiple criteria with COUNTIFS, SUMIFS, and AVERAGEIFS

Statistical Functions

Finding the middle value with MEDIAN, Ranking data without sorting with RANK, Finding the largest and smallest values with LARGE and SMALL, Tabulating blank cells with COUNTBLANK, Using COUNT, COUNTA, and the status bar

Math Functions

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

Date and Time Functions

Understanding Excel date/time capabilities in formulas, Using TODAY and NOW functions for dynamic date/time entry, Identifying the day of the week with WEEKDAY, Counting working days with NETWORKDAYS, Determining a completion date with WORKDAY, Tabulating date differences with DATEDIF, Calculating end-of-month and future/past dates with EDATE and EOMONTH, Converting text entries into dates and times with DATEVALUE and TIMEVALUE

Array Formulas and Functions

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

Reference Functions

Getting data from remote cells with OFFSET, Returning references with INDIRECT, Using INDIRECT with data validation for two-tiered pick list scenarios

Text Functions

Locating and extracting data with FIND, SEARCH, and MID, Extracting specific data with LEFT and RIGHT, Removing extra spaces with TRIM and removing hidden characters with CLEAN, Using ampersands and CONCATENATE to combine data from different cells, Adjusting the case within cells with PROPER, UPPER, and LOWER, Adjusting character content with REPLACE and SUBSTITUTE, Using other utility text functions: LEN, REPT, VALUE, TEXT

Information Functions

Extracting information with the CELL and INFO functions, Using ISBLANK, ISODD, ISEVEN, ISTEXT, ISNUMBER and other Functions, Using error-checking functions ISERR, ISERROR, IFERROR, ISNA, and IFNA etc, Using the ISFORMULA function with conditional formatting

Pivot tables

Creating and Pivoting PivotTables

Formatting data for use in a PivotTable, Connecting to an external data source, Connecting to an Access database, Consolidating data from multiple sources, Updating and refreshing PivotTable data sources, Adding, removing, and positioning subtotals and grand totals, Changing the PivotTable data field summary operation, Summarizing more than one data field, Creating a calculated field, Grouping PivotTable fields, Using PivotTable data in a formula, Drilling down to the underlying data

Sorting and Filtering PivotTable Data

Sorting PivotTable data, Creating a custom sort order, Filtering a PivotTable field by selection, Filtering a PivotTable by rule, Filtering a PivotTable using a search filter, Filtering a PivotTable using slicers, Formatting slicers, Filtering a PivotTable with report filter fields, Clearing and reapplying PivotTable filters

Formatting PivotTables

Applying a PivotTable style, Creating a PivotTable style, Changing the PivotTable layout, Changing the data field number format, Applying Conditional Formatting to PivotTables, Highlighting cells by applying a rule, Highlighting the top or bottom values in a PivotTable, Formatting PivotTable cells using data bars, Formatting PivotTable cells using color scales, Formatting PivotTable cells using icon sets, Editing conditional formatting rules, Controlling how multiple rules are applied, Deleting a conditional formatting rule

Creating and Manipulating Pivot Charts

Creating a PivotChart, Pivoting a PivotChart, Filtering a PivotChart, Formatting a PivotChart, Changing a PivotChart layout, Changing a PivotChart chart type, Adding a trend line to a PivotChart

Printing PivotTables and PivotCharts

Printing a PivotTable, Printing each item on its own page, Printing a PivotChart

Manipulating PivotTables Using Macros

Recording and reviewing a macro, Running a macro, Creating a simple PivotTable presentation kit

Data Validation

Controlling the Limits of Numeric Data, Whole-number vs. decimal limitations, Using the input message box, Using the Error Alert tab

Setting Up Dropdown Lists (Pick Lists)

Short-list and long-list variations, List location and order, Multitiered lists: Lists depending on lists

Date Controls

Date limitations with basic controls, Date limitations using formulas, Time Controls : Time limitations with basic controls, Time limitations using formulas

Text-Length Controls

Text-length limitations with basic controls, Text-length limitations using formulas

Specialized Custom Formula Controls

Requiring entries to be unique, Locating cells with data validation rules, Identifying cells that violate data validation rules

Conditional Formatting

Different types of Conditional Formatting – Color Scales,Data Bars,Icon Sets etc, Format only cells that contain text, number, or date or time values, Format only top or bottom ranked values, Format only values that are above or below average, Format only unique or duplicate values, Adding many levels of Conditional Formatting, Advanced Conditional Formatting using Formulas

Charts and Dashboards

Identifying chart elements like plot area, chart area, gridlines, and legends, Selecting the right chart type, Understanding chart terminology, Understanding the Ribbon and the Design, Layout, and Format tabs

Creating Basic Charts Quickly

Selecting data to display as a chart, Creating charts instantly with shortcuts, Creating charts with standard menu commands, Creating presentation-ready charts with just a few adjustments, Creating graphic-in-cell charts with sparklines

Fine-Tuning Charts with Design Tab Choices

Switching rows and columns for a different view of the data, Setting a default chart type and creating a template, Dealing with empty and hidden cells, Choosing a chart layout, Changing the location of a chart, Moving and resizing a chart

Layout Tab Options: Inserting Pictures, Shapes, and Text Boxes

Using pictures as chart elements, Adding shapes and arrows, Adding floating text and text boxes

Layout Tab Options: Adding Titles, Labels, and Legends

Adding, editing, and removing chart titles, Adding horizontal and vertical titles, Linking titles to content, Showing numbers of different scales, Specifying the position of tick marks and axis labels, Changing the numeric format on labels, Adding, editing, and removing legends, Adding and editing data labels, Showing the source of a chart's data

Layout Tab Options: Using Axes and Gridlines

Modifying axis scaling, Working with gridlines

Layout Tab Options: Using the Analysis Tools

Analyzing existing and future data with trendlines, Adding drop lines, Adding high-low lines and up-down bars, Adding error bars

Format Tab Options:

Adding Shapes and WordArt Styles, Selecting shape fill and outline, Adding shape effects, Applying WordArt styles

Using Other Formatting Tools

Formatting lines and borders, Filling an area with a color gradient, Specifying line style, color, and weight, Working with chart text, Changing the rotation of chart text

Adjusting Specific Chart Types

Using column and bar charts, Using line charts, Using pie charts, Using area, stock, and XY charts, Using doughnut, bubble, and radar charts

Changing a Chart's Data Source

Pasting new data into a chart, Creating charts from multiple data sources, Adding new data using a table

Printing and Sharing Charts

Printing charts, Copying and linking charts with Word and PowerPoint

Building Dynamic Charts

Using Index and Offset Function to Build Dynamic Charts, Adding Interactivity to Charts Using Form Controls.