Excel Advanced Charting – 24 Examples

There are many types of advanced charting in Excel.

Excel Advanced Charting


Download Practice Workbook

Download the practice workbook here.


 

Example 1 – Creating Combo Charts

The following dataset showcases Month, Target Sales, and Achieved Sales.

Dataset for Creating Combo Charts

  • Select the entire data and go to the Insert tab.
  • Click Insert Combo Chart.
  • Select Clustered Column – Line.

Creating Combo Charts

The Combo Chart is displayed.

Output of Creating Combo Charts


Example 2 – Dynamic Column Chart with Slicers

The dataset contains Month, Target Sales, Achieved Sales, Variance, and Percentage.

Dataset of Creating Dynamic Column Chart with Slicers

  • Create a 2-D column chart: select two sets of data and go to Insert.
  • Click Insert Column or Bar Chart.
  • Click Clustered Column to create a 2-D column chart.

Select the intended data and insert a 2-D column chart by click on Clustered Column

  • Select one set of columns (Achieved Sales) and click Chart Elements.
  • Click the extension of Error Bars  and choose More Options… .

Select the Achieved Sales in the chart, expand the Chart Elements option and later expand Error Bars options

  • Select Error Bar Options in Format Error Bars.
  • Set the Direction to Both in Vertical Error Bar.
  • To set the error amount from the table, click Custom in Error Amount.
  • Click Specify Value to define the error amount from the table.

In the Error Bar option, check Both from Direction, check Custom from Error Amount and click on Specify Value

  • Define zero values for the Positive and the Negative Error Value options, choose Variance.
  • Click OK.

Insert Zero as positive error value, choose variance for negative error value, and hit OK

Error bars are set.

  • To increase the width and decrease the gap between the bars, select the bars and go to Format Data Series.
  • Set Series Overlap and Gap Width to 0%.

Now click on a bar, click on Format Data Series and set the Series Overlap and Gap Width to 0%

  • To see the slicers only and remove the column bar in Achieved Sales, select Achieved Sales and go to the Format.
  • Select Shape Fill and click No Fill.

Click on the achieved series, go to the Format tab, expand Shape Fill, and click on No Fill

  • To label the slicers, select all slicers and click Chart Elements.
  • Click the extension of Data Labels and select More Options…

Click on the achieved series agian, expand chart elements, expand data labels and click on More Options

  • Go to Error Bar Options and check Value From Cells.
  • Define the labels from the dataset (F5:F10) and click OK.

Go to Format Data Labels, check value from cells, choose percentage values from column F and hit OK

  • You can change the slicers label positions in Label Position. Here, Inside End.

Change the label position to Inside End

  • Customize column colors, label fonts, colors, etc .

further customize the chart from the available options


Example 3 – Doughnut Progress Chart

  • This is the sample dataset.

Dataset for Doughnut Progress Chart as Advanced Chart

  • Select the dataset and go to Insert.
  • Choose Insert Pie or Doughnut Chart.
  • Click Doughnut.

Generating Doughnut Progress Chart

The doughnut chart will be created.

  • To define the doughnut chart with the completion percentage, go to Insert and click Text.
  • Choose Text Box.
  • Draw a text box in the middle of the doughnut chart, select it and use the following formula:
=Doughnut!$C$7

Adding Completed Project Amount in Doughnut Progress Chart

Doughnut defines the sheet name and C7 defines the cell value to show in the text box.

  • To modify the text, select Font in the Home tab.

Doughnut Progress Chart as Advanced Chart


Example 4 – Creating an Interactive Histogram Chart

Use the following dataset to create an interactive Histogram Chart.

Dataset for Creating Interactive Histogram Chart

  • Select the dataset and go to the Insert tab.
  • Click Insert Static Chart on the ribbon and select Histogram.

Creating Interactive Histogram Chart

  • Double-click the chart rectangle.
  • In Format Data Series,  increase the Gap Width in Series Options.

 Changing Gap between Columns

  • Click the bins.
  • In Format Axis, select Axis Options and change the Bin width.

