Excel® 2007 Dashboards & Reports For Dummies®

Excel® 2007 Dashboards & Reports For Dummies®

Released March 2008
Publisher(s): For Dummies
ISBN: 9780470228142

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

What’s the use of putting out reports that no one reads? Properly created dashboards are graphical representations that put data in a context for your audience, and they look really cool! How cool? You’ll find out when you see the dazzling examples in Excel 2007 Dashboards & Reports For Dummies. And, before long, everyone’s eyes will be riveted to your dashboards and reports too!

This revolutionary guide shows you how to turn Excel into your own personal Business Intelligence tool. You’ll learn the fundamentals of using Excel 2007 to go beyond simple tables to creating dashboard-studded reports that wow management. Get ready to catch dashboard fever as you find out how to use basic analysis techniques, build advanced dashboard components, implement advanced reporting techniques, and import external date into your Excel reports. Discover how to:

  • Unleash the power of Excel as a business intelligence tool

  • Create dashboards that communicate and get noticed

  • Think about your data in a new way

  • Present data more effectively and increase the value of your reports

  • Create dynamic labels that support visualization

  • Represent time and seasonal trending

  • Group and bucket data

  • Display and measure values versus goals

  • Implement macro-charged reporting

Using Excel 2007 as a BI tool is the most cost-efficient way for organizations of any size create powerful and insightful reports and distribute throughout the enterprise. And Excel 2007 Dashboards and Reports for Dummies is the fastest you for you to catch dashboard fever!

