# Off Topic > The Water Cooler >  >  At what point would you consider someone an advanced user of Excel?

## jlt199

I'm not sure if we're allowed general chat and opinions on this forum but I have a burning question so I thought I would give it a shot, I'm sorry if it's not allowed.

At what point would you consider somebody an advanced user of Excel?

I can happily use the following:formulas including sumifs, countifs etc.nested formulasif statementsvlookups, hlookups, index/matchfilter and sortconditional formattingcharting tools, including creating dynamic chartsI have written, not just recorded, some very complex macros

but, so far in my line of work (research scientist) I have limited experience with:pivot tablespowerpivotarray formulas
I think my experience should classify me as advanced, but when I've gone for job interviews in the past they always seem to associate advanced with pivot tables. In fact the tests I've been given at interviews have always been pivot tables.

I can use them, but it takes a bit of time to get what I'm looking for. In fact the biggest challenge I have is figuring out what I'm looking for, or what information I can get from the data.

So my question is two fold:What level should I put on my resume with the experience I have now?How can I get better at delving into data and using pivot tables?
Again, sorry if this post isn't appropriate here

Many thanks

----------


## Pete_UK

There is a Water Cooler Forum, which is specifically for items of general chat and opinions, so maybe you can ask one of the Moderators who happen to be on-line (see list on the Home page) to move this thread for you.

(I know that this does not specifically answer your question, but to me it's a bit like "how long is a piece of string?").

Hope this helps.

Pete

----------


## jlt199

Sorry, I see the Water Cooler now, and my post has kindly been moved.

I agree with how long is a piece of string, but job applications and resumes need a classifier and I want to meet people's expectations

----------


## pjwhitfield

I consider myself to be advanced when compared to the people I work with (none of us are dedicated Excel developers), I can do all the things in your two lists other than Array formulas, they are the bain of my life I just cant seem to get my head around the concept! I do quite complex VBA.

Compared however to the experts on here, Id say Im a mere beginner.

----------


## jlt199

> Compared however to the experts on here, Id say Im a mere beginner.



I know what you mean, but I've decided that there are at least two more levels above advanced; "Expert" and "Guru". That makes me feel slightly more comfortable stating that I'm not a beginner

----------


## MrShorty

From one research scientist to another, who also has no need for "database" work (like pivot tables), I, too, find this interesting. At the risk of tooting my own horn, I can use a spreadsheet to do some pretty serious science, math, calculus, etc., but I can barely use a pivot table, and I (mostly) despise array formulas as difficult to program, debug, and use.

When I was first learning spreadsheets (before Excel was the dominant application), I was often frustrated that almost all spreadsheet examples I could find were "business" oriented (which tends to be more database oriented) and seemed completely ignorant of science, math, and engineering applications of the software, that may not have much in common with database work. I think what you are seeing here is part of this "bias" towards using a spreadsheet for business/database work.

I don't have much advice. I might put advanced user of Excel with emphasis on math/science/engineering spreadsheets. Then become "conversent" with pivot tables and such so that you can answer at least basic interview questions about those spreadsheet tools. I would probably try to steer any discussion about Excel towards the things you do know (though, unless your interviewer can appreciate the math/science/engineering you do, they may not be able to appreciate it), but honestly say that you have never had need to do database work with Excel if pressed. That might also be a good time to ask if the position you are applying for will require significant amounts of database work? (and, if not, why they are even asking if you know how to use Excel as a database -- okay, that's probably a bit too combative for a job interview).

----------


## Glenn Kennedy

As another (ex) research scientist, I can understand why Pivot tables are often chosen by testers.  They can crunch lots of stuff down quickly and simply.  I was once described as the "King of the Pivot Tables".  Gradually, I moved away from them, preferring more dynamic formula-based solutions.  Now, I never use them...

Never forget that someone's EF status is highly dependent on the amount of time spent here....  there are a few spectacularly good people who are rarely here, or who joined recently.  they aren't Gurus.  Then there are old lags, like me, with too much spare time!!!

A VERY successful colleague of mine has a very simple attitude.  "The first step in becoming an expert is to describe yourself as an expert".  In his case.... it sure worked.

----------


## snb

I get rather interested to see the 'common' workbook of the research scientists that have contributed in this thread.

Can you guys please post some sample workbooks ?
It's rather difficult for me to visualize what your workbooks would look like if you did manage to do scientific research without 'data'(bases) and pivottables.

