# 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

MrKowz said:


> 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.


MrKowz said:


> 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

MrKowz said:


> 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!


----------



## arkusM

Greg Truby said:


> . It's about how good can you *make others?*


That why you (and many like you ) have thousands of posts and why this board is such a great place to be.


----------



## JazzSP8

Sandeep Warrier said:


> 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!



I've recently seen this...

She was working out a 10% rise on some figures on about 1000 lines of data, she did it all, with a calculator, reading numbers from Excel, punching them in and then entering the number from the calculator into a cell... Scary... 

Apparantly it took her hours to complete...


----------



## arkusM

Reflecting a bit it seems that people are the collateral damage of our technology lust. People who buy advance DSLR's to use as a point-n-shoot cameras; not taking advantage of the power in their hands. People who have latest gen "cell" phone who complain that the battery sucks; when they are hold a device that really is a handheld computer that happens to also make phone calls. People who use Excel that could do what they need with a piece of paper and calculator unaware of the powerful tool it is. People who drive cars that are wonders of engineering and manufacture that drive them solely as put-put grocery-getters... the list goes on. The choices are staggering and most often far exceed our requirments.

I guess we can't, or choose not too, keep up with the power that surrounds us for whatever reason. I (we) need to be patient (and humble) grasshopper - right?


----------



## Greg Truby

I'd also say to keep in mind that your question is really like asking that same question about any other subject matter.

_"How many "strong" people are in the gym?"_
_"How many "good communicators" are in the office?"_

The majority of people will tend to believe themselves on the top half (or quartile) of just about any curve. If you can bench press 200 lbs and dead lift 300, you think that's pretty strong. But if you can bench press 300 lbs and dead lift 500, you think *that's* reasonably strong. However, if you can do 400 & 650 then you think that's strong. 

Likewise most folks consider themselves pretty good communicators and some think that if their grammar and punctuation are correct, they're well above average. Others would say that because they consider how their message will be received and what is the objective, they're well above average. Others would say that because they consider the media, the sender's objectives, and not only the target audience's reaction but how the message or issue will impact the target's objectives and goals and they then practice and hone the message making rough drafts and revisions to written communiques or video taping and rehearsing their speech, then that makes them above average.

Ask the strength question on a site that caters to bodybuilders or power lifting competitors and the standards will be well thought out and "expert" will be pretty demanding.

Ask the communications question on a site dedicated to professional speaking or sales craft and it'll be likewise.

You've posed your question in a forum of hard-core Excel users - so our standard for "expert" is going to be fairly rigorous.

Keep that in mind when judging others' Excel expertise and in weighing responses from our gang here.


----------



## JamesW

It's incredibly hard to label someone based on their skillset as it is so subjective. 

What about 'the bar'?

"Joe Bloggs in my eyes is an Excel expert".

Sid Smith arrives in the company and knows twice as much as Joe

"Woah! Sid Smith is better than Joe, so he must be a Guru"

Ben Twilly arrives in the company and knows twice as much as Sid.

"Ben knows more than Sid?! My God, he's an Excel Genius!"

etc. etc.

That's why I couldn't get 100% on my A-Level IT exam


----------



## Norie

I'm not sure about the iidea of doing something in a way that you know works, and is perhaps is more efficient, but other people don't understand.


----------



## MrKowz

Good points, Greg and James; that is the reason I asked this excel question on a board with some of the highest experienced excelers out there.  This is merely one of my little threads I like to make to encourage users to openly participate in good natured discussion.  And I agree, this is a highly subjective topic, and it is always nice to have a better understanding from many points of view (from novice to expert) imo.


----------



## diddi

Its amusing to read other people's gripes and measures of 'expert' when i compare to my own ideas, as things like keyboard shortcuts, for eg, which some rate as vital, are utterly unimportant to me, and im sure the same would be true when thinking about what i think might be important

So how might i rate an expert? perhaps if you can use excel to solve the problem at hand, then you become the expert for that job. for the next project, maybe you have to sit back and give others a turn.


