Five Ways to ...

Use Microsoft Excel More Effectively

Welcome to the P&L Advice & Guidance articles. You can return to the main site by either selecting the link below to read more articles or the P&L Logo for the home page.

Return to: Advice & Guidance / Five Ways To ... Use Microsoft Excel More Effectively

Many people find the thought of numbers and spreadsheets scary and intimidating, but they don't have to be. With a little knowledge you'll be amazed at just how much they can do for you.

Here are five things to help your business get more out of Excel and spreadsheets.

1Use it as a database. It can be a very simple way to store and retrieve all your customer's contact details. Include each separate piece of information in a separate cell so they can be identified and retrieved individually.

For example, you may want addresses on letters to have title, initial and surname (Mr J Smith), but the salutation to say Dear John or perhaps Dear Mr Smith. Unless you have each of these pieces of information held separately, you'll not be able to do this and will be restricted to whatever was initially put into the database.

2Select the whole spreadsheetSort a list. This is useful for finding specific entries or to see how many entries fit into a particular category. Highlight the whole spreadsheet by clicking on the box shown in the image. Then select Data and Sort from the menu bar at the top of your screen.

This will give you a dialogue box from which you can choose how you want to sort the file. If your spreadsheet has a row of headings then tick the box marked "header row" where it says "My data range has". The select the column you want to sort the data by. When you select OK the data will have been automatically sorted.

There are more sophisticated functions you can do here, e.g. separating and counting the number of entries in each category, so have a play round once you're comfortable with the concept and see what else you'd like to do.

3Select the bottom right hand corner of the cellAutomatically complete entries. You don't always have to type in every entry, particularly if it's a list you're completing or the entries have a sequence like numbers or dates.

Put an entry in one of the cells and highlight it. If you hover over the bottom right of the box round the cell you'll see as little cross (see image). Holding down the right mouse button and dragging across a number of cells will automatically put that same entry into the cells you've highlighted, after you've release the mouse button.

If you have a sequence like numbers or data, fill in a few entries before highlighting them. This tells Excel it's a sequence. Then, highlight, drag and release to complete the sequence.

4Select the Function IconSimple maths and statistics. Statistics and calculations can be scary for some people, but Excel can make you look like a maths wizard in no time.

Imagine you wanted to add up all the figures in a column. Highlight the cell where you want the final total to be and then click the fx symbol as shown in the image. This opens the Insert Function dialogue box. Scroll down the box that says Select a function until you find the calculation you want. In this case select Sum and then OK.

The next dialogue box allows you to choose the numbers you want to add up. On the spreadsheet, highlight the numbers and when you've finished Excel will automatically fill in the range of numbers in the box called Number1. When you Select OK the column will have been added up and the total inserted in the cell you want it in.

The Insert Function has a number of different functions in it, e.g. average (AVG) or a count of the number of entries (COUNT).

5Select the Chart WizardCreate a Chart. You can liven up your presentations with diagrams and graphs or view things in a different way by using the Chart function. Select the Chart Wizard (see image for the icon) or select Insert and then Chart from the menu bar at the top. This opens a dialogue box from which you can choose the type of chart you want. Let's imagine it's a graph of sales over a period.

Select the type of chart you want from the list, e.g. Line and then select Next. The next page will allow you to tell Excel where the data for your graph is. Highlight the cells where the data is and then Next. Your chart is now complete and the remaining sections are all about how you want it displayed, e.g. with legends, lines or colours and whether you want to embed it in a sheet or give it one of its own.

You'll probably get it wrong the first few times you use it, but just cancel the wizard and do it again until you get comfortable with different layouts and where the data needs to be in your spreadsheet.

If you need help with any of the functions listed here or any other features you want to use on Excel, please contact us and we'll see what we can do. Please note that this information is provided for general guidance only and P&L cannot accept responsibility for any actions or consequences as a result of using these hints and tips.