The Top 10 Excel Hacks for Finance Professionals

Excel is a powerful tool for saving, presenting, and manipulating data, but not all finance professionals are using it as well as they could. Putting to use some basic Excel hacks and tricks, this powerful application can help significantly increase your productivity.

Did you know you can embed Word files in Excel workbooks? Have you tried using Conditional Formatting to help users navigate through your workbooks? Are you using the Protected View to prevent viruses and data breaches? This guide, consisting of 15 hacks for finance professionals, will provide easy-to-execute instructions for how to use Excel most effectively. Save your colleagues, your clients, and your own time by employing the tools you never knew Excel could offer.

1. Customizing Views

If you’re not the editor of a specific workbook, you may find that you have read-only access. But what if you only want to view a small part of the worksheet? How can you change the view of the worksheet to hide the data you don’t need without editing the content? One workaround is to manually change the view by hiding the columns you don’t need, changing the print range, or only printing the active sheets. But a much better solution is to create a custom view.

To create a custom view you’ll need the help of a user with edit permission. There’s a chance this user won’t know how to create custom views and is unaware of how safe the process is. Simply reassure them: It’s simple, will only take a few moments, and will save them valuable time.

The editor of the workbook will first need to save the original view. To do this, go to View, Custom Views, Add, name the original view, and click OK.

Next, select the data you need with File, Print Area, Set Print Area. Then repeat the procedure from step one to create a custom view. The writer should now select the original view with Custom Views, choose the original view, and click Show. If they save the file, they will always see this view when they open it and will never have to bother again with your custom view. Next time you open the file, go to Custom Views, open your custom view, and start working.

2. Drawing Shapes

If you’ve used other Microsoft Office applications such as PowerPoint or Word, you’re probably familiar with the Shapes feature. It’s a useful tool to visualize connections, guide readers through your document, and emphasize important points. But did you know that you can also use this tool in Excel to show how different cells relate to one another? For example, you could use this feature to illustrate how data in one cell flows into another cell by drawing an arrow: Go to Insert, Shapes, and choose an arrow shape. Click in the cell where you want your arrow to start and drag to the cell it should point to. With the shape still selected, open the Shape Format gallery tab, which will appear automatically on your ribbon. This set of tools allows you to customize your arrow: you can change the color, weight, and virtually every other aspect of the shape. Instead of arrows, you can draw a range of shapes depending on your needs. You can always come back to any shape you created if you want to change it. Click on the shape to select it, drag it around, or use the Shape Format gallery to change its appearance. Note that your shape will change size along with any changes made to cells by default. If this is not what you want and you want your shape to change only when you want it to, right click on the shape, choose Size and Position, Properties, and select Don’t Move or Size With Cells. Even if you change cells close to your shape it should now stay the same size.

3. Making It Easier to Navigate and Manipulate Spreadsheets

If your worksheets are relatively small you probably won’t have trouble navigating them. But what if your company uses worksheets with thousands of entries? If you need to make any modifications, finding or selecting the data you need can be a tedious task. And what if you want to add up large amounts of data in different cells while you lack in-depth knowledge of functions? Luckily, there’s an easy way to do this—it’s all in the keystrokes. Press CTRL + arrow. This will make Excel jump in the direction of the arrow, which is useful if you want to select an entire row or column. However, you may only need part of a row or column. To make your selection more specific, hold Shift + CTRL and press arrow to select the area you need. A common modification is adding up rows or columns (for example, to calculate your total sales figure). With a few keystrokes you can create a function to do this for you. First, select all the cells you need using the method described above. Select an empty row and column using Shift + Right Arrow and Shift + Down Arrow (not using the CTRL key this time). Next, go to Formulas, click AutoSum, and your selected empty cells will fill automatically with SUM= functions, calculating the total amounts for you.

4. Creating Charts

