How to add deviations into your chart

Excel 365 2016 2013 2010 2007
Sometimes you can give your charts more impressive view by showing deviations of some real process from its expected flow.
Deviations in the chart in Excel 2003

To create a chart with deviations, do the following:

   1.   Select the data range (in this example A4:C12).

Using deviations in charts in Excel 2003

   2.   Select Insert -> Chart... to open the Chart Wizard dialog box:

Chart Wizard in Excel 2003

Choose the Line type, the Line sub-type and then click Next >.

   3.   In the Step 2 of Chart Wizard check the data and click the Next > button.

   4.   In the Step 3 of Chart Wizard, enter a title in the Titles tab, hide the legend in the Legend tab and then click Next >.

   5.   In the Step 4 of Chart Wizard, click the Finish button.

   6.   Right-click one of the years along the category axis. In the popup menu select Format Axis...:

Format Axis in Excel 2003

In the Format Axis dialog box, on the Number tab, change the field Format Code (in example to show only month and year) and click the OK button:

Format Code in Excel 2003

   7.   Right-click in the chart line. In the popup menu select Format Data Series...

   8.   In the Format Data Series dialog box, on the Patterns tab, click the Smoothed line checkbox:

Format Data Series in Excel 2003

   9.   Click the Y Error Bars tab in the Format Data Series dialog box to display the error bars options.

  • In the Display group the Plus option - to show the actual data point value plus a specific error amount;
  • In the Error Amount group, the Custom: option, and then in the Specify Value use values in a worksheet range that you specify as error amounts (in this example D4:D12):
    Y Error Bars in Excel 2003

   10.   Right-click in the error bars. In the popup menu select Format Error Bars...

popup in Excel 2003

   11.   In the Format Error Bars dialog box, on the Patterns tab, select the error bars options:

Format Error Bars in Excel 2003

You can then make any other adjustments to get the look you desire.

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.