Producing a Series

Many spreadsheets include a series of values such as months of the year, days of the week or just plain numbers. Rather than type them all in laboriously, you can get Excel to automatically generate the values using the fill menu option. This would can be a boon when creating sheets containing sales data broken down month by month.

Suppose you want a series of months and years such as Jan 2002, Feb 2002 and so on. Click on the cell where you wish to begin and type in Jan 2002. Then click on the next cell and enter Feb 2002.

This is all Excel needs to know to guess how you want the values to continue. Using the mouse, select these two cells plus any the others that you wish to contain the series of dates. Now click on the Edit menu and select Fill then Series. A small dialog box will appear with some options. Excel out to have worked out the correct ones so just click on OK. The rest of the dates will then be automatically filled in.

You can use a similar method for the days of the week. Type in Monday in the first cell and Tuesday in the next. Use the mouse to select those two fields plus the rest that you wish to fill, as described above. Click on the Edit menu then select Fill/Series. When the dialog box appears, change the type to Autofill and click on OK. Excel also understands Mon, Tue etc if you prefer abbreviated forms.

Experiment with the dialog box to see what else you can achieve. The step box is used to control how big a gap there is between each value so if you entered a step of 3 you might get 1,4,7 and so on. You can also enter a stop value. When generating values, Excel will only continue  until the stop value is reached no matter big an area you initially selected for the fill. If you wanted a column of numbers from 1 to 100, enter 1 in the first column, select the entire column by clicking on the column header (where it says A, B etc) and then bring up the series dialog box. Enter a stop value of 100 and click on OK. You’ll now have the numbers 1 to 100 in that column. 

Merging Cells

Merging columns is when you make two or more adjacent cells merge in to one big cell. Useful? You bet. Merging allows you to have finer control over the layout of a sheet. Let’s look at an example where you have some sales data for the year 2002.

It would like nicer if you could get the word ‘2002’ in the middle of the Revenue and Profit columns. To do this, enter 2002 in the first cell then select the two cells you wish to merge. Now click on the merge button.

The two cells will now appear as one wide cell covering both columns and the text inside will be automatically centred as shown here. You can merge more than two cells if needed, perhaps to create a centred heading for the whole sheet. If you want to return to the original cell layout, select the cell then click on the Format menu and select Cells. A dialog box will appear. Select the Alignment tab and untick the Merge Cells check box.

Making the Layout More Attractive

Whilst spreadsheets are supposed to be functional, you can make them look attractive too. You can change fonts, colours, backgrounds and more.

To fill in the background of one or more cells, select the cells then click on the fill button as shown above. If you just click on the bucket icon it will fill the selected cells with whatever the last selected colour was, in this case Aqua. If you click on the small down arrow to the right of the bucket a box will appear from which you can select different colours.

The other icon with the Large A on it lets you change the colour of the text in one or more cells. As before, select the cells then click on the button.

You can also change the size and font of text in the same way you would with a word processor using the standard controls as shown below. You can off course also set bold, italic and underline too.

The result can be far more attractive than the usual black and white look.