Gauge chart

Gauge Chart in Excel :-

Gauge chart in Excel also known as Speedometer chart.

This chart is basically a combination of 2 charts, 1^st Doughnut chart & 2^nd Pie chart

First we will make Doughnut chart which is a base to place pie chart on it -

To make doughnut chart we requires some measures which will show the strength of area like RED area, Yellow area & the safest area which is green, we will use below mentioned table to make this chart

Performance	Measure	Color
Bad	40%	Red
Average	20%	Yellow
Good	40%	Green
Base	100%	White

We require half (100% out of 200%) of the chart to be hidden so will color this area white

To create Doughnut chart follow mentioned instructions –

·         Select Doughnut chart from Insert chart menu under other charts

·         Remove the legends & heading

·         Right click on any of the slice of the chart area and select Format Data series

·         Rotate chart by 270 degree under series options

·         Right click outside the circle and select No fill under Fill option

·         Also click on No line under border color

·         And change the slice color as per table mentioned above

Now we will make another part of Gauge chart which is Pie chart –

Pie chart will capture your actual performance %age, by changing this %age your chart will change automatically

Performance	Measure
Performance	50%

Here we are assuming 50% is your current performance which can be change manually as per the actual performance of the company

Now the below mentioned table will make Pie chart

	Data	Color
Performance - 1%	49%	White
1%  Gap to highlight	1%	Gray
	150%	White

In this table 1% is nothing but to show the line on the point of performance %age like below

To create this chart –

·         Select Pie chart from insert menu under charts

·         Remove the legends

·         Add Data Labels

·         Right Click on any slice of Pie chart and select format Data series

·         Rotate chart by 270 Degree under series options

·         Right click just outside the circle & select No Fill under Fill option

·         Also select No line under border color

·         Change the circle color as per Table mentioned

·         Remove data levels except 1%

·         Select 1%, Press equal to sign & click on performance value Cell (50%) to show performance %age

After doing this our chart will look like

Fit Pie chart over Doughnut chart & do formatting like below

·         Make a round circle to fit middle of the doughnut chart

 

·         Make a shield to protect your charts to avoid unnecessary clicks

Place circle & shield at appropriate place & your Gauge chart is ready

Please write your suggestions to make it better

Right Function

To extract the characters from right side from a text string based on the no of character specified

Syntax

RIGHT(text,num_chars)

How to extract last name where last name is having different number of letters in different cells

	B	C	D
22	Names	Last name	Formula
23	Rajeev Sharma	Sharma	=RIGHT(B23,LEN(B23)-FIND(" ",B23))
24	Rohit Bansal	Bansal	=RIGHT(B24,LEN(B24)-FIND(" ",B24))
25	Amit shrivastava	shrivastava	=RIGHT(B25,LEN(B25)-FIND(" ",B25))

Len Function returns the length of selected text and finds the position of selected characters (Space)

Now to get no of letters after space just subtract the find length from total length

Let see how we did it

Rajeev Sharma	Sharma	=RIGHT(B23,LEN(B23)-FIND(" ",B23))
Total Length	13
Length till space	7
Last name letters	13-7 =	6 (Sharma)

Left Function

 

To extract the characters from a text string based on the no of character specify from left
Syntax
LEFT(text,num_chars)
	B	C	D
8	Names	First Name	Formula
9	Rajeev Sharma	Rajeev	=LEFT(B9,FIND(" ",B9)-1)
10	Rohit Bansal	Rohit	=LEFT(B10,FIND(" ",B10)-1)
11	Amit shrivastava	Amit	=LEFT(B11,FIND(" ",B11)-1)
Find function which is used with left finds the space and returns the position of space

INDEX & MATCH function

INDEX formula is used to extract the value from array by giving row & column references

Syntax –

=INDEX(array,row position,column position)

Example –we have provided you the one Region / Month table(array) where we are going to apply this function

7	B	C	D	E	F
8	Region	Jan	Feb	Mar	Apr
9	East	2	3	4	5
10	West	6	2	4	3
11	North	11	2	2	1
12	South	3	1	1	3

Now here we are going to takeout data for Jan month & North Region

The references for this combination in the table would be 1^st column & 3^rd Row

Then formula would be -

Result	Formula
11	=INDEX($C$9:$F$12,3,1)

The Row & column position can be find out through MATCH function also just by giving selection criteria

Syntax –

=MATCH(lookup_value,lookup_array,match_type)

Selection Table for criteria–

9	B	C	D	E
10	Field	Criteria	Result	Formula
11	Region	North	3	=MATCH($C$27,$B$9:$B$12,0)
12	Month	Jan	1	=MATCH($C$28,$C$8:$F$8,0)

Match function will return the Region(North) position as 3 & month (Jan) position as 1

Now we are going to use Index with match function for same result

Result	Formula
11	=INDEX($C$9:$F$12,MATCH($C$28,$B$9:$B$12,0),MATCH($C$29,$C$8:$F$8,0))