Excel Advanced Charting (24 Unique Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

Excel Advanced Charting


Download Practice Workbook

You can download the practice workbook from here.


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.

Dataset for Creating Combo Charts

  • 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.

Creating Combo Charts

Now, we have Combo Charts on the worksheet.

Output of Creating Combo Charts


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.

Dataset of 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.

Creating a 2D Column

  • 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.

Modifying Error Bars

  • 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.

Specifying Error Amount Values

  • 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.

Defining Positive and Negative Error Values

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%.

Setting the Gap to Zero

  • 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.

Using No Fill to Slicers

  • 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…

Using Data Label

  • 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.

Adding Slicers Labels

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

Changing Label Positions

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

Dynamic Column Chart with Slicers


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.

Dataset for Doughnut Progress Chart as Advanced Chart

  • 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.

Generating Doughnut Progress Chart

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

Adding Completed Project Amount in Doughnut Progress Chart

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.

Doughnut Progress Chart as Advanced Chart


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.

Dataset for Creating 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.

Creating Interactive Histogram Chart

  • 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.

 Changing Gap between Columns

  • 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.

Changing Axis Gap

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

Histogram Chart as Advanced 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.

Dataset for Annual Trend Chart with Monthly Detail

  • 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)

Predincting Future Sales

  • 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.

Creating a Scatter Chart

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

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.

Dataset for Creating Waterfall Chart

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

Creating Waterfall Chart

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

Clicking Set as Total Option

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

Creating Dynamic Waterfall 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.

Dataset of Clustered Charts with Variance

  • 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.

Creating a Stacked Column

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

Turning into Secondary Axis

  • 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.

Turning Secondary Axis into Clustered Column

  • Thus, we will have a Clustered Chart with Variance.

Rough Clustered Charts with Variance

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

Final Clustered Charts with Variance


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.

Dataset for Distribution Chart with Comparability

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

Creating Clustered Column Chart

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

Changing Series Chart Type

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

Changing Series Chart Type

  • Similarly, change the Chart Type of Baseline to Line.

Output After Changing Series Chart Type

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

Changing Series Outline

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

Changing Gap Width

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

Making Line Invisible

  • 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.

Filling Column with Color

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

Filling Column with Color and Transparent

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

Excel Advanced Charting


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

Dataset of Creating Actual vs Target Chart

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

 Creating Clustered Chart

  • 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.

Changing the Column Outline

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

Changing the Column Outline

  • 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.

Changing the Column Outline

  • 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.

Changing the Column Outline

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

Making Overlap of Columns

  • Now, we have an advanced Actual vs Target chart.

Creating Actual vs Target Chart as Excel advanced charting

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.

Dataset for Creating Milestone Chart

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

Creating a Line Chart with Markers

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

Go to Select Data

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

Removing the existing Data

  • 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.

Adding Another New Series

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

Changing Series Chart 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.

Changing Chart Type

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

Changing the Horizontal Series

  • Define the range from the dataset.

Changing the Horizontal Series

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

Defining Labels

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

Setting Error Bars

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

Setting Error Bars

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

Removing Column

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

Creating Milestone Chart as Excel advanced charting


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.

Dataset for Gantt Chart

  • 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.

 Creating Stacked Bar

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

Selecting Select Data

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

Selecting Add Option

  • 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.

Defining Series Range

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

Changing Horizontal Axis

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

Changing Horizontal Axis

  • 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.

Changing Category in Reverse

  • 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.

Disappearing Unnecessary Bar

  • 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.

Changing Format of Maximumm and Minimum

  • 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.

Changing Axis Format with Maximumm and Minimum

  • 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.

Final Gantt Chart as Advanced Charting


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

Calculating Necessary Parameters fro Bell Curve

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

First Value from tha Calculated Parameters

  • 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.

Calculating Other Value from tha Calculated Parameters

  • 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”.

Calculating Normal Values

  • 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.

Generating Graph from the data

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.

Changing Axis Dimension

  • Now furnish your chart according to your choice.

Final Bell Curve as Excel advanced charting


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

Calculating Helping Values

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

Creating Stacked Bar Chart

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

Categorize the Axis in Reverse

  • 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.

Make the helping Column Transparency to 100%

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

Diminishing the Gap

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

Final Sales Funnel Chart as Excel advanced charting


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.

Dataset for Creating 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.

Creating Pareto Chart

  • We will have our desired Pareto Chart.

Final Pareto Chart as Excel advanced charting


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.

Dataset for Bullet Chart

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

Creating a Stacked Chart

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

Switching Row or Column

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

Changing Column from Select Data

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

Changing Chart Type

  • Check the box titled Secondary Axis and click OK.

Turning Into Secondary Axis

  • Next, select the secondary axis and delete it.

Delete Secondary Axis

  • 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.

Defining Markers Format

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

Checked Actual Value as Secondary Axis

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

Changing the Gap Width

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

Changing the Color of those Ranges

  • Thus, we can have a proper bullet chart.

Final Bullet Chart as Excel advanced charting


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.

Basic Dataset for Step Chart

  • 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.

Select and Delete Cell after Copying

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

 Setting Shift Cells Up to Delete Cell

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

Copying the Entire Dataset

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

Inserting Chart

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

Final Step Chart as Excel advanced charting


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.

Dataset for Waffle Chart

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

Selecting New Rule from Conditional Formatting

  • 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.

Defining the Rule for Conditional Formatting

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

Output after Applying the Rule

  • 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.

Formatting the Numbers from Dataset

  • 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.

Formatting Cells with Specifications

  • After pressing OK, you will get something like this.

having Rough Waffle Chart

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

Final Waffle Chart as Excel advanced charting


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.

Creating Dataset for Thermometer Chart

  • 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.

Creating Clustered Column from Data

  • 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.

Switching Between Row and Column

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

Making an Axis into Secondary Axis

  • 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.

Making Change of Chart

  • 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.

Changing Axis Dimension

  • 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.

Inserting Shapes

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

Final Thermometer Chart as Excel advanced charting


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.

Dataset for Matrix 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.

.Creating a Scatter Chart

  • 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.

Changing Y-Axis Values

  • 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)

Additional Data to Create 4 Quadrants

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

Go to Select Data

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

Click on Add to create Series

  • 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.

Adding a Series

  • Similarly, add another series for the vertical line.

Adding another Series

Thus, we will have four points.

Indicating Four Points for four quadrants

 

  • 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.

Turning Points into Line

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

Removing the Markers

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

Final Matrix Chart as Excel advanced charting


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

Creating Dataset for Meter Chart

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

Insert → Charts → Pie Chart → Doughnut

Creating a Doughnut Chart

  • 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.

Making First Slice Angle to 270 Degree

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

Diminishing the bigger Value

  • 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.

Entering Select Data Option

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

Clicking on 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.

 Defining Range for Axis

  • 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.

Having the Chart Type Option

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

Changing Chart Type

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

Setting No Fill for Bigger Portion of Pie Chart

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

Setting Angle to 270 degree

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

Final Meter Chart as Excel advanced charting


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))

Calculating Hours Left from Dataset

  • 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)

Calculating Total Burndown

  • 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

Calculating Total scheduled Hours

  • Further, to count the completed hours each week, type:
=SUM(D5:D8)
  • Afterward, AutoFill the rest of the rows up to H12.

Calculating Total Completed Hours

  • Similarly, to display efforts left per week, insert:
=C13-C12

Calculating Total Efforts Left

  • 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.

Calculating Total Burndown

  • 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.

Creating Line Chart

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

Select a Chart Style

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

Picking Select Data Option to create as Excel advanced charting

  • Click on Edit from the Select Data Source wizard.

 Choosing Edit Option

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

Selecting Axis Label Range

 

 

  • 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.

Select Change Series Chart Type

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

Selecting Chart Type to Clustered Column

  • Thus, we will have a proper Burndown Chart.

Final Burndown Chart as Excel advanced charting


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.

Excel Chart With Data Table

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

Cheking Data Table from Chart Elements

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

 Final Excel Chart With Data Table as Excel advanced charting


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.

Dataset for Distribution Chart

  • 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

Creating Age Bracket 1

  • Next, we calculate the first value in Age Bracket 2 with the following formula
="<="&E7

Creating 1st Element of Range

  • Then, apply the following formula in cell F7 and AutoFill till F13 to organize Age Bracket 2.
=E7+1&"-"&E8

Creating Middle Elements of Range

  • Apply the following formula in cell F14 as the last range in Age Bracket 2.
=">="&E13

Creating Last Element of Range

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

Managing Frequency

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

Generating Clustered Column

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

Setting Gap Width to Zero

  • Thus, we can have a proper distribution chart.

 Final Distribution Chart as Excel advanced charting


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.

Dataset for Comparison Chart

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

Creating Comparison Chart

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

Comparison Chart as Excel advanced charting


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…

Save as Template

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

Setting Location for Save


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

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

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo