5 Excel Autofill Tricks to Build Your Spreadsheets Faster
Pinterest Stumbleupon Whatsapp
Advertisement

Excel autofill features offer the most effective ways to save time while filling out spreadsheets.

Most people don’t realize that a lot of the things they do manually can be automated. 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 in a sheet.

In this article, we’ll show you how to accomplish five of the most effective automations for autofilling columns.

1. Fill in Every Other Cell

Mostly anyone who has used Excel for some time knows how to use the autofill feature.

You simply click and hold your mouse on the lower right corner of the cell, and drag it down to apply the formula in that cell to every cell beneath it.

excel autofill tricks

In the case where the 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 autofill formula to every single cell below it? For example, what if you only want every other cell to concatenate the first and last name, but you want to leave the address lines untouched?

Apply Formula to Every Other Cell

You can do this by slightly changing your autofill procedure. Instead of clicking on the first cell and then dragging down from the lower right corner, you’re going to highlight the first two cells instead. Then, place the mouse at the lower right corner of the two cells until the cursor changes to a “+”.

excel autofill tricks

Now hold and drag that down just as you would normally.

excel autofill tricks

You’ll notice that now instead of autofilling every single cell, Excel only autofills every second cell in every block.

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 tricks

You can just imagine how this flexibility could greatly enhance how efficiently you are able to automatically fill in data in sheets. It’s one of the many ways Excel helps you save time 14 Tips to Save Time in Microsoft Excel 14 Tips to Save Time in Microsoft Excel If Microsoft Excel has consumed a lot of your time in the past, let us show you how to get some of it back. These simple tips are easy to remember. Read More while dealing with a lot of data.

2. Autofill to End-of-Data

One thing that people often come across when working on Excel worksheets in a corporate environment is dealing with massive sheets.

It’s easy enough to drag the mouse cursor from the top to the bottom of a set of 100 to 200 rows in order to autofill 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 very long time.

There is a very quick trick to making this more efficient. Instead of dragging all the way down the column, just hold down the shift key on the keyboard. 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 tricks

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 tricks

This one trick can save countless hours 10 Easy Excel Timesavers You Might Have Forgotten 10 Easy Excel Timesavers You Might Have Forgotten These ten tips are sure to minimize your time performing menial Excel tasks and boost your spreadsheet productivity. Read More wasted trying the 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 3 Crazy Excel Formulas That Do Amazing Things 3 Crazy Excel Formulas That Do Amazing Things The power of Microsoft Excel lies in its formulas. Let me show you the wonders you can do with formulas and conditional formatting in three useful examples. Read More 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 cell 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. Just select all the data in the column. Then go to the Home menu, select the Find & Select icon, select Go To Special.

excel autofill tricks

In the next window, select Blanks.

excel autofill tricks

On 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 autofill tricks

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), and when you press Ctrl + Enter, it’ll apply the same formula to all of the other blank cells.

This one feature can make cleaning up a messy spreadsheet very fast and easy.

4. Fill With Previous Value Macro

That last trick actually takes a few steps. You need to click 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, check for a blank cell and if blank it’ll copy the value or formula from the cell above it.

To create the macro, click on the Developer menu item, and click the Macros icon.

excel autofill tricks

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 autofill tricks

You want to fill in the gaps in column G by filling in the blank cell with the same formula as the cell above it, you can just run your macro. After you answer the prompts for starting column and row, it’ll fill in all of the gaps in that column without touching the existing data.

excel autofill tricks

It’s essentially an autofilling the column while leaving existing data alone. This isn’t something that’s 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

An iterative calculation is a calculation performed based on the results of the previous row.

For example, the next month’s company profit may depend on the previous month’s profit. In that case, you need to incorporate the value from the previous cell into the calculation that incorporates data from throughout the sheet or the 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

In this script, the user provides both the first and the last row number. Since there’s no data in the rest of the column, the script has no idea where to start. Once the script is provided the range, it’ll then do an interactive calculation using the previous value and will fill in the entire column with the new data.

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 include the previous cell in the formula. Then, when you auto-fill that column, it’ll incorporate the previous value in the same way.

The benefit of using a macro is that if you wanted to you could add some advanced logic to the interactive calculation, which you couldn’t do inside a simple cell formula.

You can learn more about this in our article on automating your spreadsheets 5 Resources for Excel Macros to Automate Your Spreadsheets 5 Resources for Excel Macros to Automate Your Spreadsheets Searching for Excel macros? Here are five sites that have got what you're looking for. Read More with Excel macros.

Autofilling Excel Columns Is a Breeze

As you can see, the approach you take when you’re autofilling columns can really reduce your workload. This is especially true when you’re dealing with massive spreadsheets with thousands of columns or rows.

Enjoyed this article? Stay informed by joining our newsletter!

Enter your Email

Leave a Reply

Your email address will not be published. Required fields are marked *

  1. Life Ngoma
    March 1, 2018 at 1:07 pm

    This useful useful useful. Thank you very much.

  2. Talha
    February 27, 2018 at 2:41 am

    For autofill to end of data u can also simply double click your left mouse button when the cursor changes to a "+" at lower right corner of the cell. There is no need to hold down the shift key.

    I haven't tried the shift key combo maybe its a different function or has a slightly different application. Will try to today to find out.