exceltoxl.com

Hi there,

 Share on Facebook

 

Microsoft has given us an in-built or standard charts to use in Excel. However, most of the excel users developed their own charts using lot of creativity. One among them is Thermometer Chart. Let's discuss those charts one by one in different posts. Here, we see the list of inbuilt charts present in Excel application: 

 

these types of charts are used to display the numbers in a pictorial manner/representation to the higher management. Pictorial representation of data will definitely helps the management to understand the data or numbers which will help the business and to know where it stands in the market in terms of financial aspects as well as growth with the competitors. 

COLUMN CHARTS ARE USED AT THE MOST:

...

I strongly believe most of the excel users would have used Column Chart when compared to other charts while choosing a chart. Right? If you ask me I will definitely say YES to it. 

Today, we will see how Column Chart will help to prepare a Thermometer look.

Here, I have chosen thermometer chart because the data with me is Actual value vs Target value. Of-Course the actual value percentage too. To start with, here is the raw data:

 

IMPORTANT CONSTRAINTS: 

In the above pic we have three constraints that needs to concentrate 

1. Monthly Unique Visitors (B12) to exceltoxl.com
2. Target Visitors Value per month (B13) and
3. % of Visitors, monthly (B14)
   

 

COLUMN CHART PROCEDURE:

Let's start with a Column Chart using Monthly Visitors data: 

• Firstly, select the cell B12 (unique visitors)
• Goto INSERT tab and click on "Columns" in Charts group
• Select 2D column chart and click on OK (three columns will appear)
• Right Click on the chart and click on Select Data
• Select Data Source window will appear
• Click on rectangular shape icon next to "Chart Data Range"
• Click on B12 cell to select (because the chart should show only Unique Visitors column chart)
• Again, click on rectangular shape icon and enter OK

Now, right click on the chart and select last option Format Axis.

"Format Axis" window will appear.

APPLY COSMETICS AND WEAR DRESS TO THE CHART:

Here use cosmetics as well as dressing for further formatting. In "Format Axis" window

AXIS OPTIONS: 

• Minimum: Fixed to 0 (zero)
• Maximum: Fixed to 1000 (per target value here)

In the "Format Plot Area" window, Goto Border Color --> select Solid Line -->Click on Color--> choose 4th color below white color.

Now, right click on the border of the chart and choose "Format Chart Area" option -->Border Color -->No Line --> click close.

This completes adding up cosmetics to the column chart. ;)

 

FINAL TOUCH UP:

Goto "Insert" tab and click on "Shapes" in 'illustration' group to choose "OVAL" shape from "Recently Used Shapes". Draw a oval shape and fit below the column chart and adjust accordingly, thermometer shape,.

Now, select the oval shape and goto FORMAT TAB (drawing tools) -->Shape Styles -->click on "Intense Effect - Accent 1" style. Similarly the column chart as well.

Now, select the Oval Shape and type =B14 in the formula bar and press ENTER.

That's it the percentage value on the OVAL shape will change dynamically as we change the month from drop down in B8.

 

compare with original Thermometer

Finally,

 

 

DOWNLOAD EXAMPLE FILE.xlsx

 

 

 

 

 

 

 

DO YOU USE THERMOMETER CHART???

Have you worked on thermometer chart earlier, Did you find any challenges while working on chart, Have you achieved challenges while working on charts, then this is the right place to use for sharing your ideas, thoughts, achievements, applause to the world of excel users.

Did you find this topic interesting, then please do share this post on your social networking sites from the icons placed around the front page. To support me for maintaining this blog as well as to receive latest posts through e-mail, Subscribe here for free. I am sure your details will be secured and will not be displayed to the public.

SOME IMPORTANT LINKS:

Cooked  Dynamic Charts 

Cooked Dynamic Chart Flavor 1

Difference between Column Chart and Bar Chart

Camera Tool has a Shortcut Key

 

 

Thanks,