1

I have an Excel chart that I am plotting data in. I'd like the series name to be a string concatenated with a fixed string.

So for instance if I want to name the series as Channel 1, I would think that placing the formula ="Channel "&Sheet1!A1 in the "Series Name" box would do the trick, provided that the value 1 is in cell A1.

However, Excel tells me that my formula has an error.

shA.t
  • 16,580
  • 5
  • 54
  • 111
audiFanatic
  • 2,296
  • 8
  • 40
  • 56
  • Please check [this question](http://stackoverflow.com/q/18272728/4519059), I think it can be helpful ;). – shA.t Apr 25 '15 at 06:55
  • 1
    Any formulas that link chart data to worksheet data (X and Y values, series names, chart and axis titles) are restricted to link formulas. Any construction of data or labels must be done with formulas in worksheet cells. The chart elements then must be linked to these cells with formulas. – Jon Peltier Apr 26 '15 at 14:34

2 Answers2

1

What I would do is do this concatenation in a separate cell (I assume you don't want to change the value in Sheet1!A1) and then set the series title to reference the cell containing the concatenation. You can hide the column that this is occurring in to make it invisible to the user.

For example, in cell V2 type ="Channel "&A1" and then reference V2 in the series title in the chart with:

Series Name:

=Sheet1!$V$2

Soulfire
  • 4,218
  • 23
  • 33
0

I also wanted to have a series name refer to a concatenated string, although I started with the approach of citing a cell that already had the concatenated data within it. Here's a strange bug-like characteristic of Excel (from Office 365) I found:

When I manually try and type in a cell address (e.g., J5), after the "="in the "Series Name" field, I also receive a "there's an error in the formula you entered" error message.

Strangely, when I simply click on the cell (such as clicking on cell J5) I want to reference after the "="in the "Series Name" field, I don't receive this error message.

Sean
  • 1