Title Page
Copyright Page
Table of Contents
Introduction
About This Book
Foolish Assumptions
Icons Used in This Book
Beyond the Book
Where to Go from Here
Part 1 Getting Started with Excel Dashboards and Reports
Chapter 1 Getting in the Dashboard State of Mind
Defining Dashboards and Reports
Defining reports
Defining dashboards
Preparing for Greatness
Establish the audience for, and purpose of, the dashboard
Delineate the measures for the dashboard
Catalog the required data sources
Define the dimensions and filters for the dashboard
Determine the need for drill-down features
Establish the refresh schedule
A Quick Look at Dashboard Design Principles
Rule number 1: Keep it simple
Use layout and placement to draw focus
Format numbers effectively
Use titles and labels effectively
Chapter 2 Building a Super Model
Data Modeling Best Practices
Separating data, analysis, and presentation
Starting with appropriately structured data
Avoiding turning your data model into a database
Using tabs to document and organize your data model
Testing your data model before building reporting components on top of it
Excel Functions That Really Deliver
The VLOOKUP function
The HLOOKUP function
The SUMPRODUCT function
The CHOOSE function
Using Smart Tables That Expand with Data
Converting a range to an Excel table
Converting an Excel table back to a range
Introducing Dynamic Arrays
Getting the basics of dynamic arrays
Understanding spill ranges
Referencing spill ranges
Exploring Dynamic Array Functions
The SORT function
The SORTBY function
The UNIQUE function
The FILTER function
The XLOOKUP function
Chapter 3 The Pivotal Pivot Table
An Introduction to the Pivot Table
The Four Areas of a Pivot Table
Values area
Row area
Column area
Filter area
Creating Your First Pivot Table
Changing and rearranging your pivot table
Adding a report filter
Keeping your pivot table fresh
Customizing Pivot Table Reports
Changing the pivot table layout
Customizing field names
Applying numeric formats to data fields
Changing summary calculations
Suppressing subtotals
Showing and hiding data items
Hiding or showing items without data
Sorting your pivot table
Creating Useful Pivot-Driven Views
Producing top and bottom views
Creating views by month, quarter, and year
Creating a percent distribution view
Creating a month-over-month variance view
Chapter 4 Using External Data for Your Dashboards and Reports
Leveraging Power Query to Extract and Transform Data
Reviewing Power Query basics
Understanding query steps
Importing Data from Files
Getting data from Excel workbooks
Getting data from CSV and text files
Importing Data from Database Systems
Importing data from Microsoft Access
Managing data source settings
Part 2 Building Basic Dashboard Components
Chapter 5 Dressing Up Your Data Tables
Table Design Principles
Use colors sparingly
De-emphasize borders
Use effective number formatting
Subdue your labels and headers
Getting Fancy with Custom Number Formatting
Number formatting basics
Formatting numbers in thousands and millions
Hiding and suppressing zeroes
Applying custom format colors
Formatting dates and times
Chapter 6 Sparking Inspiration with Sparklines
Introducing Sparklines
Understanding Sparklines
Creating sparklines
Understanding sparkline groups
Customizing Sparklines
Sizing and merging sparkline cells
Handling hidden or missing data
Changing the sparkline type
Changing sparkline colors and line width
Using color to emphasize key data points
Adjusting sparkline axis scaling
Faking a reference line
Specifying a date axis
Autoupdating sparkline ranges
Chapter 7 Formatting Your Way to Visualizations
Enhancing Reports with Conditional Formatting
Applying basic conditional formatting
Adding your own formatting rules manually
Showing only one icon
Showing Data Bars and icons outside of cells
Representing trends with Icon Sets
Using Symbols to Enhance Reporting
Wielding the Magical Camera Tool
Finding the Camera tool
Using the Camera tool
Enhancing a dashboard with the Camera tool
Enhancing Excel Reports with Shapes
Creating visually appealing containers with shapes
Layering shapes to save space
Constructing your own infographic widgets with shapes
Part 3 Adding Charts to Your Dashboards
Chapter 8 Charts That Show Trending
Trending Dos and Don’ts
Using chart types appropriate for trending
Starting the vertical scale at zero
Leveraging Excel’s logarithmic scale
Applying creative label management
Comparative Trending
Creating side-by-side time comparisons
Creating stacked time comparisons
Trending with a secondary axis
Emphasizing Periods of Time
Formatting specific periods
Using dividers to mark significant events
Representing forecasts in your trending components
Other Trending Techniques
Avoiding overload with directional trending
Smoothing data
Chapter 9 Grouping and Bucketing Data
Creating Top and Bottom Displays
Incorporating top and bottom displays into dashboards
Using pivot tables to get top and bottom views
Top Values in Charts
Using Histograms to Track Relationships and Frequency
Using Excel’s Histogram statistical chart
Creating a formula-driven histogram
Adding a cumulative percent
Using a pivot table to create a histogram
Chapter 10 Displaying Performance against a Target
Showing Performance with Variances
Showing Performance against Organizational Trends
Using a Thermometer-Style Chart
Using a Bullet Graph
Creating a bullet graph
Adding data to your bullet graph
Final thoughts on formatting bullet graphs
Showing Performance against a Target Range
Part 4 Advanced Reporting Techniques
Chapter 11 Giving Users an Interactive Interface
Introducing Macros
Why use a macro?
Recording your first macro
Running your macros
Enabling and trusting macros
Understanding macro-enabled file extensions
Enabling macro content
Setting up trusted locations
Examining some macro examples
Building navigation buttons
Dynamically rearranging pivot table data
Offering one-touch reporting options
Getting Started with Form Controls
Finding Form controls
Adding a control to a worksheet
Using the Button Control
Using the Check Box Control
Toggling a Chart Series On and Off
Using the Option Button Control
Showing Many Views through One Chart
Using the Combo Box Control
Changing Chart Data with a Drop-Down Selector
Using the List Box Control
Controlling Multiple Charts with One Selector
Chapter 12 Adding Interactivity with Pivot Slicers
Understanding Slicers
Creating a Standard Slicer
Getting Fancy with Slicer Customizations
Size and placement
Data item columns
Other slicer settings
Creating your own slicer style
Controlling Multiple Pivot Tables with One Slicer
Creating a Timeline Slicer
Using Slicers as Form Controls
Using Slicers on Excel Table Objects
Chapter 13 Sharing Your Workbook with the Outside World
Protecting Your Dashboards and Reports
Securing access to the entire workbook
Limiting access to specific worksheet ranges
Protecting the workbook structure
Linking Your Excel Dashboards to PowerPoint
Creating a link between Excel and PowerPoint
Manually updating links to capture updates
Turning off automatic updating of links
Distributing Your Dashboards via a PDF
Distributing Your Dashboards to OneDrive
Limitations When Publishing to the Web
Part 5 The Part of Tens
Chapter 14 Ten Chart Design Principles
Avoid Fancy Formatting
Skip the Unnecessary Chart Junk
Format Large Numbers Where Possible
Use Data Tables Instead of Data Labels
Make Effective Use of Chart Titles
Sort Your Data before Charting
Limit the Use of Pie Charts
Don’t Be Afraid to Parse Data into Separate Charts
Maintain Appropriate Aspect Ratios
Don’t Be Afraid to Use Something Other Than a Chart
Chapter 15 Ten Questions to Ask Before Distributing Your Dashboard
Does My Dashboard Present the Right Information?
Does Everything on My Dashboard Have a Purpose?
Does My Dashboard Prominently Display the Key Message?
Can I Maintain This Dashboard?
Does My Dashboard Clearly Display Its Scope and Shelf Life?
Is My Dashboard Well Documented?
Is My Dashboard Overwhelmed with Formatting and Graphics?
Does My Dashboard Overuse Charts When Tables Will Do?
Is My Dashboard User-Friendly?
Is My Dashboard Accurate?
Index
EULA