more-arw search

Q&A Forum

Advanced Excel Skills Employers Need Most

What advanced Excel abilities do you find employers need most, but often find lacking? Also curious about your views on how the lack of those skills impacts organizations. I have to give a presentation on this topic.

Answers

Harold D. Tamayo
Title: Vice President of Finance
Company: MHA Inc., a Roper Technologies Company
LinkedIn Profile
(Vice President of Finance, MHA Inc., a Roper Technologies Company) |

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.

Chris Shumate
Title: Accounting Manager
Company: Dominion Development Group, LLC
LinkedIn Profile
(Accounting Manager, Dominion Development Group, LLC) |

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.

Samuel W Reed
Title: Cofounder and CTO
Company: BitMEX
LinkedIn Profile
(Cofounder and CTO , BitMEX) |

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.

Randall Bolten
Title: CEO
Company: Lucidity
LinkedIn Profile
(CEO, Lucidity) |

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 learning a whole lot of cool functions, it's about developing HABITS that lead to easy-to-read, impactful spreadsheets that have a consistent look-and-feel. These habits will help you turn out great work even when you're under time pressure.
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

James H. Blackwell
Title: MBA Candidate
Company: Eastern Michigan University
LinkedIn Profile
(MBA Candidate, Eastern Michigan University) |

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 management and peers.

Cindy Boyce
Title: Senior Manager, Outsourced Financial Ser..
Company: BDO USA, LLP
(Senior Manager, Outsourced Financial Services, BDO USA, LLP) |

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.

Phil Dillard
Title: CFO
Company: Fractal Sciences
(CFO, Fractal Sciences) |

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.

Phil Dillard
Title: CFO
Company: Fractal Sciences
(CFO, Fractal Sciences) |

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.

Donna Kott
Title: Controller, HR Manager
Company: NTA, Inc.
(Controller, HR Manager, NTA, Inc.) |

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.

Edward Thill
Title: VP - Finance & Operations
Company: Performance Trust
(VP - Finance & Operations, Performance Trust) |

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-accountant to admit this but the most accurate information presented poorly will always be inferior to materially correct information presented in a manner that tells "the story" well. The functions Harold mentions (and others) are important in enhancing the accuracy and efficiency of the information gathering process and I don't mean to minimize their importance but the story telling will ultimately provide the value.

Randall Bolten
Title: CEO
Company: Lucidity
LinkedIn Profile
(CEO, Lucidity) |

Edward, bless you, you are spot-on. Early in my career, I had a CFO who said, "I'd rather have poor work presented well than good work presented poorly." That sounds strange, but he was being tricky, clever, and correct. First of all, since presenting the work is an important part of almost every task, then a poor presentation means that it wasn't "good work" to begin with. And second, if "poor work" is presented well, it significantly improves the chance that the flaws in the work will be identified and ultimately corrected.

David Smith
Title: Manager
Company: Private
(Manager, Private) |

Thanks to all, especially Harold, Chris and Cindy for their specifics.

EMERSON GALFO
Title: CFO
Company: C-Suite Services
LinkedIn Profile
(CFO, C-Suite Services) |

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.

Bob Low
Title: Principal
Company: Perron & Low
(Principal, Perron & Low) |

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.

Kim Zarraga
Title: Accounting Manager
Company: Bio-Rad Laboratories
(Accounting Manager, Bio-Rad Laboratories) |

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.

7741 views
Products and Companies

Get Free Membership

By signing up, you will receive emails from Proformative regarding Proformative programs, events, community news and activity. You can withdraw your consent at any time. Contact Us.

Business Exchange

Browse the Business Exchange to find information, resources and peer reviews to help you select the right solution for your business.

Learn more

Contribute to Community

If you’re interested in learning more about contributing to your Proformative community, we have many ways for you to get involved. Please email content@proformative.com to learn more about becoming a speaker or contributing to the blogs/Q&A Forum.