Use AutoFill and Save Time Entering Data in Excel

As a frequent user of Excel I find that one of the most useful features is AutoFill. When preparing spreadsheets for presentations or when your organizing data to keep a sense of sanity, AutoFill will simplify that task. AutoFill will ensure data integrity and will maintain order when data series require it.

In the example below I will be compiling a list of articles in Excel. I will enter 1 row of data and use AutoFill to enter the remaining 9 rows. I will then switch from an article a day to an article everyday and introduce a pattern that Excel will also replicate. A task that could take me 15 minutes with typing or copy and paste will be done in less than 5 minutes with AutoFill. If you have any comments or questions about this feature, feel free to post them below.

Note: I used Excel 2007 when creating this tutorial.

Once you complete this how-to you will be able to use AutoFill to streamline data entry and spreadsheet formatting.

Using AutoFill

Use AutoFill in Excel

1. Select the row you wish to copy.

2. Click on the Fill Handle (the bottom right corner of the cell – your cursor should change to a + sign).

Use AutoFill in Excel

3. Drag the fill handle down to the desired number of rows.


Use AutoFill in Excel

4. Click on the AutoFill button which will open the AutoFill menu (more on these options later).

5. Click on Fill Series from the menu.

Use AutoFill in Excel

You will notice that the AutoFill feature automatically filled the series for the highlighted columns above. The Art. ID was incremented by as was the date. The other columns were copied.

Using AutoFill on Data with Patterns

Use AutoFill in Excel

6. Select the rows of patterned data or cells that you wish to use.

Note: The dates are three days apart and that one row lists Excel Related with 1k words and the other is Word Related with 2k words.

Use AutoFill in Excel

7. Click on the Fill Handle (the bottom right corner of the cell – your cursor should change to a + sign).

8. Drag the fill handle down to the desired number of rows.

Use AutoFill in Excel

Notice how Excel automatically picked up on the pattern that I started with the data. The dates are now three days apart and articles are now Word Related with 2k words and Excel related with 1k words (see highlighted rows).

The AutoFill Menu

This is one of my best friends in Excel, it’s a time saver and a very easy to use feature. The AutoFill menu will appear whenever something is pasted into a cell or you perform a fill operation as described above. The options provided are limited by the data you are manipulating…so if you are working with dates you will see the date autofill options. More information on the menu items below.

Use AutoFill in Excel

Depending on the data that you are copying you will see the following options.

  • Copy Cells – You are simply copying the source cells.
  • Fill Series – If your data contains numbers, Excel will automatically increment it based on a straight series or patterned data as described above.
  • Fill Formatting Only – This option will only paste the formatting from your selection, the data in the destination cell(s) will remain the same.
  • Fill Without Formatting – Opposite of Fill Formatting Only – only the data will pasted from the source, the destination’s formatting will remain intact.
  • Fill Days – Fills days when using dates.
  • Fill Weekdays – Fills weekdays when using dates.
  • Fill Months – Fills months when using dates.
  • Fill Years – Fills years when using dates.

 

 

  • Akshay

    I need your help! Please help me ASAP. I have three columns. All of them are names of places. The first column is a country’s name present in cell A3. The second is a state’s name present in B2 and places in the state present in cells C3-C(n). This continues till there is a second country in cell A(n), state in B(n+1) and place in C(n+2)-C(n+3+n). What I would like to do is to create a macro to automatically fill all the cells in column A with the first country’s name till it reaches the second country’s name, which when reached, should fill the remaining cells in column A with that value and so on for the whole column. This should be replicated for column B as well. Please let me know how this can be done. And please please let me know as soon as possible.