5 Excel Skills You Need to Become a Fanatic Like Me

5 Excel Skills You Need to Become a Fanatic Like Me 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 nearly a decade in the world of accounting and finance, during which I’ve hopped from 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. Sixty-nine percent of U.S. companies and 78 percent of small companies interviewed for the Benchmarking the Accounting Finance Function from Robert Half and the Financial Executives Research Foundation said they use Excel for budgeting and long-range planning. 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. 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 Vlookup() to quickly find related fields on the same row. This takes the form Vlookup(D1,A1:B300,2,FALSE), and looks up the value of a cell in column B that is on the same row as the row where the value of D1 is positioned in column A. 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: Use Control with Page Up/Down Moving between workbooks: Use Alt-Tab Highlighting: Use 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 Underlining with a bottom border: Alt-H-B-O, or ALt-H-B-U for double underlying 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. Print views: Alt-F-V for a preview. Use 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 and easy to modify I’ve noticed many of the articles on the Internet 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. 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 to the younger cohort of analysts and accountants. 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. Don't miss our career insights in the Robert Half Accounting and Finance Blog. READ MORE IN OUR NEWSLETTER Author R.J. Matthews, CPA and Chartered Financial Analyst, works in corporate due diligence in San Francisco.