Intermediate Excel for Business & Industry

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

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

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

Duration, #days, and audience details about our class.

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

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 >