Dashboard Resume - Part 1
Having the most important information available, focused on your audience, and telling a good story is essential for any great presentation. Also, it helps people understand the data and make better decisions.
In this article, I will show you how to create an easy and fun "Dashboard Resume" in Excel, and the knowledge acquired can be applied to other dashboards.
You will create the dashboard template and links. Why not use your own data later :)
Lets have some #DashboardFun
As you can see to create this nice Dashboard Resume, I did some quick research on Elon Musk (if you don't know him, his resume should tell a lot), using information from Novoresume, Wikipedia, and some personal assumptions; for instance, I tried to guess his level of English and Afrikaans :)
*** Before we start, I have few comments on Warm-up, Download File, and Colors.
Warm-up
If you are beginning your journey in the exciting world of Dashboard Creation, and have none or little experience with excel, I invite you to do some warm-up exercises to give you the basics.
Here are two nice and useful warm-ups that will help you to start your Dashboard journey:
Article a:Create beautiful Infographics in Excel
But, if you already have experience creating charts, and know the basics of Excel, go ahead; you should be fine.
Download File
The downloadable file available has three tabs: the first tab called "Dashboard" is the place that you will create your Dashboard Resume, the second tab calls "Model", and it has the complete template; third and last is called "Data" has the main data you will be using.
Click on the link below, sign up, and download the file.
Download the file here:
Colors
This dashboard is using six different colors and the "no fill", or transparent.
Below you will find the color guide and their RGB codes (Red, Green, and Blue).
1- To set the proper RGB codes you just need to go to "More Colors".
2- When the window "Colors" opens, go to the "Custom" tab, and change the numbers according to the Color Guide above. The example below is showing how to set the "Series Red" - RGB: 250,81,62.
Dashboard Resume - Part 1
1- If you are not using the downloadable file, and you want to start from scratch, Double-click the first tab and rename it "Dashboard"; add a new tab and rename it "Data".
2- Download the file to copy the data that we will use. If you want to save time, we can use my file that already has the tabs, data, and the Dashboard model.
3- Go to Page Layout tab, Sheet Options and deselect Gridlines View.
4- Select the range "A2:X53", and change the color. Use the "Main Background - RGB: 255,255,255" listed in the top of this article.
5- Go to "Format Cells", "Border". Pick the "Border Color RGB: 191,191,191" and select "Outline" then press OK.
6- Select the Range "E4:Q12" and change the color to "Section Color RGB: 230,230,230".
7- Do the same in the range "S4:W12.
8- Change the width of "Column D" to "3,00" or "26 pixels". Do the same to "column R" and "Column X".
9- Copy and paste the profile image, and contact icons, available in the "Data tab".
10- Select range "E4:Q5". Go to the "Home" tab and select "Merge & Center". Do the same for the range "E6:Q12", and marge this range.
11- Link the data available in the "Data tab" by typing "=Data!H1". The summary data should be linked.
12- Change the summary text format to: "Text Size 14", Color "Body Text - RGB: 38,38,38". Go to Alignment and click on "Increase Indent".
13- Change the profile name: "Text size 24", title color "Title - RGB: 46,67,84", and click on "Increase Indent".
14- Insert a "text box".
Change its background color to "No Fill".
Then, select border and type the formula "=Data!B2".
15- Change the text: "Text Size 14", "Body Text - RGB: 38,38,38", .
16- Hold "Ctrl key" and select the icon, and the text box. With both selected, go to "Format tab", and click on "Align Middle".
17- Create three new "text boxes", and do the same, linking the phone, email, and twitter.
18- Go to "Data tab" and select the range "A7:C14".
19- Go to "Insert" tab, "Charts" group and select "Stacked Bar".
20- With the new chart selected go to "Design" tab and select "Select Data".
21- Click on "Add"and include a new "Series values" as image below.
22- Include the "Series name" as well and click OK.
23- Do the same for "Study Start".
24- Change the order of each series by using the "Move" button. Your series should look as image below.
25- Right click an individual series and click on "Format Data Series".
26- Change the series colors. "Work Start" with "No fill" || "Work Days" Blue - RGB: 46,67,84 || "Study Start" with "No fill" || "Study Days" Red - RGB: 250,81,62.
27- The result should be as image below.
28- Click on vertical axis, and go to "Format Axis". Go to "Axis Options" and select "Categories in reverse order".
*** To move the horizontal axis, double click the horizontal axis, go to "Labels", and select "Label Position" as "High".
29- Select the chart area and go to "Format Chart Area". Change the "Height" and "Width" as below and select "Don't move or size with cells".
30- Go to Format Axis and change the Fill and Line.
31- With the chart selected, go to "Chart Elements", and let only the "Axes" option on.
32- Click on any series and go to "Format Data Series". Change the "Gap Width" to 338%.
33- Double click the "Vertical Axis" and change the size to 14, and text color "Body Text - RGB: 38,38,38".
34- Double click the "Horizontal Axis" and change the size to 12, and text color to "Body Text - RGB: 38,38,38".
35- Select the Chart and change the "Shape Outline" to "No Outline".
36- Right click the chart and click on "Move Chart".
