in Excel (2007 or newer) is there an easy way to apply any Excel formula on a set of datapoints before drawing a line/box/... on the chart, without using a helper column to do so?

(This is a generalization of the more specific question how to add an average line to the graph in excel 2007 or 2010. I actually also just want to show an average line, but I am interested in the general solution)

  • General answer is probably yes. Consider using named ranges to perform your calculations and then plotting the named range directly. If you have some data or more specifics it would be easier to show a direct answer.
    – dav
    Jun 3 '12 at 3:36
  • @David: AS I understand it, using named ranges is the same as using a helper column, which is exactly what i want to avoid. Jun 3 '12 at 8:43
  • That's not my understanding. I only know of three ways to apply a formula to data points: helper column, named ranges and VBA. Good luck with finding a solution.
    – dav
    Jun 3 '12 at 12:41

Dav is correct. You can add named ranges that perform functions. Named ranges don't have to refer to actual ranges on your worksheet. For instance:


The redline is actual data. The blue line is a named range:

Named Range

That range performs a (very simple) function on a range. Its result is an array the same size as the source range. If you do something like SUM(Range) it returns a single value and, thus, would only chart a single value. So long as you stick to functions that return arrays, it should work fine. I don't know what kind of operation you want to do, though, so it's possible that it won't work for whatever reason.

The only other oddity is when you go to add the series to the chart. You have to add the workbook name if it's a workbook-level name or the sheet name if it's local. That should update as you change workbook / worksheet names, though, so you can set it and forget it. However, if you ever copy that chart to some other file, you might be confused about why it's showing the wrong data until you remember that reference.

Series Definition

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.