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.
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 “+”.
Now hold and drag that down just as you would normally.
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.
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 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.
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.
This one trick can save countless hours 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 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.
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.
In the next window, select Blanks.
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.
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.
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:
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.
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.
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.
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.
0 Comments