Duration: 1 day – 7 hr.
Overview
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.
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.
To gain a truly competitive edge, you need to be able to extract actionable organizational intelligence from your raw data. In other words, when you have questions about your data, you need to know how to get Excel to provide the answers for you. And that’s exactly what this course aims to help you do. The ability to analyze massive amounts of data, extract actionable information from it, and present that information to decision makers is at the foundation of a successful organization that can compete at a high level.
Objectives
- 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
Audience
- 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.
Pre- requisites
- No Pre-requisites anyone can attend this course
Course Content
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
