Excellent Uses of Drag and Drop Option in Excel
Excel Drag and Drop are also known as “Fill Handle” is the PLUS (+) icon appears when we move mouse or cursor to the right bottom of the selected cell. Using this plus icon we can drag to the left, to the right, to the top and also to the bottom from the active cell. Also using this drag and drop option we can do many smart works in excel.
For an example look at the below image.
Examples to use Drag and Drop in Excel
Example #1 – Copy the Current Cell Value to other Cells
For example, assume you have a certain value in the A1 cell.
- Using drag and drop options now we can fill the same value to besides cells as well. Place a cursor on to the right bottom of the cell.
- Using this PLUS icon drag to the right also down to fill the same value to all the dragged cells.
- After drag and drop, we can see a small icon to the right bottom of the selected cells, click on this to see available options.
Here we have three options, “Copy Cells, Fill Formatting Only, and Fill without Formatting”. As of now from the active cell we have dragged and dropped to the range A1 to D5 and it has taken everything from the active cell (A1).
But using this option we can fill only formatting i.e. without value from an active cell, we can also fill without formatting i.e. only value from the active cell without any formatting.
- Let’s see how it looks when we select “Fill Formatting Only”.
- Now see “Fill without Formatting”.
Example #2 – Fill Serial Numbers
Using this Drag and Drop we can also insert serial numbers. For this first, we need to enter at least two consecutive numbers.
I have entered two consecutive serial numbers in cell A1 & A2. Now select these two cells and place a cursor to the right bottom of the cell to see the drag and drop symbol.
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
Using this drag and drop until the row number you want the serial number. In my case, I have dragged until row 10, so my serial numbers will be from 1 to 10.
How does this Work?
The first thing we did was we have entered two consecutive serial numbers i.e. 1 & 2. Using this sample excel identifies the increment number from one cell to another cell, in this case, increment number is by 1, so every time we drag the value to the next new cell it will increment the value by 1. Since I have dragged until the 10th row it has incremented the value by 10 times from the first cell value i.e. 1.
For example, look at the below image now.
I have entered 1 & 3 in the first two cells, now I will drag and drop until the 10th row and see what happens.
This we didn’t get consecutive serial numbers instead we got all the odd numbers from 1 to 19. This is because in the first two cells our values are 1 & 3 i.e. from the first cell to the second cell value is incremented by 2, so when we use drag and drop excel identifies the pattern as it has to increment by 2 every time it goes to the new cell.
Example #3 – Insert Serial Numbers Without Pattern
We can also insert serial numbers without entering two consecutive numbers, just enter 1 in any of the cells.
Now drag and drop until the 10th row.
Now click on the “Auto Fill Options” box.
From this choose the “Fill Series” option to get serial numbers that are incremented by 1.
So, we got serial numbers which are incremented by 1.
Example #4 – Fill to All Below Cell Once
Using Drag & Drop option we can fill the formula from one cell to all the below cells. For an example look at the below image.
- To arrive GP (Gross Profit) we need to enter the formula as Sales – COGS.
- We usually copy the formula from one cell and paste it to below cells but this time place a cursor to the right bottom of the formula cell i.e. D2 cell.
- When you see the Drag & Drop option just double click to apply the formula to below cells.
Things to Remember
- Drag and Drop often called a fill handle.
- Drag and Drop is the alternative available to copy and paste method.
- At a time we can drag and drop to single row and single column.
Recommended Articles
This has been a guide to Drag and Drop in Excel. Here we learn some excellent uses of excel Drag and Drop option also known as “Fill Handle” along with some practical examples and downloadable excel template. You may learn more about excel from the following articles –
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
