Cara membuat boxplot di excel

Box Plot Chart Overview

Most people are familiar with a line chart, where you show data over a period of time.

First, we'll take a quick look at a line chart, then we'll see how a box plot chart would show the data in a different way.

For both types of chart, the same data will be used:

• sales for three stores in a retail chain,
• over the 12 months of a year

Line Chart - Monthly Data

First, in the line chart below, you can:

• see how each store's sales progress over the year
• compare each store to the other two stores

Box Plot Chart - Quartiles

Instead of showing data over time, a box and whisker chart shows the data in numerical order, divided into 4 equal sections (quartiles).

This diagram shows the different parts of the box plot chart, and you'll see how to create this, in the video and written steps, further down on this page.

Store Sales Box Plot

Here is a horizontal box plot chart for the three stores' sales data. This chart lets you compare:

• Highest and lowest sales numbers for each store
• Median number (middle value) for each store
• Range of numbers on either side of the median for each store

What Does This Box Plot Show?

At a quick glance, this chart show the following nformation:

• Store 3 had 6 months of low sales (light blue box) and 6 months with a wide range of sales amounts (darker blue box).
• Store 1 had the most consistent sales over the year, with small boxes, and the shortest span from its minimum, to its maximum.

See Steps and More Examples

Excel 2013 doesn't have a Box Plot chart type, but you can build your own Box and Whisker plot, by following the examples below.

• Build a Box Plot: In the next section, my video shows how to build a vertical box plot chart, comparing sales in two regions. There are written steps below the video.
• Average Marker: Further down the page, there's another example, that shows how to add an average marker to a Box Plot chart. Watch the video to see the steps, and there are written steps too
• Wait Times: The third example compares airport security line wait times. The video shows the set-up steps, and there is a link where you can find written notes about the example.

In these examples, you'll discover how to:

• Add calculations to the worksheet
• Create a Stacked Column chart type, or a stacked bar chart
• Add custom error bars
• Format the boxes

Video: Build Your Own Box Plot

To see the steps for creating a simple box plot chart (box and whisker plot), watch this short video. The written instructions are below the video.

Note: To see the video transcript, go to the Box Plot Chart Video page.

Video Timeline

• 00:00 Box Plot Introduction
• 00:12 Add Calculations
• 00:54 Plan the Chart
• 01:14 Box Size Calculations
• 02:30 Create Stacked Column Chart
• 03:17 Add Whiskers at Top
• 03:51 Add Whiskers at Bottom
• 04:29 Add Formatting
• 05:12 Get the Sample File

Create the Box Plot Chart

To start the Box Plot chart:

1. Select cells E3:G3 -- the heading cells
2. Next, press Ctrl and select the blue data cells and labels, E10:G12.
3. On the Excel Ribbon, click the Insert tab
4. In the Charts group, click Column Chart, then, under 2-D Column, click Stacked Column

A chart is added to the worksheet, with stacked columns

To Fix the Box Plot Chart:

If the chart shows 3 stacked columns, instead of 2 (for East and West), follow these steps to fix the problem

1. Click on the chart, to select it
2. On the Excel Ribbon, go to the Chart Design tab
3. In the Data group, click the Switch Row/Column command, to get the box series stacked correctly.

The chart should have 2 stacked columns now - East and West.

To format the Box Plot Chart:

First, follow these steps to hide the bottom series (Box 1 - hidden) in the stacked columns

1. In the East column, right-click on the Box 1 - hidden series
2. In the pop-up menu that appears, click the Fill drop down arrow
3. Choose No Fill from the list
4. Next, click the drop down arrow for the Outline colour
5. Choose No Outline from the list, so it isn't visible in the chart.

Next, follow these steps to remove the legend, which isn't needed

1. Click on the chart's Legend, to select it
2. On your keyboard, press the Delete key, to remove the legend

To add the Top Whisker:

1. Click the top box, and on the Ribbon's Design tab, click Add Chart Element
2. Click Error Bars, and click More Error Bar Options
3. In the Error Bars Options, under Direction, click Plus
4. In the Error Amount Section, click Custom, and click Specify Value
5. Delete the contents of the Positive Error Value box, and select the Whisker Top values on the worksheet
   

6. Click OK to close the Custom Error Bars window.

To add the Bottom Whisker:

1. Click the hidden bottom box, and on the Ribbon's Design tab, click Add Chart Element
2. Click Error Bars, and click More Error Bar Options
3. In the Error Bars Options, under Direction, click Minus
4. In the Error Amount Section, click Custom, and click Specify Value
5. Delete the contents of the Negative Error Value box, and select the Whisker Bottom values on the worksheet
   

6. Click OK to close the Custom Error Bars window

Final Formatting Steps

To finish the box plot chart formatting, follow these steps:

1. Right-click on top box in the East column
2. In the pop-up menu that appears, click the Fill drop down arrow
3. Choose a light grey colour from the list
4. Next, click the drop down arrow for the Outline colour
5. Choose a darker grey colour from the list - this will visually separate the two boxes
6. Next, format the lower box with the same fill colour and outline colour

(Optional) Move the chart, so it covers the cells where you created the formulas.

Langkah langkah membuat boxplot?

Bagaimana Cara Membuat Chart di Excel?

Apa yang dimaksud dengan boxplot Apa keuntungan diagram boxplot?

Apa itu outlier dalam boxplot?