How to Create a Box-and-Whisker Plot with a Time Axis in Excel
Question details
The user needs to create a Box-and-Whisker plot using a time axis, but Excel defaults to a categorical axis, which distorts time-series gaps.

- Product
- Microsoft Excel
- Device & OS
- Mac
- Scenario
- Visualizing time-series data using a Box-and-Whisker chart to show data distribution over specific years.
- Observed behavior
- Excel treats years as categorical variables rather than a continuous time axis, resulting in irregular gaps and unexpected axis labels.
Review your dataset to identify the exact chronological start and end points, noting any missing years or time periods that will need blank placeholders to simulate a timeline.
Simulate a Continuous Time Axis Using Blank Categories
Since Excel's Box-and-Whisker plot only supports categorical axes natively, you can manually insert blank categories to represent missing years, creating the illusion of a continuous timeline.
Excel's built-in Box-and-Whisker chart does not support true continuous time or date axes. Instead, it treats every unique label as a distinct category. To accurately reflect time gaps and prevent distorted visualizations, you must manually structure your data table to include empty rows for years where no data exists.
Organize your data chronologically with the years entered in the first column (or row) and the corresponding data values in the adjacent cells.
Identify any missing years in your timeline. Insert empty rows for these missing years and leave the data cells completely blank so the visual timeline remains proportional.
Select your entire dataset, including the newly added blank rows. Go to the 'Insert' tab on the ribbon, click the 'Statistic Chart' icon, and select 'Box and Whisker'.
Right-click the horizontal x-axis and select 'Format Axis'. Verify that the years are rendering as your category labels. You can safely delete or hide the axis title if it clutters the chart area.

Create Professional Statistical Charts with WPS Spreadsheet
WPS Office Spreadsheet provides intuitive charting tools to effortlessly create Box-and-Whisker plots and other statistical visualizations. With high compatibility with Microsoft Excel files, you can manage your data and design professional reports seamlessly.
- 1. Open Data: Launch WPS Spreadsheet and open your dataset containing the chronological records.
- 2. Select Range: Highlight the data range you want to visualize, ensuring you include any blank rows meant to represent time gaps.
- 3. Insert Chart: Navigate to the 'Insert' tab on the top ribbon and click on 'Chart'.
- 4. Choose Box and Whisker: Select the 'Statistic' charts category, choose 'Box and Whisker', and click 'OK'.
- 5. Format Axis: Double-click the horizontal category axis to open the formatting pane and adjust your labels as needed.

Frequently Asked Questions
Can I force Excel to use a date axis for Box-and-Whisker plots?
No, Excel natively restricts Box-and-Whisker charts to categorical axes. You must use workarounds, such as inserting blank data rows for missing years, to mimic a continuous date axis.
Why are there unexpected gaps in my Box-and-Whisker chart?
Unintentional gaps usually occur if there are hidden blank rows in your dataset. Conversely, if you intentionally left blank rows to simulate a timeline, these gaps are correctly representing periods with no data.
Does this workaround apply to Excel for Windows as well?
Yes, this workaround applies to both Excel for Mac and Excel for Windows. Across all versions, the x-axis of Box-and-Whisker plots is treated exclusively as categorical data.




