In this chapter, we introduce the concept of a dashboard in a spreadsheet.
Use this workbook for the chapter.
Getting Started with Dashboards
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.
- Rename the sheet with information to Data, and then add another sheet called Dashboard.
Move the dashboard to the left of the workbook. We usually start with the dashboard all the way to the left because, in English, we read left to right.
- Create some named ranges.
- Select the range of salespeople from Alice to Kent
- While this range is selected, navigate to the name box in the top left beside the formula bar, and name that range Person.
- This can also be done by right-clicking the selected range, selecting “Define Name,” and replacing “Alice” with “Person.”
- Go to the Dashboard sheet and select a cell to be your input cell. Format the input cell using the following steps:
- In the Styles group of the ribbon, you can select a type of cell. Select the Input cell.
- Right-click on the Input cell option and select Modify. This will prompt a style box.
- Select Format and change the fill color of the cell to light yellow.
- Make the font Arial size 18, and change the color of the text to black
Next, we’ll add some data validation. It will be easier for the user if they can simply select the name of the salesperson from a drop-down list. To set up this drop-down list, complete the following:
- In the tools bar at the top of the Excel page, select the Data tab.
- In the Data Tools group, select Data Validation.
- In the dropdown option for what to allow, select List. (See Figure 36.2)
- Where it prompts you to type a source, type =person to reference the named range we created in the Datasheet and select OK.
- This will give you a drop-down list that allows you to choose the name of the salesperson.
- Name the input cell Selected.
- Copy the headings of the information from the data page and paste them into the dashboard.
- To paste, use the Paste button in the home tab and select the first option under Paste Values.
Functions in a Dashboard
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.
- Below the Age column, we’re going to choose an offset function by typing =OFFSET and selecting a cell to reference.
- Your reference will be found on the Data page. Select Salesperson as the reference.
- Use F4 to anchor it.
- The number of rows is going to be determined by an embedded MATCH formula.
- In the formula bar, after the anchored reference, type MATCH.
- The lookup value will be the selected person. Type selected into the formula, followed by a comma.
- The lookup array will come from the list of persons that you already created. Type person into the formula bar and select that option.
- Then, choose an Exact Match for the match type. (See Figure 36.4)
- For the column, look up again using a MATCH function.
- This time, the lookup value will be the Age cell on the dashboard.
- The lookup array will be the headers from Age to Year 5.
- Again, the match type will be 0, or exact match.
- There are optional parameters for height and width, but we will leave those blank for simplicity’s sake.
- Press Enter. Notice how in the dashboard, it now pulls Carl’s age back as 50.
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.
Formatting a Dashboard
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.
- Select a cell below the information you just entered on the dashboard and type Personal Information to create a new section or area to place content.
- Create another section below that’s called Contact Information.
- Create a third section to the right called Sales Contribution.
- To make the titles consistent, use a cell style to set them apart.
- For example, you can try using Heading 3, and apply it to all three sections by selecting them while holding down the CTRL key.
- From the information select Age and Gender along with their corresponding answers, and drag those four cells beneath Personal information.
- You can drag the cells so that the layout is horizontal rather than vertical. (See Figure 36.6)
- Complete the same actions for the other two sections.
- Home State and Phone will go under Contact information.
- Years 1–5 will go under Sales contribution. (See Figure 36.7)
- Make the row height of the row for the name taller by right-clicking on the desired row on the side, selecting Row Height, and changing it to 40.
- Select the two cells occupied by the name, Edward, and use the Merge and Center feature in the Alignment section.
- Make sure the Input style is applied to this cell.
On a dashboard, we want to make sure we are following the standards of professionalism.
- In the View tab, uncheck the Gridlines box.
- By right-clicking on the letter A at the top of the first column and selecting Column Width, make the first column width 4.
- Delete the extra rows between Edward and Personal information and leave only one row between the end of the Personal Information section and the start of the Contact information section. (See Figures 36.8 and 36.9)
The Sales Contribution section ends up being one of the most important pieces of information in this example, so, let’s emphasize it.
- Move that section up.
- Clean up any additional formatting errors that might keep your dashboard from appearing professional.
- This includes things such as getting rid of any unnecessary space or extra formatting and making sure things are aligned and appear uniform in size.
- Widen the column so that the phone number fits within the cell.
- Left-align all the text within the personal and contact information.
- Put a border around the personal information and the contact information by selecting one extra cell around the information and apply the border.
- You may need to move this section in order to be able to select all the cells around it.
- With the cells selected, open up the Format Cells box and choose a border.
- Select a gray border to outline the boxes.
- Format the Contact information to match the Personal information box.
- Move the Salesperson name dropdown cell to be centered with the information boxes, and change the column width to the right and left of it to 4.
- Select the columns between the columns you just adjusted and change their width to 14. (See Figure 36.10)
- This will give you enough space to see everything clearly.
Next, for Sales contribution, you will complete some calculations based on the particular numbers in that chart.
- Below the Sales contribution section, make space for these calculations in their own cells. In the cell to the right of the specific years below, use simple functions to look them up. You may need to use the format painter to adjust any numbers that don’t come out as dollar amounts.
- Top year
- For this use, use the =MAX function for the range of sales contributions through the years.
- Average year
- Use the =AVG function of the same range.
- Standard deviation (STDEV)
- Standard deviation just measures the variability around the average.
- Use =STDEV.S of the same range.
- Worst year
- Calculated with the =MIN function of this range.
- Include a chart of his sales.
- Select the sales information.
- Select Insert.
- Choose a line chart.
- There are a number of existing format styles, and if you hover over them, you can see what they might look like. (See Figure 36.11)
As always, be sure to add labels to the horizontal axis and vertical axis.
- Call the vertical axis Sales.
- Because each point is labeled as Year One, Year Two, Year Three, Year Four, and Year Five, it actually would be duplicative to have a title. In this particular instance, you can omit the horizontal axis label.
You also may want to format and add markers to this line.
- Press CTRL+1 while the line is selected, it pulls up the formatting options.
- Make the line a little thinner by making it 1.5.
- In Marker Options, select Built-in.
- Make the marker a circle the size of 7, and choose the fill as a Solid Fill color. In this case, select white.
- Use a solid line on the border, making it the same size as the line at 1.5, and the same color as the 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.
- Label the chart 5-Year Sales History.
The numbers associated with the various types of years are some summary statistics about this particular person’s sales.
- Title it Summary Sales Statistics and format it using Heading 3 across both columns occupied by the title.
- Again, add a border around the outside.
- Move Contact information and Personal information down to make room for Summary Sales Statistics.
- Move the graph to be in line with the Sales Contribution Data.
- It may be a little too narrow, so make the columns for this information have a width of 12.
- Make the columns to the sides a little bit more narrow at a width of 4.
- Now, create an offset of one cell around the entire thing for the border, using the same gray border.
- Choose outline and select OK.
- You don’t actually need a border around the chart as well, so the shape outline can be removed by selecting No Outline. (See Figures 36.12 and 36.13)
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.
- Hide all of the rows below the dashboard and the columns to the right.
- Changing the width of the rows to zero is the same thing as hiding it.
- This way, it’s very clear that if we pick a different salesperson, we’re able to see what that information is for that particular person.
- Let people know on a dashboard who they should contact if they have questions, and when the data was updated. Add a note at the bottom that says, Data last updated August 2021.
- Depending on what type of information is on the dashboard, you may also want to include the specific date and time. In this example, we’re looking at years worth of history, so a month is an appropriate time.
- Give information on who a user should contact if they need additional information by adding another note beside the date that says Contact email@example.com with any questions.
- Use the Explanatory Text feature found in the Styles tab to make it fade into the background.
- List the company’s name at the top, or put it above the drop-down list.
- Insert a row above the dropdown and call it Company Sales History by Associate. Then format the content by using a heading format.
- Select all columns across the top, choose Heading, and then choose to left-align. This will create a very clear title here at the top.
- If the company had a logo, which in this case it does not, it could be included at the top, and that would help make it look additionally even more professional.
- The last change to make on this is to make the width of the input box match the width of the other boxes below. To help it look consistent, if we add that same look and feel here where we choose input, and then we choose to merge and center across all of the cells within the dropdown, it makes it look a little bit more complete and planned out as we take a step back and look at the whole dashboard. (See Figure 36.14)
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.