Back in 2013 I wrote post on how to add a row number to an Excel table. This post is still popular and the method still works well. But it is now 2020 and I would do it differently.

Every Excel power user should become familiar with the “Data” tab which gives access to Excel Power Query Editors. If you come from an SQL Server background you will immediately recognise it as a very user-friendly ETL application (Extract, Transform, & Load). If you don’t come from an SQL Server background then you still find it easy to learn this intuitive application.

The idea is simple; Excel Power Query Editor imports the rows from a source, does something to each row and then outputs them into a new sheet. I never clean source data with formulas now, I always process it with this ETL functionality.

Using the Data Tab to add row numbers

  1. Create your table
  2. Put the cursor in the table and click “From Table/Range” on the Data tabexcel-etl1
  3. Add an Index column in Power Query editoretl2
  4. Reorder and rename columnsetl3
  5. Close and load into a new sheetetl5

I hope that you find this introduction to Power Query Editor useful and that it becomes part of your Excel tool chain.