Large corporations like IBM and Oracle are using Excel dashboards and reports as a Business Intelligence tool, and many other smaller businesses are looking to these tools in order to cut costs for budgetary reasons. An effective analyst not only has to have the technical skills to use Excel in a productive manner but must be able to synthesize data into a story, and then present that story in the most impactful way. Microsoft shows its recognition of this with Excel. In Excel, there is a major focus on business intelligence and visualization. Data Visualization with Excel Dashboards and Reports fills the gap between handling data and synthesizing data into meaningful reports. This title will show readers how to think about their data in ways other than columns and rows.
Most Excel books do a nice job discussing the individual functions and tools that can be used to create an "Excel Report". Titles on Excel charts, Excel pivot tables, and other books that focus on "Tips and Tricks" are useful in their own right; however they don't hit the mark for most data analysts. The primary reason these titles miss the mark is they are too focused on the mechanical aspects of building a chart, creating a pivot table, or other functionality. They don't offer these topics in the broader picture by showing how to present and report data in the most effective way.
What are the most meaningful ways to show trending? How do you show relationships in data? When is showing variances more valuable than showing actual data values? How do you deal with outliers? How do you bucket data in the most meaningful way? How do you show impossible amounts of data without inundating your audience? In Data Visualization with Excel Reports and Dashboards, readers will get answers to all of these questions. Part technical manual, part analytical guidebook; this title will help Excel users go from reporting data with simple tables full of dull numbers, to creating hi-impact reports and dashboards that will wow management both visually and substantively. This book offers a comprehensive review of a wide array of technical and analytical concepts that will help users create meaningful reports and dashboards.
After reading this book, the reader will be able to:
- Analyze large amounts of data and report their data in a meaningful way
- Get better visibility into data from different perspectives
- Quickly slice data into various views on the fly
- Automate redundant reporting and analyses
- Create impressive dashboards and What-If analyses
- Understand the fundamentals of effective visualization
- Visualize performance comparisons
- Visualize changes and trends over time
Table of Contents
Introduction xxi
Part I Display Data on a Dashboard 1
Chapter 1 Dashboard Basics 3
Determining When to Use a Dashboard 3
What Is a Dashboard? 5
Key Performance Indicators 6
Establishing User Requirements 6
Types of End Users 7
Assembling the Data 8
PivotTables 8
The GETPIVOTDATA Worksheet Function 13
Worksheet Functions 14
The VLOOKUP Function 14
The XLOOKUP Function 15
The INDEX and MATCH Functions 16
The SUMPRODUCT Function 17
Array Formulas 19
Tables 20
Structured Table Referencing 23
Text to Columns 24
Removing Duplicates 26
Building the Dashboard 28
Organizing Elements 28
Varying Elements 30
Showing Trends 31
Formatting the Dashboard 33
Number Formats 36
Chapter 2 Dashboard Case Studies 39
Monitoring Progress 39
Case Study: Monitoring a Software Project 40
Planning and Layout 40
Collecting the Data 42
Building the Visual Elements 43
Laying Out the Dashboard 54
Displaying Key Performance Indicators 55
Case Study: Human Resources KPIs 55
Planning and Layout 56
Collecting the Data 57
Building the Visual Elements 58
Laying Out the Dashboard 69
Reporting Financial Information 72
Case Study: Financial Information and Ratios 72
Planning and Layout 72
Collecting the Data 73
Building the Visual Elements 75
Laying Out the Dashboard 83
Chapter 3 Organizing Data for Dashboards 87
Separating Data Layers 87
Source Data Layer 89
Staging and Analysis Layer 90
Presentation Layer 91
Working with External Data 92
Power Query vs. Power Pivot 92
Text Files 92
Excel Files 98
Access Databases 105
SQL Server Databases 111
Transforming Data in Power Query 114
Managing Columns and Rows 116
Transforming Columns 119
Transforming Data Types 119
Transforming Numbers 121
Splitting Columns 123
Part II Visualization Primer 127
Chapter 4 The Fundamentals of Eff ective Visualization 129
Creating an Effective Visualization 129
Keep It to a Single Screen 130
Make It Attractive 131
Tell the Story Quickly 131
Make the Story Consistent with the Data 133
Choose the Proper Chart 135
Driving Meaning with Color 137
How to Use Color 137
Varying Color as Data Values Vary 137
Using Sharp Contrast to Highlight Data 138
Grouping Data with Color 139
Tips on Color Use 140
Use White Space 140
Use a Simple Color Pallet 141
Use Colors That Are Consistent with the Data 141
Use Enough Contrast 141
Use Non-data Pixels When Necessary 142
Focusing Attention on Text 142
Fonts 142
Legends 143
Axes 144
Data Labels 145
Showing Insights with Charts 146
Comparisons 146
Compositions 147
Relationships 149
Chapter 5 Non-chart Visualizations 151
Understanding Custom Number Formats 151
The Four Sections of a Format 152
Special Characters 153
Digit Placeholders 153
Commas and Periods 154
Text 154
Underscore 155
Asterisk 156
Escaping Special Characters 156
The Accounting Number Format 156
Date and Time Formats 158
Conditional Custom Number Formats 159
Using Icons 160
Color Scales 160
Data Bars 165
Icon Sets 167
Creating Sparklines 170
Types of Sparklines 170
Creating a Sparkline 171
Sparkline Groups 172
Customize a Sparkline 172
Changing the Source Data 173
Changing the Color and Thickness 174
Adjusting the Axis 175
Chapter 6 Using Shapes to Create Infographics 179
Working with Shapes 179
Inserting Shapes 180
Customizing Shapes 182
Framing Data with Shapes 185
Creating a Banner 186
Creating a Binder Tab 188
Working with Multiple Shapes 191
Creating Simple Charts with Shapes 193
Creating Custom Infographics 195
Adding Other Illustrations 196
Part III Tell a Story with Visualization 203
Chapter 7 Visualizing Performance Comparisons 205
Single Measurements 206
Column Charts 207
Case Study: Sales by Quarter 210
Bullet Charts 212
Case Study: Expenses vs. Budget 212
Clustered Column Charts 216
Case Study: Production Defects 217
Funnel Charts 218
Case Study: Sales Conversion 219
XY Charts 221
Case Study: Temperature vs. Sales 222
Bubble Charts 225
Case Study: Home Mortgages 226
Dot Plot Charts 228
Case Study: Production Output 229
Chapter 8 Visualizing Parts of a Whole 239
Pie Charts 239
Doughnut Charts 241
Case Study: Sales by Region 242
Waffle Charts 244
Case Study: Employee Participation by Benefit 245
Sunburst Charts 249
Case Study: Manufacturing Process Time Study 250
Histograms 252
Case Study: Restaurant Ticket Totals 254
Treemap Charts 256
Case Study: Insurance Policy Averages 257
Waterfall Charts 259
Case Study: Net Income 261
Chapter 9 Visualizing Changes Over Time 265
Line Charts 266
Case Study: Sales by Product Category 268
Column Charts with Variances 273
Case Study: Houses Sold by Month 274
Combination Charts 280
Case Study: Freight Revenue vs. Miles 281
Line Charts with Differences 284
Case Study: Current vs. Prior Quarter Revenue 285
Side-by-Side Box Plots 288
Case Study: Salaries by Department 290
Animated Charts 292
PivotCharts 293
Staging Area Formulas 295
Chart Animation Macros 299
Chart Automation 302
Manipulating Chart Objects 302
Creating Panel Charts 307
Index 317
