How our class can help you.
Our 1-day class shows you how to take command of Excel's data analysis tools, functions, and charts and use them to rapidly process and visualize your data.
It covers essential topics like using functions,
logic, and filters on your data, table construction, charts, and
reporting data with Pivot Tables. It also covers unique topics like how to
combine text values, parse date times, curve fit chart data, and
build array formulas to analyze complex datasets and lookups.
You'll also discover how to use Power Query and Excel tables to make adaptive reports that automatically import data, shape it, calculate it, and chart it at the click
of a refresh button.
Join us and our class will show you how to quickly combine Excel's critical data elements into tools that will save you time
and effort.
Key Excel data analysis topics covered in class.
- Data analysis formula design, cell naming, and
worksheet linking review
- Using Excel's Filters, Sort, Slicers, Analysis Toolpak, and Excel tables to rapidly
filter and process data
- Using Conditional Formatting to
automatically flag worksheet data based on logic
- Controlling worksheet data entry and
creating cell drop downs using Data Validation
- Creating charts, curve fits, and using
Slicers to control chart views
- Creating key
math, trig, date time, and statistical worksheet functions to analyze
data
- Using Excel table structured references
to create adaptive formulas
- Looking up data in worksheet
tables using the VLOOKUP, XLOOKUP, INDEX... functions
- Using logic functions like SUMIFS,
COUNTIFS, IF, AND... to summarize, count,
and analyze worksheet data
- Importing text files with Text Wizard and
analyzing worksheet text
with functions like LEFT, MID, CLEAN...
- Building array formulas to analyze complex engineering and science data patterns
- How to use PivotTables and PivotCharts to process,
summarize,
and visualize data
- How to use Power Query to import and
shape (i.e., filter, sort, delete, clean...) data automatically
- Creating dynamic reports by linking Excel to Word and PowerPoint
Click to enlarge, scroll < > if hidden.
Detailed class syllabus.
Available:
Public >,
Virtual >, Onsite >
How we run the class:
We focus our Excel class on what our customers need. When class
begins, we analyze those needs and shift our training outline appropriately. We will stress topics or add
topics that our customers want. No two classes are ever the same with EMAGENIT.
Data Analysis Problem
Construction on the Worksheet
- Using relative, mixed, and
absolute cell references along with various operators to
build analysis formulas
- How to use relative and mixed
references to drag and fill formulas in 1-d and 2-d data tables
- How Excel really stores text,
numbers, and dates in cells and how they affect functions and
formulas
- Using cell and range names in your
formulas to easily track problem inputs and data
- How to efficiently pass data between worksheets and workbooks using cell references
and names
- Building simple logic formulas to analyze data using the relational operators: <, >, <=, >=, < >, =
Building Tables, Filtering /
Sorting Data, and
Using the Analysis Toolpak
- The difference between an Excel
table and a regular worksheet table
- The 4 basic construction rules for worksheet
and Excel tables, primary and foreign key discussion
- Using Sort, AutoFilter, and
Advanced Filter in unique ways to sort and filter worksheet data
for reports
- How to use Copy / PasteSpecial to
paste select parts of filtered data to a new report worksheet
- Using the Remove Duplicates feature to retrieve unique values from a column
- Using an Excel table's quick
analysis tools and slicers to rapidly filter and calculate data
- What is in the Analysis Toolpak
and how to use its tools like moving average, regression
analysis, sampling... (optional discussion)
Using Conditional Formatting
/ Data Validation to Flag and Control Data
- Why control what a user types in a
data table or form?
- Using Data Validation to create
cell drop down lists and control number, date, and text entry in
your data
- What is Conditional Formatting and
how is it used to automatically flag worksheet data based on logic?
- Setting up a Conditional
Formatting logic rule to format worksheet data based on a cell
value or formula
- Setting up a Conditional
Formatting logic rule to place arrows, circles, bars... in cells
- How to edit and delete a
Conditional Formatting rule
Chart Design, Curve Fitting,
and Controlling Chart Views with Slicers
- Creating and professionally formatting engineering and science charts like XY Scatter, Column, Line,
Pie...
- Combining two or more chart types into one chart
(i.e., combo chart)
- Creating horizontal and vertical limit lines on your engineering and science charts
- Combining multiple worksheet
tables into a single chart, great for trade studies, varying
x-axis tables...
- Laying out multiple charts on the same worksheet to create
chart reports
- How to curve fit your chart data
- Using Excel tables and Slicers
to dynamically adjust and filter chart data
Creating and Using Key
Worksheet Functions to Analyze Data
- How to create and use basic functions like
SUM, COUNTA, AVERAGE, MAX, SUBTOTAL... in your formulas
- Using range references,
names, and structured references as arguments to your functions
- How to nest worksheet functions in
your formulas to perform powerful analysis tasks
- Using key statistical functions
like STDEV.S, SKEW, FREQ, RANK.EQ... in your formulas
- Using key math and trig functions
like ROUNDUP, ABS, TAN, RADIANS... in your formulas
- Using the TEXT, DAY, MONTH, YEAR, TODAY,
DATE,
TIME... functions to parse and create date time values
- How to use the NETWORKDAYS.INTL
and WORKDAY.INTL functions to calculate the # of workdays
between dates or from a date
Looking Up Data in Worksheet
Tables
- How to use the VLOOKUP, HLOOKUP, and
XLOOKUP functions to lookup data in a table
- Using XMATCH and MATCH to find
item position (i.e., 1, 2, 3...) within a row or column
- How to use the INDEX function to perform
advanced table lookups that return entire rows and columns
- Using the INDIRECT function, string concatenation, and names to
dynamically toggle worksheet
tables in formulas
Importing Text Files and Analyzing Worksheet Text Data
with Functions
- Using Text to Columns to parse worksheet text and Text Wizard to import text files
onto worksheets
- How to trim, substitute, and clean worksheet text using the text worksheet functions like SUBSTITUTE, TRIM, CLEAN...
- Parsing and cleaning worksheet text using
functions like RIGHT, MID, LEFT, TEXT, SEARCH...
- How to combine worksheet text
using string concatenation
Using Logic to Summarize, Count,
and Analyze Worksheet Data
- The basics of building logic in
formulas and determining logic triggers
in your worksheet data
- Using functions like SUMIF, SUMIFS,
MAXIFS, COUNTIF, COUNTIFS ... to sum, count, and calculate your data based on logic
- Using string concatenation to
create dynamic logic for functions that adjust to changing inputs
- 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 logic to look for pieces of
text (i.e., substrings) within text data
Using PivotTables to Analyze Engineering and Science Data
- Why use Excel tables to create the
data that a PivotTable will use?
- Creating, formatting, and
controlling PivotTables
- Calculating various statistics in a PivotTable
- Constructing a PivotChart from a PivotTable
- Using slicers and timelines to control
your PivotTables and PivotCharts
Using Array Formulas to Analyze Complex Engineering and Science Data Patterns
- Overview of how scientists and engineers can use array formulas to analyze complex worksheet data
- Creating Excel array formulas that process large complex data sets using the IF function
- How simple array formulas can be used to
preprocess data before being used in functions like VLOOKUP, IF, MAX, MIN,AVERAGE...
- How engineers and scientists can create Excel array formulas that perform advanced lookups and table merging using functions like MATCH, VLOOKUP, HLOOKUP, INDEX, ROW, COLUMN...
Using Power Query to Access and Shape External Engineering and Science Data
- Using Power Query to automatically
import data and shape it (i.e., filter, sort...)
- Using Power Query to link to
internal and external data in workbooks, text files and databases
- Performing various key shaping tasks in Power Query like merging and filtering data, replacing characters, splitting dates, cleaning text...
- Refreshing, editing, and updating power query
based tables and their sources
Linking Excel with Word and PowerPoint to Build
Dynamic Reports
- Using Excel with Word or PowerPoint to make powerful reporting tools
- Linking cell values, ranges, and
charts to Word documents
- Linking cell values, ranges, and
charts to PowerPoint presentations
Additional Classes:
If you need to contact us about our class.
Phone Number: 1.805.498.7162
Business Hours: 8:30 - 5:00 PM PT
You can email us at info@emagenit.com >
Contact us by form.
_EMAGENIT_2019.png)
