Microsoft Excel debuted in 1985, and over the last 38 years, the productivity app has become a powerhouse for financial analysis and business productivity. Whether you’re organizing key client information, creating data-driven charts and graphs, or tracking financial transactions, no tool tops Excel.
That explains why an estimated 150 million people worldwide and 1 million businesses in the United States use the app. But Excel is famously difficult to master for new users. And even experienced Excel gurus commonly make mistakes. Some of the most famous mistakes are legendary in the business world.
Examples include the $24 million a Canadian electric company accidentally spent in 2003 because of misaligned columns in a spreadsheet. Or the 16,000 positive COVID-19 test results that Public Health England left off a 2020 government report because a CSV file was improperly converted. Or the 179 extra contracts that Barclays purchased in 2008 when cells in a spreadsheet were hidden, not deleted. Or the 10,000 extra tickets sold for a 2016 London Olympics swimming event, all because a data entry specialist entered a “2” instead of a “1.”
We’ve all made spreadsheet mistakes — hopefully, none have been as consequential as those. But with proper training and education on columns, rows, formulas, and tabs, even Excel newbies can gain confidence using the application to interpret and manipulate data.
Here are 12 of the best tips and tricks that CMIT Solutions has compiled over the years to supercharge your Excel use and avoid common mistakes. (Note: some functions may work slightly differently in newer or older versions of Excel for Windows, Mac, and web-based Office 365.)
• Master common special characters. Check marks, bullet points, arrows, and other symbols can come in handy while using Microsoft Excel. Click Insert > Symbol to pull up a dialog box featuring hundreds of common options, or learn the keyboard shortcut for familiar characters like the check mark (Alt + V), bullet point (Alt + 8), or the rights reserved symbol (Alt + R).
• Insert several new rows or columns all at once. If you’re working with an ever-expanding data set, you’ll probably need to add more cells. Instead of executing this menial task one cell, row, or column at a time, you can make things more efficient by highlighting the number of pre-existing cells you want to replicate, then right-clicking and selecting Insert. If the addition doesn’t look right or you think you made a mistake, don’t worry — you can easily undo the action by clicking Edit > Undo Typing or using the keyboard shortcut Command + Z or Ctrl + Z.
• Use Flash Fill to clean up data. If there’s one underutilized feature in Excel, Flash Fill is it. This action saves time by detecting patterns in previously entered data and using that information to automatically fill in future cells. Flash Fill can be used to quickly format phone numbers or email addresses, clean up names, or extract unnecessary details. After entering something in your preferred format, press Enter, then click Data > Flash Fill, or press Command + E or Ctrl + E. Excel will sense the pattern you provided in the preceding cell, then format the following cells accordingly.
• Automate tasks with Macros. Instead of performing repeated manual functions, Excel empowers you to set up automated entries that avoid inconsistency, broken formulas, or accidental calculations. Start by creating a macro — click View > Macros > Record Macros — then execute the process you want to automate including 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.
• Highlight the data you want. 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.
• Use Format Painter to standardize your spreadsheet. Having trouble making the numbers in one cell match the format of numbers in another cell? There’s an easy fix right in the toolbar: Format Painter, which is typically represented as a paintbrush in the toolbar or by clicking the Format button. Choose the existing style of one cell — its shape, border, format, or function — by highlighting it and clicking the paintbrush, and then apply it to any other cells that need to maintain regularity and consistency by clicking that paintbrush. You can perform this function on a single cell, a row, a column, or even an entire tab.
• Go deeper with Conditional Formatting. This savvy spreadsheet task allows you to highlight cells and have them display a certain color, icon, or data bar, depending on the value of the information in the cells. Select the cells that you want to conditionally format. Then, click Styles > Conditional Formatting > Highlight Cell Rules and choose the criteria that align with your need from the drop-down menu. Popular choices include Data Bars, Color Scales, and Icon Sets, all of which can quickly provide visual assistance to any crowded spreadsheet.
• Hide or unhide the data you need. Need to remove a row or column from an Excel function without actually deleting that data? The app 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. Just be sure to review hidden cells before exporting the data to any other program — popular options like PDFs will automatically include the hidden cells.
• Try Pivot Tables. This advanced function often inspires dread in Excel novices, but Pivot Tables can provide a huge lift when you need to take a high-level 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 any component of your data. Or you can produce a Pivot Graph to display data in an easily digestible, aesthetically pleasing way.
• Get your data import right. Excel efficiently combines various types of data from all types of sources — but the critical step is importing your data correctly from the beginning so that any subsequent work is performed correctly. First rule: don’t just copy and paste complex data sets. Instead, assess the options listed under the Get External Data selection in the Data tab, including From Access, From Web, From Text, or From Other Sources. When you import a data set, review its format first before performing any other tasks. If it doesn’t look right, you can always undo it by clicking Edit > Undo Typing or using the keyboard shortcut Command + Z or Ctrl + Z.
• Review spreadsheet formulas before adding more data. If a coworker or colleague shares a spreadsheet with you, understand what you’re working with before editing or adding more data 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. If you see one in the spreadsheet that looks confusing, click that cell and then review the formula in the toolbar.
• Consolidate your Excel data for easy collaboration. Whether your business is large, small, or somewhere in between, you probably have more than a few spreadsheets containing critical Excel data that multiple users need to use. Sometimes multiple employees need to collaborate and share access to these. Make that process easy by housing spreadsheets in a single location: on a shared drive or in a secure cloud, where everyone can work from the same file. Office 365 makes this process a breeze, automatically saving work to local and remote locations and storing chronological versions in case an earlier file needs to be recovered.
Whether you’re a seasoned spreadsheet pro eager to jumpstart your Excel expertise or a Microsoft Office novice figuring out how to use the popular app, education and training are key. At CMIT Solutions, we empower organizations and employees across North America to work more efficiently and master business software.
If you want to know more about Excel or need to explore a Microsoft Office upgrade, contact CMIT Solutions today. We handle licensing, purchasing, and ongoing support so that programs like Excel can help you achieve better day-to-day productivity and boost long-term success.
