Ever found yourself needing to visualize data progression in Excel, but found the built-in charts lack the pizzazz you desire? Look no further than the thermometer chart! Not only does it stand out, but it's also incredibly useful for displaying progress, be it sales targets, project milestones, or even personal goals. Let's dive into how to create this eye-catching chart type in Excel.
First, let's ensure we understand what a thermometer chart is. Unlike your standard bar or line chart, a thermometer chart presents data as a fill-in-the-blank thermometer. The 'blank' part is your data, and as it increases, so does the color fill. By the end, you'll have a fully-colored thermometer that clearly communicates your data.
Preparing Your Data
A good thermometer chart starts with well-organized data. You'll need two columns: one for your target values and one for your actual values. For example:
| Target Sales ($) | Actual Sales ($) |
|---|---|
| 5000 | 3500 |
| 10000 | 8500 |
| 15000 | 13000 |
In this instance, the actual sales values are less than the targets, so our thermometers will show some empty space, emphasizing the shortfall.
Creating the Thermometer Chart
Now that your data is ready, let's create the chart. Select both columns of data, then click on the 'Insert' tab in the ribbon. In the 'Charts' group, click on the 'Column' option. Select the first column chart type, then click 'OK'.
Your basic column chart is now inserted. To begin transforming it into a thermometer chart, click on the chart. In the 'Design' tab under 'Chart Style', select any style to ensure all chart elements are visible. Then, click on the columns representing your target data (usually the first column).
Formatting Your Thermometers
We're almost there! Now it's time to give your thermometers their iconic fill. Click on the 'Format Selection' button (or press Ctrl + 1). In the 'Fill' gallery, choose ' meziately. This will make your thermometers appear as filled and unfilled sections.
To finish, click on the border of the chart. In the 'Format Selection' pane, under 'Border', set the 'Weight' to 'None'. This will remove the border, leaving you with a clean, tailored thermometer chart.
Customizing Your Chart
With the basics down, it's time to make your thermometer chart truly your own. Consider adding a chart title, axes labels, and a legend to provide context. You could also change the fill colors or apply a gradient to give your chart a professional sheen.
For even more customization, consider combining your thermometer chart with other chart types. For example, you could add a line to show trends over time or use secondary axis to show another set of data. The possibilities are endless!
Using Conditional Formatting for Automatic Updates
To make your thermometer chart truly dynamic, apply conditional formatting to your data. This ensures your chart updates automatically whenever your data changes. Here's how:
Select your actual values data, then click on 'Conditional Formatting' in the 'Home' tab. Under 'Highlight Cells Rules', choose 'Equal to'. In the 'Format cells that are equal to' dialog box, set the 'Format cells if true' options to match your fully filled thermometer color.
Now, your chart will update automatically as your actual values approach your targets. Isn't data visualization supposed to be this easy and engaging?
So there you have it: a professional, engaging thermometer chart that will surely catch the eye of anyone looking at your Excel data. Happy charting!