Changing Axis Gap

  • Name the graph (Histogram Chart), remove gridlines, and edit the chart.

Histogram Chart as Advanced Chart


Example 5 – Annual Trend Chart with Monthly Detail

Use the FORECAST.LINEAR function to predict a future amount and create an annual trend chart.

Dataset for Annual Trend Chart with Monthly Detail

  • Use the following formula to predict future sales based on previous sales.
=FORECAST.LINEAR(B14,$C$5:$C$13,$B$5:$B$13)

Predincting Future Sales

  • Enter the sales value of month 9 into D9.
  • Go to the Insert tab and select Insert Scatter or Bubble Chart.
  • Select Scatter with Straight Lines and Makers.

Creating a Scatter Chart

The Annual Trend Chart with Monthly Detail is displayed.

Annual Trend Chart with Monthly Detail


Example 6 – Creating a Dynamic Waterfall Chart

To create a Waterfall Chart, consider the dataset below.

Dataset for Creating Waterfall Chart

  • Select B4:C12.
  • Go to the Insert tab >> click Waterfall, Funnel, Stock or Surface Chart >> select Waterfall Chart.

Creating Waterfall Chart

  • Now select the end bar. The Format Data Point toolbar will open.
  • Check Set as total.

Clicking Set as Total Option

  • Select the first bar and check Set as total.

Creating Dynamic Waterfall Chart


Example 7 – Clustered Charts with Variance

Consider the dataset with the amounts listed between Target Sales and Actual Sales and their variances.

Dataset of Clustered Charts with Variance

  • Select the data and go to Insert.
  • In Insert Column or Bar Chart choose Stacked Column.

Creating a Stacked Column

  • Select Target Sales and choose Secondary Axis in Format Data Series.

Turning into Secondary Axis

  • Choose Achieved Sales as Secondary Axis.
  • Select Achieved Sales in the chart and click Insert.
  • Go to Insert Column or Bar Chart and choose Clustered Column.

Turning Secondary Axis into Clustered Column

  • The Clustered Chart with Variance will be displayed.

Rough Clustered Charts with Variance

  • Edit the chart.

Final Clustered Charts with Variance


Example 8 – Distribution Chart with Comparability

Create a dataset.

Dataset for Distribution Chart with Comparability

  • Select the entire dataset and go to Insert.
  • Click Clustered Column in Insert Column or Bar Chart.

Creating Clustered Column Chart

  • Select the Total column and right-click.
  • Select Change Series Data Type…

Changing Series Chart Type

  • In Change Chart Type, set the Chart Type to Line.

Changing Series Chart Type

  • Change the Chart Type from Baseline to Line.

Output After Changing Series Chart Type

  • Select the Total line and click Chart Elements.
  • Check Up/Down Bars.

Changing Series Outline

  • Decrease the Gap Width (40%).

Changing Gap Width

  • Remove the Total and Baseline lines by clicking No line in Fill.

Making Line Invisible

  • Select the bars and choose Gradient fill in Fill.
  • Set the Gradient stops to the top and bottom and remove extra stops.
  • Set a color for the top gradients stop.

Filling Column with Color

  • Choose White color for the bottom stop and make it 100% transparent.

Filling Column with Color and Transparent

  • Add Data Labels, remove the Total and Baseline from the grapes and edit the chart.

Excel Advanced Charting


Example 9 – Actual vs Target Chart

Create an Actual vs Target Chart by modifying the Clustered Column chart.

  • To create an outline outside the actual and target amounts, use the following formula in the Max Invisible and Max Outline columns.
=MAX(C5:D10)+500

Dataset of Creating Actual vs Target Chart

  • Select the entire dataset and go to Insert.
  • Go to Insert Column or Bar Chart and choose Clustered Column.

 Creating Clustered Chart

  • Select all Target Sales columns and go to Fill & Line.
  • In Fill, select No fill.
  • In Border, choose Solid line.
  • Select a color for the outline and set the width: 1.5 pt.

Changing the Column Outline

  • Select the Actual Sales column and change the color in Fill & Line.

