Overview

Course Benefits

Microsoft Excel has many time saving tools that will help you become even more productive. This Microsoft Excel Intermediate Training course covers a wide range of topics that will help you take full advantage of Excel’s powerful features. In this Microsoft Excel Intermediate Training course, 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.

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

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 Basic is expected.

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