Fill Down in Excel (Table of Contents)
What is Fill Down in Excel?
In all the software’s use of 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’s. In excel also copy & paste works the same way. Apart from copy and paste the formulas to below cells, in excel we can use the option FILL DOWN (Ctrl + D) as well.
Filling down the above cell value to below cells not necessarily requires the traditional copy and paste method. We can use the option of fill handle or Ctrl + D shortcut key.
Ctrl + D is nothing but Fill Down. It will fill down in excel the above cell value to the below-selected cells.
Excel Fill Down Shortcut
As I told Copy & Paste are the traditional method to have values in other cells. But in excel we can use the different technique to handle this.
Note: CTRL + D can only fill only values to below cells, not to any other cells.
For an example look at the below example data.
Now I want to total add the total column. Let’s apply the simple formula in cell C2.
Traditionally we use copy and paste the formula to below cells to have a formula applied for all the below cells. But place a cursor on the right bottom end of the formula cell.
Double click on the FILL HANDLE it will fill the current cell formula to all the below cells.
How cool it is to fill the formula rather than using copy & paste.
Instead of using FILL HANDLE & Copy-Paste we can use the excel fill down shortcut key Ctrl + D to fill down values from the above cell.
4.9 (1,353 ratings)
Step 1: Place a cursor on the C3 cell.
Step 2: Now press the shortcut key Ctrl + D. We will have the relative formula from the above cell.
Step 3: In order to fill all the cells. First, select the formula cell until the last cell of the data.
Step 4: Now press Ctrl + D it will fill the formula to all the selected cells.
Fill Blank Cells with Above Cell Value
This is one of the toughest challenges I have faced early on in my career. Before I tell you what was the challenge let me show you the data first.
In the above data, I was asked by my manager to fill down in excel the year to remaining cells until we find the other year i.e. I have to fill 2010 year from A3 cell to A6 cell.
This is the sample of the data but there was a huge amount of data I was needed to fill. To be very frank I spent 1 extra hour than my usual shit time to complete the task.
However, later I learned the technique to fill this void cells with the above cell value. Follow the below steps to apply the same logic.
Step 1: Select all the cells in the data range.
Step 2: Now Press F5 key. You will see Go To window.
Step 3: Now press on Special.
Step 4: In the below Go To Special window, select Blanks.
It will select all the blank cells in the selected area.
Step 5: No do not move your 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 enter. You need to use different logic here. Instead of pressing enter, press ENTER key by holding CTRL key.
It will fill the values the all the selected cells.
Wow!!! Amazing, after 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
Recently I have realized the benefit of CTRL + D while working with large excel file. In my daily job, I deal with 5 to 10 lakhs of rows of data every day. Often I require to fetch the data from one worksheet to another. I have to apply a variety of formulas to fetch the data from different workbooks.
When I copy and paste the formula to the cells, it was usually taking more than 10 minutes to complete the formula. Can you imagine what I was doing all these 10 minutes??
I was just hitting my computer and almost begging my excel to finish the process the fast. I was totally frustrated by the fact that for every formula I have to wait for more than 10 minutes every time.
Later I have started to use the Excel FILL DOWN, CTRL + D to fill the formula down to below cells. I have realized that I was taking only 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
- Ctrl + D can only fill down. Neither has it filled to the right or left.
- Ctrl + Enter will fill the values to all the selected cells in the worksheet.
- FILL Handle also fill down instead of dragging the formula.
- Ctrl + D fill down and Ctrl + R fill right.
Recommended Articles
This has been a guide to Fill Down in Excel. Here we discuss the how to Fill Blank Cells with Above Cell Value using Excel Fill down Shortcut along with practical examples and downloadable excel template. You may learn more about excel from the following articles –
Leave a Reply