I'd like to add the ability to use querytables as essential to the epitheton: 'advanced user'.

----------


## dflak

It's a good question that has multiple answers.

I consider myself to be an expert, but there are a lot of things I don't know how to do in Excel (yet). I feel confident enough that I could figure them out given the right references.

I've been knocking around in Excel for about 20 years with the last 10 using it as a primary tool for my work.

I am an expert for the job I do. I know how to use all the tools that apply to my job. If I were a carpenter, I'd have to be proficient in using saws and hammers and planes and such. However, it would not be a drawback if I did not know how to use a voltmeter or wrench or plumber's snake.

In my job, I do a lot of work with data stored in Oracle Databases, so I have become a guru in MS-Query. My own SQL skills are at the intermediate level, so I am glad I have some SQL gurus to help me in that area. I've learned that you can do some things better in one application than with the other. For example VLOOKUPS can be used like JOINS, but JOINS go faster and can do more, whereas a pivot table runs rings around cross-tab queries in both performance and flexibility.

In my work also, we get a lot of reports, apparently generated from a database that are formatted in human-readable form. I have to parse through these reports to pick out the data that used to be in an organized fashion in the database, and convert them back into data. People are very protective of their data and won't allow access to it and they their bosses won't authorize the time and money for them to develop "special reports"

Some things, like user forms, I learned more as an academic exercise. I really don't have need for them in my work, but I can see where they come in handy for some applications.

Also in the past two years, I've begun to get into having Excel interact with other office applications such as Outlook and Powerpoint.

Don't dismiss pivot tables as a business tool only. It's the quickest way I know to get a unique list of names, for example, and can be used to gather and organize data. Overlaying pivot table results with named dynamic ranges is a powerful tool.

Likewise for array formulas. They can be used with just about any aggregate function where you need to eliminate exceptions or filter in only specific data.

I've played with PowerPivot, but I don't have immediate use for it in my work. So that does not exclude me from being an Expert for my job.

I the bottom line is: if you know the tools you need to get your job done, then you are an expert.

----------


## Glenn Kennedy

Difficult in my case - IPR issues.  But they were mostly relatively small sheets in analytical chemistry result calculation - processing data captured from analytical instrumentation, retrospective IQA analysis, etc.  Later on, they were more "management" related - Unit Cost calculations, financial planning reporting and forecasting.

the chemistry stuff's raw data rarely had more than 1000-2000 rows.  the management stuff rarely more than 10,000-20,000.  Not a user of "Big Data", by any means.

----------


## jlt199

> Can you guys please post some sample workbooks ?



Likewise for IPR I can't post a workbook here. My workbooks usually contain instrument measurements, comparisons to what they should be showing and a number of dynamic charts. I've also just learnt learnt that if you format your data as a table, you can add a slicer to your chart so you don't have to keep flipping back to the raw data and using filter  :Smilie:  As a general rule Pivot tables aren't helpful in my work.

----------


## MrShorty

@snb: As noted, actual examples can be difficult to share. A few from public sources and/or available elsewhere on this forum:
From another forum I frequent with several spreadsheets performing several different ChemE calculations: http://www.cheresources.com/invision...-spreadsheets/
Final solution was never presented, but I have done stuff like this: https://www.excelforum.com/excel-cha...ar-graphs.html
Regression + calculus (differentiation) type of problem: https://www.excelforum.com/excel-for...-in-excel.html
A couple that involve a fluid mechanics equation that often requires an iterative approach to solve: https://www.excelforum.com/excel-for...equations.html
https://www.excelforum.com/excel-pro...using-vba.html
How about some trigonometry: https://www.excelforum.com/excel-for...d-degrees.html
More calculus (differentiation): https://www.excelforum.com/excel-gen...m-a-curve.html
A regression question that was (likely) solved using shg's Fourier regression spreadsheet: https://www.excelforum.com/excel-cha...dal-curve.html
A cubic function solver that uses complex numbers: https://www.excelforum.com/excel-for...onsistant.html
Newton-Raphson in a spreadsheet (though the problems I solve are rarely polynomials and therefore tend to be more complex): https://www.excelforum.com/tips-and-...ind-roots.html

As others have noted, these tend to involve taking a few input variables, performing multiple operations/calculations on those input variables to get a few output variables.

I like dflak's conclusion -- if you know the tools you need for the work you do, then you are an expert.

----------


## jlt199

