book

Excel® 2007 Dashboards & Reports For Dummies®

Name: Excel® 2007 Dashboards & Reports For Dummies®
Author: Michael Alexander
ISBN: 9780470228142

by Michael Alexander

March 2008

Beginner to intermediate

318 pages

7h 7m

English

For Dummies

Read now

Unlock full access

Copyright
About the Author
Author's Acknowledgements
Introduction
About This BookFoolish AssumptionsHow This Book Is OrganizedPart I: Making the Move to DashboardsPart II: Building Basic Dashboard ComponentsPart III: Building Advanced Dashboard ComponentsPart IV: Advanced Reporting TechniquesPart V: Working with the Outside WorldPart VI: The Part of TensSample Files for This BookIcons Used In This BookWhere to Go from Here
I. Making the Move to Dashboards
1. Getting in the Dashboard State of Mind
1.1. Defining Dashboards and Reports1.1.1. Defining reports1.1.2. Defining dashboards1.2. Preparing for Greatness1.2.1. Establish the audience and purpose for the dashboard1.2.2. Delineate the measures for the dashboard1.2.3. Catalog the required data sources1.2.4. Define the dimensions and filters for the dashboard1.2.5. Determine the need for drill-down features1.2.6. Establish the refresh schedule1.3. A Quick Look at Dashboard Design Principles1.3.1. Rule number 1: Keep it simple1.3.2. Use layout and placement to draw focus1.3.3. Format numbers effectively1.3.4. Use titles and labels effectively
2. Building a Super Model
2.1. Data Modeling Best Practices2.1.1. Separating data, analysis, and presentation2.1.2. Starting with appropriately structured data2.1.3. Avoiding turning your data model into a database2.1.4. Using tabs to document and organize your data model2.1.5. Testing your data model before building reporting components on top of it2.2. Excel Functions That Really Deliver2.2.1. The VLOOKUP function2.2.2. The HLOOKUP function2.2.3. The SUMPRODUCT function2.2.4. The CHOOSE function2.3. Using Smart Tables That Expand with Data2.3.1. Converting a range to an Excel table2.3.2. Converting an Excel table back to a range
II. Building Basic Dashboard Components
3. The Pivotal Pivot Table
3.1. An Introduction to the Pivot Table3.2. The Four Areas of a Pivot Table3.2.1. Values area3.2.2. Row area3.2.3. Column area3.2.4. Filter area3.3. Creating Your First Pivot Table3.3.1. Changing and rearranging your pivot table3.3.2. Adding a report filter3.3.3. Keeping your pivot table fresh3.4. Customizing Your Pivot Table Reports3.4.1. Changing the pivot table layout3.4.2. Customizing field names3.4.3. Applying numeric formats to data fields3.4.4. Changing summary calculations3.4.5. Suppressing subtotals3.4.6. Showing and hiding data items3.4.7. Hiding or showing items without data3.4.8. Sorting your pivot table3.5. Creating Useful Pivot-Driven Views3.5.1. Producing top and bottom views3.5.2. Creating views by month, quarter, and year3.5.3. Creating a percent distribution view3.5.4. Creating a YTD totals view3.5.5. Creating a month-over-month variance view