Table of contents

  1. Copyright
  2. About the Author
  3. Author's Acknowledgements
  5. Introduction
    1. About This Book
    2. Foolish Assumptions
    3. How This Book Is Organized
      1. Part I: Making the Move to Dashboards
      2. Part II: Building Basic Dashboard Components
      3. Part III: Building Advanced Dashboard Components
      4. Part IV: Advanced Reporting Techniques
      5. Part V: Working with the Outside World
      6. Part VI: The Part of Tens
    4. Sample Files for This Book
    5. Icons Used In This Book
    6. Where to Go from Here
  6. I. Making the Move to Dashboards
    1. 1. Getting in the Dashboard State of Mind
      1. 1.1. Defining Dashboards and Reports
        1. 1.1.1. Defining reports
        2. 1.1.2. Defining dashboards
      2. 1.2. Preparing for Greatness
        1. 1.2.1. Establish the audience and purpose for the dashboard
        2. 1.2.2. Delineate the measures for the dashboard
        3. 1.2.3. Catalog the required data sources
        4. 1.2.4. Define the dimensions and filters for the dashboard
        5. 1.2.5. Determine the need for drill-down features
        6. 1.2.6. Establish the refresh schedule
      3. 1.3. A Quick Look at Dashboard Design Principles
        1. 1.3.1. Rule number 1: Keep it simple
        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
    2. 2. Building a Super Model
      1. 2.1. Data Modeling Best Practices
        1. 2.1.1. Separating data, analysis, and presentation
        2. 2.1.2. Starting with appropriately structured data
        3. 2.1.3. Avoiding turning your data model into a database
        4. 2.1.4. Using tabs to document and organize your data model
        5. 2.1.5. Testing your data model before building reporting components on top of it
      2. 2.2. Excel Functions That Really Deliver
        1. 2.2.1. The VLOOKUP function
        2. 2.2.2. The HLOOKUP function
        3. 2.2.3. The SUMPRODUCT function
        4. 2.2.4. The CHOOSE function
      3. 2.3. Using Smart Tables That Expand with Data
        1. 2.3.1. Converting a range to an Excel table
        2. 2.3.2. Converting an Excel table back to a range
  7. II. Building Basic Dashboard Components
    1. 3. The Pivotal Pivot Table
      1. 3.1. An Introduction to the Pivot Table
      2. 3.2. The Four Areas of a Pivot Table
        1. 3.2.1. Values area
        2. 3.2.2. Row area
        3. 3.2.3. Column area
        4. 3.2.4. Filter area
      3. 3.3. Creating Your First Pivot Table
        1. 3.3.1. Changing and rearranging your pivot table
        2. 3.3.2. Adding a report filter
        3. 3.3.3. Keeping your pivot table fresh
      4. 3.4. Customizing Your Pivot Table Reports
        1. 3.4.1. Changing the pivot table layout
        2. 3.4.2. Customizing field names
        3. 3.4.3. Applying numeric formats to data fields
        4. 3.4.4. Changing summary calculations
        5. 3.4.5. Suppressing subtotals
        6. 3.4.6. Showing and hiding data items
        7. 3.4.7. Hiding or showing items without data
        8. 3.4.8. Sorting your pivot table
      5. 3.5. Creating Useful Pivot-Driven Views
        1. 3.5.1. Producing top and bottom views
        2. 3.5.2. Creating views by month, quarter, and year
        3. 3.5.3. Creating a percent distribution view
        4. 3.5.4. Creating a YTD totals view
        5. 3.5.5. Creating a month-over-month variance view
    2. 4. Excel Charts for the Uninitiated
      1. 4.1. Chart Building Basics
        1. 4.1.1. A review of the most-commonly-used chart types
        2. 4.1.2. Preparing data for different chart types
        3. 4.1.3. Creating a chart from scratch
        4. 4.1.4. Charting disparate data
      2. 4.2. Common Chart Tasks
        1. 4.2.1. Resizing and moving charts
        2. 4.2.2. Changing chart type
        3. 4.2.3. Creating a combination chart
        4. 4.2.4. Selecting and formatting chart elements
      3. 4.3. Working with Pivot Charts
        1. 4.3.1. Pivot chart fundamentals
        2. 4.3.2. Pivot charts and the x and y axes
        3. 4.3.3. Pivot charts formatting limitations
    3. 5. The New World of Conditional Formatting
      1. 5.1. Applying Basic Conditional Formatting
        1. 5.1.1. Highlight Cells Rules
        2. 5.1.2. Top/Bottom Rules
        3. 5.1.3. Data Bars, Color Scales, and Icon Sets
      2. 5.2. Getting Fancy with Conditional Formatting
        1. 5.2.1. Adding your own formatting rules manually
        2. 5.2.2. Showing only one icon
        3. 5.2.3. Showing Data Bars and icons outside cells
        4. 5.2.4. Representing trends with Icon Sets
        5. 5.2.5. Building a legend for your conditional formatting
        6. 5.2.6. Using conditional formatting with pivot tables
    4. 6. The Art of Dynamic Labeling
      1. 6.1. Creating a Basic Dynamic Label
      2. 6.2. Adding Layers of Analysis with Dynamic Labels
      3. 6.3. Excel's Mysterious Camera Tool
        1. 6.3.1. Finding the Camera tool
        2. 6.3.2. The basics of using the Camera tool
        3. 6.3.3. Cool uses for the Camera tool
      4. 6.4. Formula-Driven Visualizations
        1. 6.4.1. In-cell charting without charts or conditional formatting
        2. 6.4.2. Creating visualizations with Wingdings and things
  8. III. Building Advanced Dashboard Components
    1. 7. Components That Show Trending
      1. 7.1. Trending Dos and Don'ts
        1. 7.1.1. Using chart types appropriate for trending
        2. 7.1.2. Starting the vertical scale at zero
        3. 7.1.3. Leveraging Excel's logarithmic scale
        4. 7.1.4. Applying creative label management
      2. 7.2. Comparative Trending
        1. 7.2.1. Creating side-by-side time comparisons
        2. 7.2.2. Creating stacked time comparisons
        3. 7.2.3. Trending with a secondary axis
      3. 7.3. Highlighting Periods of Time
        1. 7.3.1. Formatting specific periods
        2. 7.3.2. Using dividers to mark significant events
        3. 7.3.3. Representing forecasts in your trending components
      4. 7.4. Other Trending Techniques
        1. 7.4.1. Avoiding overload with directional trending
        2. 7.4.2. Smoothing data
        3. 7.4.3. Catching sparkline fever
    2. 8. Components That Group and Bucket Data
      1. 8.1. Creating Top and Bottom Displays
        1. 8.1.1. Incorporating top and bottom displays into dashboards
        2. 8.1.2. Using pivot tables to create interactive top and bottom views
      2. 8.2. Using Histograms to Track Relationships and Frequency
        1. 8.2.1. Creating formula-driven histograms
        2. 8.2.2. Adding a cumulative percent to your histogram
        3. 8.2.3. Creating a histogram with a pivot table
      3. 8.3. Highlighting Top Values in Charts
    3. 9. Components That Display Performance against a Target
      1. 9.1. Showing Performance with Variances
      2. 9.2. Showing Performance against Organizational Trends
      3. 9.3. Using Thermometer-Style Charts to Display Performance
      4. 9.4. An Introduction to the Bullet Graph
        1. 9.4.1. Creating your first bullet graph
        2. 9.4.2. Adding data to your bullet graph
        3. 9.4.3. Final thoughts on formatting bullet graphs
      5. 9.5. Showing Performance against a Target Range
  9. IV. Advanced Reporting Techniques
    1. 10. Macro-Charged Reporting
      1. 10.1. Why Use a Macro?
      2. 10.2. Introducing the Macro Recorder
        1. 10.2.1. The Macro Recorder user interface
        2. 10.2.2. Recording macros with absolute references
        3. 10.2.3. Recording macros with relative references
        4. 10.2.4. Assigning a macro to a button
      3. 10.3. Macro Security in Excel 2007
        1. 10.3.1. The short-term solution to disabled macros
        2. 10.3.2. The long-term solution to disabled macros
      4. 10.4. Excel Macro Examples
        1. 10.4.1. Building navigation buttons
        2. 10.4.2. Dynamically rearranging pivot table data
        3. 10.4.3. Offering one-touch reporting options
    2. 11. Giving Users an Interactive Interface
      1. 11.1. Introducing Form Controls
      2. 11.2. Adding and Configuring Controls
      3. 11.3. Using the Button Control
      4. 11.4. Using the Check Box Control
      5. 11.5. Check Box Example: Toggling a Chart Series On and Off
      6. 11.6. Using Option Button Controls
      7. 11.7. Option Button Example: Showing Many Views through One Chart
      8. 11.8. Using the Combo Box Control
      9. 11.9. Combo Box Example: Controlling Multiple Pivot Tables with One Combo Box
      10. 11.10. Using the List Box Control
      11. 11.11. List Box Example: Controlling Multiple Charts with One Selector
  10. V. Working with the Outside World
    1. 12. Using External Data for Your Dashboards and Reports
      1. 12.1. Using the Get External Data Group
        1. 12.1.1. Importing Access data with the Get External Data Group
        2. 12.1.2. Importing SQL Server data with the Get External Data menu
      2. 12.2. Using the MS Query Wizard
      3. 12.3. Managing External Data Properties
    2. 13. Sharing Your Work with the Outside World
      1. 13.1. Protecting Your Dashboards and Reports
        1. 13.1.1. Securing the entire workbook using file protection options
        2. 13.1.2. Protecting worksheets
        3. 13.1.3. Protecting the workbook structure
      2. 13.2. Linking Your Excel Dashboards into PowerPoint
        1. 13.2.1. Creating the link between Excel and PowerPoint
        2. 13.2.2. Manually refreshing links to capture updates
        3. 13.2.3. Automatically refreshing links to capture updates
      3. 13.3. Distributing Your Dashboards via PDF
  11. VI. The Part of Tens
    1. 14. Ten Chart Design Principles
      1. 14.1. Avoid Fancy Formatting
      2. 14.2. Skip the Unnecessary Chart Junk
      3. 14.3. Format Large Numbers Where Possible
      4. 14.4. Use Data Tables instead of Data Labels
      5. 14.5. Make Effective Use of Chart Titles
      6. 14.6. Sort Your Data before Charting
      7. 14.7. Limit the Use of Pie Charts
      8. 14.8. Don't Be Afraid to Parse Data into Separate Charts
      9. 14.9. Maintain Appropriate Aspect Ratios
      10. 14.10. Don't Be Afraid to Use Something Other Than a Chart
    2. 15. Ten Questions to Ask Before Distributing Your Dashboard
      1. 15.1. Does My Dashboard Present the Right Information?
      2. 15.2. Does Everything on My Dashboard Have a Purpose?
      3. 15.3. Does My Dashboard Prominently Display the Key Message?
      4. 15.4. Can I Maintain This Dashboard?
      5. 15.5. Does My Dashboard Clearly Display Its Scope and Shelf Life?
      6. 15.6. Is My Dashboard Well Documented?
      7. 15.7. Is My Dashboard Overwhelmed with Formatting and Graphics?
      8. 15.8. Does My Dashboard Overuse Charts When Tables Will Do?
      9. 15.9. Is My Dashboard User-Friendly?
      10. 15.10. Is My Dashboard Accurate?

Product information

  • Title: Excel® 2007 Dashboards & Reports For Dummies®
  • Author(s):
  • Release date: March 2008
  • Publisher(s): For Dummies
  • ISBN: 9780470228142