Оглавление
Michael Alexander. Excel Dashboards & Reports For Dummies
Microsoft® Excel® Dashboards & Reports For Dummies® To view this book's Cheat Sheet, simply go to www.dummies.com and search for “Excel Dashboards Reports For Dummies Cheat Sheet” in the Search box. Table of Contents
List of Tables
List of Illustrations
Guide
Pages
Introduction
About This Book
Foolish Assumptions
Icons Used in This Book
Beyond the Book
Where to Go from Here
Getting Started with Excel Dashboards and Reports
Getting in the Dashboard State of Mind
Defining Dashboards and Reports
Defining reports
Defining dashboards
Preparing for Greatness
Establish the audience for, and purpose of, the dashboard
Delineate the measures for the dashboard
Catalog the required data sources
Define the dimensions and filters for the dashboard
Determine the need for drill-down features
Establish the refresh schedule
A Quick Look at Dashboard Design Principles
Rule number 1: Keep it simple
Don’t turn your dashboard into a data repository
Avoid the fancy formatting
Limit each dashboard to one printable page
Use layout and placement to draw focus
Format numbers effectively
Use titles and labels effectively
Building a Super Model
Data Modeling Best Practices
Separating data, analysis, and presentation
Starting with appropriately structured data
Spreadsheet reports make for ineffective data models
Flat data files lend themselves nicely to data models
Tabular data sets are perfect for pivot table–driven data models
Avoiding turning your data model into a database
Using tabs to document and organize your data model
SPEAKING OF DOCUMENTING YOUR DATA MODEL …
Testing your data model before building reporting components on top of it
Excel Functions That Really Deliver
The VLOOKUP function
VLOOKUP basics
Applying VLOOKUP formulas in a data model
Using data validation drop-down lists in the data model
The HLOOKUP function
HLOOKUP basics
Applying HLOOKUP formulas in a data model
The SUMPRODUCT function
SUMPRODUCT basics
A twist on the SUMPRODUCT function
Applying SUMPRODUCT formulas in a data model
The CHOOSE function
CHOOSE basics
Applying CHOOSE formulas in a data model
Using Smart Tables That Expand with Data
Converting a range to an Excel table
Converting an Excel table back to a range
Introducing Dynamic Arrays
Getting the basics of dynamic arrays
DYNAMIC ARRAYS AND COMPATIBILITY
Understanding spill ranges
Referencing spill ranges
WHY DO I SEE AN AT SIGN (@) IN MY FORMULAS?
Exploring Dynamic Array Functions
The SORT function
The SORTBY function
The UNIQUE function
The FILTER function
The XLOOKUP function
The Pivotal Pivot Table
An Introduction to the Pivot Table
The Four Areas of a Pivot Table
Values area
Row area
Column area
Filter area
Creating Your First Pivot Table
Changing and rearranging your pivot table
Adding a report filter
Keeping your pivot table fresh
PIVOT TABLES AND SPREADSHEET BLOAT
Customizing Pivot Table Reports
Changing the pivot table layout
Customizing field names
Applying numeric formats to data fields
Changing summary calculations
Suppressing subtotals
Removing all subtotals at one time
Removing the subtotals for only one field
Removing grand totals
Showing and hiding data items
Hiding or showing items without data
Sorting your pivot table
Creating Useful Pivot-Driven Views
Producing top and bottom views
Creating views by month, quarter, and year
Creating a percent distribution view
Creating a month-over-month variance view
Using External Data for Your Dashboards and Reports
Leveraging Power Query to Extract and Transform Data
Reviewing Power Query basics
MANAGING EXTERNAL DATA PROPERTIES
Understanding query steps
Importing Data from Files
Getting data from Excel workbooks
Getting data from CSV and text files
Importing Data from Database Systems
Importing data from Microsoft Access
Managing data source settings
Building Basic Dashboard Components
Dressing Up Your Data Tables
Table Design Principles
Use colors sparingly
De-emphasize borders
Use effective number formatting
Subdue your labels and headers
Getting Fancy with Custom Number Formatting
Number formatting basics
Formatting numbers in thousands and millions
Hiding and suppressing zeroes
Applying custom format colors
Formatting dates and times
Sparking Inspiration with Sparklines
Introducing Sparklines
Understanding Sparklines
Creating sparklines
Understanding sparkline groups
Customizing Sparklines
Sizing and merging sparkline cells
Handling hidden or missing data
Changing the sparkline type
Changing sparkline colors and line width
Using color to emphasize key data points
Adjusting sparkline axis scaling
Faking a reference line
Specifying a date axis
Autoupdating sparkline ranges
Formatting Your Way to Visualizations
Enhancing Reports with Conditional Formatting
Applying basic conditional formatting
Using the Highlight Cells Rules
Applying Top/Bottom Rules
Creating Data Bars
Applying Color Scales
Using Icon Sets
Adding your own formatting rules manually
Showing only one icon
Showing Data Bars and icons outside of cells
Representing trends with Icon Sets
Using Symbols to Enhance Reporting
Wielding the Magical Camera Tool
Finding the Camera tool
Using the Camera tool
Enhancing a dashboard with the Camera tool
Enhancing Excel Reports with Shapes
Creating visually appealing containers with shapes
Layering shapes to save space
Constructing your own infographic widgets with shapes
Adding Charts to Your Dashboards
Charts That Show Trending
Trending Dos and Don’ts
Using chart types appropriate for trending
Using line charts
Using area charts
Using column charts
Starting the vertical scale at zero
Leveraging Excel’s logarithmic scale
Applying creative label management
Abbreviating instead of changing alignment
Implying labels to reduce clutter
Going vertical when you have too many data points for horizontal
Nesting labels for clarity
Comparative Trending
Creating side-by-side time comparisons
Creating stacked time comparisons
Trending with a secondary axis
Emphasizing Periods of Time
Formatting specific periods
Using dividers to mark significant events
Representing forecasts in your trending components
Other Trending Techniques
Avoiding overload with directional trending
Smoothing data
Smoothing with Excel’s moving average functionality
Creating your own smoothing calculation
Grouping and Bucketing Data
Creating Top and Bottom Displays
Incorporating top and bottom displays into dashboards
Using pivot tables to get top and bottom views
Top Values in Charts
Using Histograms to Track Relationships and Frequency
Using Excel's Histogram statistical chart
Creating a formula-driven histogram
Adding a cumulative percent
Using a pivot table to create a histogram
Displaying Performance against a Target
Showing Performance with Variances
Showing Performance against Organizational Trends
Using a Thermometer-Style Chart
Using a Bullet Graph
Creating a bullet graph
Adding data to your bullet graph
Final thoughts on formatting bullet graphs
Creating qualitative bands
Creating horizontal bullet graphs
Showing Performance against a Target Range
Advanced Reporting Techniques
Giving Users an Interactive Interface
Introducing Macros
Why use a macro?
Recording your first macro
Running your macros
Enabling and trusting macros
Understanding macro-enabled file extensions
Enabling macro content
Setting up trusted locations
Examining some macro examples
Building navigation buttons
Dynamically rearranging pivot table data
Offering one-touch reporting options
Getting Started with Form Controls
Finding Form controls
Adding a control to a worksheet
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
Adding Interactivity with Pivot Slicers
Understanding Slicers
Creating a Standard Slicer
Getting Fancy with Slicer Customizations
Size and placement
Data item columns
Other slicer settings
Creating your own slicer style
Controlling Multiple Pivot Tables with One Slicer
Creating a Timeline Slicer
Using Slicers as Form Controls
Using Slicers on Excel Table Objects
Sharing Your Workbook with the Outside World
Protecting Your Dashboards and Reports
Securing access to the entire workbook
Permitting read-only access unless a password is given
Requiring a password to open an Excel file
Removing workbook-level protection
Limiting access to specific worksheet ranges
Unlocking editable ranges
Applying worksheet protection
Protecting sheet elements and actions
Removing worksheet protection
Protecting the workbook structure
Linking Your Excel Dashboards to PowerPoint
Creating a link between Excel and PowerPoint
Manually updating links to capture updates
Turning off automatic updating of links
Distributing Your Dashboards via a PDF
Distributing Your Dashboards to OneDrive
Limitations When Publishing to the Web
The Part of Tens
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
Ten Questions to Ask Before Distributing Your Dashboard
Does My Dashboard Present the Right Information?
Does Everything on My Dashboard Have a Purpose?
Does My Dashboard Prominently Display the Key Message?
Can I Maintain This Dashboard?
Does My Dashboard Clearly Display Its Scope and Shelf Life?
Is My Dashboard Well Documented?
Is My Dashboard Overwhelmed with Formatting and Graphics?
Does My Dashboard Overuse Charts When Tables Will Do?
Is My Dashboard User-Friendly?
Is My Dashboard Accurate?
Index. Symbols
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z
About the Author
Dedication
Author’s Acknowledgments
WILEY END USER LICENSE AGREEMENT
