Add a Line to Excel Charts

Charts in Excel offer a graphic way to “see” data, but sometimes adding an extra element can add meaning to the information. This weeks Software Tips and Tricks shows how to add a horizontal line of values to a bar chart. This can help illustrate relative values on the chart.

Here’s How

1.      Create or open an Excel spreadsheet. When creating the data for your chart, include a column for a static, unchanging value. This will let you add a solid line in the chart as a reference point for comparison. For example, if your Excel spreadsheet reports on several months’ data, you may decide to include a column where each cell contains the average for the period. (You can hide the column in the spreadsheet, which we will cover next week.) In this sample, Column F is formatted as a number with no decimals and displaying an Average of all chart values, using this formula:  =AVERAGE(B2:E5)

2.      After completing the chart, right-click on one the static value’s data points; in this sample, we clicked on the purple bar representing Average.

3.      In the pop-up, click Chart Type…

4.      Select Line.

5.      Click OK.