A particularly useful feature of Excel is the option to present your data as charts. This is an ideal tool to use for presentations, particularly if you’re sharing your data with less numerically-minded colleagues or clients. Explore the options by moving to the Insert tab and clicking on the drop down menu next to each chart type. This will give you a list of chart types you can select for your own use. Consider which type of chart will work best to support your presentation. If you’re not sure what chart would be best for your purpose, hover over the thumbnail to see a preview based on your data. In Excel 2016, you can also use the Recommended Charts menu to see previews of charts. By clicking OK, you will add the chart directly to the worksheet. If you’d prefer to have it as a separate worksheet, select the chart, click Move Chart, select New Sheet and click OK. In Excel 2016 you will find this option on the right side of the Insert tab on the ribbon. If you really like a chart you created you can save it as a template and reuse it for future projects. Save the chart as a .crtx file in the chart templates folder and you’ll be able to use it whenever you want, even in new workbooks. You can make the chart look more attractive and professional by using Excel’s extensive editing options. Click anywhere on the chart to bring up the Design dialogue box. You can select predefined layouts and styles, amend them, or create new ones from scratch. Designing charts can be a lot of fun, but don’t go overboard. Keeping your charts simple will improve their readability and help you communicate information more effectively.

5. Finding and Replacing More Effectively

Many users know about the Find and Replace option in Excel because it’s included in most Microsoft Office applications. Find and Replace helps you to look up data quickly and replace it with a few keystrokes if needed. However, using this feature in Excel can be surprisingly frustrating and the reason why is not always clear. Even when you’re sure a specific value is part of the worksheet Find and Replace may not be able to detect it. Why is this the case? Incorrect settings are a common cause of this problem. Unlike applications such as Word, Excel uses different types of data like text, values, and currency. This can cause Find and Replace to overlook the data you’re searching for because it’s incorrectly set for the search you’re trying to perform. To set this right, follow these steps: Go to the regular Find and Replace dialogue box. Change Within Sheet to Workbook if you want to search the entire workbook, rather than one sheet only. Other ways to amend your search and make it more specific include the option to search By Rows or By Columns, or Match Case for case sensitive data. Change Look In to Comments if you want to search comments rather than cells. This feature is useful if you use lot of comments in your worksheets to stay on top of changes or important information.

6. Using the Formula builder

Excel 2016 comes with a great new feature: the formula builder. Formulas and functions have always been one of the strongest selling points of Excel, offering virtually endless opportunities for calculation and data manipulation. But for people less familiar with functions or with limited mathematical skills, writing functions was complicated and discouraging. Excel 2016 now has a dedicated formula builder which makes selecting and writing the functions you need considerably easier and faster. To access the formula builder, go to the Formulas tab and click Insert Function. This will bring up the Insert Function dialog box on the right side of your screen. If you want you can grab it to move it to a more convenient place on your screen. You’ll see a list of available functions. Type in the name of the function you need. If you click on the name of a function you’ll get a short description to help you decide whether this function is right for your purpose. If this is the function you need, click on Insert Function. The formula builder will now ask you to specify parts of the formula. You can do this by typing data directly into the boxes or clicking on the cells that hold the data you want to use. The last option is particularly useful if you have little experience with functions as it helps you avoid syntax errors. If you click on the wrong cell or want to make changes, click on the correct one and your formula will be automatically updated with data in the right format. If you’re not sure what information you need, click on More Help With This Function at the bottom to get much more help and information. Prefer to write your functions manually? Excel 2016 still fully supports this option. And if you do get stuck, the Help function is more expansive than ever before and even includes sample tables to practice with.

7. Mastering the SUMIF Function

While the SUM function is probably the most-used function Excel has to offer, and applying it is easy thanks to the AutoSum function, it’s not always the best function for your needs. What if you only want to add up specific cells and disregard the ones you don’t need? If your worksheet is large, selecting the cells manually can be a tedious task. This is where the SUMIF function comes in handy because it only adds up the values that meet criteria specified by you. With the new Formula Builder creating a SUMIF function is easier and quicker than ever before. Start by selecting the cells you want the calculated totals to appear in, then go to Formulas and click Insert Function. In the Formula Builder menu on the right side of your screen, select SUMIF and click Insert Function. You’ll now need to specify the range (which cells you want to add up), as well as the criteria. For example, if you want to add up B1 to B2, specify B1:B2 as the range. If you only want to add up if B1’s value is larger than five, specify >5 and click done. To do the same for an entire row, select the cell with the SUMIF function, press CTRL + C, select the rest of the column, and click CTRL + V. This will automatically add up columns B and C, provided the values in B are larger than five. Like all functions created through the Formula Builder, you can always amend the SUMIF function by changing your criteria or clicking in cells you want the function to be applied to.

8. Connecting Excel Data to Data in Other Formats

