Excel Dashboards & Reports For Dummies book cover

Excel Dashboards & Reports For Dummies

By: Michael Alexander Published: 04-05-2022

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.

Articles From Excel Dashboards & Reports For Dummies

page 1
page 2
page 3
page 4
page 5
45 results
45 results
Excel Dashboards and Reports For Dummies Cheat Sheet

Cheat Sheet / Updated 03-10-2022

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. This Cheat Sheet provides some useful information and tips for working with Excel dashboards and reports.

View Cheat Sheet
Format Numbers in Thousands and Millions in Excel Reports

Article / Updated 10-15-2021

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. After the Format Cells dialog box opens, click the Custom option to get to the screen shown in this figure. In the Type input box, add a comma after the format syntax. #,##0, After you confirm your changes, your numbers will automatically appear in the thousands! The beauty part The beautiful thing is that this technique doesn’t change your numeric values, or truncate them, in any way. Excel is simply making them look cleaner. Take a look at this figure: The selected cell has been formatted to show in thousands; you see 118. But if you look in the formula bar above it, you’ll see the real unformatted number (117943.605787004). The 118 you are seeing in the cell is a simpler version of the real number shown in the formula bar. Custom number formatting has obvious advantages over using other techniques to format numbers to thousands. For instance, many beginning analysts would convert numbers to thousands by dividing them by 1,000 in a formula. But that changes the integrity of the numbers dramatically. When you perform a mathematical operation in a cell, you are changing the value represented in that cell. This forces you to carefully keep track of and maintain the formulas you introduced to make the numbers easier to read. Using custom number formatting avoids that by changing only how the number looks, keeping the actual number intact. Making numbers even clearer If you like, you can even indicate that the number is in thousands by adding to the number syntax. #,##0,"k" This would show your numbers like this: 118k 318k You can use this technique on both positive and negative numbers. #,##0,"k"; (#,##0,"k") After applying this syntax, your negative numbers also appear in thousands. 118k (318k) What about millions? Need to show numbers in millions? Easy. Simply add two commas to the number format syntax in the Type input box. #,##0.00,,"m" Note the use of the extra decimal places (.00). When converting numbers to millions, it’s often useful to show additional precision points, as in 24.65m More digestible numbers are easy in Excel, once you know these little tricks.

View Article
9 Chart Design Principles

Step by Step / Updated 03-27-2016

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. If the data you're reporting can be more effectively shared in a table, that's how it should be presented. Remember that the goal of a dashboard is not to present everything in a chart — it's to present key data in the most effective way possible.

View Step by Step
How to Use Slicers as Form Controls in Excel Dashboards

Step by Step / Updated 03-27-2016

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.

View Step by Step
Limit Icons from the Icon Set on Your Excel Dashboard

Step by Step / Updated 03-27-2016

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. Excel provides a clever mechanism to allow you to stop evaluating and formatting values if a condition is true. In this example, you want to remove the Check icons. The cells that contain those icons all have values above the average for the range. Therefore, you first need to add a condition for all cells whose values are above average. To do so, follow these steps:

View Step by Step
10 Excel Chart Types and When to Use Them

Step by Step / Updated 03-27-2016

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. Here is a rundown of the chart types most frequently leveraged in dashboards and reports.

View Step by Step
Adding Extra Layers of Analysis to Your Excel Charts

Article / Updated 03-26-2016

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. Although this solution works, there is a problem: As the underlying data changes, the text boxes don't reflect the new data. In addition, the text box placement is static. So as the chart columns grow and shrink, the text boxes don't move up or down with the columns. Instead of using independent text boxes, a better solution is to make the extra layer of analysis part of the visualization itself. In this case, we can incorporate the extra growth percent analysis into the chart. Here's how: Add a helper series using formulas that essentially adds a few points to the actual data you're charting. The idea here is to have the helper series be slightly greater than the actual data so that the labels for the helper series end up above the series for the actual data. Create a new Column chart using both the actual data and the newly created helper series. Right-click the helper series, select Format Series, and then choose to place the series on a Secondary axis. Moving the helper series to the secondary axis will allow you to give it its own labels. Right-click the Secondary Axis labels and select Delete. You won't want these fake numbers to show up. Right-click the helper series and choose the Select Data option. In the Select Data Source dialog box, click the Helper Series entry and then click the Edit button. Edit the Category Labels input to point to the cells which contain the category labels for your helper series. These are the labels that will actually show up on the chart. In this case, the category labels will be the growth percentages for each respective quarter. Right-click the helper series and choose Add Data Labels. Right-click the newly added data labels and select Format Data Labels. Choose to show only the Category Name as the data labels. Right-click the helper series and select Format Data Series. In the Fill options, click No Fill. This will ensure the helper series is invisible. There you have it. Your extra analysis is now incorporated into the chart. Because the growth percentages are part of the chart (not just text boxes), the growth percentage labels will keep up with changes in the chart. This ensures that the extra analysis will always stay with the data.

