Skip main navigation

New offer! Get 30% off your first 2 months of Unlimited Monthly. Start your subscription for just £29.99 £19.99. New subscribers only. T&Cs apply

Find out more

Thermometer style custom visual

Column charts can be used as the base for custom visuals in Excel. Watch Paula explain and demonstrate more.
So we need to chart our actual value and we need to chart what’s left. These two values are going to make up a hundred percent, which is the budget value. So, we’ll select these values. So we are going to insert a stacked column chart. I’m going to move this over here, so we can see it. And I’m going to keep our format here beside it, so we can also see this as well. Now I’m going to select the chart from chart design. We’re going to switch rows to columns. What rows two columns will do is stack one on top of the other. We see this value. This orange value up here at the top is the value that is left.
We’re going to change that to no fill. I’m going to put a solid border around so we can actually see the line around it and we put that into black we’ll then select the bottom half which is the actual will change this to solid fill. And we’re going to make it red and increase the transparency of this one to fifty percent. So that makes it, that little bit more transparent. Now, I’m going to delete the legend on the bottom and I’m also then going to delete the chart title. Now we can make this a little bit smaller.
So now we have our actual chart and what we can do now is get her custom visual overlay and start overlaying this on top of our chart. So you can download your custom visual overlay, if there is a copy of it in the workbook. You can also search online and download the custom visual overlay that you want to use. So I’m going to select this one here. I’m going to bring it over here and I am going to say bring it to front. Started bringing it in front of our chart, then what we can do is start lining things up a little bit. So I’m going to select our chart and make it smaller.
So, it’s starting to fit in and we can line it up the top. Of the top, there is a half, a hundred percent. Now the bottom we still need to raise this up a little bit, so the bottom 0 is showing up in our chart there and we can make our custom visual a tiny little bit smaller. So, it just fits that little bit better. Now what we need to do is fill in this bottom part here, so we can go to our insert and on our insert, we can insert a shape, so we’ll select shapes, we will select a circle. So we have a circle here and we can just draw our Circle.
And then we can play around with our circle down. So first of all, we will change the format of our circle so that it is the red color and will also increase the transparency to 50. So, it is the same and we’re going to make sure that it has no line around of. Now what we can do is just make it that bigger a little bit bigger. So it fits into our circle like that, then we have a fitting, we can send it to the back. Now it Is not filling fully for rest there. So we have sent it too far back.
We’re going to click again, and we are going to move it to the front because it’s behind the bottom part of the chart. And now, if we line it up, it’s looking a hell of a lot better. And that is how we can create a custom overlay, a visual custom overlay. So we can see that it is showing that 77%. Let’s go back in here and add a new value. So we can add in, let’s say 500 of a new value and go back into our image overlay. We see the October value has come in.
That we now have our left value of 635, we have our actual value and we have our actual percentage at 80% and our custom visual overlay has updated along with neither is a problem here with this in that axis has changed. So we can go into axis options and inner axis options. We can set this so it is always at 0 so that gives us a better representation now because it is down at zero. So now, if we go back in and we add in another value, let’s say for November and we see it fills up even more on our visual is dynamic. Our calculations are dynamic and everything will update as the new data is entered into your spreadsheet.

It is important that you are able to recreate this custom visual. Using the calculations you prepared in the previous step, take some time now to recreate this custom visual. You can pause and replay this video as necessary.

Comments below:

What are your main takeaways from this video? What other custom visual overlays do you think would make an effective visualization?

This article is from the free online

Design and Create Custom Dashboards in Excel

Created by
FutureLearn - Learning For Life

Reach your personal and professional goals

Unlock access to hundreds of expert online courses and degrees from top universities and educators to gain accredited qualifications and professional CV-building certificates.

Join over 18 million learners to launch, switch or build upon your career, all at your own pace, across a wide range of topic areas.

Start Learning now