The NASPP Blog

In an ideal world, stock plan managers would have so much automation that they would rarely, if ever, need to use Excel. The reality is that Excel is probably an essential part of everyday life for most stock plan management teams. If you haven’t already, it’s a great idea to take a course in Excel. Excel is an amazing tool, and has a lot more functionality than most of us will ever know. I find it especially helpful when other users share with me their favorite tips and which functions they are using regularly. In that spirit, here are my top five tips for using Excel:

Tip #1: When writing a complex formula, write each function separately first before combining them.

Often, the function that will bring you the results you are looking for in Excel isn’t one function at all; it’s several combined together. When you are writing a complex formula with multiple functions, keeping track of where you are (especially making sure you have the right number of parentheses) can get confusing. In order to make sure that the result you are getting is based on the correct formula assumptions, or to avoid an error that may be difficult to pick apart, build your formula piece by piece the first time. Put each function within the formula in a different field and check that it returns the correct result. Then, combine them and confirm that your combined formula brings back the same result before you populate the rest of the fields where that formula will be used.

Tip #2: Reference the entire column when using functions like Vlookup.

One of the steps in creating a Vlookup function is to indicate where the function should look to find the results you want populated in the function field. If you highlight just the area you want the formula to search in and then copy that formula down the entire column, you run the risk of it returning an error message after a certain point because Excel will automatically move the search area down as well. By far the quickest and easiest way to keep this from happening is to simply build your formula so that it searches the entire column (or columns) that contain the data set you want to search in. Not only does this eliminate the problem of moving search areas, it is also much faster to highlight an entire column than to highlight just the area you are looking for!

Tip #3: When you are working with a lot of data, remove the actual formula from your worksheets.

When you use a function in Excel, it doesn’t just store the results of that function; it stores the function process. If you need to reference a field that has a formula in it, then Excel must recalculate the original function and your new function simultaneously. This isn’t a big deal until you are working with a spreadsheet that has a lot of data. Excel will let you know that there is “too much” data by freezing up. To avoid this, remove the actual formula from the fields by copying the column and pasting back just the values. You do not want to lose that formula, so create a row above your data rows and copy in just the formula by itself. That way, you can go back and check the accuracy of the formula or update it if you need to.

Tip #4: Color-code any fields that should have formulas in them.

I know it sounds very low-tech, but keeping track of which fields in your worksheets are intended to be data points and which are the result of a formula is an essential part of auditing your work. The simple way to do this is to use a specific color to highlight any fields where the data is the result of a formula. If you are removing the formula (see tip #3), this will alert you to the fact that the data in those fields is not a part of the original data, but rather from a formula that you created. The color-coding is also very helpful if another person is checking the accuracy of the spreadsheet (which is always a good idea, no matter how skilled you are in Excel).

Tip #5: Use the Compare and Merge Worksheets feature to find data that has changed between two versions of the same report.

If you have run the same report from your stock plan database at different times and come up with different results, it can be a daunting task to find the exact pieces of data that have changed or been deleted. One particularly clever way to find the changes is to use the Compare and Merge Worksheets feature. In Excel 2007, you may need to add this feature to your quick access toolbar. This feature is intended to compare different versions of a shared worksheet, so you will need to make sure that the two reports are saved with the same exact name and are marked as “being shared”. The first step to merging the two reports is to compare them and contrast their differences. You can use this step to find all your data points that have changed.

Only 11 days until the 17th Annual NASPP Conference!
The Conference is sold out, but you can still sign up for the live nationwide video webcasts of the 4th Annual Proxy Disclosure Conference and the 6th Annual Executive Compensation Conference–you get both webcasts for the price of one. 

You can hear any–and all–of the NASPP Conference sessions by purchasing the downloadable audio.  Purchase just the sessions you want or save by purchasing one of the package deals.

Registration is also still open for the Restricted Stock Essentials.

-Rachel