When filling out spreadsheets, Excel's autofill features are the most efficient way to save time. Most people don't realize that they can automate many of the things they do manually in Excel.

For example, maybe you want to apply a formula to only every second or third row when you drag down to autofill. Or maybe you want to fill in all of the blanks on a sheet. This article will show you some of the most effective ways to automate column filling.

1. Autofill Every Other Cell in Excel

Anyone who has used Excel for some time knows how to use the autofill feature to autofill an Excel cell based on another.

You simply click and hold your mouse in the lower right corner of the cell, and drag it down to apply the formula in that cell to every cell beneath it (similar to copying formulas in Excel).

Excel autofill tricks

In a case where a first cell is just a number and not a formula, Excel would just automatically fill in the cells by counting upwards by one. However, what if you don't want to apply the Excel autofill formula to every single cell below it? In such cases, how do you auto-populate data in excel based on another cell?

How to Auto Populate Every Other Cell in Excel

Follow the below-given steps to auto-populate data in every other cell in Excel:

  1. Highlight the first two cells in the desired column.
  2. Further, place the mouse at the lower right corner of the two cells until the cursor changes to a '+'.
    Excel autofill spreadsheet
  3. Now hold and drag that down just as you would normally.
    excel autofill menu

Excel no longer automatically fills every single cell based on the first cell, but now only fills every second cell in every block.

Auto-populating is quite useful in many cases and can help you save some time. For example, if you only want every other cell to auto-populate and concatenate the first and last name, but want to leave the address lines untouched.

How Other Cells Are Handled

What if those second cells aren't blank? Well, in that case, Excel will apply the same rules in the second cell of the first block you highlighted to every other cell as well.

For example, if the second cell has a "1" in it, then Excel will autofill every other cell by counting up by 1.

excel autofill worksheet

You can just imagine how this flexibility could greatly enhance how efficiently you can automatically fill in data in sheets. It's one of the many ways Excel helps you save time while dealing with a lot of data. If you use Excel daily, we strongly recommend that you learn some useful Microsoft Excel formulas to help you save time and be more productive.

2. Autofill to End-of-Data in Microsoft Excel

It's quite easy to drag the mouse cursor from the top to the bottom of a set of 100 to 200 rows to auto-fill that column. But, what if there are actually 10,000 or 20,000 rows in the spreadsheet? Dragging the mouse cursor down across 20,000 rows would take a long time.

There is a quick trick to making this more efficient. Here's how to auto-populate large areas in Excel.

  1. Open the Excel worksheet and select any two cells in a column.
  2. Instead of dragging all the way down the column, just hold down the shift key on the keyboard.
  3. Now you'll notice when you place your mouse on the lower right corner of the cell, instead of a plus icon, it's an icon with two horizontal, parallel lines.
    excel autofill cell
  4. Now, all you have to do is double-click that icon, and Excel will automatically autofill the entire column, but only down to where the adjacent column actually has data.
    excel autofill details

This one trick can save countless hours wasted trying to drag the mouse down across hundreds or thousands of rows.

3. Fill in the Blanks

Imagine you've been tasked with cleaning up an Excel spreadsheet, and your boss wants you to apply a specific formula to every blank cell in a column.

You can't see any predictable pattern, so you can't use the 'every other x' autofill trick above. Plus, that approach would wipe out any existing data in the column. What can you do? Well, there's another trick you can use to fill in only blank cells with whatever you like.

excel autofill tricks

In the sheet above, your boss wants you to fill in any blank cells with the string "N/A". In a sheet with just a few rows, this would be an easy manual process. But in a sheet with thousands of rows, it would take you an entire day.

So, don't do it manually. Follow these steps to fill in the blank spaces with the required string:

  1. Open your Excel worksheet and select all the data in the column.
  2. Then go to the Home menu, select the Find & Select icon, and select Go To Special.
    excel autofill find & select
  3. In the next window, select Blanks.
    excel autofill selection
  4. Now, in the next window, you can enter the formula into the first blank cell. In this case, you'll just type N/A and then press CTRL + Enter so that the same thing applies to every blank cell found.
    Excel sheet example

If you wanted to, instead of 'N/A', you could type in a formula into the first blank cell (or click on the previous value to use the formula from the cell just above the blank one).

This method works well if you want to fill all the blank spaces in your worksheet with the same string, but what if you don't want the blank spaces at all? For this, we've already covered some useful methods for removing extra spaces in Excel.

4. Fill With Previous Value Macro

That last trick actually takes a few steps. You need to click on a bunch of menu items—and reducing clicks is what becoming more efficient is all about, right?

So let's take that last trick one step further. Let's automate it with a macro. The following macro will basically search through a column and check for a blank cell. If it's blank, it'll copy the value or formula from the cell above it. Here's how you can use a macro to get your work done with ease.

To create the macro, click on the Developer menu item, and then click on the Macros icon. If you're using the latest version of Microsoft Excel, the Macros option is under the View tab.

Excel macro feature

Name the macro and then click the Create Macro button. This will open a code editor window. Paste the following code into the new function:

FirstColumn = InputBox("Please enter the column letter.")                               
FirstRow = InputBox("Please enter the row number.")
                                        
LastRow = Range(FirstColumn & "65536").End(xlUp).Row
                                        
For i = FirstRow To LastRow
                                        
If Range(FirstColumn & i).Value = "" Then
                              
Range(FirstColumn & (i - 1)).Copy Range(FirstColumn & i)
                                        
End If
                                        
Next i

The approach in the script above is to make it flexible, so it'll let the user of the sheet specify what column and row to start on. So now, when you have a sheet that looks like this:

Excel worksheet example

Once you fill in the blank cell with the same formula as the cell above it, you can run your macro to fill in the gaps in column G.

After you answer the prompts for the starting column and row, it'll fill in all of the gaps in that column without touching the existing data.

Excel spreadsheet data

It's essentially auto-filling the column while leaving the existing data alone. This isn't easy to do by just dragging the mouse down the column, but using either the menu-driven approach described above or the macro approach outlined in this section, it's possible.

5. Iterative Calculations Macro

Iterative calculations are based on the results of previous rows. For example, the next month's company profit may depend on the previous month's profit.

In that case, you must include the previous cell's value in the calculation that incorporates data from throughout the sheet or workbook. Accomplishing this means you can't just copy and paste the cell, but instead perform the calculation based on the actual results inside the cell.

excel autofill tricks

Let's modify the previous macro to perform a new calculation based on the results from the previous cell.

FirstColumn = InputBox("Please enter the column letter.")                               
FirstRow = InputBox("Please enter the first row number.")
                                        
LastRow = InputBox("Please enter the last row number.")
                                        
For i = FirstRow To LastRow
                                        
Range(FirstColumn & i).Value = 5000 + (Range(FirstColumn & (i - 1)).Value * 0.1)
                                        
Next i

Keep in mind that this is only an alternative approach to iterative calculations. You can do the same thing by typing a formula directly into the next empty cell and including the previous cell in the formula. Then, when you auto-fill that column, it'll incorporate the previous value in the same way.

If you haven't used macros before, you can learn more about errors to avoid when making Excel macros here.

AutoFilling Excel Columns Is a Breeze

As you can see, the approach you choose when auto-filling columns can really reduce your workload. After some practice, you'll be able to effortlessly fill in formulas in Excel.

You can also become familiar with many Excel formulas and functions to quickly become an Excel master and increase your productivity while working with several worksheets.