Data Analysis in Excel
Learning Objectives
Analyze a data set using native Apply Excel tools and formulas to Create pivot tables to slice and
Excel tools transform and structure data dice data
Visualize data with pivot charts Explore the landscape of data
and Excel charts analysis and visualization
BIDA® - Business Intelligence & Data Analysis
Chapter Introduction – Analyze Data
Sort and Filter Data
Conditional Formatting
Analyze Data
Excel Functions
Create Functions
BIDA® - Business Intelligence & Data Analysis
Chapter Review – Analyze Data
Analyze Data with Excel
Interface
Conditional Formatting
Analyze Data
Excel Functions
Create Functions
BIDA® - Business Intelligence & Data Analysis
Chapter Introduction – Transform Data
Import & Deduplicate
Transform Text & Date
Transform Data Values
Fix Errors & NA Values
Lookup Data
BIDA® - Business Intelligence & Data Analysis
Chapter Review – Transform Data
Transform Data with
Excel Interface
Transform Text & Date
Transform Data Values
Fix Errors & NA Values
Lookup Data
BIDA® - Business Intelligence & Data Analysis
Chapter Introduction – Excel Tables
Manage & analyze related data Excel Tables
Data preparation & transformation tool in Excel Power Query
Provide the ability to connect to data on variety of subjects Linked Data Types
BIDA® - Business Intelligence & Data Analysis
Chapter Review – Excel Tables
Excel tables are important to a modern data analysis
StudentID FirstName LastName GradeAverage Faculty Tuition OfficeHoursParticipated TutorialsAttended ClassesSkipped
20123456 John Park B Arts 44191 0 10 5
Power Query 20123457
20123458
Alex
Sebastian
Great
Taylor
B
B
Science
Business
32245
42679
4
6
10
3
10
7
20123459 Michael Bay A Math 46478 15 3 2
20123460 Scott Foster A Engineering 36784 5 9 8
20123461 Amy Winehouse B Arts 36537 10 6 3
20123462 Ralph Wiggins B Business 40762 2 6 8
20123463 Homer Simpson C Engineering 47669 4 8 7
Linked Data Types 20123464
20123465
Marge
Peter
Simpson
Gryffin
B
D
Math
Arts
39429
31956
10
7
5
9
3
7
20123466 Louise King D Business 33227 6 2 7
20123467 Megan Botts A Science 34751 25 5 1
20123468 Cyrus Wong A Science 49298 20 0 0
20123469 Michelle Chang B Business 35046 5 10 4
Dynamic Arrays 20123470
20123471
Zachary
Angus
Chua
Helmsworth
A
B
Business
Business
31210
47515
10
10
8
6
0
1
20123472 Aaron McDowell B Business 43421 13 7 2
20123473 Carol Kuo B Engineering 43063 7 0 4
20123474 Tim James B Science 46775 9 3 4
20123475 Johnson Curry B Engineering 40539 9 4 3
20123476 Paul Reed A Business 41397 14 2 2
Pivot Tables 20123477
20123478
Josh
Justin
Hart
Kang
A
A
Engineering
Engineering
46468
35859
4
2
8
4
8
9
20123479 Kevin Yoo A Arts 41048 8 9 1
20123480 Rosaline Jun A Arts 44915 25 6 3
20123481 Jimin Park B Engineering 34570 20 1 3
20123482 Joseph Kim A Math 33376 12 5 6
20123483 Chris Dang F Business 44737 3 8 8
Data Visualizations 20123484
20123485
Robbie
Shelly
Tee
Yoon
B
A
Engineering
Math
49682
33585
10
5
5
7
6
10
20123486 Namjoon Yoongi A Arts 53585 7 8 2
BIDA® - Business Intelligence & Data Analysis
Chapter Introduction – Dynamic Arrays
Dynamic array formulas allow us to reference multiple values of data at once.
{} Formulas
= Functions
Dynamic Arrays
X XLOOKUP ()
Best Practices
BIDA® - Business Intelligence & Data Analysis
Chapter Review – Dynamic Arrays
Multiple Values in one
{}
formula
= Dynamic Array Functions
Dynamic Arrays
Array “Spill”
Future-proof solution
BIDA® - Business Intelligence & Data Analysis
Chapter Introduction – Pivot Tables
Pivot tables can be used to summarize large data sets and quickly generate lots of different insights.
Pivot Tables
Pivot Charts
Pivot Tables
Power Pivot
Analyze Data – AI Ideas
BIDA® - Business Intelligence & Data Analysis
Chapter Review – Pivot Tables
Pivot Tables
Slicers & Timelines
Pivot Tables
Pivot Charts
Power Pivot
BIDA® - Business Intelligence & Data Analysis
Chapter Introduction – Visualize Data
Visualizing Data with Excel Charts:
Select Data
Select Chart
Format Chart
BIDA® - Business Intelligence & Data Analysis
Chapter Review – Visualize Data
Select the Data to Visualize
Choose a Chart Type
Create a Chart
BIDA® - Business Intelligence & Data Analysis