But, do I know the right tools to get through an Excel practical test in a job interview?

...I guess not, since I've had two and didn't get either job  :Frown:  But I'm sure in different circumstances I could Excel them under the table!

----------


## AliGW

Well, from your description of your capabilities in your opening post, I would say that you are definitely beyond beginner status. If the definition of 'expert' is being more capable than 99% of your colleagues, then I am definitely one, however within this community, I most definitely am not, and as I have no capabilities with VBA (which some would consider essential), I cannot compete with some of the wizards here.

Your last post is interesting: perhaps if you shared with us the type of problem you were given to solve with Excel, we might be able to tell you what you need to brush up on. Did you get any interview feedback?

I would strongly recommend that you get into Power Query (Get & Transform): there is lots of training available online and you can find plentiful tutorials. I have been working with it now for some months, and am just beginning to realise its full potential, having worked through vairous free resources I've found.

----------


## FDibbins

Another thing to consider, is that it is probably almost impossible to become a real expert in all aspects of excel.  It is just such a powerful tool, with so many different sides to it.  Like Ali, my VBA is poor to weak, but I would consider myself an expert/power user for most formulas and arrays (to some extent), but again, compared to some of the experts in here, I am merely adequate.

I have said many times, that I used to consider myself good at excel, until some of the members here showed me what good really looks like  :Smilie:

----------


## jlt199

> Your last post is interesting: perhaps if you shared with us the type of problem you were given to solve with Excel, we might be able to tell you what you need to brush up on. Did you get any interview feedback?



In the test that still haunts me, I was given a dataset regarding some antidepressant prescription medications and told to analyse it and see what I could discern (I was given 30 minutes). No specific questions to answer. It through me through a loop and I started panicing and as such didn't achieve much. I was able to tell them where the gaps in their data were and do some very preliminary analysis, but no visualisations etc. I couldn't quell the panic and think straight throughout. My weakness seems to be being able to discern what questions I can ask of the data I have. Especially database type data, which I have almost no access to. 

How do you hone that skill?

I'd also like more experience with Excel's BI tools and building dashboards, with the possibility of shifting over to that type of work.

----------


## jlt199

> Another thing to consider, is that it is probably almost impossible to become a real expert in all aspects of excel.  It is just such a powerful tool, with so many different sides to it.  Like Ali, my VBA is poor to weak, but I would consider myself an expert/power user for most formulas and arrays (to some extent), but again, compared to some of the experts in here, I am merely adequate.
> 
> I have said many times, that I used to consider myself good at excel, until some of the members here showed me what good really looks like



I think I would consider myself as adequate in most aspects of Excel, enough to get done what I want with help via Google and the occasional forum post. Is this broad knowledge, without being an expert in any one particular area, enough to make me advanced? Compared to my colleagues I would say I was advanced, but then we are far from an Excel centric organisation.

----------


## xlnitwit

If that is the sort of question you are being asked at interview, I would suggest that learning pivot tables should be your first priority- whether that is through a course, or book(s) or blog(s) like Contextures. The ability to slice and dice quickly by any combination of criteria is basically the essence of the pivot table. You will of course still need good reasoning and analytical skills to determine whether what you might see is a true correlation or pattern, and to decide what might be a good starting point, but being able to switch views with a simple drag and drop of fields will enable you to consider a multitude of options in a short time.

If you wish to go into BI and don't have much of a background in it, I would strongly recommend going on a course where you can actually bounce questions off an instructor rather than just reading a book or a blog.

I'm not sure it matters what you _call_ yourself- it's whether you can do what they ask in an interview that is likely to be more important.

----------


## TMS

Yep, based on that question alone, I would say you need to become proficient in creating and manipulating Pivot Tables.  It is a key built in Data Analysis tool and it allows you to quickly summarise large amounts of data.  You can then drag the fields around to get different "views" of the data. Sounds like that was what they were looking for.

With Pivot Tables under your belt, it should be a relatively simple step up to using Power Pivot ... though I've never used it so I'm prepared to be put straight on that.

VBA is very useful for coping with repetitive and labour intensive tasks, or building bespoke applications using forms, etc. But I would expect that an employer looking for VBA skills would specifically mention them in the job spec.  If you know it, you know it.  If you don't, it can take a while to get up to speed (although that may be improved if you have a programming background).  But you say you've written complex macros, so I guess you know that.  That said, are they as efficient and effective as possible?  Could they be improved?

