Enter your contact information below to download the Nexus Managed IT Services buyers guide.
Want to become an Excel rock star? These tips will provide you with the knowledge you need to master this powerful productivity tool and become the go-to Excel expert in your office.
There are people in this world who are able to make spreadsheets practically sing: creating complicated charts and analysis without breaking a sweat and in only a few moments (or so it seems). And then there’s everyone else, struggling through creating basic formulas after trial and error but not truly understanding the power of the tool. Fortunately, Excel is relatively straightforward to use and there are plenty of websites offering tips and tricks to help improve your skills. This roundup will help you take your Excel skills from lackluster to power user in just a few minutes.
Quick Navigation
If you’re unfamiliar with Excel’s navigation idiosyncrasies, you may find yourself surprised that hitting Enter when you’re typing doesn’t necessarily do the same thing each time depending on where you are in the document and the other actions you’ve been performing. Once you gain comfort with the platform, this will become much more intuitive. Until then, here’s a quick cheat sheet showing some common Excel keyboard commands:
These tips alone can save you hours of time each week navigating your spreadsheets. Granted, it will take some time to begin using all of these shortcuts, but add a few each week and you’ll be scooting around the platform in no time!
Pivot Tables and Pivot Charts
Have you ever wondered how your Excel-loving friends are able to quickly and easily analyze vast quantities of data? Chances are, they’re utilizing Pivot Tables, an incredibly powerful analytic tool built right into Excel. Creating Pivot Tables is simple as long as you first ensure that all columns have a header and that you don’t have any blank columns within your data. Once you have your worksheet structured well, simply select the data you want to analyze and pop the resulting Pivot Table into a new worksheet. The new Excel tool for Recommended Pivot Tables allows even beginners to see the value of this powerful tool, which provides a way to cross-analyze a variety of data types such as costs and dates. Once you master Pivot Tables, Pivot Charts allow you to design visualizations of your data sets based on categories, data series and more.
Filter Your Data
The beauty of using Excel is that the functions available within the tools are not only powerful but often incredibly simple. Such is the case with Filters, which you can easily apply to any row of a spreadsheet to easily view only the data values that are applicable to a specific query. For instance, if you have a spreadsheet that is several thousand rows long, and you want to quickly determine how many of the values include “Tennessee”, simply add a filter to that column, display only the data that equals “Tennessee”, and you’ll see only the selected data displayed. Even better, you can see a count of the number of records that match your filter in the lower left-hand side of your screen.
Conditional Formatting
Long rows of data or dozens of columns can be difficult to look at, especially if you’re looking for how particular data points are different than others. Fortunately, conditional formatting provides users with a way to rapidly differentiate between a long row of values in a very colorful way. Get started by choosing a few cells or an entire range, then click the Conditional Formatting drop-down. From there, you can select how you want to discern between the values, with many users deciding to add color to a field based on whether the number has raised, lowered or is a certain quantity of units away from another number.
Get Creative with Paste
Copy, cut and paste are some of the most-used functions within the entire Office productivity suite, and for good reason. Users are always needing to move content around to a slightly different location on the page, but pasting between programs can be a real chore. If you need to copy information from the internet and paste it into your spreadsheet, for example, you may find that you’re retaining a great deal of the HTML formatting in the text as you bring it into Excel. This is rarely a “good thing”, so getting creative with the Paste function allows you to easily strip out that annoying formatting and only paste in the standardized text. The cleanest way to make the paste is to look for the icon that is a plain clipboard, which allows you to paste your content in a way that matches the formatting of your content’s destination. Alternatively, if you want to retain the formatting from the web, select to ‘Keep Source Formatting’. It’s important to note that this option often ends up with content that will need additional formatting by resizing columns or rows in order to have a cohesive look for your spreadsheet.
As you can see, there are plenty of ways to utilize Microsoft Excel to become even more effective and efficient in your daily workflow. Whether you’re a daily Excel user or a sometime analyst, these tricks let you get more out of this dynamic platform. Need to upgrade to a new version of Microsoft Office or considering a move to Office 365 for your company?