Unknown Title

Title:

Excel dashboards & reports / by Michael Alexander.

Author:

Alexander, Michael, 1972- author.

ISBN:

9781119844396

Personal Author:

Alexander, Michael, 1972- author.

Uniform Title:

Excel dashboards and reports

Edition:

4th edition.

Physical Description:

xiii, 362 pages : illustrations ; 24 cm

Content Type:

text

Media Type (RDA):

unmediated

Carrier Type:

volume

Series:

For dummies

--For dummies.

General Note:

Previous edition: Hoboken: John Wiley & Sons, 2016.

Includes index.

Contents:

Getting started with excel dashboards and reports -- Building basic dashboard components -- Adding charts to your dashboards -- Advanced reporting techniques -- Part of tens.

Abstract:

"Beneath the seemingly endless rows and columns of cells, the latest version of Microsoft Excel boasts an astonishing variety of features and capabilities. But how do you go about tapping into some of that power without spending all of your days becoming a spreadsheet guru?"-- From publisher's description.

Title Subject:

Microsoft Excel (Computer file)

Subject Term:

Dashboards (Management information systems)

Statistics -- Charts, diagrams, etc.

Business report writing.

Genre:

Handbooks and manuals.

Format:

Books

Available:*

Library	Item Barcode	Shelf Number	Status
Searching... Batavia - Richmond Memorial Library	34020004092498	005.54 ALEX	Searching... Unknown

Bound With These Titles

On Order

Summary

It's time for some truly "Excel-lent" spreadsheet reporting

Beneath the seemingly endless rows and columns of cells, the latest version of Microsoft Excel boasts an astonishing variety of features and capabilities. But how do you go about tapping into some of that power without spending all of your days becoming a spreadsheet guru?

It's easy. You grab a copy of the newest edition of Excel Dashboards & Reports For Dummies and get ready to blow the pants off your next presentation audience!

With this book, you'll learn how to transform those rows and columns of data into dynamic reports, dashboards, and visualizations. You'll draw powerful new insights from your company's numbers to share with your colleagues - and seem like the smartest person in the room while you're doing it.

Excel Dashboards & Reports For Dummies offers:

Complete coverage of the latest version of Microsoft Excel provided in the Microsoft 365 subscription Strategies to automate your reporting so you don't have to manually crunch the numbers every week, month, quarter, or year Ways to get new perspectives on old data, visualizing it so you can find solutions no one else has seen before

If you're ready to make your company's numbers and spreadsheets dance, it's time to get the book that'll have them moving to your tune in no time. Get Excel Dashboards & Reports For Dummies today.

Author Notes

Michael Alexander is a senior consultant at Slalom Consulting with more than 15 years' experience in data management and reporting. He is the author of more than a dozen books on business analysis using Microsoft Excel, and has been named Microsoft Excel MVP for his contributions to the Excel community.