----------



## tweedle

Andrew Fergus said:


> 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.


This is why I so enjoy being a p/t trawler of this forum. 
It's so much more challenging than anything the kids in the office think up. 



MrKowz said:


> 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."


Absolutely; I was once asked on a contract if I "could do INDEX/MATCHes'. 
Upon learning that I could, they had me decypher literally hundreds of them that someone (imo) of Expert/MVP caliber built, because their lack of understanding had frozen the bonus process. [I'm pretty sure I was 'schooled' on that job; backing into them isn't nearly as easy as building them]

Cindy's scale is pretty much dead-on.


----------



## MrKowz

tweedle said:


> Absolutely; I was once asked on a contract if I "could do INDEX/MATCHes'.
> Upon learning that I could, they had me decypher literally hundreds of them that someone (imo) of Expert/MVP caliber built, because their lack of understanding had frozen the bonus process. [I'm pretty sure I was 'schooled' on that job; backing into them isn't nearly as easy as building them]



Reverse-Engineering someone's spaghetti-stringed code or scatterbrained megaformulas in Excel is exponentially harder than creating the code from scratch.


----------



## Norie

Which is why you should make formulas and code as transparent as you can - you aren't the only one who might need to understand it.


----------



## SuperFerret

My workplace has what I feel is a very strange view on the Excel Knowledge scale.

There levels are:
Introductary - The Excel screen, Opening a blank workbook, Saving, Closing, Navigation, Entering data, Cut and Paste, Printing a document.

Intermediate - Charts, Data Validation, Sorting, working with multiple worksheets

Advanced - Using a template, IF function, Lookup, working with multiple workbooks, comments and protection

Given that 98% of our office works on Excel 90% of their day... you would think people would be at the 'Intermediate', possibly 'Advanced' levels... but no, everyone seems to be at Introductary. They know what Excel is and how to move around, but they have no idea about formulas, data validation, or conditional formatting.

