5 Excel Skills You Need to Become a Fanatic Like Me

5 Excel Skills You Need to Become a Fanatic Like Me5 Excel Skills You Need to Become a Fanatic Like MeIf you have even the slightest attachment to the world of finance, it behooves you to boost or simply reboot yur Microsoft Excel skills from time to time.After nearly a decade in the world of accounting and finance, during which Ive hopped from jobs in audit, valuation, equity research and transaction advisory, Ive 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 awayFirst, 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 dont 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 finance1. Hunting for selective criteriaInstead of messily filtering or sorting your data, consider these functions, which are much friendlier and less cumbersome than clunky Pivot TablesUse Sumifs() to sum certain fields based on multiple conditions. The parameters are quick to set up and change on the fly. Sumifs(AA, BB, 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(AA,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. Co nsider a 6x5 grid (6 down, 5 over) defined over the area A1E6, where the top-most row contains unique names (Joey, Jimmy, Tommy, Timmy in B1E1), the left-most column are ages (11,12,13,14,15 in A2A6), and the rest of the matrix is filled with the boys heights at different ages. To look up Tommys 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 (A1E6,3,4), which would output Tommys height at age 12.Use Vlookup() to quickly find related fields on the same row. This takes the form Vlookup(D1,A1B300,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 modelingAnother of the Excel skills many seasoned users still employ is the Irr() function to calculate internal rates of return, but theres a superior function in the voreingestellt 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 stringsThe 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 followingUse the function Right(A1,4) to pull out the yearUse Left(A1,1) to captur e the accountUse Mid(2,2) to find the monthUse Concatenate() to tie strings together, such as Concatenate(Left(A1,1),Right(A1,4)) produces A2016Use Len() to count the number of characters in the string that is, Len(A1) outputs 74. Avoiding the mouseGenerally, 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/negativ 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/DownMoving between workbooks Use Alt-TabHighlighting Use Control-Space to highlight columns and Shift-Space to highlight rows.Inserting Control-Shift-PlusTracing dependents Control-Changing decimals Alt-H-0 to add, Alt-H-9 to removeUnderlining with a bottom border Alt-H-B-O, or ALt-H-B-U for double underlyingAligning cells Alt-H-A-C for center alignment and Alt-H-A-R for right alignmentAuto-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 colo r menuConditional formatting Alt-H-L-N5. Keeping it simple and easy to modifyIve noticed many of the articles on the Internet boast of the capabilities offered by Excels Pivot Tables, Goal Seek macro, Indirect() function, and Data Tables. While useful in a handful of situations, Ive 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 Excels 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.Dont miss our career insights in the Robert Half Accounting and Finance Blog.READ MORE IN OUR NEWSLETTERAuthor R.J. Matthews, CPA and Chartered Financial Analyst, works in corporate due diligence in San Francisco.