Intermediate Microsoft Excel Training
for Business

Our 1-day intermediate Excel training will show you how to effectively use Excel to process business data. Key topics include cell and range naming, building formulas using functions like VLOOKUP, COUNTIF, SUMIF and IF; linking and managing worksheet information; organizing and analyzing worksheet data with Excel's data tools and functions; building professional charts; building basic Pivot Tables and Pivot Charts; cleaning worksheet text and working with text files; and linking Excel to other programs like Word and PowerPoint. Also discussed are the basics of creating and organizing models on the worksheet.

Select this intermediate Excel training if you or your group:

• Have opened and saved a workbook file
• Know the basics about worksheets, cells and using cell references like A1
• Have copied and pasted worksheet data
• Have performed basic cell formatting tasks like coloring a cell, bolding, aligning...
• Have typed data in worksheet cells and built basic formulas like =A1+A2
• Have used a worksheet function like =Sum(A1:A10) in a formula or have seen it before

Intermediate Excel Review of Worksheet Formulas, Cell Naming and Sheet Linking

• A review of how to create basic formulas on the worksheet that add, subtract, multiply and divide
• How to debug your formula errors and map how your formulas relate to each other
• How to name a cell or range so the name can be used in a formula instead of a cell reference like A1
• How to manage cell and range name editing and deletion in your workbook
• How to manage and link sheet information in your workbook using names and cell references

Using Data Validation to Control Your Worksheet Data Entry

• Intermediate Excel uses of Data Validation
• How to use 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

Key Things Intermediate Excel Users Need to Know About Building Tables on the Worksheet

• The difference between a report and a table on a worksheet that stores data
• Different types of tables, how they impact your 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
• How to control Excel tables and use their quick summary tools to analyze data
• How to format Excel tables including creating custom table styles
• How to use Excel table structured references in formulas to make them adaptive

Worksheet Formatting and Form Construction

• How to use formatting, pictures and drawing shapes to give your worksheet a professional business look
• How to use drawing shapes to create different types of worksheet diagrams
• How to design and format useful worksheet forms that your associates can use to store work related data
• How to use Data Validation and worksheet protection to lock down your form entries ensuring data integrity

Key Worksheet Function Review for the Intermediate Excel User

• 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, 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

Key Lookup Worksheet Functions for the Intermediate Excel User

• Review of the VLOOKUP, HLOOKUP, MATCH and INDEX functions
• How to use VLOOKUP to lookup table information and merge it
• How to use MATCH, INDIRECT and INDEX to perform table lookups that VLOOKUP cannot

Key Logical Worksheet Functions for the Intermediate Excel User

• Logic, the key to advanced tool construction in Excel
• How to use logic in general and figure out logical triggers
• How to build logic formulas using the relational operators: <, >, <=, >=, < >, =
• How to build logic formulas using the IF, AND, OR logical worksheet functions
• How to protect your formulas from errors using the IF and the IS functions
• How to create formulas that appear and disappear when needed using the IF and IS functions

Essential Data Analysis Worksheet Functions for the Intermediate Excel User

• How to use SUMIF and SUMIFS to summarize your worksheet data
• How to use COUNTIF and COUNTIFS to count how many instances you have in your worksheet data

Must Know Text Analysis Techniques for the Intermediate Excel user

• How to analyze, split and clean worksheet text using the text worksheet functions like RIGHT, MID, LEFT, SUBSTITUTE, TRIM, CLEAN, SEARCH and REPLACE
• How to use the Text to Columns feature to separate delimited data into separate worksheet columns
• How to use Text Wizard to import text files
• How to use the Remove Duplicates feature to retrieve unique names from a column

Using the Group, Advance Filter and Sort Tools on Your Worksheet Tables

• How to hide and unhide worksheet rows using the Group feature
• How to use the Sort feature to sort tables based on text and colors
• How to use the Advanced Filter to build advanced table filters

Using Conditional Formatting to Flag Your Data

• The uses of Conditional Formatting
• How to use the Conditional Formatting feature to flag data in tables and in visual displays
• How to use the Conditional Formatting feature as a dashboard system (i.e. graphical bars, icons...)

Creating Business Charts

• How to create charts that have a professional look
• Chart formatting tips including picture placement, data labels and professional coloring
• How to combine two or more chart types into one chart

How to Create Pivot Tables and Pivot Charts on the Worksheet

• What is a Pivot Table and Pivot Chart?
• How to arrange and name worksheet data for a Pivot Table to use
• How to create Pivot Tables from worksheet data
• How to control Pivot Tables using filtering and grouping
• How to refresh and remap Pivot Table data
• How to create different Pivot Table summary types
• How to format a Pivot Table using styles and the Format Cells dialog box
• How to make slicers and timelines for Pivot Tables
• How to format and control the options for timelines and slicers
• How to construct, edit and format Pivot Charts
• How to use the filters on a Pivot Chart and arrange them
• How to use the Pivot Tables, Pivot Charts, slicers and timelines to create dashboards and reports

Linking Excel with Word and PowerPoint

• Basic linking between the programs with copying and pasting
• How to link cells, ranges and charts with Word and PowerPoint