What is (or should be) considered "advanced" knowledge in Excel?

[MrKowz]

Reading some of the discussion in the PreVisor Excel Test Thread and working in an office environment has sparked a question in me:

What is considered "advanced" in Excel for the common workplace? Is it possible to be too advanced, if so, is that a bad thing? Do employers tell you to do some things in excel "their way", simply because they don't understand your formula?

I've run into this problem in a past workplace. My former supervisor hated when I did array-entered INDEX/MATCH formulas to match on multiple conditions. They much prefered that I made a concatenate helper column for what I need to match, and then index/match off of those. Even after trying to explain it in depth and how my method is more accurate, I was still told to use the concatenate method because "If I don't understand it, I don't want you to use it."

Also, when working with another employee (them at the keyboard and you off to the side), watching their methods. Does your knowledge pick at you when they do something incredibly inefficient/inaccurate? Such as wanting to tell them to stop sorting and summing groups of data, to instead use a SUMIF?

 

[arkusM]

What is considered "advanced" in Excel for the common workplace?

Lol from what I've seen anything beyond a sum() or occasionally a vlookup() is advanced around here.

Also, when working with another employee (them at the keyboard and you off to the side), watching their methods. Does your knowledge pick at you when they do something incredibly inefficient/inaccurate? Such as wanting to tell them to stop sorting and summing groups of data, to instead use a SUMIF?

All the time. My tongue is scared from being bitten as I grin and bear through the experience.

 

[Cindy Ellis]

I rarely cringe at the way people choose to do specific tasks in Excel as long as they are using Excel functionality and as long as it provides an accurate result...with a few exceptions, such as manually entering formulas instead of copying them, manually re-entering data from a data source instead of importing it, manually counting instances instead of using Excel's capabilities, calculating externally and transcribing to Excel, or doing obviously repetitive and complicated tasks without taking advantage of macros. (Hmmm...I think I see a pattern here )

Mostly though, I see people trying their best to use a tool that they're not expert in to complete a task that's been assigned to them without clear requirements, in an attempt to meet an impossible deadline.

That said, many people I work with come to me for advice or assistance about Excel, and in those cases, I'll show them how I would do it, but in the end, if there's a sequence of steps they can take reproducibly to provide an answer and it's comfortable for them, my way is just different. If it's more complicated, that decreases the probability that they will be able to do it independently, and I'll get stuck in their "deadline loop," which goes something like "Hi Cindy, this is Bob. Remember that report you helped me with last quarter? It's due again at noon today, and Excel's not updating it right. Can you take a look at it?" I'd rather help someone be independent of me rather than dependent on me.

I personally don't take offense at helper columns if it reduces questions further down the road, although I have used array formulas and sumproduct when necessary to maintain my guru status .

That doesn't really answer the question, though, does it?

In my opinion, advanced knowledge includes 9but is not limited to) macros beyond simply recording a task, complex logic functions (combinations of and, or, iserror, etc., in combination or in place of the IF function), and application of statistical functions beyond just using the built in data analysis tools.

 

[Andrew Fergus]

Cindy has touched on a good point. The standard for being considered an 'expert' at the office is lower than that required on internet forums, on the basis of peer review. The most complex solutions here will rarely see the light of day in the office.

Also, the standard is so low at the office as Cindy has pointed out with her examples. There seems to be an assumption that if you can use a computer then you can use Excel and, judging by some CVs, if you can use Excel independently then you can consider yourself an expert.

So in answer to your question, it depends on your audience. If you are one step ahead of your boss or peers, then you are the office 'expert'. But to reject a solution/formula on the basis you don't understand it highlights some personal issues (IMHO)

Andrew

 

[Greg Truby]

Most folks reckon that if'n they kin draw up a VLookup() from scratch they're power users.

If a gal can write even the most basic of macros and do a bit of interface customization, she'll probably be considered the office Excel wizard.

And I'd say my experience jibes pretty much with Cindy's. Both in the kinds of things that are frustrating to watch and in her counsel to beware creating anything for someone else that they cannot comprehend, unless you are willing to be the "tech support" for said creation. It ain't about how good you are. It's about how good can you make others?

For me, the hardest thing was dealing with people that just wanted me to "work my magic" and have me walk away with my showing them how it worked. I had a reasonably deep well of patience with folks that'd make an honest effort ta boot strap themselves into Excel competence. My patience was pretty thin with folks that made no effort to learn how to use Excel better.

 

[SydneyGeek]

"Expert" is definitely relative. One place where I ran a training session "power user" seemed to mean "can use SUM". VLOOKUP fried their brains.
Other environments (eg, when working with analysts) are much more advanced. But definitely, most of the formulas created on MrE would bamboozle 99.9% of office workers.

