Chapter 4. Making Your Data Visually Appealing and Meaningful with Formatting, Conditional Formatting, and Charts

Now that we have our data elements for a dashboard, we'll start pulling the pieces together and format them. When presenting on dashboards, I often talk about the dashboard in my car. Yes, I know, so does everyone else, but I have a different twist on it. I talk specifically about the gas tank gauge. If it wasn't there, how often would you buy gas, or worse, run out of gas? No doubt, we'd all carry around a canister of spare gas, just in case. With the normal gas gauge of empty to full, you can figure out how much gas you have with a little math, provided you know how big your tank is. Trust me, nobody wants me driving while doing math in my head. Would that be a Driving While Ciphering (DWC)? Anyway, with the newer visuals of how far I can go with the gas I have in my tank, based on my average driving, I can make informed decisions. That's what a financial dashboard is all about—allowing you to make informed decisions.

Tip

My personal definition of business intelligence is making informed business decisions based on timely and accurate information.

In this chapter, we will start assembling and formatting our dashboard with the help of the following topics:

• Get pivot data
• Excel formatting
• Icon sets
• Sparklines
• Data bars
• Charts
• Slicers and timelines
• Additional formatting

The idea behind conditional formatting is that we want data elements to change visually based on the value of the data. The simplest example is the classic way of presenting positive numbers in black and negative numbers in red. With the conditional formatting options in Excel, we can do so much more. However, first, we have to start building our dashboard.

Note

The presentation of negative numbers in red is where we get the English phrase "in the red" to describe a company that is losing money.