Excel Dashboards & Reports For Dummies
Overview
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.
Read More
About The Author
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.
Sample Chapters
excel dashboards & reports for dummies
CHEAT SHEET
Companies and organizations are always interested in business intelligence — raw data that can be turned into actionable knowledge. This need for business intelligence manifests itself in many forms.Dashboards are reporting mechanisms that deliver business intelligence in a graphical form. Most data analysis benefits from a spreadsheet, so Excel is inherently part of any business-intelligence tool portfolio.
View Cheat Sheet
View Cheat Sheet
HAVE THIS BOOK?
View Book Companion Site
Access practice tests
Additional Book Resources
Articles from
the book
Building a chart in Excel is not in and of itself a terribly difficult thing to do. The hard part is wrapping your mind around which types of chart to use in which situation. Excel has 11 major chart types, with variations on each type. For most business dashboards and reports, you need only a handful of the chart types available in Excel.
Excel
Most users think of Excel shapes as mildly useful objects that can be added to a worksheet only if you need to show a square, some arrows, a circle, and so on. But if you use your imagination, you can leverage Excel shapes to create stylized interfaces that can really enhance your dashboards. Here are ten examples of how Excel shapes can spice up your Dashboards.
Excel makes charting so simple that it's often tempting to accept the charts it creates no matter how bad the default colors or settings are, but you can easily avoid charting fiascos by following a few basic design principles.
Don't be afraid to use something other than a chart. Ask yourself whether a simple table will present the data just fine.
Excel
It's not uncommon to be asked to add additional analysis to your visualization that typically isn't plotted on an Excel chart. For example, this chart shows sales for each quarter, but your client may want to also see the percent growth during the same quarters.
Most Excel analysts fulfill this need with actual text boxes.
Excel
A creative alternative to using the icon sets offered with conditional formatting is to use the various symbol fonts that come with Office. The symbol fonts are Wingdings, Wingdings2, Wingdings3, and Webdings. These fonts display symbols for each character instead of the standard numbers and letters.
The idea here is simple.
Excel
As the building block for your Excel reports, the data in your data models needs to be structured appropriately. Not all datasets are created equal. Although some datasets work in a standard Excel environment, they may not work for data modeling purposes. Before building your data model, ensure that your source data is appropriately structured for dashboarding purposes.
Most Excel dashboards are designed around a set of measures, or key performance indicators (KPIs). A KPI is an indicator of the performance of a task deemed to be essential to daily operations or processes. The idea is that a KPI reveals performance that is outside the normal range for a particular measure, so it therefore often signals the need for attention and intervention.
Excel
You might have read that measures used on an Excel dashboard absolutely should support the initial purpose of that dashboard. The same concept applies to the back-end data model. You should only import data that's necessary to fulfill the purpose of your dashboard or report.
In an effort to have as much data as possible at their fingertips, many Excel users bring into their spreadsheets every piece of data they can get their hands on.
Excel
Excel’s number formatting syntax consists of different individual number formats separated by semicolons. By default, the syntax to the left of the first semicolon is applied to positive numbers, the syntax to the right of the first semicolon is applied to negative numbers, and the syntax to the right of the second semicolon is applied to zeros.
Excel
There will undoubtedly be lots of numbers on your Excel dashboards. It's important that you format your numbers effectively to allow your users to understand the information they represent without confusion or hindrance. Remember that every piece of information on your dashboard should have a reason for being there.
Excel
Dashboard design expert Stephen Few has the mantra, "Simplify, simplify, simplify." The basic idea is that dashboards cluttered with too many measures or too much eye candy can dilute the significant information you're trying to present. How many times has someone told you that your reports look "busy"? In essence, this complaint means that too much is going on in the page or screen, making it hard to see the actual data.
Excel
Only measures that support the Excel dashboard's utility and purpose should be included on the dashboard. However, it should be said that just because all measures on your dashboard are significant, they may not always have the same level of importance. In other words, you'll frequently want one component of your dashboard to stand out from the others.
Excel
It's common sense, but many people often fail to label items on Excel dashboards effectively. If your manager looks at your dashboard and asks you, "What is this telling me?" you likely have labeling issues. Here are a few guidelines for effective labeling on your dashboards and reports:
Always include a timestamp on your reporting mechanisms.
Excel
Companies and organizations are always interested in business intelligence — raw data that can be turned into actionable knowledge. This need for business intelligence manifests itself in many forms.Dashboards are reporting mechanisms that deliver business intelligence in a graphical form. Most data analysis benefits from a spreadsheet, so Excel is inherently part of any business-intelligence tool portfolio.
Table design is one of the most underestimated endeavors in Excel reporting. How a table is designed has a direct effect on how well an audience absorbs and interprets the data in that table. Unfortunately, putting together a data table with an eye for economy and ease of consumption is an uncommon skill.
For example, the table shown here is similar to many found in Excel reports.
Excel
In Microsoft Excel, you can improve the readability of your dashboards and reports by formatting your revenue numbers to appear in thousands. This allows you to present cleaner numbers and avoid inundating your audience with gigantic numbers.
Here's how it works
To show your numbers in thousands, highlight them, right-click, and select Format Cells.
Excel
You can learn a lot from the approaches other have taken to design their Excel dashboards and reporting mechanisms. Here is a list of sites (in no particular order) dedicated to business intelligence and the presentation of data through dashboards. Visit these sites to get ideas and fresh new perspectives on Excel dashboards and reports:
Perceptual Edge: Visualization expert Stephen Few provides some fascinating insights on data visualization and dashboarding.
Excel
You don't have to use one of the predefined scenarios offered by Excel. Excel gives you the flexibility to create your own formatting rules manually. Creating your own formatting rules helps you better control how cells are formatted and allows you to do things you wouldn't be able to do with the predefined scenarios.
Excel
The Excel pivot tables you create often need to be tweaked to get the look and feel you're looking for. Excel gives you a choice in the layout of your data in a pivot table. The three layouts, shown side by side in the following figure, are Compact Form, Outline Form, and Tabular Form.
The three layouts for a pivot table report.
Excel
One advantage you gain with slicers in Excel is that each slicer can be tied to more than one pivot table; that is to say, any filter you apply to your slicer can be applied to multiple pivot tables.
To connect your slicer to more than one pivot table, simply right-click the slicer and select Report Connections from the menu that appears.
Excel
If you're using Excel 2016, you get the luxury of using Excel's new statistical charts. Statistical charts help calculate and visualize common statistical analyses without the need to engage in brain-busting calculations. This new chart type lets you essentially point and click your way into a histogram chart, leaving all the mathematical heavy lifting to Excel.
Excel
The default Excel slicer styles are a bit of a drag. Oftentimes, the look and feel of slicers don't match the aesthetic of your dashboard. Luckily, Excel provides a way for you to customize your slicers to fit into any reporting theme. With minimal effort, your slicers can be integrated nicely into your dashboard layout.
Excel
The Timeline slicer works in the same way a standard slicer does, in that it lets you filter a pivot table using a visual selection mechanism instead of the old Filter fields. The difference is the Timeline slicer is designed to work exclusively with date fields, providing an excellent visual method to filter and group the dates in your pivot table.
Excel
Microsoft Access is used in many organizations to manage a series of tables that interact with each other, such as a Customers table, an Orders table, and an Invoices table. Managing data in Access provides the benefit of a relational database in which you can ensure data integrity, prevent redundancy, and easily generate datasets via queries.
Excel
A waffle chart is an interesting visualization in Excel that helps display progress toward a goal. A waffle chart is basically a square divided into a 10 x 10 grid. Each grid box represents 1 percent toward a goal of 100 percent. The number of grid boxes that are colored or shaded is determined by the associated metric.
Excel
Excel Form controls are starting to look a bit dated, especially when paired with the modern-looking charts that come with Excel 2016. One clever way to alleviate this problem is to hijack the Slicer feature for use as a proxy Form control of sorts.Create a simple table that holds the names you want for your controls, along with some index numbering.
Excel
Although the CHOOSE function may not look useful on the surface, this function can dramatically enhance your Excel data models. The CHOOSE function returns a value from a specified list of values based on a specified position number.
For instance, if you enter the formulas CHOOSE(3, "Red", "Yellow", "Green", "Blue") into a cell, Excel returns Green because Green is the third item in the list of values.
Excel
The SUMPRODUCT function is actually listed under the math and trigonometry category of Excel functions. Because the primary purpose of SUMPRODUCT is to calculate the sum product, most people don't know you can actually use it to look up values. In fact, you can use this versatile function quite effectively in most data models.
Excel
Excel offers several methods for getting your Access data into your Excel data model for your reports and dashboards. For simplicity, you just can't beat the drag-and-drop method. You can simultaneously open an empty Excel workbook and an Access database from which you want to import a table or query. When both are open, resize each application's window so that they're both fully visible on your screen.
Excel
Before you send out your finished Excel dashboard, it's worth your time to step back and ask some key questions. Use these questions as a checklist before distributing your dashboard.
Does your dashboard present the right information?
Look at the information you are presenting and determine whether it meets the purpose of the dashboard identified during requirements gathering.
Excel
In many cases, you may not need to show all icons when applying the Icon Set in Excel. In fact, showing too many icons at one time may serve only to obstruct the data you're trying to convey on the dashboard.
IIn the real world, you often need to bring attention to only the below-average values. This way, your eyes aren't inundated with superfluous icons.
OneDrive provides an ideal web-based platform to store and share your Excel dashboards and reports. As you publish multiple Excel workbooks to OneDrive, you may find the need to do things such as delete them, rename them, copy them, share them, and move them around.
You can follow these steps to manage the workbooks you publish to OneDrive.
Excel
A dashboard environment in Excel may not always have enough space available to add a chart that shows trending. In these cases, Icon Sets are ideal replacements, enabling you to visually represent the overall trending without taking up a lot of space.
The following figure illustrates this concept with a table that provides a nice visual element, allowing for an at-a-glance view of which markets are up, down, or flat over the previous month.
Excel
Excel
A data model provides the foundation upon which your Excel reporting mechanism is built. One of the most important concepts in a data model is the separation of data, analysis, and presentation. The fundamental idea is that you don't want your data to become too tied into any one particular way of presenting that data.
Excel
Bars and Icon Sets give you a snazzy way to add visualizations to your dashboards; you don't have a lot of say in where they appear within the cell, as you can see in this figure:
Showing Data Bars inside the same cell as values can make it difficult to analyze the data.
By default, the Data Bars are placed directly inside each cell, which in this case almost obfuscates the data.
Excel
In Excel, an effective and informative way to display performance against a target is to plot the variances between the target and the performance. The standard way to display performance against a target is to plot the target and then plot the performance. This is usually done with a line chart or a combination chart, such as the one shown here.
Excel
If you're going to use slicers on an Excel dashboard, you should do a bit of formatting to have your slicers match the theme and layout of your dashboard. The following discussion covers a few formatting adjustments you can make to your slicers.
Size and placement of slicers
A slicer behaves like a standard Excel shape object in that you can move it around and adjust its size by clicking it and dragging its position points.
Excel
One of the challenges you can encounter when building data models in Excel is a data table that expands over time. That is to say, the table grows in the number of records it holds due to new data being added. To get a basic understanding of this challenge, take a look at the following figure.
The date in both the table and chart ends in June.
Excel
This best practice for building Excel data models is simple. Make sure your data model does what it's supposed to do before building dashboard components on top of it. In that vein, here are a few things to watch for:
Test your formulas to ensure they're working properly. Make sure your formulas don't produce errors and that each formula outputs expected results.
Dynamic labels in Excel are labels that change according to the data you're viewing. With dynamic labeling, you can interactively change the labeling of data, consolidate many pieces of information into one location, and easily add layers of analysis.
A common use for dynamic labels is labeling interactive charts.
The HLOOKUP function in Excel is the less popular cousin of the VLOOKUP function. The H in HLOOKUP stands for horizontal. Because Excel data is typically vertically oriented, most situations require a vertical lookup, or VLOOKUP.
However, some data structures are horizontally oriented, requiring a horizontal lookup; thus, the HLOOKUP function comes in handy.
Excel
Color is most often used to separate the various sections of an Excel table. The basic idea is that the colors applied to a table suggest the relationship between the rows and columns. The problem is that colors often distract and draw attention away from the important data. In addition, printed tables with dark-colored cells are notoriously difficult to read (especially on black-and-white printers).
Excel
Wanting to keep your Excel data model limited to one worksheet tab is natural. Keeping track of one tab is much simpler than using different tabs. However, limiting your data model to one tab has its drawbacks, including the following:
Using one tab typically places limits on your analysis. Because only so many datasets can fit on a tab, using one tab limits the number of analyses that can be represented in your data model.
In Excel, it isn't difficult to use report and dashboard interchangeably. In fact, the line between reports and dashboards frequently gets muddied. It's helpful to clear the air and understand the core attributes of what are considered to be reports and dashboards.
Defining reports in Excel
The report is probably the most common application of business intelligence.
Load More Articles
Related Books
0:00
0:00
https://cdn.prod.website-files.com/6630d85d73068bc09c7c436c/69195ee32d5c606051d9f433_4.%20All%20For%20You.mp3
Frequently Asked Questions
No items found.