Denis

 

[xenou]

... and some formulas on MrE bamboozle 99.9% of all of us ... I think things like Pivot Tables and Conditional Formats tend to be the things that stronger users (who are really interested in leveraging Excel) discover. I think SumIf and SumProduct also tend to go that way too. Or using Named Ranges. In any case, that's advanced enough for me -- I have some confidence in these users!

BTW, as far as helper columns go - no shame there. I like them quite a lot since they are even somewhat self-documenting in breaking down a formula into its component parts.

ξ

 

[Cindy Ellis]

A more complete answer than my earlier post...
I tend to make a mental distinction between advanced and expert. My personal classification scheme is something like:

Beginner: Knows how to open Excel, enter data, and create simple formulas (single functions with clickable inputs). Has figured out how to create bar charts and line charts, but not how to arrange their data to get the chart they really want. Knows how to print worksheet, but not how to adjust the page setup. Knows how to fill cells with an annoying variety of colors that have meaning only to the originator.

Intermediate: Generally uses appropriate Excel functions, but maybe not the most efficient function for a task. Comfortable with a couple levels of nested IF statements, and can use a VLOOKUP with only an occassional reminder to add in the argument for exact match when it returns the "wrong" data. Comfortable with a variety of charts and chart formatting. Comfortable with basic pivot tables, but not necessarily with changing layouts, functions, or page filters. May use Data Analysis tools with little or no guidance other than on the statistical soundness of their analysis (as in "No, calculating the standard deviation of the ID number will not give you meaningful data"). Has figured out how to lock formulas and protect a worksheet. Understands page layout options reasonably well. Uses (limited) color on the worksheet in a meaningful way. May occassionally record a simple macro to automate a repetitive task, but doesn't use the Visual Basic editor to generalize it or remove macro-recorder idiosynchrasies. May create user forms on the way to becoming advanced users.

Advanced: Uses Excel as a tool of choice. Regularly sets up their data to take advantage of Excel's capabilities (few or no merged cells, no grouping of data with blank lines in between, etc.) Can create templates for other users, with input cells clearly identified and formulas validated and locked. Helps beginner and intermediate users solve problems. Creates charts (including those with secondary axes) that communicate information, not just data. Never says "well, I put the data in, and this is what Excel gave me". Comfortable with complex analyses, complex and compound functions, advanced filters. Probably creates and edits macros; if so, knows how to get rid of all of the "select" and "scroll" bits and pieces, and write a Do loop or While loop with a couple of nested layers. (I recognize that not all advanced users need macros...so I don't want to exclude those formula super-gurus from advanced status!!!)

Expert: Uses Excel as a tool of choice, but understands many of its limitations. Contributes to the MrExcel.com forum . Probably knows 2 or 3 ways to do a task in Excel, and can recommend a "best choice" for the situation at hand. May use Excel macros to manipulate other applications or to access the file system. May still stumble over syntax occassionally, but knows that their expert status isn't threatened by asking a question or 2 of their online forum members. Not afraid to tackle a Mr. Excel question that they don't really know anything about, as an opportunity to learn something new.

Cindy

 

[TinaP]

I agree wholeheartedly with Cindy.

In my office the breakdown is in the neighborhood of 95% beginner, 4.5% intermediate and 0.5% advanced. I cannot include myself in the expert category, yet. Give me time.

What I hear and how I define the user:
Beginner: "I put the information in and this is what it gives me. If it doesn't balance, I use the adding machine and put the right number in, overwriting the formula instead of figuring out why it doesn't balance. No, I don't need to learn anything else about Excel."

Intermediate: "Excel can do THAT?! Holy cow, show me more. I have to put together a workbook, can you show me the most efficient way to set it up? Can you show me how to record macros?"

Advanced: "I don't know the formula, but I can find it with a little research. I shouldn't need a macro for this, a pivot table will give me more flexibility and useable data. Complex formulas? Yeah, I can put a complex formula together. Do you mean this OR that or this AND that?"

Expert: "Hmm. I can probably do that, but I'll have to get creative. Give me some time."

 

[Sandeep Warrier]

Also, when working with another employee (them at the keyboard and you off to the side), watching their methods. Does your knowledge pick at you when they do something incredibly inefficient/inaccurate? Such as wanting to tell them to stop sorting and summing groups of data, to instead use a SUMIF?

Ohh yes!

Specially when they use the mouse to do simple tasks when they would be done in a jiffy with keyboard shortcuts.

The kind that test my patience the best is people who have an Excel workbook full of numbers in front of them and open up calculator to do calculations!