When filling out spreadsheets, Excel autofill features are the most efficient way to save time. Most people don't realize that they can automate many things they do manually on 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 in a sheet. This article will show you five 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 on 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).
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? For example, what if you only want every other cell to auto-populate and concatenate the first and last name, but you want to leave the address lines untouched?
How to Auto Populate Every Other Cell in Excel
You can do this by slightly changing your autofill procedure. Instead of clicking on the first cell and then dragging it 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.
Excel no longer automatically fills every single cell based on the first cell, but now only fills 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 in Microsoft Excel
One thing that people often come across when working on Excel worksheets in a corporate environment is dealing with massive worksheets.
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 long time.
There is a quick trick to making this more efficient. Here's how to auto-populate large areas in Excel. 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.
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.
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).
When you press Ctrl + Enter, it'll apply the same formula to all of the other blank cells. Cleanup of a messy spreadsheet can be quite quick and easy with this feature.
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 and check for a blank cell. 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:
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 starting column and row, it'll fill in all of the gaps in that column without touching the existing data.
It's essentially auto-filling the column while leaving 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.
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 numbers. 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 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. It's especially important when you're dealing with spreadsheets with thousands of columns or rows. After a little patience and practice, you will effortlessly be able to autofill formulas in Excel.
Comments
Post a Comment