2

If I were to have data in excel just like the table below, and then make line charts with that data. All is well and good until I have to come back around and add rounds 11, 12, and 13 to the data. If the 'Select Data' had Minutes and Cartons set as Series they would originally go from rows 2 to 11 in their respective columns, but since I added 3 new data points, I want the chart to automatically update to graph rows 2 to 14.

How do you get excel to do that on its own, instead of me having to go into the Select Data and edit every series every time?

The table below

Improve this question
1
  • can you use Access? you could create a table in a database and then insert into the table every time you have new information, then you just create a query that pulls information from the last 10 records inserted and then Graph it, I think you can graph inside of Access, I usually use SQL Server and Reporting software though. just an idea.
    – Malachi
    Apr 30, 2014 at 19:12

2 Answers 2

Reset to default
3

Convert your data to an Excel Table Insert>Table. Then, create your Chart from the Table column. As you add data to your table (by simply typing in the next row), your Chart will automatically expand.

  • As a bonus, as you sort or filter your table, the Chart will automatically update with the Table's data.

  • As an additional bonus, you can use Excel's ability to reference Table elements in your formula's, and you can do some basic summary and formatting work.

Improve this answer
4
  • This is simpler than my answer...both work, this is definitely more elegant.
    – James
    Apr 30, 2014 at 19:08
  • @dav This is a good solution, problem is that it won't let me put that table on a different sheet than where the chart is placed. Any way around that?
    – tarheel
    Apr 30, 2014 at 19:33
  • I can't test right now, but have you tried leaving the Table stationary and moving the Chart? Since the Chart references the data location, you should be able to move it anywhere. Not sure about moving the Table (although, it seems like it should work).
    – dav
    Apr 30, 2014 at 21:00
  • There does appear to be an issue with a blank Chart that is created, and then data from the Table is added-the Chart data doesn't track changes to the Table (it retains its initial range). However, Charts created from the Table (i.e. select the Table data and then Insert>Chart) doesn't have this problem, regardless of where it moves.
    – dav
    May 1, 2014 at 11:43
2

Insert is one answer here.

Once you set the range, anything inserted into that range is taken into account.

So lets say this is where you started:

Set Up

If you then insert rows (right click, insert)into the middle of your range, like so:

Step 2

That should add to the range. Then you simply copy the data that was below to its proper place (in this case Round 9 and 10) and then add what you need to add (in this case 11 -16).

enter image description here

Improve this answer
7
  • I asked the question because I previously tried this, and this did not work.
    – tarheel
    Apr 30, 2014 at 19:27
  • What was the issue? This worked fine for me
    – James
    Apr 30, 2014 at 20:04
  • I insert and it doesn't expand the selected data.
    – tarheel
    May 5, 2014 at 17:08
  • What version of excel are you on?
    – James
    May 5, 2014 at 17:17
  • I am on MS Excel 2010 on Windows 7.
    – tarheel
    May 5, 2014 at 17:20

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.