Changing the Column Outline

  • Select the Max Invisible columns and go to Fill & Line.
  • In Fill, select No fill.
  • In Border, choose Solid line.
  • Select a color for the outline and set the width: 2 pt.

Changing the Column Outline

  • Select the Max Outline columns and go to Fill & Line.
  • In Fill, select No fill.
  • In Border, choose Solid line.
  • Choose a color (similar to the actual sales color) for the outline.

Changing the Column Outline

  • Select any column and go to Series Options.
  • Set the Series Overlap to 100%.

Making Overlap of Columns

  • The Actual vs Target chart is displayed.

Creating Actual vs Target Chart as Excel advanced charting

Note
We can not see the target sales mark if the achieved value exceeds that value.

Example 10 – Milestone Chart

Consider the following dataset.

Dataset for Creating Milestone Chart

  • Select the dataset and go to Insert.
  • Select Insert Line or Area Chart and click Line with Markers.

Creating a Line Chart with Markers

  • Right-click the chart and go to Select Data…

Go to Select Data

  • Delete the available series by clicking Remove.
  • Click Add to create a new series.

Removing the existing Data

  • Define a series (Time), and the range C5:C12.
  • Click OK to create a series.

  • Create the Activity series.

Adding Another New Series

  • Click the chart and right-click.
  • Choose Change Series Data Type…

Changing Series Chart Type

  • Click the extension of Chart Type in the Activity series and select Stacked Column.
  • Click OK.

Changing Chart Type

  • To edit the horizontal axis, go to Edit.

Changing the Horizontal Series

  • Define the range from the dataset.

Changing the Horizontal Series

  • Now select the data labels and go to Label options.
  • Check Category Name and uncheck the other options.

Defining Labels

  • Go to Chart Design and choose Add Chart Element.
  • Select Error Bars and click More Error Bars Options…

Setting Error Bars

  • Set the error bars direction to Minus in Vertical Error Bar.
  • Set the End Style to No Cap and the Percentage to 100%.

Setting Error Bars

  • Select the columns and choose No fill in Fill and No line in Border.

Removing Column

  • Finalize your chart by editing it.

Creating Milestone Chart as Excel advanced charting


Example 11 – Gantt Chart

Create a Gantt chart by modifying the Stacked Bar chart.

  • Create a dataset.

Dataset for Gantt Chart

  • Select C4:C12 (the Start Date column), and go to Insert.
  • Choose Insert Column or Bar Chart in Charts.
  • Select Stacked Bar in 2-D Bar.

 Creating Stacked Bar

  • Right-click the chart and click Select Data.

Selecting Select Data

  • In Select Data Source, click Add.

Selecting Add Option

  • In Series name, enter E4.
  • Select E5:E12 in Series values.
  • Click OK.

Defining Series Range

  • Click Edit.

Changing Horizontal Axis

  • Select B5:B12 in Axis Labels and click OK.

Changing Horizontal Axis

  • Click the axis.
  • In Format Axis, check Categories in reverse order.

Changing Category in Reverse

  • Select all blue bars by double-clicking them.
  • In Format Data Series, choose No fill in Fill & Line.

Disappearing Unnecessary Bar

  • Find the earliest task date in Minimum Value and the latest date in Maximum Value.
  • Select the cells and turn them into General format.

Changing Format of Maximumm and Minimum

  • To adjust the label values, double-click the horizontal axis label.
  • In Format Axis, enter the Minimum and Maximum values manually in Axis Options.

Changing Axis Format with Maximumm and Minimum

  • Customize your Gantt Chart.

Final Gantt Chart as Advanced Charting


Example 12 – Bell Curve

The Bell Curve is a graph that represents the normal distribution of a variable. It is also known as Normal Distribution Curve. The peak point of this Curve is the mean of distribution.

  • Calculate the Mean, Std Deviation, 7% Low, 99.7% High, and Gap using the following formulas with the values from Score.

Formula for Mean:

=AVERAGE(C5:C12)

