HP Tech@Work
Today's trends for tomorrow's business
Subscribe
Top 16 Microsoft Excel Tips And Tricks
Tom Gerencer
|
February 24, 2022
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.
See the full paint format instructions.
2. Filter your data
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.
See the full filter data instructions.
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.
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
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.
See this article for tips and tricks to select cells in Excel.
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
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.
See more on how to tag someone in a comment in Excel.
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.
See the full list of Excel conditional formatting instructions.
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.
Get all the tips you need for how to pull data from another Excel spreadsheet.
16. Hide 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.
See more on how to hide rows and columns in Excel.
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:
- Add graphics to charts
- Add dropdowns
- Insert screenshots
- Link Excel to Word
- Reformat to percent (without formatting glitches)
- Use the best Excel keyboard shortcuts
- Use macros between sheets
- DIY data analysis with pivot tables
- Freeze rows
- Use Excel templates
- Easy data validation
Many of the same tips and tricks in this article also work in the Google Apps answer to Excel: Google Sheets.
Summary
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 Zety.com, and a regular contributor to Boys' Life and Scouting magazines. His work is featured in Costco Connection, FastCompany, and many more.
