Microsoft Office is fairly easy to use if you stick to the basic features, but knowing a few tricks and shortcuts can help you get even more out of programs like Word and Excel. Luckily, SETWorks team members have lots of experience across the software space, so today we’re sharing our go-to tips, tricks, and shortcuts that we use regularly in Microsoft Office.
SETWorks Support Manager Kevin Grunwald, who used to work for Microsoft, shared this helpful tip for anyone who doesn’t have access to Microsoft Office: You can use the online version for free by creating an account. While the free version doesn’t have all the features of Office, it does include the most common functionality.
In Microsoft Office, the ribbon is the bar at the top of the window that contains different tools and commands. You’re probably familiar with using the ribbon to format text or insert an image. But did you know that you can also customize the ribbon to your preferences? This allows you to see the tools you use the most often at a glance.
According to SETWorks Client Operations Director Preston Finkle, the #1 Microsoft Office tip people should know is how to sort and filter data. SETWorks co-CEO and founder David Lindell agrees that filtering and sorting in Excel is “magical” when analyzing and working with data.
Sorting is useful when you want to rearrange your data in different ways. For example, you could sort clients by location, department, or last name. Meanwhile, filtering allows you to temporarily hide data so you can focus on the information you want to see. While filtering is available only in Excel, you can also sort the contents of a table in Word.
Another great feature in both Excel and Word is the ability to find and replace text or numbers in a worksheet or document. This allows you to quickly locate something in your file, such as a specific date or name. You can also replace the text with a different value; for example, if you want to find all the clients that are assigned to a specific employee and reassign them to someone else.
To speed things up even more, you can use the keyboard shortcut Ctrl + F to open the Find dialog box. Or, use the shortcut Ctrl + H to bring up the Replace dialog box.
Speaking of shortcuts, there are lots of other keyboard shortcuts in Microsoft Office that will save you time and help you get your work done more efficiently. For example, if you’re putting together a report, you might need to copy and paste data from Excel or an image from a Powerpoint presentation into a document. You can use the keyboard shortcuts Ctrl + C to copy the selected item and Ctrl + V to paste it between different programs.
Some other keyboard shortcuts that come in handy include:
SETWorks Support Specialist Keegan Comstock, who also worked at Microsoft, adds that Alt+ lets you use shortcuts from older versions of Microsoft Office.
When you’re working in Excel, you may want to adjust your columns and rows to fit the data. However, dragging each one separately to size can be tedious, to say the least. Instead, SETWorks Onboarding Manager Mat Koelling shared this trick: Click in the top left corner to highlight the whole sheet, then double-click the border between any two columns or rows to automatically adjust everything.
Another feature in Excel is the ability to transpose data from rows to columns. This is useful if you have a worksheet with data in columns and you want to rearrange it so that the data is displayed in rows instead. For instance, let’s say you had your data arranged with dates listed across the top and other categories across the left side. You could rotate this to show categories across the top and dates listed across the left side instead.
When you scroll through a large worksheet, you lose sight of the top row that contains your column headings — which can make things confusing. Keep the top row visible by using the “Freeze Panes” feature. Now, when you scroll through the spreadsheet, your headings will stay at the top.
This feature is also useful when you want to compare two rows or columns of data that are not next to each other. Simply freeze the first row or column you want to compare. This row will stay in a fixed location while you scroll to the other row or column you want to compare it to.
When you’re copying and pasting information in Excel, you might want to paste the result of a formula but not the formula itself. Or, you may want to copy values but not formatting. In that case, Preston recommends using the “Paste Special” feature. This feature allows you to choose what you do (and don’t) want to paste. For example, “Paste Values” will remove all formulas and formatting and only copy values.
Sometimes a single cell or column contains a large chunk of data that needs to be broken up into different columns; for instance, if you have a cell that contains both a first and last name and you want to split these into two separate cells. SETWorks Marketing Manager Anna Spexarth loves using the “Convert Text to Columns” feature in Excel. As the name implies, this feature allows you to convert text into multiple columns, creating different columns for each separate piece of information.
SETWorks co-CEO and founder Henri McCracken finds the “Remove Duplicates” feature particularly useful. This feature allows you to quickly remove duplicate values without having to manually comb through the spreadsheet. If you’d prefer to remove the values manually, you also have the option to use conditional formatting rules to highlight duplicate values so you can decide if you want to delete them.
Functions are predefined formulas in Excel. Using functions saves you from manually having to write out formulas yourself. For example, the AVERAGE function quickly calculates the average of a range of cells.
Excel has hundreds of different functions built in, but some of our team’s favorite functions include:
Another handy trick from Henri is to use the dollar sign ($) in functions to make that row or column ‘sticky’. In other words, if you copy that function to another cell, whatever is referenced next to the dollar sign will stay the same. (This can get a little confusing, so you can read more about cell references here!)
By default, values entered in Excel are displayed the way you type them. However, you also have the option to apply different number formats such as dollars, percentages, dates, phone numbers, and even Social Security numbers. For example, if you are entering dates in a worksheet and you type ‘09012022’, you can use the Date format to display this value as ’09/01/2022′ or ‘September 1, 2022’.
SETWorks Client Success Manager Jocelyn Leatherman says that learning pivot tables in Excel was a “game-changer”.
If you’re not familiar with pivot tables, they’re a quick way to summarize and analyze data so that you can see patterns and trends. As the name implies, they “pivot” or turn the data to show it a different way without actually changing the spreadsheet.
If you want to see expiring authorizations, for instance, you could run a pivot table on consumer name and see how many consumers have expiring authorizations with how many each consumer has uniquely. This is much faster than having to count individual authorizations by hand.
Many people use OneNote as a scratch pad, but you can do so much more with this tool than just jot down notes. For one, Kevin points out that you can create libraries of notebooks. This is useful when you want to organize a bunch of different notes so you can find the information you need quickly. You can also assign tags or add links to other notes, which makes finding relevant information much easier.
Our last tip isn’t for Microsoft Office specifically, but it has made all the difference when we need to grab a screenshot from one of these programs. Jocelyn loves using Lightshot to capture and edit screenshots for emails or Knowledge Base articles for clients. The best part is it’s completely free and works with the PrtScn button on your keyboard.
If a screenshot doesn’t do the job, there’s also ScreenToGif, a free gif creation tool that lets you record your screen as a gif animation.
After reading all these tips and tricks, we hope you found at least one idea that will help you use Microsoft Office more effectively. For more ideas on how to adapt to the demand to become tech-savvy, download our free guide, “5 Ways To Become A Data-Driven Organization”.
Going to a conference? These four tips and tricks will help you maximize your time and make the most of the opportunity.
Cerebral Palsy Awareness Month is an annual event to raise awareness for a condition that affects one million Americans.
One in 25 people worldwide are living with a rare disease.
"*" indicates required fields
