Excel Functions Tutorials
- Excel Tools
- Excel Ribbons and Tabs
- Quick Access Toolbar in Excel (QAT)
- "Save As" Shortcut in Excel
- Accounting Number Format in Excel
- Add-Ins in excel
- Add Filter in Excel
- Advanced Filter in Excel
- Auto Filter In Excel
- Auto Format Excel
- AutoFill in Excel
- Analysis ToolPak in Excel
- ANOVA in Excel
- Border in Excel
- Checkbox in Excel
- Check Mark in Excel (? Tick Symbol)
- Combo Box in Excel and VBA
- Conditional Formatting in Excel
- Conditional Formatting with Formulas
- Conditional Formatting for Blank Cells
- Conditional Formatting Based on Another Cell Value
- Conditional Formatting in Pivot Table
- Consolidate Data in Excel
- Comma Style in Excel
- CSV vs Excel
- Data Bars in Excel
- Data Table in Excel
- Data Validation Excel
- Data Model in Excel
- Developer Tab in Excel
- Descriptive Statistics in Excel
- Dynamic Named Range in Excel
- Drawing in Excel
- Excel Fill Handle
- Excel Fill Down
- Error Bars in Excel
- Excel Forms for Data Entry
- Excel Tables
- Excel Power View
- Exponential Smoothing in Excel
- Filters in Excel
- Flash Fill in Excel
- Freeze Panes in Excel
- Freeze Columns in Excel
- Freeze Cells in Excel
- Format Painter in Excel
- Shortcut for Format Painter in Excel
- F-Test in Excel
- Goal Seek in Excel
- Gridlines in Excel
- Heat Map in Excel
- 3D Maps in Excel
- Header and Footer in Excel
- Insert Button in Excel
- Insert / Draw Line in Excel
- Insert Function in Excel
- List Box in Excel VBA
- Lock Cells in Excel
- Macros in Excel
- Enable Macros in Excel
- Merge and Center in Excel
- Merge Cells in Excel
- Merge Tables in Excel
- Name Box in Excel
- Name Range in Excel
- Null in Excel
- One Variable Data Table in Excel
- OneDrive Excel
- Protect Workbook in Excel
- Pivot Table in Excel
- Pivot Table Examples
- Pivot Table Filter
- Pivot Table Slicer
- Paste Special in Excel (With Top 10 Shortcuts)
- Quick Analysis Tools in Excel
- Radio Button in Excel
- Recording Macros in Excel
- Regression Analysis in Excel
- Scenario Manager in Excel
- Scroll Bars in Excel
- Scroll Lock in Excel
- Slicers in Excel
- Solver in Excel
- Sort by Color in Excel
- Sort by Number in Excel
- Sort Data in Excel
- Sparklines in Excel
- Spell Check in Excel
- Split Panes in Excel (Horizontally, Vertically, Cross Split)
- Status Bar in Excel
- Text to Columns in Excel
- Timeline in Excel
- Toolbar on Excel
- Track Changes in Excel
- Trend Line in Excel
- Two-Variable Data Table in Excel
- Watch Window in Excel
- Wrap Text in Excel
- XML in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tips (178+)
- VBA (162+)
- Power Bi (35+)
When we autofill data in excel and drag data down or any formula the visually represented as solid cross is known as excel fill handle, using this excel fill handle any user can fill the series with formulas numbers or data available in the cells above, this 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
- How to Use the Excel Fill Handle To Make Your Data Entry Easier?
- How To Use from Keyboard?
- How to Display or Hide?
What Is Fill Handle In Excel?
Let us suppose that we have a document from which we have to extract data and enter it into excel. This information or content goes into cells. Cells are the small rectangles, thousands of which make up one spreadsheet. They are located at the intersection of a row and a column.
The cell is named by the respective column and row for each cell. For example, the highlighted cell in the preceding image is in Column A at Row 1, so it is named cell A1.
Once we fill in the information into a cell, it can be used as a starting point for many activities using the fill handle. The fill handle in Excel is the small solid square at the bottom right corner of a highlighted cell.
When clicked, the excel fill handle will be highlighted as a black cross as shown below:
The fill handle is an Excel feature to enable us to bypass copying and pasting each value into cells and use patterns instead to fill out information. This tiny cross is a versatile tool in the excel suite to perform data entry, data transformation, and many other applications. We would be learning more about how this feature makes working with excel so much easier and also, where and when it can be used.
How To Use The Excel Fill Handle To Make Your Data Entry Easier?
The basic idea of using the fill handle is to just 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 Excel 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 The Fill Handle in Excel
We can use the fill handle to quickly copy and paste data into adjacent cells by dragging the fill handle to the cells that we want to fill with the same data.
#2 – Quickly Enter A Numbered List Into Excel
We can use the fill handle in Excel 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
Let’s say we want to create a calendar of activities for a certain number of days. We fill in the initial date in the first cell we want to start the dates from and drag the fill handle down till the cell where we want the dates to end.
This can also be used to enter months of the year by changing the date format or by 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
The fill handle can be used to draw up a variety of lists using patterns. To do this, we have to establish a pattern in the first few cells which need to be repeated and then drag fill handle till the cell we want the pattern to end it.
#5 – Copy A Formula Using Fill Handle In Excel
We can use the fill handle in Excel to copy a formula to adjacent cells by following a similar process to the one mentioned in our other examples. We just have to create the excel formula 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 in Excel
The fill handle in Excel can be used to separate values using different separators like commas, full stops etc. Here’s 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 columns and is a fast and powerful way to establish a data structure quickly.
How To Use The Excel Fill Handle From The Keyboard?
The fill handle in Excel can also be used through 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.
How to Display or Hide the Fill Handle in Excel 2007, 2010, 2013, 2016
The fill handle in excel is sometimes hidden and to display it, the following steps need to be completed in sequence:
Step 1 – On the File menu, click on Options
Step 2 – Select the Advanced or Alt+FTA option, under this option, select Allow to fill handle and cell drag-and-drop box
Formulas may not work with the fill handle when automatic workbook calculation is not enabled. Let’s see how to turn this on.
How to Turn on Automatic Workbook Calculation?
Step 1 – On the File menu, click on Options
Step 2 – Select Formulas, under Calculation Options, select Automatic
Things To Remember About The Fill Handle in Excel
It is very important to check the cell references when using the fill handle in excel. We have two types of cell references, relative reference and absolute reference.
Relative references are used when we want the references to change incrementally while using the fill handle in excel, 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.
If we do not want the references to change, then 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, making us more productive and efficient.
This 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 also how to display or hide the fill handle in Excel along with excel examples and excel templates. You may learn more about Excel from the following articles –