How to Add a Horizontal Reference Line at a Fixed Value in Excel Charts
Question details
The user needs to add a straight horizontal reference line at a specific fixed value (e.g., y = 24.8) across the category or dimension axis of an existing chart.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Displaying a fixed target, threshold, or baseline against varying data points in a line or column chart to visually analyze performance.
- Observed behavior
- The standard chart lacks a built-in reference line, requiring a workaround to display a constant threshold value across all data categories.
Ensure your primary chart is already created and identify the exact fixed value you want to use for your horizontal reference line.
Add a Helper Data Series to Create the Reference Line
By adding a new column to your source data filled with your constant fixed value, you can overlay a line chart on top of your existing data to act as a dynamic reference line.
This method is highly recommended over drawing a static line shape because the reference line will automatically adjust if the chart's axis scales change or if the chart is resized.
In your source data table, click to select the column header directly to the right of your category (Dimension) column. Right-click and select 'Insert' to create an empty column.
Highlight all the empty cells in the new column that align with your data rows. Type your fixed value (e.g., 24.8), then press 'Ctrl + Enter' to populate all selected cells simultaneously.
Click on your existing chart to select it. Right-click and choose 'Select Data'. Click 'Add' in the Legend Entries (Series) box, name it 'Reference Line', and select the newly created column as the series values.
Right-click the newly added data series on the chart and select 'Change Series Chart Type'. Locate the 'Reference Line' series in the dialog box and change its chart type to 'Line'. Click 'OK'.
Create Professional Charts and Reference Lines with WPS Spreadsheet
WPS Spreadsheet makes managing complex chart data and visualizing thresholds incredibly easy. Adding helper columns and combining chart types for horizontal reference lines is fully supported, with a highly intuitive interface.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office, open Spreadsheet, and load your existing workbook containing the chart.
- 2. Add your target values: Create a new column next to your data named 'Target' and fill it with your constant numerical value.
- 3. Add the series to the chart: Click the chart, go to the 'Chart Tools' tab, click 'Select Data', and include your new 'Target' column.
- 4. Adjust the chart type: Right-click the new data points on the chart, click 'Change Series Chart Type', and select the 'Line' option.
Frequently Asked Questions
Can I draw a line shape instead of modifying my data table?
Yes. You can go to the 'Insert' tab, select 'Shapes', choose 'Line', and hold the Shift key while drawing to ensure it remains perfectly horizontal. However, this shape will not automatically scale or move if your chart's axis values change or the chart is resized.
Why does my newly added reference line look like bars?
When you add new data to a column or bar chart, Excel defaults to the same chart type. You must right-click the new data series, choose 'Change Series Chart Type', and specify 'Line' specifically for the reference series.
How can I make the reference line dynamic based on the data average?
Instead of typing a fixed number like 24.8 in your helper column, type the formula =AVERAGE($B$2:$B$10) (assuming column B holds your data). Drag this formula down. The reference line will automatically move up or down as your data changes.
Does this method work for scatter plots as well?
Yes. For scatter plots, you add the new series and set its X values to span from the minimum to the maximum axis bounds, and both Y values to your fixed reference value (e.g., 24.8), then format it as a line connecting those two points.
