Creating auto-updating tables

The last thing I mentioned on my post about printing was creating automatically updating duplicates of your tables.

Sometimes, when I’m working with a lot of data and doing a lot of synthesizing, I want to make a table on the same worksheet at the data I’m synthesizing from. It makes formulas easier to make and to read, for one thing, and those useful little borders around cells that you’ve used in your formula are only useful if you can see them when you’re editing it.

However, when it comes to printing, I might want to print that table on the same page as some other tables, all of which were made on the same worksheets as their relevant data.

I could just copy the tables and paste them as values and as formats in another sheet. (If I just pasted them normally and I had any references that weren’t named variables then the results would be guaranteed to be messed up because they’d no longer be pulling data from the correct cells.) But if I do that, then I’ll have to do it every single time I’m ready to print after the data’s been changed.

A better solution is to create tables that are automatically updated every time the data in the orignal table changes. Fortunately this is really straightforward and easy to do, once you think about it.

  1. Insert/create a new worksheet, if necessary. Instructions are here. I recommend naming the worksheet soemthing like “ToPrint”.
  2. Select and copy the original table.
  3. Go to the new worksheet.
  4. Select where you want the table to be. You don’t have to select all the rows and columns that will be in the table — just the one that will be in the upper left corner is sufficient.
  5. Paste special as formats. I haven’t explicitely discussed how to do this, but it’s in the same menu as paste special as values. You should now have a blank area with whatever borders and shading you had in the original table. You’ll also have the text formatting, but it won’t be visible since there’s no text.
  6. Go back to/select the upper left corner of the blank table. Actually this can be any cell in the table, but I recommend that it be one of the corners.
  7. Enter a formula to make it equal the corresponding cell in the original table. For example: if your worksheets are ToPrint and OriginalData, and both tables start in the first row & first column, then in A1 of ToPrint you’d enter the formula “=OriginalData!A1” (without quotes). You can do this by simply typing = in the cell and then (without hitting tab or enter) clicking on the “OriginalData” worksheet, then on the cell A1 in that worksheet. Then you can hit enter or tab to tell the program you’re done editing the formula in that cell. If the cell you’re referring to doesn’t have any data in it, then you might see a zero instead of a blank area. That’s okay — we’ll fix it later.
  8. Copy the cell with the formula. Just select that cell and hit ctrl-c, or however you prefer to copy. Do not go up to the formula bar and highlight/copy the formula there. If you copy the formula itself, rather than copying the cell with the formula, then after you do the next step you’d end up with a table wherein every entry is equal to the first cell of the original table.
  9. Select the entire area where you want the auto-updating table to be. This is why you start by pasting the formats, rather than finishing with that step — with the formats already there, you can easily see where the edges of the table are.
  10. Paste special as formulas. Again, this option will be in the same menu as paste special as values. If you use regular paste than you’ll be overwriting the formats of the other cells.
  11. Delete any formulas in cells that are supposed to be blank. Sometimes I’ll have a blank cell in the upper left, with column headers to its right and row headers below it. But if you make one cell equal to an empty cell (one with nothing in it, as opposed to one with =””) a lot of programs will give you a zero (0). So you need to get rid of cells like that. You could avoid this step by only doing step 10 into cells that are supposed to have data. However, I find it easier to paste once and remove the excess than to paste once over part of a table, then again over a different part.

That’s it! Now, every time your data changes, this table will change as well, and it’ll stay on a clean sheet that’s easy to print.