Introduction	p. 1
About This Book	p. 2
Foolish Assumptions	p. 4
Icons Used in This Book	p. 4
Beyond the Book	p. 4
Where to Go from Here	p. 5
Part 1 Getting Started with Excel Dashboards and Reports	p. 7
Chapter 1 Getting in the Dashboard State of Mind	p. 9
Defining Dashboards and Reports	p. 10
Defining reports	p. 10
Defining dashboards	p. 11
Preparing for Greatness	p. 12
Establish the audience for, and purpose of, the dashboard	p. 12
Delineate the measures for the dashboard	p. 13
Catalog the required data sources	p. 14
Define the dimensions and filters for the dashboard	p. 15
Determine the need for drill-down features	p. 16
Establish the refresh schedule	p. 16
A Quick Look at Dashboard Design Principles	p. 16
Rule number 1: Keep it simple	p. 17
Use layout and placement to draw focus	p. 19
Format numbers effectively	p. 20
Use titles and labels effectively	p. 20
Chapter 2 Building a Super Model	p. 23
Data Modeling Best Practices	p. 24
Separating data, analysis, and presentation	p. 24
Starting with appropriately structured data	p. 27
Avoiding turning your data model into a database	p. 30
Using tabs to document and organize your data model	p. 31
Testing your data model before building reporting components on top of it	p. 33
Excel Functions That Really Deliver	p. 34
The VLOOKUP function	p. 34
The HLOOKUP function	p. 38
The SUMPRODUCT function	p. 40
The CHOOSE function	p. 43
Using Smart Tables That Expand with Data	p. 45
Converting a range to an Excel table	p. 46
Converting an Excel table back to a range	p. 49
Introducing Dynamic Arrays	p. 49
Getting the basics of dynamic arrays	p. 49
Understanding spill ranges	p. 51
Referencing spill ranges	p. 53
Exploring Dynamic Array Functions	p. 54
The SORT function	p. 55
The SORTBY function	p. 56
The UNIQUE function	p. 57
The FILTER function	p. 58
The XLOOKUP function	p. 61
Chapter 3 The Pivotal Pivot Table	p. 65
An introduction to the Pivot Table	p. 65
The Pour Areas of a Pivot Table	p. 66
Values area	p. 66
Row area	p. 67
Column area	p. 67
Filter area	p. 68
Creating Your First Pivot Table	p. 69
Changing and rearranging your pivot table	p. 72
Adding a report filter	p. 73
Keeping your pivot table fresh	p. 74
Customizing Pivot Table Reports	p. 76
Changing the pivot table layout	p. 76
Customizing field names	p. 78
Applying numeric formats to data fields	p. 79
Changing summary calculations	p. 80
Suppressing subtotals	p. 81
Showing and hiding data items	p. 84
Hiding or showing items without data	p. 86
Sorting your pivot table	p. 88
Creating Useful Pivot-Driven Views	p. 89
Producing top and bottom views	p. 89
Creating views by month, quarter, and year	p. 93
Creating a percent distribution view	p. 95
Creating a month-over-month variance view	p. 97
Chapter 4 Using External Data for Your Dashboards and Reports	p. 101
Leveraging Power Query to Extract and Transform Data	p. 102
Reviewing Power Query basics	p. 102
Understanding query steps	p. 109
Importing Data from Files	p. 111
Getting data from Excel workbooks	p. 111
Getting data from CSV and text files	p. 113
Importing Data from Database Systems	p. 114
Importing data from Microsoft Access	p. 114
Managing data source settings	p. 116
Part 2 Building Basic Dashboard Components	p. 119
Chapter 5 Dressing Up Your Data Tables	p. 121
Table Design Principles	p. 122
Use colors sparingly	p. 122
De-emphasize borders	p. 123
Use effective number formatting	p. 126
Subdue your labels and headers	p. 127
Getting Fancy with Custom Number Formatting	p. 129
Number formatting basics	p. 129
Formatting numbers in thousands and millions	p. 132
Hiding and suppressing zeroes	p. 134
Applying custom format colors	p. 135
Formatting dates and times	p. 136
Chapter 6 Sparking Inspiration with Sparklines	p. 139
Introducing Sparklines	p. 139
Understanding Sparklines	p. 141
Creating sparklines	p. 142
Understanding sparkline groups	p. 144
Customizing Sparklines	p. 145
Sizing and merging sparkline cells	p. 145
Handling hidden or missing data	p. 146
Changing the sparkline type	p. 147
Changing sparkline colors and line width	p. 147
Using color to emphasize key data points	p. 147
Adjusting sparkline axis scaling	p. 148
Faking a reference line	p. 149
Specifying a date axis	p. 151
Autoupdating sparkline ranges	p. 152
Chapter 7 Formatting Your Way to Visualizations	p. 153
Enhancing Reports with Conditional Formatting	p. 154
Applying basic conditional formatting	p. 154
Adding your own formatting rules manually	p. 162
Showing only one icon	p. 166
Showing Data Bars and icons outside of cells	p. 169
Representing trends with Icon Sets	p. 171
Using Symbols to Enhance Reporting	p. 173
Wielding the Magical Camera Tool	p. 176
Finding the Camera tool	p. 176
Using the Camera tool	p. 177
Enhancing a dashboard with the Camera tool	p. 179
Enhancing Excel Reports with Shapes	p. 180
Creating visually appealing containers with shapes	p. 180
Layering shapes to save space	p. 182
Constructing your own infographic widgets with shapes	p. 182
Part 3 Adding Charts to Your Dashboards	p. 185
Chapter 8 Charts That Show Trending	p. 187
Trending Dos and Don'ts	p. 188
Using chart types appropriate for trending	p. 188
Starting the vertical scale at zero	p. 190
Leveraging Excel's logarithmic scale	p. 192
Applying creative label management	p. 193
Comparative Trending	p. 196
Creating side-by-side time comparisons	p. 196
Creating stacked time comparisons	p. 198
Trending with a secondary axis	p. 199
Emphasizing Periods of Time	p. 202
Formatting specific periods	p. 202
Using dividers to mark significant events	p. 203
Representing forecasts in your trending components	p. 204
Other Trending Techniques	p. 206
Avoiding overload with directional trending	p. 206
Smoothing data	p. 207
Chapter 9 Grouping and Bucketing Data	p. 211
Creating Top and Bottom Displays	p. 211
Incorporating top and bottom displays into dashboards	p. 212
Using pivot tables to get top and bottom views	p. 213
Top Values in Charts	p. 216
Using Histograms to Track Relationships and Frequency	p. 220
Using Excel's Histogram statistical chart	p. 220
Creating a formula-driven histogram	p. 223
Adding a cumulative percent	p. 226
Using a pivot table to create a histogram	p. 228
Chapter 10 Displaying Performance against a Target	p. 231
Showing Performance with Variances	p. 231
Showing Performance against Organizational Trends	p. 233
Using a Thermometer-Style Chart	p. 234
Using a Bullet Graph	p. 235
Creating a bullet graph	p. 236
Adding data to your bullet graph	p. 239
Final thoughts on formatting bullet graphs	p. 241
Showing Performance against a Target Range	p. 243
Part 4 Advanced Reporting Techniques	p. 247
Chapter 11 Giving Users an Interactive Interface	p. 249
Introducing Macros	p. 249
Why use a macro?	p. 250
Recording your first macro	p. 251
Running your macros	p. 254
Enabling and trusting macros	p. 257
Understanding macro-enabled file extensions	p. 258
Enabling macro content	p. 258
Setting up trusted locations	p. 258
Examining some macro examples	p. 259
Building navigation buttons	p. 260
Dynamically rearranging pivot table data	p. 261
Offering one-touch reporting options	p. 262
Getting Started with Form Controls	p. 263
Finding Form controls	p. 263
Adding a control to a worksheet	p. 264
Using the Button Control	p. 266
Using the Check Box Control	p. 266
Toggling a Chart Series On and Off	p. 268
Using the Option Button Control	p. 270
Showing Many Views through One Chart	p. 272
Using the Combo Box Control	p. 274
Changing Chart Data with a Drop-Down Selector	p. 275
Using the List Box Control	p. 277
Controlling Multiple Charts with One Selector	p. 279
Chapter 12 Adding Interactivity with Pivot Slicers	p. 283
Understanding Slicers	p. 283
Creating a Standard Slicer	p. 286
Getting Fancy with Slicer Customizations	p. 288
Size and placement	p. 288
Data item columns	p. 288
Other slicer settings	p. 289
Creating your own slicer style	p. 289
Controlling Multiple Pivot Tables with One Slicer	p. 293
Creating a Timeline Slicer	p. 294
Using Slicers as Form Controls	p. 296
Using Slicers on Excel Table Objects	p. 300
Chapter 13 Sharing Your Workbook with the Outside World	p. 303
Protecting Your Dashboards and Reports	p. 303
Securing access to the entire workbook	p. 304
Limiting access to specific worksheet ranges	p. 307
Protecting the workbook structure	p. 310
Linking Your Excel Dashboards to PowerPoint	p. 311
Creating a link between Excel and PowerPoint	p. 312
Manually updating links to capture updates	p. 313
Turning off automatic updating of links	p. 314
Distributing Your Dashboards via a PDF	p. 316
Distributing Your Dashboards to OneDrive	p. 318
Limitations When Publishing to the Web	p. 321
Part 5 The Part of Tens	p. 323
Chapter 14 Ten Chart Design Principles	p. 325
Avoid Fancy Formatting	p. 325
Skip the Unnecessary Chart Junk	p. 327
Format Large Numbers Where Possible	p. 329
Use Data Tables Instead of Data Labels	p. 330
Make Effective Use of Chart Titles	p. 332
Sort Your Data before Charting	p. 333
Limit the Use of Pie Charts	p. 333
Don't Be Afraid to Parse Data into Separate Charts	p. 334
Maintain Appropriate Aspect Ratios	p. 336
Don't Be Afraid to Use Something Other Than a Chart	p. 337
Chapter 15 Ten Questions to Ask Before Distributing Your Dashboard	p. 339
Does My Dashboard Present the Right Information?	p. 339
Does Everything on My Dashboard Have a Purpose?	p. 340
Does My Dashboard Prominently Display the Key Message?	p. 340
Can I Maintain This Dashboard?	p. 341
Does My Dashboard Clearly Display Its Scope and Shelf Life?	p. 341
Is My Dashboard Well Documented?	p. 341
Is My Dashboard Overwhelmed with Formatting and Graphics?	p. 342
Does My Dashboard Overuse Charts When Tables Will Do?	p. 343
Is My Dashboard User-Friendty?	p. 343
Is My Dashboard Accurate?	p. 344
Index	p. 345

Available:*

Bound With These Titles

On Order

Summary

Summary

Author Notes

Table of Contents