- \n
- Using the + symbol for autofill.<\/span><\/li>\n
- Choose and drag the cells.<\/span><\/li>\n
- Click on the + symbol.<\/span><\/li>\n
- Drag the + symbol until which the cells should fill.<\/span><\/li>\n<\/ul>\n
Let’s look at a sample:<\/strong><\/p>\n
![\"Fill](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/04\/drag-the-.png\")
<\/a><\/p>\nClick on the + symbol and drag until which the autofill should work. In this sample, we are filling until cell number 11 which stops with the value of 50.<\/p>\n
![\"Drag](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/04\/5-values.png\")
<\/a><\/p>\n1. Auto-fill in Excel<\/h4>\n
Auto-fill does not work only on the whole numbers, but also for all types like:<\/span><\/p>\n
- \n
- Date<\/span><\/li>\n
- Day<\/span><\/li>\n
- Number sequences<\/span><\/li>\n<\/ul>\n
The only requirement in the auto fill is that the pattern should be recognizable.<\/span><\/p>\n
In this sample, we will look at how dates and days format in the autofill.<\/span><\/p>\n
![\"autofill](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/04\/autofill-date-day-pic1.png\")
<\/a><\/p>\nIn this, we have filled two rows for each heading i.e., the 3rd and 4th row of the spreadsheet.
\nSelect the cells from the 3rd row so that it can recognize the patterns.<\/p>\n![\"date](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/04\/date-pattern.png\")
<\/a><\/p>\nLook at the red box here. It depicts the selection of two cells in the date column i.e., A column cell.
\nAfter selecting, just drag the cells until where you wanted to auto-fill.<\/p>\n![\"date](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/04\/date-autofill.png\")
<\/a><\/p>\nHere, we are filing it until A12. It saves much of the user time.
\nLet’s look at the day and weekday sample:<\/p>\n![\"day](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/04\/day-sample.png\")
<\/a><\/p>\nHope you noticed that the cells are being selected and then just drag the cells until where you wanted to auto-fill.<\/p>\n
![\"day](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/04\/day-autofill.png\")
<\/a><\/p>\nHere, we are filling it until cell row number 12. Notice the name format in both the cells, when the user enters it as mon, tue.. then the auto fill also fills it up as such and once the user enters it as a full name like monday, tuesday.. then the auto fill automatically fills the columns as such.<\/p>\n
Other Options<\/h4>\n
Now, let’s look at the other options available in the icon beside the right edge of the cell.<\/p>\n
<\/p>\n
![\"other](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/04\/other-options-days.png\")
<\/a><\/p>\nWhen you click on the icon, you can see various other options like:<\/p>\n
1. Copy cells in Excel<\/h4>\n
It copies the value as such and pastes it repeatedly.<\/p>\n
![\"copy](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/04\/copy-cell.png\")
<\/a><\/p>\nIn this sample, we have only monday and tuesday in the cells. Just click on it and drag it until where you want.<\/p>\n
![\"copy](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/04\/copy-cells-sample.png\")
<\/a><\/p>\nOnce you drag, click on the icon and choose copy cells. It copies and pastes only the values the user has entered.<\/p>\n
2. Fill series in Excel<\/h4>\n
Fill series auto fills as per the sequence. Let’s look at a sample<\/p>\n
![\"fill](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/04\/fill-series.png\")
<\/a><\/p>\nFrom the above sample, we can understand that the cells are filled as per the sequence using the option fill series.
\nIn the B column cells, it is filled as mon, tue, wed, etc. In the C column cells, it is filled as monday, tuesday, wednesday, thursday..<\/p>\n3. Fill formatting only in Excel<\/h4>\n
In this option, it fills only the formatting but not the values.<\/p>\n
![\"format](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/04\/format-series.png\")
<\/a><\/p>\nHere, we are just going to copy the format alone from the B3 and B4 cells.<\/p>\n
![\"fill](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/04\/fill-formatting-sample.png\")
<\/a><\/p>\nFrom the above sample, we can understand that only the format in the cells are being copied and filled.<\/p>\n
4. Fill without formatting in Excel<\/h4>\n
In this option, it fills the values but it doesn’t keep up with the formatting.
\nLet\u2019s look at a sample:<\/p>\n![\"fill](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/04\/fill-without-formatting.png\")
<\/a><\/p>\nFrom the above sample, we can understand that only the values in the cells are being formatted. The borderline after every two rows is not visible in the filled cells, it\u2019s because the cells are being filled without formatting.<\/p>\n
5. Fill Days in Excel<\/h4>\n
This option is available only for the days format. When you choose this option, it fills all the days as per the order.<\/p>\n
Let’s look at a sample below:<\/p>\n
![\"fill](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/04\/fill-days.png\")
<\/a><\/p>\nFrom the above sample, we can notice that the days are just filled as per we provide. If it’s mon then it is followed by tue, if it’s monday then it’s followed by tuesday.<\/p>\n
6. Fill Weekdays in Excel<\/h4>\n
This option is also available only for the days format. It keeps filling the cells only with the weekdays which means it skips the weekends.<\/p>\n
Let’s look at a sample:<\/p>\n
![\"fill](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/04\/fill-weekdays.png\")
<\/a><\/p>\nFrom the sample, we can observe that the saturday, sunday are being skipped since we have chosen the fill weekdays option.<\/p>\n
7. Number Sequence in Excel<\/h4>\n
In auto-fill, the values in the cells are first analyzed and then auto-filled accordingly.<\/p>\n
It helps in filling the serial numbers, multiples of a number, odd numbers, even numbers, increment by 1, decrement by 1.<\/p>\n
Let\u2019s look at a sample.<\/p>\n
![\"number](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/04\/num-sequence.png\")
<\/a><\/p>\nIn this sample, we have just entered three input values for each headings. These 3 input values help the excel to understand the sequence it follows.<\/p>\n
Now, let\u2019s look at the sample once it\u2019s dragged until the required cell.<\/p>\n
![\"number](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/04\/num-seq-sample.png\")
<\/a><\/p>\nFrom the sample, we can see that-<\/span><\/p>\n
- \n
- In the first column of serial numbers, the values are just incremented by 1 as that’s the sequence for a serial number.<\/span><\/li>\n
- In the second column of Increment by 2, the values are being incremented by 2 once it’s moved to the next cell.<\/span><\/li>\n
- In the third column of Decrement by 2, the values are being decremented by 2 once it’s moved to the next cell.<\/span><\/li>\n
- In the fourth column of odd numbers, the cells are just filled with odd values.<\/span><\/li>\n
- In the fifth column of even numbers, the cells are just filled with even values.<\/span><\/li>\n
- In the sixth column of multiples of a number, the cells are just filled with multiples of 10 since its sequence follows it.<\/span><\/li>\n<\/ul>\n
8. Multiple Excel Fill Handle in Excel<\/h4>\n
It is not that the excel fill handle works only on one set at a time. It can also work on multiple cells with multiple sequences at a time.<\/p>\n
Let’s look at a sample:<\/p>\n
![\"multiple](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/04\/multiple-fill.png\")
<\/a><\/p>\nIn this sample, we will have to fill the A column cells with the serial numbers i.e., 1, 2, 3, 4, 5\u2026
\nIn the B column cells, we have to fill it with the day names such as mon, tue, wed..
\nIn the C column cells, we have to fill it with multiples of 3 like 3, 6, 9, 12, 15, 18..<\/p>\nUntil now, we used to fill it by filling one by one series. But now, we are going to select all the cells and drag it until where we want to fill the sequence.<\/p>\n
![\"Excel](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/05\/pasted-image-0-29.png\")
<\/a><\/p>\nSo, in the above sample, we have chosen all 3 columns together and dragged it until where the cells should maintain the sequence.<\/p>\n
9. Fill Handle usage in Formulas in Excel<\/h4>\n
Now, let’s look at how the fill handle helps while using it with a formula. If the cells are sequential and adjacently available, then it’s very much easy for the fill handle to perform its task.<\/p>\n
Let’s look at a sample:<\/p>\n
![\"formula](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/04\/formula-fill-handle-1.png\")
<\/a><\/p>\nIn the above sample, C3 represents the sum of A3 and B3.
\nNow without applying the formula for C4 and C5, we are going to calculate the sum.<\/p>\nIn this case, we are just going to drag the cursor until which the formula should apply.<\/p>\n
![\"multiple](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/04\/multiple-fill-sample.png\")
<\/a><\/p>\nFrom the above sample, we can observe that the C4 and C5 cells are the sum values. But this time, we didn\u2019t enter the formula for each and every cell, instead the cells are dragged from the above cell i.e., from the C3 cell. The main advantage in this is we don’t have to type the formula each time, just a drag could help us finish it.<\/p>\n
10. Custom Auto-fill in Excel<\/h4>\n
Though, we have so many extra features in auto-fill. There is also a way where we can customize the auto-fill list.
\nTo do so, follow the below steps:<\/p>\n1: Click on the File tab.<\/p>\n
![\"file](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/04\/file-tab.png\")
<\/a><\/p>\n2: Choose options from the available menu.<\/span><\/p>\n
![\"options](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/04\/options-label.png\")
<\/a><\/p>\n3: Click on the advanced options.<\/p>\n
![\"advanced](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/04\/advanced-option.png\")
<\/a><\/p>\n4: Go to edit the custom list button under the heading of general.<\/p>\n
5: Click on it.<\/p>\n
![\"custom](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/04\/custom-entry.png\")
<\/a><\/p>\nA box appears with a place to enter the new list entries.<\/p>\n
Here, in the above sample, we have updated the list with entering the list values.<\/p>\n
6: After updating the list, click on the add button.<\/p>\n
Then, that list will also be updated in the custom lists and hereafter, it can be accessed anytime.<\/p>\n
7: Finally, press ok.<\/p>\n
Note<\/strong>: You can also update the list by entering it as a cell value. After the box appears, click on the import list from the cells label. Drag the cells which all should be added in the list. Click import and press ok.<\/p>\n
![\"import](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/04\/import-custom-list.png\")
<\/a><\/p>\nIn this sample, we are importing the cells from A2 to A6 as the list that should be added to the custom lists.<\/p>\n
![\"update](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/04\/update-list.png\")
<\/a><\/p>\nHope you can observe that the list is inserted in the custom lists. Hence, we can access that list also in the auto fill.<\/p>\n
![\"autofilling](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/04\/autofilling-custom-lists-1.png\")
<\/a><\/p>\nThese two are the lists we have updated in the custom list entries. So, let’s have a look at how it auto-fills.<\/p>\n
![\"custom](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/04\/custom-autofill.png\")
<\/a><\/p>\nHope, you can observe that the values are being filled as per the sequence we provided.<\/p>\n
11. FLASH FILL in Excel<\/h4>\n
Flash fill helps in filling out the data automatically. If once the pattern is analysed then the preview will be shown in a box. It can also merge the cell values and auto fill the other cells and it can also separate a value from a cell itself.<\/p>\n
Let’s look at a sample,<\/p>\n
![\"Flash](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/04\/auto-fill-nums.png\")
<\/a><\/p>\nIn the C column cells, the value is said to be as the first 3 digit number of a number. The value is first fed to the excel sheet in the c3 cell, then once we start typing in the c4 cell, the values are automatically analysed and shown in a box.<\/p>\n
Press enter if you wanted those values in the cells.<\/p>\n
Let’s look at another sample:<\/p>\n
![\"auto](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/04\/auto-fill-names.png\")
<\/a><\/p>\nFrom the sample, we can observe that the D cells are filled by the \u2018first name – last name\u2019. The D3 cell is typed manually to make the excel sheet understand. Then, once we start typing the D4 cell, it automatically shows the list that should be entered in the cells. This is how auto-fill works.<\/p>\n
If you don’t see the box in your excel sheet, then just check whether your autofill box is checked in.<\/p>\n
To check whether Flash fill box is checked:<\/strong><\/p>\n
1: Click on the File tab.<\/p>\n
<\/a><\/p>\n2: Choose options from the available menu.<\/span><\/p>\n
- In the second column of Increment by 2, the values are being incremented by 2 once it’s moved to the next cell.<\/span><\/li>\n
- Day<\/span><\/li>\n
- Choose and drag the cells.<\/span><\/li>\n