Excel Advanced Charting (24 Unique Examples)

This article is about different forms of Excel advanced charting. It includes different types of advanced charts and how to create them in Excel.

To represent our work data and results more clearly, we often use charts. There are many kinds of advanced charting in Excel that help to make the data more presentable. I hope this article will be helpful for you if you are looking for advanced charting.

Download Practice Workbook

You can download the practice workbook from here.

Advanced Charts.xlsx

Excel Advanced Charting and Its Importance

An Advanced Chart is nothing but a type of chart that offers more features and functionality than a basic chart. There are many advanced charts like Combo Charts, Doughnut Progress Charts, Gantt Charts, Bell Curve, Sales Funnel Charts, and so on. They provide a more detailed analysis of the available data.

The importance of advanced charts is beyond description.

i. The advanced charts can provide all the information in a single chart in a simple and detailed form.
ii. They can be more helpful for you to calculate your risk.
iii. They offer more features to present a chart more professionally.
iv. Advanced charts can be a very helpful tool for you to identify trends and patterns in the market.

Excel Advanced Charting: 24 Different Examples

There are many advanced ways to represent data through charting and I am going to discuss about 24 of them in detail in this article. Let’s go through this article.

1. Creating Combo Charts

Combo Charts, a kind of Excel advanced charting, are often used by professionals to compare two sets of data visually and identify any relationships between them. For the explanation of the combo charts, I have considered the following dataset with the Month, Target Sales, and Achieved Sales columns.

Select the entire data first and go to the Insert tab.
Next, click on Insert Combo Chart from the ribbon.
From the available options, pick Clustered Column – Line.

Now, we have Combo Charts on the worksheet.

2. Dynamic Column Chart with Slicers

Dynamic Column Chart with Slicers can be considered as advanced charts too. It will allow you to filter the data in the chart by selecting different values in a slicer. This can be a very useful way to explore data and identify trends.
I have considered the following dataset with the Month, Target Sales, Achieved Sales, Variance, Positive Variance, Negative Variance, and Percentage columns for the simplification of the Dynamic Column Chart with Slicers.

To create a dynamic column chart with slicers, create a 2-D column chart first. For this, select two sets of data and go to Insert.
Now, click on Insert Column or Bar Chart from the ribbon.
From the available options, click on Clustered Column to create a 2-D column chart.

Then, select one set of columns (i.e. Achieved Sales) and click on the Chart Elements option.
Afterward, click on the extension part of the Error Bars option and click on More Options… from the available options.

Now, go to Error Bar Options from Format Error Bars.
Set the Direction to Both from the Vertical Error Bar option.
To set the error amount from the table, click on Custom from the Error Amount option.
Then click on Specify Value to define the error amount from the table.

Now, define values from the dataset to the Positive Error Value and Negative Error Value options from the Custom Error Bars wizard.
Next, click on OK.

Now, error bars are set.

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

In order to have only the slicers and remove the column bar of the Achieved Sales column, select the columns of Achieved Sales and click on the Format tab.
Go to Shape Fill and click on No Fill from the available options.

To have the labels of the slicers, select all the slicers and click on the Chart Elements option.
Now, click on the extension part of the Data Labels option and select More Options…

Go to error Bar Options and check the box named Value From Cells.
Define the labels from the dataset (i.e. H5:H10) and click on OK.

You can change the label positions of the slicers from the Label Position I have set label positions to Inside End.

You can further customize the column colors, label fonts, colors, etc from the available options.

3. Doughnut Progress Chart

A Doughnut Progress Chart is a great tool to visualize the completion status of a task in a circular-shaped chart and can be considered as Excel advanced charting.

First of all, have a dataset with the completion percentage of work and the remaining percentage of work.

Now select them and go to Insert.
Go to Insert Pie or Doughnut Chart from the ribbon.
Next, click on Doughnut from the available options.

The doughnut chart will be created.

