You are previewing Excel® Dashboards & Reports.
O'Reilly logo
Excel® Dashboards & Reports

Book Description

The go to resource for how to use Excel dashboards and reports to better conceptualize data

Many Excel books do an adequate job of discussing the individual functions and tools that can be used to create an "Excel Report." What they don't offer is the most effective ways to present and report data. Offering a comprehensive review of a wide array of technical and analytical concepts, Excel Reports and Dashboards helps Excel users go from reporting data with simple tables full of dull numbers, to presenting key information through the use of high-impact, meaningful reports and dashboards that will wow management both visually and substantively.

  • Details how to analyze large amounts of data and report the results in a meaningful, eye-catching visualization

  • Describes how to use different perspectives to achieve better visibility into data, as well as how to slice data into various views on the fly

  • Shows how to automate redundant reporting and analyses

Part technical manual, part analytical guidebook, Excel Dashboards and Reports is the latest addition to the Mr. Spreadsheet's Bookshelf series and is the leading resource for learning to create dashboard reports in an easy-to-use format that's both visually attractive and effective.

Table of Contents

  1. Copyright
  2. About the Author
  3. About the Technical Editor
  4. Author's Acknowledgments
  5. Preface1
  6. INTRODUCTION
    1. What You Need to Know
    2. What You Need to Have
    3. Conventions in This Book
      1. Keyboard conventions
        1. Formula listings
        2. VBA code listings
        3. Key names
        4. Functions, procedures, and named ranges
      2. Mouse conventions
      3. What the icons mean
    4. How This Book Is Organized
      1. Part I: Moving from Spreadsheets to Dashboards
      2. Part II: Excel Chart Basics
      3. Part III: Going Beyond Tables and Charts
      4. Part IV: Creating Advanced Dashboard Components
      5. Part V: Automating Your Dashboards and Reports
      6. Part VI: Working with the Outside World
    5. About the Companion Web Site
    6. About the Power Utility Pak Offer
    7. Reach Out
  7. I. Moving from Spreadsheets to Dashboards
    1. 1. Introducing Dashboards
      1. 1.1. What are Dashboards and Reports?
        1. 1.1.1. Defining reports
        2. 1.1.2. Defining dashboards
      2. 1.2. Establish the User Requirements
        1. 1.2.1. Define the message(s)
        2. 1.2.2. Establish the audience
        3. 1.2.3. Define the performance measures
        4. 1.2.4. List the required data sources
        5. 1.2.5. Define the dimensions and filters
        6. 1.2.6. Determine the need for drill down details
        7. 1.2.7. Establish the update schedule
      3. 1.3. A Quick Look at Dashboard Design Principles
        1. 1.3.1. Rule number 1: Keep it Simple
          1. 1.3.1.1. Don't turn your dashboard into a Data Mart
          2. 1.3.1.2. Forget about the fancy formatting
          3. 1.3.1.3. Skip the unnecessary chart junk
          4. 1.3.1.4. Limit each dashboard to one viewable page or screen
        2. 1.3.2. Use layout and placement to draw focus
        3. 1.3.3. Format numbers effectively
        4. 1.3.4. Use titles and labels effectively
      4. 1.4. Key Questions to Ask Before Distributing Your Dashboard
        1. 1.4.1. Does my dashboard present the right information?
        2. 1.4.2. Does everything on my dashboard have a purpose?
        3. 1.4.3. Does my dashboard prominently display the key message?
        4. 1.4.4. Can I maintain this dashboard?
        5. 1.4.5. Does my dashboard clearly display its scope and shelf life?
        6. 1.4.6. Is my dashboard well documented?
        7. 1.4.7. Is my dashboard user-friendly?
        8. 1.4.8. Is my dashboard accurate?
    2. 2. Developing Your Data Model
      1. 2.1. Building a Data Model
        1. 2.1.1. Separating the data, analysis, and presentation layers
          1. 2.1.1.1. The data layer
          2. 2.1.1.2. The analysis layer
          3. 2.1.1.3. The presentation layer
      2. 2.2. Data Model Best Practices
        1. 2.2.1. Avoid storing excess data
        2. 2.2.2. Use tabs to document and organize your data model
        3. 2.2.3. Test your data model before building presentation components
      3. 2.3. Excel Functions for Your Data Model
        1. 2.3.1. The VLOOKUP function
          1. 2.3.1.1. VLOOKUP basics
          2. 2.3.1.2. Adding VLOOKUP formulas to a data model
          3. 2.3.1.3. Using drop-down lists
        2. 2.3.2. The HLookup function
          1. 2.3.2.1. HLOOKUP basics
          2. 2.3.2.2. Applying HLOOKUP formulas to a data model
        3. 2.3.3. The Sumproduct function
          1. 2.3.3.1. SUMPRODUCT basics
          2. 2.3.3.2. A twist on the SUMPRODUCT function
          3. 2.3.3.3. Applying SUMPRODUCT formulas to a data model
        4. 2.3.4. The CHOOSE function
          1. 2.3.4.1. CHOOSE basics
          2. 2.3.4.2. Applying CHOOSE formulas to a data model
      4. 2.4. Working with Excel Tables
        1. 2.4.1. Converting a range to an Excel table
        2. 2.4.2. Converting an Excel table back to a range
  8. II. Excel Chart Basics
    1. 3. Introducing Excel Charts
      1. 3.1. What Is a Chart?
      2. 3.2. How Excel Handles Charts
        1. 3.2.1. Embedded charts
        2. 3.2.2. Chart sheets
      3. 3.3. Parts of a Chart
      4. 3.4. Basic Steps for Creating a Chart
        1. 3.4.1. Creating the chart
        2. 3.4.2. Switching the row and column orientation
        3. 3.4.3. Changing the chart type
        4. 3.4.4. Applying a chart layout
        5. 3.4.5. Applying a chart style
        6. 3.4.6. Adding and deleting chart elements
        7. 3.4.7. Formatting chart elements
      5. 3.5. Working with Charts
        1. 3.5.1. Moving and resizing a chart
        2. 3.5.2. Converting an embedded chart to a chart sheet
        3. 3.5.3. Copying a chart
        4. 3.5.4. Deleting a chart
        5. 3.5.5. Adding chart elements
        6. 3.5.6. Moving and deleting chart elements
        7. 3.5.7. Formatting chart elements
        8. 3.5.8. Copying a chart's formatting
        9. 3.5.9. Renaming a chart
        10. 3.5.10. Printing charts
    2. 4. Understanding Chart Types
      1. 4.1. Conveying a Message with a Chart
      2. 4.2. Choosing a Chart Type
      3. 4.3. Excel's Chart Types
        1. 4.3.1. Column charts
        2. 4.3.2. Bar charts
        3. 4.3.3. Line charts
        4. 4.3.4. Pie charts
        5. 4.3.5. Scatter charts
        6. 4.3.6. Area charts
        7. 4.3.7. Doughnut charts
        8. 4.3.8. Radar charts
        9. 4.3.9. Surface charts
        10. 4.3.10. Bubble charts
        11. 4.3.11. Stock charts
      4. 4.4. Creating Combination Charts
      5. 4.5. Creating and Using Chart Templates
    3. 5. Working with Chart Series
      1. 5.1. Specifying the Data for Your Chart
      2. 5.2. Adding a New Series to a Chart
        1. 5.2.1. Adding a new series by copying a range
        2. 5.2.2. Adding a new series by extending the range highlight
        3. 5.2.3. Adding a new series using the Select Data Source dialog box
        4. 5.2.4. Adding a new series by typing a new SERIES formula
      3. 5.3. Deleting a Chart Series
      4. 5.4. Modifying the Data Range for a Chart Series
        1. 5.4.1. Using range highlighting to change series data
        2. 5.4.2. Using the Select Data Source dialog box to change series data
        3. 5.4.3. Editing the SERIES formula to change series data
      5. 5.5. Understanding Series Names
        1. 5.5.1. Changing a series name
        2. 5.5.2. Deleting a series name
      6. 5.6. Adjusting the Series Plot Order
      7. 5.7. Charting a Noncontiguous Range
      8. 5.8. Using Series on Different Sheets
      9. 5.9. Handling Missing Data
      10. 5.10. Controlling a Data Series by Hiding Data
      11. 5.11. Unlinking a Chart Series from Its Data Range
        1. 5.11.1. Converting a chart to a picture
        2. 5.11.2. Converting a range reference to arrays
      12. 5.12. Working with Multiple Axes
        1. 5.12.1. Creating a secondary value axis
        2. 5.12.2. Creating a chart with four axes
    4. 6. Formatting and Customizing Charts
      1. 6.1. Chart Formatting Overview
        1. 6.1.1. Selecting chart elements
          1. 6.1.1.1. Selecting with the mouse
          2. 6.1.1.2. Selecting with the keyboard
          3. 6.1.1.3. Selecting with the Chart Elements control
        2. 6.1.2. Common chart elements
        3. 6.1.3. UI choices for formatting
          1. 6.1.3.1. Formatting by using the Ribbon
          2. 6.1.3.2. Formatting by using the Mini Toolbar
          3. 6.1.3.3. Formatting by using the Format dialog box
      2. 6.2. Adjusting Fills and Borders: General Procedures
        1. 6.2.1. About the Fill tab
        2. 6.2.2. Formatting borders
      3. 6.3. Formatting Chart Background Elements
        1. 6.3.1. Working with the chart area
        2. 6.3.2. Working with the plot area
      4. 6.4. Formatting Chart Series
        1. 6.4.1. Basic series formatting
        2. 6.4.2. Using pictures and graphics for series formatting
        3. 6.4.3. Additional series options
      5. 6.5. Working with Chart Titles
        1. 6.5.1. Adding titles to a chart
        2. 6.5.2. Changing title text
        3. 6.5.3. Formatting title text
        4. 6.5.4. Linking title text to a cell
      6. 6.6. Working with a Chart's Legend
        1. 6.6.1. Adding or removing a legend
        2. 6.6.2. Moving or resizing a legend
        3. 6.6.3. Formatting a legend
        4. 6.6.4. Changing the legend text
        5. 6.6.5. Deleting a legend entry
        6. 6.6.6. Identifying series without using a legend
      7. 6.7. Working with Chart Axes
        1. 6.7.1. Value axis versus category axis
        2. 6.7.2. Value axis scales
        3. 6.7.3. Using time-scale axes
        4. 6.7.4. Creating a multiline category axis
        5. 6.7.5. Removing axes
        6. 6.7.6. Axis number formats
      8. 6.8. Working with Gridlines
        1. 6.8.1. Adding or removing gridlines
      9. 6.9. Working with Data Labels
        1. 6.9.1. Adding or removing data labels
        2. 6.9.2. Editing data labels
        3. 6.9.3. Problems and limitations with data labels
      10. 6.10. Working with a Chart Data Table
        1. 6.10.1. Adding and removing a data table
        2. 6.10.2. Problems and limitations with data tables
  9. III. Going Beyond Tables and Charts
    1. 7. Using Pivot Tables
      1. 7.1. Introducing the Pivot Table
        1. 7.1.1. Anatomy of a pivot table
          1. 7.1.1.1. Values area
          2. 7.1.1.2. Row Labels area
          3. 7.1.1.3. Column Labels area
          4. 7.1.1.4. Report Filter area
        2. 7.1.2. Creating the basic pivot table
          1. 7.1.2.1. Laying out the pivot table
          2. 7.1.2.2. Modifying the pivot table
          3. 7.1.2.3. Changing the pivot table view
          4. 7.1.2.4. Updating your pivot table
      2. 7.2. Customizing Your Pivot Table
        1. 7.2.1. Renaming the fields
        2. 7.2.2. Formatting numbers
        3. 7.2.3. Changing summary calculations
        4. 7.2.4. Suppressing subtotals
        5. 7.2.5. Removing all subtotals at one time
        6. 7.2.6. Removing the subtotals for only one field
        7. 7.2.7. Removing grand totals
        8. 7.2.8. Hiding and showing data items
        9. 7.2.9. Hiding or showing items without data
        10. 7.2.10. Sorting your pivot table
      3. 7.3. Examples of Filtering Your Data
        1. 7.3.1. Producing top and bottom views
        2. 7.3.2. Creating views by month, quarter, and year
        3. 7.3.3. Creating a percent distribution view
        4. 7.3.4. Creating a YTD totals view
        5. 7.3.5. Creating a month-over-month variance view
    2. 8. Using Pivot Charts
      1. 8.1. Getting Started with Pivot Charts
        1. 8.1.1. Creating a pivot chart
        2. 8.1.2. A pivot chart example
      2. 8.2. Working with Pivot Charts
        1. 8.2.1. Hiding field buttons
        2. 8.2.2. Moving a pivot chart
        3. 8.2.3. Working with slicers
          1. 8.2.3.1. Adding slicers
          2. 8.2.3.2. Customizing slicers
          3. 8.2.3.3. Filtering slicers
      3. 8.3. More Pivot Chart Examples
        1. 8.3.1. Question 1
        2. 8.3.2. Question 2
        3. 8.3.3. Question 3
        4. 8.3.4. Question 4
        5. 8.3.5. Question 5
        6. 8.3.6. Question 6
      4. 8.4. Creating a Frequency Distribution Chart
        1. 8.4.1. Specifying which rows to plot
    3. 9. Using Excel Sparklines
      1. 9.1. Introducing Sparklines
      2. 9.2. Creating Sparklines
      3. 9.3. Customizing Sparklines
        1. 9.3.1. Sizing and merging sparkline cells
        2. 9.3.2. Handling hidden or missing data
        3. 9.3.3. Changing the sparkline type
        4. 9.3.4. Changing sparkline colors and line width
        5. 9.3.5. Using color to emphasize key data points
        6. 9.3.6. Adjusting sparkline axis scaling
        7. 9.3.7. Faking a reference line
        8. 9.3.8. Specifying a date axis
        9. 9.3.9. Auto-updating sparkline ranges
        10. 9.3.10. Displaying a sparkline for a dynamic range
    4. 10. Chartless Visualization Techniques
      1. 10.1. Dynamic Labels
      2. 10.2. Linking Formulas to Text Boxes
      3. 10.3. Excel's Camera Tool
        1. 10.3.1. Finding the Camera tool
        2. 10.3.2. Using the Camera tool
        3. 10.3.3. Enhancing a dashboard with the Camera tool
          1. 10.3.3.1. Consolidating disparate ranges into one print area
          2. 10.3.3.2. Rotating objects to save time
          3. 10.3.3.3. Creating small charts
      4. 10.4. Formula-Driven Labels
        1. 10.4.1. In-cell charting
        2. 10.4.2. Using fancy fonts
        3. 10.4.3. Using symbols
  10. IV. Creating Advanced Dashboard Components
    1. 11. Components that Show Trending
      1. 11.1. Trending Dos and Don'ts
        1. 11.1.1. Using chart types appropriate for trending
          1. 11.1.1.1. Using line charts
          2. 11.1.1.2. Using area charts
          3. 11.1.1.3. Using column charts
        2. 11.1.2. Starting the vertical scale at zero
        3. 11.1.3. Leveraging Excel's logarithmic scale
        4. 11.1.4. Applying creative label management
          1. 11.1.4.1. Abbreviating instead of changing alignment
          2. 11.1.4.2. Implying labels to reduce clutter
          3. 11.1.4.3. Going vertical when you have too many data points for horizontal
          4. 11.1.4.4. Nesting labels for clarity
      2. 11.2. Comparative Trending
        1. 11.2.1. Creating side-by-side time comparisons
        2. 11.2.2. Creating stacked time comparisons
        3. 11.2.3. Trending with a secondary axis
      3. 11.3. Emphasizing Periods of Time
        1. 11.3.1. Formatting specific periods
        2. 11.3.2. Using dividers to mark significant events
        3. 11.3.3. Representing forecasts in your trending components
      4. 11.4. Other Trending Techniques
        1. 11.4.1. Avoiding overload with directional trending
        2. 11.4.2. Smoothing data
          1. 11.4.2.1. Smoothing with Excel's moving average functionality
          2. 11.4.2.2. Creating your own smoothing calculation
    2. 12. Components that Group Data
      1. 12.1. Listing Top and Bottom Values
        1. 12.1.1. Organizing source data
        2. 12.1.2. Using pivot tables for interactive views
      2. 12.2. Using Histograms to Track Relationships and Frequency
        1. 12.2.1. Adding formulas to group data
        2. 12.2.2. Adding a cumulative percent
        3. 12.2.3. Using a pivot table
      3. 12.3. Emphasizing Top Values in Charts
    3. 13. Components that Show Performance against a Target
      1. 13.1. Showing Performance with Variances
      2. 13.2. Showing Performance against Organizational Trends
      3. 13.3. Using a Thermometer-Style Chart
      4. 13.4. Using a Bullet Graph
        1. 13.4.1. Creating a bullet graph
        2. 13.4.2. Adding data to your bullet graph
        3. 13.4.3. Final thoughts on formatting bullet graphs
          1. 13.4.3.1. Creating qualitative bands
          2. 13.4.3.2. Creating horizontal bullet graphs
      5. 13.5. Showing Performance against a Target Range
  11. V. Automating Your Dashboards and Reports
    1. 14. Macro-Charged Reporting
      1. 14.1. Why Use a Macro?
      2. 14.2. Introducing the Macro Recorder
        1. 14.2.1. The Record Macro dialog box
        2. 14.2.2. Recording macros with absolute references
        3. 14.2.3. Recording macros with relative references
        4. 14.2.4. Assigning a macro to a button
      3. 14.3. Enabling Macros in Excel 2010
        1. 14.3.1. Viewing the new Excel security message
        2. 14.3.2. Setting up trusted locations
      4. 14.4. Excel Macro Examples
        1. 14.4.1. Building navigation buttons
        2. 14.4.2. Dynamically rearranging pivot table data
        3. 14.4.3. Offering one-touch reporting options
    2. 15. Adding Interactive Controls to Your Dashboard
      1. 15.1. Getting Started with Form Controls
        1. 15.1.1. Finding Form controls
        2. 15.1.2. Adding a control to a worksheet
      2. 15.2. Using the Button Control
      3. 15.3. Using the Check Box Control
        1. 15.3.1. Check box example: Toggling a chart series on and off
      4. 15.4. Using the Option Button Control
        1. 15.4.1. Option button example: Showing many views through one chart
      5. 15.5. Using the Combo Box Control
        1. 15.5.1. Combo box example: Controlling multiple pivot tables with one combo box
      6. 15.6. Using the List Box Control
        1. 15.6.1. List box example: Controlling multiple charts with one selector
  12. VI. Working with the Outside World
    1. 16. Importing Microsoft Access Data into Excel
      1. 16.1. The Drag-and-Drop Method
      2. 16.2. The Microsoft Access Export Wizard
      3. 16.3. The Get External Data Icon
      4. 16.4. Microsoft Query
        1. 16.4.1. Start MS Query
        2. 16.4.2. Set up your data source connection
        3. 16.4.3. Build your custom data pull
    2. 17. Sharing Your Work with the Outside World
      1. 17.1. Securing Your Dashboards and Reports
        1. 17.1.1. Securing access to the entire workbook
          1. 17.1.1.1. Permitting read-only access unless a password is given
          2. 17.1.1.2. Requiring a password to open an Excel file
          3. 17.1.1.3. Removing workbook-level protection
        2. 17.1.2. Limiting access to specific worksheet ranges
          1. 17.1.2.1. Unlocking editable ranges
          2. 17.1.2.2. Applying worksheet protection
          3. 17.1.2.3. Removing worksheet protection
        3. 17.1.3. Protecting the workbook structure
      2. 17.2. Linking Your Excel Dashboards to PowerPoint
        1. 17.2.1. Creating the link between Excel and PowerPoint
        2. 17.2.2. Manually updating links to capture updates
        3. 17.2.3. Automatically updating links
      3. 17.3. Distributing Your Dashboards Via a PDF