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.)
1. Select the data you want — and fast.
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.
2. Insert more than one new row or column.
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.
3. Use Format Painter to visually organize and format your information.
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.
4. Automate tasks that typically require manual entry.
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 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.
5. Import your data correctly the first 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.
6. Hide or unhide important data.
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.
7. Give Pivot Tables a try.
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.
8. Warehouse your Excel data in one centralized location.
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 its 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.
9. Display Excel spreadsheet formulas.
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 press Ctrl + ` on your keyboard) to display all of the formulas used in a workbook.
10. Copy formulas or data between worksheets.
Need to deal with data that’s scattered across different tabs that requires 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 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.