I have a large table which is frequently filtered and also has hidden columns. I have 2 problems:

  1. Every day I add data between the header row and the first row of my table. The table will not let me just copy Row 1 into the new row I insert; I have to first unfilter the table, then insert a new row and then copy row 1 into the new row.
  2. Also, I have many charts referring to this very large table (my boss is always constantly filtering these charts). Every day, when I add my new table row I have to edit the Horizontal Axis on all of my charts to include the new row I added. Not only is this very time consuming, if my boss has changed the filter since the day before I have to unfilter everything to ensure I have updated all the Horizonal Axis. The really stupid things is, it used to auto update, but for some reason, it no longer does.

Many thanks in advance if someone is able to help me.

  • Are you on the latest version of Excel? (Just because you have Office 365, doesn't mean you're running the latest version. Office 365 is just a subscription model, not a piece of software.) When did the auto update stop working and did you change anything around that time (like installing updates)? – Worthwelle Nov 6 '19 at 16:09

Take a look at your defined series. Make sure the series still includes the first and last row of your data. When you insert a new row, do not select the first row. Doing so will push down your selected ranges. Instead, select the second row and insert there. You data ranges will still reference the first row and the last row will be augmented by one to match you new range.

Copy the first row to the second row, then overwrite the information in the first row.

In terms of updating your horizontal axis, you will either need do blank out the upper and lower limits so they can adjust automatically to numbers in the range.

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.