Library Technology – Reviews, Tips, Giveaways, Freeware

How to Use the Excel 2010 Basic Fill Feature

Posted In MS office - By Jack on Friday, October 14th, 2011 With No Comments »

Microsoft Excel has had a fill feature of some kind going back several versions, and while each version did much the same as the last, you couldn’t always count on things just exactly the same way; below is the way it works in Excel 2010.

To begin, let’s start with a simple data sheet:

Next, highlight the bottom row of data and the one below it, like this:

Then, click on the Fill icon on the Home tab ribbon:

First up is the Down menu choice:

Clicking on that gives this:

Which shows that Fill then Down means to fill all of the highlighted cells with the contents of the cell above them.

Next, highlight the far right column and the empty one next to it:

Then click on Fill, then Right, to get this:

Which shows that Fill then Right means to fill all of the cells to the right of the ones highlighted with the contents of the one on the left.

Next, highlight the top row and the empty row above it, then click on Fill, then Top:

To get this:

Which means that Fill, then Up means to fill all of the cells above the ones highlighted with the contents of the cells below them.

And finally, highlight the far left column and the empty one next to it,

Then click Fill, then Left to get this:

Which of course means Fill, then Left, fills the empty cells on the left of the highlighted cells with the contents of the ones on the right.

That’s the easy stuff.

Next up, is the Series feature, to see how that works, pick an empty cell with nothing around it and type the word “January” then highlight the 11 cells to the right of it:

Then click on Fill, then Series, to get this popup menu:

Choose Autofill:

…then click on OK to get this:

This shows that the purpose of the Series Fill is to automatically finish a series of something for you; in this case, it was the months of the year. The whole point with this is to save you from having to type them all in.

In addition to months, you can also use days of the week, or even specific dates, so long as they are in a recognizable date format, e.g. 12/24/2011.

As another example, type the number 25 into a cell, then highlight several of the cells to the right of it, then click on Fill, then Series, to get the popup again, only this time, stick with the defaults of Rows and Linear, to get this:

Excel has “guessed” that you would like to create a list of numbers counting up from 25, of course you helped it guess by letting it know it was to be a series, linear (meaning numbers) and that you’d like to step up one number at a time.

If instead of incrementing the number in the automated list by one, you’d like them to increment by a bigger number type in a number other than one, for example if you start with a cell contents of 5,then type in this:

Each new cell to the right gets five added to it.

Also, if you’d like your list to grow by a certain factor, type in a number in your sheet, highlight it, then click Fill, then Series, only this time, click on Growth, and type in a growth factor:

In this example we chose to start with the number 5, then to multiply each next number by 2, which means double it, which resulted in this:

Finally, many of the types of fills we’ve shown here can be performed much quicker and easier by making use of the fill handle:

To use the fill handle, you first highlight cells you wish to use as a base example, then grab hold of the fill handle and pull it in any direction for as many cells as you want automatically filled, then let go. You should get something like this:

Also note that after you drag the fill handle and let go, a tiny menu appears:

Clicking on it causes a dropdown to appear:

You can either go with the initial results or choose one of the options from the dropdown menu.

Excel Fills are an important feature when working with spreadsheets as they can save you an enormous amount of time, so it’s worth most anyone’s time to learn to use them.


More contents in: , ,

About -