In Person / Live Online
Next Date: Click Dates|Signup
All Dates | Signup
*Our live, hands-on, instructor led Excel online training is running as scheduled above. Public
in person training resumes on 8/1.
Supercharge your formula and data analysis skills in
Excel.
Our intermediate Excel training shows you the skills that you need to start analyzing, processing and modeling business information using a whole host of Excel tools,
functions and formulas.
Questions? 1.805.498.7162 Customers >
Take a tour of our intermediate Microsoft Excel training.
Our 1-day intermediate Excel
training will show you how to effectively use Excel to process
business data. Key topics include cell and range
naming, building formulas using functions like VLOOKUP, COUNTIF, SUMIF and IF; linking and managing
worksheet information; organizing and analyzing worksheet
data with Excel's data tools and functions; building
professional charts; building basic Pivot Tables and
Pivot Charts; cleaning worksheet text and working with text files; and linking Excel to other programs like
Word and PowerPoint. Also discussed are the basics of creating and
organizing models on the worksheet.
The
skills you need for our intermediate training.
Select this intermediate Excel training if you or your group:
- Have opened and saved a
workbook file
- Know the basics about
worksheets, cells and using cell references like A1
- Have copied and pasted
worksheet data
- Have performed basic cell
formatting tasks like coloring a cell, bolding, aligning...
- Have typed data in worksheet
cells and built basic formulas like =A1+A2
- Have used a worksheet
function like =Sum(A1:A10) in a formula or have seen it
before
Our intermediate Microsoft Excel
training syllabus.
Intermediate Excel Review of Worksheet Formulas,
Cell Naming and Sheet Linking
- A review of how to
create basic formulas on the worksheet that add,
subtract, multiply and divide
- How to debug your
formula errors and map how your formulas relate to each
other
- How to name a cell or
range so the name can be used in a formula instead of a
cell reference like A1
- How to manage cell and
range name editing and deletion in your workbook
- How to manage and link
sheet information in your workbook using names and cell
references
Using Data Validation
to Control Your Worksheet Data Entry
- Intermediate Excel uses of Data
Validation
- How to use Data
Validation to control what number, text or date a person
types in a worksheet cell
- How to create a list in
a worksheet cell using Data Validation
Key Things Intermediate Excel Users
Need to Know About Building Tables on the Worksheet
- The difference between a
report and a table on a worksheet that stores data
- Different types of
tables, how they impact your analysis approach in Excel
- Proper header and data
column design for your worksheet tables
- What is an Excel table and how
to use them to manage data and enhance data processing tasks
- How to create an Excel table and
add, edit and delete its rows
- How to control Excel tables and
use their quick summary tools to analyze data
- How to format Excel tables
including creating custom table styles
- How to use Excel table structured
references in formulas to make them adaptive
Worksheet Formatting and Form
Construction
- How to use formatting, pictures and drawing shapes to give your worksheet a professional business look
- How to use drawing shapes to create different types of
worksheet diagrams
- How to design and format useful
worksheet forms that your associates can use to store work
related data
- How to use Data Validation and worksheet protection to lock down your form entries
ensuring data integrity
Key Worksheet Function Review for
the Intermediate Excel User
- What is a worksheet
function, how does it work and what is an argument list
- Review of how to use basic
functions in your formulas like SUM, COUNTA, AVERAGE,
MAX, MIN, SUBTOTAL
- How to use range references, cell
references, names, constants and Excel table structured
references in your worksheet function formulas
- How to nest Microsoft
Excel worksheet functions to create powerful formulas
Key Lookup Worksheet Functions for the
Intermediate Excel User
- Review of the VLOOKUP,
HLOOKUP, MATCH and INDEX functions
- How to use VLOOKUP to
lookup table information and merge it
- How to use MATCH,
INDIRECT and INDEX to perform table lookups that VLOOKUP
cannot
Key Logical Worksheet Functions for the
Intermediate Excel User
- Logic, the key to
advanced tool construction in Excel
- How to use logic in
general and figure out logical triggers
- How to build logic
formulas using the relational operators: <, >, <=, >=, <
>, =
- How to build logic
formulas using the IF, AND, OR logical worksheet
functions
- How to protect your
formulas from errors using the IF and the IS functions
- How to create formulas
that appear and disappear when needed using the IF and
IS functions
Essential Data Analysis Worksheet Functions for the
Intermediate Excel User
- How to use SUMIF and
SUMIFS to summarize your worksheet data
- How to use COUNTIF and
COUNTIFS to count how many instances you have in your
worksheet data
Must Know Text Analysis Techniques
for the Intermediate Excel user
- How to analyze, split
and clean worksheet text using the text worksheet
functions like RIGHT, MID, LEFT, SUBSTITUTE, TRIM,
CLEAN, SEARCH and REPLACE
- How to use the Text to
Columns feature to separate delimited data into separate
worksheet columns
- How to use Text Wizard to
import text files
- How to use the Remove
Duplicates feature to retrieve unique names from a
column
Using the Group, Advance Filter and
Sort Tools on Your Worksheet Tables
- How to hide and unhide
worksheet rows using the Group feature
- How to use the Sort
feature to sort tables based on text and colors
- How to use the Advanced Filter to build advanced table filters
Using Conditional
Formatting to Flag Your Data
- The uses of Conditional Formatting
- How to use the Conditional
Formatting feature to flag data in tables and in visual
displays
- How to use the
Conditional Formatting feature as a dashboard system
(i.e. graphical bars, icons...)
Creating Business Charts
- How to create charts
that have a professional look
- Chart formatting tips
including picture placement, data labels and
professional coloring
- How to combine two or
more chart types into one chart
How to Create Pivot Tables and Pivot Charts on the Worksheet
- What is a Pivot Table and Pivot Chart?
- How to arrange and name
worksheet data for a
Pivot Table to use
- How to create Pivot Tables
from worksheet data
- How to control Pivot Tables
using filtering and grouping
- How to refresh and remap
Pivot Table data
- How to create different
Pivot Table summary types
- How to format a Pivot Table
using styles and the Format Cells dialog box
- How to make slicers and
timelines for Pivot Tables
- How to format and control
the options for timelines and slicers
- How to construct, edit and
format Pivot Charts
- How to use the filters on a
Pivot Chart and arrange them
- How to use the Pivot Tables,
Pivot Charts, slicers and timelines to create dashboards and
reports
Linking Excel with Word and PowerPoint
- Basic linking between
the programs with copying and pasting
- How to link cells,
ranges and charts with Word and PowerPoint
_EMAGENIT_2019.jpg)
