Track finances in Excel
Quick ways to analyse the numbers that affect your business
Whether you're tracking expenses, projecting revenue or charting loan payments, here are quick ways to use Microsoft Excel 2003 to manage and analyse the numbers that impact your business.
Automate your invoices
One of the most useful items to set up in Excel is an invoice template. Once you've done it, you'll use it again and again. And it saves time, because Excel can do the calculations for you.
An easy way to create an invoice is to use a pre-built template. You'll find a number of them on the Microsoft Office Online website.
1. | With Excel open, choose New from the File menu. |
2. | Make sure you are connected to the internet and choose Templates on Office Online from the task pane. |
3. | In the Office Online website, type invoice in the search box at the top right of the screen and press Enter. |
4. | Look through the list for an Excel invoice template. Click the name of the template. Click the name of the template to preview it, and then click the Download Now button to download it to your machine. |
Once you have a template you like, you can customise it and save the result as your own personal invoice template.
| • | Make the changes (such as adding your company name and logo) |
| • | Click on the Save button and type a name for your invoice template |
| • | Choose the Template option in the Save as type box under the file name. This will put your template in the Templates folder on your machine. Click Save |
To use your invoice template, choose the On my computer option from the New Workbook Task Pane and select your new invoice template.
Analyse your data
As you add data to an invoice or inventory list, it will become more useful if you can sort it and filter it.
Sorting
| • | Click in the column you want to sort by and press the required Sort button |
| • | For more sorting options, choose Sort from the Data menu |
Filtering
Filtering enables you to extract data of a particular interest. For example, you might want to see all of the most recently added items on an inventory spreadsheet.
1. | Go to the Data menu, choose Filter and then AutoFilter. You will now have an arrow at the top of each column. |
2. | Click the arrow of the column you want to filter and choose the required option. To reveal all the data again, return to the Data menu, choose Filter and Show All. |
Create financial reports
Creating a financial report may be as simple as turning one of your working spreadsheets into a formatted document to make it readable when printed out. Try these ideas:
| • | Hide rows or columns which you don't want to show when printed. To do this, right click on the row or column heading and choose Hide. |
| • | Use borders and fill colours to make a solid mass of figures more meaningful. |
| • | Use diagonal text orientation to make long titles fit better into narrow columns. To do this, select the headings, choose Cells from the Format menu and change the orientation on the Alignment tab. |
Bring data alive with charts
Data is often more meaningful when displayed in a chart because you can see trends at a glance and compare different sets of numbers. Plus some people are more visual; they relate better to pictures or graphics than they do to words and numbers. For instance, here is a simple quarterly sales table built in Excel.
The chart shown below was quickly created from the Excel data in the table.
To create a chart:
1. | Click Insert then click Chart. |
2. | Select the type of chart you think would best illustrate your data graphically, click Next. |
3. | Highlight the columns or rows you want to highlight on your chart, click Next. |
4. | Add titles, categories, and and/or values, click Next. |
5. | Select where you want your chart to appear, click Finish. |
You don't need to modify your chart if your figures change; Excel automatically updates it to show the latest data. However, you can make changes to how your chart looks:
| • | To change the appearance of something on your chart, click the item to select it, go to the Format menu, and choose the first option on the menu |
| • | To change the type of chart, go to the Chart menu and choose Chart Type |
| • | To add other things to your chart (perhaps data labels and titles), go to the Chart menu and choose Chart Options |
More info >