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:
- Your original sheet must be saved in the format that supports charts (.XLS for example)
- You still need to keep the destination file open to keep the connections "live"