Posts Tagged ‘excel’

How to retain original row numbers in an Excel spreadsheet

Monday, September 21st, 2009

Sometimes I receive spreadsheets that contain requirements or issue lists, and find that it’s hard to reference a specific row if I add or remove rows from the original document. For example, if I receive a spreadsheet with 25 requirements in it, and no unique identifier column, I’d use the row number to identify a specific requirement if I had to email a followup question. However, if I deleted one of the requirements because it was no longer applicable, the numbering would change; when you delete a row, all rows below it are moved upward. If I had referenced one of the rows under the row I’d deleted, that row number is now incorrect. The easiest way around this is to ensure that the spreadsheet has a unique ID column in it, and avoid using the row numbers. Use the following steps to add an ID column to a spreadsheet.

  1. Add two new columns to the far left side of the sheet. These will become the new columns “A” and “B”
  2. In the first row of the first column, enter the number 1.
  3. In the second row of the first column, enter the formula =A1+1; after typing the Enter key, the number 2 should appear.
  4. Copy the the second row of the first column, then select all the other rows in the first column, and paste
  5. You should now have a list of numbers in column A that match the row numbers; select them all, and then copy them.
  6. Use Paste Special to paste just the values (NOT the formulas) into the second column.
  7. Delete the first column. The new column A is now the original row number, and should match up with the ‘real’ line numbers. You can now add and delete rows and still be able to reference by the original row number.

The reason why you need to ‘Paste Special’ is because if you used formulas, those values would change when rows were added and deleted, and you’d have the original issue: unable to identify the original row number.

And now, a word about using spreadsheets to track defects or issues: Just don’t do it. Spreadsheets were made for calculating things, not to be a database. In fact, it makes a terrible database. If you need to track issues, or report bugs, use a real issue tracking system such as Bugzilla or Jira.

I would do the same for tracking requirements. While issue trackers usually do not do a good job tracking requirements, they do a better job than a spreadsheet.