What Is Fill Handle In Excel?
The Fill Handle is an Excel feature that enables us to bypass the copy and paste of each value into cells and uses patterns instead of filling out the information. This tiny cross is a versatile tool in the Excel suite to perform data entry, transformation, and many other applications. It is an inbuilt feature of Excel and does not require any manual steps to enable it.
Table of contents
- What Is Fill Handle In Excel?
- Excel Fill Handle To Make Your Data Entry Easier
- #1 – Quickly Copy And Paste Data Using Fill Handle
- #2 – Quickly Enter A Numbered List Into Excel
- #3 – Enter Days Of The Month & Months of The Year Automatically
- #4 – Entering A Patterned List With Items Which Need To Be Differentiated
- #5 – Copy A Formula Using Fill Handle
- #6 – Separating values Using Fill Handle
- How To Use Excel Fill Handle From The Keyboard?
- Display or Hide Fill Handle in Excel 2007, 2010, 2013, 2016
- How to Turn on Automatic Workbook Calculation?
- Things To Remember
- Recommended Articles
- Excel Fill Handle To Make Your Data Entry Easier
Excel Fill Handle To Make Your Data Entry Easier
The basic idea of using the fill handle is to select the initial value that needs to be pasted or referenced, dragging it down or across the excel sheet by holding the right mouse button and releasing the button at the end of our data.
As we drag the fill handle over the sheet, Excel displays the value that would be auto-filled in the cell we are hovering on. It looks like this:
We can perform multiple operations on the filled data using the Excel fill handle, as shown below:
#1 – Quickly Copy And Paste Data Using Fill Handle
We can use the fill handle to quickly copy and paste data into adjacent cells by dragging the fill handle to the cells we want to fill with the same data.
#2 – Quickly Enter A Numbered List Into Excel
We can also use this feature to create a numbered list in excel by establishing a pattern for the first few cells and dragging the fill handle to the cell you want to be numbered. Remember that we have to select more than one cell for this feature to work:
#3 – Enter Days Of The Month & Months of The Year Automatically
We want to create a calendar in excelCreate A Calendar In ExcelA valuable function in Excel is the ability to insert a calendar. A calendar can be populated with any text, memo, or note. When manually entering data, this saves time. of activities for a certain number of days. First, we fill in the initial date in the first cell. Then, we want to start the dates and drag the fill handle down to the cell where we want the dates to end.
We can also use this to insert months of the year by changing the date format or entering the first few months and dragging the fill handle to the required cell.
#4 – Entering A Patterned List With Items Which Need To Be Differentiated
We can use this feature to draw up various lists using patterns. We have to establish a pattern in the first few cells that need to be repeated and drag the fill handle until the cell we want the pattern to end.
#5 – Copy A Formula Using Fill Handle
We can use this feature also to copy a formula to adjacent cells by following a similar process to the one mentioned in our other examples. We have to create the excel formulaCreate The Excel FormulaThe excel formula cheat sheet allows the user to perform quick actions and use shortcuts. For example, LEFT (string, no of characters) returns the specified number of characters from the string's left side, whereas CONCATENATE (String1, String2,..., StringN) merges two given strings to form one. in one cell and drag the fill handle till the required cell or cells as the case may be.
#6 – Separating values Using Fill Handle
We can use it to separate values using different separators like commas, full stops, etc. Here is how it works, we take a value and perform the separation for the first cell manually, and then we use the fill handle to flash-fill the rest of the list:
As we can see from the above examples, the fill handle works for both rows and columnsRows And ColumnsA cell is the intersection of rows and columns. Rows and columns make the software that is called excel. The area of excel worksheet is divided into rows and columns and at any point in time, if we want to refer a particular location of this area, we need to refer a cell. and is a fast and powerful way to establish a data structure quickly.
How To Use Excel Fill Handle From The Keyboard?
- We can use Ctrl+D to fill the cells below the selected cell.
- We can use Ctrl+R for filling cells to the right.
Display or Hide Fill Handle in Excel 2007, 2010, 2013, 2016
The fill handle is sometimes hidden. For displaying it, the following steps need to be completed in sequence:
- First, we must go to the “File” menu and click on “Options.”
- Then, select the “Advanced” or “Alt+FTA” option. Under this option, select “Enable fill handle and cell drag-and dropbox.”
The formulas may not work with the fill handle when automatic workbook calculation is not enabled. So let us see how to turn this on.
How to Turn on Automatic Workbook Calculation?
Step 1 – First, we must go to the “File” menu and click on “Options.”
Step 2 – Then, we must select “Formulas.” Then, under “Calculation options,” choose “Automatic.”
Things To Remember
- It iIt is very important to check the cell references when using the fill handle. We have two types of cell referencesTypes Of Cell ReferencesCell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1., relative reference and absolute reference.
- Relative references in excel are used when we want the references to change incrementally while using the fill handle. For example, if we use =sum(A1,B1) and the fill handle, the next cell below this would be =sum(A2,B2), and so on.
- Suppose we do not want the references to change. In that case, we use absolute references by adding a dollar sign ($) in the first cell reference like this =sum($A$1,B1) so that the cell below that would return the value =sum(A1,B2) instead of =sum(A2,B2) as is the case with relative references. The dollar sign instructs Excel to keep pointing to A1 as we keep filling subsequent cells.
- It is impressive how fast we can work on data using this feature, saving a huge amount of time and effort in excel, and making us more productive and efficient.
This article has been a guide to Fill Handle in Excel. Here we discuss how to use the fill handle to make your data entry easier and display or hide the fill handle along with Excel examples and downloadable templates. You may learn more about Excel from the following articles: –
- Flash Fill ExcelFlash Fill ExcelAutomatic fillers in the cells of an excel table are known as flash fills. When you type certain characters in the adjacent cells, the suggested data appears automatically. It can be found under the data tab's data tools section or by pressing CTRL + E on the keyboard.
- Top 20 Excel Shortcuts
- Subscript in Excel
- Watch Window in Excel