The tabs are those items that distinguish worksheets within Excel. If you are not sure what a worksheet is then this article will more than likely be more advanced than what you need right now. I recommend checking out some sites on the basics of Excel before continuing.

Being able to color code your worksheet tabs can help you further organize your entire workbook making it easier to find information when you need it most. The steps are very simple.

You can do it a couple of ways. The first way which is what I find to be the easiest is to right click with your mouse on the tab that you want to change the color of. This will bring up what is called a pop up menu. In the pop up menu that is an option called “Tab Color”. You'll never guess what that is for? Ok maybe you will.

Click on the sub menu item labeled “Tab Color”. This brings up a color palette where you can choose the color that you want. Select the color that you desire and then finally click the ok button.

At first glance it looks as if nothing has happened. However if you click off of that worksheet by left clicking a different tab, you will then see your new tab color. Just remember that a white tab always represents the active tab that you are working on.

The second way to change the tab color has a couple of more steps but the end result will still be the same. This is very typical of any Microsoft product which is the ability to do one thing in a variety of ways.

Up at the top of the Excel window you will see the main menu header. Click the header menu item that is labeled, “Format”. When you do this a sub menu appears. You will see an item in the sub menu labeled “Sheet”. Click that sub menu item. That brings up yet another sub menu. You will notice in this third menu an option that is labeled “Tab Color”. When you click on this third menu item it will bring up the color palette as it did in the previous example. Everything hereafter is identical.

You can see that the second approach adds a couple more steps but the end result is still the same. Which way you do it is completely up to you.

If you are unfamiliar with how this is done, read on and take a look at the steps I perform to achieve this task. You will see that it is very easy to do.

The first thing you want to do, obviously, is open up Excel. Once Excel is displayed on your screen, go ahead and type in cell “A1” the month name “January”. In the cell “B1” type in the month name, “February”, in “C1” type in “March” and so on until you type in all twelve months each in their own cell on the spreadsheet.

Now that we have some test dummy data on our spreadsheet we can go ahead and reformat the column widths. You can add more data if you would like or even use an already existing spreadsheet. The choice is completely up to you; however the steps to reformat the column widths are all the same as you will soon see.

The next step is to highlight the columns that you want to reformat. To reformat the columns we are using in our example, using the month names, you need to click on the column header labeled “A”. Now hold down the “shift” key and click the column header labeled “L”. In other words you are clicking the first column and the last column. If you chose to use your own spreadsheet, your columns that you use may be different. You could also click on column header “A” and holding down the left mouse button, drag the cursor over top of the final column, in this case “L”, and let go of the mouse button. Either way is fine.

Now that the columns you want reformatted to a different size have been chosen, it is time to do just that. Using your computer's mouse, right click anywhere within the highlighted area. This will bring up a pop-up menu. On that menu is the option “column width”. Click on that option. This will bring up a window asking you to enter in the width of the columns. If there is a number already in that box you will need to erase that first by highlighting it and pressing your “delete” key. Now type in the number “12” and click the “Ok” button.

You should now see that all of the highlighted columns have changed to the size of 12. Repeat the steps above to alter the sizes and find one that looks more visually appealing for the spreadsheet that you are working on.

Remember that at anytime you can undo what you just did by clicking the “Edit” menu option at the top of the screen and click the sub-menu option of “undo”.

Go ahead and open up Excel. You should be staring at a blank worksheet. The first thing we need to do is enter some data into a column. For our purposes we will use column “A” to enter our data. In rows one through ten go ahead and type some numbers in. It doesn't matter what they as long as they are not in numerical order.

You can enter in as many numbers as you would like and as large as you like it all depends on how much typing you want to do.

No click on the column header labeled “A”. You will notice that this highlights the entire column. This signifies that we will be working with all of the data in column “A”.

There are two ways you can sort the data automatically and both are correct and exactly the same. You will see at the top of the spreadsheet some menu options. They are “File”, “Edit”, “View”, “Insert” and so on. Look for the “Data” menu option and click it. Clicking the “Data” menu option will bring up a sub menu. On the sub menu option labeled “Sort”. This will bring up a window asking you for specific sorting options. The window already has the defaults set that we want so go ahead and click the “Ok” button.

The data in your column that was highlighted should now be sorted in an ascending manner or in others one through ten as opposed to descending which would be ten through one.

The second method to sorting the data would be to just click the sort button located on the toolbar. There are two sort buttons. One is displayed as the letter “A” sitting over top the letter “Z” with a down arrow, meaning to sort the data in an ascending manner and the second button is the letter “Z” sitting over top the letter “A” with a down arrow, meaning to sort the data in a descending manner.

You need to reset your data so that it is jumbled up again like it was before. To do this click on the “Edit” menu bar option then click on “Undo Sort”. You will see this unsorts the data.

Now go ahead and highlight the column of data just the same way as you did before. Click on either sort button described earlier and you will see that your data is now sorted. You will also notice that you did not get that window of options this time. By using the sort buttons you are telling Excel that you are ok with the defaults they have chosen.

So there you have it, sorting data in Microsoft Excel in its most basic explanation and definition. If you want to try more complicated data to see how it works, add in additional columns of data.

Here are the steps to achieve this process:

Type in a year in cell A1, then on row 2 type the months January through May in each of their own cells. For example, January would go in A2, February would go in B2, March in C2 and so on.

The next step you want to do is click on cell A1 where your year is, then holding down the shift key, click on the cell E1. This will highlight the cells we want to merge. You can highlight as many cell as you want, however we are choosing A1 through E1 for this example.

You then want to make sure you can see the formatting toolbar. This is important because on this toolbar is the merge button we will need to press in order to merge the cells.

To view this toolbar, click the “view” menu option located at the top of the screen, on the sub-menu item that appears click on “toolbars”. On the next sub-menu appears if there is no check mark next to “formatting”, then click the word “formatting”. This will display the formatting toolbar. If there is a check next to it, then you are already displaying this particular toolbar.

Now that you have your formatting toolbar displayed, look for the button that looks like a lower case “a” with arrows on each side of it. On the formatting toolbar it usually is located between the right justified button and the dollar sign button.

The cells that you highlighted earlier should still be highlighted. If they are not go back and repeat that step. If they are go ahead and click the merge button. You will see that the highlighted cells have now merged into one large cell and have centered the data, in this case the year.

You now know how to merge cells in rows. You can also merge column cells. This is done the same exact way as rows, except you do it vertically as opposed to horizontally.

Spreadsheets have basic database functionality. In order for this to work, however, it must be switched on. Then, you can do some things that you would normally associate with databases.

Open your spreadsheet Household Goods 2

Delete Rows 4 and Rows 10 (the empty rows between your headings and the items, and the items and totals. The contents of the spreadsheet need to be right next to each other in order for this to work).

Click in to the Cell G3 (Total Cost).

Then click on the tools Data menu and choose the Filter option and then AutoFilter (see the example below).

Your spreadsheet should the look like this, with filtering option along row 3, for each of your headings. All of the columns that contain data have now gained a dropdown box.

Click on to the Total Cost dropdown box. You will see a variety of choices available to you.

Choose sort ascending, and the data will start with the smallest total.

Choose sort descending and the data will start with the largest total.

You can also customise your choice, so if you want to see any particular data you can.

We will see data less than £20 first of all.

Click on to the custom option

Change the dialogue box so it corresponds with this on the right.

You should then get just the items that have a total of less than £20

Repeat this for greater than £50

You should then get a list of the items over £50 in price.

To remove the autofilter function - at any time - click on the tools Data menu and choose the Filter option and then AutoFilter (it should have a tick next to it to say it is “on”)

Your spreadsheet should then appear normal.

Save the spreadsheet and Exit

NEXT: More practice with Min, Max, If and the filter function

OK, let's begin

