In this chapter, we introduce the concept of a dashboard in a spreadsheet.
Use this workbook for the chapter.
A dashboard is used to summarize key information. For example, if you were driving, you would use the car’s dashboard to gather information about the performance of the vehicle and the status of certain tools such as temperature, lights, and notifications about issues in the car. It wouldn’t tell you everything about what's going on in the engine, but it could tell you some of the most important things that you might need to know while driving.
An Excel spreadsheet can be used in a similar way: we can create a dashboard that summarizes key information for the user. This summary of information helps users find what they’re looking for without feeling overwhelmed with unnecessary content. This chapter will walk you through how to build a dashboard. See Figure 36.1 to see what your dashboard will look like by the end of this lesson
This dashboard is going to be created using a data sheet with information about people at a company. It has some information about each salesperson, their age, gender, where they live, their cell phone number, and their sales history for the last five years.
We will simply summarize information for just one salesperson on that dashboard and highlight the key information that we want to see for that individual.
There are a number of different LOOKUP functions that you can use in Excel. VLOOKUP, HLOOKUP, Index, Match Combination formula, XLOOKUP formula, and many others are different ways to correctly pull back information to the dashboard. In this chapter, only one of them will be demonstrated, but you are encouraged to search online for how to use some of those additional methods. Please do not feel that just because this particular method is being demonstrated in this section that it is the only acceptable method for this class.
We’re going to use something called the Offset function. The offset function is used to reference a cell that is a certain distance away from the selected cell. Using the formula, you dictate where the offset should be in regards to the selected cell by typing how many rows up or down or how many columns to the left or right you’d like to move to select the offset. Negative numbers will move rows up and columns left. If the row or column should not change, you can put 0 for that argument. The formula will require the first three arguments to be entered: reference, rows, and columns. (See Figure 36.3)
Complete the following to use the Offset function.
When using functions, we want to avoid having to repeatedly edit cells. In this example, if you dragged the formula across the rows, you’ll see that it just keeps Carl’s age (50). To fix this issue, we should anchor the lookup array properly. We don’t want to anchor the dashboard reference in C5, but we do want to reference the following lookup array to be the same every time. See Figure 36.5 for an example of correct formatting.
After anchoring the array, you should be able to drag the formula across and see the correct information from the previous sheet. You may need to use the format painter to make sure you get the right number format.
Now we have all the information about our selected salesperson on the dashboard. The next step is to take the time to lay out the dashboard in a way that makes it easy for the user to see and understand.
On a dashboard, we want to make sure we are following the standards of professionalism.
The Sales Contribution section ends up being one of the most important pieces of information in this example, so, let’s emphasize it.
Next, for Sales contribution, you will complete some calculations based on the particular numbers in that chart.
As always, be sure to add labels to the horizontal axis and vertical axis.
You also may want to format and add markers to this line.
It’s important that you understand what information is most needed on the chart. For example, because we show the sales amount on the axis, we don’t need to label each data point. Those amounts are also listed out above in a table view in case the users want to see the specific numbers. This chart is used to see the trend of those numbers.
Now, you can see we have a nice visible trend between the actual point and the trajectory it took to get to the next point.
The numbers associated with the various types of years are some summary statistics about this particular person’s sales.
Some other things that can be done to tighten it up a bit might include changing the column widths so that things are aligned more nicely.
Like many other things in Excel, dashboards are a matter of personal preference. The principles in this chapter can be applied in different ways depending on what is being presented. For this example, we wanted to summarize the information for a given associate on a single page, so making it easy for the user to quickly pick a name and see the information was important.
As you work on your own dashboards, keep readability and usability in mind.