Sub Total
Excluding delivery and voucher code discounts.
Go to basket

Free Delivery on all orders to UK mainland within 3 working days.

HP Tech@Work
Today's trends for tomorrow's business
 Top 16 Microsoft Excel Tips and Tricks

Top 16 Microsoft Excel Tips And Tricks

Tom Gerencer
Microsoft Excel is one of the most powerful programs most people don’t fully use. Many of us use Excel for work daily, but miss the many resources to improve our productivity right in front of our eyes. But the good news is that it doesn’t take long to improve your Excel spreadsheet skills.
Take a look below at these efficiency-boosting tips. You’ll learn how to pull data from other sheets, use filters like a pro, automate your cell data, improve poorly thought-out legacy spreadsheets, and so much more.

1. Paint format

One of the quickest, easiest Excel tips and tricks is the format painter feature. Instead of telling each cell (or group of cells) what colours, fonts, and format to use, just copy-paste the format from another cell. Here’s how to format cells faster:
  • Click a cell with the formatting you want to copy.
  • On the Home tab, click the paintbrush icon in the upper right.
  • Click or select the cell or cells you want to paste the format to.

2. Filter your data

Using data filter in Excel
Sometimes you only want to see the rows that contain a certain last name, pay scale, topic, or other detail. You can do that with less scrolling and squinting if you use the filter data function.
  • Click or select the row, column, or range you want to filter.
  • Select the Data tab up top.
  • Click the Filter button with the funnel icon in the toolbar.
  • In the cell range, click the arrow.
  • In the drop-down menu, select the data points to filter.

3. Clip or wrap text

Spreadsheets with long links or blocks of text can be unwieldy. To fix that, you can choose to clip the text or wrap it if you’d rather see the full picture.
  • Select a cell or range of cells.
  • In the Home tab, above the word Alignment, click Wrap Text.
  • Click once to wrap the text or a second time to clip it.
Here are Microsoft’s instructions for how to wrap text in Excel.

4. Use formulas and functions

Here are some of the best Excel formulas to make your spreadsheets more efficient:
  • SUM, AVERAGE, COUNT: Add the values in a group of cells, take their average or count the cells that have a number value.
  • IF: Create a flag if data meets a certain condition, like if a cost is over or under budget.
  • CONCATENATE: Combine text from two cells or columns, such as a first and last name. (To avoid typing “concatenate,” just type “&” instead.)
  • MAX or MIN: Find the max or min number in a range.
Creating a formula in Excel
Here’s a full list of all Excel functions and a guide for how to use Excel formulas. To find a formula that fits your needs, open the list and do a CTRL-F word search for your terms.

5. Autofill cells

There’s no need to type similar but slightly different text into Excel if it fits a pattern. You can autofill dates, a series of numbers, or other patterns like this:
  • Select the cells that have the data pattern you like.
  • Drag the fill handle in the lower right of the range.
Here’s how to use Excel autofill in detail.

6. Automate with IF

One of the most popular Excel functions, IF gives you massive granularity to gain insights into data. To use it, enter data with the syntax below into a cell. You can make the formula as complex as you like.
  • Syntax: =IF(D3>C3,"Over Budget","Good")
  • What it means: If D3 is greater than C3, the cell will read “Over Budget.” Otherwise, it’ll read, “Good.”
See this article on how to use Excel IF statements.

7. Flash fill

Sometimes Excel will spot a pattern in your data as you enter it – kind of like autosuggest on your phone. To use it, all you have to do is hit Enter when you see suggested text pop up in your cells.
  • You can also click Data, then Flash Fill to start the process manually.
See this guide to how to use Excel Flash Fill.

8. Quick select

Selecting cell contents in Excel
If you want to select a group of cells, rows, or columns without mouse-dragging, use the mouse pointer and the shift key. This cool Excel trick comes in handy when you need to select across a wide range that extends outside the screen without suddenly bopping down to row 10,000.
  • Click on a cell with the mouse pointer.
  • Hold down the shift key and click another cell.

9. Split text or columns

What if you have a legacy spreadsheet with first and last names in the same column or other data lumped together? If you need to separate that data, just tell Excel to split the text like this:
  • Select the column or cell you’d like to split.
  • Click Data then Text to Columns.
  • Use the wizard to zero in on how you’d like to split your text.
See this guide on how to split text in Excel.

10. Change rows to columns

You may enter data in rows, then realize that it should be in columns. Don’t retype it. Instead, use Paste Special and Transpose to switch to columns. You can also change columns into rows.
  • Select your rows or columns.
  • Press Ctrl+C.
  • Right-click a new cell.
  • Under Paste Options, hover over the icon for Transpose and click it.
Here are the complete steps to change rows to columns in Excel.

11. Tag someone in a comment

Use a mention to tag someone for feedback
Have you ever tried to call someone’s attention to a cell when they don’t have the spreadsheet open? You don’t need screenshots when you can tag someone in a comment. This one comes in handy when you’re working on Excel files with a team.
  • Right-click the cell.
  • From the popup, click New Comment.
  • Type @ and a team member’s last name.

12. Many cells, same data

Imagine you just want to take one value and pepper it into several scattershot cells. This can be tricky when the cells don’t line up neatly in a row.
  • Click the cell with the data you’d like to copy.
  • Press Ctrl+C.
  • Hold the Ctrl key while you click as many blank cells as you like.
  • Hit Enter.
  • That value will show up in all the cells you clicked.

13. Conditional formatting

It’s easy to make cells turn a different colour to call out numbers over or under a certain value, past due items, or any other details. For instance, you could make cells in your daily schedule turn blue to show you’re past a certain hour in the day.
  • Select a range of cells.
  • In the Home tab, select Conditional Formatting.
  • Choose parameters to highlight.
  • Select colours and other formatting changes to apply.

