Excel Fill Down is an option when we want to fill down or copy any data or formulas to the cells below. We can use the keyboard shortcut “CTRL + D” while copying the data and selecting the cells. Else, we can click the “Fill” button in the “Home” tab and use the option to fill it down from the list.
What is Fill Down in Excel?
In all the software, copy and paste methods are invaluable. Probably the Ctrl + C and Ctrl + V are the universal shortcut keys everyone knows. Excel is no different from other software. In Excel also, copy and paste works the same way. Apart from copying and pasting the formulas to the below cells, we can also use Excel’s option “FILL DOWN” (Ctrl + D).
Filling down the above cell value to the below cells does not necessarily require the traditional copy and paste method. Instead, we can use the option of “Fill Handle” or the Ctrl + D shortcut key.
Ctrl + D is nothing but “Fill Down.” It will fill the above cell value in Excel to the below-selected cells.
Table of contents
Excel Fill Down Shortcut
As we said, copying and pasting is the traditional method to have values in other cells. But in Excel, we can use different techniques to handle this.
Note: CTRL + D can only fill only values to below cells, not to any other cells.
For example look at the below example data.
Now, we want to add the “Total” column. But, first, let us apply the simple formula in cell C2.
Traditionally, we copy and paste the formula to the below cells to have a formula applied for all the below cells. But, we place a cursor on the right bottom end of the formula cell.
We need to double-click on the “FILL HANDLE.” As a result, it will fill the current cell formula to all the below cells.
How cool is it to fill the formula rather than copy and paste?
Instead of using “FILL HANDLE” and “Copy-Paste,” we can use the Excel “Fill Down” shortcut in excelShortcut In ExcelAn Excel shortcut is a technique of performing a manual task in a quicker way. Ctrl + D to fill down values from the above cell.
- Place a cursor on the C3 cell.
- Now, press the shortcut key Ctrl + D. We will have the relative formula from the above cell.
- In order to fill all the cells. First, select the formula cell until the last cell of the data.
- Now, press Ctrl + D. It will fill the formula for all the selected cells.
Fill Blank Cells with Above Cell Value
It is one of the toughest challenges I have faced early on. Before telling you the challenge, let me show you the data first.
In the above data, I was asked by my manager to fill in Excel the year to the remaining cells until we found the other year, i.e., I have to fill 2010 year from A3 cell to A6 cell.
It is the sample of the data, but there was a huge amount of data it needed me to fill. Therefore, I spent one extra hour than my usual time to complete the task.
However, I later learned the technique to fill these void cells with the above cell value. Therefore, follow the below steps to apply the same logic.
Step 1: We must first select all the cells in the data range.
Step 2: Now press the F5 key. As a result, we may see a “Go To” window.
Step 3: Now, we need to press on “Special.”
Step 4: In the below “Go To Special” window, select “Blanks.”
It will select all the blank cells in the chosen area.
Step 5: Now, do not move the cursor to select any cells. Rather, press equal and give a link to the above cell.
Step 6: After giving a link to the above cell, do not simply press the “Enter” key. Using different logic here will help; instead of pressing the “Enter” key, press ENTER key by holding the CTRL-key.
Consequently, it will fill the values of all the selected cells.
Since the first incident, I have not extended my shift for these silly reasons.
I have moved on from CTRL + C to CTRL + D in Excel
I recently realized the benefit of CTRL + D while working with large Excel files. I deal with 5 lakhs to 10 lakhs of rows of data every day in my daily job. Often, I require to fetch the data from one worksheet to another. I have to apply various formulas to fetch the data from different workbooks.
When I copied and pasted the formula to the cells, it usually took more than 10 minutes to complete the formula. Can you imagine what I was doing all these 10 minutes?
I hit my computer and almost begged my Excel to finish the process fast. I was frustrated that I had to wait for more than 10 minutes every time for every formula.
Later, I started using the Excel “FILL DOWN,” CTRL + D to fill the formula below cells. Then, I realized that it only took 2 minutes, not more than that.
So, I have moved on from Ctrl + C to Ctrl + D in Excel.
Things to Remember While Fill Down in Excel
- The Ctrl + D shortcut can only fill down. It neither fills to the right nor left.
- The Ctrl + Enter shortcut can fill the values to all the selected cells in the worksheet.
- The “FILL HANDLE” also fills down instead of dragging the formula.
- We must use the Ctrl + D shortcut to fill down and Ctrl + R to fill right.
This article has been a guide to Fill Down in Excel. We discussed filling blank cells with the above cell value using Excel fill-down shortcuts, practical examples, and a downloadable Excel template. You may learn more about Excel from the following articles: –
- VBA ISNULL FunctionVBA ISNULL FunctionISNULL is a logical function in VBA that determines whether or not a given reference is empty or NULL. If the reference is empty, it returns true; otherwise, it returns false.
- VBA LEN FunctionVBA LEN FunctionThe Len function is a common function for both worksheet and VBA. In both platforms, the arguments to this function are the same, which is a string. This function returns the length of a string.
- MATCH Excel FormulaMATCH Excel FormulaThe MATCH function looks for a specific value and returns its relative position in a given range of cells. The output is the first position found for the given value. Being a lookup and reference function, it works for both an exact and approximate match. For example, if the range A11:A15 consists of the numbers 2, 9, 8, 14, 32, the formula “MATCH(8,A11:A15,0)” returns 3. This is because the number 8 is at the third position.
- TRANSPOSE Function in Excel