To define the doughnut chart with the completion percentage of work, go to Insert and click on Text.
From the available options, pick Text Box.
Now, draw a text box in the middle of the doughnut chart and apply the following formula after selecting that text box.

=Doughnut!$C$7

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

You can make necessary modifications to the inserted text from the Font section of the Home tab and have as Excel advanced charting.

4. Creating Interactive Histogram Chart

Another very commonly used type of Excel advanced chart is the Histogram Chart. This chart shows how data is spread across different intervals. So, it is very handy to understand the underlying distribution of data.

We will use the following dataset with Student ID and English for the creation of the interactive Histogram Chart.

First of all, select the data and go to the Insert tab.
Next, click on Insert Static Chart from the ribbon and pick the Histogram option.

Double-click on the chart rectangle and a new window named Format Data Series will appear beside the worksheet.
You can increase the Gap Width according to your preference from the Series Options.

Now, click on the bins values, and a new window named Format Axis will appear beside the worksheet.
From the Axis Options, change the Bin width according to your choice.

You can insert a graph name (i.e. Histogram Chart), remove gridlines, and further editings to furnish the chart.

5. Annual Trend Chart with Monthly Detail

This is a form of a Scatter Chart containing the monthly details of a year. It can not only contain the values of existing data but also can predict them for the upcoming months. In this section, we are going to go in-depth about how.

We can use the FORECAST.LINEAR function to predict the future amount and create an annual trend chart as advanced charting. Initially, I have considered a dataset with the Month and Sales columns.

Apply the following formula to predict future sales based on the previous months’ sales.

=FORECAST.LINEAR(B14,$C$5:$C$13,$B$5:$B$13)

Before creating a scatter chart, set the sales value of month 9 into cell D9.
Go to the Insert tab and select Insert Scatter or Bubble Chart in the ribbon.
From the available options, select Scatter with Straight Lines and Makers.

Now, we have an Annual Trend Chart with Monthly Detail.

6. Creating Dynamic Waterfall Chart

Waterfall Chart, an advanced chart, is a visual representation of the net changes between the start and end points of a cumulative value. It shows each unique increase or decrease value that created the net change instead of just showing the initial and final value.

To create a Waterfall chart, I have considered a dataset with the Particulars and Sales Flow columns.

First, select the cell range B4:C12.
Then go to the Insert tab >> click on Waterfall, Funnel, Stock or Surface Chart >> select Waterfall Chart.

Now select the end bar. The Format Data Point toolbar will open.
After that, turn on Set as total.

Similarly, select the first bar and check the box named Set as total to finish the chart.

7. Clustered Charts with Variance

In advanced charts like Clustered Charts with Variance, we can find the variance between projected and actual amounts. For this, we need a proper dataset.
Here, I have considered a dataset with the amounts listed between Target Sales and Actual Sales and their different types of variances.

Now, select the defined data from the dataset and go to Insert.
Go to Insert Column or Bar Chart and pick the Stacked Column option.

Now select the Target Sales portion and turn it into Secondary Axis from Format Data Series.

Similarly, turn Achieved Sales into Secondary Axis.
Now select the Achieved Sales values in the chart and click on Insert.
Afterward, Go to Insert Column or Bar Chart and pick the Clustered Column option.

Thus, we will have a Clustered Chart with Variance.

You can further make necessary editing of the chart based on your preferences.

8. Distribution Chart with Comparability

A distribution chart can be used to make comparisons among data as an advanced chart. In case you don’t want to use a histogram chart, you can use this for the same purpose.

First of all, create a proper dataset. Here, I have a dataset with examination marks, total, and base marks.

Select the entire dataset and go to Insert.
Click on Clustered Column from Insert Column or Bar Chart.

Now select the Total column and right-click on the mouse.
From the available options, click on Change Series Data Type…

From the Change Chart Type wizard, set the Chart Type to Line.

Similarly, change the Chart Type of Baseline to Line.