14. Lock cell references

It’s so handy to write a formula (like units x price = cost) and then drag to fill it in a range of cells without retyping. But what if you want to refer to a single cell through all that dragging? Just use the dollar sign to lock cell references.
  • In your formula, type a $ before the column, row, or both, like this: $A$5 or $A5 or A$5
  • The location after the $ will stay the same in every cell you drag it to.
See the full guide to how to lock Excel references.

15. Pull data from another sheet

Let’s say you want to share some data in a spreadsheet, but not all. No problem. Excel lets you pull data from one sheet into another.
  • Select the cells where you want the data to appear.
  • Type a formula like “=SUM(” in the formula bar.
  • Switch to the sheet you’d like to pull the data from.
  • Select the cells you’d like to reference and hit Enter.

16. Hide rows or columns

You can hide or show rows or columns
Nobody loves unmanageable data in massive, endlessly scrolling spreadsheets. If you have outdated data (like last year’s budget numbers) and you’re tired of scrolling through it, hide the range.
  • Select the rows or columns that you’d like to hide.
  • Right-click, then choose Hide.
  • To unhide, right-click nearby columns and click Unhide.

More of the Best Excel Tips

Need a few more tips? Click below to see the official Microsoft how-to guides on how you can:
Many of the same tips and tricks in this article also work in the Google Apps answer to Excel: Google Sheets.


Microsoft Excel is a powerhouse of hidden functionality. The Excel quick tips above will take your spreadsheet skills to the next level and drive your efficiency through the digital roof. Whether tagging people in comments or filtering your data, you’ll open up new data-crunching worlds with these tricks.
About the Author: Tom Gerencer is a contributing writer for HP Tech Takes. Tom is an ASJA journalist, a career expert at, and a regular contributor to Boys' Life and Scouting magazines. His work is featured in Costco Connection, FastCompany, and many more.

Disclosure: Our site may get a share of revenue from the sale of the products featured on this page.


Prices, specifications, availability and terms of offers may change without notice. Price protection, price matching or price guarantees do not apply to Intra-day, Daily Deals or limited-time promotions. Quantity limits may apply to orders, including orders for discounted and promotional items. Despite our best efforts, a small number of items may contain pricing, typography, or photography errors. Correct prices and promotions are validated at the time your order is placed. These terms apply only to products sold by; reseller offers may vary. Items sold by are not for immediate resale. Orders that do not comply with terms, conditions, and limitations may be cancelled. Contract and volume customers not eligible.

HP’s MSRP is subject to discount. HP’s MSRP price is shown as either a stand-alone price or as a strike-through price with a discounted or promotional price also listed. Discounted or promotional pricing is indicated by the presence of an additional higher MSRP strike-through price

The following applies to HP systems with Intel 6th Gen and other future-generation processors on systems shipping with Windows 7, Windows 8, Windows 8.1 or Windows 10 Pro systems downgraded to Windows 7 Professional, Windows 8 Pro, or Windows 8.1: This version of Windows running with the processor or chipsets used in this system has limited support from Microsoft. For more information about Microsoft’s support, please see Microsoft’s Support Lifecycle FAQ at

Ultrabook, Celeron, Celeron Inside, Core Inside, Intel, Intel Logo, Intel Atom, Intel Atom Inside, Intel Core, Intel Inside, Intel Inside Logo, Intel vPro, Itanium, Itanium Inside, Pentium, Pentium Inside, vPro Inside, Xeon, Xeon Phi, Xeon Inside, and Intel Optane are trademarks of Intel Corporation or its subsidiaries in the U.S. and/or other countries.

In-home warranty is available only on select customizable HP desktop PCs. Need for in-home service is determined by HP support representative. Customer may be required to run system self-test programs or correct reported faults by following advice given over phone. On-site services provided only if issue can't be corrected remotely. Service not available holidays and weekends.

HP will transfer your name and address information, IP address, products ordered and associated costs and other personal information related to processing your application to Bill Me Later®. Bill Me Later will use that data under its privacy policy.

Microsoft Windows 10: Not all features are available in all editions or versions of Windows 10. Systems may require upgraded and/or separately purchased hardware, drivers, software or BIOS update to take full advantage of Windows 10 functionality. Windows 10 is automatically updated, which is always enabled. ISP fees may apply and additional requirements may apply over time for updates. See

“Best All In One Printer” and “the easiest printer you’ve ever had to set up” from Wirecutter. ©2020 The Wirecutter, Inc.. All rights reserved. Used under license.

Get Marvel’s Avengers when you purchase HP gaming PCs with qualifying 9th gen or 10th gen Intel® Core™ i5, i7 and i9 processors. Redemption code will be sent out by email within 60 days of purchase. Limited quantities and while supply lasts. Offer valid thru 12/31/2020 only while supplies last. We reserve the right to replace titles in the offer for ones of equal or greater value. Certain titles may not be available to all consumers because of age restrictions. The Offer may be changed, cancelled, or suspended at any time, for any reason, without notice, at Intel’s reasonable discretion if its fairness or integrity affected whether due to human or technical error. The Offer sponsor is Intel Corporation, 2200 Mission College Blvd., Santa Clara, CA 95054, USA. To participate you must create an Intel Digital Hub Account, purchase a qualifying product during the redemption period, enter a valid Master Key, and respond to a brief survey. Information you submit is collected, stored, processed, and used on servers in the USA. For more information on offer details, eligibility, restrictions, and our privacy policy, visit

© 2020 MARVEL. © Intel Corporation. Intel, the Intel logo, and other Intel marks are trademarks of Intel Corporation or its subsidiaries in the U.S. and/or other countries. Other names and brands may be claimed as the property of others.

The personal information you provide will be used according to the HP Privacy Statement (