Wednesday, December 7, 2011

Custom Auto Fill in Excel

Custom auto fill is a function used to auto fill custom text, name, number etc.. It is particularly useful when you have to type the same text again and again which is very irritating. This trick will help you save time and make you more efficient at work.

Here is what we are going to do today.
  1. Look at the basic auto fill function in excel.
  2. Creating custom list of auto fill in excel (i.e. user defined auto fill).
Basic Auto Fill Function
This example will give you an idea of auto fill function.
  1.  Type "1" in cell A1.
  2.  Press Ctrl + Enter on keyboard.
  3.  Move your mouse pointer to the bottom right corner of the handler till it changes into a black " + "sign.
  4.  Hold Ctrl and then click and drag downwards. You will notice that the column is filled with ascending    number.

In a similar way you can auto fill name of months and days.The only thing you have to remember in case of months and is that you don't need to hold control. Simply type the first month and drag along the direction in which you want months to appear. You can either drag horizontally of vertically depending on your requirement. It works in both the directions.

This function is particularly useful in case of formulas. Suppose you have formula in C1 for adding values in column A and column B. See image below for description.

Type in the formula "=A1+B1" in cell C1.
Press Ctrl + Enter on the keyboard.
Move your pointer to bottom right corner till it turns into plus sign and then click and drag to fill the sum of column A and column B.

Custom Auto Fill Function.
To make custom auto fill possible, we must first define the custom fill i.e what we want to fill. Instruction to do this is below.
Click on Office button at the top of excel 2007 sheet.
Click Excel option at the bottom right corner of the office button window.
Click Popular.

Click on Edit Custom Lists... button
Type the custom list in List entries separated by comma.
Click Add and then OK.

Now go to excel sheet and type India and then click and drag. The columns or rows will auto fill with the custom values we just created. Your comments will be greatly appreciated to improve these tutorials. 

No comments:

Post a Comment

HostGator Promos