View Article
The Art of Dynamic Labeling in Excel

Article / Updated 03-26-2016

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. In the figure below, you see a pivot chart that shows the Top 10 Categories by market. When the market is changed in the Filter drop-down list, the chart changes. Now, wouldn't it be nifty to have a label on the chart itself that shows the market for which the data is currently being displayed? To create a dynamic label within your chart, follow these steps: On the Insert tab in the Ribbon, select the Text Box icon. Click inside the chart to create an empty text box. While the text box is selected, go up to the formula bar, type the equal sign (=), and then click the cell that contains the text for your dynamic label. The text box is linked to cell C2. You'll notice that cell C2 holds the Filter drop-down list for the pivot table. Format the text box so that it looks like any other label. You can format the text box using the standard formatting options found on the Home tab. If all goes well, you'll have a label on your chart that changes to correspond with the cell to which it's linked. In the figure seen here, the France label within the chart is actually a dynamic label that changes when a new market is selected in the drop-down list. What would happen if you were to link your text boxes to cells that contained formulas instead of simple labels? A whole new set of opportunities would open up. With text boxes linked to formulas, you could add a layer of analysis into your charts and dashboards without a lot of complex hocus pocus. Take this simple example. Here, you see two views of the same pivot chart. On the top, the Northwest market is selected and you see that the pivot chart is labeled with a layer of analysis around Q4 variance. On the bottom, Southeast is selected and you can see that the label changes to correspond with the analysis around Q4 variance for the Southeast market. This example actually uses three dynamic labels. One to display the current selected market, one to display the actual calculation of Q4-2003 vs Q4-2003, and one to add some contextual text that describes the analysis. Take a moment to examine what's happening here. The label showing 41% is linked to cell B13 which contains a formula returning the variance analysis. The label showing the contextual text is linked to cell C13, which contains an IF formula that returns a different sentence depending on whether the variance percent is an increase or decrease. Together, these labels provide your audience with a clear message about the variance for the selected market. This is one of countless ways you can implement this technique.

View Article
Great Online Resources for Excel Dashboards and Reports

Article / Updated 03-26-2016

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. A generous amount of articles and examples can be found at his site and his blog. Edward Tufte’s website: Professor Edward Tufte is an icon in the field of information design and data visualization. He shares his thoughts around visual communication in a series of articles at his site. Although many of these ideas are academic in nature, they’ll get you thinking in new ways about how to best present data. Chandoo’s blog: Chandoo (aka Purna Chandra) came out of nowhere around 2007 to amaze us all with his innovative Excel tips via his blog Chandoo.org. He has a knack for clean, simple visualization techniques — which he shares freely to all his readers. Jon Peltier’s website: Although his is not a site dedicated to dashboarding, Jon Peltier offers over 200 pages of unique and effective Excel charting ideas. A quick look at the excellent tutorials posted on his site will have you reeling with new ideas on how to chart your Excel data. ExcelCharts.com: Jorge Camoes's blog and website is focused on helping his readers make sense of their business data through better data analysis and visualization techniques. With several years’ worth of articles, his site proves to be rich source dashboarding concepts. The Dashboard Spy: The Dashboard Spy posts examples of business intelligence dashboards, pointing out examples of good and bad dashboard design. This is a virtual warehouse of dashboarding ideas. Juice Analytics: Zach and Chris Gemignani of Juice Analytics use their site as a platform to critique charts and offer interesting ideas around reporting data.

View Article
Adding Symbol Fonts to Your Excel Dashboards and Reports

Article / Updated 03-26-2016

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. Make a formula that returns a character, then change the font so that the symbol for that character is shown based on the font you select. For example, imagine you want to test if values in column A are greater than 50. You can enter the formula =IF(A1>50,”P”,”O”). In a standard font like Arial, this formula would return either a P or an O. However, if you would change the font to Windings2, you would see a check mark or an X.

View Article
page 1
page 2
page 3
page 4
page 5