Learning Objectives

In this course, you will analyze and visualize data using Microsoft Excel and associated tools. You will:

Perform data analysis fundamentals.

Visualize data with Excel.

Analyze data with formulas and functions.

Analyze data with PivotTables.

Present visual insights with dashboards in Excel.

————————————————

Lesson 1: Data Analysis Fundamentals

Topic A: Introduction to Data Science

Topic B: Create and Modify Tables

Topic C: Sort and Filter Data

Lesson 2: Visualizing Data with Excel

Topic A: Visualize Data with Charts

Topic B: Modify and Format Charts

Topic C: Apply Best Practices in Chart Design

Lesson 3: Analyzing Data with Formulas and Functions

Topic A: Analyze Data with Formulas and Named Ranges

Topic B: Analyze Data with Functions

Topic C: Implement Data Validation, Forms, and Controls

Topic D: Create Conditional Visualizations with Lookup Functions

Lesson 4: Analyzing Data with PivotTables

Topic A: Create a PivotTable

Topic B: Analyze PivotTable Data

Lesson 5: Presenting Visual Insights with Dashboards in Excel

Topic A: Visualize Data with PivotCharts

Topic B: Filter Data Using Slicers and Timelines

Topic C: Create a Dashboard in Excel

Length: 1 day

Certificate of Completion

Live Instructor Training

Register Today

Course Overview

Advances in technology have made it possible to store ever-increasing amounts of data. Along with this, the need to analyze data and gain actionable insight is greater than ever. You already have experience working with Microsoft Excel and creating basic PivotTables to summarize data. But Excel is capable of doing much more. Being able to harness the power of advanced PivotTable features and create PivotCharts will help you to gain a competitive edge. You will not only be able to summarize data for you to analyze, but also organize the data in a way that can be meaningfully presented to others. This leads to data-driven business decisions that have a better chance for success for everyone involved.

Learning Objectives

In this course, you will use the advanced PivotTable functionality in Excel to analyze your raw data. You will:

Prepare data for PivotTable reporting and create PivotTables from various data sources.

Analyze data by using PivotTables.

Work with PivotCharts.

Lesson 1: Preparing Data and Creating PivotTables

Topic A: Prepare Data

Topic B: Create a PivotTable from a Local Data Source

Topic C: Create a PivotTable from Multiple Local Data Sources

Topic D: Create a PivotTable from an External Data Source

Lesson 2: Analyzing Data by Using PivotTables

Topic A: Summarize PivotTable Data

Topic B: Organize PivotTable Data

Topic C: Filter PivotTable Data

Topic D: Format a PivotTable

Topic E: Refresh and Change PivotTable Data

Lesson 3: Working with PivotCharts

Topic A: Create a PivotChart

Topic B: Manipulate PivotChart Data

Topic C: Format a PivotChart

Length: 0.5 day

Certificate of Completion

Live Instructor Training

Register Today

Chapter 1: Set up a workbook

Create workbooks

Modify workbooks

Modify worksheets

Merge and unmerge cells

Customize the Excel app window

• Manage the ribbon

• Manage the Quick Access Toolbar

• Customize the status bar

• Change the magnification level of a worksheet

• Arrange multiple workbook windows

Skills review

Practice tasks

Chapter 2: Work with data and Excel tables

Enter and revise data

Manage data by using Flash Fill

Move data within a workbook

• Sidebar: Quickly access data-formatting commands

Find and replace data

Correct and fine-tune data

Define Excel tables

Skills review

Practice tasks

Chapter 3: Perform calculations on data

Name data ranges

• Sidebar: Operators and Precedence

Create formulas to calculate values

Chapter 4: Change workbook appearance

Format cells

Define and merge cell styles

Apply and modify workbook themes

Apply and modify table styles

Make numbers easier to read

Change the appearance of data based on its value

Add images to worksheets

Skills review

Practice tasks

Chapter 5: Create charts and graphics

Create standard charts

Create combo charts

Create specialized charts

• Hierarchy charts

• Statistic charts

• Scatter charts

• Stock charts

• Map charts

Customize chart appearance

Identify data trends

Summarize data by using sparklines

Illustrate processes and relationships

Insert and manage shapes

Sidebar: Insert mathematical equations

Skills review

Practice tasks

Chapter 6: Print worksheets and charts

Add headers and footers to printed pages

Prepare worksheets for printing

• Fit your worksheet contents to the printed page

• Change page breaks in a worksheet

• Change the page printing order for worksheets

Print worksheets

Print parts of worksheets

Print charts

Skills review

Practice tasks

Chapter 1: Perform calculations on data

Summarize data that meet specific conditions

Copy and move formulas

Create array formulas

Find and correct errors in calculations

Configure automatic and iterative calculation options

Skills review

Practice tasks

Chapter 2: Manage worksheet data

Filter data ranges and tables

Summarize filtered data

• Sidebar: Randomly select list rows

Enforce data entry criteria

Skills review

Practice tasks

Chapter 3: Reorder and summarize data

Sort worksheet data

Sort data by using custom lists

Outline and subtotal data

Skills review

Practice tasks

Chapter 4: Combine data from multiple sources

Look up data from other locations

• Locate information in the same row (VLOOKUP)

• Locate information in the same column (HLOOKUP)

• Locate information anywhere (XLOOKUP)

Link to data in other locations

Consolidate multiple sets of data

Skills review

Practice tasks

Chapter 5: Analyze alternative data sets

Define and display alternative datasets

Forecast data by using data tables

Identify the input necessary to achieve a specific result

Skills review

Practice tasks

Chapter 6: Collaborate with colleagues

Manage comments

Protect workbooks and worksheets

Finalize workbooks

Save workbook content as a PDF file

Create and distribute workbook templates

Skills review

Practice tasks