Excel tips

Making sense of our data-rich, noisy world is hard but vital. Used well, Conditional Formatting brings out the patterns of the universe, as captured by your spreadsheet. That's why Excel experts and Excel users alike vote this the #1 most important feature. This can be sophisticated. But even the simplest colour changes can be hugely beneficial. Suppose you have volumes sold by sales staff each month. Just three clicks can reveal the top 10% performing salespeople and tee up an important business conversation.

To create a spreadsheet that helps you manage your monthly household budget, it’s best to start with a template. Click File > New, then type budget into the search box and press Enter. There’s a template called Personal monthly budget planning (shown below) that can be easily adapted to suit most household needs. Double-click it to open a new workbook based on the template. You’ll need to make a few changes to make the template work for you. By default, all values are shown in dollars, for example. To change to pounds, press Ctrl+A to select all the cells on the sheet, then right-click, select Format cells and choose Currency. Next, you may wish to rename categories to reflect your own needs. If you own your home, you could click the Mortgage or Rent cell and type Mortgage instead. You can change the fonts, colours and other formatting, too, if you wish. The worksheet only represents your budget for a single month so, once you’ve got it looking the way you want, you could copy it and paste it into new worksheets for further months. Press Ctrl+A to select the entire sheet, then Ctrl+C to copy it. Now click the + button next to the Personal Monthly Budget tab to create a new sheet. Press Ctrl+A again to select your new sheet, then Ctrl+V to paste the contents of the previous sheet into the new one. Repeat this process until you have enough worksheets for a year. You can rename worksheets by right-clicking each tab, selecting Rename, then typing a new name – for example, you could rename your monthly budget sheets January, February and so on.

Turning a dull spreadsheet into a colourful chart is a great way to help you visualise and analyse your data. Excel makes this easy to do, too. To demonstrate, create a simple spreadsheet with the months of the year in column A and a set of corresponding values in column B representing the amount of outgoings spent for each month. Next, select the data range by clicking and dragging to highlight the cells for each month and their values. Click Insert, then choose one of the options in the Charts section at the top of the window. Clicking Recommended Charts will present you with some preselected options designed to suit the data you’ve selected. Select a chart, then click OK to insert it into your sheet.

If you’ve tried to print an Excel spreadsheet before, you’ll know it isn’t always easy. It’s all too common to see your printer spit out seemingly random bits of your document across dozens of sheets. But there are easy ways to fix this. With your spreadsheet open, click File > Print. Below the preview image, you’ll see how many pages will be printed – for example, 1 of 1. If the second number is high, you may need to change the way your document is printed to stop it being awkwardly split across multiple pages. Under Settings, you can choose between Portrait Orientation (vertical) and Landscape Orientation (horizontal) – sometimes changing this can help fit more columns or rows on to each page. You can also use the scaling options to cut down on printing waste. By default, No Scaling is usually selected – click this and you can choose to Fit Sheet on One Page, for example.

Excel can carry out maths functions that dynamically update whenever you change your data. You can automatically add up all the numeric values in one column and keep a running total that updates when you add or change any figures in the column. In the expenses spreadsheet above, for instance, we could click in cell B9 at the bottom of our COST column and click the AutoSum button (symbolised by the Greek letter Sigma). AutoSum (shown below) makes an intelligent guess as to the type of calculation we want to perform. If AutoSum doesn’t automatically select the correct data, you can click and drag to select the cells you want to include in your sum. Pressing Enter on your keyboard will apply the formula to the cell. Addition is just one of the automatic functions you can perform with AutoSum. Click the down arrow next to the AutoSum button to see the other options available, such as Average, or click More Functions to search for something specific.

Say you want to create a document where you can keep track of the costs of home renovations or an event that you’re planning. You could do this in a word-processing program such as Word, however if you do it in Excel you can organise data more neatly and get some handy extras, such as automatic calculations. To create an Excel spreadsheet suitable for tracking expenses:

• Open a blank workbook
• Enter the word ITEM in cell A1 and COST in cell B1
• Below cell A1, enter the names of items you’re paying for in column A
• Enter the cost of each item in column B If your text doesn’t fit in the cell, you can make a column wider by moving your mouse to the line between column letters in the column header at the top of the sheet. When the cursor changes to a vertical bar with a double-headed arrow, click and drag to change the width of the column on the left.

A blank worksheet can be more than a little intimidating. But Microsoft helps to soften the blow by providing a wide range of templates so you’re not starting from scratch. By using a template, a lot of the hard work – formatting, calculations and so on – is done for you. All you have to do is supply the data and tweak anything you don’t like. To choose a template, click File > New and scroll down to see the templates that are available. Note that you’ll find a few handy tutorials as well. The selection of templates offered here is only the tip of the iceberg – there are loads more available online. If you’re after something specific, click Search for online templates and type a keyword – ‘budget’, for example, then press Enter on your keyboard.

Let’s start with the simple stuff. Launch Excel, then select Blank Workbook from the home screen and you’ll see a gridded area with columns labelled A, B, C and so on along the top, and rows numbered from 1, 2, 3 and onwards down the left-hand side. Each of the empty squares in the grid is known as a cell, and each cell is identified by the letter and the number where the column and the row intersect. So, for example, the cell in the top-left corner is cell A1. At the bottom of the worksheet, you’ll see a tab that says Sheet 1. Excel workbooks can be made up of multiple worksheets – click the + icon to the right of the tab to add another worksheet and click the tabs to switch between sheets.

Explore data in a table quickly. Filtering effectively hides data that is not of interest. Usually there's a value (e.g. Blue cars) that you're looking for and Filters will bring up those and hide the rest. But in more modern versions of Excel, you can now also filter on number values (e.g. is greater than, top 10%, etc), and cell colour. Filtering becomes more powerful when you need to filter more than one column in combination e.g. both colours and vehicles to find your blue car. Alt D F F is the shortcut (easier than it sounds - give it a go). Conditional Formatting and Sorting serve related purposes. Sorting involves rearranging your spreadsheet, which is intrusive and may not be desirable. Conditional formatting brings visualisation. Filtering is fast and effective. Choose well.