Formula for Std Deviation:

=STDEV.P(C5:C12)

Formula for 99.7% Low:

=C14-3*C15

Formula for 99.7% High:

=C14+3*C15

Formula for Gap:

=(C17-C16)/C18

Calculating Necessary Parameters fro Bell Curve

  • The first value is from C16.

First Value from tha Calculated Parameters

  • Select the D6:D12 and enter this formula.
=D5+$C$19

The interval value is used to get the other values using this formula.

Calculating Other Value from tha Calculated Parameters

  • Select E5:E12 and use this formula.
=NORM.DIST(D5,$C$14,$C$15,FALSE)

The formula returns the normal distribution for the given mean and standard deviation. We have set these values in the code. The Cumulative was set to FALSE to get the “probability density function”.

Calculating Normal Values

  • Select  D5:E12.
  • Go to the Insert tab >>> Insert Scatter (X,Y) or Bubble Chart >>> select Scatter with Smooth Lines.

Generating Graph from the data

This will be the basic Bell Curve.

  • Double-click the horizontal axis.
  • In Format Axis, set the Bounds:

Minimum: 30.
Maximum: 90.

Changing Axis Dimension

  • Customize your chart.

Final Bell Curve as Excel advanced charting


 Example 13 – Sales Funnel Chart

  • To create a Sales Funnel Chart with the Sales Stage and Deal Value, create a Helper Column with the following formula:
=(LARGE($D$4:$D$9,1)-D6)/2

Calculating Helping Values

  • Select the entire dataset and go to the Menu Bar.
  • Click Insert >>> Insert Column or Bar Chart >>> Stacked Bar.

Creating Stacked Bar Chart

  • Double-click the vertical axis and select Categories in reverse order in Axis Options.

Categorize the Axis in Reverse

  • Select the helper data in the chart.
  • In Fill & Line, choose Solid fill and make transparency to 100%.

Make the helping Column Transparency to 100%

  • In Axis Options, set the Gap Width to 0%.

Diminishing the Gap

  • Customize the chart.

Final Sales Funnel Chart as Excel advanced charting


Example 14 – Pareto Chart

Consider the following dataset to create a  Pareto Chart.

Dataset for Creating Pareto Chart

  • Select the entire dataset and go to the Insert tab.
  • Select Insert Static Chart and choose Pareto in Histogram.

Creating Pareto Chart

  • This is the output.

Final Pareto Chart as Excel advanced charting


Example 15 – Bullet Chart

  • Create a dataset with quantitative bands, and actual and target values.

Dataset for Bullet Chart

  • Select the entire dataset and go to Insert.
  • In Insert Column or Bar Chart, click Stacked Column.

Creating a Stacked Chart

  • Select the chart and click Switch Row/Column in Chart Design.

Switching Row or Column

  • Right-click and hold the cursor. Select Change Series Chart Type…

Changing Column from Select Data

  • Change the Chart Type of the Target Value to Stacked Line with Markers.

Changing Chart Type

  • Check Secondary Axis and click OK.

Turning Into Secondary Axis

  • Select the secondary axis and delete it.

Delete Secondary Axis

  • Select the marker and change its format.
  • Go to Fill & Line >>> Marker.
  • In Marker Options, select Built-in, set Type to Dash, and Size to 30.
  • In Fill, select Solid fill, and choose a color.
  • In Border, select No line.

Defining Markers Format

  • Go to Change Series Chart Type… and set the Actual Value to Secondary Axis.

Checked Actual Value as Secondary Axis

  • Select the Actual Value column and set the Gap Width to 400%.

Changing the Gap Width

  • Select the bands one by one and change their color in Fill.

Changing the Color of those Ranges

  • The bullet chart is displayed.

Final Bullet Chart as Excel advanced charting


Example 16 – Step Chart

Create a dataset.

Basic Dataset for Step Chart

  • Copy the entire dataset to a new location (E4).
  • Select  E5 and F15 and right-click.
  • Click Delete.

