Miscrosoft Excel
Create spreadsheets to analyse your spending, monthly budgets and other numeric data.
Plan and analyse
Excel is a spreadsheet app that enables you to analyse numeric data, forecast what may happen when it changes and so plan ahead. It is often used to analyse finances, but this is far from its only use.
Use a template
Spreadsheets are used by companies to analyse data relating to their business, but they are also useful for home users – for example, to monitor and analyse your monthly spending and create budgets. An Excel file is called a workbook and it contains one or more worksheets, so you could track your spending for each year in separate sheets and save them all in one workbook.
To start from scratch, click File>New>Blank workbook (Fig 1). It contains one spreadsheet and the ‘+’ button at the bottom of the window enables extra sheets to be added in tabs. There are hundreds of ready-made workbooks and you can save a lot of time and effort by loading one that is similar to what you need and then customising it. Click File>New and enter a word or phrase to search for, such as budget, invoice, calendar or expense (Fig 2). Matching workbooks are shown as thumbnails and clicking one shows a larger image and file details. Click Create to open the workbook.
Enter data
Click any cell in the sheet and you can enter text or numbers. Excel can usually tell what has been entered, but to avoid confusion, use the correct cell format option. This is needed for telephone numbers, otherwise Excel strips off leading zeros. Numbers are right-aligned and text is left-aligned, but the Alignment section of the Home tab has buttons to left, centre or right align the contents of a cell.
Format cells
On the Home tab of the ribbon are lots of controls for formatting cells and in the Font section you can select the font and the size of the cell contents. It has no effect on the calculations performed, but it can help to make a spreadsheet containing lots of numbers and text easier to read. Choose a large font for the title and make it bold by clicking the B icon. Click the paint bucket icon or the arrow next to it to select a background colour; click the font colour icon and so on. Colour the headings placed in columns or rows and highlight important cells, such as totals or the result of calculations. This draws the eye towards them.
The spreadsheet grid shows where the cells are, but it often helps to draw a heavier line around certain cells to highlight or separate them. Click the arrow next to the border icon in the Font section of the Home tab to display a list of borders you can apply.
Click the top-left corner of the spreadsheet in order to apply formatting to all cells.
Customise the status
You can create formulas and enter them into cells to analyse the data and show various interesting facts about it, but it is not the only way to use Excel and it is possible to discover a lot of facts and figures simply by pointing and clicking within the cells. Right-click the status bar that runs along the bottom of the window
To insert or delete a row or column, right-click the column or row label to bring up a menu.
and a large menu is displayed (Fig 3). The important functions are in the bottom half of this menu and you should tick Average, Count, Numerical Count, Minimum, Maximum and Sum.
Once you have done this, click in the spreadsheet to select a cell and drag down a column of numbers or money, or drag along a row of numeric data. The status bar now shows all those functions that you enabled. For example, you can see the sum of the numbers or money, the average value, the maximum and minimum values and so on. This feature enables you to quickly analyse part or all of the spreadsheet without even writing a single function. Just point, click and drag over cells.
Another useful interface feature is the zoom slider in the bottom-right corner. Drag the control to zoom in or out.
Use AutoSum
One of the most common uses for Excel is to find the sum of a column of numbers and this achieved with AutoSum. Click below the last cell in a column of numbers or money and click the sum button (E) in the Editing section of the Home tab. Excel enters the formula needed to calculate the total, such as =SUM(B1:B8). Press Enter to accept it. Click the arrow next to the AutoSum button to insert other functions like maximum, minimum, average etc.
More complex functions are entered by selecting them on the Formulas tab and entering cell references like A1, B3 and so on. Often it is sufficient to point and click on the cells to use in a formula and enter plus, minus, multiply or divide symbols between them – such as =B2+C4, which adds cells B2 and C4.
Backstage view
Excel has a Backstage view that is accessed by clicking File > Info. It displays information about the workbook and it enables you to modify settings. For example, on the right is Properties and you can give the workbook a title by clicking ‘Add a title’. Tags and categories can be set and these could be used to organise workbooks or to search for them using Explorer.
The Versions section is useful and if you make a mess of a spreadsheet, you can return to a previous version of the file and undo any recent changes. Excel automatically saves a workbook as a new version every so often and these are all listed. The Protect Workbook button displays useful functions that enable you to lock a finished workbook so that it cannot be changed, such as by other people who use it. You can also encrypt the file and add a password to prevent other people from opening it.
Insert a chart
A spreadsheet full of numbers is difficult to understand and it is often hard to see what all the figures mean in simple terms. Although certain parts like totals and balances can be highlighted with coloured backgrounds, it isn’t easy to see what part
all the other numbers play. A simple example is your monthly bills, which are easily added to a spreadsheet to track your spending. A function will tell you the total amount, but breaking down the figures and seeing where your money is going isn’t easy. A chart can make a huge difference and you can see at a glance which bills are the highest, and the proportion each bill contributes to the total. Excel is able to create charts from financial figures or any type of numeric data and there is a wide range to choose from (Fig 4). There are the familiar pie and bar charts, but also specialised ones such as radar, doughnut, scatter and others. A great feature of Excel is that it can suggest the type of chart that is best for the data in your spreadsheet and then with a couple of clicks of the mouse, it can create it and insert it for you. There is very little work involved and often the resulting chart just needs to be dragged into position and re-sized.