SAP - Advanced Excel Chart

Back to Course

Lesson Description

Lession - #487 Advanced Excel Charts – Gauge Chart

A Gauge may be a device for measuring the number or size of one thing, for instance, fuel/rain/temperature gauge.

There are varied eventualities wherever a Gauge is utilized-

• To Gauge the temperature of someone, a measuring device is employed.
• To Gauge the speed of associate degree automotive, a meter is employed.
• To Gauge the performance of a student, a mark sheet is employed.

Gauge charts came into usage to see the performance as against a group goal. The Gauge charts are supported the construct of meter of the cars. These became the foremost most well-liked charts by the executives, to understand at a look whether or not values are falling at intervals a suitable value(green>
or the surface acceptable value (red>

What is a Gauge Chart?
Gauge charts, additionally brought up as Dial charts or meter charts, use a pointer or a needle to indicate data as a reading on a dial. A Gauge Chart shows the minimum, the utmost and also the current valueportraying however aloof from the utmost you're. or else, you'll be able to have 2 or 3 varys between the minimum and most values and visualize during which range the present value is falling.

Advantages of Gauge Charts
Gauge charts are often wont to show a valuerelative to 1 to a few information ranges. they're ordinarily wont to visualize the subsequent –
• 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 popular ones by most of the executives, there are sure drawbacks with them. They are-
• Very easy in nature and can't portray the context.
• Often mislead by omitting key data, that is feasible within the current massive information image desires.
• They waste house just in case multiple charts are to be used. for instance, to show data relating to totally different cars on one dashboard.
• They aren't color-blind friendly.

For these reasons Bullet charts, introduced by author Few are getting distinguished. {the information|the info|the information} analysts notice Bullet charts to be the means that for data analysis.

Creating a Gauge Chart
You can produce Gauge charts in 2 ways in which –
• Creating an easy Gauge chart with one value: this straightforward Gauge chart relies on a chart.
• Creating a Gauge chart with additional variety of Ranges: This Gauge chart relies on the mix of a Doughnut chart and a chart.

Simple Gauge Chart with One value
We will find out how to arrange the info and build an easy Gauge chart with single value.

Preparation of information
Consider the subsequent information –
Step 1: produce information for Gauge chart as shown below.
Step 2: the info can look as follows –

You can observe the subsequent –

• C7 contains the worth similar to C2.

• C5 has 100% to represent 1/2 the chart.

• C6 includes a valueto form C6 and C7 to be 100% that produces last half of the chart.

Creating an easy Gauge Chart
Following are the steps to make an easy Gauge chart with one value-
Step 1: choose the info – C5:C7.
Step 2: Insert a chart.
Step 3: Right click on the chart.
Step 4: choose Format information Series from the dropdown list.
Step 5: Click SERIES choices.
Step 6: kind ninety within the box – Angle of 1st slice.
As you'll be able to observe, the higher 1/2 the chart is what you may convert to a Gauge chart.
Step 7: Right click on all-time low Pie slice.
Step 8: Click on Fill. choose No Fill.
This will create all-time low Pie slice invisible.
You can see that the Pie slice on the correct represents the cash in on.
Step 9: create the chart appealing as follows –

• Change the Fill colours of the Pie slices.

• Click on the correct Pie slice, choose three-D FORMAT as high bevel, and opt for Angle.

• Click on the left Pie slice, choose three-D FORMAT as high bevel, and opt for Divot.

• Click on the correct Pie slice, choose I Chronicles as purpose Explosion underneath SERIES choices.

• Click on the correct Pie slice and add information Label.

• Size and Position the info Label.

• Deselect Legend in Chart parts.

• Give the chart Title as cash in on and Position it.

Your Gauge chart is prepared.

Gauge Chartwith Multiple Ranges
Now allow us to see a way to create a gauge chart with additional ranges.

Preparation of information
Arrange the info for values as given below.

This information are going to be used for Doughnut chart. organize the info for Pointer as given below.

You can observe the subsequent –

• The valuewithin the cell C8 is that the valueyou would like show within the Gauge chart.

• The valuewithin the cell C9 is that the Pointer size. you'll be able to take it as five for brevity in info and later modification to one, to form it a skinny pointer.

• The valuewithin the cell C10 is calculated as two hundred – (C8+C9>
. this can be to complete the chart.

Creating Gauge Chart with Multiple Ranges
You can produce the Gauge chart with a Doughnut chart showing {different|totally totally different|completely different} regions similar to different Values and a chart denoting the pointer. Such a Gauge chart appearance as follows –
Step 1: choose the values information and build a Doughnut chart.
Step 2: Double click on the 0.5 portion of the Doughnut chart (shown in yellow colorise the higher than chart>
Step 3: Right click and underneath the Fill class, choose No Fill.
Step 4: Deselect Chart Title and Legend from Chart parts.
Step 5: Right click on the chart and choose Format information Series.
Step 6: kind 271 within the box – Angle of 1st slice within the SERIES choices within the Format information Series pane.
Step 7: modification the Doughnut Hole Size to five hundredth within the SERIES choices within the Format information Series pane.
Step 8: modification the colours to form the chart appealing.
As you'll be able to observe, the Gauge chart is complete in terms of values. consequent step is to own a pointer or needle to indicate the standing.
Step 9: produce the pointer with a chart as follows -

• Click on the Doughnut chart.

• Click the planning tab on the Ribbon.

• Click choose information within the information cluster.

• Select information supply panel seems. Click the Add button.
Step 10: The Edit Series panel seems.

• Select the cell containing the name Pointer for Series name.

• Select the cells containing information for value, Pointer and finish, i.e. C8:C10 for Series values. Click OK.
Step 11: Click OK within the choose information supply panel.

• Click the planning tab on the Ribbon.

• Click modification Chart kind within the kind cluster.

• Change Chart kind panel seems. choose jazz band underneath the tab All Charts.

• Select the chart varieties as following-

o Doughnut for valueseries.

o Pie for Pointer series.

• Check the box Secondary Axis for the Pointer series. Click OK. Your chart appearance as shown below.

Step 12: Right click on every of the 2 larger Pie slices.

• Click on Fill then choose No Fill. Right click on the Pointer Pie slice and choose Format information Series.

• Type 270 for Angle of 1st slice within the SERIES choices. Your chart appearance as shown below.

Step 13: Right click on the Pointer Pie slice.

• Click on Format information Series.

• Click on Fill & Line.

• Select Solid Fill for Fill and choose the colour as black.

• Select Solid Line for Border and choose the colour as black.

Step one4: modification the Pointer valuefrom five to 1 within the information to form the Pointer Pie slice a skinny line.

Step 15: Add a data Label that depicts to complete.
Your Gauge chart is ready.