You can do impressive things with Array Formulae ... but they can have a performance hit on your workbook (and possibly other open workbooks) if they are used excessively/badly.  And, quite often, there are non-Array Formulae solutions that are just as good, or better.

As others have said, it is unlikely that anyone (any mortal, anyway) can/will know everything about Excel.  But, the more you do know, the more likely it is that you will know what questions to ask and how to research a solution to a problem.

Using Forums such as this, not only to ask questions but to answer (or try to answer) them, will improve and enhance your skills

----------


## 63falcondude

Data Analyst here. I have been using Excel heavily for about 2.5 years.

I am comfortable in Excel when it comes to most built-in features such as formulas, data validation, conditional formatting, pivot tables, array formulas, etc.
If there is something that I do not know off the top of my head, I usually Google it and make modifications to work for the problem in front of me.
I know next to nothing about anything code-related.

That being said, skill is relevant. I have no idea how one person might perceive my skill even though I know that plenty see me as being advanced.

As others have suggested, helping others on this forum will greatly strengthen your Excel skills.
Even if you don't see a question asking for a Pivot Table solution, try to solve their problem using a Pivot Table. The only way of getting more comfortable with them is to use them frequently.

----------


## TMS

> If there is something that I do not know off the top of my head, I usually Google it and make modifications to work for the problem in front of me.



 Ditto.  I quite often see a question on the forum that I don't know anything about, Google it, take a few examples, test and try, and quite often I can offer a potential solution, learning in the process.

----------


## Logit

.
This is not an attempt to promote any other service or site :

https://chandoo.org/wp/advanced-excel-skills/

http://www.computergaga.com/tips/adv...el_skills.html

https://www.udemy.com/advanced-micro...-excel-course/

https://content.wisestep.com/essenti...ng-candidates/

https://www.linkedin.com/pulse/essen...luwasogo-glmp/


If you have completed a few interviews the experiences should have provided a 'base line' to draw from. What are the employers consistently asking/testing for ? If more than two or three are asking the same question .....






> In fact the biggest challenge I have is figuring out what I'm looking for, or what information I can get from the data.



From my 'excel hobby experience' that too is the biggest challenge. Oftentimes, the person making a request is not fluent in describing what the need is ... which makes it extremely difficult at best to provide a solution. 
When I view a thread with numerous postings ... it almost always seems due to the questions not being answered adequately. It takes several times 'around the block' to get to the starting line. Then there is the next 
challenge of using your skills to present a viable answer. Beating that hurdle comes from experience .. experience ... experience.

Persevere !   :Smilie:

----------


## sandy666

try to find: EXAM 77-420 Microsoft Excel - from MS

[I forgot: this is for basic users, if they do everything from this file, you can find another exam for advanced]

----------


## jlt199

Thank you very much for all your comments.

I agree I need to brush up on Pivot tables, but it's hard when you have neither a dataset or a purpose (obviously my purpose would be to learn pivot tables, what I meant was questions to answer regarding the data). I actually spent a day last week trying to solve a problem with pivot tables before realizing that their limitations wouldn't allow me to do what I needed. In the end I just wrote some VBA code to loop through the data and find the information I needed. I think I could have achieved what I trying to accomplish using PowerPivot, unfortunately I was building a solution for someone who doesn't have access to it.

----------


## xladept

I've been avoiding Pivot Tables as I've had no use for them - although. years ago I wrote an automatic set-up program. Before I joined this Forum, I had made my living with Excel VBA and had even written what became a rare book.  I thought,as a professional, that  I was an advanced user  but I have improved so much since then because of this site.  I also think that dflak nailed it.  Good Luck! :Smilie:

----------


## Doc.AElstein

> I've been avoiding Pivot Tables as ....



I avoided them as I tried once and instantly got a bad headache...   :Smilie: 
_.__________________________________________-
I know generally basic VBA quite well, and am catching up a bit on spreadsheet formula stuff that I seem to have missed. I don’t think that would quite be enough to get me as job or make me a professional / Expert in the real world. Certainly not as an advanced user.
I have always thought that an advanced user should know *and understand*  the tools that he is using. That is something similar to what  dflak said. But I think if you *know* the tools to get the job done then you can be a good professional “worker”. To be an advanced user I think you should understand them enough to have a chance for example,  of fixing them when they go wrong. Just a personal opinion. I find it a bit difficult to imagine an advanced user of Excel who is not also an Expert at Excel and the aspects of a computer associated with maintaining a successful use of Excel on a computer . 
 I can see that possibility in other forms of life. You could have a special qualification as an advanced train driver on some new revolutionary train system and  have just as little idea about how any train engine works as your mate that earns less than half you do shunting goods wagons around. But I don’t think with computers the parallel works too well. The train drive is not expected to fix the train in hardly any way what so ever. He has a massive amount of paid support there.
