How to fill a formula into a large number of cells in Excel

2013/09/25
· by · in

I’ve been working on a project in Microsoft Access with a table with over 875000 rows – probably larger than the average Access database, but well within the limits of what Access can handle [1]. The table is indexed, and my Access forms and reports can fetch the data quickly.  However, I needed to add a field that is calculated from multiple records in the same table – a ranking of a record based on a value in a field.

Access does not have the RANK() OVER function that recent version of SQL Server has [2].  I tried writing a VBA function that calculates the rank, and then running an UPDATE query that set the rank field to the output of the function.  This produced correct results but was so slow that the query could not complete all 875000 rows in a reasonable amount of time.

I could have imported the data into SQL Server and run an UPDATE query using the RANK() OVER function.  However, I thought it would be quicker to export the data to Excel, calculate the field using an Excel function, and import the data back into Access.  This worked with just one hitch – when I write the function, how do I apply the function to all 875000 rows?  I’m used to clicking the corner of the cell and dragging down to fill a function down a worksheet.  But that would take forever with 875000 rows.

So today I learned that there is another way to fill a lot of rows with the same function in Excel [3]. The procedure is:

  • Enter the function into the first cell.
  • Highlight and copy the first cell.  Since the cell is copied, it will have animated lines around it.
  • Click in the address bar. The address bar is the little bar to the left of the function bar.  It holds the name of the current cell when you click on a cell.
  • Type the range into the address bar (e.g., A1:B875000).
  • Paste.

The formula is filled into the entire range!