Intermediate Microsoft Excel for Business and Industry

Class overview.

What makes our training different?

• Live, Hands-On, Industry topics
• Bring your problems
• Limited class size
• Industry Topics

Our 1-day class  shows you how to start harnessing Excel’s powerful calculation, data processing, and reporting abilities. It covers the must know Excel skills like how to build worksheet tables; filter data for reports; control what you type in a cell; report data with PivotTables; and make professionally formatted charts.

Our class also covers important formula / function topics like how to summarize and count data; perform table lookups; use logic to make formula decisions; and parse dates / text. We'll also demonstrate how to layout your problems on the worksheet.

Join us and our class will show you how to use Excel in new ways that will allow you to complete your work tasks rapidly.

Some key topics we cover 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

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:   2010-2019, Microsoft 365, Windows and Mac
• Repeats:   Repeat your Excel training. Details >

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

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 >