Excel Dashboards and Reports for Dummies

Each book covers all the necessary information a beginner needs to know about a particular topic, providing an index for easy reference and using the series' signature set of symbols to clue the reader in to key topics, categorized under such titles as Tip, Remember, Warning!, Technical Stuff and True Story. Original.

List of contents

Introduction 1About This Book 2Foolish Assumptions 3Icons Used In This Book 3Beyond the Book 4Where to Go from Here 5Part I: Getting Started with Excel Dashboards and Reports 7Chapter 1: Getting in the Dashboard State of Mind 9Defining Dashboards and Reports 9Defining reports 10Defining dashboards 11Preparing for Greatness 12Establish the audience and purpose for the dashboard 12Delineate the measures for the dashboard 13Catalog the required data sources 14Define the dimensions and filters for the dashboard 15Determine the need for drill-down features 15Establish the refresh schedule 16A Quick Look at Dashboard Design Principles 16Rule number 1: Keep it simple 17Use layout and placement to draw focus 18Format numbers effectively 19Use titles and labels effectively 20Chapter 2: Building a Super Model 21Data Modeling Best Practices 22Separating data, analysis, and presentation 22Starting with appropriately structured data 25Avoiding turning your data model into a database 28Using tabs to document and organize your data model 29Testing your data model before building reporting components on top of it 31Excel Functions That Really Deliver 32The VLOOKUP function 32The HLookup function 36The Sumproduct function 37The Choose function 41Using Smart Tables That Expand with Data 43Converting a range to an Excel table 44Converting an Excel table back to a range 46Part II: Building Basic Dashboard Components 47Chapter 3: Dressing Up Your Data Tables 49Table Design Principles 49Use colors sparingly 50De-emphasize borders 52Use effective number formatting 54Subdue your labels and headers 55Getting Fancy with Custom Number Formatting 57Number formatting basics 57Formatting numbers in thousands and millions 59Hiding and suppressing zeroes 62Applying custom format colors 62Formatting dates and times 63Chapter 4: Sparking Inspiration with Sparklines 65Introducing Sparklines 65Understanding Sparklines 67Creating sparklines 68Understanding sparkline groups 70Customizing Sparklines 71Sizing and merging sparkline cells 71Handling hidden or missing data 72Changing the sparkline type 73Changing sparkline colors and line width 73Using color to emphasize key data points 73Adjusting sparkline axis scaling 74Faking a reference line 75Specifying a date axis 77Autoupdating sparkline ranges 78Chapter 5: Formatting Your Way to Visualizations 79Enhancing Reports with Conditional Formatting 79Applying basic conditional formatting 80Adding your own formatting rules manually 88Show only one icon 91Show Data Bars and icons outside of cells 94Representing trends with Icon Sets 96Using Symbols to Enhance Reporting 98The Magical Camera Tool 102Finding the Camera tool 102Using the Camera tool 103Enhancing a dashboard with the Camera tool 105Chapter 6: The Pivotal Pivot Table 107An Introduction to the Pivot Table 107The Four Areas of a Pivot Table 108Values area 108Row area 109Column area 109Filter area 110Creating Your First Pivot Table 111Changing and rearranging your pivot table 114Adding a report filter 115Keeping your pivot table fresh 116Customizing Your Pivot Table Reports 119Changing the pivot table layout 119Customizing field names 120Applying numeric formats to data fields 122Changing summary calculations 122Suppressing subtotals 124Showing and hiding data items 127Hiding or showing items without data 128Sorting your pivot table 132Creating Useful Pivot-Driven Views 133Producing top and bottom views 133Creating views by month, quarter, and year 137Creating a percent distribution view 139Creating a YTD totals view 141Creating a month-over-month variance view 142Part III: Building Advanced Dashboard Components 145Chapter 7: Charts That Show Trending 147Trending Dos and Don'ts 147Using chart types appropriate for trending 148Starting the vertical scale at zero 150Leveraging Excel's logarithmic scale 151Applying creative label management 153Comparative Trending 156Creating side-by-side time comparisons 156Creating stacked time comparisons 158Trending with a secondary axis 160Emphasizing Periods of Time 163Formatting specific periods 163Using dividers to mark significant events 165Representing forecasts in your trending components 166Other Trending Techniques 167Avoiding overload with directional trending 167Smoothing data 168Chapter 8: Grouping and Bucketing Data 173Creating Top and Bottom Displays 173Incorporating top and bottom displays into dashboards 174Using pivot tables to get top and bottom views 175Using Histograms to Track Relationships and Frequency 178Adding formulas to group data 179Adding a cumulative percent 183Using a pivot table to create a histogram 185Emphasizing Top Values in Charts 187Chapter 9: Displaying Performance against a Target 191Showing Performance with Variances 191Showing Performance against Organizational Trends 193Using a Thermometer-Style Chart 194Using a Bullet Graph 195Creating a bullet graph 196Adding data to your bullet graph 200Final thoughts on formatting bullet graphs 200Showing Performance against a Target Range 203Part IV: Advanced Reporting Techniques 207Chapter 10: Macro-Charged Dashboarding 209Why Use a Macro? 209Recording Your First Macro 210Running Your Macros 214Enabling and Trusting Macros 217Macro-enabled file extensions 217Enabling macro content 217Setting up trusted locations 218Excel Macro Examples 219Building navigation buttons 219Dynamically rearranging pivot table data 220Offering one-touch reporting options 221Chapter 11: Giving Users an Interactive Interface 223Getting Started with Form Controls 223Finding Form controls 224Adding a control to a worksheet 226Using the Button Control 227Using the Check Box Control 228Check box example: Toggling a chart series on and off 229Using the Option Button Control 232Option Button Example: Showing Many Views through One Chart 233Using the Combo Box Control 236Combo Box Example: Changing Chart Data with a Drop-Down Selector 237Using the List Box Control 239List Box Example: Controlling Multiple Charts with One Selector 241Chapter 12: Adding Interactivity with Pivot Slicers 245Understanding Slicers 245Creating a Standard Slicer 247Formatting Slicers 250Size and placement 250Data item columns 250Slicer color and style 251Other slicer settings 252Controlling Multiple Pivot Tables with One Slicer 253Creating a Timeline Slicer 254Using Slicers as Form Controls 256Part V: Working with the Outside World 261Chapter 13: Using External Data for Your Dashboards and Reports 263Importing Data from Microsoft Access 263The drag-and-drop method 264The Microsoft Access Export wizard 265The Get External Data icon 266Importing Data from SQL Server 271Chapter 14: Sharing Your Workbook with the Outside World 275Protecting Your Dashboards and Reports 275Securing access to the entire workbook 275Limiting access to specific worksheet ranges 279Protecting the workbook structure 283Linking Your Excel Dashboards to PowerPoint 284Creating a link between Excel and PowerPoint 284Manually updating links to capture updates 286Automatically updating links 288Distributing Your Dashboards via a PDF 289Distributing Your Dashboards to SkyDrive 291Limitations when Publishing to the Web 294Part VI: The Part of Tens 295Chapter 15: Ten Chart Design Principles 297Avoid Fancy Formatting 297Skip the Unnecessary Chart Junk 299Format Large Numbers Where Possible 301Use Data Tables instead of Data Labels 302Make Effective Use of Chart Titles 304Sort Your Data before Charting 304Limit the Use of Pie Charts 305Don't Be Afraid to Parse Data into Separate Charts 306Maintain Appropriate Aspect Ratios 307Don't Be Afraid to Use Something Other Than a Chart 308Chapter 16: Ten Questions to Ask Before Distributing Your Dashboard 309Does My Dashboard Present the Right Information? 309Does Everything on My Dashboard Have a Purpose? 309Does My Dashboard Prominently Display the Key Message? 310Can I Maintain This Dashboard? 310Does My Dashboard Clearly Display Its Scope and Shelf Life? 311Is My Dashboard Well Documented? 311Is My Dashboard Overwhelmed with Formatting and Graphics? 312Does My Dashboard Overuse Charts When Tables Will Do? 312Is My Dashboard User-Friendly? 313Is My Dashboard Accurate? 314Index 315

Product details

• Authors: Michael Alexander
• Publisher: Wiley, John and Sons Ltd
• Languages: English
• Product format: Paperback / Softback
• Released: 21.03.2014
• EAN: 9781118842249
• ISBN: 978-1-118-84224-9
• No. of pages: 336
• Series: For Dummies (Computers); For Dummies (Computers)
• Subject: Natural sciences, medicine, IT, technology > IT, data processing > Application software