Spreadsheets are very good at gathering and calculating information, but they aren’t very good at communicating that information. Rows and Columns of numbers and text are a poor way to quickly show what your data is trying to say. That’s why you need a Spreadsheet Dashboard.
When I’m creating a spreadsheet in Numbers or any other spreadsheet software it’s really easy for me to just start entering information into rows and columns. Sure I might do a few things to help make it more readable, but I don’t think about the data’s interpretation. I just think about getting in there and getting the job done.
About a decade ago, I was asked to interpret some data to help my boss make a decision about a direction for the company. We were trying to gather information to make a decision on whether or not a certain business segment should move forward. I jumped into Excel and started entering all my financial and empirical data. And I came to the conclusion that we should move forward.
When my boss and I met to discuss my findings I showed him my spreadsheet and told him my conclusion. After a few minutes, he looked up from my spreadsheet and said, “Well, it’s clear from the data that you gathered that it is not a good idea to move forward.”
How could that be? We both looked at the same data and drew opposite conclusions. Well, it came down to the fact that we both interpreted the data differently.
I went back to my desk and scrolled through Twitter and saw an infographic. Remember those? Infographics are graphics that convey data through bite-sized images and graphs. They are very compelling because when done right you can take a few key points and put easily digestible information in front of a lot of people quickly. Typically, they tell a story from the key data that isn’t visible to everyone who just looks at the numbers.
So, I got to work and created a new sheet on my file with a few key points represented as graphs and key performance indicators.
I went back to my boss and told him that I didn’t think he saw the data the same way I did and I explained to him what I saw and showed him my new charts and KPIs, and it worked! He was actually apologetic about the whole thing. That business segment went on to make the company a whole lot of money too.
Having a dashboard in your spreadsheet will help you understand and read your data better, but what should you include in your dashboard? Well, a quick Google search for ‘Software Dashboard’ will give you a lot of examples to draw influence from.
But here are a few that I like to include.
Key Performance Indicators (KPIs)
Key Performance Indicators or KPIs are boiled down versions of key data sets. For instance, if you had a spreadsheet that tracked employee pay you might want a KPI that shows the average salary per employee, or a KPI that shows how many employees you have, or one that shows how much revenue per employee work hour you are making. KPIs help you interpret your data and make them much easier to understand.
I know graphs sound cliche, but it’s true, a graph can tell you a lot about your data quickly and proficiently. Think about sales data, you can see it in a column, but on a graph,it comes alive. Play around with this you’ll find you have a lot of options.
A progress chart is the easiest way to see how close you are to completing something: a goal, a project milestone, a timeframe. Often they are presented numerically, but could also be presented as a percentage. You can change them up a little.
Let’s say you had a sales goal of 100 sales this year. You could make a small table that has two fields: your goal – 100, and your current number of sales – let’s say 32 for our example. Then you can create a stacked 2D bar chart and make the goal portion the color white, and you’ll have a simple progress chart. Also, using a similar technique you could create a circle chart, And then another table to place the number of current sales that updates automatically. That way you can show your progress and have a number associated with it.
I set these up a lot. They can tell me how close I am to my goals quickly.
I love circle charts when it comes to the total make-up of a large data set. For instance, in budgets, I take all my total spending and categorize it so I can see where I spend my money quickly. These are also good for progress charts as well.
If you want to get really fancy, you could make a circle chart as well as a KPI that represents a percentage or goal and place the KPI underneath the circle chart for a cool effect.
When you set up any Graph, KPI, or Chart make sure they can update automatically when you update your data. You don’t want to have to manually change them all the time.
I’m sure there are more that you could add, but these three are the ones I find most helpful and that I use on a constant basis.
What elements do you include in your dashboards? What elements or features do you wish Apple would release for Numbers to make our dashboards even better?