Intermediate Microsoft Excel
for Business Training

Our 1-day intermediate Excel training will show you how to effectively use Excel to process, analyze, and model business information. Key topics include:

• A review of formula design, data entry, cell references, cell names, formatting, and sheet linking
• Using Data Validation to control your worksheet cell entries
• Construction rules for worksheet tables and Excel tables storing business data
• How to run worksheet functions in formulas
• Performing table lookups and merging business data with worksheet functions
• Using logic worksheet functions to make decisions about your business data
• Using logic to summarize business data categories with SUMIFS, COUNTIFS...
• Importing text files and modifying worksheet text data
• Using Conditional Formatting to flag your business data based on logic
• Filtering, sorting, and grouping business table data to make reports
• Creating and formatting business charts
• Creating basic PivotTables / PivotCharts to rapidly summarize and view business data
• Linking Excel with Word and PowerPoint to quickly build business reports

Select this intermediate Excel training if you or your group have:

• Opened and saved a workbook file
• Copied and pasted worksheet data
• Performed basic cell formatting tasks like coloring a cell, bolding, aligning...
• Typed data in worksheet cells
• Built basic formulas like =A1+A2 and know how to use cell references
• Used a worksheet function like =Sum(A1:A10) in a formula or have seen it before

 A Review of Formula Design, Data Entry, Cell References, Cell Names, Formatting, and Sheet Linking

• How to create basic formulas on the worksheet that add, subtract, multiply, and divide
• Using the different cell references in your formulas and dragging and filling formulas down the worksheet
• Worksheet formatting including cell coloring, borders, fonts, and alignments
• A review of data entry, how Excel really stores data, and using number formats
• Debugging your formula errors and mapping how your formulas relate to each other
• Naming a cell or range so the name can be used in a formula instead of a cell reference like A1
• Using string concatenation for rapid formula drag and fills
• How to link sheet information in your workbook using names and cell references

Using Data Validation to Control Your Worksheet Cell Entries

• What is Data Validation and how can it be used in user interfaces (UI's)?
• Using Data Validation to control what number, text, or date a person types in a worksheet cell
• How to create a list in a worksheet cell using Data Validation
• Why use Data Validation to control worksheet table and form entries?

Construction Rules for Worksheet Tables and Excel Tables Storing Business Data

• What is the difference between a report on a worksheet and a table that stores data on a worksheet?
• How different types of tables impact your data analysis approach in Excel
• Proper header and data column design for your worksheet tables
• What is an Excel table and how to use them to manage data and enhance data processing tasks?
• How to create an Excel table and add, edit, and delete its rows
• Controlling Excel tables and using their quick summary tools to analyze data
• Formatting Excel tables including creating custom table styles

How to Run Worksheet Functions in Formulas

• What is a worksheet function, how does it work, and what is an argument list?
• Review of how to use basic functions in your formulas like SUM, COUNTA, AVERAGE, MAX, MIN, and SUBTOTAL
• How to use range references, cell references, names, constants, and Excel table structured references in your worksheet function formulas
• How to nest Microsoft Excel worksheet functions to create powerful formulas

Performing Table Lookups and Merging Business Data with Worksheet Functions

• Review of the VLOOKUP, HLOOKUP, MATCH, and INDEX functions
• Using VLOOKUP to lookup table information and merge it
• How to use MATCH and INDEX to perform table lookups that VLOOKUP cannot
• Overview of using the XLOOKUP and XMATCH worksheet functions

Using Logic Worksheet Functions to Make Decisions About Your Business Data

• What is logic and how to leverage it in your formulas to make decisions?
• The basics of determining logic triggers in your business data
• Review of building logic formulas using the relational operators: <, >, <=, >=, < >, =
• Review of building logic formulas using the IF, AND, and OR logical worksheet functions
• Using the IF function to decide what business formula to run based on logic conditions
• Using the IF function to decide what value to output based on logic conditions
• Using the IF, AND, and OR functions to determine when to output a value between start / stop points
• Creating formulas that appear and disappear when needed using the IF and IS functions, great for business forms

Using Logic to Summarize Business Data With SUMIFS, COUNTIFS...

• How to build the logic criteria for the COUNTIF, SUMIF, SUMIFS, COUNTIFS... functions
• Using the SUMIF and SUMIFS functions to summarize your worksheet data using logic
• Using the COUNTIF and COUNTIFS functions to count how many item instances you have in your worksheet data using logic
• Review of using MAXIFS, MINIFS, AVERAGEIFS... functions to determine max, min, and average values based on logic

Importing Text Files and Modifying Worksheet Text Data

• Analyzing, splitting, trimming, and cleaning worksheet text using worksheet functions like RIGHT, MID, LEFT, SUBSTITUTE, TRIM, CLEAN, SEARCH, and REPLACE
• Using the Text to Columns feature to separate delimited worksheet data into separate worksheet columns
• Using Text Wizard to import text files into Excel
• How to use the Remove Duplicates feature to retrieve unique values from a repeating table column

Using Conditional Formatting to Flag Your Business Data Based on Logic

• What is Conditional Formatting and how is it used to flag worksheet data?
• Formatting and coloring data in worksheet tables and in visual displays using the Conditional Formatting tool
• Using the Conditional Formatting tool to place graphical bars, icons... in cells

Filtering, Sorting, and Grouping Business Table Data to Make Reports

• Hiding and unhiding worksheet rows and columns using the Group feature
• Using the Sort feature to sort worksheet tables based on text, colors, icons...
• Using the Autofilter and Advanced Filter tools to build powerful table filters
• Copying and pasting filtered and sorted data to make reports, PasteSpecial reviewed
• Using Excel table filters and slicers to create report tools

Creating and Formatting Business Charts

• Creating the worksheet tables that are used for various Excel chart types
• How to create and format various pie, column, area, and line business charts
• Creating vertical limit line, horizontal limit lines, data labels, error bars... in charts
• How to use the secondary axis on a chart
• Combining two or more chart types into one chart
• How to make a chart template for rapid reporting
• How to use the worksheet gridlines to line up charts and make a simple chart report sheet

Creating Basic PivotTables / PivotCharts to Rapidly Summarize and View Business Data

• What are a PivotTable and PivotChart used for?
• Laying out a worksheet table for PivotTable use
• How to build and format a PivotTable
• Creating different summary types for your PivotTables
• How to construct, edit, and format a PivotChart
• How to filter the view of a PivotTable and PivotChart
• Creating slicers and timelines to control PivotTable and PivotChart views

Linking Excel with Word and PowerPoint to Quickly Build Business Reports

• Using Excel with Word or PowerPoint to make powerful reporting tools
• Linking cell values to Word paragraphs
• Linking ranges and charts to Word documents
• Linking ranges and charts to PowerPoint presentations