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+)
Autofill is a function in excel where excel identifies the series and fills the data automatically for us when we drag down the data, such as if a cell value 1 and another cell below has value 2 when we select both the cells and drag the cells down the visual representation is like a solid cross and the series is automatically filled which is the autofill feature of excel.
AutoFill in Excel (Table of Contents)
What is AutoFill in Excel?
Sometimes we work on the data in Excel which is repetitive. It can be a sequence of dates, a specific series of numbers or patterns. Instead of entering this type of data manually, Excel provides a very useful feature called AutoFill Feature. This saves a lot of time while working on a spreadsheet.
Excel AutoFill Option allows you to create an entire column or rows of data which are based on the values from other cells. In other words, this feature fills cells with data that follows a pattern or that are based on data in other cells.
With this facility, we can work on spreadsheets more efficiently and can quickly fill cells with a series of data.
In this article, we will cover the 5 ways of AutoFill in Excel.
- Simple Excel AutoFill Option
- AutoFill using more than one starting value
- AutoFill dates & Times in Excel
- AutoFill Text Values in Excel
- Creating Custom Lists in Excel
How to Use AutoFill in Excel?
Here we will understand the working of Excel AutoFill Option with some examples.
#1 – Simple AutoFill Option in Excel
- Enter any value in a cell.
- Select that cell. You will see that in the bottom right corner of the cell, there is a small square that’s called “Excel Fill Handle”. Refer below screenshot.
- With the help of mouse drag, this Fill Handle across the cells to be filled.
- Excel will fill the selected cells by either repeating the value in the first cell or by inserting a sequence from the first cell and second cell. See the above screenshot.
- At the end of the selected cells range in the bottom right corner, there is an Excel AutoFill Options box available (shown in the below screenshot).
- Click on this box. It shows many Excel Autofill options:
- Copy Cells – Copy the starting cell value all over the selected range.
- Fill Series – Fill the selected range with a series of values incrementing by 1.
- Fill Formatting Only – Fill the selected range with the formatting but not the values of the starting cell.
- Fill Without Formatting – Fill the selected ranges with values, not with the formatting.
- Flash Fill – Fill the selected range with the data after detecting the patterns.
#2 – AutoFill Using More Than One Starting Value
For recognizing the patterns in data, first, type the two values of your series into the first and second cells. Select both the cells and drag the Fill Handle across the range to be filled.
Excel will automatically recognise the pattern from these two cell values and fill the range of cells in continuation. By using these steps, we can fill the range of cells by increment or decrements. See the below screenshot.
Drag these two cells and it will auto-fill these value pattern across the selected cell range as shown below:
Note: If you want to fill the cell range with alternative repeated values, then fill the first two cells with the values.
Select those cells and drag the Fill Handle across the selected range and then click on the “Excel AutoFill Options”. Then select the option “Copy Cells” to repeat the starting cell values across the selected range and the final result is shown below:
#3 – AutoFill Dates & Times in Excel
We also can Autofill the dates and times in Excel. How to do this, will understand with some example:
- Type any date or time in a cell
- Drag the Fill Handle across the selected range.
- The selected range will fill in a series of dates by adding one day.
- You can change the different type of fill by clicking on Excel Autofill options box.
Note: If you drag the time across the selected range of cells, it will fill with a series of times by adding one hour. Refer below screenshot.
When we drag the time, the Excel Autofill options box has 4 same options as we discussed under the section “Same AutoFill”.
For dates, Excel has additional options under Excel Auto Fill Options along with 4 options (Copy cells, Fill series, Fill formatting only, Fill without formatting). These additional Excel Auto Fill Options are:
- Fill days – While filling the selected cells, this observes the pattern in the day.
- Fill Weekdays – Look the patterns in the day while filling the selected cells but exclude Saturdays or Sundays in the series.
- Fill Months – Look for the pattern in the month while filling the selected cells.
- Fill Years – Look for the pattern in the year while filling the selected cells.
#4 – AutoFill Text Values in Excel
The excel fill the row or column with text values by repeating the values in the selected range of cells. There are some text values which Excel recognizes as a part of the series. They are:
- Weekdays (shown in below screenshot)
- Months (shown in below screenshot)
- Rank (shown in below screenshot)
- Text & Number (shown in below screenshot)
#5 – Creating Custom Lists in Excel
For Auto Fill values Excel provides a customs facility where we can create a list of items (called custom list). Below are the steps for creating a custom list:
- Go to File.
- Click on Options.
- A dialog box will open for Excel Options. Click on Advanced in the left pane.
- Some editing options will display in the right side box. Go to General section and click on Edit Custom Lists button as shown below:
- A Custom List dialog box will open.
Under Custom Lists section in a left side box, as we can see these are system-defined lists.
Here we can create our own custom list and can use it in the same way as the System defined custom lists.
Lets’ create our own list by typing some values one by one in the right side box under List entries section and click on Add button (shown in below screenshot).
Click on OK and exit from the Excel Options dialog box.
Now Enter the first value of our own list in a cell manually and drag the Fill Handle into the selected range of cells as shown below.
It will fill with the list of items created by us.
This has been a guide to AutoFill in Excel. Here we discuss top 5 types of ways to use AutoFill Option n Excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –