Lots of people have a copy of Microsoft Excel on their computer but have never used it.
Spreadsheets sound like they are only useful for accountants, but this isn’t really true.
In fact, Excel can be very useful in so many areas. Families can use it to budget for home improvements, control phone bills, or simply to convert grammes to ounces as we’ll show here.
Text within 'single quote marks' is the exact text to type into cells as we proceed step by step.
Getting started
Here’s an example of using Excel to figure out the cost of fitting a fully tiled
bathroom that will have a shower, basin and toilet. We’ll give examples using
the latest version of Excel, 2007, first, then explain if any tasks are
different using older versions.
Open Excel and click the Home tab if it’s not on top. In cells A1, B1, and C1 enter 'Trade', 'Item', and 'Price'. Underneath column B, in any order, enter every item needed for the job, such as a basin, toilet and tiles, then enter the cost of each alongside it in Column C. Beside each of these items in column A, enter either fixtures, accessories, plumbing, electrical or building work. Save the file.
A simple table of data like this is called a Table in Excel 2007 and a List in earlier versions. The column headings are known as Fields. The details in each row are called Records. To separate the Field Names from the rest of the List, we’ll change the way they look. Click the mouse on cell A1 and, holding it down, move the cursor over to cell C1 before releasing it. This will select cells A1, B1 and C1. Press the Control and B keys together to make the text bold, then click the down arrow by the Font Color tool and pick a colour.
Sort it out
To see how much is being spent in each category we’ll group the items. Click
anywhere in column A. Under the Home tab, click the Sort & Filter tool.
Select Sort A to Z. In older versions of Excel choose Sort from the Data menu,
then choose Sort by Trade, Ascending.
Now all the items in each category are grouped together. Click in column A again. Click the Data tab and the Subtotal tool, or in older versions of Excel choose Subtotals from the Data menu. Click OK to simply accept the default subtotal settings.
In the process of inserting subtotals, Excel will also create what it calls an Outline. This means there are three little numbers to the left of the column headings: 1, 2 and 3. Click on 1 and everything but the grand total is concealed. Click on 2 and only the subtotals are shown. Click on 3 and the whole table will appear again.
If the grand total seems a little high for the intended budget we can rearrange the data to see where the most money is presently spent. Click any cell in column A, then click the Subtotal tool (in older versions pick Subtotals from the data menu) and in the Subtotal dialogue box click the Remove All button. Click in column C. Under the Home tab click Sort & Filter and choose Sort Largest to Smallest (in older versions choose Sort from the data menu then Price, Descending). This will sort the table so the most expensive items are clearly shown at the top of the list.
