Excel Article

Excel Tables (Part 6) - Preserving Entry Order

In this short article we will look at preserving the entry order of a Excel Table.

Firstly a confession. I have struggled to think of a good short name for this column, and although I use "Id" - short for Index - it is possibly a misnomer. However the name is not so important, keeping it short means the column width can be kept narrow.

Excel Tables have this great feature that they are very easy to filter and sort the data in the table. But there are times after doing a sequence of sort operations when you may want to get back to the original order.

As an example, imagine you are manually copying items from your bank statement into an Excel Table. You want your table to be in the same order as the statement to ease checking. However sorting on the date column will not always get you back to the right order if you have several entries all on the same date.

The solution is quite simple. Add a column to your table. When you have entered your data into, autofill the column with an ascending sequence of numbers as below.

Excel Table with filled Id column

Now whenever you need to get back to the original order, simply sort on this column.

Even if you find you are inserting data into the middle of the table, provided you fill the numbers from above the insert position to the bottom you will be fine.

Tips for autofilling the column

There are at least 2 ways to autofill the column with ascending numbers.

The first method is to put 1 in the first row of the column, 2 in the second row, like this

Start of autofill

Then selected the first two cells

Highlighted for autofill

Hover over the little grab handle at the bottom right of the highlighted cells, click down with the left mouse button, drag the selection down to the bottom and then release the left mouse button. This should autofill you column with ascending numbers.

The second method is better for larger tables. Firstly make sure the first cell in the Id column contains a 1

Then select the rest of the data part of the column. You can either right click any cell in the column and choose Select > Table Column Data from the context menu, or for those of you who like keystrokes using CTRL-Spacebar.

Then use the fill tool which you can find in the ribbon menu, and select Series...

Fill tool

In the dialog box, choose Series in "Columns", Type "Linear" and Step value of "1"

Series dialog

Incidently there is a third way of selecting the data part in a column. If you hover near the top of the column in the header area, the pointer should change to a down arrow. Click once and you will select the data part of the column and click again you will get the whole column. However it does require accurate mouse work because if the pointer changes to a 4 way arrow you will select the whole table.

Screen shots created in Excel 2007 and Excel 2010 where stated, all on Windows 7

Back to the top