|
Summarizing Excel Data to Make Better Business Decisions |
Course Outline
Introduction: One of the great challenges business people face today is not collecting
information, but making sense of the information they already have. Within the
endless rows of Microsoft Excel data is information that can help you make
better business decisions, but how can you filter you way through all that
information? This course, through hands-on activities, will teach you some of
the most effective techniques in data summary and display. It covers the role
of business intelligence in todays workplace and introduces the power of Excel
in business intelligence analysis. Students also learn how to hone their skills
regarding data lists and PivotTables.
Audience: This course is designed for experienced Excel users
who have a vested interest in analyzing Excel data more effectively. This
audience is familiar with tracking project budgets and chart trends, and they
have no difficulty using Excel to create formulas, charts, and cell formats.
At
Course Completion:
|
|
Describe the role business
intelligence plays in organizational planning and explain how to extend
business intelligence analysis beyond the spreadsheet. |
|
|
Use data lists and SUBTOTAL formulas to summarize their
Excel data. |
|
|
Create and use PivotTables to visualize worksheet data. |
|
|
Limit the data displayed in a PivotTable to only the data
needed to make a specific decision. |
Before attending this course, students must have:
|
|
Experience with analyzing business data to make decisions
about products, projects, and strategic direction. |
|
|
The ability to create formulas, including advanced formulas
using the Insert Function dialog box. |
|
|
The ability to create line graphs and column charts from
Excel data. |
Module 1: Deriving Business Intelligence from Excel Data
This module introduces the process of
deriving business intelligence from spreadsheet data both in general, as
presented in the course lecture segments, and in the context of your business
environment.
Topics and Activities
|
|
What Is Business Intelligence? |
|
|
The Need for Business Intelligence |
|
|
Discussion: The Need for Business Intelligence |
|
|
Summary of Discovering Business Intelligence in Excel |
Module 2: Summarizing Data Using Lists
This module introduces data lists and
SUBTOTAL formulas, which you can use to produce meaningful subsets of your
Excel data. The exercises in this module ask you to analyze sample data in
terms of sample files for a fictitious company.
Topics and Activities
|
|
Demonstration: Revealing Information in Data Lists |
|
|
Walkthrough 1: Creating a Data List |
|
|
Walkthrough 2: Creating a Crosstab Table |
|
|
Walkthrough 3: Creating a SUBTOTAL Formula |
|
|
Walkthrough 4: Filtering a Data List |
|
|
Tips and Tricks for Using Data Lists and SUBTOTAL Formulas |
Module 3: Creating a PivotTable
This module introduces PivotTables and
shows you how to create them. The information in this module also places
PivotTables in the context of analyzing product sales by a store or a product.
Topics and Activities
|
|
The Power of PivotTables |
|
|
Demonstration: Exploring the Capabilities of PivotTables |
|
|
Walkthrough 1: Creating a PivotTable |
|
|
Walkthrough 2: Pivoting a PivotTable |
|
|
Walkthrough 3: Adding Fields to and Removing Fields from
the PivotTable |
|
|
Using PivotTables to Reveal Business Intelligence |
Module 4: Fine-Tuning PivotTables
This module extends your knowledge of
PivotTables by showing you how to limit the data shown in your PivotTable. You
will also learn how to make your data easier to comprehend by formatting your
PivotTable, adding or hiding summary rows and columns, and creating dynamic
charts based on your data.
Topics and Activities
|
|
Demonstration: Filtering Data in PivotTables |
|
|
Exercise 1: Filtering a PivotTable |
|
|
Walkthrough 1: Filtering a PivotTable by Using Page Fields |
|
|
Walkthrough 2: Formatting a PivotTable |
|
|
Walkthrough 3: Creating a PivotChart |
|
|
Revealing Business Intelligence by Using Excel |
|
|
Discussion: Taking Business Intelligence Outside the
Workbook |