Select and Delete Cell after Copying

  • Select Shift cells up and click OK.

 Setting Shift Cells Up to Delete Cell

  • Copy B5:C15 and paste it into E15.

Copying the Entire Dataset

  • Select E4:F25 and go to Insert.
  • In Insert Line or Area Chart, select Line.

Inserting Chart

  • Customize your Step Chart.

Final Step Chart as Excel advanced charting


Example 17. Waffle Chart

To create a waffle chart, you need a special type of dataset.

  • Create a dataset as shown below.

Dataset for Waffle Chart

  • Select B4:K13 and click Conditional Formatting in the Home tab.
  • Select New Rule.

Selecting New Rule from Conditional Formatting

  • In New Formatting Rule, select Format only cells that contain in Select a Rule Type.
  • In Cell Value, select less than or equal to and D15.
  • Choose a fill option in Format.

Defining the Rule for Conditional Formatting

  • Click OK.

Output after Applying the Rule

  • Repeat the procedurefor the same range, but with values greater than the value of D15. A gray fill was selected, here.
  • Select the range and press Ctrl+1.
  • In  Number, select Custom format and enter ;;; in Type.

Formatting the Numbers from Dataset

  • Go to the Border tab, select a thick border and White as the color of the border. Make sure both Outline and Inside are selected.

Formatting Cells with Specifications

  • Click OK.

having Rough Waffle Chart

  • Resize the cells.

Final Waffle Chart as Excel advanced charting


Example 18 – Thermometer Chart

To create a Thermometer Chart:

Create a dataset with total profit and targeted profit.

Creating Dataset for Thermometer Chart

  • Select B17:C18 and go to Insert.
  • Click Clustered Column in 2D Chart.

Creating Clustered Column from Data

  • Select any of the data columns, and in Chart Design, click Switch Row/Column.

Switching Between Row and Column

  • Select the chart column and click Secondary Axis in Series Options.

Making an Axis into Secondary Axis

  • Go to Fill and Line.
  • In Fill, select No fill.
  • In Border, click Solid line.
  • Make sure the color of the border matches the column color.
  • Set the Width to 1.25 pt.

Making Change of Chart

  • Enter the Minimum bound as 0 and press enter.
  • Enter 0 in the Maximum bound and press Enter.

Changing Axis Dimension

  • Go to the Insert tab to add a bulb shape below the chart.
  • Click Shapes and choose oval shape.

Inserting Shapes

  • Place the oval at the bottom of the Chart.
  • This is the output.

Final Thermometer Chart as Excel advanced charting


Example 19 – Matrix Chart

Create a 4-Quadrant Matrix chart (it can only comprise 2 sets of values). Use selling prices and cost prices in the dataset below.

Dataset for Matrix Chart

  • Select C4:D8 and go to Insert>> Charts >> Insert Scatter (X, Y) or Bubble Chart >> Scatter.

.Creating a Scatter Chart

  • Double-click the axis to set the upper bound and lower bound limits of the X-axis and Y-axis.

Go to the Axis Options Tab >> expand Axis Options >> set the limit of the Minimum bound as 0.0 and the Maximum bound as 4000.0.

Changing Y-Axis Values

  • Add an additional data range to add the 2 lines (there are 4 quadrants here).
  • In Horizontal,  add the following values in the X and Y coordinates.

X → 0 (minimum bound of X-axis) and 6000 (maximum bound of X-axis)

Y → 2000 (average of the minimum and maximum values of the Y-axis → (0+4000)/2 → 2000)

  • In Vertical, add the following values in the X and Y coordinates.

X → 3000 (average of the minimum and maximum values of the X-axis → (0+6000)/2 → 3000)

Y → 0 (minimum bound of Y-axis) and 4000 (maximum bound of Y-axis)

Additional Data to Create 4 Quadrants

  • Select the graph and right-click.
  • Choose Select Data.

Go to Select Data

  • In the Select Data Source wizard, click Add.

Click on Add to create Series

  • For Series X values, select the X coordinates of the horizontal part of the Quadrant sheet.
  • For Series Y values, select the Y coordinates of the horizontal part.
  • Click OK.

