Dashboards in Excel can update when a user makes a selection on a slicer or refreshes data. You can even use macros to automatically update a chart or dashboard for you. In this post, I’ll share with you a template that I’ve created that will allow you to effectively play your dashboard, updating it from one period to the next, and showing the change in the chart over time. Here it is in action:
The template has three sections: one for pivot tables, one for the data, and one for the dashboard. You can set the file up however you want, the main area that needs to remain largely the same is the dashboard sheet. Every chart on this sheet only will automatically get updated. And for it to work properly, all the charts need to be linked to the one timeline chart in here (i.e. there cannot be more than one). For information on how to set up your timeline (or any other slicer for that matter) so that you can link it to multiple charts, you’ll need to learn about how to adjust Report Connections in this post.
Once you’ve got the charts you want to be connected to the timeline, then it’s a matter of just updating the settings section on the Dashboard tab. This is off to the left, with the values that you need to enter/update highlighted in yellow.
These simply specify what date you want to start from, where you want to end at, and by which interval you want to jump (e.g. x days/months/years). Depending on the frequency you select, your dashboard can either play very quickly, or very slowly.
The last step is to just click the Animate Dashboard button at the end of the home tab:
Upon clicking this, the timeline will jump by the intervals you specified. No other changes will be made to any filters or slicers you have selected. The only changes will take place to the timeline, at which point, you should see something similar to the video posted at the top of this post.
If you liked this free template that helps you animate your dashboards, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
In Excel, you can create quick and easy visuals with only a formula. You don’t need to insert charts or worry about if they are set up correctly. In this post, I’ll show you how you can quickly create both bar and column charts.
For this example, I’m going to use data from Apple’s most recent earnings report, to see the split between sales of its different categories. Here’s what the data looks like from its most recent filing:
To create a simple bar chart, I’m going to use the REPT function, which allows me to repeat text. The character I’m going to repeat is the “|” line, which on most keyboards is the button above the enter key. Holding shift and that key should give you that line. The number of times I want to repeat the character will be the value in column B. But because it’s too large, I’m going to divide it by a factor of 100. Here’s how that formula will look:
=REPT("|",B4/1000)
If I copy this down, my bar chart remains a work in progress:
One way to make this look like more of a bar chart is by changing the font. In column C, I’m going to change it to Britannic Bold. And now, this looks like a proper bar chart:
If I sort the values from largest to smallest, then it’s easier to see the progression:
This is good, but this is also still a bar chart. To convert this into a column chart, I need to make a couple of changes. The first thing is I need to arrange the data differently so that the fields and the values are going horizontally rather than vertically. To do this, you can just transpose the data. You can do this using the TRANSPOSE function. Now, my data is better suited for a column chart:
Now, I’ll add back the REPT function and use the same font. Except for this time, I will modify the cells so that the alignment is vertical:
Now, after compressing the columns, I have a column chart set up:
Here’s a quick video showing the steps:
If you liked this post on How to Create Column Charts in Excel With Just a Formula, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
The MACD line and chart is a popular tool for technical analysts who buy and sell stocks. And in this post, I’ll show you how you can create it from start to finish. In my example, I’ve downloaded Apple’s stock price history for the past year from Yahoo Finance, and I’ll use that to calculate its MACD line. Here’s a sample of what I’m starting with:
Calculating the exponential moving averages
To calculate the MACD line, I’ll need to create multiple exponential moving averages (EMAs). One for 9 days, 12 days, and for 26 days. The logic will be the same so I can start with creating a formula for the 9-day EMA and then apply that to the others.
I’m going to create a couple of variables. The first being the n for the number of days. And the second one is for the weighting that you’ll apply to more recent values, and thus, turning it from a simple moving average into an exponential one. The weighting is calculated as follows:
=2/(1+n)
I’ll start my formulas to calculate the 9-day EMA by first checking to see if I have at least 10 data points. If I don’t, then I’m only calculating a simple moving average. Here’s how the start of that formula looks, assuming my closing stock prices start from cell B5 and my variable n is in cell C1:
IF(COUNTA($B$5:$B5)<=C$1,AVERAGE($B$5:$B5)
A key part of the formula is freezing cells properly. Cell $B$5 won’t move, but $B5 will as I drag it down. And this allows me to calculate the cumulative number of data points, and the corresponding average. The next part of the formula is what happens if I have more than nine data points. In that case, I will take the weighting (this is cell C2) on my sheet, and multiply that by the difference between the most recent stock price and the previous EMA. This will then get added to the previous day’s EMA:
C$2*($B5-$C4)+$C4
Column C is the one that contains the EMAs. My complete formula is as follows:
I can now copy this logic across multiple columns to calculate the 12 and 26 day EMAs as well:
Now, I’ll set up the calculations for the final three columns:
MACD: This involves taking the 12-day EMA and subtracting the 26-day EMA from that.
Signal Line: This is a 9-day EMA of the MACD line.
Histogram: This is calculated as the difference between the MACD line and the Signal line.
With all those columns set up, here is my completed table:
Creating the charts
With all the columns set up, the next part is to put the key data into a chart to illustrate the MACD line, Signal line, and Histogram. To make the chart look like a typical MACD chart, I’ll need to set the MACD line and Signal line to be line charts, and for the Histogram to be a column chart.
Initially, when the chart is created, there’s too much data in there since the data set is bigger than it needs to be:
To fix this, I right-click on the chart and click Select Data. Then, I remove all the series except for the last three: MACD line, Signal line, and Histogram. My updated chart looks as follows:
There are still a few more changes that I am going to make here. The first is to fix the axis, as there are gaps between the column charts. That’s because Excel is recognizing the axis as a date axis. And while that’s correct, that means there will be gaps since stocks don’t trade every day of the week, and thus, those gaps, are weekends. To fix this, right-click on the axis and click Format Axis. And then, change the Axis Type so that it is a Text axis:
And then, under the Labels section, I set the position so that it is Low and at the bottom of the chart. My updated chart looks a bit better:
The last change you may want to consider is adjusting the column chart gap, to shrink it so the chart looks more like a histogram. If you right-click on them and click Format Data Series, there’s an option to change the Gap Width. I find that setting this to 50% normally results in a good gap size::
And now we’ve got a chart that resembles what you might find on major finance sites when looking at MACD. If you want to follow along with the sheet that I’ve created, you can download my MACD chart template here.
If you liked this post on How to Create a MACD Chart, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
A pie chart can be a great way to show percentages, such as how much significant an individual’s product sales are to the total number. However, if you’re dealing with more than 10 items, a pie chart can start to get a bit messy. But there is a way to address that, and that’s with a second pie chart. In this post, I’ll show you how to make a pie of a pie chat so that it’s easier to display more items.
At what point should you consider another chart?
There’s no magic number as to how many items is the limit for a pie chart. It will ultimately depending on how big your slices are, and the size of your text. For my example, I’m going to use data from healthcare company Pfizer‘s most recent quarterly results to show sales of its top-selling products. For the third quarter of 2021, the revenue of its top products were as follows:
If I were to create a pie chart using this data, this is how it would look like:
Since there are many small items accounting for 3% or less of revenue, a case could be made for creating a second pie chart here. You don’t have to do so, but it could help make the visual more readable for users.
Creating a second pie chart
To add a second pie chart, what I need to do is select the chart. Then, go into the Chart Design tab and select the Change Chart Type option. From there, I will select the Pie of pie chart:
Upon making the selection, I now get a second chart that offloads some of the items onto there:
Customizing the size of the pie chart
By default the smallest slices will be on to the second pie chart. However, you can specify how you want to split the charts. If you right-click and select Format Data Series, you can specify the number of items that show in the second chart:
In the above example, there are 4 values in the second chart. But you can change this higher or lower and ultimately that will be discretionary; it will depend on how you want your data to look. If you have many small items in your summary that you want to show in a smaller pie chart, then you may want to increase the number of values in the second plot. If that’s not the case, you could opt for a smaller number.
This may seem a bit arbitrary and there are other rules you can apply instead. For example, you can select to Split Series By value:
In this case, any value that’s less than 1,000 will be moved off to the second pie chart. Here’s what that looks like:
Another option is to split the chart based on the percentage of each slice:
In the above example, I’ve selected any items that account for les than 3% of the total, they will be pushed onto the second pie chart:
If you want to manually move items from one chart to the other, you can also change the Split Series By option to ‘Custom’ and then click on a slice you want to move. You’ll see an option there to set whether it belongs to the Second Plot or to the First Plot.
Making these changes will move the slice to either the first pie chart or the second one.
If you liked this post on How to Make a Pie of a Pie Chart, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
In Excel, there are multiple different stock charts you can create. All you need is some combination of the date, opening price, high price, low price, closing price, and volume to generate what you need. In this post, I’ll show you how you can utilize Power Query to pull that data in and transform it, without having to apply any manual changes to it every time. For an overview, you can check out this post on How to Get Stock Quotes From Yahoo Finance Using Power Query. I’m not going to repeat those steps and will assume that you’re familiar with that process.
In this example, I’ve downloaded the stock prices for Apple (NASDAQ:AAPL) for the month of October 2021:
This already has all the data that is needed to create the four types of stock charts in Excel:
High-Low-Close
Open-High-Low-Close
Volume-High-Low-Close
Volume-Open-High-Low-Close
The key to making these different charts is just ensuring that you’ve got the correct fields in your download, and in the right order. For the High-Low-Close chart, you only need three fields to generate the following chart:
If you’re creating the open-high-low-close chart, all you need is to add the open field to the data set:
And for the volume-high-low-close chart, it’s just the volume instead of the open that goes at the start, and then you get something that looks like this:
The last chart includes both the volume and the open before the high, low, and close values:
These charts are fairly straightforward to generate once your data is in the right order. But rather than moving around different fields, you can make all the changes within Power Query so that right when your data loads, it’s in the correct format.
Using Power Query to adjust your download
To modify an existing query, go to the Data tab and select Queries & Connections. Off to the right, you should see your query, where you can right-click and Edit it:
The first thing I’ll edit is the date range. In my earlier post, I just downloaded the full year of data. But if I want to filter only for October, then I can click on the drop-down for the Date field and select Date Filters to filter Between a range of dates.
Using the calendar icon, I can specify the range of dates I want to include in my download:
Next, if I want to just include the data for the most basic chart, the high-low-close chart, I’ll right-click on the Open, Adj Close, and Volume columns to remove them. Then, I’m left with the following:
Now, if I were to load the data in Excel, I would already have all the columns I need to create the chart:
Note: if you want to get rid of the gap in dates on the chart, click on Format Axis and for the Axis Type, select Text axis:
This prevents Excel from trying to fill in any missing dates from your data set Another thing you may want to do is format the date field so that it is a custom format showing MMM DD so that it saves space:
Now, let’s go back into Power Query and this time create the more complex download, for the volume-open-high-low-close chart. I’ll start by removing the last step I applied which removed more columns than I need for this current download. To remove any steps in Power Query, click on the ‘X’ next to it:
In this example, it’s just the Removed Columns step I will eliminate. The only column I need to remove from the download this time around is the Adj Close. So that’s what I’ll do, right-click and remove that column. However, my table still is not in the correct order:
The Volume column needs to go before the Open column. This is as easy as just dragging the column and putting it in front:
Now that it’s in the right order, I can load and close this into Excel. And now, the volume-open-high-low-close chart can easily generate:
Suppose I want to adjust my chart to include data from September as well. Again, I can go back to edit my query. This time, I’ll select the gear icon next to the Filtered Rows step:
There, I can just modify my date range using the calendar:
Now, re-loading the data into Excel automatically updates my chart with a simple refresh:
The beauty of this is this query will update with new data and your chart will also update, taking out the manual steps of having to make any changes yourself. And if you incorporate named ranges like in my post covering pulling stock prices, then the data will easily refresh based on the variables that you’ve entered.
If you liked this post on How to Create Stock Charts in Excel Using Power Query, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
In a previous post, I showed how to make a forecast chart in Excel with a dotted line. This time around, I’m going to go one step further and show you how to create a chart that shows a range of possible values. This is useful in the event that you want to show some flexibility in your forecast and where providing a range might be a more realistic option.
For this example, I’m going to project a company’s future dividend payment. Below, I have a a record of the past dividend payments along with the annual rate of increase:
In order to create a range, I’m going to set both a high rate of growth and a low one. Since the company has made 10% increases in the past, I’m going to use that as the high. And for the low rate of growth, that will be 5%. Using those different rates, I can set up additional columns for what the dividend would be if the low rate were used and if the high one were applied. I will also create a column to calculate the difference between the high and low amounts, as well as one for a base amount — which will just be equal to the low amount. This will be used for stacking the difference on top of it to create the desired area chart:
Creating the chart
Now that the data is set up, I’m going to start creating the chart. Using a combination approach, I’ll set a line chart for the actual, low, and high columns. And for the base and difference amounts, I will set those to be stacked area charts. The growth rate I’ll leave as is because I will remove that once the chart is created:
Next, I’ll right-click on the chart and click on Select Data. From the next screen, I will untick the box for the Growth Rate:
Then, I will right-click on the x-axis, select Format Axis and select the option to put Categories in reverse order. Now my chart looks as follows:
Now, I’ll remove the legend and format the base color, which is currently grey, to a blank fill color:
I’ll change the line color for the high amount to green, the low amount to red, and apply dashed lines to both. For the actuals, I’ll set that to a black line. And for the area chart that is in green right now, I will apply a Pattern Fill and use a checkered pattern:
With all those changes, my updated forecast chart now looks like this:
If you liked this post on How to Make a Forecast Chart in Excel With a Dotted Line, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
Charts are an effective tool in forecasting. In this post, I’ll show you can show you can make an actual and forecast chart in Excel look like one continuous line chart, with the forecasted numbers being shown on a dotted line.
For this example, I’m going to use Amazon’s recent quarterly sales as my starting point:
I’m going to create another column for forecasted amounts for future quarters. I’ll make a simple forecast and assume that sales will increase by 10% every quarter:
For the last quarter (2021-09), I’m including the same total in the Forecast column. This is to ensure that the new line chart picks up where the last one ends and that there isn’t a gap. Then, I’ll create a line chart for these data points, which, by default, looks like this:
I’m going to flip this chart in reverse order so that the forecasted values are on the right. To do this, right-click on the x-axis and select FormatAxis. Then, check off the box that says Categories in reverse order:
Now, at least my chart is going in the right direction (an alternative could be to structure your data in the opposite direction):
Because of the change in colors, this makes it easy to differentiate my actuals from my forecast. But I want it to be all the same color and only be differentiated by dotted lines. To do this, I will right-click on the forecasted line and select Format Data Series:
There will be an option to change the Dash type. The default is solid, and I’m going to change that to the second option from the top — Square Dot. After changing that and making the colors the same, and applying some formatting, here’s what my chart looks like:
If you liked this post on How to Make a Forecast Chart in Excel With a Dotted Line, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
If you have values that you are plotting on a chart and some of them are zeroes, you will likely notice your chart sliding all the way down to the bottom. It can be problematic when you are entering in year-to-date data which will inevitably lead to blank or zero values. That’s why in this post, I’ll show you how to hide zero values from showing up on a chart in Excel.
Let’s start with the following example:
In this case, we have values for just a few months. From April through to December, the values aren’t necessarily zero — we just don’t have data yet. But the problem is that on the chart, the line graph shows them as being zeroes. If we were to get rid of those zero values, it would fix the issue:
But the problem is that this may not be a convenient solution. If you want to create formulas to calculate the totals for each month, going back and deleting the ones with no values and remembering to put the formulas back in for future months isn’t going to be a very convenient option. There is a way that the calculations can be adjusted so that you can still get the zero values not to show.
Let’s suppose you have a SUMIF calculation for each month which looks as follows:
One way to fix this issue is to add an IF statement to avoid the zero values. But returning a blank value won’t fix the issue. Instead, what we’ll want to do is return an #N/A value. To do that, you just need to use the following formula:
=NA()
That just needs to be incorporated into the formula to say that if the sum is equal to 0, an NA value is returned:
=IF(SUMIF(E:E,A3,F:F)=0,NA(),SUMIF(E:E,A3,F:F))
Although this solves the problem, it creates a bit of an eyesore with the #N/A values showing up in our data set. If you want to get rid of that, there’s a solution for that as well. Using conditional formatting, we can adjust the values so that any #N/A values show up blank. To do this, I’ll select column B and under the Conditional Formatting in the Home tab, select the option for a New Rule:
Select the option to Use a formula to determine which cells to format and enter the following:
=ISNA(B1)
I need to use B1 since that is the start of the range that I have selected. Next, you can just click on the Format button and set the font color to white so that the #N/A values don’t show up. This is what my sheet and chart looks like after applying the formatting:
If you liked this post on How to Hide Zero Values on an Excel Chart, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
It’s time for an updated dashboard post. My original post is now three years old and probably overdue for an update. This time around, I’m going to start from scratch using a real data set from the Bureau of Labor Statistics, where I’ll walk you through my process from start to finish. To follow along, you can download the data I’m going to use from here (I’m going to use the 2020 state data. This is the XLS link).
Preparing the data
If your data is no good, then it won’t matter how great your charts and visuals look. That’s why it’s important to have a look through the data to see how usable it is. And you may not notice any issues until you start populating your charts. But one of the things that are noticeable right of the bat in this data set is that instead of empty values on this sheet, there are # or * signs.
That’s going to be a problem if you want to do any computations on this data. You can use Find and Replace to replace the data with empty values. Note that for the *, you’ll need to find ~* rather than just *, otherwise Excel will interpret the * as a wildcard and find everything.
One other thing that I am going to do is create another column for the occupation titles. In column J, there are more than a dozen titles for the ‘major groups’ (major is indicated in column K). I am going to create a table to group them even further. I’ve put this on a separate lookup sheet:
Now, what I am going to do is insert a column on my main data sheet, after column J, which will do a lookup on this table. The formula will be as follows:
=IF(L2=”major”,VLOOKUP(J2,Sheet1!A:B,2,FALSE),””)
Now, I have a category field in column K for the ‘major’ group classifications:
Next, I’m going to convert the data into a table. To do this, click on any of the cells in your data set, and on the Insert tab, click on Table:
Once done, you should notice some default table formatting gets applied to your data set:
And to make it easy to reference, I’m going to click on the Table Design tab, and under the Table Name section on the left, I’m going to re-name the table to tblData:
To change the name of a table, all you need to do is click on it and make your changes, then press enter.
Creating the pivot tables
For this dashboard, I’m going to create pivot tables and use charts to show the following:
Median salary for the specified position.
Wages by percentile.
Median salary for the specified state based on job categories.
A pie chart showing how many jobs there are by category.
A gauge chart showing how the median salary compares to the national average.
A map chart showing the median wages by state.
Median salary for the specified position
To create this visual, I’m going to create a pivot table from the tblData and put it on a new ‘PT’ tab. For this, I am just going to take the average of the A_MEDIAN column. I will also filter the O_GROUP field so that it only includes the ‘detailed’ group to avoid including the categories. I will also adjust the formatting so that it uses the accounting format. The pivot table itself contains just one value:
I only want this value to show up in a box but what I’m going to do is create a column chart from this. For just the number to be visible, I’m going to add a data label and then remove everything else, including the legend, gridlines, and make the column a clear color. Lastly, I’ll copy my first visual onto a new ‘Dashboard’ tab and put the words ‘Median Salary’ directly above it:
Wages by percentile
Next, I’m going to create a bar chart that shows the wages for a position by the various percentiles that are in the data set. For this, I’m going to grab all the different percentile fields, including the median:
A_PCT10
A_PCT25
A_MEDIAN
A_PCT75
A_PCT90
I’ll need to set these calculations to be averagesjust like on the earlier calculation. I can re-name these to ’10th percentile’, ’25th percentile’, and so on, to make it easier to read. Then, I’m going to create a 3-D bar chart, change the colors, and add some labels so it looks like this:
Median annual wage for the specified state based on job categories
Now, I’m going to create a pivot table and chart to show what the median annual wage is across the different categories I specified earlier for the selected region. This is a simple pivot table set up, all that’s needed is the A_MEDIAN average in the values section of the pivot table, the CATEGORY in the rows, and the O_GROUP to filter just the ‘major’ jobs. This will result in the creation of the following column chart:
A pie chart showing how many jobs there are by category
One of the interesting metrics in the data set is the number of jobs there are per 1,000 jobs in the given region. This is going to be similar to the previous chart, except this time I am going to use the JOBS_1000 field. I’m going to use a pie chart for this visual just to change it up a little bit.
A gauge chart showing how the median salary compares to the national average
I’m going to use a gauge chart to compare the median salary against the national median and how it compares. For detailed steps on how to create a gauge chart, please check out this post. For this visual, I need to create one pivot table just for the national median wage. To do this, I just need to grab the median value and filter the O_GROUP by ‘total.’
For the actual gauge chart, I need to set up a table for the slices and the ranges. I will go with a setup as follows:
The % of completion will take the median value and divide it by the national average. But to avoid it going over 100, I’ll use the MIN formula. And for the ‘high end’ value, I take 200 (think of 100 as the top half of the circle and the other 100 the bottom half) and subtract the % of completion and add the size of the slice. Here is what it looks like when the median salary is greater than the national median:
This is what the gauge chart looks like once it’s been set up following the steps in the previous post:
A map chart showing the median wages by state
Creating a map chart is pretty easy in this situation because we have all the state names and all I need to do here is create a pivot table with the A_MEDIAN value. Here’s what my pivot table looks like:
However, you can’t create a pivot chart directly from a pivot table. But there is a way around that. I’m going to create another table that copies the values from the pivot table. They simply equal the values to the left:
Now, I can create a map chart based on this table:
I now have all of my charts set up:
What’s next is to set up the slicers.
Adding and linking the slicers
I’m going to add two slicers for the dashboard, one for the state and one for the job title.
To insert a slicer, all that’s necessary is to click on any one of the pivot tables and on the Insert tab, click on the Slicer button:
Then, select the fields you want to add. Generally, I add the fields that have the most selections and longest names going down vertically. In this case, that’s the OCC_TITLE field. For the State and Category slicers, I have those going across:
I’ve also added a title just below the slicers to give the dashboard a name. The last piece of the puzzle here is to link the slicers to the pivot tables. Previously, I linked them to all of the tables. But for some of these charts, I don’t want them to link to everything.
For the State and Category slicers, I want them to update everything except the national median pivot table. And for the OCC_TITLE slicer, it should also not update the jobs per 1000 pivot table or the median wage by category. The reason being is that those charts will lose their value if only one job is selected, as the point is they should give an overview of the different categories. Similarly, you could also unlink the state slicer to the map chart.
To manage these connections, you can slicer and select Report Connections:
From there, you can select with pivot tables you want the slicer to link to:
And to keep your slicers from staying in put despite any changes, you can also right-click and select Size and Properties and then select the option to Don’t move or size with cells:
Now, the dashboard is ready to go!
If you liked this post on Making Dashboards in Excel With Map and Gauge Charts, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
A box plot chart can show you lots of information in just one visual: the minimum, maximum, median, and interquartile range of a data set. It can be a great way to visualize your data to see its range and how narrow or broad the values are. In this post, I’ll show you a couple of ways to create a box plot. The first is through the box and whisker chart on newer versions of Excel, and also how you might create this just from a stacked chart.
For this data set, I am going to create some random test results to compare an easy test versus one that is average, and another that is difficult.
Creating the box and whisker chart
This is the sample data I’m going to work with for this example:
The way the data is formatted, it is already ready to use in a box and whiskers chart. Simply click anywhere on the data set and on the Insert tab, you can click on the pop out button for Charts or just click on the Recommended Charts:
Then, under the All Charts section, there is an option for Box & Whisker. Click on the only chart available in that section:
And then that will put your data into a box plot:
Besides adjusting the range so that it does not go higher than 100, the box and whiskers chart is ready to use immediately with minimal adjustments. From the above chart, we can see that the easy exam had the smallest range, highest min and max values, and a broader interquartile range than the moderate exam — this suggests more variability. The real value in box plots is in being able to compare them against other data sets.
Creating a box plot using a stacked chart
Another way to make a box plot in Excel is by using a stacked chart. This involves more steps but it allows you to make this work on older versions of Excel. If you have trouble following along, you can download the sheet I created for this purpose here.
First, we’ll need to organize the data by quartiles. A table is needed that starts off with the minimum value, and then the size of each quartile. For the minimum value, you can just use the MIN function and put in the entire range in there. The quartile functions may be a bit unfamiliar for many users but the calculations aren’t complex. The QUARTILE.INC function would look as follows if you want to pull in the first quartile:
=QUARTILE.INC(A1:A100,1)
If your range is in A1:A100, then the above formula would return the position of the first quartile. Changing the last argument to 2 and then 3 will give you the position of the remaining quartiles (you don’t need quartile 4 for this calculation). Once you have the value of each of the quartiles, then the next step is to calculate their respective sizes.
For the first quartile, you’ll take the first quartile and subtract the minimum value. The size of the next quartile will be the Median value (for this you can use the MEDIAN function) less the first quartile. The third quartile size will take the third quartile and subtract the median. Finally, the last quartile size will take the maximum value and subtract the third quartile.
Based on those calculations, the table that will be used in the stacked chart is as follows:
If I go to create a stacked chart using that table, by default it will look like this:
The first thing I need to do is flip the rows/columns. To do this, right-click on the chart and click Select Data. And then, click on the button that says Switch/Row Column, which will transform the chart into this:
This is a bit better but I still need to remove the min section out of sight. To do this, I can right-click on that part of the chart and change the fill color to be blank. Then, my chart starts from the minimum value, rather than from 0:
For the bottom (orange) series, I will do the same and change the fill colour to orange. I will also take an additional step afterwards and under the Chart Design tab, select Add Chart Element and click on Error Bars and then Standard Deviation:
That will create the following line leading up to the next quartile:
However, this is not exactly what is desired since it should only start from the minimum value. To correct this, right-click on the error bar and click Format Error Bars and change the settings to the following:
Direction: Minus
End Style: No cap
Error Amount: Percentage: 100%
Now, the line starts from the minimum:
Follow the same steps for the blue quartile range at the top and then the chart will look as follows:
The last steps are really optional but I will get rid of the legend and also change the colors and outlines of the yellow and grey quartiles so they are all one color. My finished box plot looks as follows:
If you liked this post on How to Make a Box Plot in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
