Trying to add labels to box plot in excel

Question

Hello, I'm trying to make a box plot in excel with data and labels for those data. When I select just two rows of data (the numbers I'm comparing) it makes a box plot just fine but it doesn't have the labels that I want. So I try with the labels I want as the horizontal (category) axis labels in the select data menu, but when I select all three rows of data (the one row of titles and two rows of data), the plot marks every data point as an individual x as opposed to making individual box plots, however it does display the labels that I want sometimes.

Answer 1

Hello Jacob,

I'm Ibhadighi and I'd happily help you with your question. In this forum, we are Microsoft consumers just like yourself.

To add labels to your box plot in Excel, you should follow these steps:

1. First, create the box plot using just the numeric data. Do not include the row of labels in this step.
2. Click on your newly created box plot to select it.
3. With the chart selected, go to the Chart Design tab or Chart Tools on the ribbon, click on "Add Chart Element" > "Data Labels" > "None" to clear any existing labels, then again "Add Chart Element" > "Data Labels" > "More Data Label Options".
4. In the label options pane, check the "Value From Cells" option. A dialog box will appear prompting you to select the range of your label cells. Select the row that contains the labels you want to use.
5. Ensure that the range you select for labels corresponds to the data points in your box plot.
6. Click OK to apply the labels, then close the format pane.

The labels should now be displayed on your box plot corresponding to the appropriate data points. If you run into any issues, it may be helpful to use the "Select Data" option on the chart to manually adjust which data range is used for the horizontal axis labels.

I hope this helps.

Best Regards, Ibhadighi

Answer 2

Hi Ibhadighi,

I followed your instructions but it still produced a plot with the individual x's and also was displaying my number values, not titles even though I went into the select data option on the chart and set my titles as the horizontal axis labels.

Before step 5

After step 5 (changing the horizontal axis labels):

Answer 3

Hello Jacab,

Thank you for letting me know,

The images you've shared suggest that the box plot is interpreting each data point as a separate series, which is why you're seeing individual markers (the X's) instead of boxes. To fix this and ensure that your titles are used as axis labels rather than the data being plotted, you'll need to follow a specific process when setting up your data range for the box plot.

Here's a simplified version of the process:

1. Ensure your data is arranged correctly. For a box plot, you typically want each set of data that you want to plot as a box in a single column with the label at the top.
2. Go to the 'Insert' tab, click on the 'Insert Statistic Chart' dropdown, and choose 'Box and Whisker'.
3. Select the Data Correctly:
   • After inserting the box plot, right-click and select 'Select Data'.
   • Use 'Add' under 'Legend Entries (Series)' to add a new series.
   • In the 'Series Name' field, select the cell that contains the label for your first data set.
   • In the 'Series Values' field, select the range of cells that contains the data for this box plot.
   • Repeat this for each data set you have.
4. Remove Default Labels:
   • If Excel has automatically added labels from the number values, remove them.
   • Right-click on the chart, select 'Select Data', and then remove any automatically added series that you don't want.
5. Set Axis Labels:
   • If your labels are still not displaying correctly after setting up the series, you might need to manually edit the horizontal axis labels again.
   • In the 'Select Data Source' dialog, click 'Edit' under 'Horizontal (Category) Axis Labels' and select the range of your category labels.
6. Some versions of Excel handle box plots differently. If you're using an older version, consider updating or look for version-specific instructions.

If these steps don't work, it might be a good idea to create a box plot with a different tool or software that offers more flexibility with data plotting, such as R or Python's seaborn library, which allows for more complex and customized box plots.

Thank you, Ibhadighi

Answer 4

Hi

It's hard to see exactly what you want in the screen shots. Can you supply an example workbook?

Why a sample file is important for troubleshooting - - - - - - - and how to do it.