Now select the Total line and click on Chart Elements.
Afterward, check the Up/Down Bars box.

And decrease the Gap Width (i.e. 40%).

Next, remove the Total and Baseline lines by clicking on the No line from the Fill option.

Now select the bars and pick the Gradient fill option from Fill.
Set the Gradient stops to the top and bottom and remove extra stops.
Set a color for the top gradients stop.

Similarly, set the White color for the bottom stop and make it 100% transparent.

Now, you can add Data Labels, remove the Total and Baseline from the grapes and make other edits to finalize the distribution chart.

9. Actual vs Target Chart

The Actual vs Target Chart is nothing but a chart to define the distinction between the predicted and actual estimation. This is helpful to identify your numeric goal requirements for a certain period.

We are going to modify the Clustered Column chart for this purpose.

To create an outline outside the actual and target amounts, apply the following formula in the Max Invisible and Max Outline columns.

=MAX(C5:D10)+500

Now select the entire dataset and go to Insert.
Go to Insert Column or Bar Chart and pick the Clustered Column option.

Now select all the Target Sales columns and go to Fill & Line.
From the Fill section, select No fill.
From the Border section, choose Solid line.
Pick a color for the outline and make the width 1.5 pt.

Now select the Actual Sales column and change the color from Fill & Line.

Similarly, select all the Max Invisible columns and go to Fill & Line.
From the Fill section, select No fill.
From the Border section, choose Solid line.
Pick the White color for the outline and make the width 2.

Afterward, select all the Max Outline columns and go to Fill & Line.
From the Fill section, select No fill.
From the Border section, select Solid line.
Pick a color similar to the actual sales color for the outline.

Now select any type of column and go to Series Options.
From there, set the Series Overlap to 100%.

Now, we have an advanced Actual vs Target chart.

Note

We can not see the target sales mark where the achieved value exceeds.

10. Milestone Chart

A Milestone Chart is an advanced chart that allows us to plot milestones on a timeline. This is also known as the project milestone chart. This provides an overview of key project events and scheduled timing for them. To create a Milestone chart, I have considered the following dataset.

Select the dataset and go to Insert.
Pick the Insert Line or Area Chart option and click on Line with Markers.

Now right-click on the chart and go to Select Data…

Delete the available series by clicking on Remove.
Following that, click on Add to create a new series.

Now define a series with the name Time, and range C5:C12 and click on OK to create a series.

Similarly, create the Activity series.

Now click on the chart and right-click on the mouse.
From the available options, pick Change Series Data Type…

Now click on the extension part of the Chart Type option of the Activity series and click on Stacked Column.
Click OK to finish here.

To edit the horizontal axis, go to the Edit option.

Define the range from the dataset.

Now select the defined data labels and go to Label options.
Check the box named Category Name and uncheck the rest.

Afterward, go to Chart Design and pick Add Chart Element from the ribbon.
Select Error Bars and click on More Error Bars Options…

Now set the error bars direction to Minus from the Vertical Error Bar group.
Set End Style with No Cap and Percentage to 100%.

Now select the columns and set No fill from Fill and No line from Border.

Finalize your chart by making necessary adjustments to the font from the Home tab.

11. Gantt Chart

Gantt chart is used basically in project management. You can not create a Gantt chart in Excel in a direct way. But don’t worry, you can make an advanced-level Gantt chart in Excel by modifying the Stacked Bar chart.

Create a proper dataset related to project management or a similar type.

Firstly, select the data range from cell C4 to cell C12 which is the Start Date column, and go to the Insert
From the ribbon, choose Insert Column or Bar Chart command from the Charts group.
Next, choose the Stacked Bar command under the 2-D Bar section.

Afterward, right-click on the chart and click on the Select Data command.

You will see a dialogue box that’s name is Select Data Source.
Then click on the Add option from that box.

In the Series name type box, insert cell E4 which is the column name of the new data series.
Select cell range E5:E12 from the data table in the Series values dropdown as the values.
Finally, press OK after fulfilling all the criteria.

