What advanced
Advanced Excel Skills Employers Need Most
Answers
In my experience what I have seen is not knowing what are the most commonly used formulas like sumif, vlookup, hlookup, rank, count a, count if, if statements, etc to be able to properly manipulate data. There are many more (and I am sure many people in our proformative community can give their very valuable insight) but those are some of the few that I have seen being underutilized. Also, which is what I find a BIG deficiency is using check formulas to catch mistakes. The impact is quite extensive because you have spreadsheets with erroneous information being distributed. In many instances, someone has been working for hours with an analysis in a spreadsheet and they find out they have the wrong conclusions because the information is wrong due to formula errors.
One of my biggest hindrances with Excel is making it "pretty". I know many advanced formulas. I am not skilled at formatting the workbook in a way that is pleasant to view. Analyzing a raw data dump, putting information into cells, extracting that information, and others are much easier for me.
I blame it on my inability to draw, color, and similar creative functions.
1
To make it visually appealing, I do 3 things. First, I treat it like it was one of the old hard copy set of work papers. This means there is a cover or summary sheet on the first tab of each workbook, followed by supporting tabs. Secondly, use shading, bold and outlining on conclusions...draw the eye to where it needs to be. Third, each worksheet should be stand-alone. Put a title at the top, notes or text boxes to explain assumptions. Make it read like a book (left to right and top to bottom). The most important part of #3 is do not overload individual sheets with too many calcs. Spread them out..like putting a series of pivots on separate sheets.
Harold makes some excellent points, but Chris has touched on what I consider the real issue, namely turning a boring, confusing Excel spreadsheet into something that actually tells your story. Knowing how to make Excel "pretty" is really important, and not that difficult. The real trick isn't about
Think of this as needing the same kind of skills that make some people effective writers or speakers. Take a look at my book, "Painting with Numbers".
--Randall
1
All good points, thanks.
I have Randall's book, just by reading a few pages a week has help me tremendously with my Excel presentation. The book allows me to better understand the "Excel" story to myself, before I explaining it to my
All good points, we can use all the advanced formulas and pivot tables in the world and if data checks are not included, the information presented is just wrong. And, as Chris and Randall point out creating a spreadsheet in a format that presents the information in a readable and meaningful way is critical -- graphical presentations often work well to present information at a glance. My final essential for what I will call "resuable" workbooks is user instructions and either locking existing formulas or clearly indicating data entry fields. Organizations could further benefit from setting standard formats.
2
Good point. Thanks for sharing. What do you do to create these reusable templates?
Also, do you know of a good resource for training people on how to use advanced formulas and pivot tables. I have an analyst who could use additional training here and don't have a good ready resource.
I find that having well trained, technically proficient junior people out of college to be a challenge. Junior employees have a lot of understanding of the basics, however they need lots of additional on the job training. For software developers, they have solid boot camps on how do use a specific programming language or development approach and be very solid contributors in the workplace. Finding junior people who have those skills out of the box can be more challenging.
I was just a seminar this week and we talked to this exact point. I got excel sheet with correct formulas and good data is not pretty. The concentration should be on making sure you have good reliable raw data and adding an additional sheet that you can make "pretty" with good formatting, tables, graphs, etc.
I'm with Randall on this one (your book is on my to-do list) -- even accuracy is secondary to understandability. Remember that our purpose is to present information in order to "paint with numbers". Whether looking at actuals or presenting forecasts, you are painting a picture of either the past or the future. It hurts my inner-
Edward, bless you, you are spot-on. Early in my career, I had a
Thanks to all, especially Harold, Chris and Cindy for their specifics.
This may be in the BEST PRACTICE aspect of excel that is not mentioned in the thread.
Aside from the check formulas. another thing I try to teach my staff when they are preparing spreadsheets are....... the liberal use of cell notes and refraining from hard coding numbers without noting where it came from or how. (My pet peeve is seeing a staff using a calculator while working on a spreadsheet). I tell them that the spreadsheet is NOT only for their consumption but it will benefit reviewers and other users of the spreadsheet to know what the cells mean, it's context or where the numbers come from.
It does take some extra time and effort doing this but it sure does take away the extra QandA time when reviewing it.
One response I don't see above is embedding assumptions within formulas -- for example, instead of having a cell where you can input an assumption like an interest rate or a fringe rate and referencing that cell, the rate is simply entered as part of a formula.
I would break this down into a few categories: there are key functions which I find less advanced users don't know which could leapfrog their skills which are sumif (and sumifs), vlookup, and filters. Then there are some best practices including finding ways to double check the work whether is it things like footing and cross-footing, checking for obvious errors (like a balance sheet out of balance or a summary not tying out to the detail, or simply taking a step back to see if the results make sense. I also prefer to write formulas to catch errors rather than "eyeball" results. Finally, knowing when to invest time to make a sheet more readable or reusable. The former is addressed by many of the comments above but the latter refers to how many 1-time exercises turn into analyses that need to be repeated. Taking the time to write formulas that will work when data or assumptions change can save time later.
One item not mentioned is using conditional formatting to visibly catch data errors. Most of our worksheets are set up to highlight when cross foots don't match or certain cells that may need consideration, such as top 10% etc.
Filed Under:
Budgeting & Forecasting