How to use auto fill in Excel should be a priority to you as an Excel professional.
Welcome back, you are doing great by following each lesson patiently, Thumps up to you! In this lesson we would learn the auto fill option in excel.
Rather than typing a lot of repetitive data, why not let excel do it for you. If you follow up from our previous lesson on how to use copying and pasting techniques in excel, you will have a worksheet just like this, right? That’s correct.
Instead of typing the regions from ‘January-June’, excel has a great feature called the auto fill feature, excel will just perform the task for you, but with a little bit of help from you, delete the cell range from ‘January-June’, type January exactly as you see in the worksheet below and stay put in that cell using the;
- Control (Ctrl)+Enter: fill selected range with current entry
If you take a close look at the cell below containing the word ‘January’, you will notice in the lower right corner of the cell is little square dot. Normally your mouse pointer while working in Excel is little big + sign, but if you put your mouse pointer on that little square dot, the mouse pointer turns to a cross sign. That’s what is called the “auto fill” handle in excel.
When you get that, make sure it is that cross sign, it can never be another mouse pointer, now drag to the right and it fills the months for you. Well, that’s pretty cool right?
You can also use the auto fill handle in excel to auto fill the days of the week, from Monday to Friday, rather than typing it.
There is a lot you can do with the auto fill handle in excel, let’s say, you wanted to type ‘January- December’ but this time around abbreviated ‘Jan-Dec’, well, I’m happy to tell you, excel can do that for you, all you have to do is type ‘Jan’ in the first cell and drag to the right and it auto fills for you, amazing right?
The auto fill works down the column also, not only across the row, when you type in a cell and select the little square dot and you get the cross arrow, just drag down the column, excel automatically fill in the series for you down the column.
Also, take note at the end of a series, excel automatically repeats same series from beginning, for example, assuming you want to auto fill days of the week from ‘Sunday-Saturday’, once it get to the last day of the week, excel automatically begins the series again for you as long as you keep dragging the cursor down the column, so you might need to be a little careful here.
Apart from text, excel allows auto fill for numbers, but numbers are a little bit different, I would explain with illustrations below, read carefully.
Column ‘E’ above auto filled with number 1 downward is possible, first type 1 in the first cell in column “E”, stay put in the cell by hitting the Control key + Enter.
Locate the square dot, left click on it, keep holding the left click button and drag down to your desired cell.
Filling numbers incrementally is one of excel powerful features, this time around, fill column ‘F’ just as above, but, before dragging the square dot downward, make sure to hold down the Control key on your keyboard while dragging down, excel fills in the numbers incrementally for you.
What if you want to create your own series, like the series in column ‘H’ above, all you have to do is fill the first two columns with number 10 and 20 respectively, select the two columns together by hovering the mouse over them, when you have multiple cell selected, they share a common auto fill handle, locate and drag the square dot downward to your desired cell and viola, excel do the filling for you.
This is all possible because months, dates, numbers are built in series in excel, but won’t it be nice? if you could create your own pre-made series. It could be series of your vendors, client or people you work with, it could be anything.
Let’s try to create a list of regions our company operate, that’s called custom list, here is how to go about it,
- File menu – Excel Options – Advanced – General – Edit Custom lists
By now, you should know how to navigate the backstage view from previous lessons. In the advanced section, scroll down under the general tab, click on edit custom lists, a custom list dialogue box should pop up,
In the list entries box, you can type in your preferred series, don’t forget to separate list entries by pressing the Enter key, then click on add button to add to your custom lists on the left-hand side and make sure to click Ok below the dialogue box when you are done.
Anywhere you want on a worksheet, type in one of the regions in the custom list into a cell, drag the square dot button downward and excel fills in the rest for you.
Note, you don’t have to start at the beginning of the list, you can absolutely start from any name of the regions on the list and it will still fill in the list for you. The auto fill handle works filling across the rows as well as below the rows
So, the auto fill handle is a great feature, you should allow excel do a lot of the typing for you.