Now click on the Edit command which is under the Horizontal (Category) Axis Labels heading.

Select the cell range B5:B12 as the Axis label range in the Axis Labels box and click on OK.

Now, click on the axis and a new window named Format Axis will appear right beside the chart.
Under the Axis Option label, mark the Categories in reverse order command.

Select all the blue bars from the chart by double-clicking on them.
Then a window named Format Data Series will appear on the right side of the chart.
Choose the No fill command under the Fill & Line tab.

At this moment, find the earliest task date and the latest date when a task has ended in the Minimum Value and Maximum Value cells.
Select the cells and turn them into the General format.

To adjust the label values in the chart, double-click on the horizontal axis label.
After that, the Format Axis window pane will appear.
In the Axis Options tab, input the Minimum and Maximum values manually that we have already calculated.

That is the formation of a Gantt Chart. You can customize your Gantt Chart and make it more attractive and understandable according to your choice.

12. Bell Curve

The Bell Curve is a graph that represents the normal distribution of a variable. So, this is also known as the Normal Distribution Curve. The peak point of this Curve signifies the mean of the distribution. The Curve is lower on both sides. This also denotes the probability, which will be much lower for the extreme values (i.e. highest or lowest).

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

To begin with, the first value will be from cell C16.

Then, select the cell range D6:D12 and type this formula.

=D5+$C$19

We are using the interval value to get the other values using this formula.

Then select the cell range E5:E12 and apply this formula.

=NORM.DIST(D5,$C$14,$C$15,FALSE)

This formula returns the normal distribution for the given mean and standard deviation. We have set these values in the code. Moreover, we’ve set Cumulative to FALSE, this will ensure we get the “probability density function”.

To begin with, select the cell range D5:E12.
Next, from the Insert tab >>> Insert Scatter (X,Y) or Bubble Chart >>> select Scatter with Smooth Lines.

This will be our basic Bell Curve.

First, double-click on the horizontal axis and it will bring up the Format Axis dialog box.
Then set the Bounds –

Minimum: 30.
Maximum: 90.

Now furnish your chart according to your choice.

13. Sales Funnel Chart

From the perspective of a lead, the sales funnel considers the entire customer journey. Analysts use the Sales Funnel Chart as a part of the advanced chart. The most common usage involves tracking the sales at each stage of the cycle, helping identify the bottlenecks in stages, optimization of different sale stages, etc.

To create a Sales Funnel Chart with the Sales Stage and Deal Value, we need another to create an additional column named Helper Column with the following formula so that there is enough space on each side of our pipeline funnel chart.

=(LARGE($D$4:$D$9,1)-D6)/2

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

Now double-click on the vertical axis and select Categories in reverse order from Axis Options.

Afterward, select the helper data in the chart.
From the Fill & Line tab, fill as Solid fill and make transparency to 100% to disappear the helper columns data.

From the Axis Options tab, make the Gap Width to 0%.

You can customize the color, remove the legends etc to furnish the graph according to your choices.

14. Pareto Chart

A Pareto Chart is a graphical tool that combines a vertical bar chart with a horizontal line chart. The purpose of this chart is to display the magnitude of different issues in a given dataset, arranged in descending order of importance.

I have considered the following dataset for creating a Pareto Chart.

First of all, select the entire dataset and go to the Insert tab.
Then click on Insert Static Chart and select Pareto from the Histogram chart group.

We will have our desired Pareto Chart.

15. Bullet Chart

A Bullet Chart is an advanced chart that is used to display a single data point on a linear scale along with qualitative ranges for context. Overall, it shows the progress toward a specific goal or target. So it is widely used in dashboards and performance trackers with key performance indicators.

Create a dataset with quantitive bands along with actual and target values.

Select the entire dataset and go to Insert.
From the Insert Column or Bar Chart option, click on Stacked Column.

Afterward, select the chart and click on Switch Row/Column from the Chart Design tab.

Now, right-click on the mouse keeping the cursor on the chart, and pick Change Series Chart Type…

Now change the Chart Type of the Target Value to Stacked Line with Markers.

Check the box titled Secondary Axis and click OK.

Next, select the secondary axis and delete it.

Now select the marker and change its format.
For this, go to Fill & Line >>> Marker tab.
From the Marker Options group, select Built-in, set Type to Dash, and Size to 30 or something according to your choice.
From Fill, set to Solid fill, and pick a color.
From Border, set No line.

Again, go to Change Series Chart Type… and set the Actual Value line to Secondary Axis.

Next, select the Actual Value column and set the Gap Width to 400%.

Now, select the bands one by one and change their color from the Fill option.

Thus, we can have a proper bullet chart.

16. Step Chart

A Step Chart is an advanced type of chart that connects data points with horizontal and vertical lines to show the progression of data over discrete intervals.

First of all, create a specific type of dataset. Here, I have a dataset with Date and Petrol Price.

Now, copy the entire dataset to a new location (i.e. E4).
Then select cells E5 and F15 and right-click on the mouse.
From the available options, click on Delete.

Now pick the Shift cells up option and click on OK.

Afterward, copy the entire data from B5:C15 and paste it into cell E15.

Now select the range E4:F25 and go to Insert.
From the Insert Line or Area Chart option, select Line.

After making the necessary edits, you can finalize your Step Chart.

17. Waffle Chart

A Waffle Chart, resembling a waffle, is a special type of chart. Each small square of this waffle represents a percentage or a specific portion of a whole. To create a waffle chart, you need a special type of dataset.
Here are the steps to create a waffle chart in Excel.

First, create a dataset like this.

Now select all the cells (range B4:K13) and select Conditional Formatting from the Home tab.
Then select New Rule.

In the New Formatting Rule box, select Format only cells that contain under Select a Rule Type.
Select less than or equal to as the Cell Value option and select cell D15 for the field beside it.
Choose a fill option from the Format option there.

After clicking on OK, we can get something like this.

Do the same again for the same range, but with values greater than the cell value of D15. We have selected a grey fill for that.
While the range is selected, press Ctrl+1 to open the formatting options.
In the Number tab, select the Custom format and write ;;; as the Type.

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

After pressing OK, you will get something like this.

Now resize the cells to make it a perfect waffle chart.

18. Thermometer Chart

Another advanced chart named the Thermometer Chart is a visual representation that is used to track progress towards a goal.

First of all, we need to have a proper dataset. For this, I have generated a dataset with total profit and the targetted profit.

Now select range B17:C18 with targetted and achieved profit percentage and go to Insert.
Then from the drop-down menu, click on the Clustered Column from the 2D Chart option.

In the new Chart, select any of the data columns, and then from the Chart Design tab, click on the Switch Row/Column to shuffle the axis row and column.

Now select the chart column and click on the Secondary Axis on the Series Options from the right-side panel.

After that, go to Fill and Line from the right-side panel menu.
Then in the Fill option, select No fill.
And in the Border option, click on the Solid line.
Make sure the color of the border matches the column color.
And set the Width to 1.25 pt from 0.75 pt.

In the right-side panel, input the Minimum bound as 0 and press enter.
And input 0 in the Maximum bound option and press Enter.

After we have the Thermometer-shaped Chart, we can now add the bulb shape below it.
To add this shape, go to the Insert tab.
And from there, click on the Shapes, and from there, click on the oval shape icon.

And place the oval at the bottom of the Chart.
The final form of the Thermometer will look like the below image.

19. Matrix Chart

Here, we will be creating the other type of Matrix chart which is the 4-Quadrant Matrix chart. One thing is to remember that here you can only create a chart for 2 sets of values. So, we will use the selling prices and the cost prices of the products to make a Quadrant chart.

Select the range of values (C4:D8) and then go to the Insert Tab >> Charts Group >> Insert Scatter (X, Y) or Bubble Chart Dropdown >> Scatter option.

Double-click on the axis to set the upper bound and lower bound limits of the X-axis and Y-axis and we will have the Format Axis pane on the right side.

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

For adding the 2 lines to have 4 quadrants we have to add an additional data range here.
For the Horizontal part 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)

For the Vertical part 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)

Select the graph and right-click on the mouse.
Then choose the Select Data option.

Afterward, the Select Data Source wizard will open up.
Now, click on Add.

For Series X values, select the X coordinates of the horizontal part of the Quadrant sheet, and then for Series Y values, select the Y coordinates of the horizontal part.
Next, click OK.

Similarly, add another series for the vertical line.

Thus, we will have four points.

Now, select the horizontal point and go to the Fill & Line tab.
Expand the Line Option and click on the Solid line option.
You can choose your desired color too.

To hide the points, go to the Fill & Line tab and expand the Marker Options option.
Now, click on the None option.

Similarly, create a line with the vertical points and remove the gridlines to have a complete matrix chart.

20. Meter Chart In Excel

A Meter Chart or SPEEDOMETER Chart is very useful for vehicles to understand their speed or velocity. Using Microsoft Excel, we can easily create a meter chart as Excel advanced charting.

Create a dataset that contains information about several speeds of a vehicle. We calculate the end value of the pointer using a simple mathematical formula. The formula is,

=200-F5-F6

Next, select cells range from B5 to C9. Hence, go to,

Insert → Charts → Pie Chart → Doughnut

After that, double-click on a small portion of the chart, and a Format Data Series drop-down list will pop up.
Type 270 degrees in the Angle of first slice typing box under the drop-down list named Series Options.

Further, select the bigger portion of the Doughnut chart, and check the No fill option under the Fill drop-down list.

Now, we will create a pointer to the meter chart. To do that, place your cursor on the chart and right-click on the mouse. As a result, a window will appear in front of you. From that window, select the Select Data option.

Hence, a wizard named Select Data Source will appear in front of you. From there, select the Add option.

From the Edit Series dialog box, type =Meter!$E$4 in the Series name typing box.
Again, type =Meter!$F$5:$F$7 in the Series values typing box.
Lastly, click on OK.

Hence, we will have a new chart. Now, we will change the type of the chart. To do that, place the cursor on the chart and right-click on the mouse.
From the available options, select the Change Series Chart Type option.

From the Change Chart Type wizard, select Pie chart in the pointer and click on OK.

Select the biggest portion of the Pie chart, and check the No fill option from Fill.

After that, type 270 degrees in the Angle of first slice typing box under Series Options.

Now, remove the other bigger portion of the chart with No fill and we will have a perfect Meter Chart.

21. Burndown Chart In Excel

A Burndown Chart plots the amount of work remaining versus the amount of time. Time usually runs along the horizontal axis, with the amount of unfinished work (or backlog) on the vertical axis.

Calculate the total number of hours in 5 weeks and subtract it from the total estimated hours to find the remaining hours. Just apply the following formula in cell I5 and AutoFill the rest cells in column I:

=C5-(SUM(D5:H5))

To calculate both Total Burndown and Efforts Left in the Start section for the second table, write the formula in C14 & C15:

=SUM(C5:C8)

Add up the Estimated Hours and divide them by the total number of weeks to get the resulting hours. Therefore, type the formula in D11 and AutoFill till H11:

=SUM(C5:C8)/5

Further, to count the completed hours each week, type:

=SUM(D5:D8)

Afterward, AutoFill the rest of the rows up to H12.

Similarly, to display efforts left per week, insert:

=C13-C12

Lastly, to calculate the total burndown for each week separately, write:

=C14-D11

Thus, you get the desired number in the display. AutoFill the rest of the range.

