A box plot or box and whisker plot can be created in Excel using the new built-in Box and Whisker chart for Excel 2016 or later. To create your own chart, you'll need to use a couple of tricks. First, the box can be created using stacked column charts. Second, the whisker can be created using y-error bars. Excel provides built-in functions that you will need to calculate the quartiles used for the 'box' part of the box and whisker plot. You can jump right in by downloading the free Box Plot Template below, but I also suggest you read through the information below which describes how the functions and calculations are used to create a box and whisker plot.
Box Plot Template
Download stageplotpro 2982 for mac from our website for free. Stage plot template for mac free. Create professional accurate and easy to read stage plots for stage managers and sound engineers. You can build a collection of stage plots for different kinds of gigs then print or email them directly from your mobile device. SPG is a mobile, efficient, and customizable stage plot design program that gives production staff, musicians, and other behind-the-scenes technical staff, the ability to see at a glance, exactly how instrumentation, amps, lights, platforms, and other 'props' should be organized on stage. Here are some details; Stage Plot Design. See our free timeline template collection. This free timeline template gallery was created to help professionals make clear, visually-appealing timelines for high-level presentations. Click on Download to save the desired template as a.pptx slide and edit it in PowerPoint or, where available, select Edit Online to open it in Office Timeline.
for Excel (without using the new built-in chart type)Download
⤓ DownloadLicense: Private Use (not for distribution or resale)
'No installation, no macros - just a simple spreadsheet' - by Jon Wittwer
Description
Create a box plot quickly and easily. Enter your data into the Data sheet and the chart in the Plot worksheet will update automatically.
Limitation: This template shows only the maximum or minimum outliers, if there are any. Normal convention for box plots is to show all outliers. To show all outliers, you can use the new Box and Whisker Chart that is a new built-in chart type in Excel 2016 or later (see the template below).
Regarding Negative Values: Using bar charts to display the interquartile range limits the technique described below to displaying positive values (or at least Q1 must be positive). There are a couple ways around this problem and both of these alternate methods are included as additional worksheets in the file. (1) You can shift the data so that it is positive before creating the box plot and (2) you can avoid the use of bar charts and display Q1, Q3, and the Median using series markers instead.
Box and Whisker Chart Template
for Excel (Using the new Box and Whisker Chart type for Excel 2016 or later)Download
⤓ ExcelLicense: Private Use (not for distribution or resale)
Description
This template uses the built-in Box and Whisker Chart. Although the chart does not require you to calculate the summary statistics, I included the formulas so that I could figure out how Excel was doing things in their chart.
IMPORTANT: The new built-in Box and Whisker Chart in Excel 2016 shows whiskers that exclude the outliers. See below for more information about creating a box plot, and how the new 2016 may differ from my original template (or other conventions).
Creating a Box and Whisker Plot
Box plots are very useful data visualization tools for depicting a number of different summary statistics and especially for graphically comparing multiple data sets. It is much easier to create these plots in Excel if you know how to structure your data. You can take a look at the template as an example.
Fig 1. An example box and whisker plot from the Box Plot Template showing the IQR, whiskers, and max/min outliers.
Creating the Box
The box part of a box and whisker plot represents the central 50% of the data or the Interquartile Range (IQR). The lower edge of the box plot is the first quartile or 25th percentile. The upper edge of the box plot is the third quartile or 75th percentile. You may want to check out my article on percentiles for more details about how percentiles are calculated.
- Find the first quartile, Q1, using =QUARTILE(range,1) or =PERCENTILE(range,0.25)
- Find the median, Q2, using =MEDIAN(range) or QUARTILE(range,2) or =PERCENTILE(range,0.5)
- Find the third quartile, Q3, using =QUARTILE(range,3) or =PERCENTILE(range,0.75)
- Calculate the interquartile range (IQR) as Q3-Q1
- Calculate the mean using AVERAGE(range). The mean is not always displayed in a box plot, but in the new built-in Box and Whisker Chart for Excel 2016+, it is shown as an 'x'.
Note: To exclude the median when calculating the quartiles, you can use the new PERCENTILE.EXC and QUARTILE.EXC functions. It appears that the older PERCENTILE and QUARTILE functions are the same as PERCENTILE.INC and QUARTILE.INC functions.
The location of the median line relative to the first and third quartiles indicates the amount of skewness or asymmetry in the data. If the distribution is symmetric, the median will be exactly in the middle. if the median is closer to Q3, the distribution is negatively skewed (or 'skewed to the left' meaning the left tail of the distribution is longer). If the median is closer to Q1, the distribution is positively skewed.
The plot in Excel is created using a stacked column chart with 3 series. The first series (bottom column) is Q1 and the border and area properties are set to none so that the column is not visible in the chart. The second series is Q2-Q1. The third series is Q3-Q2. These two series, stacked together make up the interquartile range. The area property is set to none for these two series to create just the outline for the box.
Creating the Whisker
The whiskers in a plot represent the tails of the distribution. The whiskers can be created using error bars in Excel. Because of the ease of calculation, the convention for the length of the whisker that I have used in the box plot template comes from [1]:
- The upper whisker starts at Q3 and extends upward to Q3+1.5(IQR) or the maximum value, whichever is lower.
- The lower whisker starts at Q1 and extends downward to Q1-1.5(IQR) or the minimum value, whichever is greater.
For the built-in Box and Whisker chart in Excel 2016+, the upper whisker starts at Q3 and extends upward to Q3+1.5(IQR) or the maximum non-outlier value, whichever is lower. Similar change for the lower whisker.
Another common convention is that instead of extending the whisker to a calculated value of Q3+1.5(IQR), the whisker is extended to the last data point that is less than or equal to Q3+1.5(IQR), and similarly with the lower whisker.
In the box plot template, the whiskers are created by adding Y-error bars to series 1 (Q1) and series 3 (Q3-Q2).
Outliers
The biggest problem with creating your own box and whisker plot in Excel is showing all the outliers - the points that fall outside of the range depicted by the box and whiskers. This is a problem because you don't know how many outliers there will be. Instead of showing a point for each outlier, the custom box plot template above shows only the max and min values if they are outliers. For reference, the number of upper and lower outliers is given in the table to indicate if there are more outliers than just the max or min.
The new built-in Box and Whisker Chart for Excel 2016+ can show all of the outliers. You can also select an option to show all internal (non-outlier) data points as well.
Variations
I haven't found a good way to create the following box plot variations in Excel, so I won't describe them in detail, but I've included them here for your information. Most good statistical software like MINITAB® will be able to include these additional features.
Variable-Width Box Plot
Notched Box Plot
How to Set up a Data Table for the Box and Whisker Chart in Excel 2016+
Stage Plot Pro
I've included this example to show how Excel requires that you set up your data table if you are using the new built-in chart type.
Note that the columns are different series (which can be displayed via the Legend), while the use of titles along the X-axis requires you to use a column like column A for identifying the groups.
In my template above, I prefer using columns for different data sets, so I'm using the Legend to label the chart, with the x-axis label removed.
Other Statistics Spreadsheets
- Pareto Chart - This template helps you perform a pareto analysis to analyze most significant factors.
- Control Chart - Create an X-bar, R or S Chart for process control.
- Dot Plot - How to create a dot plot in a spreadsheet without a chart object.
References
- Box Plots at wikipedia.com - This is actually a really good article.
- [1] A. Mitra, Fundamentals of Quality Control and Improvement, 2nd ed., Prentice Hall: New Jersey, 1998.
- [2] S. B. Vardeman, Statistics for Engineering Problem Solving, PWS Publishing Company: Boston, 1994.
- Built-in Box and Whisker Chart in Excel - Read about the new chart type.
- More Details about the new Excel Chart - Talks about the definitions as well as the difference between including or excluding the mean.
How to cite this page
Stage Plot Template For Mac Free Download
When using this template to create an image for a publication, you can use the following citation (or one appropriate for your paper's format):- Wittwer, J.W., 2009, Box Plot Template, Vertex42.com, https://www.vertex42.com/ExcelTemplates/box-whisker-plot.html (your access date).
Related Content
Create your Tech Rider online, fast and free
When you're playing in a band, you want everything to be perfect. You want to look good, sound good and have a day that's easy with no technical problems. Many bands don't realize that they can make this process so much easier by generating a Stage Plot online. Tech Riders are essential for every band and enable everybody to have a much easier show experience. On your Stage Plot you can show where each member of the band and each instrument will be positioned on stage, including the locations of the mics, amplifiers, monitors and possibly the cable positions. The key is to create a clear but specific Tech Rider, so everything is set up accordingly and everything is prepared for you, get it right and you will find your checks will go smoothly with very little hassle. A good Stage Plot will save you a lot of stress in the long run.
Plus
- Drop up to 15 icons
- Choose from 69 objects
- 30 days duration
Limited offer!$3.99
Ultra
- Rotate Icons 360°
- Drop up to 100 icons
- Choose from 200+ objects
- Colored icons
- Lifetime deal
- No Advertisements
Regular $36.99$24.99
Pro
- Rotate Icons 360°
- Drop up to 25 icons
- 189 objects available
- 1 year duration
Limited offer!$12.99
UPGRADE NOW
Stage Plot
What is a Stage Plot?
A Stage Plot (or technical rider) is a document that details exactly what your band needs to make a show fantastic. It details the equipment and instruments of every band member, the input you will need from the venues that you're playing and how you want your stage to look and sound.
What Can You Include on a Tech Rider?
If you're a small band and you don't have much equipment or instruments with you, your Stage Plot will most likely mainly cover the gear that you're using onstage. As your band grows, your Tech Rider will start to include any FOH consoles that you bring with you, the lighting rigs you're touring with and any pyrotechnics that you have onstage. A Stage Plot is constantly updated as you continue touring and continue growing as a band.
How Do You Create a Stage Plot?
You can search for bands who have a similar sound to yours, as they will more than likely have a similar Tech Rider to the one that you'll need. When you don't have a good idea of what you need to include on your Tech Rider, then make a list of the equipment you have for your band and the make and model that this equipment is. When you've done this, you can use an online Stage Plot generator to create your full Tech Rider online.
Stage Plot Template Pdf
What Do You Do With a Stage Plot?
Once you've created your Tech Rider, you can send it out to all venues that you will be playing in advance of you doing your shows there. They will know exactly what equipment to expect from you and your day will run a lot more smoothly. You'll soon see what a difference sending this Tech Rider out to venues in advance makes to the quality of your live show!
Don’t forget your name and contact details. Preferably you want to have someone as your technical contact, it can be any members of the band who have the best knowledge and set up experience.
Hospitality Rider
First off let us just explain that a rider is a set of requests and criteria made by the performer prior to the performance. Everyone has a Hospitality Rider and everyone uses them, from Adele performing in concert to Hugh Jackman performing in theatre.
What is a Hospitality Rider?
A Hospitality Rider is just as it sounds, a set of requests to welcome the performer and make them feel at home. They can also be contractual, so if the needs of the rider aren't met the performer has the right to cancel the performance and the fees be paid regardless.
Stage Plot Template For Mac Free Pdf
What sort of requests are in a Hospitality Rider?
Within reason anything can be written in the rider, from the hotel they stay in to the food they eat and the furniture they sit on. The bigger the name, the more demands you can get away with it seems.
The hotel - In some riders a price for accommodation is specified and sometimes a location.
Food and drink - Most performers stipulate a certain set of beverages, maybe part of their routine is to have a nice cold drink before or after the performance and it has to be a particular brand. Food is also a popular request, some request they must have fruit in their room or strictly forbid deep fried foods. Vicious Cycle for example stipulated a hot meal for the artists and crew but no fried food.
Transportation - Do you need a car to and from a show or to take you to somewhere after a show? Of course you do.
The majority of the Hospitality Rider are requests made for comfort and to help the performer relax but there are times where the big names become 'divas' and demand outrageous requests and god help the organisers if these demands aren't met.