But I would guess that the IT Department would not to be expected to sort out  too many problems that crop up for an Advanced Excel User. So my suggestion is that an Advanced Excel User would be a very inefficient employee if he was not a fairly confident Expert at computer things effecting Excel as well. 

 I am trying hard to get there as an Expert, I am not always quite sure why though , apart from maybe that I don’t want to be always having to buy a new computer or new software because Microsoft try there best to get me to buy again, …  by giving up supporting the old stuff after  maybe sabotaging it with the last updates they virusesed me with.
 I am not certain that I will get there as an Expert… maybe.. I would like still to try. 

I think also if I wanted to call myself an  Expert/ advanced user , then  I should “branch out a bit more” 
I don’t think you need to know all about Excel and VBA to be a professional/ Expert in it as it is just too big. But if I was looking for a career in it then I would force myself into getting into some of the other areas discussed in this Thread. 
Those would include the areas like pivot tables. I have not got a clue about them , but the impression I get is that if you are serious of having a job in Excel then you should at least have a basic knowledge of them, along with some  “ advanced “drop down list” filtering stuff “  which my uneducated guess is somehow related . Its is on my list of things I will probably never get around to learning.

----------


## dflak

I make use of this forum to take on projects that do not have anything to do with what I do for a living. It forces me out of my comfort zone and causes me to look up things that I would otherwise not even think about. As a result, I have a few tools in my box that I do not have immediate use for.

Also, I've learned a bit about other industries and picked up some information about finance accounting, geology, horse trading,  etc. Is it just me, or do HR people seem to have the the most difficult workbooks to work with and are their requirements much better served with a database?

I also picked up a very small smattering of other languages and have recently worked with manipulating strings in Arabic and once worked a Hebrew spreadsheet where the columns were "backwards!" Also I learned about some regionalisms. For example the format "yyyymm" doesn't always work. I think it was Dutch where I had to use "jjjjmm" since year is jaar.

----------


## 6StringJazzer

I consider Pivot Tables to be essential intermediate knowledge. Power Pivot is an add-in; I consider that among the niche "expert" tools for Excel. That is, it is advanced, but the more advanced these features are, the fewer people really need them. (I have never used Power Pivot although maybe I should.) Other advanced features that you should know exist, but may never use, are

Scenario manager (What-If analysis)Data analysis tools like Goal Seek and Solver (there are several other data analysis tools that I have never used)Regression tools like Trendlines and accompanying formulas, and know how to interpret R2 

At some point, expert-level knowledge goes beyond Excel into things like forecasting and analysis. These areas simply use Excel as a tool. You need to learn the field first, then learn how to use a tool to support it. If you know absolutely nothing about forecasting but try to learn how Excel can be used for forecasting, you are putting the cart before the horse.





> In the test that still haunts me, I was given a dataset regarding some antidepressant prescription medications and told to analyse it and see what I could discern (I was given 30 minutes).



As I alluded to above, this is not an Excel test. This is a test to see if you understand data analysis, simply using Excel as a tool. I don't know what job you were being tested for, by what company, or what your background is, but I'm sure they were trying to determine if you understood analytical methods. You might also have to know something about drug trial protocols to make sense of the data. So they were testing your analytical and industry knowledge, not just your Excel knowledge. 

How do you learn it? Formal education and industry experience. For example, I have over 30 years of experience in project management, and took a graduate-level class in forecasting (which used Excel as the tool), and I still do not feel I'm an expert in forecasting. I learned regression analysis in upper-undergrad and graduate-level math classes and used it on the job before Excel was even invented.

----------


## dflak

> I
> At some point, expert-level knowledge goes beyond Excel into things like forecasting and analysis. 
> ...
> , and took a graduate-level class in forecasting (which used Excel as the tool), and I still do not feel I'm an expert in forecasting. 
> 
> I learned regression analysis in upper-undergrad and graduate-level math classes and used it on the job before Excel was even invented.



