More than 30 years after its debut, Microsoft Excel remains the most widely used spreadsheet software on the planet. When you need to work with important business data, nothing beats Excel. Depending on your level of Excel expertise—and the knowledge your co-workers have with the program—problems can still pop up.
In 2003, a spreadsheet snafu caused Fannie Mae to lose more than $1 billion in shareholder equity. In 2012, JP Morgan’s $6 billion in trading losses were traced back to a manual copy and paste error. And during the 2012 London Olympics, more than 10,000 tickets were accidentally oversold for a synchronized swimming event.
That’s why Excel education is so important. The Microsoft Office software allows users to interpret and manipulate data in a million different ways—but only if you know what you’re doing with all those columns, rows, formulas, and tabs.
At CMIT Solutions, we know how important Excel is to your day-to-day operations. We’ve collected ten of our top tips and tricks to help boost your Excel use in efficient and productive ways. (Note: some functions work slightly differently in different versions of Excel.)
Rather than manually clicking on and dragging over the cells you want to highlight, select the first one, hold down Ctrl + Shift, then press either the down or up arrow to highlight all the data in a column, or press the left or right arrow to select everything in a row.
As you work with an ever-expanding data set, you’ll likely need to add more rows and columns. But instead of performing this tedious task one by one, you can get it done fast. Need three new rows? Highlight three pre-existing ones, then right-click and press Insert.
Use this handy tool (most commonly represented by a paintbrush in the toolbar or found under the Format button on the toolbar) to quickly copy formatting from one part of a spreadsheet to another. Select an existing style you like—a shape, a cell, a picture border, a type of text—then apply it to cells that need to maintain regularity and consistency.
Work performed over and over again has a tendency to become inconsistent. Beyond data entry mix-ups, finicky formulas or outdated calculations can derail a good spreadsheet. That’s why an automated entry is so important. From exporting data to other programs to producing charts and analyzing trends, Excel can do it. Start by creating a macro—click View > Macros > Record Macros—then go through the process you want to automate. Once finished, you’ll have an in-program recording of those routine steps so you can simply hit replay next time.
Excel can combine various types of data from all sorts of sources. The trick is importing that data properly so you can create drop-down lists or pivot tables from it. Don’t copy and paste complex data sets; rather, use the options from the Get External Data selection under the Data tab, where you’ll see a menu that includes From Access, From Web, From Text, or From Other Sources.
Want to remove a row or column without actually deleting it? Easy—simply select the whole thing by clicking the letter or number header, right-click, and select Hide. Need to reverse the operation? Also easy—select the columns or rows surrounding the hidden one, right-click, and select Unhide.
Although this advanced function can seem complicated to the untrained eye, Pivot Tables can provide handy summaries of a large set of data. Save a new copy of your raw data set, then select Insert > Pivot Table. Excel can also try out Recommended Pivot Tables to draw out certain assumptions about your data, or even create a Pivot Graph to make information more visually appealing and easy to understand.
No matter its size, your business probably manages multiple streams of Excel data. Multiple users can easily share and collaborate on that data, as well—as long as it’s housed in one single repository. Store spreadsheets on a shared drive, in the cloud, or using another collaborative tool so that all of your employees are working from the same Excel baseline.
Diving into a spreadsheet created by someone else? Don’t fret—you can easily orient yourself and find out which formulas are used by clicking the Show Formulas button (or by pressing Ctrl + ` on your keyboard) to display all of the formulas used in a workbook.
Need to deal with data that’s scattered across different tabs that require repetitive calculations? With the worksheet containing the data you want to copy open, press Ctrl and click on the tab of the worksheet you want to copy it to. Click on the cells you need, press F2, and press Enter. This will re-enter the data into the same corresponding cell in the other selected worksheet.
Whether you’re an experienced Excel pro looking for a few new productivity-boosting tricks or a rookie user scrambling to make sense of those confusing formulas, hopefully, these tips will supercharge your experience.
Curious about a Microsoft Office upgrade? Looking for other ways to make your technological life more productive and efficient? Contact CMIT Solutions today. We make technology work for your business, not against it.