This course is the second in the Excel series and is designed to help users display data in graphs and chart, use Headers and Footers and change the format/orientation/alignment on the page for printing, create and format tables with calculations, use Auto and Advanced Filters such as VLOOKUP, AVG, MAX, MIN and find records in a worksheet. The course includes creating PivotTables with calculated fields, using What-If tables, Scenarios and Problem-Solving.
This one-day intermediate Excel course is the second class in our Microsoft Excel series and runs live with an instructor from 8:30 a.m. to 4:15 p.m. You start with pictures, shapes and SmartArt, then build, modify and format charts, including combination and pie charts. The class covers page setup and printing, creating and formatting Excel tables, and sorting and filtering data with AutoFilter and Advanced Filter. You also work with database functions such as DSUM and DAVERAGE and use VLOOKUP with exact and approximate matches.
Later in the class you create PivotTables with calculated fields, slicers and PivotCharts to summarize large amounts of data. You also build one- and two-variable data tables, set up scenarios, and use Goal Seek and the Solver add-in to test business decisions. This intermediate Excel training suits people who are comfortable with the basics from Level I Foundations and now need to analyze and present data with more confidence.
The course fee is $345 for 8 hours of live, instructor-led training. After this class, Level III Advanced Topics covers data validation, linking workbooks, logical functions and macros. Organizations can also book this Level II class as private training for a team, and the form on this page lets you ask about upcoming dates or a group session.
Enhance Worksheets with Visual Elements
Insert and format pictures, shapes, and SmartArt to visually represent information.
Apply artistic and picture effects to create professional-quality visuals.
Organize and manage objects using the Selection Pane.
Create and Customize Charts
Construct various chart types to visually represent data.
Modify chart layouts, styles, labels, and axes to improve clarity and visual impact.
Format chart elements using WordArt, color schemes, 3-D rotation, and combination chart types.
Manage Page Layout and Printing Options
Set up page breaks, margins, orientation, scaling, and headers/footers.
Customize print areas and preview content for efficient printing of sheets, selected ranges, and charts.
Create and Use Excel Tables
Design, format, and manage Excel tables for structured data.
Use table styles, perform calculations within tables, and convert tables to ranges.
Sort and filter table data using built-in and customized criteria.
Apply Filters and Database Functions
Use AutoFilter and Advanced Filter tools to extract and analyze specific data sets.
Utilize database functions (e.g., DSUM, DAVERAGE, DMAX, DMIN, DCOUNTA) for targeted calculations.
Apply VLOOKUP functions for data retrieval with exact and approximate match settings.
Build and Analyze PivotTables
Create, update, and customize PivotTables for interactive data analysis.
Add calculated fields, slicers, and filters to refine PivotTable reports.
Generate and modify PivotCharts from PivotTable data.
Perform What-If Analysis and Scenario Management
Construct one-variable and two-variable data tables to explore outcomes.
Develop and apply scenarios to model different business cases or assumptions.
Utilize Excel’s Problem-Solving Tools
Apply the Goal Seek tool to find desired outcomes.
Activate and use the Solver add-in to optimize complex decision models.
Course Outline:
Working With Illustrations and Charts
How To Insert a Picture And Apply a Style To It
How To Apply An Artistic Effect To a Picture
How To Apply a Picture Effect
How To Insert Shapes And Apply Styles
How To Rearrange, Show/Hide Objects Using The Selection Pane
How To Insert A SmartArt
How To Use The SmartArt Text Pane
How To Change The Shape In A SmartArt
Creating A Chart
How To Insert a Chart
How To Move a Chart
How To Resize a Chart
How To Change The Chart Type
How To Add, Edit or Remove a Chart Data Source
How To Swap Labels In The Legend With The Horizontal Axis
How To Change The Order Of a Data Series
How To Move An Existing Chart To a New Worksheet
Modifying A Chart
How To Change A Chart Layout And Style
How To Change The Chart Title
How To Change The Axis Titles
How To Change The Legend
How To Insert Data Labels
How To Add a Data Table
How To Change The Axes
How To Change The Gridlines
How To Insert a New Chart Element
How To Delete a Chart
Formatting a Chart
How To Select a Chart Element
How To Format The Chart Elements
How To Format Text In a Chart Using WordArt
How To Change The Text Orientation
How To Change The Color Of a Chart Element
How To Create a Combination Of Chart Types
How To Create a Pie Chart
How To Set The 3-D Rotation
How To Explode a Wedge In A Pie Chart
How To Create a Picture Chart
Setting Up a Page and Printing
How To Insert And Remove a Page Break
How To Set And Clear The Print Area
How To Change The Margin In The Page Layout View
How To Change The Page Orientation
How To Change The Scaling
How To Change The Paper Size
How To Change The Page Margin
How To Align To The Center Of The Page
How To Add A Header / Footer
How To Add An Image As a Watermark
Previewing and Printing
How To Preview The Print
How To Print Active Sheets
How To Print Selected Cells
How To Print A Chart
How To Print Multiple Copies
Creating Tables
How To Create a Table
How To Change The Style Of a Table
How To Create A New Table Style
How To Show Or Hide Table Elements
How To Create A Calculation In a Table
How To Insert And Delete A Table Row
How To Resize a Table
How To Convert A Table To a Range
How To Sort The Data In a Table
How To Perform Multiple Sort
How To Customize The Sort Order
Using The AutoFilter
How To Enable / Disable The AutoFilter
How To Apply / Remove The Filter
How To Customize The Filter
Using The Advanced Filter
How To Define The Criteria Range
How To Use The Advanced Filter
Using Simple Database Functions
How To Find The Sum Of Specific Records Using The Insert Function Button
How To Find The Average Of Specific Records
How To Find The Maximum / Minimum Value Of Specific Records
How To Find The Number Of Records
Using The VLOOKUP Function
How To Use VLOOKUP To Find Data
How To Limit The Lookup Value For An Exact Match
How To Use The Closest Match For The Lookup Value
Creating a PivotTable
How To Create a PivotTable
Updating a PivotTable
How To Update The Information In a PivotTable
How To Change The Layout Of a PivotTable
How To Add An Information Field To a PivotTable
How To Arrange Information In a PivotTable
How To Filter The Report In a PivotTable
How To Filter Specific Records
Working With Calculated Fields In A PivotTable
How To Change The Calculated Field Value Settings
How To Insert a Calculated Field
How To Delete Calculated Fields
How To Show Different Values In A PivotTable
How To Filter a PivotTable With Slicers
How To Format And Delete A Slicer
How To Change a PivotTable Options
Creating A Chart From A PivotTable
How To Create a Chart From a PivotTable
How To Modify a PivotChart
Exploring What-If Tables
How To Create a One Variable What-If Table
How To Create a Two Variables What-If Table
Using Scenarios
How To Create Scenarios
How To Apply a Scenario
Implementing Problem Solving
How To Use The Goal Seek Tool
How To Load Add-Ins
How To Use The Solver Tools
Cancellation Policy: 5 working days for full refund. Cancellations after that time are charged full tuition for the course.
For more information regarding refund, concerns, and/or program cancellation policies please contact our offices at 919-878-7100 ext. 22