I am glad I am not alone with my headaches with forecasting. The first thing I had to get my head around is that this week we are talking about next week's forecasts and last week's actual results and that everything is reported week beginning and not week end. So actual results are as of last Saturday but reported as the previous Sunday and you have to look two weeks back to see how the forecast and actual results compare.

My background is in Electrical Engineering. One of the products I have to analyze is seasonal in nature and subject to annual growth. So I came up with the following formula to predict where it should be based on history:
A*SIN(B*t+C) + D*t + E. 
It's a sine function (for seasonality) with a linear growth component. I suspect that the growth is actually exponential but over the course of a year, linear works out "good" enough.

There was no way I was going to solve this algebraically, so I used solver to minimize the sum of the squares of the differences when compared to the predicted values calculated by the coefficients (the "answer" to my solver "question.") Left to "freewheel," Solver came up with a period close to 365 days for the various data sets, so it confirmed my suspicions of seasonality. The formula allowed me to pick out the signal from the noise, and I used the noise to manage the upper and lower control limits. When plotted, it was easy to see if an individual day's performance was in or out of limits.

As a short-term predictor (for next 6 months), it was good to within 10%. As an engineer, I was disappointed with the results. My co-worker (who does have experience with forecasts) was amazed.

----------


## ben_hensel

@dflak:
THERE IS A story about two friends, who were classmates in high school, talking about their jobs. One of them became a statistician and was working on population trends. He showed a reprint to his former classmate. The reprint started, as usual, with the Gaussian distribution and the statistician explained to his former classmate the meaning of the symbols for the actual population, for the average population, and so on. His classmate was a bit incredulous and was not quite sure whether the statistician was pulling his leg. "How can you know that?" was his query. "And what is this symbol here?" "Oh," said the statistician, "this is pi." "What is that?" "The ratio of the circumference of the circle to its diameter." "Well, now you are pushing your joke too far," said the classmate, "surely the population has nothing to do with the circumference of the circle."

----------


## Logit

.
Ok .. I'll be the one to say it ... HUH!?    :Confused:

----------


## 6StringJazzer

> So I came up with the following formula to predict where it should be based on history:
> A*SIN(B*t+C) + D*t + E. 
> It's a sine function (for seasonality) with a linear growth component.



