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 techniques, Excel 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 may not know 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.
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 not ideal: 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.
11. Recording Macros
For years Excel has offered the option to create macros—sets of instructions to perform the same task repeatedly. For users with little or no coding experience, this was a complicated and error-prone process. Newer versions of Excel include the Record Macros feature which lets you record actions and converts them into Visual Basic code. Even if you have no knowledge of coding at all, you can now create macros quickly and easily.
Before you start, enable the Developer tab. Go to File, Options, Customize Ribbon, and select the Developer check box.
Enable macros by going to the Developer tab, Macro Security, Macro Settings, Enable Macros. Disable macros after you have finished recording your macros: malicious macro code can seriously damage your computer.
On the Developer tab click on Record Macro. Give your macro a name and choose a keyboard shortcut if you like.
Click OK to start recording and perform your tasks. When you’re done, go back to the Developer tab and click Stop Recording. Your macro is now saved and can be used whenever you need it.
Note that the Macro Record will record every action once you have started recording, including mistakes. You can modify the VBA code to correct mistakes, but if you’re not a confident coder this won’t be a simple task. Make the recording process easier by noting down the actions you’ll need to perform before you start recording and keep your instructions handy while you record your macro.
If you’re considering recording a macro to simplify a long and complex task, consider recording several short macros rather than recording one long one. This will significantly decrease the risk of errors.
12. Visualizing Data Without Charts
If you want to maximize the effectiveness of your presentations, convert your data sets into visualizations. This will give your audience, as well as yourself, a quick and easy overview of trends in your data.
If you don’t want to use charts, the Conditional Formatting menu is your ally: It gives you data bars, color scales, and icon sets. Data bars adds an in-cell bar chart to each cell and give cells with the larger values longer bars. Color scales add a mix of colors to each range, whereas icon sets do the same using icons instead of colors.
To add data bars to your cells, select the cells and go to Home, Conditional Formatting, Data Bars, and choose a style you like. You can use the built-in styles Excel offers or use the customize option to create your own theme.
Adding color scales works through the same dialog box, giving you the option to choose between two- or three-tone color scales. The former looks better when printed in black and white.
The same applies to icons: they can be hard to distinguish if printed in black and white. Unlike color scales or data bars, icons respond to the content of the cell. Because numbers tend to be right-aligned, icons are automatically left-aligned, which can cause confusion if they end up too close to the number in the adjoining cell. To change this, select the relevant cells and click on Format on the Home tab. Go to the Alignment tab and change alignment to Justify. This hack will move the numbers closer to the icons to clarify which icons and numbers go together.
Note that color scales and colored data bars are less clear if your visualization is not printed in color, so make sure to pick colors which are still easy to distinguish when printed in black and white to prevent confusion.
13. Forecasting with ‘One Click’
In the past, forecasting data in Excel was a tedious process to be attempted only by users with a good understanding of regression analysis. Excel 2016 has introduced a feature which makes it possible even for complete novices to use Excel for the prediction of trends. With One Click Forecasting it’s now possible to create forecasts with just a few clicks and keystrokes.
Say you want to forecast your sales figures for the next few months based on your performance over the past 12 months. Start by compiling a worksheet with all the data you need.
Click inside your range and click on the Data tab on your ribbon. On the far right you’ll find the Forecast section. Click on the Forecast Sheet option.
You’ll notice immediately that your forecast sheet doesn’t look very sophisticated. Luckily Excel offers plenty of options to customize your sheet and make your forecast more accurate.
One thing you can easily change is the forecast end date. If you decide you’d prefer to forecast trends for the next few years instead of months, change the date in the Forecast End Date box.
You can also set seasonality manually or ask Excel to detect it automatically. Choosing the manual option may give better results, so experiment with both.
Once you’re happy with your forecast click Create.
Note that this feature is only available for Windows users. If you’re using the Mac version of Excel 2016, or an earlier Microsoft version, you can still use the existing forecasting options.
14. Using Text and Text Boxes
Although you probably mainly use Excel to work with large amounts of numbers, you may occasionally want to include longer blocks of text, such as instructions or keys to help users understand complex worksheets. Rather than simple typing large blocks of text into a cell, it’s a good idea to format the text so that it doesn’t interfere with your values.
If you want to wrap the text to make it fit into a specific range of columns, paste the text, then select a rectangular range that holds all the text and some extra white space.
Go to the Home tab, select Fill, then Justify. Note that this command will not work if the cell contains more than 255 characters and that you’ll have to perform it again if you change the shape of any other columns.
If your text is too long to be formatted with a Justify command, consider using a text box. Start by going to the Insert tab and selecting the Text Box option, then draw a rectangle large enough for your text to fit into. Paste or type your text into the text box.
As you would in other Office applications you can now change your text to give it any look you want. In Excel the best way to do this is by selecting the text, right click and use the mini toolbar to change the color, font, and weight.
If you’re using a text box you can also adjust the number of columns your text box should cover (unlike the approach where you paste all your text into a single cell). Right click on the text box, choose Format Text Effects, and choose No Line in the Line category. If you go to the Text Box category you can specify the number of columns your text box should cover, integrating your text with the rest of your worksheet. By clicking outside the text box the borders will disappear and your text will float on the worksheet.
15. Securing Spreadsheets with ‘Protected View’
As every organization should know, online security is more important than ever before. One small mistake or misjudgment can affect an entire company, especially if the result is a virus or data breach. Companies are only safe as long as employees are aware of the importance of online security and apply safety instructions. Excel may not seem like a massive security risk at first sight, but Excel files are frequently shared and downloaded online without a second thought. This is where problems can occur.
To prevent costly mistakes from happening, Excel offers a Protected View which allows users to open and browse any file without the risk of virus infections. Functionality in this mode is limited, with no editing options, but once you’ve decided the document is safe to open you can switch to the Editing View and start working.
Protected View is only available in Excel 2010 and newer versions. If you’re using an older version of Excel you’ll need to decide whether you want to enable macros (taking a potential security risk) without being able to browse the file first.
Excel 2010 and newer versions open workbooks automatically in Protected View if they are downloaded from the internet, saved as a Temporary File, sent as an e-mail attachment, or if they fail validation.
If you want to change your Protected View settings you’ll need to open a document in Protected View and click on the yellow information bar to open the settings dialogue. This will allow you to select which documents you want to mark as trusted.
Changed your mind? Unfortunately you can’t change the status of one workbook, but you can untrust all workbooks by choosing File, Options, Trust Centre, Trust Centre Settings, Clear. Unfortunately this option is not available in Excel 2016 for Mac.
What Next?
With these hacks in your toolbox you’re well on track to becoming the Excel expert in your office. Still want to learn more? A good place to start is Excel’s built-in Help Center.
Excel 2016 has a sophisticated Tell Me What You Want to Do feature which helps users to complete the most complicated tasks. You’ll find it at the top of your screen, and all you need to do is type in your query to see suggestions to solve your problem.
If you’re using an earlier version that doesn’t have this feature, Excel Help can still save you time. Click on the blue question mark to bring up the full Help Centre with a fully searchable database of frequently asked questions and solutions.
One useful tip is the full list of keyboard shortcuts which you can access by simply searching for “keyboard shortcuts.” Keep this list handy to save yourself the time you’d spend looking for the Excel tool you need. But most importantly, each time you’re about to start a tedious task, stop and ask yourself whether there’s an easier way to do it. Chances are Excel already has you covered—you just need to know the right hack.
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.