Microsoft Excel tips and tricks: seven ways to master your spreadsheets in 2020

Faced with an empty Excel worksheet and lots of confusing options, it can be difficult to know where to start, especially if you’re relatively new to this type of tool. Our top tips will have you using spreadsheets like a pro.

Microsoft Excel is so much more than a glorified checklist. It has a lot of powerful tools and features that can help you visualise and analyse all kinds of data in interesting, useful ways. You can create charts or graphs, perform automatic calculations and more.

But the software doesn’t just have to be a tool for managing boring corporate data – keep scrolling for the details.

Which? Tech Support – our experts will help you tame your tech.

Buy Smart newsletter sign up box

Get to grips with Microsoft Excel

1. Learn your way around

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.

2. Use Excel templates

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.

3. Track your expenses

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:

  1. Open a blank workbook
  2. Enter the word ITEM in cell A1 and COST in cell B1
  3. Below cell A1, enter the names of items you’re paying for in column A
  4. 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.

4. Perform automatic calculations

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.

5. Print Excel spreadsheets

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.

6. Create charts

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.

7. Manage your budget

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.

Free Microsoft Excel alternatives

Excel is the industry-standard spreadsheet tool, but there’s no shortage of free alternatives. Most can open and export Excel (.xlsx) files and work in much the same way as we’ve described.

The only difference is that they don’t provide the advanced features and templates Microsoft’s market leader does. There’s a free online version of Excel – just log in with a Microsoft account at onedrive.live.com and click the nine dots icon in the top-left corner of the page.

Google Sheets

Log in with a Google account at docs.google.com, then click the menu (three lines) button top left and select Sheets. Google Docs and Microsoft’s Office apps are also available for Android and Apple phones and tablets.

LibreOffice

If you prefer to download and install software rather than use online tools, the best free option is LibreOffice (libreoffice.org), which includes the very Excel-like Calc spreadsheet tool

Sign up for Which? Computing

  • The UK’s largest computing and technology title, published six times a year.
  • Easy, jargon-free advice so you can make the most of your tablet, laptop or smartphone.
  • One-to-one support from our friendly Tech Support team, ready to respond to unlimited member queries.

Additional reporting by Tom Morgan.



source https://www.which.co.uk/news/2020/09/microsoft-excel-tips-and-tricks-seven-ways-to-master-your-spreadsheets-in-2020/
Post a Comment (0)
Previous Post Next Post