That is pretty much what I would expect a EE to come up with  :Wink:  (I don't mean that disparagingly; I was a EE major but then switched to Comp Sci before I graduated.) It's a very sensible engineering approach. A sine function certainly gives the periodicity you might need for a seasonal model, although the contour may not fit the historical data as well as other approaches. But using Solver to optimize the parameters is pretty much the standard approach. I just pulled out my text and there are seven pages on seasonal forecasting with additive effects (which is simple enough to modify for growth over time), the book is large format (8.5" x 11") so I'm not going to try to summarize here.

----------


## 6StringJazzer

> .
> Ok .. I'll be the one to say it ... HUH!?



I assume you're talking about the circumference story that had us all ROTFL  :Roll Eyes (Sarcastic): 

I think it's related to *this*:



> One application that will doubtless be familiar to students taking high school statistics classes is the “normal probability distribution”, which governs a wide range of natural phenomena, ranging from rolls of dice and student test scores to measurements of distant supernovas. The normal distribution is given by the formula:
> 
> [formula here]
> 
> where the μl is the mean or average of the distribution and σ is its standard deviation.  π appears prominently in this formula, even though *there is no discernible connection to circles or geometry*.



 Emphasis mine. The point in both stories being that pi shows up in calculations that have nothing to do with circles.

----------


## Logit

.




> The point in both stories being that pi shows up in calculations that have nothing to do with circles.



Wow !  And I thought at times that I was a nerd ....   :Cool: 

Guess it went over my head because I never owned a slide rule or pocket protector.

----------


## 6StringJazzer

I no longer use a pocket protector but was the last generation to learn to use a slide rule in high school. When I was a junior, Chuck L. showed up at school with a newfangled Texas Instruments calculator. During a physics test his batteries died and he had to ask the teacher to sit near an electrical outlet so he could plug it in.

----------


## dflak

> I no longer use a pocket protector but was the last generation to learn to use a slide rule in high school. When I was a junior, Chuck L. showed up at school with a newfangled Texas Instruments calculator. During a physics test his batteries died and he had to ask the teacher to sit near an electrical outlet so he could plug it in.



I still have my TI-30. No, not the original that ran on a 9-volt battery, but an updated version. 

And yes, I still own all three of my slide rules: the 6" pocket version, the plastic "everyday use" one and the metal K&E one which might fetch a fair price at an antiques market.

----------


## kingpnp

> I'm not sure if we're allowed general chat and opinions on this forum but I have a burning question so I thought I would give it a shot, I'm sorry if it's not allowed.
> 
> At what point would you consider somebody an advanced user of Excel?



Here's the thing that I've noticed while interviewing or even reading job descriptions. When you hear someone ask you about pivot tables as if the other stuff you know doesnt matter. It's because odds are they only really know pivot tables and nothing else.  This is the thing about excel. Its so cavernous. that on one person knows everything or is a guru at all things excel. you have people that are darn good at most things and experts at some things. the issue is there is no job where you need ALL of excel to do said job. some jobs all you need is some pivot table knowledge or a little conditional formatting or a little bit of vlookup tossed in for good measure.  and lastly, anything you can be asked an answered with a pivot table can be answered in 10 different ways using excel without touching the pivot tables. It's just that at times pivot tables are the quickest way to your answer.  That to me is the difference between beginners and advance to experts.  
#1 do you know these things exists and do you know what they are supposed to do?
#2 do you know when to use which excel feature to be the most accurate and the most efficient to get the job done?

example:

Boss sends you this sheet:

Item                  Cost
red balloon         $5.00
green balloon      $4.50
Blue Toy Truck     $6.00
red hat               $10.00
Yellow Doll          $8.25
red  shirt             $10.00

If your boss sends this to you in  spreadsheet and asks:
What is the total cost for all the red items?
-You can of course do it by hand(but we have to imagine if this sheet is 1000 lines long with 500 different items). that wont be feasible.

- You can do a filter for "red"  . only red things with the name red in it will show up. then you can highlight the cost column and get your total at the bottom right of excel.   But what if he keeps asking you this question every monday and he adds another item color in the mix from time to time.

- You can use a split formula using Left, 3  or some nonsense like that. and only get the word RED all in one new column. filter by that column. use the costs there only and get your answer. While also getting any other item color with only 3 letters(thats the issue with going this route).

You can use an If statement formula.

You can use an If statement formula in your sheet. just to grab item colors in one column. THEN use your favorite Pivot tables to parse out every item by Color or Non Colored items and by Cost(total, average), Count of items, Max Item cost, Min, etc.

You can make the sheet a Table and pull up the table totals.  or you can use the last row and put in Sum formulas to get the totals.

If you're really cute you can use the DBase formulas. To turn excel to a mini access database. where you can give that to your boss and have it linked to a table you created. and he can ask it the question himself. and get accurate answer back.

There's 20 different ways to do the same thing in excel. the question remains, what is the most efficient way to do it that will produce the most accurate answer.  Is this a one off question? Will I be asked this question often or on a weekly, monthly, quarterly, etc basic? If so, maybe i need to setup something that will continuously answer that question without be doing anything else but pulling the current data and pumping it into a table. Your choice.

----------


## ben_hensel

The answer, of course, is




```
Please Login or Register  to view this content.
```


I'm not sure how to feel about immediately jumping on that, thinking, "yes, I have opinions about the best way to do that."

----------


## sandy666

Why "of course" ?  :Smilie: 



```
Please Login or Register  to view this content.
```

----------


## kingpnp

> ... I have no capabilities with VBA (which some would consider essential), I cannot compete with some of the wizards here.



You know VBA. you just dont know you know it because you rarely have had to use it in your day to day work. If you know If statements within formulas you know VBA. what you dont know is all the syntax. You can google that if need be. 

Dim Dog As Animal (what am I)
Dog = "Fido" (who am I)

what these gurus on here can do due to repetition is give you the best route home or the most efficient way to your answer. Because they've done it so many times before they already know that route, they know the pitfalls if you do it this way or that way. but the basic concepts of VBA is the basic concepts of If statements which is IF this is true do that, if false do that.  You can nest anything into the true or false section. same goes for VBA. If this is = to That, THen do this, ELSE(if its not = to that) Do this other thing.  the basics of all programming no matter how high level. it's plain jane logic.

----------


## ben_hensel

> Why "of course" ?



yeah that was the joke LOL

Sometimes I put something like that in my forum responses, "oh yeah that's just a ho-hum lookup" or maybe "what, that's a simple array construction" or even "everybody knows that you can't nest more than 7 IF statements in earlier versions of Excel"; and then I feel like I have no perspective at all.

----------


## sandy666

as you can see [  :Smilie:  ] it was joke also  :Wink:

----------


## kingpnp

> Why "of course" ? 
> 
> 
> 
> ```
> Please Login or Register  to view this content.
> ```



lol. of course. Now this is the most hilarious part of excel gurus. when you ask them a simple question and someone, never fails. Someone decides to give you a 5 line vba formula.  I'm thinking, now you know good and well all he/she needed was a 3 word formula in one cell to answer that.   But when people get hooked on a particular way of doing things and it has brought them results. They live and die by it. it's like a religious faction within the religion of excel.

----------


## sandy666

it was example for different way only. sometimes better is to use simple formula than PQ but with over million rows and columns (I don't remeber limits for worksheet) I prefer PQ because with formula like above Excel slow down and file is bigger. I think there is no _golden point_
_of course_ not with that formula  :Smilie:

----------


## kingpnp

excellent point. and you proved my point. You have gone thru the fire and made mistakes in the past thinking you can use a nice simple formula for a million rows of data. your excel froze and kept crashing.  then you found out about utilizing vba and now things are all better.   But thats a sign of advanced to expert excel user. one who knows when and where to apply the things they know. Not to just apply the things they know to everything all of the time.

----------


## sandy666

The expert is the one who knows where to find and how and when to use it - but he does not have to know everything in his mind  :Smilie: 

that is my point of view....

sandy out

----------


## kingpnp

I agree.  having everything in your mind in 2018 is no longer necessary. That's what the Google is for. lol. You save your mind power for thinking about ways to make things better/streamline processes etc. all of the specifics can be located online but like you said you have to know it exists and what you're looking for to even ask the question or search for the answer.

----------


## AliGW

Kingpnp - please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

----------


## AliGW

> You know VBA. you just dont know you know it because you rarely have had to use it in your day to day work. If you know If statements within formulas you know VBA. what you dont know is all the syntax. You can google that if need be ... it's plain jane logic.



I feel I need to clarify: I did not say that I am incapable of learning VBA or that I would not know how to go about it if I needed to. As a linguist, I know all about syntax.  :Wink:

----------


## kingpnp

Thanks for the heads up Ali :-)

I know... I did it again. I had to get that last one out of my system. :Wink:

----------


## kingpnp

I knew what you meant Ali. I was speaking in a general sense. Lot's of people think they are so far away from knowing something. At times you are, but with what we're discussing you're not as far off as you think you may be. Was the point I was making for those who may think that way.

----------


## AliGW

Yes, I agree, actually. What makes the mix here so good is precisely that there is a mix - of competences. I like that! And, for the record, I have actually managed to tweak a few bits of VBA in the past, but I've never taught myself properly.  :Smilie:

----------


## kingpnp

And see thats another point. You actually were teaching yourself properly. Thats how most programmers learn, especially VBA/macros. You record some stuff. Go back into the recorded macro (vba automatically setup for you) and you tweak it to your liking. The more you tweak the more you learn. Most of these gurus did not start off with a book on vba or a class going "here's step 1". And truthfully even in the good classes they will tell you start off by recording a macro first then tweaking it.   It's just amazing how there is so many ways to even write vba to get the very same result.

----------


## kingpnp

And to go back to the original theme in regards to employers asking for specific things in excel that they believe you need to know or specific things you need to know utilizing specific database management systems. The truth is, most of these employers are asking the wrong question.

Dont ask me if I know pivot tables. Dont ask me if I know SQL. Dont ask me if Know Crystal reports, Sap, etc, etc.

Ask me  a question my boss would want answered and let me give you the answer in a certain amt of time.
Dont test to see if I know pivot tables. or if I know how to do vlookup thru memory of the syntax. Even though you can do it thru point and click or not necessarily use it at all because their are 20 other ways to get that same answer.

Unless of course you're a DBA. Thats different. 
But if you're some type of analyst, or manager/person that needs to pull data to get answers to questions. Asking me do I know this one specific way to get your answer isnt what you care about. You care about the bottom line. If i ask you how close are our sales reps to meeting their sales quotas in the south east region. can you give me that data? YES oR NO . I dont care how you do it. I just want the answer.

I dont need to know YOUR system. You can hook me into the back in ODBC style and I can get that data for you all sorts of ways.

----------