Adding a Series

  • Add another series for the vertical line.

Adding another Series

There will be four points.

Indicating Four Points for four quadrants

 

  • Select the horizontal point and go to Fill & Line.
  • In Line click Solid line.
  • Choose a color.

Turning Points into Line

  • To hide the points, go to Fill & Line tab and select Marker Options.
  • Click None.

Removing the Markers

  • Create a line with the vertical points and remove the gridlines to have a complete matrix chart.

Final Matrix Chart as Excel advanced charting


Example 20 – Meter Chart In Excel

Create a Meter Chart or SPEEDOMETER.

  • Create a dataset with information on vehicle speed. To calculate the end value of the pointer, use a simple mathematical formula:
=200-F5-F6

Creating Dataset for Meter Chart

  • Select B5:C9.
  • Go to:

Insert → Charts → Pie Chart → Doughnut

Creating a Doughnut Chart

  • Double-click a small portion of the chart.
  • In Format Data Series, enter 270 degrees in the Angle of first slice in Series Options.

Making First Slice Angle to 270 Degree

  • Select the bigger portion of the Doughnut chart, and check No fill in Fill.

Diminishing the bigger Value

  • Create a pointer. Place your cursor on the chart and right-click.
  • In the new window, select Select Data.

Entering Select Data Option

  • In Select Data Source, select Add.

Clicking on Add Option

  • In Edit Series, enter =Meter!$E$4 in Series name.
  • Enter =Meter!$F$5:$F$7 in Series values.
  • Click OK.

 Defining Range for Axis

  • Change the type of chart. Place the cursor on the chart and right-click.
  • Select Change Series Chart Type.

Having the Chart Type Option

  • In Change Chart Type, select Pie chart in the pointer and click OK.

Changing Chart Type

  • Select the biggest portion of the Pie chart, and check No fill in Fill.

Setting No Fill for Bigger Portion of Pie Chart

  • Enter 270 degrees in the Angle of first slice in Series Options.

Setting Angle to 270 degree

  • Remove the other bigger portion of the chart, selecting No fill to display the Meter Chart.

Final Meter Chart as Excel advanced charting


Example 21 – Burndown Chart In Excel

Create a Burndown Chart.

  • Calculate the total number of hours in 5 weeks and subtract it from the total estimated hours to find the remaining hours. Use the following formula in I5 and AutoFill the rest cells in column I:
=C5-(SUM(D5:H5))

Calculating Hours Left from Dataset

  • To calculate both Total Burndown and Efforts Left in the Start section of the second table, enter the formula in C14 and C15:
=SUM(C5:C8)

Calculating Total Burndown

  • Add the Estimated Hours and divide them by the total number of weeks to get the resulting hours. Enter the formula in D11 and AutoFillthe column:
=SUM(C5:C8)/5

Calculating Total scheduled Hours

  • To count the completed hours each week, enter:
=SUM(D5:D8)
  • AutoFill the rest of the rows.

Calculating Total Completed Hours

  • To display efforts left per week, enter:
=C13-C12

Calculating Total Efforts Left

  • To calculate the total burndown for each week separately, use:
=C14-D11
  • AutoFill the rest of the range.

Calculating Total Burndown

  • To create a line chart, go to Table-2  and select B11:H14.
  • In Insert, choose Charts.
  • Click Insert Line and Area Chart and choose Line Chart.

Creating Line Chart

  • Change the chart design in Chart Design.

Select a Chart Style

  • Right-click  keeping the cursor on the chart, and select Select Data… .

Picking Select Data Option to create as Excel advanced charting

  • Click Edit in Select Data Source.

 Choosing Edit Option

  • In Axis Labels, select  $C$10:$H$10 and click OK.

Selecting Axis Label Range

 

 

  • To insert the Scheduled Hours and Completed Hours columns to Clustered Column, select a chart line and right-click.
  • Select Change Series Chart Type….

Select Change Series Chart Type

  • Select Clustered Column in Chart Type for Scheduled Hours and Completed Hours and click OK.

