Get Which? Tech Support for £49 a year or £4.99 a month
Contact our experts for unlimited 1-to-1 support by phone, email or remote fix.
Find out moreBy clicking a retailer link you consent to third-party cookies that track your onward journey. If you make a purchase, Which? will receive an affiliate commission, which supports our mission to be the UK's consumer champion.
Microsoft Excel tips and tricks: 20+ useful features you need to try
Our experts help you save time while rummaging around in a spreadsheet, whether you're using Microsoft Excel or Google Sheets
Microsoft Excel dominates the world of spreadsheets and is backed by seriously powerful capabilities in data analysis. With so many features, there's always something new to learn.
To get to grips with Excel is to be able to manipulate numbers effortlessly, carry out complex calculations and produce charts that get across a message that a column of figures couldn't.
Below, we’ve rounded up some handy Excel tips, plus details that should be useful in Google Sheets. The two apps follow similar principles, but you might find that menu options or icons are in different places in Google’s Sheets app.
(A version of this article was originally published in Which? Tech Magazine.)
News, deals and stuff the manuals don't tell you. Sign up for our free, monthly Tech newsletter.
Quick tips to save time
Adding up numbers
If you want to tot up a column of numbers quickly, there are a couple of ways to do it.
One is to highlight the cells and click the Functions button on the Excel Home tab – it’s a Greek letter sigma Σ – which drops down to give some alternatives, such as averaging the numbers.
Alternatively, you can just highlight the cells and look at the bottom of the Excel window, where you’ll find an average, total and a readout of how many cells you’ve selected.
In Google Sheets, this only shows the total – for more options, click on that number and look for the bottom-right Sum drop-down menu.
Resizing rows
To make the data in all your rows and columns more readable, autofit the cells once you’ve finished writing into them.
Use Ctrl + A to select all the data, then from the Home tab, select Format and choose Autofit Row Height or AutoFit Column Width.
Automatically fill in data
Excel can fill in predictable data if you specify how it starts, such as the months of the year–
Select the existing data, then click and hold on the bottom right of the cells. Drag down across all the cells you want to autofill - and helpfully it shows you what the final cell will contain.
Google Sheets can do the same thing, but it gives you less of a clue about when to stop as it doesn’t have the same handy tooltips.
- Add today's date to a cell – Select the cell, press Ctrl + ; and it will pop straight in. To add today’s date to a formula, use =TODAY().
- Tidy text – Ever typed text into a cell and seen it continue across the sheet? Place your cursor in the cell where you want the line to break and press Alt + Enter. Alternatively, wrap the text. In Excel, select the Home tab and then Wrap Text button. In Google Sheets, go to Wrapping > Wrap.
Got an ancient, sluggish laptop? Upgrade and get more done - see our expert laptop reviews.
Get creative
Conditional formatting
This clever way of changing cell colour allows you to more easily see how the numbers in a cell range relate to each other, such as which is the highest.
To find it, open the Home tab and choose Conditional Formatting, then find an option from the fly-out menu that suits your needs. In Google Sheets, it’s on the Format menu.
Alternating colours
To make a chart easier to read, you can automatically apply alternating colours. This can help differentiate, for example, different rows and make your spreadsheet instantly look smarter.
In Excel, on the Home tab, next to the Conditional Formatting button, select Format as Table. In Sheets, select Format then Alternating colours.
Data from images
Ever pulled your hair out having seen a picture of a spreadsheet? Excel can analyse the image and extract the data.
Save the picture on your PC, or copy it to your clipboard, then start a new workbook in Excel and from the Data tab choose Get Data. Then choose From Other Sources > From Picture, and pick whether the file is on your PC or clipboard. Excel analyses the image – higher resolution files work best – and you can review the data before inserting it.
- Trim your data – Excel's TRIM function gets rid of unwanted spaces in data, making your sheets tidier. It can remove leading, trailing or double spaces from text or numbers, but spaces created by your keyboard won’t be touched.
- Chart hits – It’s fairly easy to create charts from Excel data, but any element of a chart can support a graphic of its own. This means a bar chart of various countries’ GDP can be jazzed up with flags, or you can differentiate the slices of a pie with more than just colours. Create a chart from your data using the Insert tab, then click a segment and drop down the Series Options menu on the right. Choose the Fill option, and you’ll get a choice of gradients, solid colours, patterns or an external file.
Tips for sharing a spreadsheet with others
Get your own view
If you need to play with data but don't want to affect the view of other users, create your own ‘temporary’ view.
To try it, select View > New View and your sheet will change tone so you can edit it in Temporary View, with the option to keep or discard when you’re done. Be aware anyone with access to the main spreadsheet can also see your filter view.
Quick graphics
Excel allows you to add a tiny chart at the end of a row in its own cell – it calls these Sparklines. To get one, select the cells and click the Quick Analysis button that appears, then choose Sparklines from the fly-out menu.
In Google Sheets, it requires a formula using the SPARKLINE command and some information about the data range.
Freeze a column or row
This means being able to see areas all the time while you scroll the rest of the sheet. To do this, select a row or column by clicking the edge cell with its letter or number, then go to the View tab and drop down the Freeze menu, which gives you a few options.
In Google Sheets, go to View > Freeze.
- Flash Fill – This will attempt to autofill cells based on their neighbours. Just select the cells you want to fill and click the lightning bolt icon.
- Group cells – Select the cells you want to hide, then from the Data tab choose Group. On the far left of the sheet, you’ll see a - button that will collapse your selected cells into one row, turning into a + button you can use to get them back again. In Google Sheets, select a row header, right-click, then choose Group row.
Get more from your money - our computing experts have picked out the best laptop deals.
Quick and useful formulas and functions
See which cells are being referenced by a formula
Press F2 with the cell selected and they’ll be highlighted as long as they’re in the same sheet.
In Google Sheets: double-click a cell with a formula in it. Go to the cell ref referenced in a formula in Excel, use Ctrl + [. Press F2 again in the input field at the top of the screen. F4 lets you repeat your last action.
Use CEILING to round up numbers
The CEILING function in both Excel and Google Sheets rounds up an entry to the nearest multiple of a number you specify. So if you’ve got 64 in cell D6, you can round it up to a multiple of 5 by specifying =CEILING(D6,5) and getting the answer 70.
To round down, use FLOOR in the same way.
Merge text strings
Excel isn’t all about numbers. There’s a CONCATENATE function that will combine text cells, so with the text ‘Which?’ and ‘Tech’ in cells A2 and A3, =CONCATENATE(A2&” “&A3) will produce ‘Which? Tech’ in the cell you put it into. Note the use of speech marks to create a space between the words.
This same function works in Google Sheets, too.
- Dating differences – To return the difference in years, months or days between two dates, use DATEDIF. Make a note of the cell addresses containing the two dates, then enter =DATEDIF(cell1,cell2,”Y”) to see the difference.
- The IF function is very powerful – The basic usage gives you a formula that returns one result if a condition is true, and another if it’s false. This is handy if you want to see at a glance if you’ve gone over budget for the month, say. The syntax goes like this: =IF(condition, true_value, false_value), and that can become something like =IF(B3<1500,”Under”,”Over”). Press Enter, select that cell and drag the bottom right corner to cover other cells.
If you're considering a new Apple laptop, find out which model is right for you and which retailers have the best prices - see best MacBooks.
Handy keyboard shortcuts
Select a whole row
You don’t have to use your mouse to select large amounts of data – you can do it with your keyboard to save time. Click on your chosen cell, then use Ctrl + Shift + the down arrow to select it and the whole column below it, or Ctrl + Shift + the up arrow to do the same in the upward direction.
The left and right arrows do exactly the same thing across rows. Excel is clever in that it doesn’t select the entire column, but stops when it runs out of entries. Ctrl + Space selects the whole column of data, and this works in Google Sheets, too.
Paste without formatting
This works across all Windows apps. Select your cell, press Ctrl + Shift + V – from the drop-down Ctrl menu that appears, choose Match Destination Formatting to get just the data and not the colours or fonts. There’s a choice to keep your source formatting if you want to.
Similarly, it’s worth activating Windows 11’s clipboard history, and using Win + V to bring up a list of all your previously copied snippets. This can save a lot of time going back and forth.
Transpose
In the box where you join with Ctrl + Shift + V, there are a lot more options if you’ve copied a column or row of numbers from an Excel sheet. One of these is Transpose, which will paste a column as a row, or a row as a column.
Join Which? Tech Support
Which? Tech Support can help you keep on top of your home tech. Our experts explain things clearly so that you can resolve issues and feel more confident using your devices.
Get unlimited 1-2-1 expert support:
- By phone – clear guidance in choosing, setting up, using and resolving issues with your home tech devices.
- By email – outline the issue and we’ll email you our answer.
- By remote fix – we connect securely from our office to your home computer and resolve issues while you watch.
- In print – Which? Tech Magazine, six issues a year delivered to your door.
You can join Which? Tech Support for £49 a year or £4.99 a month.
Thanks to spreadsheet experts Mark Allen, Lisa Galliers, Charles Lambert, Jonny Martin, Chris Matthews, Michael Passingham, Adrian Porter and Will Stapley.
Additional reporting by Tom Morgan.
