0

Is it possible to have more then one value in a cell and then use an X Y (Scatter) chart to represent the dots on the X and Y Axis ?

Here is what I mean:

Instead of having just one value per cell like in the image below, I want to have all the values in one cell, but the same X Y Scatter chart. Is it possible ?

enter image description here

like this :

enter image description here

7
  • 1
    I wouldn't have thought so without creating yourself a hell of a lot of work. They're called cells because they're the most basic building blocks in Excel. What you're trying to do is invent Excel organelles... Excel won't understand :) Why would you want to do this?
    – Andi Mohr
    Oct 8, 2014 at 15:24
  • 1
    I should add some information to the Excel someone else worked on already. We have a Box Plot and I want to add the outliers and they should show up on the chart. It would be much better if I could put more numbers in one cell. Post the comment as answer I will give it a +1.
    – Devid
    Oct 8, 2014 at 15:30
  • Thanks, but you may as well accept Fixer1234's answer, that's the same.
    – Andi Mohr
    Oct 8, 2014 at 15:31
  • 1
    You can easily add additional values to an existing chart. Several ways. 1. If you click on the chart, it will outline the data on which it is based. The lower right of each column outline will have a control that you can drag to include the additional data. 2. In the chart details, the data series will be defined. You can edit those to change the range.
    – fixer1234
    Oct 8, 2014 at 15:37
  • This seems like a question that might be useful to others. You wrote the question pursuing a specific solution rather than about how to solve your problem. If you want to expand the question, I'll expand the answer to make it all more comprehensive for someone else with the same issue. Suggestion: change the title to be about how to add data to a chart and minimally modify the question to suggest your approach as a possible method, with a request for any alternate solutions.
    – fixer1234
    Oct 8, 2014 at 15:44

3 Answers 3

2

You can't do that directly with the built-in charting tools. They rely on data not only in separate cells, but arranged in specific ways. If there is a particular reason why the data is in that form (like imported that way or compacted for some purpose), you would need to parse it into separate cells in a layout that the charting features can handle.

3

It is actually possible but not directly. As it was mentioned, Excel treats every cell in a very specific way, kind of like a function where you can't have more than one value produced by one input into the function.

So, what you can do is set up a data connection, where your original sheet, saved as a file, would be a source and a new sheet, saved as another file - a destination. You can save your original sheet in a file in various formats for this purpose, but i prefer CSV, so i can use it for other data mining tasks by other applications. Then you create a new Excel file, and on the task ribbon go to:

Data -> From Text -> "Select your file" -> Select "Delimited" -> Select comma (you can select more than one delimiter of course) as your delimiter -> Select "General" as your data format -> Click "Finish"

This will produce "Import data" dialog box. Select:

Check "Add this data to the Data Model", check "Table", check "Existing worksheet" and click "Properties". There i would advise unchecking "Prompt for file name on refresh", checking "Refresh every XX minutes" and "Refresh data when opening the file"

Once it is all done you get a row of values from your original sheet with each value in a separate cell. Select the whole row by clicking on its number and then click on: Insert -> Insert Scatter (X, Y) or Bubble Chart

Now, since the data connection is "live", your destination will be updated every time a change is introduced into the original file and the file is saved (well, after the X amount of minutes that you specified earlier). The chart will update itself automatically as well. The only thing you need to make sure of is that from now on, all new data you enter in the original sheet must use only the type of delimiters that you specified in the appropriate step.

Also, you can copy and paste the chart from your destination sheet\file back into the original sheet\file and the chart in the original sheet\file will update itself automatically, again when you make changes and save them. The 2 provisions are:

  1. Your original sheet must be saved in the format that supports charts (.XLS for example)
  2. You still need to keep the destination file open to keep the connections "live"
3
  • Way cool! If you read the comments on the original question, the actual purpose of the question was a way to add data to an existing chart (by adding extra data points to existing cells). This answer wouldn't be a solution for that, but it is great information, and the automatic live update could be a real benefit in some applications. Love it!
    – fixer1234
    Oct 8, 2014 at 16:28
  • 1
    @fixer1234 Ah, yeah, i see the comments now. I guess i started writing the answer before seeing the comments. But i think he can still use this approach, creating brand new chart and then pasting it back into the original Excel sheet. You can set up .XLS to .XLS data connection - no problems, maybe even better for this purpose, this way the original file can have embedded, automatically updated charts.
    – ruslaniv
    Oct 8, 2014 at 16:45
  • @RusI very good , I might try this.
    – Devid
    Oct 8, 2014 at 16:53
1

Worksheet cells are cheap compared to your time.

It may be easier to display the values in a list within one cell, but Excel can only chart data in separate cells.

So make yourself two ranges: one for containing chart-ready data, and another for eyeball-ready data. You can even use a formula to turn the separate data into a lis:

=B1&","&B2&","&B3&","&B4

etc.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .