Four decades after its debut, Microsoft Excel remains the most widely used spreadsheet software on the planet. Whether you’re organizing important business information, creating data-driven charts and graphs, or tracking financial transactions, no tool tops Excel.
That doesn’t mean this key component of the Microsoft Office suite is easy to understand, though. Depending on how long you’ve used Excel and how deep you get into its functions, formulas, and formatting, challenges can pop up on a daily basis. And those challenges can lead to serious problems.
In 2013, a student review revealed that two of Harvard University’s most preeminent economists relied on faulty spreadsheet data to recommend billions of dollars in budget cuts across the U.S. and European Union. In 1995, a plus sign accidentally entered in place of a minus sign led Fidelity’s Magellan fund to mark a $1.3 billion loss as a gain. In 2010, British intelligence agency MI5 admitted that they bugged the wrong phones because of a simple spreadsheet formatting error. And in 2012, $6 billion in trading losses were traced back to a single JP Morgan employee’s manual copy-and-paste error.
That’s what makes Excel education and training so important. Users can interpret and manipulate data in a million different ways. But success only comes if you know what you’re doing with all those columns, rows, formulas, and tabs.
CMIT Solutions has collected some of our favorite strategies to help boost your Excel use in an efficient and productive manner. (Note: some functions work slightly differently in different versions of Excel.)
Find yourself working with an ever-expanding data set? Chances are you’ll need to add more rows and columns. Rather than executing this menial task one cell, row, or column at a time, you can get it done quickly by highlighting the number of pre-existing cells you want to replicate, then right-clicking and selecting Insert.
This tip builds on the previous one: instead of performing manual functions over and over again, set up automated entries to avoid inconsistent data entry, finicky formulas, or misguided calculations. Start by creating a macro—click View > Macros > Record Macros—then go through the process you want to automate: exporting data to other programs, creating charts, or analyzing trends. Once you’ve completed the task, Excel stores it as a macro so you can simply hit repeat next time.
Instead of manually clicking on and then dragging your mouse 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. Either way, you’ll end up with the information you need, not a bunch of unnecessary cells.
Want to remove a row or column from an Excel function without actually deleting that data? The program makes it simple: just select the entire row or column by clicking the letter or number header, then right-clicking and selecting Hide. Need to run the operation in reverse? That’s also easy—select the columns or rows surrounding the hidden one, right-click, and select Unhide.
This advanced function often makes Excel novices tremble, but Pivot Tables can come in handy when you need a bird’s-eye view of a large data set. Save a new copy of the raw information, then select Insert > Pivot Table. Excel will also execute Recommended Pivot Tables if you want to analyze your data. Or you can produce a Pivot Graph to display data in an easy-to-understand, aesthetically appealing way.
This convenient tool is typically represented by a paintbrush in the toolbar. Alternatively, you can find it under the Format button. Either way, applying one spreadsheet section’s formatting to another becomes a breeze. Choose the existing style you like—a shape, a cell, a picture border, or a type of text, for instance—then apply it to any other cells that need to maintain regularity and consistency.
Excel efficiently combines various types of data from all types of sources—but the key here is to import your data correctly at the start so any subsequent work is performed correctly.
The first rule: don’t just copy and paste complex data sets. Instead, review the options listed under the Get External Data selection in the Data tab, including From Access, From Web, From Text, or From Other Sources.
If you’re working with a spreadsheet created by a co-worker or colleague, orient yourself before you start editing by displaying all existing formulas. Click the Show Formulas button (or press Ctrl + ` on your keyboard) to display all the different formulas currently in use.
Whether your business is big, small, or somewhere in between, you probably have more than a few spreadsheets containing critical Excel data that multiple users need to use. Make collaboration and sharing easy by housing those spreadsheets in a single location: on a shared drive, in the cloud, or with another collaborative tool. That way, all of your employees are working from the same Excel baseline—and everyone’s work is saved on a regular basis.
Want to dissect data that might require repetitive calculations but remains scattered across several tabs? 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 a seasoned spreadsheet pro looking to fine-tune your expertise or a Microsoft Excel novice scrambling just to make sense of all those bewildering functions, hopefully, these tips will enhance your Excel experience.
Want to know more? Curious about a Microsoft Office upgrade? Looking for other ways to supercharge your technology and run your business more efficiently? Contact CMIT Solutions today. We know how important Excel is to day-to-day business success, and we can help you leverage the software in productive, efficient ways.