Excel Dashboards and Reports for Dummies

Оглавление

Alexander Michael. Excel Dashboards and Reports for Dummies

Introduction

About This Book

Foolish Assumptions

How This Book Is Organized

Icons Used In This Book

Beyond the Book

Where to Go from Here

Part I. Getting Started with Excel Dashboards & Reports

Chapter 1. Getting In the Dashboard State of Mind

Defining Dashboards and Reports

Preparing for Greatness

A Quick Look at Dashboard Design Principles

Key Questions to Ask Before Distributing Your Dashboard

Chapter 2. Building a Super Model

Data Modeling Best Practices

Excel Functions That Really Deliver

Using Smart Tables That Expand with Data

Part II. Building Basic Dashboard Components

Chapter 3. Dressing Up Your Data Tables

Getting Fancy with Custom Number Formatting

Chapter 4. Sparking Inspiration with Sparklines

Introducing Sparklines

Understanding Sparklines

Customizing Sparklines

Chapter 5. Formatting Your Way to Visualizations

Enhancing Reports with Conditional Formatting

Using Symbols to Enhance Reporting

Wielding the Magical Camera Tool

Making Waffles with Conditional Formatting and the Camera Tool

Chapter 6. The Pivotal Pivot Table

An Introduction to the Pivot Table

The Four Areas of a Pivot Table

Creating Your First Pivot Table

Customizing Pivot Table Reports

Creating Useful Pivot-Driven Views

Part III. Building Advanced Dashboard Components

Chapter 7. Charts That Show Trending

Trending Dos and Don’ts

Comparative Trending

Emphasizing Periods of Time

Other Trending Techniques

Chapter 8. Grouping and Bucketing Data

Creating Top and Bottom Displays

Top Values in Charts

Using Histograms to Track Relationships and Frequency

Chapter 9. Displaying Performance against a Target

Showing Performance with Variances

Showing Performance against Organizational Trends

Using a Thermometer-Style Chart

Using a Bullet Graph

Showing Performance against a Target Range

Part IV. Advanced Reporting Techniques

Chapter 10. Macro-Charged Dashboarding

Why Use a Macro?

Recording Your First Macro

Running Your Macros

Enabling and Trusting Macros

Examining Some Macro Examples

Chapter 11. Giving Users an Interactive Interface

Getting Started with Form Controls

Using the Button Control

Using the Check Box Control

Toggling a Chart Series On and Off

Using the Option Button Control

Showing Many Views through One Chart

Using the Combo Box Control

Changing Chart Data with a Drop-Down Selector

Using the List Box Control

Controlling Multiple Charts with One Selector

Chapter 12. Adding Interactivity with Pivot Slicers

Understanding Slicers

Creating a Standard Slicer

Getting Fancy with Slicer Customizations

Controlling Multiple Pivot Tables with One Slicer

Creating a Timeline Slicer

Using Slicers as Form Controls

Part V. Working with the Outside World

Chapter 13. Using External Data for Your Dashboards and Reports

Importing Data from Microsoft Access

Importing Data from SQL Server

Leveraging Power Query to Extract and Transform Data

Chapter 14. Sharing Your Workbook with the Outside World

Protecting Your Dashboards and Reports

Linking Your Excel Dashboards to PowerPoint

Distributing Your Dashboards via a PDF

Distributing Your Dashboards to OneDrive

Limitations When Publishing to the Web

Part VI. The Part of Tens

Chapter 15. Ten Chart Design Principles

Avoid Fancy Formatting

Skip the Unnecessary Chart Junk

Format Large Numbers Where Possible

Use Data Tables Instead of Data Labels

Make Effective Use of Chart Titles

Sort Your Data before Charting

Limit the Use of Pie Charts

Don’t Be Afraid to Parse Data into Separate Charts

Maintain Appropriate Aspect Ratios

Don’t Be Afraid to Use Something Other Than a Chart

Chapter 16. Ten Excel Chart Types and When to Use Them

Line Chart

Column Chart

Clustered Column Chart

Stacked Column Chart

Pie Chart

Bar Chart

Area Chart

Combination Chart

XY Scatter Plot Chart

Bubble Chart

About the Author

Author’s Acknowledgments

Отрывок из книги

The term business intelligence (BI), coined by Howard Dresner of Gartner, Inc., describes the set of concepts and methods to improve business decision-making by using fact-based support systems. Practically speaking, BI is what you get when you analyze raw data and turn that analysis into knowledge. BI can help an organization identify cost-cutting opportunities, uncover new business opportunities, recognize changing business environments, identify data anomalies, and create widely accessible reports.

Over the past few years, the BI concept has overtaken corporate executives who are eager to turn impossible amounts of data into knowledge. As a result of this trend, whole industries have been created. Software vendors that focus on BI and dashboarding are coming out of the woodwork. New consulting firms touting their BI knowledge are popping up virtually every week. And even the traditional enterprise solution providers, like Business Objects and SAP, are offering new BI capabilities.

.....

In Part II, you take an in-depth look at some of the basic dashboard components you can create using Excel. Chapter 3 starts you off with some fundamentals around designing effective data tables. Chapter 4 shows you how you can leverage the Sparkline functionality found in Excel. Chapter 5 provides a look at the various techniques that you can use to visualize data without the use of charts or graphs. Chapter 6 rounds out this section of the book by introducing you to pivot tables and discussing how a pivot table can play an integral role in Excel-based dashboards.

In Part III you go beyond the basics to take a look at some of the advanced chart components you can create with Excel. This part consists of three chapters, starting with Chapter 7, where I demonstrate how to represent time trending, seasonal trending, moving averages and other types of trending in dashboards. In Chapter 8, you explore the many methods used to bucket data – putting data into groups for reporting, in other words. Finally, Chapter 9 demonstrates some of charting techniques that can help you display and measure values versus goals.

.....