5 Excel Skills You Need to Become a Fanatic Like Me

If you have even the slightest attachment to the world of finance, it behooves you to boost or simply reboot your Microsoft Excel skills from time to time.

After 15 years in the world of accounting and finance, during which I’ve had jobs in audit, valuation, equity research and transaction advisory, I’ve found a number of shortcuts to creating and maintaining great financial models with Excel.

While the following list of Excel tips is brief, I think there are three important themes readers should take away:

• First, there will always be more than one way to solve a problem using Excel.
• Second, working faster by using shortcuts provides more time for review and formatting.
• Third, this is an iterative software that changes over time, so don’t be complacent with your current habits.

The latest Salary Guide From Robert Half lists advanced Excel competencies among finance and accounting’s most in-demand skills.

Here are five ways to put your Excel skills to work so you can do your best work in accounting and finance:

1. Hunting for selective criteria

Instead of messily filtering or sorting your data, consider these functions, which are much friendlier and less cumbersome than clunky Pivot Tables:

• Use Sumifs() to sum certain fields based on multiple conditions. The parameters are quick to set up and change on the fly. Sumifs(A:A, B:B, C1) sums the cells in column A only if the adjacent cell in column B is equal to the content of cell C1.
• Use Countif() to count the instances of a particular condition. For example, Countif(A:A,A1) tells you how many times A1 appears in column A. If the output is more than 1, you know it is a duplicate. A similar function, Countifs() enables users to check the existence of a series of conditions.
• Use Index() combined with Match() to find the position and value of a cell within a 2 dimensional grid matrix. Consider a 6x5 grid (6 down, 5 over) defined over the area A1:E6, where the top-most row contains unique names (Joey, Jimmy, Tommy, Timmy in B1:E1), the left-most column are ages (11,12,13,14,15 in A2:A6), and the rest of the matrix is filled with the boys’ heights at different ages. To look up Tommy’s height at age 12, for example, you would use the Match() function to find the relative position of the name Tommy in the top row (4th) and the relative position of 12 in the left column (3rd) as inputs to the function Index (A1:E6,3,4), which would output Tommy’s height at age 12.
• Use Xlookup() to quickly find related fields on the same row or column. This is a newer Excel function that combined two popular older functions (Vlookup and Hlookup), takes the form Xlookup(a1,f1:f10,k1:k10,false) if the reference data is vertical, looks up the relative position of a cell along the array in column F and returns the value in the same position along the array in column K.

2. Abnormal cash flow modeling

Another of the Excel skills many seasoned users still employ is the Irr() function to calculate internal rates of return, but there’s a superior function in the standard menu Excel now offers. With Xirr(), you can incorporate the timing of cash flows, which are rarely evenly distributed in the real world, into your calculation. The other benefit of Xirr() is that it yields an annualized rate. Contrast this with the Irr() function, which outputs a rate over the narrow period between cash flows. The Irr() function then requires multiplication or compounding to determine the annual rate, the more intuitive periodic rate used to analyze investment returns.

3. Playing the strings

The following functions should never be forgotten, especially if you need to parse a database full of complex unique identifiers. For example, if you had a text string “A122016” in cell A1, and you knew the first character identifies an account (“A” for Amortization), the next two characters identifies the month, and the last four characters reveals the year, you could pull out the following:

• Use the function Right(A1,4) to pull out the year.
• Use Left(A1,1) to capture the account.
• Use Mid(2,2) to find the month.
• Use Concatenate() to tie strings together, such as Concatenate(Left(A1,1),Right(A1,4)) produces “A2016.”
• Use Len() to count the number of characters in the string; that is, Len(A1) outputs 7.

4. Avoiding the mouse

Generally, it is faster to keep your fingers glued to the keyboard rather than slowly moving your hand to and from the mouse. Your Excel skills will be enhanced if you remember that the keyboard has more to offer you than just a 26-letter alphabet. Use the arrows, the spacebar, the F keys, Page Up/Down, Control, Shift, Tab, Alt, brackets and plus/minus keys. Use these in a host of combinations and be masterfully efficient.

• Moving between cells: Hold the Control key and use the arrows to jump between empty and non-empty cells.
• Moving between tabs: Control with Page Up/Down.
• Moving between workbooks: Alt-Tab
• Highlighting: Control-Space to highlight columns and Shift-Space to highlight rows
• Inserting: Control-Shift-Plus
• Tracing dependents: Control-[
• Changing decimals: Alt-H-0 to add, Alt-H-9 to remove
• Aligning cells: Alt-H-A-C for center alignment and Alt-H-A-R for right alignment
• Auto-adjusting row heights and column widths: Alt-H-O-A for height and Alt-H-O-I for width
• F keys: F2 to edit cell contents, F4 to lock a cell reference or to repeat your last command, F5 to return to your original source after tracing dependents (see above for shortcut), F9 to calculate manually, and F12 to save a new version of you workbook
• Freeze Frames: Alt-W-F-Enter to freeze frame (and Alt-W-F-Enter again to un-freeze a frame set by others)
• Print views: Alt-F-V for a preview and Alt-P-R-S to set print area
• Grouping and collapsing: Never hide rows, unless you want to frustrate anyone else who uses your workbook. Use Alt-A-G-G to group, then Alt-A-H to collapse.
• Changing calculation setting to manual: This will speed up your modeling if you have lots of data in the workbook. Go to File-Options-Formulas, then select Manual.
• Changing colors: Alt-H-F-C to quickly navigate to color menu
• Conditional formatting: Alt-H-L-N

5. Keeping it simple, easy to modify and clean

I’ve noticed many sources boast of the capabilities offered by Excel’s Pivot Tables, Goal Seek macro, Indirect() function, and Data Tables. While useful in a handful of situations, I’ve always found them to be less useful when sharing models with others. Indirect() makes tracing dependents (to check the source of the data) very difficult. Pivot Tables are clumsy, Goal Seek is hard to replicate quickly, and Data Tables are truly wonderful for one thing: slowing down (and crashing) your workbook.

Remember that the best Excel files are shareable and easy to follow, so before finalizing a working version, remove any hidden tabs and unnecessary conditional formatting. Also, eliminate unnecessary cell names and external links by using Microsoft’s cleaning app, XLStylesTool.

Excel is the most important piece of software for many of us. Every few years, Microsoft modifies Excel’s suite of features and functions, so it is easy to lose your competitive edge. I hope these tips help as a refresher or as confirmation that you are up-to-date on the new tricks and abreast of the best of the old ones.

Author R.J. Matthews, CPA and Chartered Financial Analyst, works in corporate due diligence in San Francisco.