Excel's auto-complete features offer the most effective ways to save time when completing spreadsheets.
Most people don't realize that many of the things they do manually can be automated. For example, you might want to apply a formula to only every second or third row when you drag down to autocomplete. Or maybe you want to fill in all the blanks on a sheet.
In this article, we'll show you how to achieve five of the most effective automations for autofill columns.
Mainly, anyone who has used Excel for a while knows how to use autocomplete.
Simply click and hold the mouse in the lower right corner of the cell, and drag down to apply the formula in that cell to every cell below it.
In the event that the first cell is just a number and not a formula, Excel will simply automatically fill in the cells by counting up by one.
However, what if you don't want to apply the autocomplete formula to every cell below it? For example, what if you only want all other cells to concatenate the first and last name, but want to leave the address lines untouched?
You can do this by slightly changing the autofill procedure. Instead of clicking the first cell and then dragging down from the bottom right corner, it's going to highlight the first two cells instead. Then hover your mouse in the lower right corner of the two cells until the cursor changes to “+”.
Now hold and drag that down as you normally would.
You'll notice that now, instead of autofilling every cell, Excel only autofills every second cell in every block.
What if those second cells are not blank? Well, in that case, Excel will apply the same rules in the second cell of the first block that you highlighted in all other cells as well. For example, if the second cell has a "1" in it, then Excel will automatically fill every other cell counting up to 1.
You can just imagine how this flexibility could greatly improve the efficiency with which you can automatically populate data on 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 been a time-consuming user in the past, let us show you how to get some of it back. These Simple tips are easy to remember. Read More
One thing people often encounter when working on Excel spreadsheets in a corporate environment is dealing with massive sheets.
It's fairly easy to drag your mouse cursor from the top to the bottom of a set of 100 to 200 rows to automatically fill that column. But what if there are actually 10,000 or 20,000 rows in the spreadsheet? Dragging the mouse cursor down 20,000 rows would take a long time.
There is a very quick trick to make this more efficient. Instead of dragging all the way down the column, just hold down the Shift key on your keyboard. Now you'll notice that when you mouse over the bottom right corner of the cell, instead of a plus icon, it's an icon with two parallel horizontal lines.
Now all you have to do is double-click that icon and Excel will automatically fill the entire column, but only as far as the adjacent column has data.
This trick can save countless hours. 10 Easy Excel Time-Savers You May Have Forgotten 10 Easy Excel Time-Savers You May Have Forgotten These ten tips are sure to minimize your time performing manual Excel tasks and increase your spreadsheet productivity. Read More
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. Here are some Excel formula productivity hacks. Read More You can't see any predictable patterns, so you can't use the "every other x" autocomplete trick above. Also, that approach would remove any existing data in the column. What can you do?
Well, there's another trick you can use to fill in blank cells with whatever you want.
On the sheet above, your boss wants you to fill in any blank cells with the string "N/A." On a sheet with only a few rows, this would be an easy manual process. But on a sheet with thousands of rows, it would take you a whole day.
So don't do it manually. It just selects all the data in the column. Then go to the House menu, select the Find and select icon, select Go to special .
In the next window, select Blanks .
In the next window, you can enter the formula in the first blank cell. In this case, you will simply write N / A and then press Ctrl + Enter so the same applies to each blank cell found.
If you'd like, instead of "N/A", you can type a formula in the first blank cell (or click the previous value to use the formula from the cell just above the blank cell), and pressing Ctrl + Enter will apply the same formula to all other blank cells.
This feature can make cleaning up a messy spreadsheet very quick and easy.
That last trick actually takes a few steps. You have to click a lot of menu items, and reducing clicks is what getting more efficient is all about.?
So let's take that last trick one step further. Let's automate it with a macro. The following macro will basically look down a column, find a blank cell, and if it's blank, copy the value or formula from the cell above it.
To create the macro, click on the Developer menu, and click the Macros icon.
Name the macro and then click Create macro button. This will open a code editor window. Paste the following code into the new function.
FirstColumn = InputBox ("Por favor ingrese la letra de la columna") FirstRow = InputBox ("Por favor ingrese el número de la fila.") LastRow = Rango (FirstColumn & "65536"). End (xlUp) .Row For i = FirstRow To LastRow If Rango (FirstColumn & i) .Value = "" Then Range (FirstColumn & (i - 1)). Copy Range (FirstColumn & i) End If If Next i The focus in the above script is to make it flexible, so it will allow the user of the sheet to specify which column and row to start at. So now when you have a sheet that looks like this:
If you want to fill in the blanks in column G, please fill in the blank cell with the same formula as above, you can just run your macro. After responding to the requests for the starting column and row, it will fill all the gaps in that column without touching any existing data.
It's essentially an autocomplete of the column while leaving the existing data alone. This is not something that is easy to do by simply dragging down the column, but using the menu-based approach described above or the macro approach described in this section, it is possible.
An iterative calculation is a calculation performed based on the results of the previous row.
For example, the company's profit for the following month may depend on the profit for the previous month. In that case, you need to append the value of the previous cell to the calculation that incorporates data from the entire sheet or workbook.
Achieving this means that you can't just copy and paste the cell, but perform the calculation based on the actual results within the cell.
Let's modify the above macro to perform a new calculation based on the results of the previous cell.
FirstColumn = InputBox ("Por favor ingrese la letra de la columna") FirstRow = InputBox ("Por favor ingrese el primer número de fila.") LastRow = InputBox ("Por favor ingrese el último número de fila.") Para i = FirstRow To LastRow Range (FirstColumn) & i) .Value = 5000 + (Rango (FirstColumn & (i - 1)). Valor * 0.1) Siguiente i In this script, the user provides the first and last row number. Since there is no data in the rest of the column, the script has no idea where to start. Once the range is provided the script will perform an interactive calculation using the old value and populate the entire column with the new data.
Note that this is just an alternate approach for 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 it auto-populates that column, it will append the old value in the same way.
The advantage of using a macro is that if you want, you can 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 Excel Macro Resources to Automate Your Spreadsheets 5 Excel Macro Resources to Automate Your Spreadsheets Looking for Excel Macros? Here are five sites that have what you're looking for. Read More
As you can see, the approach you take when autocompleting columns can really reduce your workload. This is especially true when dealing with massive spreadsheets with thousands of columns or rows.