Table of Contents for Excel Dashboards and Reports


Introduction 1(10)
What You Need to Know
2(1)
What You Need to Have
3(1)
Conventions in This Book
3(1)
Keyboard conventions
3(1)
Mouse conventions
4(1)
What the icons mean
4(1)
How This Book Is Organized
5(1)
Part I Getting Started with Excel Dashboards
5(1)
Part II Introducing Charts into Your Dashboards
5(1)
Part III Advanced Dashboarding Concepts
6(1)
Part IV Pivot Table Driven Dashboards
6(1)
Part V Working with the Outside World
6(1)
About the Companion Website
6(1)
About the Power Utility Pak Offer
7(1)
Reach Out
7(4)
Part I Getting Started with Excel Dashboards
Chapter 1 Introducing Dashboards
11(18)
What Are Dashboards and Reports?
11(1)
Defining reports
12(1)
Defining dashboards
12(1)
Establish the User Requirements
13(1)
Define the message(s)
14(1)
Establish the audience
14(1)
Define the performance measures
14(2)
List the required data sources
16(1)
Define the dimensions and filters
16(1)
Determine the need for drill-down details
17(1)
Establish the update schedule
17(1)
A Quick Look at Dashboard Design Principles
17(1)
Rule number 1: Keep it simple
18(4)
Use layout and placement to draw focus
22(1)
Format numbers effectively
22(1)
Use titles and labels effectively
23(1)
Key Questions to Ask Before Distributing Your Dashboard
24(1)
Does my dashboard present the right information?
24(1)
Does everything on my dashboard have a purpose?
24(1)
Does my dashboard prominently display the key message?
24(1)
Can I maintain this dashboard?
25(1)
Does my dashboard clearly display its scope and shelf life?
25(1)
Is my dashboard well documented?
25(1)
Is my dashboard user-friendly?
26(1)
Is my dashboard accurate?
26(3)
Chapter 2 Table Design Best Practices
29(18)
Table Design Principles
30(1)
Use colors sparingly
30(1)
De-emphasize borders
31(3)
Use effective number formatting
34(1)
Subdue your labels and headers
35(1)
Enhancing Reporting with Custom Number Formatting
36(1)
Number formatting basics
37(2)
Formatting numbers in thousands and millions
39(2)
Hiding and suppressing zeros
41(1)
Applying custom format colors
42(2)
Formatting dates and times
44(1)
Adding conditions to customer number formatting
45(2)
Chapter 3 Using Excel Sparklines
47(14)
Understanding Sparklines
48(1)
Applying Sparklines
49(1)
Creating Sparklines
50(2)
Customizing Sparklines
52(1)
Sizing and merging sparkline cells
52(1)
Handling hidden or missing data
53(1)
Changing the sparkline type
54(1)
Changing sparkline colors and line width
54(1)
Using color to emphasize key data points
55(1)
Adjusting sparkline axis scaling
55(1)
Faking a reference line
56(2)
Specifying a date axis
58(1)
Auto-updating sparkline ranges
59(2)
Chapter 4 Chartless Visualization Techniques
61(28)
Enhancing Reports with Conditional Formatting
61(1)
Applying basic conditional formatting
62(8)
Adding your own formatting rules manually
70(3)
Show only one icon
73(2)
Show Data Bars and icons outside of cells
75(2)
Representing trends with icon sets
77(2)
Using Symbols to Enhance Reporting
79(3)
Using Excel's Camera Tool
82(1)
Finding the Camera tool
82(1)
Using the Camera tool
83(2)
Enhancing a dashboard with the Camera tool
85(4)
Part II Introducing Charts into Your Dashboards
Chapter 5 Excel Charting for the Uninitiated
89(18)
What Is a Chart?
89(1)
How Excel Handles Charts
90(2)
Embedded charts
92(1)
Chart sheets
93(1)
Parts of a Chart
93(3)
Basic Steps for Creating a Chart
96(1)
Creating the chart
96(2)
Switching the row and column orientation
98(1)
Changing the chart type
98(1)
Applying chart styles
99(1)
Applying a chart style
99(1)
Adding and deleting chart elements
100(1)
Moving and deleting chart elements
101(1)
Formatting chart elements
101(1)
Working with Charts
102(1)
Moving and resizing a chart
102(1)
Converting an embedded chart to a chart sheet
103(1)
Copying a chart
103(1)
Deleting a chart
104(1)
Copying a chart's formatting
104(1)
Renaming a chart
104(1)
Printing charts
105(2)
Chapter 6 Working with Chart Series
107(24)
Specifying the Data for Your Chart
107(2)
Adding a New Series to a Chart
109(1)
Adding a new series by copying a range
110(1)
Adding a new series by extending the range highlight
111(1)
Adding a new series using the Select Data Source dialog box
111(1)
Adding a new series by typing a new SERIES formula
112(1)
Deleting a Chart Series
113(1)
Modifying the Data Range for a Chart Series
113(1)
Using range highlighting to change series data
113(2)
Using the Select Data Source dialog box to change series data
115(1)
Editing the SERIES formula to change series data
116(1)
Understanding Series Names
117(1)
Changing a series name
118(1)
Deleting a series name
119(1)
Adjusting the Series Plot Order
120(1)
Charting a Noncontiguous Range
121(1)
Using Series on Different Sheets
122(1)
Handling Missing Data
123(1)
Controlling a Data Series by Hiding Data
124(1)
Unlinking a Chart Series from Its Data Range
125(1)
Converting a chart to a picture
126(1)
Converting a range reference to arrays
127(1)
Working with Multiple Axes
128(1)
Creating a secondary value axis
128(1)
Creating a chart with four axes
129(2)
Chapter 7 Formatting and Customizing Charts
131(40)
Chart Formatting Overview
131(1)
Selecting chart elements
132(2)
Common chart elements
134(1)
Ul choices for formatting
135(3)
Adjusting Fills and Borders: General Procedures
138(1)
About the Fill tab
138(2)
Formatting borders
140(1)
Formatting Chart Background Elements
140(1)
Working with the chart area
141(1)
Working with the plot area
141(2)
Formatting Chart Series
143(1)
Basic series formatting
143(1)
Using pictures and graphics for series formatting
143(2)
Additional series options
145(1)
Working with Chart Titles
146(1)
Adding titles to a chart
147(1)
Changing title text
147(1)
Formatting title text
147(1)
Linking title text to a cell
148(1)
Working with a Chart's Legend
149(1)
Adding or removing a legend
150(1)
Moving or resizing a legend
150(1)
Formatting a legend
150(1)
Changing the legend text
150(1)
Deleting a legend entry
151(1)
Identifying series without using a legend
151(1)
Working with Chart Axes
151(1)
Value axis versus category axis
152(1)
Value axis scales
153(6)
Using time-scale axes
159(2)
Creating a multiline category axis
161(1)
Removing axes
162(1)
Axis number formats
163(1)
Working with Gridlines
163(1)
Adding or removing gridlines
164(1)
Working with Data Labels
164(1)
Adding or removing data labels
164(1)
Editing data labels
165(2)
Problems and limitations with data labels
167(2)
Working with a Chart Data Table
169(1)
Adding and removing a data table
169(1)
Problems and limitations with data tables
169(2)
Chapter 8 Components That Show Trending
171(22)
Trending Dos and Don'ts
171(1)
Using chart types appropriate for trending
172(1)
Starting the vertical scale at zero
173(2)
Leveraging Excel's logarithmic scale
175(1)
Applying creative label management
176(3)
Comparative Trending
179(1)
Creating side-by-side time comparisons
179(2)
Creating stacked time comparisons
181(1)
Trending with a secondary axis
182(2)
Emphasizing Periods of Time
184(1)
Formatting specific periods
184(1)
Using dividers to mark significant events
185(1)
Representing forecasts in your trending components
186(1)
Other Trending Techniques
187(1)
Avoiding overload with directional trending
187(1)
Smoothing data
188(5)
Chapter 9 Components That Group Data
193(16)
Listing Top and Bottom Values
193(1)
Organizing source data
194(1)
Using pivot tables to get top and bottom views
195(2)
Using Histograms to Track Relationships and Frequency
197(1)
Adding formulas to group data
198(3)
Adding a cumulative percent
201(1)
Using a pivot table to create a histogram
202(2)
Emphasizing Top Values in Charts
204(5)
Chapter 10 Components That Show Performance Against a Target
209(16)
Showing Performance with Variances
210(1)
Showing Performance Against Organizational Trends
210(2)
Using a Thermometer-Style Chart
212(1)
Using a Bullet Graph
212(1)
Creating a bullet graph
213(3)
Adding data to your bullet graph
216(1)
Final thoughts on formatting bullet graphs
217(2)
Showing Performance Against a Target Range
219(6)
Part III Advanced Dashboarding Concepts
Chapter 11 Developing Your Data Model
225(22)
Building a Data Model
225(1)
Separating the data, analysis, and presentation layers
226(4)
Data Model Best Practices
230(1)
Avoid storing excess data
230(1)
Use tabs to document and organize your data model
231(1)
Test your data model before building presentation components
232(1)
Excel Functions for Your Data Model
233(1)
Understanding lookup tables
233(1)
The VLOOKUP function
233(4)
The HLookup function
237(1)
The SUMPRODUCT function
238(3)
The Choose function
241(2)
Working with Excel Tables
243(1)
Converting a range to an Excel table
244(1)
Converting an Excel table back to a range
245(2)
Chapter 12 Adding Interactive Controls to Your Dashboard
247(18)
Getting Started with Form Controls
248(1)
Finding Form controls
248(2)
Adding a control to a worksheet
250(1)
Using the Button Control
251(1)
Using the Check Box Control
251(2)
Check box example: Toggling a chart series on and off
253(2)
Using the Option Button Control
255(1)
Option button example: Showing many views through one chart
256(2)
Using the Combo Box Control
258(1)
Combo box example: Changing chart data with a drop-down selector
259(2)
Using the List Box Control
261(1)
List box example: Controlling multiple charts with one selector
262(3)
Chapter 13 Macro-Charged Reporting
265(16)
Why Use a Macro?
265(1)
Recording Your First Macro
266(3)
Running your macros
269(1)
Assigning a macro to a button
270(2)
Enabling Macros in Excel 2013
272(1)
Viewing the new Excel security message
272(1)
Setting up trusted locations
272(2)
Excel Macro Examples
274(1)
Building navigation buttons
274(1)
Dynamically rearranging pivot table data
275(1)
Offering one-touch reporting options
276(5)
Part IV Pivot Table Driven Dashboards
Chapter 14 Using Pivot Tables
281(30)
Introducing the Pivot Table
281(1)
Anatomy of a pivot table
282(2)
Creating the basic pivot table
284(7)
Customizing Your Pivot Table
291(1)
Changing the pivot table layout
291(1)
Renaming the fields
292(1)
Formatting numbers
293(1)
Changing summary calculations
294(1)
Suppressing subtotals
295(1)
Removing all subtotals at one time
295(1)
Removing the subtotals for only one field
296(1)
Removing grand totals
297(1)
Hiding and showing data items
297(2)
Hiding or showing items without data
299(2)
Sorting your pivot table
301(1)
Examples of Filtering Your Data
302(1)
Producing top and bottom views
302(3)
Creating views by month, quarter, and year
305(2)
Creating a percent distribution view
307(1)
Creating a YTD totals view
308(1)
Creating a month-over-month variance view
309(2)
Chapter 15 Using Pivot Charts
311(18)
Getting Started with Pivot Charts
311(1)
Creating a pivot chart
312(2)
Understanding the link between pivot charts and pivot tables
314(2)
Limitations of pivot charts
316(1)
Using conditional formatting with pivot tables
316(3)
Customizing conditional formatting
319(5)
Alternatives to Pivot Charts
324(1)
Disconnecting charts from pivot tables
324(2)
Create standalone charts that are connected to your pivot table
326(3)
Chapter 16 Adding Interactivity with Slicers
329(14)
Understanding Slicers
329(2)
Creating a Standard Slicer
331(2)
Formatting slicers
333(3)
Controlling multiple pivot tables
336(1)
Creating a Timeline Slicer
337(2)
Using Slicers as Form Controls
339(4)
Chapter 17 Using the Internal Data Model and Power View
343(22)
Understanding the Internal Data Model
344(1)
Building out your first data model
344(4)
Using your Data Model in a pivot table
348(2)
Using external data sources in your internal Data Model
350(3)
Creating a Power View Dashboard
353(1)
Creating and working with Power View charts
354(4)
Visualizing data in a Power View map
358(2)
Changing the look of your Power View dashboard
360(5)
Part V Working with the Outside World
Chapter 18 Integrating External Data into Excel Reporting
365(16)
Importing Data from Microsoft Access
366(1)
The drag-and-drop method
366(1)
The Microsoft Access Export Wizard
367(1)
The Get External Data icon
368(4)
Importing Data from SQL Server
372(3)
Passing Your Own SQL Statements to External Databases
375(1)
Manually editing SQL statements
375(1)
Running stored procedures from Excel
376(1)
Using VBA to create dynamic connections
377(4)
Chapter 19 Sharing Your Work with the Outside World
381(18)
Securing Your Dashboards and Reports
381(1)
Securing access to the entire workbook
381(3)
Limiting access to specific worksheet ranges
384(3)
Protecting the workbook structure
387(1)
Linking Your Excel Dashboards to PowerPoint
388(1)
Creating the link between Excel and PowerPoint
388(2)
Manually updating links to capture updates
390(1)
Automatically updating links
391(1)
Distributing Your Dashboards via a PDF
392(2)
Distributing Your Dashboards to SkyDrive
394(3)
Limitations when publishing to the web
397(2)
Index 399