Brazil vs Germany Chart
Here is another installment of Do It In Excel series. I call this data visualization Brazil vs Germany Chart. Let’s see how can you build this nice chart in Excel.
Here is the data visualization we are going to re-create in Excel. It is taken from: http://greatlatitude.com/visual-data/
I tried to make this chart as functional as I can. You can change numbers and chart figures will change accordingly. Chart will be able to show up to 100 dots in each side. But to change colors and teams, you need to adjust things manually.
Here is the data set we need to prepare for our Brazil vs Germany Chart.
From this dataset, we are going to create a gauge chart, you can see how to create one from this post. For quick pointers, just select cells A1 to B4 and insert a donut chart. Then delete legend, title, etc. and leave only the donut. After that, adjust rotation of the chart and donut hole size. Then adjust colors and you are done.
Doing these will transform your chart like you see below:
Here is how I arranged donut charts hole size and angle of first slice.
Colorful dots are made using conditional formatting. To set them up, I created two sets of cells in another sheet. First set is for yellow dots and the second set is for red dots. This setup will allow our chart to respond to changes in data (when goal number changes from 39 to 45, 45 dots will be visible). Let’s say this is Sheet3.
Now we need to arrange two 25×4 cell field with a gap between in our chart sheet. Fields are for dots and gap is for the donut. You can see how to set this up below:
Select numbers on the left and apply conditional formatting (Icon Sets->3 Traffic Lights). Here is how it is supposed to look like:
Now select a conditionally formatted cell and access conditional formatting settings from Home-> Conditional Formatting -> Manage Rules -> Edit Rule. Arrange rule settings as shown below. Click OK and click Apply on the next screen.
Find the cell that has “1” as value and type in following formula:
(Assuming corresponding cell is Sheet3!Z3 (check the preparation above), Brazil goal amount is inputted into Sheet1!C2)
Copy this formula into all the cells formatted in left side. This formula will set first 39 cells to have 100 and rest have 99 as value. You will notice 39 cells closest to donut will have yellow dots. Just like the original chart.
Arrange right side exactly like the left, with dot color as red. You can see the expected result below when finished. Our Brazil vs Germany Chart is almost ready.
From here on all you need to do is to add textboxes with no background (no fill) and no borders (no outline). Just remember not to type into them. Instead, select the textbox and click on formula bar. Input “=source cell for text”. For example, input =Sheet1!A2 for the text box that will read “Brazil”.
Set size, font and positioning of text boxes. Hide gridlines from View ribbon and you are done.
Here is the finished Excel version of Brazil vs Germany Chart:
You can download and inspect finished chart from this link: Brazil vs Germany Chart