Excel 2013 Data Analytics Capabilities Overview
Overview of
Analytics Capabilities for
Excel 2013 Data
Bob & Wilma Andrews
Virginia Commonwealth University
Overview
Excel 2013
New data capabilities
Data
Excel 2013 Data Capabilities
Basic level
New features
INQUIRE
A
n
a
l
y
z
e
w
o
r
k
b
o
o
k
f
o
r
p
r
o
b
l
e
m
s
o
r
i
n
c
o
n
s
i
s
t
e
n
c
i
e
s
S
e
e
l
i
n
k
s
b
e
t
w
e
e
n
w
o
r
k
b
o
o
k
s
,
w
o
r
k
s
h
e
e
t
s
o
r
c
e
l
l
s
C
o
m
p
a
r
e
w
o
r
k
b
o
o
k
v
e
r
s
i
o
n
s
(
g
r
a
p
h
i
c
a
l
d
e
p
e
n
d
e
n
c
y
m
a
p
s
)
W
o
r
k
s
h
e
e
t
r
e
l
a
t
i
o
n
s
h
i
p
d
i
a
g
r
a
m
INQUIRE
Add-in
Turn on at Options/Add-ins/COM
INQUIRE
Workbook Analysis Report
E
x
c
e
l
H
e
l
p
–
A
n
a
l
y
z
e
a
w
o
r
k
b
o
o
k
E
x
c
e
l
H
e
l
p
-
W
h
a
t
y
o
u
c
a
n
d
o
w
i
t
h
S
p
r
e
a
d
s
h
e
e
t
I
n
q
u
i
r
e
Relationships
http://www.microsofttraining.net/b/exceltraining/2013/04/excel-2013-audit-your-spreadsheet-with-
the-inquire-add-in/
Relationships
>300 Million Excel users worldwide
T
h
o
s
e
w
h
o
c
r
e
a
t
e
P
i
v
o
t
T
a
b
l
e
s
10% - Pivot Pros
(Rob Collie)
Recommended PivotTables
Recommended PivotTables
Recommended Charts
Recommended Charts
All Charts
Basic Excel
Power User
One step up
Power User
New features
Microsoft’s four BI
(Business Intelligence)
products
Power Query
Power Map
Power Pivot
Power View
Power Query – simplify data discovery/access
Data discovery and query tool
Enhances self-service Business Intelligence experience
Public search feature (US only)
Power Query
Power Query
Power Query
Power Query
Table format
Power Map (GeoFlow)
3-D data visualization of data sets
Power Map (GeoFlow)
Power Map (GeoFlow)
GeoFlow
% Population 65 and Older by Locality
% People in Poverty by State
Basic Excel
Power User
Another step up
Super Power User
Super Power User
New features
Power Pivot
“The best thing to happen to Excel in 20 years.”
Bill Jelen, Mr. Excel
“This is the rare technology that can (and will)
fundamentally change the lives of millions of
people.”
Rob Collie, PowerPivotPro
Power Pivot
B
y
f
a
r
,
t
h
e
b
i
g
g
e
s
t
“
c
o
m
p
e
t
i
t
o
r
”
t
o
P
o
w
e
r
P
i
v
o
t
i
s
…
E
x
c
e
l
i
t
s
e
l
f
.
I
n
o
t
h
e
r
w
o
r
d
s
,
l
a
c
k
o
f
a
w
a
r
e
n
e
s
s
t
h
a
t
P
o
w
e
r
P
i
v
o
t
e
v
e
n
E
X
I
S
T
S
i
s
s
t
i
l
l
t
h
e
b
i
g
g
e
s
t
“
c
o
m
p
e
t
i
t
o
r
”
t
o
P
o
w
e
r
P
i
v
o
t
t
o
d
a
y
.
http://www.powerpivotpro.com/2014/01/what-is-power-pivots-1-competitor/#more-8854
Power Pivot
Power Pivot
Add-in
File/Options/Add-ins/COM Add-Ins
Power Pivot
DAX FORMULAS FOR PowerPivot, Rob Collie, p.10
DAX FORMULAS FOR PowerPivot, Rob Collie, p.11
Power Query vs Power Pivot
Power Query - discover, connect to, and import data.
Power Pivot - model imported data
Both use Excel data in Excel to explore and visualize with
Power Map, Power View, PivotTables and Pivot Charts.
Power View
Power View
PowerPivot models intuitive data visualization
Join data together in a cohesive unit = data model
Data models created behind the scenes between tables
and relationships
Power View
Power View
Add-in
File/Options/Add-ins/COM Add-Ins
Power View
Commercial Excel Add-ins
JMP -
http://www.jmp.com/support/help/Import_Data_Using_the_Excel_Add-In.shtml
StatTools6 -
https://www.palisade.com/stattools/
XLMiner -
http://www.solver.com/xlminer-data-mining
XLSTAT -
http://www.xlstat.com/en
/
Apps for Office
Apps for Office
Apps for Office
Apps for Office
Resources
V
i
s
u
a
l
i
z
i
n
g
D
a
t
a
w
i
t
h
M
i
c
r
o
s
o
f
t
P
o
w
e
r
V
i
e
w
,
Larson, Davis, English and Purington
D
a
x
F
o
r
m
u
l
a
s
f
o
r
P
o
w
e
r
P
i
v
o
t
,
C
o
l
l
i
e
M
i
c
r
o
s
o
f
t
E
x
c
e
l
2
0
1
3
,
B
u
i
l
d
i
n
g
D
a
t
a
M
o
d
e
l
s
w
i
t
h
P
o
w
e
r
P
i
v
o
t
,
F
e
r
r
a
i
a
n
d
R
u
s
s
o
Microsoft Support site
Videos at Excel New
Excel Videos
Excel Videos
Resources
Bill Jelen –
Mr. Excel
http://www.youtube.com/user/bjele123
Resources
Mike Girven -
Excel is Fun
http://www.youtube.com/user/ExcelIsFun
Resources
Rob Collie – powerpivotpro.com
http://www.youtube.com/channel/UCoZckhwYkolkjBIdVXpSNSg
Discussion
Explore the comprehensive data analytics capabilities of Excel 2013, including data acquisition, management, analysis, and visualization. Learn about new features, such as Inquire add-in for workbook analysis, recommended PivotTables and charts, Power Query, Power Map, and more. Enhance your Excel skills and harness the power of data for informed decision-making.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
E N D
Presentation Transcript
Overview of Analytics Capabilities for Excel 2013 Data Bob & Wilma Andrews Virginia Commonwealth University
Overview Excel 2013 New data capabilities
Data obtain manage analyze visualize
Excel 2013 Data Capabilities Obtain Obtain Obtain Obtain Manage Manage Manage Analyze Visualize Analyze Visualize Analyze Visualize Analyze Visualize Manage X X X X Inquire Inquire Recommended PivotTables Recommended Charts Recommended Charts Power Query Power Map Power Map Power Pivot Power View Power View Commercial Add-Ins Apps for Office Inquire Recommended PivotTables PivotTables Recommended Charts Power Query Power Query Power Map Power Pivot Inquire Recommended Recommended PivotTables Recommended Charts X X X X X X X X X X X X X X X X X X X X X X X X X X
Basic level New features
INQUIRE Analyze workbook for problems or inconsistencies See links between workbooks, worksheets or cells Compare workbook versions (graphical dependency maps) Worksheet relationship diagram
INQUIRE Add-in Turn on at Options/Add-ins/COM
INQUIRE Relationships workbook, worksheet, cells Manage passwords Clean excess cell formatting Workbook analysis Show workbook links Compare files
Workbook Analysis Report Excel Help Analyze a workbook
Relationships Excel Help - What you can do with Spreadsheet Inquire
Relationships http://www.microsofttraining.net/b/exceltraining/2013/04/excel-2013-audit-your-spreadsheet-with- the-inquire-add-in/
>300 Million Excel users worldwide Those who create Pivot Tables 10% - Pivot Pros (Rob Collie)
One step up Power User Basic Excel
Power User New features
Microsofts four BI (Business Intelligence) products Power Query Power Map Power Pivot Power View
Power Query simplify data discovery/access Data discovery and query tool Enhances self-service Business Intelligence experience Public search feature (US only)
Power Map (GeoFlow) 3-D data visualization of data sets
Another step up Super Power User Power User Basic Excel
Super Power User New features
Power Pivot The best thing to happen to Excel in 20 years. Bill Jelen, Mr. Excel This is the rare technology that can (and will) fundamentally change the lives of millions of people. Rob Collie, PowerPivotPro
Power Pivot By far, the biggest competitor to Power Pivot is Excel itself. In other words, lack of awareness that Power Pivot even EXISTS is still the biggest competitor to Power Pivot today. http://www.powerpivotpro.com/2014/01/what-is-power-pivots-1-competitor/#more-8854
Power Pivot Handles massive volumes of data Builds on known pivot table skills Possible multi-table pivots Save As option for interactive web application
Power Pivot Add-in File/Options/Add-ins/COM Add-Ins
Power Query vs Power Pivot Power Query - discover, connect to, and import data. Power Pivot - model imported data Both use Excel data in Excel to explore and visualize with Power Map, Power View, PivotTables and Pivot Charts.
Power View Explore Visualize Present
Power View PowerPivot models intuitive data visualization Join data together in a cohesive unit = data model Data models created behind the scenes between tables and relationships
Power View Charts Filtering Slicers Sorting Reports with multiple views
Power View Add-in File/Options/Add-ins/COM Add-Ins
