Intermediate Excel Class for Business and Industry

Next Class: 12/06/2022

More Dates >

See the must know topics you will learn.

No time to attend, get a manual instead.

Covers the Excel topics, strategies, and methods needed to solve your Excel workplace tasks quickly.

Our manual is a combination of beginning, intermediate, and advanced Excel topics and also covers hi-tech dashboard design.
Covers everything from typing data to using functions and formulas to automated reporting using Power Query, Excel tables, and Pivot Tables.
Also discusses must know topics like problem organization; using names to pass data efficiently; and data processing.
100's of real world examples included. Learn More >
Buy a manual now, apply the price towards a class later. Manuals are included with our classes.

Key Excel topics covered in class.

Laying out your problems, entering data, and building formulas on the worksheet
Building and using worksheet and Excel tables to store data
Using Data Validation to control worksheet data entry in problems, forms, and tables
Using Conditional Formatting to automatically control cell formatting based on logic
Filtering, sorting, and grouping worksheet data to make reports
Looking up and merging worksheet data using the lookup functions
Making decisions in your formulas using the logic operators and functions
Analyzing date time worksheet data using the date functions
Importing text file data to the worksheet using Text Wizard
How to parse, clean, and combine worksheet text using the text functions and string concatenation
Creating and formatting industry grade charts
Building basic PivotTables / Pivot Charts to rapidly summarize data

Class examples.

Pricing and class options.

Online Virtual Learning

Live, hands-on, talk to the instructor training
Bring your own problems to class
Online Details >

Dates / Signup >

Public In-Person Learning

Live hands-on training
Bring your own problems to class
We can supply a computer

Dates / Signup >

Onsite Hands-On Learning

We come to you and solve your problems
We cover in person in the USA and Canada
We cover the world virtually using your time zone

Details >

Detailed 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.

Laying Out Problems, Entering Data, and Building Formulas on the Worksheet

Organizing your problem on the worksheet for easy user entry and formula construction
Formatting your worksheet problem including cell coloring, borders, fonts, and alignments
Review of worksheet data entry, number formats, and how Excel really stores data
Using operators, cells, ranges, and basic functions (i.e., SUM, MAX, SUBTOTAL...) to build formulas
How to build and use cell names to easily track cell information in formulas and other Excel features
Using sheet references and cell names in your formulas to track information on multiple worksheets

Building Worksheet and Excel Tables to Store Data

What is the difference between an Excel table and a regular worksheet table?
Design rules for laying out your worksheet and Excel tables
How to create an Excel table and control its formats, filters, rows, columns...
When to use an Excel table vs. a worksheet table to store data, adaptive reports
Using structured references to build functions that adapt to changing data
Using an Excel table's quick analysis tools and slicers to report data

Using Data Validation and Conditional Formatting to Control and Flag Data

What is Data Validation and how is it used to control worksheet data entry in problems, forms, and tables?
Using Data Validation to control what number, text, or date a user types in a worksheet cell
How to use Data Validation to create a worksheet cell drop down list
What is Conditional Formatting and how is it used to flag worksheet data based on logic?
Setting up a Conditional Formatting logic rule to automatically format worksheet data based on a criteria
How to edit and delete a Conditional Formatting logic rule

Filtering, Sorting, and Grouping Worksheet Data to Make Reports

Hiding and unhiding worksheet rows and columns using the Group feature
Using the Sort feature to sort worksheet data based on text, colors, icons...
Using the Autofilter and Advanced Filter tools to build powerful report filters for worksheet data
Copying and pasting parts of filtered and sorted worksheet data to report worksheets, PasteSpecial reviewed
Using Excel table filters, slicers, and quick summary tools to rapidly report worksheet data

Performing Table Lookups and Merging Worksheet Data

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

Using Logic to Make Decisions in Your Formulas

What is logic and how to leverage it in your formulas to make decisions?
The basics of determining logic triggers in your worksheet data
Review of building logic formulas using the relational operators: <, >, <=, >=, < >, =
Review of building logic formulas using the IF, AND, OR, NOT, and IS logical functions
Using IF to decide what formula to run or what value to output based on logic criteria
Using IF and IS to create formulas that disappear or return a value when an input is missing, or an error occurs

Using Logic to Summarize and Count Worksheet Data

How to build the logic criteria for the SUMIF, SUMIFS, COUNTIFS, MAXIFs... 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

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 and create date time values
Using the TODAY, NOW, and EOMONTH functions to generate various date times including end of month date
How to use the NETWORKDAYS.INTL and WORKDAY.INTL functions to calculate the # of workdays between dates or from a date
Assembling valid date time values from multiple columns of data

Importing, Parsing, and Cleaning Text Data on the Worksheet

Using Text Wizard to import downloaded text files onto a worksheet
Using Text to Columns to separate delimited worksheet data into separate worksheet columns
How to use Remove Duplicates to retrieve unique values from repeating worksheet data
Parsing, trimming, and cleaning worksheet text using functions like LEFT, MID, SUBSTITUTE, TRIM, CLEAN...
How to use string concatenation to merge worksheet text data

Creating and Formatting Industry Grade 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 vertical limit lines, horizontal limit lines, gridlines, titles, data labels... in charts
How to create a secondary chart axis
How to make a chart template for rapid reporting
Laying out charts on worksheets to make a chart report

Creating Basic PivotTables / Pivot Charts to Rapidly Summarize Data

What are a PivotTable and 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

Excel skills needed for our class.

Select this 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

The class details.

Audience: Business, government, industry, military, engineering, science, techs... general audience
Duration: 9:00 - 5:00 CT
# of Days: 1-day
Instruction Type: Hands-on, live, instructor taught training
Full Excel Manual Included: Our 2200 + page manual full of Excel tips, how-to-do topics and Excel examples included with the training. Details >
Excel Versions: 2010-2019, Microsoft 365, Windows and Mac
Repeats: Repeat your Excel training. Details >
Personal Examples: We review our attendees personal examples in our training instruction
Available Training Formats:

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 >

Contact us by form.

Beginning Excel for Business / Industry	Intermediate Excel for Business / Industry	Advanced Excel for Business / Industry	Excel Dashboard Design for Business / Industry	Beginning Excel VBA for Business / Industry	Excel VBA Data Processing for Business / Industry	Advanced Excel VBA for Business / Industry