Selecting Chart Type to Clustered Column

  • This is the Burndown Chart.

Final Burndown Chart as Excel advanced charting


Example 22 – Excel Chart With Data Table

  • A chart with Target Sales vs Achieved Sales was already created.

Excel Chart With Data Table

  • To add a data table to that chart, select it.
  • Click Chart Elements and check Data Table.

Cheking Data Table from Chart Elements

  • This is the output.

 Final Excel Chart With Data Table as Excel advanced charting


Example 23 – Distribution Chart

Create a Distribution Chart.

  • The dataset shows the names of club Members and their Ages.

Dataset for Distribution Chart

  • Add a column for the bins, here, Age Bracket 1.
  • The Age value starts at 25, so set the starting value of the bin to 20. Choose a Bin Size of 10.
  • Enter the expression below in E8 and AutoFill the column.
=E7+$G$4

Creating Age Bracket 1

  • Calculate the first value in Age Bracket 2 with the following formula
="<="&E7

Creating 1st Element of Range

  • Use the following formula in F7 and AutoFill the column.
=E7+1&"-"&E8

Creating Middle Elements of Range

  • Enter the following formula in F14.
=">="&E13

Creating Last Element of Range

  • Add the frequency column with the header Number of Member and enter this formula.
=FREQUENCY(C5:C14,E7:E13)

Managing Frequency

  • Select the Age Bracket 2 and the Number of Member columns.
  • Go to Insert >>> Insert Column or Bar Chart >>> Clustered Column.

Generating Clustered Column

  • Double-click any of the bars to open the Format Data Series window.
  • Set the Gap Width to 0%.

Setting Gap Width to Zero

  • This is the distribution chart.

 Final Distribution Chart as Excel advanced charting


Example 24 – Comparison Chart In Excel

  • Create a dataset by merging the same states and adding a blank row.

Dataset for Comparison Chart

  • Select the whole dataset.
  • Go to the Insert tab >> Insert Column or Bar Chart >> Clustered 2-D Column.

Creating Comparison Chart

  • Remove the gridlines and add data labels.

Comparison Chart as Excel advanced charting


How to Save a Chart Template in Excel

  • Select a chart and right-click.
  • Click Save as Template…

Save as Template

  • Save it on your computer.

Setting Location for Save


Things to Remember

  • The Pareto analysis only looks at past data. So, it’s important to keep updating data.

Frequently Asked Questions

Q1. Why is text placement used in the Milestone chart?
Text Placement is often used in the Milestone chart to have the equal length of columns on both sides of the main axis.

Q2. How to use a customized template for a chart?
After creating a chart, right-click it and select Change Chart Type. In Templates, you will be able to use the customized templates.

Q3. What is the Difference Between a Histogram and a Pareto Chart?
A Pareto chart is like a histogram, but instead of showing the bins in order of size, they are arranged from the most frequent to the least frequent.


<< Go Back to Excel Charts | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Tags:

Naimul Hasan Arif
Naimul Hasan Arif

Naimul Hasan Arif, a BUET graduate in Naval Architecture and Marine Engineering, has been contributing to the ExcelDemy project for nearly two years. Currently serving as an Excel and VBA Content Developer, Arif has written more than 120 articles and has also provided user support through comments His expertise lies in Microsoft Office Suite, VBA and he thrives on learning new aspects of data analysis. Arif's dedication to the ExcelDemy project is reflected in his consistent contributions and... Read Full Bio

2 Comments
  1. The positive percentage error bars on your Dynamic Column Chart with Slicers are done incorrectly. They should start even with the preceding green bars, but they start higher than that, at the top of the (hidden) orange bars. You should use the Variance column (E5:E10) as the values for the negative error bars and zero for the positive error bars.

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto May 27, 2024 at 1:01 PM

      Dear Jon Peltier

      Thanks for your invaluable feedback and suggestions! You are correct about the positive percentage error bars being inserted incorrectly. Based on your suggestions, we have updated the article section.

      Regards
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo