AutoFill in Excel (Table of Contents)
- Autofill in Excel
- Autofill in Excel for Text
- Autofill in Excel for Dates
- Autofill in Excel with Alphabets
AutoFill in Excel
AutoFill in Excel is quite a time-saving command which automatically fills the value to all selected cell. To use autofill, first select the data which we need to fill down below and then drag pointer which is there at the bottom right corner to all subsequent cells. For example, if we have 3 numbers as 1, 2 and 3 and we want to fill the rest of the values automatically then select the number and then drag these using Small Square located at the bottom right corner. We will see the rest of the number followed by currently selected numbers in order.
Autofill in excel is the feature that will help you to input data automatically for few cases. Let’s see the examples.
When we want to enter multiple years continuously in a spreadsheet, we don’t need to input them manually. We just need to input the first two years, later you can drag until the required number of years.
Then drag the selection until the required number of cells. The next subsequent years will fill in that dragged cells.
We can observe I dragged about 4 cells down and the dragging is showing 1996 which is the value that will fill in the last cell. Once you release the mouse the data will fill. Below is the picture after releasing the mouse.
It is not limited to the sequence of number, but you can use this auto-fill feature dragging of even or odd numbers like below. We can observe we have 2 and 4 in the spreadsheet when we drag three cells it is showing value 10.
Once we leave the mouse after dragging, the results will be like below.
As shown above, we can perform for odd numbers.
Suppose we want to know the values of the 9th table. Fill 9 and 18 then drag until the required number of cells it will give us the values of the 9th table.
So, autofill in excel will apply for something of a similar pattern.
AutoFill in Excel for Text
This autofill feature is not only for the number but also for pattern texts like months and days.
Input January in one cell,
And drag until the required length it will automatically fill the next months once we release the mouse.
Similarly, try applying for day.
Input Monday and drag until required length it will fill next days.
Suppose you are not good at spellings and lazy to enter entire month or day just input the short form and drag. Still, the Autofill feature will work. We have seen when I just input ‘Mon’ it still gives ‘Thu’.
It will not only apply in Vertical way but can use this feature horizontally also.
Below is an example of days.
In the above example, I dragged to my right hence it is showing as ‘Tue’ if I drag to my left it will show as per that. We can find the below example which dragged from right to left. While dragging you will not find the “+” sign cursor in the left corner it will be in right corner only, we just need to click on right corner and drag to our left side. Below is the example for reference.
AutoFill in Excel for Dates
Now we will see how we can use this autofill in excel for dates. First, we will see how to automate dates, months and years. Input the required date from where you want subsequent dates. Suppose we need the dates from 1st Jan 2019 just input and drag as usual. See we have input 1-Jan-19
and dragged for 4 cells it is showing 5-Jan-2019.
Hope you have understood the concept of autofill in excel. Now we will see how to work with working days (Except Saturday and Sunday).
To fill only working days drag as usual and click on the smart tag which is marked in red color as shown in the below picture.
Once you click on the smart tag a drop down will come asking for the choice fill days, months, years and weeks etc.
From the drop-down, select the required option, now we want only weekdays select weekdays option.
It will fill all the working days avoiding Saturday and Sunday.
If you observe in the below example 5-Jan-19 is Saturday and 6-Jan-19 is Sunday.
Similarly, we can autofill in excel the years and months also by selecting a respective option. In addition to these, if we want to fill dates of required sequence like input every 5th day or 7th day, we can simply perform this with the help of auto-fill in excel. In this case, we need to auto-fill in excel two days as per required sequence then select both and drag until the required range.
The above example will give the below sequence.
AutoFill in excel with Alphabets
This feature of autofill in excel will not work with Alphabets. Try to fill A and B in the two cells subsequently, select them and try to drag it will not fill the next letters but it will only fill the same A and B in the next cells also.
If you observe the below picture it is repeating the same A and B pattern. If you give three letters It will repeat that pattern.
If you really want to fill Alphabets you can apply this. First input 65 and 66 and drag them then input a formula CHAR(Number) which will convert the number to alphabet as per ASCII code. This is not a part of autofill at all but I usually come across this problem while working on Excel and got this solution from Google.
In this way, we can fill alphabets in excel. Once you reach Z the letters will change hence you need to stop at Z and select the entire A to Z range and drag autofill feature.
Things to Remember
AutoFill in Excel is one of the features that are available in Excel. It helps to fill subsequent data automatically when we drag the selected cell corners.
We can use autofill in excel for numbers, sides (East, West, North, and South), years, dates, months and some other types of data. Smart tag helps to fill the sequence, format, months, etc.
Because of this feature, we can save time while working on a large amount of data. This AutoFill in excel is not capable to fill alphabets this is one of the drawbacks of this.
This has been a guide to AutoFill in Excel. Here we discuss the AutoFill in Excel and how to use the AutoFill in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles-