How our class can help you.
Our 1-day class shows you the key Excel elements you need to start building adaptive analysis tools.
It covers essential topics like table construction, key
analysis formulas / functions, and report filters. Our class also covers
unique topics like using advanced logic to color cells, merging text and
date time values, and using array formulas to analyze complex datasets.
We’ll also show you how to combine Power Query, PivotTables, PivotCharts, Excel tables,
and formulas to build adaptive reports that
automatically filter, summarize, and update their data from changing
source files.
Join us and our class will show you how to combine all
these Excel elements and more to build the flexible analysis tools you need
to get the job done.
Key advanced Excel topics covered in class.
- Using relative, mixed, and absolute
references to perform various formula fills
- How to manage and use cell / range names to
rapidly build
analysis formulas and link data
- Using Excel tables, slicers, structured references,
and charts to quickly build adaptive analysis tools
- Creating worksheet UIs to control
your Excel tools using Data Validation, ActiveX Controls, and shapes
- Building adaptive formulas that react to
changing logic and data using names, structured references,
concatenation, and functions
- Using Power Query to rapidly import, merge,
and shape data from workbooks, text files, and databases
- Dynamically changing cell format and
placing bars, arrows, circles... in cells using Conditional
Formatting and logic
- Creating advanced logic for your
formulas and Conditional Formatting
- Using array formulas to analyze complex
worksheet data sets and perform multi-condition lookups
- Using slicers, timelines, PivotTables, PivotCharts,
and Excel tables to construct adaptive data reports
- How to link Excel with Word and
PowerPoint to create dynamic reports
Click to enlarge, scroll < > if hidden.
Detailed class syllabus.
Available:
Public >,
Virtual >, Onsite >
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.
Advanced Formula Design, Names, and Linking Data
- Using operators, relative, mixed, and
absolute cell references to
build analysis formulas
- Building formulas that drag and fill
horizontally and vertically using relative and mixed references
- How does Excel really store numbers,
text, and dates and how they affect your formulas
- Using range and cell names in your
formulas to easily track data
- Passing data between workbooks and
worksheets using cell references and names
- Using the relational operators: <, >,
<=, >=, < >, = to build basic logic formulas
- How to use string concatenation to
merge worksheet text data and date time values
Using Excel Tables to Quickly
Build Adaptive Analysis Tools
- Using an Excel table's quick
analysis tools and slicers to report table data
- How to create formulas in Excel
tables that reference other worksheet and Excel tables
- Using structured references to
build formulas that adapt to changing data size and arrangement
- Using structured references to
lock onto part of an Excel table, great for lookups
- Piggy backing names on top of
Excel tables so they self-adjust, alternate to structured
references
- Designing Excel tables and Slicers
to quickly control charts in a dashboard
Creating Worksheet UIs from Data
Validation, ActiveX Controls, and Shapes
- Using Data Validation to create cell
drop down lists and control number, date, and text entry in a cell
- Creating adjustable Data Validation
cell drop lists using names
- What is the difference between ActiveX controls and Form controls?
- Creating and using check box,
spinner, scroll bar.. controls to control user entry on the
worksheet
- Dynamically displaying cell values in
shapes and creating linked pictures that update
Building Adaptive Data Analysis
Formulas that Lookup, Sum, or Count
- How adaptive formulas will help you
get rid of duplicated worksheets, extra columns, extra formulas...
- Review of nesting formulas and
functions to create self-adjusting formulas
- What is string concatenation and how
is it used to quickly build self-adjusting formulas?
- Using concatenation to build names,
cell references, and logic from user selections and data
- Using concatenation and text functions to build adaptive
lookup values for VLOOKUP, XLOOKUP...
- Creating adaptive logic for SUMIFS,
COUNTIFS... from selections, data, and concatenation; great for fill
downs with changing logic
- Using INDIRECT and OFFSET to
automatically toggle between tables, columns, cells... in your
formulas and functions
Using Power Query to Rapidly Access, Merge,
and Shape Your Worksheet Data
- What is Power Query and how is it used to
import and rapidly shape (filter, parse, clean, trim, merge...)
worksheet data?
- How to refresh a Power Query table at the click of a button
and update its source
- Using Power Query to link with and process data
in a workbook, text file, and database
- Performing various Power Query shaping tasks like merging data, filtering data, replacing characters,
sorting, splitting dates, cleaning text...
- Design strategies for using Power Query to filter data through a series of tables so the final table can be used in calculations, charts,
reports...
- Performing formula calculations inside a Power Query table
Creating Advanced Logic Formulas
and Using Conditional Formatting to Flag Data
- Logic review including relational operators: <, >, <=, >=, < >, = and using the IF, AND, OR... logical worksheet functions
- Creating logical formulas using the IF and IS functions that determine if cells are blank, contain numbers, contain text, have errors...
- Using the IFS function to evaluate an
IF, ELSEIF, ELSEIF... type of logic decision
- Developing advanced logical tests for your IF functions that analyze parts of text, parts of dates, test for data existence... in worksheet data
- How to use Conditional Formatting and
logic to automatically flag worksheet data with colors, icons,
bars....
- How to create a Conditional
Formatting rule that formats a cell based on value or formula
- Modifying and deleting Conditional
Formatting rules
Using Array Formulas to Process Complex Worksheet Data Sets
- What is an array formula and how are they used to
analyze worksheet data?
- How to tell when a worksheet function
or formula can take an array and produce an array
- How simple array formulas can be used to pre-process data for use in functions like VLOOKUP, MATCH, SUM, MAX,...
- Creating array formulas that process complex logic data sets using the IF worksheet function
- Using array formulas to clean, replace, merge, and split worksheet data while simultaneously processing it
- Creating array formulas that perform
multi-condition lookups and return entire rows and columns from
tables
Creating Advanced PivotTables and PivotCharts That Summarize and View Your Data
- Creating a connection to a database, text file, Excel table... for use in a PivotTable / PivotChart
- Creating running totals, % OF, differences, ranks... in your PivotTables
- Creating formulas that reference PivotTable data including how to use
the GETPIVOTDATA function
- Creating and using relationships from Excel tables and external data sources to build PivotTables and PivotCharts
- Using slicers, timelines, PivotTables, and PivotCharts to rapidly construct dashboards
- Getting creative with PivotTables and
using them for other things besides summarizing data
Linking Excel with Word / PowerPoint to Quickly Build Reports
- Using Excel with Word or PowerPoint to make
dynamic reporting tools
- Linking cell values, ranges, and
charts to a Word document
- Linking cell values, ranges, and
charts to a PowerPoint presentation
- How to save your linked files, open
them, and break those links when emailing
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)
