Overview

Courses Benefits

Microsoft Excel has many time saving tools that will help you become even more productive. This Microsoft Excel Advanced training course covers a wide range of topics that will help you take full advantage of Excel’s powerful features. You will learn how to use many of Microsoft Excel’s features such as advanced functions, named ranges, conditional formatting, and how to visualize data using charts.

In this Microsoft Excel Advanced training course, you will learn how to maximize Excel even further to help you analyze data quicker. You will learn features like PivotTables, macros, and how to protect your spreadsheets from being misused by others.

You Will Learn How To

  • Make formulas easier to understand using named ranges
  • Use functions such as XLOOKUP, string and date functions
  • See different “What-if” scenarios using the Scenario Manager
  • Group data and see subtotals
  • Create more engaging reports by adding images
  • Visualize data using charts
  • Take advantage of Excel Tables
  • Apply different styles and conditional formatting
  • Resolve formula errors
  • Manage data such as removing duplicates, validating and transposing data
  • Answer “What-if” scenarios using goal seek
  • Work with PivotTables
  • Create basic dashboards to visualize data
  • Use Macros to automate repetitive tasks
  • Protect your spreadsheet from misuse by others

Who Should Attend

Anyone who wants to use Microsoft Excel to create spreadsheets and automate data processing. Basic Excel knowledge at the level of Microsoft Excel Essentials is expected for this Microsoft excel training.

Course Outline

Advanced Formula Tasks

  • Understanding Relative and Absolute Cell References
  • Using Multiple Cell References
  • Using 3D References

Working with Named Ranges

  • Understanding Named Ranges
  • Defining Named Ranges
  • Editing Named Ranges
  • Deleting Named Ranges
  • Using Named Ranges in Formulas

Using Advanced Functions

  • Using PROPER, UPPER, and LOWER Functions
  • Using the TRIM Function
  • Using LEN and SUBSTITUTE Functions
  • Using LEFT, RIGHT, and MID Functions
  • Using the CONCAT Function
  • Using TODAY, NOW, DATE, and TIME Functions
  • Using YEAR, MONTH, DAY Functions
  • Using VLOOKUP, HLOOKUP, XLOOKUP Functions
  • Using SUMIF, COUNTIF, and AVERAGEIF Functions
  • Using IF, AND, OR Functions
  • Using PMT Function

Outlining and Grouping Data

  • Outlining Data
  • Showing and Hiding Outline Details
  • Grouping Data
  • Creating Subtotals
  • Removing Outlining and Grouping

Working with Scenarios

  • Creating Scenarios
  • Loading Scenarios
  • Merging Scenarios
  • Editing Scenarios
  • Creating a Scenario Summary Report
  • Deleting Scenarios

Working with Images

  • Inserting Smart Art
  • Inserting Text Boxes
  • Inserting WordArt
  • Inserting Pictures
  • Inserting Icons
  • Drawing Shapes

Visualizing Your Data

  • Inserting a Chart
  • Choosing a Chart Type
  • Changing Colors
  • Adding Axis Titles
  • Adding Chart Title
  • Adding Data Labels
  • Adding Gridlines
  • Adding a Legend
  • Using Quick Layout
  • Switching Rows and Columns
  • Using Chart Options

Working with Tables

  • Why Use Tables?
  • Converting a Range to a Table
  • Modifying Tables
  • Defining Titles
  • Converting a Table to a Range

Advanced Formatting

  • Using Styles
  • Creating and Modifying Templates
  • Using Conditional Formatting

Auditing Worksheets

  • Tracing Formula Precedents and Dependents
  • Showing Formulas
  • Evaluating Formulas
  • Setting Error Checking Options
  • Using Error Option Buttons
  • Running an Error Check

Data Tools

  • Consolidating Information
  • Removing Duplicates
  • Configuring Data Validation
  • Transposing Data
  • Converting Text to Columns
  • Using Goal Seek

PivotTable Basics

  • What is a PivotTable?
  • Creating a PivotTable
  • Using the PivotTable Fields Pane
  • Using PivotTable Classic View
  • Viewing Details of Summarized Data
  • Sorting Pivoted Data
  • Filtering Pivoted Data

Advanced PivotTable Features

  • Using Natural Language to Create PivotTables
  • Changing Field Layout and Sorting
  • Adding Calculated Fields
  • Showing Values in Different Ways
  • Customizing Column Names
  • Changing Order of Rows
  • Changing Order of Columns
  • Grouping Data
  • Changing the PivotTable Layout
  • Creating PivotTables from Data in Multiple Worksheets

Creating Basic Dashboards

  • Creating a Basic PivotChart
  • Using Slicers
  • Using Sparklines
  • Creating Timelines
  • Using the Camera Tool

Using Macros

  • Recording a Macro
  • Editing a Macro
  • Running a Macro

NOTE: Macro programming is not covered

Working with Others

  • Co-authoring
  • Sharing a Workbook
  • Protecting a Worksheet
  • Protecting Cells
  • Protecting a Workbook