USING THE AUTO-FILL FUNCTION FOR NUMEROUS ROWS OF DATA IN MICROSOFT EXCEL
Have you ever thought about how painful and tiring it is to use the auto-fill function in Microsoft Excel for over a million rows of data by dragging the auto-fill cursor to the very last row??
Today’s blog will be about how to do the above seamlessly without having your hands pain you.
Let’s assume we have a dataset with 1,000,000 rows of data as depicted below, and we want to add columns A (first_number) and B (second_number) together to get column C (third_number) for all row entries, carry out the steps below:
1. Click on cell C2, go to the name box, also known as address bar and type the desired address range.
· To get the address range, click on a column header in the dataset and check the count of rows at the bottom of the sheet. This COUNT will be the expected end of the address range. In our case, COUNT is 1000001.
· Therefore the range to be typed in the address bar is C2:C1000001
· After typing the range above press ENTER (This will highlight all cells in the range entered)
2. Go to the formula bar and type the formula for column C, the formula will automatically be entered in cell C2. In our case, the formula to be typed is “=A2 + B2”. Before leaving the formula bar, press CTRL + ENTER instead of only ENTER.
3. After doing this, the formula will be auto-filled for all row entries.
N.B: The address bar is the name box which in our case is the bar just above column A.
