Aug
10
Advanced Excel – Gauge Chart ”; Previous Next A Gauge is a device for measuring the amount or size of something, for example, fuel/rain/temperature gauge. There are various scenarios where a Gauge is utilized − To Gauge the temperature of a person, a thermometer is used. To Gauge the speed of an automotive, a speedometer is used. To Gauge the performance of a student, a mark sheet is used. Gauge charts came into usage to visualize the performance as against a set goal. The Gauge charts are based on the concept of speedometer of the automobiles. These have become the most preferred charts by the executives, to know at a glance whether values are falling within an acceptable value (green) or the outside acceptable value (red). What is a Gauge Chart? Gauge charts, also referred to as Dial charts or Speedometer charts, use a pointer or a needle to show information as a reading on a dial. A Gauge Chart shows the minimum, the maximum and the current value depicting how far from the maximum you are. Alternatively, you can have two or three ranges between the minimum and maximum values and visualize in which range the current value is falling. A Gauge chart looks as shown below − Advantages of Gauge Charts Gauge charts can be used to display a value relative to one to three data ranges. They are commonly used to visualize the following − Work completed as against total work. Sales compared to a target. Service tickets closed as against total service tickets received. Profit compared to the set goal. Disadvantages of Gauge Charts Though the Gauge charts are still the preferred ones by most of the executives, there are certain drawbacks with them. They are − Very simple in nature and cannot portray the context. Often mislead by omitting key information, which is possible in the current Big Data visualization needs. They waste space in case multiple charts are to be used. For example, to display information regarding different cars on a single dashboard. They are not color-blind friendly. For these reasons Bullet charts, introduced by Stephen Few are becoming prominent. The data analysts find Bullet charts to be the means for data analysis. Creating a Gauge Chart You can create Gauge charts in two ways − Creating a simple Gauge chart with one value − This simple Gauge chart is based on a Pie chart. Creating a Gauge chart with more number of Ranges − This Gauge chart is based on the combination of a Doughnut chart and a Pie chart. Simple Gauge Chart with One Value We will learn how to prepare the data and create a simple Gauge chart with single value. Preparation of Data Consider the following data − Step 1 − Create data for Gauge chart as shown below. Step 2 − The data will look as follows − You can observe the following − C7 contains the value corresponding to C2. C5 has 100% to represent half of the Pie chart. C6 has a value to make C6 and C7 to be 100% that makes second half of the Pie chart. Creating a Simple Gauge Chart Following are the steps to create a simple Gauge chart with one value − Step 1 − Select the data – C5:C7. Step 2 − Insert a Pie chart. Step 3 − Right click on the chart. Step 4 − Select Format Data Series from the dropdown list. Step 5 − Click SERIES OPTIONS. Step 6 − Type 90 in the box – Angle of first slice. As you can observe, the upper half of the Pie chart is what you will convert to a Gauge chart. Step 7 − Right click on the bottom Pie slice. Step 8 − Click on Fill. Select No Fill. This will make the bottom Pie slice invisible. You can see that the Pie slice on the right represents the Profit %. Step 9 − Make the chart appealing as follows. Change the Fill colors of the Pie slices. Click on the right Pie slice, select 3-D FORMAT as Top bevel, and choose Angle. Click on the left Pie slice, select 3-D FORMAT as Top bevel, and choose Divot. Click on the right Pie slice, select 1% as Point Explosion under SERIES OPTIONS. Click on the right Pie slice and add Data Label. Size and Position the Data Label. Deselect Legend in Chart Elements. Give the chart Title as Profit % and Position it. Your Gauge chart is ready. Gauge Chart with Multiple Ranges Now let us see how to make a gauge chart with more ranges. Preparation of Data Arrange the data for values as given below. This data will be used for Doughnut chart. Arrange the data for Pointer as given below. You can observe the following − The value in the cell C8 is the value you want display in the Gauge chart. The value in the cell C9 is the Pointer size. You can take it as 5 for brevity in formatting and later change to 1, to make it a thin pointer. The value in the cell C10 is calculated as 200 – (C8+C9). This is to complete the Pie chart. Creating Gauge Chart with Multiple Ranges You can create the Gauge chart with a Doughnut chart showing different regions corresponding to different Values and a Pie chart denoting the pointer. Such a Gauge chart looks as follows − Step 1 − Select the values data and create a Doughnut chart. Step 2 − Double click on the half portion of the Doughnut chart (shown in yellow color in the above chart). Step 3 − Right click and under the Fill category, select No Fill. Step 4 − Deselect Chart Title and Legend from Chart Elements. Step 5 − Right click on the chart and select Format Data Series. Step 6 − Type 271 in the box – Angle of first slice in the SERIES OPTIONS in the