Quick, what’s your favorite Excel formula?
Is it the tried and true old faithful “SUMPRODUCT” formula we’ve all grown to love, or maybe once Microsoft added the “SUMIFS” formula in Excel 2007 you never looked back? Perhaps you got tired of always having to create a lookup column in the left most column of your table for your “VLOOKUP”, and once you discovered the powerful “INDEX” and “MATCH” combination your heart was set?
This article is the first in a series of articles that is going to transform how you use Microsoft Excel. This first article will walk through how to setup Excel so it can be used efficiently and give tips to shorten the time to perform certain tasks. The future articles to be published will attempt to explain best practices in formula writing, common pitfalls of excel programing, formatting tips, macro writing, and best practices in model building. These articles are written for actuaries and non-actuaries, excel beginners to advanced users. Though we are healthcare actuaries, these practices go well beyond healthcare or actuarial science.
Actuaries have many job functions; pricing, forecasting, reserving, financial analyses, trend analyses, etc. However, no matter the job function, the most commonly used (and sometimes overused) tool in the actuarial toolbox is good old Excel. Need some analysis done? No problem, just open a blank workbook and away you go. Not long after it becomes clear that Excel has so much versatility regarding functions, formulas, and formatting options, it can be confusing on which is the best or most efficient. Soon after, questions like, “How do I add these cells together?”, “How do I reference cells from other tabs?” or, “What’s an array/CSE formula?”, “How do I include a new sheet in a 3D sum?”, and “What’s a Pivot Table?” begin to emerge; and don’t even get me started on hotkeys or writing Macros.
Even though the focus of actuarial work is to apply business knowledge in various analytical capacities, and not to be an Excel MVP, having a good working knowledge of the program is essential.
Excel can be as simple or as complex as one wants it to be, and a good general rule of thumb is to keep it at as simple as possible. Unfortunately, this isn’t always possible. Analyses can get complex or the file size can become so large that Excel calculates slowly or even crashes. Also, some work can be very repetitive and a Macro could be used to automate it. In cases such as these, simplicity can very easily go right out the window.
Even though the focus of actuarial work is to apply business knowledge in various analytical capacities, and not to be an Excel MVP, having a good working knowledge of the program is essential. There are many tips/tricks/ideas that can improve how Excel is used and workflow. In the following sections will present techniques to help make the everyday use of Excel easier and more efficient.
First, let’s get PERSONAL
Probably because you must initially create it, and even then, it’s hidden, but it’s a little-known fact that Excel can create a personal workbook (by default it’s conveniently named PERSONAL, and yes, it’s capitalized) to help save information or Macros. A personal workbook is an entirely separate workbook that by default is saved on the user’s computer and is automatically opened when Excel opens. Even though it is hidden it is fully accessible and can be used just like any other workbook. It is specific to each user and won’t be shared with anyone else. If you’ve ever wanted to have easy access to things like notes, lists, relational database information, or maybe write a program to automate repetitive actions that are always available to use (in Excel terms this is called a Macro), the PERSONAL workbook is a great solution. Since the PERSONAL workbook is always running in the background, just unhide it, save the information or use the visual basic editor window to write the macro you’d like to keep around, then hide it again.
Here’s a simple way to create a personal workbook.
- Make sure Excel is showing the developer tab to make it more efficient to record a macro.
- Go to File>>Options>>Customize Ribbon
- In the right most box make sure the check box next to “Developer” is checked then press Ok.
- Go to the newly revealed Developer tab on the Ribbon
- In the Code section, click on Record Macro
- In the “Store macro in” drop box select “Personal Macro Workbook”
- Go back to the same Code section, click “Stop Recording”
At this point the PERSONAL workbook is running in the background and will open every time you open Excel. Now when you want to see it you’ll just need to unhide it. To unhide and view the personal workbook follow the steps below:
- Go to the View tab in the ribbon
- In the Window section click Unhide
- Select “PERSONAL” and click ok
- To hide, activate the PERSONAL workbook and select Hide in the same Windows section
For the more advanced users that want to know where the workbook is stored follow these steps.
- Press Alt+F11 to open the visual basic editor window
- Press CTRL+G to open the immediate window
- In the immediate window, type: “?Application.StartupPath” (without the quotation marks), press enter and the path will pop up
Wait, where was that button again?
Excel has so many functions and formulas available, it is hard to know or remember them all. Many of the functions are found in menus and it can be cumbersome to remember where they even are and how to get back to them. Frequently used function like: sort and/or filter data, remove duplicates, goal seek, insert/delete rows or cells, show/hide guidelines, protect/unprotect sheets or cells, are helpful to use, but can be a game of hide and seek to find. The Quick Access Toolbar is a useful tool in excel that puts many of your favorite functions in an easy to use/find place.
The Quick Access Toolbar is what its name implies; a toolbar meant to allow quick access to common functions/procedures so you don’t have to hunt for them.
By default, at the very top of Excel there are small icons, including a little floppy disk icon that when clicked will save your file, and two circular arrows, one to undo and one to redo an action. This is the Quick Access Toolbar and is a very useful, but seldom used resource. You can add almost any Excel function to this toolbar so you’ll never need to go looking for the button or menu item again. Often time things filters, sorts, page setup, or paste values are added to the Quick Access Toolbar.
To add/remove icons to the Quick Access Toolbar
- Click the downward pointing arrow in the toolbar that looks like this then click on “More Commands” or go to File>>Options>>Quick Access Toolbar
- In the Choose commands from drop down select “All Commands”
- In the Customize Quick Access Toolbar make sure “For all documents” is selected
- Find the function you want to add, click it, and click the Add>> button to add it to your toolbar
- At the bottom, you can check the box “Show Quick Access Toolbar below the Ribbon” if you like. Having the toolbar lower makes it slightly more convenient to use.
Mouse? What Mouse?
Work around those who use Excel frequently and there is usually at least one person who makes extensive use of the infamous Excel hotkeys. The Excel hotkeys are sequences of key strokes that when combined, perform the actions of the buttons we’re all used to pressing. Gain enough understanding of the hot keys and someone can manipulate Excel almost entirely with them and greatly eliminate the use of their mouse.
Want to highlight table headers across columns? You could either click and drag to the end of the header or you could use Excel hot keys and start at the left most cell and press CTRL+Shift+Right Arrow Key. Want to add a filter? The most basic way is to click the “Data” tab and under the “Sort & Filter” section click the “Filter” button. When using the Excel hotkeys, you can have the same outcome by pressing CTRL+Shift+L.
There are many Excel hotkeys, and they are very well documented. A quick Google search will yield all the hotkeys someone’s heart could desire. Instead of attempting to mention them all, I have included a few simple favorites in the table below.
|Hold Ctrl + Up (Down, Right, or Left ) Arrow||The control butt on tells Excel to move the active cell to the end of conti nuous data, and the arrow keys tell it which directi on you want it to go. If you press Ctrl + Down in a blank workbook you’ll find your cursor in the last possible row Excel can handle. If there are blanks in a list of items this command will take you to the first blank cell.|
|Hold Ctrl + Hold Shift + Up (Down, Right, or Left ) Arrow||This is almost the same as the previous sequence except with the addition of the Shift key. This key tells Excel to highlight the cells in the path. When pressing Ctrl + Shift + Down Arrow key in a blank workbook, not only did Excel go to the very last row but it highlighted every cell along the way.|
|Hold Ctrl + Page Up or Page Down||Page Up will toggle up sheets in a workbook and Page Down will toggle down|
|Hold Ctrl + Z or Y||Using Z will undo the last action, Y will redo that action|
|Hold Shift or Hold Ctrl +
|Using Shift will highlight an entire row, Ctrl will highlight an entire column|
|Hold Ctrl + Hold Shift +
“&” or “_”
|Using “&” with create a border around the active cell(s). Using “_” will remove the border|
Excel is one of the most used programs out there when it comes to data analysis and spreadsheets, and for good reason. It is extremely versatile, functional, and customizable. It has more functions then most people realize, it can connect to databases to pull in data, and there are even add ins that can expand its functionality further. However, with all its usefulness, it can be confusing on how best to use the program. Many users acquire their knowledge on the fly in a working environment where using Excel efficiently isn’t the priority. Although everyone has their own style, there are also good tips and best practices, like the three mentioned in this article, that can help make Excel a little easier to use for everyone.
About the Author
Sean Lorentz, ASA, MAAA is a Consulting Actuary at Axene Health Partners, LLC and is based in AHP’s Murrieta, CA office.