They see this every day (in spreadsheets I've updated) and are amazed how things _magically_ change colour or how it knows that what you've put in doesn't fit (barcodes that should be 13 digits when they try to put in 8).

The person providing the training doesn't help, I got sent on an Advanced course... and suggested a different method of acheiving the same result and was told _No it wont do that_ (which it did) and when I mentioned putting images in comments he informed me that _you can't do that in Excel_ so I did it...just to prove you can.
It as got to be bad when someone else on the course shouts that you should be doing the training instead


----------



## Norie

Did they actually do that?

Did you take over the class?

Might have lead to a new career avenue.


----------



## arkusM

SuperFerret said:


> I got sent on an Advanced course... and suggested a different method of acheiving the same result and was told _No it wont do that_ (which it did) and when I mentioned putting images in comments he informed me that _you can't do that in Excel_ so I did it...just to prove you can.
> It as got to be bad when someone else on the course shouts that you should be doing the training instead


 
 nice.

my experiance in an "advanced" excel course taught be CTRL + ":" put the date in a cell at a cost >$400 (CND) not a great value. 
The "advanced courses" in this place often baffle me!


----------



## SuperFerret

Norie said:


> Did they actually do that?
> 
> Did you take over the class?
> 
> Might have lead to a new career avenue.


 
Yes, he wasn't a happy bunny having someone there who knew more than him in there and wouldn't let me take over, or show anyone else any shortcuts (which they didn't teach) or variations on the formulas he was teaching. Miserable so-and-so!

I wouldn't mind teaching others, I'd probably do a better job than some places by the sounds!



arkusM said:


> my experiance in an "advanced" excel course taught be CTRL + ":" put the date in a cell at a cost >$400 (CND) not a great value.
> The "advanced courses" in this place often baffle me!


 
The people on my team were amazed when I showed them CTRL+: maybe they think it's a big secret?


----------



## SydneyGeek

I've always treated the manual as a very rough guide. I start with about an hour of hints and tips in a blank workbook. Usually the feedback is that that section was the most useful part of the course -- most of the good stuff is _not _in those manuals. 

Denis


----------



## Smitty

Norie said:


> Which is why you should make formulas and code as transparent as you can - you aren't the only one who might need to understand it.


 
I'm in the process of decoding a few Access databases that a no-longer-employed developer purposely obfuscated (query sql hidden in hidden tables, user passwords hidden in random tables, no comments, etc.) 



> 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."


 
Nothing wrong with helper columns as long as it's done for the right reasons. 

As for the rest, I've got to agree that Cindy is pretty much dead on. With the exceptioin that if you want to learn more, don't let your employer hold you back by sticking to what's comfortable to them. Yesterday I paid a visit to the Fortune 500 I worked with for years, and am still the only one who writes VBA (and very few there, even in finance, even used recorded macros).



> It as got to be bad when someone else on the course shouts that you should be doing the training instead


 
So get some training material from me, Denis or Truby and start doing it!


----------



## RoryA

Smitty said:


> So get some training material from me, Denis or Truby and start doing it!



Do you have any for 2010? I think I'm about to get lumbered with training for our upgrade at work and I really don't have the time to put anything together (new baby to get ready for).


----------



## Fazza

From my perspective, an expert knows more than me. In the workplace, I am yet to meet such people - or even people anywhere close to my level. I do encounter them on line & in books - there are thousands & thousands of experts with amazing knowledge. It is enjoyable to learn from these experts & share my knowledge with others. regards


----------



## Greg Truby

rorya said:


> Do you have any for 2010? I think I'm about to get lumbered with training for our upgrade at work and I really don't have the time to put anything together (new baby to get ready for).


 
I'm afraid all of my materials are from when I was a contract trainer with Fred Pryor seminars and they developed pretty much all of the materials I used.

@ Super Ferret - one thing I would never do when doing training is say "you can't do that in Excel".  I know a *lot* of Excel tricks and tips and yet I don't know it all.  There's all sorts of little nuggets all around that I'm not aware of.  People pull rabbits out of hats around here all the time.

Having said that -- this thread has already alluded to the fact that even though essentially 100% of most office personnel have Excel install on their computers, > 95% underutilize it.   I'm sure Smitty's & Denis' experiences are similar to mine as far a public seminars goes -- 1/3 of attendees are on an all-day coffee break; 1/3 are neutral and about 1/3 are actually engaged and trying to learn something.   Don't start teaching seminars thinking that 100% of the class is going to think you're an answer to their prayers.  Some will actually hate you because you love Excel.  I'm not saying don't do it -- the 1/3 that are there to learn make it worth it; and sometimes you can spark interest in the neutrals.  

Teaching seminars is a very interesting business and an amazing learning opportunity.  But it's not for the feint of heart either.


----------



## SydneyGeek

Greg Truby said:


> I'm afraid all of my materials are from when I was a contract trainer with Fred Pryor seminars and they developed pretty much all of the materials I used.
> 
> @ Super Ferret - one thing I would never do when doing training is say "you can't do that in Excel". I know a *lot* of Excel tricks and tips and yet I don't know it all. There's all sorts of little nuggets all around that I'm not aware of. People pull rabbits out of hats around here all the time.
> 
> Having said that -- this thread has already alluded to the fact that even though essentially 100% of most office personnel have Excel install on their computers, > 95% underutilize it. I'm sure Smitty's & Denis' experiences are similar to mine as far a public seminars goes -- 1/3 of attendees are on an all-day coffee break; 1/3 are neutral and about 1/3 are actually engaged and trying to learn something. Don't start teaching seminars thinking that 100% of the class is going to think you're an answer to their prayers. Some will actually hate you because you love Excel. I'm not saying don't do it -- the 1/3 that are there to learn make it worth it; and sometimes you can spark interest in the neutrals.
> 
> Teaching seminars is a very interesting business and an amazing learning opportunity. But it's not for the feint of heart either.


 
@rory, I'm in a similar position to Greg. The materials that I used were provided by the firms that I contracted for. 
The hints and tips that I run through are pretty generic, but usually the sort of things that drive people slightly nuts. Examples:

(don't laugh -- heaps of people don't know this stuff)
Double-click the fill handle to fill formulas down 
Alt+= to total multiple columns / rows
Navigation / selection using the keyboard
Using GoTo | Special for...
  Filling in blanks with zeros
  Deleting rows with blanks
  Highlighting cells with formulas / constants
Using the various Paste Special options
...and whatever questions arise from these demos

What else for 2010?
Ribbon
Backstage
Sparklines
Pivot Tables
Slicers
SUMIFS / COUNTIFS
IFERROR

Particularly if you are running something internal, use "real" work examples. 

@Greg, 

Yep -- sometimes you really have to work to get any sort of reaction. But when you see people switch on it's a buzz. And I love it when somebody shows me something I didn't know before. Why shouldn't the trainer also be learning stuff? 

Denis


----------



## SuperFerret

Smitty said:


> SuperFerret said:
> 
> 
> 
> It as got to be bad when someone else on the course shouts that you should be doing the training instead
> 
> 
> 
> 
> So get some training material from me, Denis or Truby and start doing it!
Click to expand...

 
Any training materials I really would appreciate!! I've got an ever increasing army of people now all protesting to the higher up's that I should be providing the training haha. 

Only yesterday I showed someone CTRL+Return and they've been on the in-house course.


----------



## Jaafar Tribak

From my experience, most people in the workplace never get past the beginner level - Yet, they somehow often manage to get the best jobs in the office  

I truely believe that excel is under utilized in the workplace.What is more, lots of office personnel are resistant to improving their existing spreadsheets because they have the perception it will make their spreadsheets too technical and therefore more difficult to handle specially if something goes wrong with them when you are not around.

Also, there is the fact that each departmenet has its own main piece of  software to get the job done which further contributes to the loss of  interest in enhacing excel skills.

Apparently, advanced/expert excel users live in the virtual world,not the real world


----------



## SuperFerret

Jaafar Tribak said:


> From my experience, most people in the workplace never get past the beginner level - Yet, they somehow often manage to get the best jobs in the office


 
I hear that! I've lost count of the number of Managers and Directors which come to me, the lowly Admin Assistant to provide them with a solution to their Excel woes.



Jaafar Tribak said:


> Apparently, advanced/expert excel users live in the virtual world,not the real world


It appears so!


----------



## Sandeep Warrier

Jaafar Tribak said:


> From my experience, most people in the workplace never get past the beginner level - Yet, they somehow often manage to get the best jobs in the office



That is soooo true!!!


----------



## Greg Truby

@ Rory (or anyone else training) - a good technique is to demonstrate the painful method before giving them the tip. This one was probably my most popular...

{show table with six or eight columns}
"OK - we need to move this column [E] over here [between A & B]. How many of you would have done this?
{Insert column between A & B [*NOT* using Ctrl+Space then Ctrl++]}
{Copy & Paste or Drag E to new column}
{Delete the now-blank Column E [again *NOT* using Ctrl+Space then Ctrl+-]}

Now check this out!
{Go to a second copy of worksheet}
{Demo the drag while holding Shift}
{listen to oooh's and aaaah's - wait until they say "do that again" or if about six or eight seconds go by ask "would you like to see it again?"}
{Do it again}


----------



## arkusM

Greg Truby said:


> @ Rory (or anyone else training) - a good technique is to demonstrate the painful method before giving them the tip. This one was probably my most popular...
> 
> {show table with five or eight columns}
> "OK - we need to move this column [E] over here [between A & B]. How many of you would have done this?
> {Insert column between A & B [*NOT* using Ctrl+Space then Ctrl++]}
> {Copy & Paste or Drag E to new column}
> {Delete the now-blank Column E [again *NOT* using Ctrl+Space then Ctrl+-]}
> 
> Now check this out!
> {Go to a second copy of worksheet}
> {Demo the drag while holding shift}
> {listen to oooh's and aaaah's - wait until they say "do that again" or if about six or eight seconds go by ask "would you like to see it again"}
> {Do it again}


 
huh, just learned something new. Shiny.


----------



## RoryA

Last time I did it, I think the best received bits were the bits I threw in as I was going along - double-click filling and right-click filling, and formatting subtotal rows (after the Data Subtotals method) in one go.


----------



## Darren Bartrup

Greg Truby said:


> Now check this out!
> {Go to a second copy of worksheet}
> {Demo the drag while holding Shift}
> {listen to oooh's and aaaah's - wait until they say "do that again" or if about six or eight seconds go by ask "would you like to see it again?"}
> {Do it again}



Oooohhhhh.

That's 2 things I've learnt from this thread now - didn't know the Ctrl+: either.


----------



## Darren Bartrup

Best response I've had was explaining SUMPRODUCT.  Took a few months for people to get it, but got there in the end.
Best recent response I've had was explaining that if you hold shift down while dragging data around......

(sorry, would've put that as an edit to last post - but my 10 mins was up).


----------



## Greg Truby

rorya said:


> ...right-click filling...


 
Try showin' 'em a right-click _drag_. :wink: The unwashed heathens won't react. But 99% of your power users will go "no way!" when you show them how easy it is to "copy and paste values" that-a-way -- especially when you drag away and then drop back on top of the original source; i.e. convert formulas to values. [Though, if you've been doing it long enough - your fingers will just sort of do the "Ctrl+C | Alt+E+S+V" on their own, so for a certain percentage of power users (yours truly included) it might actually be slower.]


----------



## RoryA

Ha - I did the right-click drag to Paste Link and only one person even noticed (I have buttons for that sort of thing)


----------



## Greg Truby

Your comment reminds me -- it wasn't until I was teaching classes about fifteen months ago that I ever actually tried out the _Paste Link_ option in the _Paste Special_ dialog.    I couldn't believe that ******'d been there all these years and I hadn't tried it before!


----------



## RobMatthews

Greg Truby said:


> Your comment reminds me -- it wasn't until I was teaching classes about fifteen months ago that I ever actually tried out the _Paste Link_ option in the _Paste Special_ dialog.  I couldn't believe that ******'d been there all these years and I hadn't *tried it* before!


This is how I learn a lot of stuff: see menu option or Rclick option, and say, hey, I wonder what that does... But so many people just have no wonderment. For those people, it probably applies to their life outlook, not just their excel use.

Plus, I've learnt some cool things in this thread; probably more than in that other thread a wile back about "What are your favourite shortcuts?"

My lovely, intelligent wife tends to put all formulae inside a sum, to wit: "=sum(blahblah)". It's only recently that I've been able to convince her that this is not necessary.

The other thing with keyboard shortcuts, like Alt-W-F: what is the responsiveness of the ribbon like? We are on 03 here, but my other primary program (Autodesk Inventor) is now be-ribboned, and it just doesn't respond immediately, and I find that it misses a key-press or two, if I'm too quick. Plus, of course, the accelerator keys are now different in the ribbon version...


----------



## MrKowz

The ribbon in 07 responds very well.  I didn't like it at first, due to spending so many years working on 03; but I find that I prefer the ribbon style now.  Easier to find certain options.


----------



## Sandeep Warrier

RobMatthews said:


> The other thing with keyboard shortcuts, like Alt-W-F: what is the responsiveness of the ribbon like? We are on 03 here, but my other primary program (Autodesk Inventor) is now be-ribboned, and it just doesn't respond immediately, and I find that it misses a key-press or two, if I'm too quick. Plus, of course, the accelerator keys are now different in the ribbon version...



In 2007/10, you need to do an extra F.... Alt+W+F+F

I still tend to use loads of 2003 short-cut keys in 2010... and fortunately almost all work


----------



## arkusM

Greg Truby said:


> Try showin' 'em a right-click _drag_. :wink: The unwashed heathens won't react. But 99% of your power users will go "no way!" when you show them how easy it is to "copy and paste values" that-a-way -- especially when you drag away and then drop back on top of the original source; i.e. convert formulas to values. [Though, if you've been doing it long enough - your fingers will just sort of do the "Ctrl+C | Alt+E+S+V" on their own, so for a certain percentage of power users (yours truly included) it might actually be slower.]


 

  Always mini lessons here. That is sweet. I just made a macro key-shortcut for the paste values (Ctrl-Shift-V).

Paste Link - Cool never tried that either.


----------



## Fazza

Tis true, Greg, my fingers do the Alt+E+S+V automatically. Though I also have a personal/custom shortcut CTRL-Z to paste special values only, so don't always use the super familiar ALT-E-S-V.

Annoyingly, another automatic sequence ALT-I-C is sometimes a problem for me. If unwittingly it is done when the active cell is in a pivot table, instead of inserting a column as I expect/want it inserts a formula field into the pivot table and even if I immediately use CTRL-Z to undo it, it is to no avail. 'Lucky' that I am 'always' saving working files & go close the file & go to a recent copy without any harm done. I am ALWAYS using CTRL-S to backup my working files. A long term habit - CTRL-S very frequently.


----------



## PassTheSugar

Hi Guys,

I am looking for a bit of advice. When a job is looking for an advanced knowledge of excel, how confidently should I be saying 'Yes' to that question, or Yes to intermediate level given the following information:

 - I have been using the Excel 2003 for 7 months every working day, however much of this is simply looking at data in workbooks and changing simple stuff.

- The job is similar (ish) to the job I'm doing now, at a slightly higher level.

- I can confidently do Lookups, sums, navigate confidently around the sheets using all filters and sorting comfortably.

- Can confidently build and use pivot tables

- Some knowledge of IF formulas but very little use (probably just need to practice but never really needed them)

 - No knowledge of Macros etc and need to get used to 2010 excel.

 - Even with that limited knowledge, probably more advanced than many users in the office

So given that information - where am I on the scale!?

Constructive criticism welcomed and any direction on an easy guide to IF stuff would be appreciated.

Thanks


----------



## SydneyGeek

It really depends where you work and what the job requires. If you can do what they need, go for it. 
"Advanced" in a general office environment might be very different to "advanced" for a futures trader, for example, and there is a huge range of capability even within those two broad sectors. 
The other thing to remember is that someone will consider you to be advanced if you know a bit more than them, a guru if you know a bit more than that. 

By the sound of it your skills could be a reasonable match for what they need, but there's nothing stopping you from using sites like this to increase your knowledge. You'd be silly not to. 

Denis


----------



## Smitty

Another thing to bear in mind is how well you know the business, and how creatively you can think.  I've seen "seasoned" users be stymied by a new position with regards to Excel (and other job aspects), because they hadn't learned the business, or couldn't think past what they already knew.

If you can look at data in ways that will be meaningful to the business, then you can learn what you need/can do with it in Excel, especially if you already have a good grasp of it.


----------



## Greg Truby

Try to find out *exactly* what they're looking for. Talk to whomever is in charge of the spreadsheet and, if you can, get a copy of the workbook(s). My old boss didn't have a clue as to how good I was with Excel. He literally told me one time that if I got stuck, Brett, an engineer with whom I worked knew a bit about macros and might be able to help me; not knowing that I had helped Brett out of several VBA jams and that I knew about twenty times as much as Brett when it came to Excel.

If the person in charge of hiring for this position is looking for someone to take the reins from himself then yes, he will be able to articulate what he needs. However if the hiring manager is looking to replace someone and he is not the person that built whatever-it-is; or if the position is new then there is a very good chance he or she will not have a deep grasp of the skills needed. Looking at the workbooks themselves is the best way to gauge whether this is something you think you can tackle.


----------

