Excel Dashboards and Reports For Dummies, 2nd Edition

Table of contents

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