To create a line chart, go to Table-2 first and select B11:H14.
Afterward, take your cursor to the Insert tab and navigate to the Charts group.
Click Insert Line and Area Chart and choose the Line Chart option.

We can change the chart design from the template chart under Chart Design.

Now, right-click on the mouse keeping the cursor onto the chart, and select the Select Data… option.

Click on Edit from the Select Data Source wizard.

Eventually, an Axis Labels dropdown box appears.
Select range $C$10:$H$10 and click on OK to complete the step.

To push the Scheduled Hours and Completed Hours columns to Clustered Column, select a chart line and right-click on the mouse.
After that, select the Change Series Chart Type… option.

Now, select Clustered Column from Chart Type for Scheduled Hours and Completed Hours and click OK.

Thus, we will have a proper Burndown Chart.

22. Excel Chart With Data Table

Normally, the dataset that is used to create a chart is not added to the chart. But if anyone wishes to add a data table with the chart, it is possible to do so.

We already have a chart with Target Sales vs Achieved Sales as the first advanced chart.

To add a data table with that chart, select the chart first.
Then click on Chart Elements and check the box labeled Data Table.

Thus, we will have the data table with the chart quite easily.

23. Distribution Chart

A Distribution Chart in Excel allows you to visually represent and analyze the frequency distribution of data, providing valuable insights into its characteristics and patterns.

Here, the dataset shows the names of club Members and their Ages respectively.

At the very beginning, add a column for the bins, in this case, Age Bracket 1.
Now, in this dataset, the Age value starts at 25, so we set the starting value of the bin to 20. In addition, we chose a Bin Size of 10.
Then, enter the expression given below in cell E8 and AutoFill till E13 to organize Age Bracket 1.

=E7+$G$4

Next, we calculate the first value in Age Bracket 2 with the following formula

="<="&E7

Then, apply the following formula in cell F7 and AutoFill till F13 to organize Age Bracket 2.

=E7+1&"-"&E8

Apply the following formula in cell F14 as the last range in Age Bracket 2.

=">="&E13

Now add the frequency column with the header Number of Member and enter this formula.

=FREQUENCY(C5:C14,E7:E13)

Now select the Age Bracket 2 and the Number of Member columns.
Next, go to Insert >>> Insert Column or Bar Chart >>> Clustered Column.

Then double-click on any of the bars to open the Format Data Series window.
Set the Gap Width to 0%.

Thus, we can have a proper distribution chart.

24. Comparison Chart In Excel

A Comparison Chart is a kind of chart where we can compare two or more different kinds of data and understand various correlations among them.

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

Now, select the whole dataset.
Go to the Insert tab >> Insert Column or Bar Chart drop-down >> Clustered 2-D Column option.

After that, removing the gridlines and adding data labels, we can have a proper distribution chart.

How to Save Chart Template in Excel

We can save the chart template of an advanced chart quite easily to lessen the workload.

Select a chart and right-click on the mouse.
From the available options, click on Save as Template…

Now, save it on your computer and use it when necessary.

Things to Remember

It is worth mentioning that you might get confused while reducing the transparency of the Helper Data during the creation of the Sales Funnel Chart as you will find two Color and Transparency So before doing any kind of reduction make sure you choose the right one, at the top of the box under the Fill option.
The Pareto analysis only looks at past data and doesn’t predict the future. So, it’s important to keep updating the data to improve the process continually.
Printing your chart will help you identify any errors or inconsistencies more clearly. Thus, print a chart or see the print preview before presenting your chart.

Frequently Asked Questions

Q1. Why text placement is 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 on the chart and select the Change Chart Type options. Then, you will find an option named Templates from where 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.

Conclusion

In this article, I have tried to explain different kinds of advanced charting. Based on the situation, you can use any one of the advanced charts. I hope this article will be helpful for you. For any further questions, please comment below. You can also visit our site for more Excel-related articles.

<< Go Back to Excel Charts | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!