4. Excel Charts for the Uninitiated
4.1. Chart Building Basics4.1.1. A review of the most-commonly-used chart types4.1.2. Preparing data for different chart types4.1.3. Creating a chart from scratch4.1.4. Charting disparate data4.2. Common Chart Tasks4.2.1. Resizing and moving charts4.2.2. Changing chart type4.2.3. Creating a combination chart4.2.4. Selecting and formatting chart elements4.3. Working with Pivot Charts4.3.1. Pivot chart fundamentals4.3.2. Pivot charts and the x and y axes4.3.3. Pivot charts formatting limitations
5. The New World of Conditional Formatting
5.1. Applying Basic Conditional Formatting5.1.1. Highlight Cells Rules5.1.2. Top/Bottom Rules5.1.3. Data Bars, Color Scales, and Icon Sets5.2. Getting Fancy with Conditional Formatting5.2.1. Adding your own formatting rules manually5.2.2. Showing only one icon5.2.3. Showing Data Bars and icons outside cells5.2.4. Representing trends with Icon Sets5.2.5. Building a legend for your conditional formatting5.2.6. Using conditional formatting with pivot tables
6. The Art of Dynamic Labeling
6.1. Creating a Basic Dynamic Label6.2. Adding Layers of Analysis with Dynamic Labels6.3. Excel's Mysterious Camera Tool6.3.1. Finding the Camera tool6.3.2. The basics of using the Camera tool6.3.3. Cool uses for the Camera tool6.4. Formula-Driven Visualizations6.4.1. In-cell charting without charts or conditional formatting6.4.2. Creating visualizations with Wingdings and things
III. Building Advanced Dashboard Components
7. Components That Show Trending
7.1. Trending Dos and Don'ts7.1.1. Using chart types appropriate for trending7.1.2. Starting the vertical scale at zero7.1.3. Leveraging Excel's logarithmic scale7.1.4. Applying creative label management7.2. Comparative Trending7.2.1. Creating side-by-side time comparisons7.2.2. Creating stacked time comparisons7.2.3. Trending with a secondary axis7.3. Highlighting Periods of Time7.3.1. Formatting specific periods7.3.2. Using dividers to mark significant events7.3.3. Representing forecasts in your trending components7.4. Other Trending Techniques7.4.1. Avoiding overload with directional trending7.4.2. Smoothing data7.4.3. Catching sparkline fever
8. Components That Group and Bucket Data
8.1. Creating Top and Bottom Displays8.1.1. Incorporating top and bottom displays into dashboards8.1.2. Using pivot tables to create interactive top and bottom views8.2. Using Histograms to Track Relationships and Frequency8.2.1. Creating formula-driven histograms8.2.2. Adding a cumulative percent to your histogram8.2.3. Creating a histogram with a pivot table8.3. Highlighting Top Values in Charts
9. Components That Display Performance against a Target
9.1. Showing Performance with Variances9.2. Showing Performance against Organizational Trends9.3. Using Thermometer-Style Charts to Display Performance9.4. An Introduction to the Bullet Graph9.4.1. Creating your first bullet graph9.4.2. Adding data to your bullet graph9.4.3. Final thoughts on formatting bullet graphs9.5. Showing Performance against a Target Range
IV. Advanced Reporting Techniques
10. Macro-Charged Reporting
10.1. Why Use a Macro?10.2. Introducing the Macro Recorder10.2.1. The Macro Recorder user interface10.2.2. Recording macros with absolute references10.2.3. Recording macros with relative references10.2.4. Assigning a macro to a button10.3. Macro Security in Excel 200710.3.1. The short-term solution to disabled macros10.3.2. The long-term solution to disabled macros10.4. Excel Macro Examples10.4.1. Building navigation buttons10.4.2. Dynamically rearranging pivot table data10.4.3. Offering one-touch reporting options
11. Giving Users an Interactive Interface
11.1. Introducing Form Controls11.2. Adding and Configuring Controls11.3. Using the Button Control11.4. Using the Check Box Control11.5. Check Box Example: Toggling a Chart Series On and Off11.6. Using Option Button Controls11.7. Option Button Example: Showing Many Views through One Chart11.8. Using the Combo Box Control11.9. Combo Box Example: Controlling Multiple Pivot Tables with One Combo Box11.10. Using the List Box Control11.11. List Box Example: Controlling Multiple Charts with One Selector
V. Working with the Outside World
12. Using External Data for Your Dashboards and Reports
12.1. Using the Get External Data Group12.1.1. Importing Access data with the Get External Data Group12.1.2. Importing SQL Server data with the Get External Data menu12.2. Using the MS Query Wizard12.3. Managing External Data Properties
13. Sharing Your Work with the Outside World
13.1. Protecting Your Dashboards and Reports13.1.1. Securing the entire workbook using file protection options13.1.2. Protecting worksheets13.1.3. Protecting the workbook structure13.2. Linking Your Excel Dashboards into PowerPoint13.2.1. Creating the link between Excel and PowerPoint13.2.2. Manually refreshing links to capture updates13.2.3. Automatically refreshing links to capture updates13.3. Distributing Your Dashboards via PDF
VI. The Part of Tens
14. Ten Chart Design Principles
14.1. Avoid Fancy Formatting14.2. Skip the Unnecessary Chart Junk14.3. Format Large Numbers Where Possible14.4. Use Data Tables instead of Data Labels14.5. Make Effective Use of Chart Titles14.6. Sort Your Data before Charting14.7. Limit the Use of Pie Charts14.8. Don't Be Afraid to Parse Data into Separate Charts14.9. Maintain Appropriate Aspect Ratios14.10. Don't Be Afraid to Use Something Other Than a Chart
15. Ten Questions to Ask Before Distributing Your Dashboard
15.1. Does My Dashboard Present the Right Information?15.2. Does Everything on My Dashboard Have a Purpose?15.3. Does My Dashboard Prominently Display the Key Message?15.4. Can I Maintain This Dashboard?15.5. Does My Dashboard Clearly Display Its Scope and Shelf Life?15.6. Is My Dashboard Well Documented?15.7. Is My Dashboard Overwhelmed with Formatting and Graphics?15.8. Does My Dashboard Overuse Charts When Tables Will Do?15.9. Is My Dashboard User-Friendly?15.10. Is My Dashboard Accurate?

Overview

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!

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Streams, Walls, and Feeds: 109 Design Guidelines for Improving Notifications, Messages, and Alerts Sent Through Social Networks and RSS

Nielsen Norman Group

Publisher Resources

ISBN: 9780470228142Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills