Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Is it possible to plot data in excel without having to define the function of the data in an extra column?

Think of this simplified example:

There are two columns with numbers A and B. Is it possible to plot A, B and Sum(A+B) without having to create a column to pre-calculate the last one (eg: =SUM(A1:B1))?

Plot using extra column

Plot using extra column

What I want to achieve

I want to avoid extra columns (only one in this example) and have this logic directly in the graph.

Cumulative column

The above illustration is not valid, excel displays an error similar to "this function is not valid for this context" (I use Greek Excel, cannot tell you the exact English error msg)

*Note: I am aware that I can use extra/hidden columns, extra Sheets or even Sheets from external files. I don't want such thing. I want to somehow include this info directly into the graph. Also, I cannot use VbScript.

share|improve this question
    
Excel charts are designed to plot worksheet ranges. Is there a specific reason you don't want to put a formula into a worksheet range? You can put it elsewhere (a different range or even a different sheet) if you don't want to clutter up the view of the worksheet. –  Jon Peltier Jul 21 '13 at 14:42

3 Answers 3

I think the closest that you can get is a chart that shows the two lines of data combined with an area chart, as shown below.

To set the chart up, I went through the following steps:

  • Inserted a standard line chart for the two data series.

  • Copied and pasted each of the series into the line chart.

  • Changed the chart type for each of the pasted series to stacked area. This can be done from the Design tab of Table Tools (which is shown on the ribbon when the chart is selected).

  • Set the fill of the two area series to "No fill" and set the line colors of the series to match.

combination chart

share|improve this answer
    
Not what I had in mind, but does the job in this scenario –  Odys Jul 13 '13 at 11:09
    
A possibility is to create the 3 series, click the totals series, select the SERIES formula that appears in the formula bar, press F9 to convert the formula to values. You can then delete the third column. However, the totals series will no longer be linked to the cell values, so it is not the best solution. –  Andy G Jul 14 '13 at 1:37

I believe Excel 2010 doesn't allow using formula in Series Values box. Upon entering a formula in Series value for a chosen series, as you mentioned, Excel will throw an error "The formula you typed contains an error."

It would be really nice if Microsoft had such a feature available. Just as you described in your note, alternatives are additional columns, external data, VBA etc.

This should have been a comment but I don't have comment privileges yet.

share|improve this answer

Suppose your data are in A2:A10 and B2:B10 of Sheet1.

Go to the Formulas tab > Define Name, and enter a new Name, named AplusB, Refers To

=Sheet1!A2:A10+Sheet1!B2:B10

Create your chart with A and B. Go to Chart Tools > Design tab > Select Data, click Add, and in the Y Values box, enter

=Sheet1!AplusB

This adds a new series using the defined name as its Y values.

This technique is nice, but can be problematic to maintain and debug. If at all possible, use a worksheet range instead of a defined name.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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