You won’t always be able to have all the data you need conveniently saved in a single format or document. If you want to combine data saved in separate Word, PDF, and Excel documents, there are two routes you can take: embedding or converting. Depending on your data and goals either method eliminates the need to move back and forth between documents as you check your data. Most users won’t know that it’s possible to embed Word documents in Excel worksheets. This is useful if you want to add information you can’t display in a regular worksheet format, without the need for separate documents. Even if the Word document has multiple pages you can give readers a preview with the option to open the whole document in a separate window: Start by selecting where you want the top left corner of the Word document to appear. Click in this cell. Go to Insert, Object, Insert Object. Then choose Word document. Once you’ve selected the Word file it will open automatically in a separate window. Close this window down. You will see the Word document embedded in the Excel worksheet. Make sure your users know that they can open the full Word document, they may not be familiar with this option! Type instructions in a cell above the Word preview telling users that the document will open in a separate window when they double click on it. But what if you want to integrate PDF data into an Excel worksheet? Most users will already be aware of the option to export Excel data into a PDF. Surprisingly, there’s no straightforward way to do the reverse and convert a PDF to an Excel worksheet. Adobe Reader no longer offers this option in its free version, but then again, converting your files this way used to negatively affect the formatting. The easiest method to convert data from PDFs into worksheets is quite low tech but works well for small PDF files. To make sure you don’t lose your formatting, copy and paste your data from PDF into a Word document, then copy the data again and paste them from Word into Excel. Unfortunately, this will not work for very large or complicated data sets, for which you’ll need third-party solutions such as Able2Extract.

9. Removing Duplicates

When your workbooks grow and are edited by multiple people, you may end up with workbooks which contain duplicate records. This is bad news: It means the results generated when you analyze the data may be inaccurate. Furthermore, if you’re using a workbook to set up a mailing list some clients may receive the same message twice. To stay on top of your workbooks and keep them accurate, it’s important to get rid of duplicate records. Fortunately, Excel now has the functionality to do this automatically for you. Before you start, be very cautious. Removing duplicates is a destructive function, which means you could lose data if you use it carelessly. Always save a copy of your workbook before starting the process. Go to the Data tab and choose Remove Duplicates. Click OK and Excel will remove any duplicate values for you. Although this method is straightforward, you may accidentally lose data you actually want to keep. Perhaps you have two clients with the same name, in which case losing details of one of them would not be a smart move. To avoid this you can ask Excel to identify duplicates rather than remove them straight away. Start by selecting the range of values. On the Home tab choose Conditional Formatting, Highlight Cells Rules, Duplicate Values. The duplicate values will now be highlighted in red. If you’d prefer to keep the first occurrence un-highlighted, because this is information that you want to keep, go to Home, Conditional Formatting, More Rules, and use the drop down menu to select Use a Formula to Determine Which Cells to Format. Set up a COUNTIF formula to mark only the true duplicates, specifying the range and what to look for. An example formula is =COUNTIF($A$2:$A2, A2)>1. If you want the formatting to start at a different cell than A2, change the formula to refer to the cells you need. Once you’ve clicked OK only duplicates will be highlighted.

10. Using ‘Flash Fill’

Auto Fill, an option which fills cells automatically based on information you’ve previously added, has long been a feature of Excel. Excel 2013 and 2016 add a more sophisticated Fill option: the Flash Fill function. This option can save you a lot of time when you’re entering large amounts of data or if you’re using Excel to compile mailing lists. Like Auto Fill, Flash Fill works based on information you entered previously and detects patterns to help you add new information in a much quicker way. Unfortunately, this option is only available for Microsoft Windows users at this point. To use Flash Fill go to the Data tab and click the Flash Fill icon. You can also access it by using the CTRL + E keyboard shortcut. If this feature is enabled it will activate automatically if it detects patterns in your data. If, for example, you already have a list of email addresses and now want to make a list of names, Flash Fill will automatically suggest a full list of names after you’ve started typing. If you are happy with the suggestions press enter to apply. You can also click and drag the bottom right corner of the cell in which you typed your first entry to fill all the other cells that make up your list. Finally, if you right click on your first cell and select Flash Fill, other cells that are part of the list will fill automatically. If you don’t like this feature, you can always switch it off (or switch it back on again if you change your mind). Go to File, Options, Advanced, and tick or untick the Flash Fill box. Always check data generated through Flash Fill to make sure they are correct. The tool is mostly accurate but may occasionally get confused, especially when extracting names from email addresses.