Intermediate Excel for Business and Industry Class

How our class will boost your Excel skills.

Our 1-day class shows you how to start harnessing Excel’s calculation, data processing, and reporting abilities. It covers essential topics like building key formulas, linking worksheets and workbooks, and organizing data in Excel tables for quick analysis and use.

In addition, our training shows how to use worksheet functions to perform table lookups; sum and count data; parse dates and text; and make logic decisions in your formulas.

Also covered is how to use various table filters; build industry charts; and create powerful PivotTable reports.

Read our detailed outline > below and see what you'll discover in our class.

What you'll learn in our class.

Learn to layout problems; use cell reference notation; type and nest functions; control cell input; and link sheet data.

Learn to create Excel tables to automate reports and use the IF, AND, OR... functions to make decisions in formulas.

Learn to use Excel tables, filters, sorting, grouping, and slicers to rapidly visualize, calculate, and report data.

Learn to look up and merge data with VLOOKUP, XLOOKUP... and use Conditional Formatting to flag data based on criteria.

Learn to use functions like INDEX and MATCH to return parts of tables and use names to rapidly access data on other sheets.

Learn to calculate data using logic (SUMIFS, MAXIFS...) and use concatenation (&) to build logic criteria that adapts when filled.

Learn to import text; merge text with concatenation, analyze and clean text; and parse and calculate dates.

Learn to build key chart types, use formatting to enhance chart data, and organize charts, ranges, and formulas for reports.

Learn to rapidly organize, summarize, and visualize worksheet data using PivotTables and Pivot Charts.

Problem layout; building formulas; cell names; and linking workbooks / worksheets
Building and using worksheet and Excel tables to store and analyze data
Using Data Validation to control worksheet data entry and to create cell lists
Making decisions in your formulas using the IF, And... functions and <,>,=... operators
Filtering, sorting, and grouping worksheet data to make reports
Using Conditional Formatting to automatically color data based on logic
Looking up and merging worksheet data using the lookup functions
Using logic to summarize and count worksheet data
Analyzing date and times on the worksheet using the date / time functions
Importing a text file to the worksheet using Text Wizard and Text to Columns
How to parse, clean, and combine text using the text functions and concatenation
Creating and formatting industry charts and combining charts and ranges to make reports
Building basic PivotTables / Pivot Charts to rapidly summarize data

Some key topics we cover in class.

4 ways to train.

Virtual

View the dates, signup, and pricing for our hands-on live virtual training.
Virtual Signup > Learn More >

In-Person

View the dates, signup, and pricing for our hands-on in-person training.
In-Person Signup > Learn More >

Manual

Can't take the class, buy the self-study manual, apply the price later.
View Manual >

Onsite

Learn how we can help your business save time and effort in Excel.
View Onsite Details >

Class syllabus.

How we run the class: We focus our training on what our customers need. When training begins, we analyze those needs and shift our training outline appropriately. We will stress topics or add topics that our customers want. No two training sessions are ever the same with EMAGENIT.

Download Class Brochure

Problem Layout, Formulas, Using Functions, and Linking

Organizing your problem on the worksheet for easy user entry and formula construction
Review of the different types of cell references and operators used in formula construction
Review of using basic functions (i.e., SUM, MAX...) and nesting functions in formulas
How to create names to track cells and ranges in formulas and other Excel features
How to quickly link to sheets and other workbooks in formulas
Using Data Validation to control cell inputs and create cell drop down lists

How to Make Logic Decisions in Your Formulas

What is logic and how to leverage it in your formulas to make decisions?
The basics of determining logic triggers (criteria) in your worksheet data
How to use the relational operators: <, >, <=, >=, < >, = to make decisions in formulas
How to use the IF, AND, IS, OR... logical functions to make decisions
Using worksheet functions to build logic decisions for the IF function
Using IF and logic criteria to decide what formula to run or value to output

Reporting Data with Excel Tables, Filters, and Conditional Formatting

How to create a worksheet table and Excel table to store your data
Hiding and unhiding worksheet rows and columns using the Group feature
Using Autofilter, Advanced Filter, Sort, and Remove Duplicates to filter worksheet data
Controlling an Excel table's formats, filters, analysis tools, and slicers
Copying and pasting parts of filtered worksheet data with PasteSpecial
Using Conditional Formatting to automatically color and format worksheet data

Performing Table Look Ups on the Worksheet

Rules for designing a worksheet table that can be used by the lookup functions
Using VLOOKUP, HLOOKUP, and XLOOKUP to lookup table information
Using XMATCH and MATCH to find item position (i.e., 1, 2, 3...) within a row or column
How to use INDEX to perform table lookups that XLOOKUP and VLOOKUP cannot
Returning rows, columns, and parts of a table using INDEX and spill over arrays

Calculating Worksheet Data Using Logic

How to make the logic criteria for the SUMIFS, COUNTIFS... functions
Using SUMIF, SUMIFS to summarize your worksheet data based on logic
Using COUNTIF, COUNTIFS to count how many instances of an item are in your data based on logic
Using COUNTIF to determine if worksheet tables have matching content
How to use MAXIFS, MINIFS, AVERAGEIFS... to determine max, min, and average values based on logic
Using string concatenation to create adaptive criteria for SUMIFS, COUNTIFS...

Analyzing Dates and Times in Your Worksheet Data

Extracting the month and day name from your date values using the TEXT function
Using the DAY, MONTH, YEAR, DATE, TIME... functions to break down dates and times
Using the TODAY, NOW, EOMONTH.. functions to generate various date times
How to use the date / time functions to calculate the # of workdays, start of the week, days left...
Assembling valid date time values from multiple columns of data using concatenation

Importing, Cleaning, and Analyzing Text Data

Using Text Wizard to import a text file onto a worksheet
Using Text to Columns to separate delimited worksheet data into separate columns
Parsing, trimming, and cleaning worksheet text using functions like LEFT, MID, TRIM, CLEAN...
How to use string concatenation to merge worksheet text data

Creating and Formatting Industry Charts

Creating the worksheet tables that are used for various Excel chart types
How to create and format various chart types including combo charts, pie, column, line...
Creating limit lines, gridlines, titles, data labels... in charts
How to create a secondary chart axis
How to make a chart template for rapid reporting
Blending charts, ranges, and formulas to make a chart report

Creating Basic PivotTables / Pivot Charts to Rapidly Summarize Data

What is a PivotTable / Pivot Chart used for?
Laying out a worksheet table for PivotTable use
How to build, format, sum, subtotal, total... a PivotTable
How to construct, edit, and format a Pivot Chart
How to filter the view of a PivotTable / Pivot Chart using filters, slicers, and timelines

Additional Class Information

Prerequisites for our Excel training.

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

Class time, duration, manual, and repeats.

Audience: Business, government, industry, military, engineering, science, techs...
Duration: 9:00 - 5:00 CT
# of Days: 1-day
Instruction Type: Live, hands-on
Class Manual: Our complete Excel manual for business and industry (TCEM). Details >
Excel Versions: 2013-Present, Microsoft Excel 365, Windows and Mac
Repeats: Repeat your Excel training. Details >

If you need to contact us about our class.

Phone Number: 1.805.498.7162

Business Hours: Mon-Fri 8:00 AM - 7:00 PM CT

You can email us at info@emagenit.com >

Form >

Intermediate Excel for Business and Industry - Class