You are here: Home / Archives for Autofill

How to perform autofill without dragging the fill handle

July 26, 2008 By

If you have a large worksheet with formulas and you want to perform an autofill, then it may be a tedious job to drag the fill handle across hundreds of rows or columns. Here is a simple procedure to make your life easier:

1. Select the first cell in your range (the one which has a formula already).
2. While holding down the shift key go to the last cell in the range and select it. Now you should have all your range selected.
3.In the Edit menu click Fill then Series.

Fill Series 1

4. When you get the Series window click Autofill.

Fill Series 2

All cells in the range should now be updated with formulas.

Filed Under: Data, Formulas Tagged With: , ,

Autofill, formulas and cells references

April 24, 2008 By

In a previous post I explained How to use Autofill with formulas.Today I will cover the same subject but from a different perspective.

Suppose you have a list of values in a certain currency and you want to convert those values to another currency based on a given currency rate. If we follow the same scenario as in the previous post then it will go like this.

  1. Enter the formula in the first row of data as follows:

Formula Cell refrence 1

  1. Drag the fill handle across the rest of the rows to generate the formulas.

Formula Cell refrence 2

  1. Your data will now look like this.

Formula Cell refrence 3

What is wrong?

The cell reference style we are using here e.g. A3, B1 is called Relative Cell Reference. When you perform autofill downwards with relative cell reference, Excel will increase the row number by 1 successively e.g. A1, A2, A3 or B1, B2, B3 … etc.

In our case we want cell B1, which contains the rate, to be fixed. In this case we use the Absolute Cell Reference which means whatever cell reference you specify will remain fixed when you use autofill. The B1 cell will be coded like this: $B$1.

Now back to our example. This is how the formulas look after modification. $B$1 is the same in all rows.

Formula Cell refrence 4

And this is how our values will look like.

Formula Cell refrence 5

Related Links:

How to use Autofill with formulas


Applies to: Excel 2003

Filed Under: Data, Formulas Tagged With: , , , ,

How to use Autofill with formulas

April 20, 2008 By

The Autofill feature of Excel can be applied on formulas as well as predefined lists. Let us take a simple example:

Suppose you have two columns of Quantity and Price and you want to derive a third column of Value by multiplying the quantity by the Price, then you:

  1. Enter the formula in the first row of data as follows:

Autofill Formula 1

  1. Drag the fill handle Autofill Handle across the rest of the rows to generate the formulas.

Autofill Formula 2

  1. Your data will now look like this.

Autofill Formula 3

Filed Under: Data, Formulas Tagged With: , , ,

How to create a custom list to use in Autofill

April 2, 2008 By
  1. In Excel menu click Tools.
  2. In the Tools menu click Options.
  3. Click the Custom Lists tab.
  4. Click the Add button. The cursor will move the List entries edit box.
  5. Enter the data for your list separated by commas. E.g. East, West, North, South.

 

Now if you enter “East” in any cell and drag the fill handle, other cells will automatically be filled with West, North … etc.

Applies to: Excel 2003

 

Filed Under: Data, Lists Tagged With: , , , ,

Automatically fill data in adjacent cells (Autofill)

March 31, 2008 By

You can save yourself allot of typing if you know how to make use of autofill feature in Excel.

Suppose you want to enter names of days in cells A1 to A7 (Sun, Mon, …. Sat). Here is what you do:

  1. Enter “Sun” on cell “A1”.
  2. Drag the fill handle across the cells that you want to fill (A2 to A7).

You can also use Autofill to enter your own series of numbers. For example you can fill cells with the numbers 5,10,15,20 … etc as follows:

  1. Select the first cell in the range that you want to fill.
  2. Type the starting value for the series (5 in our example).
  3. Type a value in the next cell to establish a pattern (10).
  4. Select the first two cells that contain the starting values.
  5. Drag the fill handle across the range that you want to fill.

Applies to: Excel 2003

Filed Under: Data, Lists Tagged With: , , , ,