Introduction 1 About This Book 2 Foolish Assumptions 4 Icons Used in This Book 4 Beyond the Book 4 Where to Go from Here 5 Part 1: Getting Started with Excel Dashboards and Reports 7 Chapter 1: Getting in the Dashboard State of Mind 9 Defining Dashboards and Reports 10 Defining reports 10 Defining dashboards 11 Preparing for Greatness 12 Establish the audience for, and purpose of, the dashboard 12 Delineate the measures for the dashboard 13 Catalog the required data sources 14 Define the dimensions and filters for the dashboard 15 Determine the need for drill-down features 16 Establish the refresh schedule 16 A Quick Look at Dashboard Design Principles 16 Rule number 1: Keep it simple 17 Use layout and placement to draw focus 19 Format numbers effectively 20 Use titles and labels effectively 20 Chapter 2: Building a Super Model 23 Data Modeling Best Practices 24 Separating data, analysis, and presentation 24 Starting with appropriately structured data 27 Avoiding turning your data model into a database 30 Using tabs to document and organize your data model 31 Testing your data model before building reporting components on top of it 33 Excel Functions That Really Deliver 34 The VLOOKUP function 34 The HLOOKUP function 38 The SUMPRODUCT function 40 The CHOOSE function 43 Using Smart Tables That Expand with Data 45 Converting a range to an Excel table 46 Converting an Excel table back to a range 49 Introducing Dynamic Arrays 49 Getting the basics of dynamic arrays 49 Understanding spill ranges 51 Referencing spill ranges 53 Exploring Dynamic Array Functions 54 The SORT function 55 The SORTBY function 56 The UNIQUE function 57 The FILTER function 58 The XLOOKUP function 61 Chapter 3: The Pivotal Pivot Table 65 An Introduction to the Pivot Table 65 The Four Areas of a Pivot Table 66 Values area 66 Row area 67 Column area 67 Filter area 68 Creating Your First Pivot Table 69 Changing and rearranging your pivot table 72 Adding a report filter 73 Keeping your pivot table fresh 74 Customizing Pivot Table Reports 76 Changing the pivot table layout 76 Customizing field names 78 Applying numeric formats to data fields 79 Changing summary calculations 80 Suppressing subtotals 81 Showing and hiding data items 84 Hiding or showing items without data 86 Sorting your pivot table 88 Creating Useful Pivot-Driven Views 89 Producing top and bottom views 89 Creating views by month, quarter, and year 93 Creating a percent distribution view 95 Creating a month-over-month variance view 97 Chapter 4: Using External Data for Your Dashboards and Reports 101 Leveraging Power Query to Extract and Transform Data 102 Reviewing Power Query basics 102 Understanding query steps 109 Importing Data from Files 111 Getting data from Excel workbooks 111 Getting data from CSV and text files 113 Importing Data from Database Systems 114 Importing data from Microsoft Access 114 Managing data source settings 116 Part 2: Building Basic Dashboard Components 119 Chapter 5: Dressing Up Your Data Tables 121 Table Design Principles 122 Use colors sparingly 122 De-emphasize borders 123 Use effective number formatting 126 Subdue your labels and headers 127 Getting Fancy with Custom Number Formatting 129 Number formatting basics 129 Formatting numbers in thousands and millions 132 Hiding and suppressing zeroes 134 Applying custom format colors 135 Formatting dates and times 136 Chapter 6: Sparking Inspiration with Sparklines 139 Introducing Sparklines 139 Understanding Sparklines 141 Creating sparklines 142 Understanding sparkline groups 144 Customizing Sparklines 145 Sizing and merging sparkline cells 145 Handling hidden or missing data 146 Changing the sparkline type 147 Changing sparkline colors and line width 147 Using color to emphasize key data points 147 Adjusting sparkline axis scaling 148 Faking a reference line 149 Specifying a date axis 151 Autoupdating sparkline ranges 152 Chapter 7: Formatting Your Way to Visualizations 153 Enhancing Reports with Conditional Formatting 154 Applying basic conditional formatting 154 Adding your own formatting rules manually 162 Showing only one icon 166 Showing Data Bars and icons outside of cells 169 Representing trends with Icon Sets 171 Using Symbols to Enhance Reporting 173 Wielding the Magical Camera Tool 176 Finding the Camera tool 176 Using the Camera tool 177 Enhancing a dashboard with the Camera tool 179 Enhancing Excel Reports with Shapes 180 Creating visually appealing containers with shapes 180 Layering shapes to save space 182 Constructing your own infographic widgets with shapes 182 Part 3: Adding Charts to Your Dashboards 185 Chapter 8: Charts That Show Trending 187 Trending Dos and Don''ts 188 Using chart types appropriate for trending 188 Starting the vertical scale at zero 190 Leveraging Excel''s logarithmic scale 192 Applying creative label management 193 Comparative Trending 196 Creating side-by-side time comparisons 196 Creating stacked time comparisons 198 Trending with a secondary axis 199 Emphasizing Periods of Time 202 Formatting specific periods 202 Using dividers to mark significant events 203 Representing forecasts in your trending components 204 Other Trending Techniques 206 Avoiding overload with directional trending 206 Smoothing data 207 Chapter 9: Grouping and Bucketing Data 211 Creating Top and Bottom Displays 211 Incorporating top and bottom displays into dashboards 212 Using pivot tables to get top and bottom views 213 Top Values in Charts 216 Using Histograms to Track Relationships and Frequency 220 Using Excel''s Histogram statistical chart 220 Creating a formula-driven histogram 223 Adding a cumulative percent 226 Using a pivot table to create a histogram 228 Chapter 10: Displaying Performance against a Target 231 Showing Performance with Variances 231 Showing Performance against Organizational Trends 233 Using a Thermometer-Style Chart 234 Using a Bullet Graph 235 Creating a bullet graph 236 Adding data to your bullet graph 239 Final thoughts on formatting bullet graphs 241 Showing Performance against a Target Range 243 Part 4: Advanced Reporting Techniques 247 Chapter 11: Giving Users an Interactive Interface 249 Introducing Macros 249 Why use a macro? 250 Recording your first macro 251 Running your macros 254 Enabling and trusting macros 257 Understanding macro-enabled file extensions 258 Enabling macro content 258 Setting up trusted locations 258 Examining some macro examples 259 Building navigation buttons 260 Dynamically rearranging pivot table data 261 Offering one-touch reporting options 262 Getting Started with Form Controls 263 Finding Form controls 263 Adding a control to a worksheet 264 Using the Button Control 266 Using the Check Box Control 266 Toggling a Chart Series On and Off 268 Using the Option Button Control 270 Showing Many Views through One Chart 272 Using the Combo Box Control 274 Changing Chart Data with a Drop-Down Selector 275 Using the List Box Control 277 Controlling Multiple Charts with One Selector 279 Chapter 12: Adding Interactivity with Pivot Slicers 283 Understanding Slicers 283 Creating a Standard Slicer 286 Getting Fancy with Slicer Customizations 288 Size and placement 288 Data item columns 288 Other slicer settings 289 Creating your own slicer style 289 Controlling Multiple Pivot Tables with One Slicer 293 Creating a Timeline Slicer 294 Using Slicers as Form Controls 296 Using Slicers on Excel Table Objects 300 Chapter 13: Sharing Your Workbook with the Outside World 303 Protecting Your Dashboards and Reports 303 Securing access to the entire workbook 304 Limiting access to specific worksheet ranges 307 Protecting the workbook structure 310 Linking Your Excel Dashboards to PowerPoint 311 Creating a link between Excel and PowerPoint 312 Manually updating links to capture updates 313 Turning off automatic updating of links 314 Distributing Your Dashboards via a PDF 316 Distributing Your Dashboards to OneDrive 318 Limitations When Publishing to the Web 321 Part 5: The Part of Tens 323 Chapter 14: Ten Chart Design Principles 325 Avoid Fancy Formatting 325 Skip the Unnecessary Chart Junk 327 Format Large Numbers Where Possible 329 Use Data Tables Instead of Data Labels 330<.