Discover How To Use Fill In Excel

Fri 23rd September 2011

Discover how to use the versatile Fill feature in Excel to save you time when you need to fill text, numbers, dates and custom lists.

If you've used Excel for some time you may have discovered the Fill feature. You can use fill to copy and paste a formula over a range of cells, create a series of values depending on the cell contents, create a series of values from a built in list, or create your own custom list to Fill. This article describes how the Fill feature can be used in Excel in different ways.

Fill a formula

Suppose you have several columns of numbers and you've then summed the first column under the numbers. If you Copy the resulting formula, then highlight all the cells under the other columns, then choose Paste, the SUM formula is applied under all the other columns.

If you examine each formula you'll see that the column references have changes for each column, because Excel cell references are of course relative to where they are placed. So if you copy a formula from one column and paste it to the next column, the column references increase by one.

If you undo the copying and pasting operation so that the sum formula is again only in one column, you can do the same thing by using Excel's Fill feature. To do this ensure the cell with the formula is selected. Then hover over the bottom right of the cell where you see a small black square and the cursor becomes a black cross. Then press and hold down the mouse left button and drag to the right to include all the other columns under the numbers and then release the mouse.

You'll see that you get the same result as before. Fill lets you copy and paste from a cell in just one action. Next we'll look at using Fill in different situations, starting with filling a cell containing text.

Fill text, and text with numbers

If you type some text into a cell and then Fill the cell either across the worksheet or down the worksheet you'll see that the text just repeats. By the way you cannot Fill in both directions at once. However if you Fill several cells down the worksheet and leave the cells highlighted you can then hover the Fill point for the block of highlighted cells and fill the block across the worksheet. So in this way you can fill in both directions.

Try typing some text into an empty cell clear of the first example, but this time add a number after the text. Then fill the cell and you'll discover that the text is repeated but the number increases by one as you fill across several cells.

Fill numbers

Numbers behave a little differently when filled, compared to text. Type a number in a cell, then fill it and you'll see that the number just repeats, which is just how text behaves.

However type two numbers in successive cells, such as 1 then 2 and highlight both cells. Now carefully find the fill point for the highlighted two cells and fill in the same direction as the highlight. You'll find that the series 3,4,5 and so on is created in successive cells.

So if you Fill a single number it will just repeat. However if you choose two numbers, the Fill will determine the gap between the numbers and add this to each filled cell.

Fill a CUSTOM LIST

Once you've got the hang of Fill you'll discover that you can fill special words to create a preset series of words. These are called custom lists.

In Excel there are four of these. The lists are the short form days of the week such as Mon for Monday, the full days of the week such as Monday, the short form months of the year such as Jan and the full months of the year such as January.

To see how these work try typing in any short form day of the week, for example Wed into an empty cell and fill in downwards. You'll see you create a series of short form days of the week. Then in a different empty cell try typing in a complete day of the week, such as Thursday and fill the cell downwards. This time you'll create a series of the full named days of the week. It doesn't which day you start with.

Once you fill the series will fill in the correct sequence and then repeat for as many cells as you fill. You can also create a short form or full words of the months of the year. So if you try typing in Feb you'll Fill to Mar, Apr, May and so on. If you type February you'll Fill March, April, May and so on.

You can also combine custom series data with numbers. So if you type in Jan11 for example this will Fill to Feb11, Mar11 and so on. With careful use of Fill you can easily create calendars, schedules and similar tables with repeating or part repeating headings.

If you try typing Jan11 and Jan12, then highlight both cells, you're telling Excel what the gap is. So if you fill these highlighted cells you'll create the series Jan13, Jan14 and so on.

So Fill is a very versatile feature in Excel. You can use Fill to copy and paste formula, create lists, create custom lists in many different ways to create specialized tables, schedules and calendars. If you want to find out more about Excel's many features you might like to consider attending a training course. Many are based on practical exercises and this is a really effective way to greatly increase your Excel skills.

Author is a freelance copywriter. For more information on microsoft excel courses london, please visit https://www.stl-training.co.uk

Original article appears here:
https://www.stl-training.co.uk/article-1961-how-use-fill-in-excel.html