1. Open Microsoft Excel
2. Enter this title in cell A1: RENTING HOUSEHOLD GOODS and make it bold.
3. Enter the following information, starting in cell A3. Format the figures to two decimal places.
4. Your spreadsheet should now look something like this.

1. Check that you have entered the data correctly
2. Save the spreadsheet as Household goods
3. Print it on one page in Portrait (File  Page Setup  Page  Portrait)
4. Change the FREEZER RENTAL to 55.92 and the HI-FI INSURANCE to 0 (zero)
5. In cell E5 use a formula to calculate the COST BEFORE DISCOUNT of the WASHER

(COST BEFORE DISCOUNT = SUM(RENTAL:INSURANCE)

• Click in cell E5
• Click AutoSum button (the one that looks like a capital E)
• Press Enter

Copy this formula to get totals in column E for all the other items (use the fill handle for this)

Switch to Formula View

To do this, press and hold the Ctrl key and click the key above the tab button.

Save the sheet as Household Goods 1 and print the page

Change the screen back to Spreadsheet View. To do this, repeat the operation above. It is like turning a light switch on and off!

In cells C11, D11 and E11 use formulae to calculate the total cost of RENTAL and INSURANCE and the TOTAL of the TOTAL COST. To do this:

• Click in cell C11
• Click AutoSum button
• Click on
• Copy the formula to D11 and E11

Insert a new row between MICROWAVE and TV and enter the following data:

Use a formula to calculate the TOTAL COST of the DISHWASHER

Save the sheet as Household Goods 2

Print a copy in Portrait

In cells B14 onwards, enter the following formulae.

This will give you a list of the smallest price, the largest price - and the average price of the items in the list.

The management have decided that if the items cost more than £40, then there will be a discount of £10.

In Cell F3, enter the heading Discount?

In Cell F5, enter the formula =IF(E5>40, "YES", "NO")

Press Enter or tick the green button to confirm the formula

Replicate this formula down to F10

In Cell G3, Enter the heading TOTAL COST

In Cell G5, enter the formula =IF(F5="YES",E5-10,E5)

Press Enter or tick the green button to confirm the formula

Replicate this formula down to G10

In G12, add up the total cost using the Autosum Function

Your complete spreadsheet should then look like this.

Save the spreadsheet with the same name and print both the formulae and the worksheet.

NEXT: THE FILTER FUNCTION

 

We use formulae to perform a variety of operations, not only calculations. Some of these are special formulae that we call Functions. You will meet some of these as you develop your Excel skills at a higher level.

In this section we shall look at some straightforward formulae for adding, subtracting, multiplying and dividing.

As you have seen when calculating the SUM of data in a number of cells, we place a formula in a cell in the same way as text or numbers. It can be a very simple formula, such as adding the contents of 2 or more cells.

First of all, we need some data, so:

• Go to FILE and NEW
• Type in the data as shown here:

Format the spreadsheet as follows:

• The spreadsheet font is 10-point Arial
• Change the title in cell A1 to 12-point Arial Bold and centre it across the columns A to G
• Change the word TOTAL in cell A11 to Bold
• Change all the headings in row 3 to 10-point Arial Bold and centre them in their cells
• Change the data in columns B and C (B5:C9) to 10-point Arial Bold
• Insert a blank column between columns C and D
• Adjust the column widths if necessary

The formatted sheet should look like this:

In this spreadsheet we shall write FORMULAE for four types of calculation (addition, subtraction, multiplication and division), using the two sets of numbers in columns B and C.

Addition

The first step is to add together the two numbers in cells B5 and C5 (that is, B5 plus C5) and put the answer in cell E5. To do this:

• Click in cell E5 and type an = sign.

The = sign is your way of telling the computer that what follows in this cell is some kind of calculation. Without the = sign, you are only inputting text or numbers, and the program will not recognise that you want to calculate something.

• Complete the formula in cell E5 so that it looks like this: =B5+C5
• Confirm this formula by pressing the ENTER key
• Click in cell E5 again to see the result of the formula. It should be 14.

What has happened to the formula? It is still there but in cell E5 you can see only the result of the calculation. To see the formula, look up at the Formula Bar (below the Formatting toolbar).

The next step is to add the other pairs of numbers in rows 6 to 9, using a similar formula. You could make a separate formula for each row but - as you saw in section 3.2 - the quicker and better way is to replicate the formula in E5 into the cells E6:E9 using the Fill Handle. Refer back to section 3.2 to remind yourself how to do it.

Subtraction

Column F requires a different formula. This time we must create a formula in cell F5 to calculate the number in cell B5 minus the number in cell C5.

• In cell F5 type: =B5-C5
• Use the fill handle in F5 to replicate the formula into cells F6 to F9

Multiplication

Create a formula in cell G5 to multiply the two numbers in cells B5 and C5.

Note: In Excel we use the symbol * (asterisk) for Multiply.

• In cell G5 type: =B5*C5
• Replicate the formula into cells G6 to G9

Division

Create a formula in cell H5 to divide the number in cell B5 by the number in cell C5.

Note: In Excel we use the symbol / (forward slash) for Divide.

• In cell H5 type: =B5/C5
• Replicate the formula into cells H6 to H9

At this point your spreadsheet should look just like this one:

Calculating Totals Using the SUM function

Look back at sections 3.1 to 3.3 to remind yourself how to use the SUM function. Then complete your spreadsheet by doing the following:

• Use Autosum in cell E11 to calculate the total for cells E5:E9
• Replicate this formula into cells F11:H11
• Format all the numbers in column H to 2 decimal places

Here is the finished spreadsheet:

• Save the spreadsheet with the name FORMULAE.XLS.
• Print a copy of the spreadsheet and show it to your tutor
• Go to FILE and CLOSE

Next: more new practice and advanced formulas.

Now is the time to make some changes to this spreadsheet. However, we do not want to lose the original, so first we will make a copy. Here is the spreadsheet as it is at the moment.

• Click in cell A1
• Select the whole spreadsheet by clicking and dragging, or by using the SHIFT and arrow keys
• Go up to the EDIT menu
• Choose COPY
• Click in cell A20. We are telling the computer that we want the copy of the spreadsheet to start from this point when we paste it.
• Go to EDIT
• Choose PASTE

Now you should be able to see an identical copy of the original spreadsheet.

Important: In the subsections that follow (4.2 to 4.6) you are going to make changes to this copied spreadsheet but NOT to the original spreadsheet above it.

Inserting Rows And Columns

Make these changes to the spreadsheet copy:

Insert a new row called Car between the rows containing Clothes and Others. To create this new row:

• Click in any cell in row 32 (for example, in A32, which contains the word “Others”)
• Go up to the Insert menu
• Click on Rows
• You now have an empty row in which you can type:

Car 59.56 4.67 296.45 112.49

Now insert another row called Presents between Car and Others.

• Again click in the row containing the word Others. Go to INSERT then ROW and type in:

Presents 45.78 10.78 27.99 278.89

To improve the layout of the spreadsheet, let us create a blank column between the figures and the Yearly Total, i.e. between columns E and F:

• Click anywhere in column F
• Go to the INSERT menu
• Choose COLUMNS

NoteF: After you typed in each of the new figures for Car and Presents, did you notice that the totals at the bottom of columns B to G changed? When we insert a new row or column, the formulae take the changes into account.

Deleting Rows and Columns

The expenditure figures in row 27 suggest that you gave up smoking after March 2003, so you have decided to delete this entry completely from your spreadsheet. This means that you can delete the whole row.

You would follow a similar procedure to delete a whole column. To do this you would click anywhere in the column, select EDIT, followed by DELETE then click on Entire Column and OK

Copying and Pasting Cells, Formulae, Rows and Columns

In Excel, as in Word and other Microsoft applications, we can copy the contents of any part, or even the whole, of a spreadsheet. When we do so in Excel, it copies not only text and numbers but also any formulae that are in the area that we are copying from. As an example, let us copy the first line of the lower spreadsheet (row 24 beginning “Fares and Travel”).

• Select cells A24 to G24
• EDIT
• COPY
• Click in cell A38
• EDIT
• PASTE

So, you can see that you can copy and paste not only text and numbers, but also the formulae and the computer will calculate the answer for the new location.

• Finally, as we do not need the extra row, click on the Undo button to remove the data in row 38.

Formatting Number Cells to Show Decimal Places or Currency

To make our finished spreadsheet look more professional we shall make a couple of changes to the formatting of the numbers.

You may have noticed that some of the numbers in this spreadsheet look a bit odd. Look at these two rows:

Most of the numbers show two figures after the decimal point e.g. 24.32, 104.39 but others don't do so. This is because these other numbers would have 0 (zero) at the end after the decimal but do not show zeros. So, 226.00 appears as 226 and 19.50 appears as 19.5. We can change this so that all numbers show two decimal places, including zeros.

• Highlight all cells from B24 to G35
• Go to the FORMAT menu and choose CELLS

Next, change the Quarterly Totals and Yearly Totals to currency values:

• Do the same for cells G24 to G35 in the Yearly Totals column

Save your work again.

There are many different options in the NUMBER screen. You will be asked to FORMAT cells quite frequently so you will become familiar with these as your knowledge of Excel increases.

Your lower spreadsheet should look like this:

More About Number Format and Integers

In an earlier section you learnt how to change the format of numbers so that all numbers showed 2 decimal places. There is a shortcut way to do this, using buttons on the Formatting Toolbar. Try this:

• Select cell B24 containing the number 127.63.

• Click on the Increase Decimal button on the toolbar. Note how the number now has a 0 (zero) at the end: 127.630. This does not change the value of the number because the 0 comes at the end of the number after the decimal point.
• Click the Increase Decimal button again and you will see that another 0 has been added: 127.6300. The number therefore has 4 decimal places.
• This time, click on the button to the right of the Increase Decimal button. This is the Decrease Decimal button, which has the opposite effect. If you click it twice, your number will return to its original form: 127.63.
• Click the Decrease Decimal button twice more and you will see that the number has no decimal places and that it has been rounded up to the next whole number: 128
• We describe a whole number like this as being in Integer Format. You will meet this term in future Excel exercises, so you need to know what it is and how you can produce it (as you have just done!).

NEXT: PRINTING YOUR SPREADHSEET

Calculating Totals

The next step is to make some calculations using the data that we have put into the spreadsheet. To make a calculation, we must decide where we want to put the answer to the calculation and create a formula in that cell. (Note: The plural of formula is formulae - or formulas in American English).

Open the file SPEND.XLS and click on OK. So far the sheet looks like this:

Firstly, we need to add the data in each column and put the totals in the Quarterly Totals cells; secondly, to add the data in each row and put the totals in the Yearly Totals cells. For both of these operations we shall the same kind of calculation.

To start, let's total the JAN-MAR column. To do so, we are going to make a SUM function in cell B15. Click in cell B15 and type in the formula that will add up all the figures from B5 to B13, like this

=SUM(B5:B13)

Replicating A Formula With The Fill Handle

Our next step is to find the totals of the remaining columns. We could simply type in the appropriate formulae one at a time in each of the cells C15, D15 and E15, but it's not necessary to use this slow method in Excel. Instead, we can use a technique called REPLICATE to put the SUM formula in these adjacent cells.

Click in cell B15 if it is not already selected, and notice the small black square in the bottom right-hand corner of the cell.

This is called the FILL HANDLE. Position your mouse over the Fill Handle until it changes from the white plus sign to the black cross. Now click and drag the mouse over the next three cells (C15-E15) until you can see a grey line around all four cells. When you release the mouse button, the cells will automatically fill up with totals. Click on each one in turn and read its contents in the Formula Bar. The correct formulae for the four cells are:

=SUM(B5:B13), =SUM(C5:C13), =SUM(D5:D13) and =SUM(E5:E13)

When you have completed this, you will notice that a small icon has appeared at the point where you released the mouse button.

Move your mouse pointer over this icon and it will expand to show a downward-pointing arrow head.

Click on this to view a drop-down menu of Auto-fill Options.

In our example, select Copy Cells. This will copy not only the formula but also any formatting in cell B15 (e.g. the font and font size).

The Autosum Function

The next thing to do is calculate the Yearly Totals in column F, and for this we will use another shortcut. By far the most used formula in Excel is =SUM, which adds up columns or rows of numbers. Because this is used so frequently, there is an icon specifically dedicated to it. It is called the AUTOSUM button.

Now calculate the yearly total for Fares and Spending.

Click in the cell F5, go up to the toolbar and click on the AUTOSUM icon. The program will automatically put in a formula for you.

Look at what it has suggested: are there dotted lines running around the correct block of cells? If the formula is =SUM(B5:E5), that is correct and you can press ENTER.

There are times when the AUTOSUM will not choose the correct block of cells. Here is an example:

Select cell F5 and then, using the FILL HANDLE, replicate the formula in the NEXT 4 ROWS ONLY.

Column F should now look like this:

Click in cell F10 and then click the AUTOSUM icon. Look at the block of cells it has chosen. It wants to add up cells F5:F9, but we want it to add up cells B10:E10 for the total of “Going Out”.

We can change the block easily by clicking on cell B10 and dragging the mouse along to cell E10. As you do this, watch the formula in F10 change. When you are satisfied that the formula is correct - =SUM(B10:E10) - press the ENTER key.

Now continue to REPLICATE the formula into the other cells in the Yearly Totals column by clicking in cell F10 and dragging down to cell F13.

Although it is a very quick way of totalling up rows and columns, it is important to use the AUTOSUM function carefully. Do not just accept the range of cells that the computer offers you. Check that they are correct and, if they are not, use your mouse to click and drag over the desired range.

To find out our grand total for the year in cell F15, we have a choice: we can add up either the Quarterly Totals or the Yearly Totals. Both will come to the same amount so you can decide which way you prefer.

Use AUTOSUM in cell F15 to calculate the Grand Total for the whole year.

Save your work again. We do not need to rename the spreadsheet so you can just click on FILE and SAVE.

Showing And Hiding The Formulae

Sometimes boss tutor will ask you to hand in a spreadsheet with all the formulae showing in their cells. If you look at what is on the screen at the moment, you can see that the formulae are hidden; in their cells we can see only the answers to the calculations. To view the formulae in their cells - Formula View - use the key combination CTRL + ` (grave accent), as follows:

Hold down the CTRL key

The result is that all the columns on your spreadsheet will double in width and all the formulae will be shown in their cells. Where there are no formulae, numbers and text will simply remain as before - the only change that you will see to these numbers is that they are now left-aligned in their cells. DO NOT try to change this - it is the way that Formula View shows data.

To turn this facility off and go back to the normal Data View repeat the key combination CTRL + `.

Editing A Formula From The Formula Bar

Finally, if you realise that you have made a mistake in a formula or want to change it for some other reason, you can edit it:

Click in the cell that has the incorrect formula

Click once on the formula as it appears in the FORMULA bar

Use the arrow keys to move your cursor backwards or forwards to the parts of the formula that you want to change

Change the formula in the same way that you would make changes in Word

When you have finished, press ENTER

Save the spreadsheet again and continue to the next in this series of instructions!

NEXT: Editing, inserting and deleting

Are The Correct Toolbars On?

Now let's try creating a spreadsheet. Before we begin, check that both the STANDARD and the FORMATTING toolbars are at the top of the screen.

If they are not:

• go to the VIEW menu
  • click on TOOLBARS

Click in the Standard and Formatting checkboxes

ENTERING DATA - START HERE!

Enter all the data as shown in the worksheet below. When you type in the title “PERSONAL SPENDING 2003” in cell A1, it doesn't matter if the cell doesn't appear wide enough to hold the text.

To let the computer know that you have finished a particular entry, you can either press the RETURN key, which will move you down to the next row, or press one of the Arrow keys, which moves you in the direction of the arrow.

NOTE: Don't worry if your spreadsheet looks different from the one below. You'll find out later how to change its appearance (for example, how to change column widths to fit your data).

Adjusting Column Widths & Row Heights

Some of the text in column A is too wide to fit the column. This is acceptable for a main heading (like PERSONAL SPENDING 2003 in row 1) but not for the text in other rows. If we want to change the width of the column so that we can see the whole text, there are various ways to do this:

Position your cursor on the line between A and B until it changes into a black cross with two arrowheads. You can then:

Either: click and drag until the column becomes the required width, then release the mouse button.

Or: simply double-click your mouse button. The column will automatically adjust to the width of the longest entry. You can also set a column width by selecting the whole column, going to the Format menu, then Column, Width and entering the exact size you require. Press OK.

Similarly, to adjust the height of a row, you can use the same click and drag method, or click on the number then Format, Row, Height, OK.

Try adjusting the width of column A, then the height of row 15.

Changing Data In A Cell

Although Excel looks quite different from Word, it has many of the same capabilities. Look at the Excel toolbars; you will see several icons that look familiar - CUT, COPY, PASTE, BOLD, ITALIC, UNDERLINE and so on.

If you know how to change the appearance of text in Word, you should be able to change the appearance of any data in Excel. Simply select the cell or block of cells and click on the appropriate icon. Try these changes:

Change the font size of the spreadsheet title (PERSONAL SPENDING 2003) to 14 point

Select all the month headings and make them Bold

Select all the expenses in column A and change their font from Arial to Times New Roman, size 12

Alignment

Notice how the spreadsheet has aligned the entries you have made. All text is aligned to the left while all numbers are aligned to the right. Try centering the heading in column E (OCT-DEC). First select the appropriate cells - in this case E4 to E14 - then click on the Centre icon.

Click somewhere else on the spreadsheet to deselect the cells E4:E14.

Centring Titles across a Number of Columns

We often centre the title across all the used columns. In this example we have used columns A to F. Position the cursor in cell A1 and select all the cells across to F1, using one of the methods explained in section 1.4. Click the “Merge & Centre” icon. Deselect the block of cells.

The six cells A1 to F1 have now been merged into one cell and the title is centered across this area:

Wrapping Text

Look at row 3: the last entry “Yearly Totals” is much wider than necessary. It would look better if the words were on top of each other instead of side by side.

To do this, select cell F3, then click on the FORMAT menu, followed by CELLS, then the ALIGNMENT tab.

Click in front of the checkbox called Wrap text, then on OK.

Changing Or Deleting Data In A Cell

It turns out that there is a data error in this spreadsheet. The figure for JAN-MAR Fares and Travel should be 127.63 not 217.63. There are two methods of changing the contents of a cell:

Select the cell (B5), press DELETE and type in the correct figures

Select the cell, position your cursor on the FORMULA bar and click. The cursor becomes an INSERTION BAR and you can now edit the entry as you would in Word. When you have finished, you can press the ENTER key.

Changing The Size Of The Screen View

If you find that your spreadsheet is so big that you can't fit all of it on the screen at the same time, you can adjust the PERCENTAGE VIEW of the sheet. Look at the percentage shown near the right-hand end of the Standard Toolbar. Click on the down arrow next to it and change to 75%.

If you prefer a percentage that is not in the list (e.g., 80%, 90%), click on the highlighted figure at the top, then type in your own preferred size. Press ENTER.

The spreadsheet on your screen should look like this:

Before continuing, make the following changes:

Change the title size to 12 points. (NOTE: Click in cell A1 to select the title)

Select all the month headings and turn off the Bold.

Select all of the expenses in column A and change their font from Times New Roman, size 12 to Arial, size 10

Change the figures for OCT-DEC back to right-aligned
Finally, save the file with the name SPEND.XLS

Close the document.

NEXT: Calculating Totals and Editing Formulae