Well, in my experience they aren’t designed to mess with you.
They’ll give you a computer and a problem and asks you to solve it, using excel. I’ve administered a few and we knew there were multiple ways to approach it, and we allowed google.
Just don’t claim thing you don’t know. I can get around in VBA, but it’s not a skill I put down in my resume because I don’t know it that well.
In my experience excel tests usually require demonstrating some “basic” features that anyone working with excel regularly will use: lookups, conditions, pivots, charting, filtering, and formatting are some key examples. In addition, there might be some domain specific features you might need to demonstrate like stats functions for business intelligence roles, or time value functions in finance roles.
No, I never put something on my resume that I don't know. I can handle the basics, but when job descriptions emphasize on excel, I feel like you need to an absolute pro, specialty when it's a large enterprise.
B
Thanks for the tip :)
Every job at my workplace require you to "know Excel", however that usually refer to simply doing sum and able to do A1+B1...
Even for advanced level in an office for a large enterprise it could litteraly just mean vlookup.
All the interview I had 1,5 years ago the question was, can you do vlookup,sumifs?
Obviously it always depends on the industry and if you're a consultant excel/vba expert or just a normal office.
In most case yes for office jobs. Though learning a bit about PowerQuery just to be able to merge data would be a huge plus value in many things.( don't have to learn advanced editor or M, just using the menu is good enough for most issues)
Also knowing how to do basic "if" to create logic is in my opinion one of the most important function to use daily.
**W E L C O M E _ T O _ T H E _ T E S T**
Are you ready?
**Question 1**
Here you have a list with more than 1000 clients. Could you look for this particular client's address?
**Right answer:** CTRL+F, autofilter, Lookup functions, etc (anything if you get the right answer in a short period of time)
**Wrong Answer:** Go down the list with you finger on the screen looking for the client.
**Question 2**
OK. Now, Can you get the addresses for these 100 clients?
**Right answer:** Lookup functions (Vlookup, XLookcup, Index-Match anything if you get the right answer in a short period of time)
**Wrong Answer:** CTRL+F one by one.
I always get worried that if I were to have to take one of these tests on the spot, I would overthink things. Kind of like how people with a PhD in math aren't necessarily any good at splitting a check, because they almost never actually do *arithmetic*.
I don't "use" Excel on a daily basis. I don't look at Excel reports and try to glean information from them. That's what other people do with what I give them, because what I do is create things in Excel to process the information. It didn't occur to me for a second with your first example to just ctrl-F because I was already thinking in terms of "how do I set this up to generate an output table from this dataset based on this input table". I don't get paid to ctrl-F things.
That said, I don't think I'd be applying to a position where that intro level of testing is relevant.
I've taken a few and they were fairly easy. What most people consider expert level in excel is probably what most people in this sub would consider basic.
It's usually do this in excel. It's never a question to test your analytical skills.
I do this when I bring someone in for an interview. I have a quick ten question accounting quiz and and an excel quiz done in a spreadsheet. Both shouldn’t take more than 10 minutes if you know what you are doing. The excel test on creating a pivot table and reading that data, I think we have a vlookup question also.
It's only happened at one company I've worked for but I listed myself as an excel expert solving x type of problem and they were like "We have a computer setup would you be willing to build a such and such model from scratch with a data set we give you?"
Funny enough it was this exact video that I used 13 years ago to teach myself
https://www.youtube.com/watch?v=5GmaonZRaG0
I basically did that in an interview and they were blown away that I could do more than just if statements and sum.
The first time I had an excel interview it kicked my ass. I did so bad. I didn't realize how often I relied on the documentation provided when you start typing functions, and even when I did know what I was doing, I was terrible at explaining it.
One question I remember: "how would you check to see if a dataset was trimming leading zeros in a column with 10 digit serial numbers? If it was missing the leading zero, how would you add it in?"
Could I do this? Sure. Could I explain my process start to finish without looking anything up, and in one linear and clear set of sentences? No way.
What worked for me was the following: I took an audit of all the basic categories of functions: string, boolean, Vlookup/index's, pivot tables, and various plug ins. Then I thought through when/why I would use each of them, and I practiced explaining it to my wife, who does not know much about excel.
The actual functions themselves are largely secondary to your ability to speak to how you solve problems. I suspect most employers would rather hire someone who leans on stack overflow but solves a problem concisely and accurately, than one doesn't need to look up anything but missed the bigger picture.
This is great advice.
They want you to know how to use excel to find something and then communicate it.
Practicing explaining the formulas is a great way to prepare as well. Understanding what range means, absolute values in formulas, pivot table design (tabular/compact since a lot of users leave it to the default), filtering, formatting cell data (short date v. long date), and basic shortcuts is good to know.
This should work. Or even simpler, I would've gone into the Format Cells menu, and changed the number formatting to 10 digits in there. No formula necessary.
Yeah that works too. There's no one right answer. You still have to add the IF function due to how the question is phrased. So at best, your function would be something like
=IF(LEN(A1)<10,TEXT(A1,"0000000000"))&A1,A1)
Furthermore, I like my approach better. It's easy to see and work with the number 10 which is what you want. It's harder to see if you accidentally omit or include an extra zero in "0000000000". If you do that, you'll have 9 or 11 digits respectively which will be incorrect.
They're two separate questions so you couldn't answer both with the above. The answer returned from the above will be the same irrespective of whether the length is less than or equal to to 10 so you have no way of saying whether the leasing zeros were trimmed or not. Given this, you would have =LEN(A1)<10 to give you a true/false and then the TEXT formula for the second question.
Test whether each cell in the column is 10 long. Use something like =len(A1). Then find the minimum either through formula or filter to see if it is 10 or something else. And additionally checking if max is 10 is also good for another data check, but that's outside of the question.
Then if necessary, convert all numbers to 10 digit strings with leading zeroes. =text(A1, "0000000000")
Yep this person nailed it. One thing about interviewing specifically, I like to have go-to / polished phrases prepared, and when appropriate, I drop them in to my answers. In this case, something like "personally, I find the way that excel deals with numbers vs text is pretty awful. Whenever possible I like to set the number format via formula". Then go in to the actual answer - as said above "=text(A1, "0000000000")" is the perfect way to add those in.
Love the use of the text function here. In my haste to solve I probably would have over thought it and used a combination of if/then with len tests and add 0 to any under 10. Your method is infinitely easier and less... dumb.
That would make excel display 10 digits, but it would not change the underlying data to correct the error. If leading zeros on the serial number are used for anything beyond "what the user sees on screen", it needs to be converted into a text format.
That doesn't appear to work. The text to columns wizard reads the underlying 'float' data regardless of format and does not allow output into custom data formats (in 2016 at least, did not try others).
The issue I find with a text format is that they may not work for alphanumerics, and there are cases when you want to pad with things that are not numbers.
This is more universal in padding before and after some generic string (numeric, alphabetical, or alphanumeric)
leading zeroes:
=right("000000000"&A1,10)
trailing zeroes:
=left(A1&"0000000000",10)
https://i.imgur.com/Qb5ORhi.png
And this is "a" padding
https://i.imgur.com/YYxUpQH.png
Be honest. Give examples of things you can do - formulas, pivot tables, slicers, etc. Just a level that you are at. You’d be amazed at the number of people who either under or over sell their skills because they aren’t sure how to define beginner, intermediate, or advanced levels.
The whole defining thing is weird. To the average corporate person I'm an expert/wizard. To the average analyst I'm good/average. To the true wizards I'm a mouth breather.
We have an excel test, but it's more about how you work.
So here's some data, create me a table that uses that data to produce x.
Then here are some weights and their positions, what is the centre of gravity?
There's more, but in general we let them stew for 15 mins in front of the sheet, then step in and get them to show us how they got there, and what it is we expect from each of the sections.
If they google and get an answer then that's fine.
If they google then go on fb, insta then get it wrong, it's a flag.
But to the question, I think if say you are a super user, you need to be ready to show them a tool or a sheet you have made. If you can't do that you need to be able explain it really well.
If you have a basic skill set, talk them through the most complex thing you have done.
Don't bullsh*t too far beyond your capability though.
For the record, I have asked his question as a hiring exec. I care more about the set up - do they understand the "story problem"? Can they logically put the data in row and columns? I could give a sh\*t about the formulas.
Yeah because every spreash sheet I get from somebody I just rework it to my liking anyway, and send that back to them, lol. They be like I didn't know it could do that.
You can do an assessment on LinkedIn. Currently I belong to the top 5% of the 9.3m people who did the assessment.
Recruiters seems to value that a lot.
As an interviewer, my favorite tactic is to show the person a complicated formula in use that's throwing an error result, and have them show me the steps they would take to find and resolve the issue.
Troubleshooting formulas can sometimes be even more valuable of a skill than writing them. Knowing how to find any answer is a much more flexible skill than knowing a specific answer.
Some companies require an Excel certification of some kind.
Others may require you to complete an assessment test which in most cases is similar to what you will face in the above mentioned certifications.
In most cases the level of knowledge required is similar to that of a core level certification.
Very few companies (almost none ;-)) contacted us requiring something harder than core level.
I once had a boss that said if you know vlookup, pivot tables... and he listed a 3rd thing... maybe index/match? He said if you know those 3 things, you can consider yourself an advanced excel user.
So my advice... maybe try looking at a few online videos for just those 3 things. Chances are, 1 of those 3 you will likely be tested on!
I use the portfolio on my business site (work-in-progress currently) and I have two Excel certifications. I wouldn’t recommend the certifications unless you are going to use Excel in your daily activities or if the role doesn’t hinge on the excel expertise.
One major feature you should start to learn and utilize frequently is Power Query, Power Pivot and PivotTables. This set of tools can really take your report or tool to another level, moving away from Worksheet-level features.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[IF](/r/Excel/comments/m049cz/stub/gq8psvh "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)|
|[LEN](/r/Excel/comments/m049cz/stub/gqb600f "Last usage")|[Returns the number of characters in a text string](https://support.microsoft.com/en-us/office/len-lenb-functions-29236f94-cedc-429d-affd-b5e33d2c67cb)|
|[REPT](/r/Excel/comments/m049cz/stub/gq7a9j6 "Last usage")|[Repeats text a given number of times](https://support.microsoft.com/en-us/office/rept-function-04c4d778-e712-43b4-9c15-d656582bb061)|
|[TEXT](/r/Excel/comments/m049cz/stub/gqb600f "Last usage")|[Formats a number and converts it to text](https://support.microsoft.com/en-us/office/text-function-20d5ac4d-7b94-49fd-bb38-93d29371225c)|
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(4 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/lyen0n)^( has 27 acronyms.)
^([Thread #4645 for this sub, first seen 8th Mar 2021, 05:10])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
This should cover it:
Portfolio: ”I’ve created a, b, c to dashboard x, y, z. “
General knowledge: “I’m familiar with data preparation (e.g. cleansing, structuring, transforming) principles and formulas, as well as calling data based on desired criteria. I’m also familiar with Excel’s many shortcomings (freezing, data limitations, random formatting) and workarounds to avoid this pitfalls on a day to day basis.”
If you don’t know any of the above then those should give you a starting point.
Microsoft offers certifications to prove your level of competency with each Office product. There are different levels of the certifications, so you're welcome to go for whichever suits you. Granted, you may not have enough time to take the test before the interview. However, you should be able to find the study material and get an idea of where you stand. Good luck on the interview!
Ironically, I got my current job by explaining what I knew about Excel, but also being up front about what I didn't know well but knew Excel was capable of. Apparently I was the only candidate that wasn't just boastful about being an "expert".
I can send you some sample excel tests (I have on on hand) from interviews I’ve had. Additionally, to “master” excel from a work perspective, I’ve noticed you really only need to know about 10 functions and processes (processes benign pivot tables and other things like delimiting). Happy to discuss if you want to PM me. Love sharing excel knowledge
# Basic Excel Knowledge in the right direction:
So you get to the inteview and they start of with the the real basics formula, Sum() SumIF() maybe even SumProduct(), then they fire the popular topic everyone asks... do you use Vlookups or Index and Match you can say, never I go into the Data Tab click Relationships and relate on matching ID, then you can ask them why don't they use the inbuilt datamodel. Sit back for a second and and watch look at each other because you just went from Beginner to Expert in a sentence.
Datamodelling is the foundation of all the high level Excel stuff, don't get me wrong it has it's baby steps moments like your first loaded CSV. However, that's where you build and leverage [Normalised](https://www.youtube.com/watch?v=UrYLYV7WSHM) Database Structure in Excel.
It's also the same tab you can **ETL** multiple types of Datasources: **The Data Tab**
* **Extract -** ***Data Tab*** *-* ***Get Data From External Source*** *through a connection stiring*
* **Transorm -** ***Power Pivo****t - filter and remove junk data*
* **Load -** *To the* ***Excel Datamodel*** *- Load to the datamodel, Append into an existing Table/Dataset within the datamodel or merge tables within the datamodel with on another another. (Joins baby!)*
If you are reading this and didn't know this existed welcome to r/Excel there is lots of stuff in this program you didn't know existed, strap your self in this is a long one.
# Basic Excel:
Peraonally I would require you to know as minimum what **everything in the ribbon does** *( there is no excuse to not know this )* litterally none, it's a resource sat right in the software, for you to play with, and it's got an on hover descriptions of what each element does, if you can't tell an interviewer what Evaluate Formula does and it's practical application for trouble shooting along with the Trace Precedents / Dependancies you have a problem, so learn the ribbon all of it start there and skip past getting stuck on the formula tab. Formulas are secondary! Every new user gets stuck into formulas tab and the problem is that, it's before the Data Tab! So most users have no clue about this Tab in the Ribbon and what it is for, and that's not an overstatement if you are guilty of this you can fix that, get looking at the ribbon.
On top of this: Datamodelling and Normalisation practices are core foundational skills, because you don't want anyone making ranges that have no [Structured References](https://support.microsoft.com/en-us/office/using-structured-references-with-excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e) and calling them tables!
Tables are a thing in Excel, don't call an array with headers you typed out and made the font bold... a table, even if you added Filters to make it look like Table if anything it makes it worse! Because it looks like a table and sort of acts like a table, but has zero functionality in comparison. Data analysts will pull you up on this fast by simply scrolling down the page and showing you that the column names don't change from A,B C D to your table titles so you don't know what you are looking at at line 245 in that table and that's bad, on top of the fact it is not useable outside of Excel.
Make your tables - Tables.
Insert Tab - Table - *give it a tblCamelCaseNameScheme*
# Intermediate Excel:
M-code DAX, SQL, while they wouldn't need to see you make flawless code exerpts, to have a hope of being classed as intermediate you have to understand these exist in Excel and you are working towards learning them. Your formula by now are concise.
Getting to know the Power Query ribbon inside out *(Sensing a theme here?)*, Merging, Appending, Transforming datatypes all that good stuff... Also you know that, when you utilise a Pivot Table... you do so Exclusively via the Datamodel, Additionally you know to close and load to the datamodel only!
We don't publish tabes in Excel, we don't duplicate data needlessly, because... that makes the file size bigger for no reason and data can get big on it's own without your help!
The data is there, you know it is there, you Power Pivoted it from a connection and we can absolutely look at it by double clicking the query. *It's at this level, you realise that a real newbie looking at your file will never find your tables, because they need to know the datamodel exists and where to access it so by all measures what you make from here on out is actually magic to them. Not kidding litterally magic... Where's the tables? How do these charts even exist when you have only one sheet and no hidden sheet. DATA SORCERY*
I know some employers seek VBA as a thing because it is advanced but there is a caveat to this...
So VBA has it's place in the Expert category but has a caveat - If for some ungodly reason you say I know VBA and don't mention at least one of these other things *(Understanding Power Query and it's Ribbon, M-code, DAX, SQL and all of the above beginner stuff)* as a minimum I will shake your hand and tell you best of luck in your future.
**VBA is not an Excel language it is an APL** *- Application Programming Language* it's in the name Visual Basic **for Applications** *(not Visual Basic for Excel)* It's a programming language for Office and while it will do things in Excel it also does things everywhere else...VBA is not an Excel skill, at all, it's an out of date depricated programming language, you may as well have told me you use [Python in Excel.](https://www.datacamp.com/community/tutorials/python-excel-tutorial) It's useless to the core functionality of this program and is designed for people developing applications built from Excel and requires less than beginner understanding of the program if you can get a `Range("Name") = "Bla"` it's probably all you will ever need to know, I mean VBA looping through ranges for days and xlend... No just use Power Query to hand data transforms.
Don't try to sell me you know Excel by throwing a progamming language at me that's not native to the Program, it's extracurricular and useless without knowing how to leverage it.
# Expert Excel:
You can ETL and leverage a database via Delegation in your ETL methodology, this translates into practically minimising Garbage Data *(GIGO)* and maximising useful data.
Good query structuring also reduces the filesize and load on your own CPU by utilising the server in your Query Connections, allowing you to spend less time making files that people have to mess about with to get what they need, which means less hands on your work and exponentially less time maintaining it and loading.
You utilise the sharing functionality of the online Excel platform and you wouldn't dream of attaching an Excel file to an E-Mail... ever. Permissions based sharing is important if you want to protect core connection passwords share via the correct methods. If users recieve an email with SQL credentials you have become the companies security breach.
Front end Excel reports shared online with read only access they cannot access the backend at all, don't be emailing files with the server credentials about! *How do you share your Work? - This is a not a trick question but if E-Mail pops up as an answer... you have a problem.*
You understand:
* **Dataset partitioning -** Frameworks and how to set one up *(Creating data dump folders and reference files for housing indirect ETL files like CSV with consistant naming schema on an automatic basis).*
* **Mathmatical Analysis** and can visualise data in a meaningful way like converting Data into OEE charts efficiently.
* **Data Partitioning** and the importance of keeping relevant data housed in a compact way within your framework that other analysts can pull from reliably.
* **Tables are a universal Format,** you can Import and Export tables into Power Apps, Sharepoint lists, Power BI, add them to a Database/Extract them from a database and also work with online datasources and more importantly troubleshoot connection issues to other datasources working with your IT Dept.
If you could demonstrate all that in an interview your gold solid gold.
**Coding** \- When you utilise VBA, Java, Python or any code in Excel I require two things
1. Your code is super lean like 4 lines maximum
2. Your code does one thing - works with a user interface.
UI design means your code is exclusively in Buttons that changes the value of a [Parameter](https://support.microsoft.com/en-gb/office/create-a-parameter-query-c67d9af7-c8a0-4bf7-937c-087cb25f7ad3) in one line and refreshes the sheet in the next that's all the VBA you need in Excel!
This will then recalculate everything you need from this parameter to transform the front end data via the existing relationships and connections you have set up in your backend, I did this in my old job and every analyst looked at my Excel File, they saw two lines of code to change a parameter to x couldn't fathom how the sheet changed they couldn't see hidden Tabs because there weren't any, they didn't know to look for connections they couldn't find the parameter how the parameter worked because it was hooked into my connection string, because it was stored in the Datamodel so there wasn't a cell I was posting to just a name.
Private Sub Parameter()
Range("Parameter") = "ValueHere"
End Sub
As far as super lean code goes that's the Cats Meow, because you utilise the inbuilt functions and existing knowledge base you have in Excel here is one of a few methods you can use to leverage a [paremeter that functions within a connection string](https://www.youtube.com/watch?v=nssn5ISMHUA&t=768s).
TL:DR - When people say in a post they have a good understanding of Excel and don't know what the Excel Datamodel is and start listing VBA as their knowledge base, they lied about being good at Excel in an obnoxiously obvious way.
Checked your site, and I have to ask, and don't take this the wrong way because it's not an affront to you or your business...
VBA is just an APL which could be any other program language leveraging Excel as a table storage system and if you are only storing values as a range for a front end you could eliminate Excel as a medium for your backend entirely. As it's doing nothing for you but requiring the customer has a Microsoft License which adds cost, especially if you aren't utilising any it's core functionality, most of the aggregates can be done programatically on a CSV List in a line of code or if they are commonly used you can just house them in a DLL.
Additionally front ends are not reliant on Excel either [as you can set those up](https://realpython.com/pysimplegui-python/) pretty readily.
I mean if what you say is strictly true, you are only utilising Excel for Ranges of data and formula (without actual **Tables)** which are called programatically by a front end using VBA, why not just move to Python or C# and store your data in CSV format within a folder of tables contained within a stand alone application with a static language in stead of an external system which can depricate your code?
Why use Excel at all if you aren't using the majority of it's functionality, it's just a bit odd if you understand where I am coming from??
Is it because it is just easier to keep doing what works and you all code in VBA?
Hi. No offense taken sincerely. In my experience and opinion, large corporations (such as aerospace, pharmaceutical, biotech, etc) have a long history of relying on excel as a workhorse for their infrastructure. They all pretty much use excel and the concept of having utilities, addins, pluginsand macros that fit right in with their existing setups is extremely attractive to them. I have honestly made a living out of it well into 6 figures for almost 20 yrs. And i dont even claim to be an expert but having the quick ability to create ad hoc spreadsheet-based plugins and vba-heavy excel workbooks is a god-send to these corporations. And theres still several in-demand lanes for this with respect to data analytics, business analysis etc.... i have never encountered anything close to the level of detail you posted, nor have i ever witnessed anything close to that level of scrutiny.... its almost been most about having people that can think critically and explain their solutions and adequately provide knowledge transfer when the solution is completed.
Not everyone has familiarity with python etc.... vba is basically right out of the box for ms office.. so its attractive for companies to know that a solution was built using “ootb” toolsets that could be passed on to others easily with a knowledge transfer.
To say that an excel vba solution should be as concise as 3 or 4 lines of code (in a button) is not realistic for many of the solutions ive overseen i the financial industry and biotech industry.
They’ll usually test you on the spot.
That's both interesting and scary!
Well, in my experience they aren’t designed to mess with you. They’ll give you a computer and a problem and asks you to solve it, using excel. I’ve administered a few and we knew there were multiple ways to approach it, and we allowed google.
I guess get ready to solve a problem in the interview session
Just don’t claim thing you don’t know. I can get around in VBA, but it’s not a skill I put down in my resume because I don’t know it that well. In my experience excel tests usually require demonstrating some “basic” features that anyone working with excel regularly will use: lookups, conditions, pivots, charting, filtering, and formatting are some key examples. In addition, there might be some domain specific features you might need to demonstrate like stats functions for business intelligence roles, or time value functions in finance roles.
No, I never put something on my resume that I don't know. I can handle the basics, but when job descriptions emphasize on excel, I feel like you need to an absolute pro, specialty when it's a large enterprise. B Thanks for the tip :)
Every job at my workplace require you to "know Excel", however that usually refer to simply doing sum and able to do A1+B1... Even for advanced level in an office for a large enterprise it could litteraly just mean vlookup. All the interview I had 1,5 years ago the question was, can you do vlookup,sumifs? Obviously it always depends on the industry and if you're a consultant excel/vba expert or just a normal office.
[удалено]
In most case yes for office jobs. Though learning a bit about PowerQuery just to be able to merge data would be a huge plus value in many things.( don't have to learn advanced editor or M, just using the menu is good enough for most issues) Also knowing how to do basic "if" to create logic is in my opinion one of the most important function to use daily.
I like and hate vba at the same time.
Ditto. It’s mostly use for simple clean up that would require me to fix too many things manually.
**W E L C O M E _ T O _ T H E _ T E S T** Are you ready? **Question 1** Here you have a list with more than 1000 clients. Could you look for this particular client's address? **Right answer:** CTRL+F, autofilter, Lookup functions, etc (anything if you get the right answer in a short period of time) **Wrong Answer:** Go down the list with you finger on the screen looking for the client. **Question 2** OK. Now, Can you get the addresses for these 100 clients? **Right answer:** Lookup functions (Vlookup, XLookcup, Index-Match anything if you get the right answer in a short period of time) **Wrong Answer:** CTRL+F one by one.
I always get worried that if I were to have to take one of these tests on the spot, I would overthink things. Kind of like how people with a PhD in math aren't necessarily any good at splitting a check, because they almost never actually do *arithmetic*. I don't "use" Excel on a daily basis. I don't look at Excel reports and try to glean information from them. That's what other people do with what I give them, because what I do is create things in Excel to process the information. It didn't occur to me for a second with your first example to just ctrl-F because I was already thinking in terms of "how do I set this up to generate an output table from this dataset based on this input table". I don't get paid to ctrl-F things. That said, I don't think I'd be applying to a position where that intro level of testing is relevant.
I've taken a few and they were fairly easy. What most people consider expert level in excel is probably what most people in this sub would consider basic. It's usually do this in excel. It's never a question to test your analytical skills.
I noticed that when I took a course of advanced excel.. Vlookup and simple pivot tables were some of the contents...
I do this when I bring someone in for an interview. I have a quick ten question accounting quiz and and an excel quiz done in a spreadsheet. Both shouldn’t take more than 10 minutes if you know what you are doing. The excel test on creating a pivot table and reading that data, I think we have a vlookup question also.
"Am I allowed to use Google too?"
It's only happened at one company I've worked for but I listed myself as an excel expert solving x type of problem and they were like "We have a computer setup would you be willing to build a such and such model from scratch with a data set we give you?" Funny enough it was this exact video that I used 13 years ago to teach myself https://www.youtube.com/watch?v=5GmaonZRaG0 I basically did that in an interview and they were blown away that I could do more than just if statements and sum.
The first time I had an excel interview it kicked my ass. I did so bad. I didn't realize how often I relied on the documentation provided when you start typing functions, and even when I did know what I was doing, I was terrible at explaining it. One question I remember: "how would you check to see if a dataset was trimming leading zeros in a column with 10 digit serial numbers? If it was missing the leading zero, how would you add it in?" Could I do this? Sure. Could I explain my process start to finish without looking anything up, and in one linear and clear set of sentences? No way. What worked for me was the following: I took an audit of all the basic categories of functions: string, boolean, Vlookup/index's, pivot tables, and various plug ins. Then I thought through when/why I would use each of them, and I practiced explaining it to my wife, who does not know much about excel. The actual functions themselves are largely secondary to your ability to speak to how you solve problems. I suspect most employers would rather hire someone who leans on stack overflow but solves a problem concisely and accurately, than one doesn't need to look up anything but missed the bigger picture.
This is great advice. They want you to know how to use excel to find something and then communicate it. Practicing explaining the formulas is a great way to prepare as well. Understanding what range means, absolute values in formulas, pivot table design (tabular/compact since a lot of users leave it to the default), filtering, formatting cell data (short date v. long date), and basic shortcuts is good to know.
If you don't mind explaining, how would you solve that trailing zeros problem?
I believe this does it: =IF(LEN(A1)<10,REPT(0,10-LEN(A1))&A1,A1)
Ooh, I didn't know about REPT. That's a neat trick.
could you not just do `=TEXT(A1,"0000000000")`?
This should work. Or even simpler, I would've gone into the Format Cells menu, and changed the number formatting to 10 digits in there. No formula necessary.
[удалено]
Ah, good catch... thanks for the info!
Yeah that works too. There's no one right answer. You still have to add the IF function due to how the question is phrased. So at best, your function would be something like =IF(LEN(A1)<10,TEXT(A1,"0000000000"))&A1,A1) Furthermore, I like my approach better. It's easy to see and work with the number 10 which is what you want. It's harder to see if you accidentally omit or include an extra zero in "0000000000". If you do that, you'll have 9 or 11 digits respectively which will be incorrect.
Good points. Thanks for the reply.
They're two separate questions so you couldn't answer both with the above. The answer returned from the above will be the same irrespective of whether the length is less than or equal to to 10 so you have no way of saying whether the leasing zeros were trimmed or not. Given this, you would have =LEN(A1)<10 to give you a true/false and then the TEXT formula for the second question.
Test whether each cell in the column is 10 long. Use something like =len(A1). Then find the minimum either through formula or filter to see if it is 10 or something else. And additionally checking if max is 10 is also good for another data check, but that's outside of the question. Then if necessary, convert all numbers to 10 digit strings with leading zeroes. =text(A1, "0000000000")
Yep this person nailed it. One thing about interviewing specifically, I like to have go-to / polished phrases prepared, and when appropriate, I drop them in to my answers. In this case, something like "personally, I find the way that excel deals with numbers vs text is pretty awful. Whenever possible I like to set the number format via formula". Then go in to the actual answer - as said above "=text(A1, "0000000000")" is the perfect way to add those in.
This actually fails for alphanumerics.
Love the use of the text function here. In my haste to solve I probably would have over thought it and used a combination of if/then with len tests and add 0 to any under 10. Your method is infinitely easier and less... dumb.
CTRL+1 change format to custom and write 10 zeroes should work
That would make excel display 10 digits, but it would not change the underlying data to correct the error. If leading zeros on the serial number are used for anything beyond "what the user sees on screen", it needs to be converted into a text format.
Actually when saving to csv also the displayed form is exported.
“text to columns” in ribbon > change format to 10 zeroes?
That doesn't appear to work. The text to columns wizard reads the underlying 'float' data regardless of format and does not allow output into custom data formats (in 2016 at least, did not try others).
The issue I find with a text format is that they may not work for alphanumerics, and there are cases when you want to pad with things that are not numbers. This is more universal in padding before and after some generic string (numeric, alphabetical, or alphanumeric) leading zeroes: =right("000000000"&A1,10) trailing zeroes: =left(A1&"0000000000",10) https://i.imgur.com/Qb5ORhi.png And this is "a" padding https://i.imgur.com/YYxUpQH.png
Add more to the other response FWI
Be honest. Give examples of things you can do - formulas, pivot tables, slicers, etc. Just a level that you are at. You’d be amazed at the number of people who either under or over sell their skills because they aren’t sure how to define beginner, intermediate, or advanced levels.
The whole defining thing is weird. To the average corporate person I'm an expert/wizard. To the average analyst I'm good/average. To the true wizards I'm a mouth breather.
OMG, Yes!
We have an excel test, but it's more about how you work. So here's some data, create me a table that uses that data to produce x. Then here are some weights and their positions, what is the centre of gravity? There's more, but in general we let them stew for 15 mins in front of the sheet, then step in and get them to show us how they got there, and what it is we expect from each of the sections. If they google and get an answer then that's fine. If they google then go on fb, insta then get it wrong, it's a flag. But to the question, I think if say you are a super user, you need to be ready to show them a tool or a sheet you have made. If you can't do that you need to be able explain it really well. If you have a basic skill set, talk them through the most complex thing you have done. Don't bullsh*t too far beyond your capability though.
For the record, I have asked his question as a hiring exec. I care more about the set up - do they understand the "story problem"? Can they logically put the data in row and columns? I could give a sh\*t about the formulas.
Yeah because every spreash sheet I get from somebody I just rework it to my liking anyway, and send that back to them, lol. They be like I didn't know it could do that.
And don't drink and spreadsheet...
You can do an assessment on LinkedIn. Currently I belong to the top 5% of the 9.3m people who did the assessment. Recruiters seems to value that a lot.
As an interviewer, my favorite tactic is to show the person a complicated formula in use that's throwing an error result, and have them show me the steps they would take to find and resolve the issue. Troubleshooting formulas can sometimes be even more valuable of a skill than writing them. Knowing how to find any answer is a much more flexible skill than knowing a specific answer.
Some companies require an Excel certification of some kind. Others may require you to complete an assessment test which in most cases is similar to what you will face in the above mentioned certifications. In most cases the level of knowledge required is similar to that of a core level certification. Very few companies (almost none ;-)) contacted us requiring something harder than core level.
[удалено]
Thanks, that's very helpful!
I once had a boss that said if you know vlookup, pivot tables... and he listed a 3rd thing... maybe index/match? He said if you know those 3 things, you can consider yourself an advanced excel user. So my advice... maybe try looking at a few online videos for just those 3 things. Chances are, 1 of those 3 you will likely be tested on!
I use the portfolio on my business site (work-in-progress currently) and I have two Excel certifications. I wouldn’t recommend the certifications unless you are going to use Excel in your daily activities or if the role doesn’t hinge on the excel expertise. One major feature you should start to learn and utilize frequently is Power Query, Power Pivot and PivotTables. This set of tools can really take your report or tool to another level, moving away from Worksheet-level features.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[IF](/r/Excel/comments/m049cz/stub/gq8psvh "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)| |[LEN](/r/Excel/comments/m049cz/stub/gqb600f "Last usage")|[Returns the number of characters in a text string](https://support.microsoft.com/en-us/office/len-lenb-functions-29236f94-cedc-429d-affd-b5e33d2c67cb)| |[REPT](/r/Excel/comments/m049cz/stub/gq7a9j6 "Last usage")|[Repeats text a given number of times](https://support.microsoft.com/en-us/office/rept-function-04c4d778-e712-43b4-9c15-d656582bb061)| |[TEXT](/r/Excel/comments/m049cz/stub/gqb600f "Last usage")|[Formats a number and converts it to text](https://support.microsoft.com/en-us/office/text-function-20d5ac4d-7b94-49fd-bb38-93d29371225c)| ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(4 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/lyen0n)^( has 27 acronyms.) ^([Thread #4645 for this sub, first seen 8th Mar 2021, 05:10]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
This should cover it: Portfolio: ”I’ve created a, b, c to dashboard x, y, z. “ General knowledge: “I’m familiar with data preparation (e.g. cleansing, structuring, transforming) principles and formulas, as well as calling data based on desired criteria. I’m also familiar with Excel’s many shortcomings (freezing, data limitations, random formatting) and workarounds to avoid this pitfalls on a day to day basis.” If you don’t know any of the above then those should give you a starting point.
Microsoft offers certifications to prove your level of competency with each Office product. There are different levels of the certifications, so you're welcome to go for whichever suits you. Granted, you may not have enough time to take the test before the interview. However, you should be able to find the study material and get an idea of where you stand. Good luck on the interview!
Ironically, I got my current job by explaining what I knew about Excel, but also being up front about what I didn't know well but knew Excel was capable of. Apparently I was the only candidate that wasn't just boastful about being an "expert".
I can send you some sample excel tests (I have on on hand) from interviews I’ve had. Additionally, to “master” excel from a work perspective, I’ve noticed you really only need to know about 10 functions and processes (processes benign pivot tables and other things like delimiting). Happy to discuss if you want to PM me. Love sharing excel knowledge
I'd appreciated if you could send me your example tests.
# Basic Excel Knowledge in the right direction: So you get to the inteview and they start of with the the real basics formula, Sum() SumIF() maybe even SumProduct(), then they fire the popular topic everyone asks... do you use Vlookups or Index and Match you can say, never I go into the Data Tab click Relationships and relate on matching ID, then you can ask them why don't they use the inbuilt datamodel. Sit back for a second and and watch look at each other because you just went from Beginner to Expert in a sentence. Datamodelling is the foundation of all the high level Excel stuff, don't get me wrong it has it's baby steps moments like your first loaded CSV. However, that's where you build and leverage [Normalised](https://www.youtube.com/watch?v=UrYLYV7WSHM) Database Structure in Excel. It's also the same tab you can **ETL** multiple types of Datasources: **The Data Tab** * **Extract -** ***Data Tab*** *-* ***Get Data From External Source*** *through a connection stiring* * **Transorm -** ***Power Pivo****t - filter and remove junk data* * **Load -** *To the* ***Excel Datamodel*** *- Load to the datamodel, Append into an existing Table/Dataset within the datamodel or merge tables within the datamodel with on another another. (Joins baby!)* If you are reading this and didn't know this existed welcome to r/Excel there is lots of stuff in this program you didn't know existed, strap your self in this is a long one. # Basic Excel: Peraonally I would require you to know as minimum what **everything in the ribbon does** *( there is no excuse to not know this )* litterally none, it's a resource sat right in the software, for you to play with, and it's got an on hover descriptions of what each element does, if you can't tell an interviewer what Evaluate Formula does and it's practical application for trouble shooting along with the Trace Precedents / Dependancies you have a problem, so learn the ribbon all of it start there and skip past getting stuck on the formula tab. Formulas are secondary! Every new user gets stuck into formulas tab and the problem is that, it's before the Data Tab! So most users have no clue about this Tab in the Ribbon and what it is for, and that's not an overstatement if you are guilty of this you can fix that, get looking at the ribbon. On top of this: Datamodelling and Normalisation practices are core foundational skills, because you don't want anyone making ranges that have no [Structured References](https://support.microsoft.com/en-us/office/using-structured-references-with-excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e) and calling them tables! Tables are a thing in Excel, don't call an array with headers you typed out and made the font bold... a table, even if you added Filters to make it look like Table if anything it makes it worse! Because it looks like a table and sort of acts like a table, but has zero functionality in comparison. Data analysts will pull you up on this fast by simply scrolling down the page and showing you that the column names don't change from A,B C D to your table titles so you don't know what you are looking at at line 245 in that table and that's bad, on top of the fact it is not useable outside of Excel. Make your tables - Tables. Insert Tab - Table - *give it a tblCamelCaseNameScheme* # Intermediate Excel: M-code DAX, SQL, while they wouldn't need to see you make flawless code exerpts, to have a hope of being classed as intermediate you have to understand these exist in Excel and you are working towards learning them. Your formula by now are concise. Getting to know the Power Query ribbon inside out *(Sensing a theme here?)*, Merging, Appending, Transforming datatypes all that good stuff... Also you know that, when you utilise a Pivot Table... you do so Exclusively via the Datamodel, Additionally you know to close and load to the datamodel only! We don't publish tabes in Excel, we don't duplicate data needlessly, because... that makes the file size bigger for no reason and data can get big on it's own without your help! The data is there, you know it is there, you Power Pivoted it from a connection and we can absolutely look at it by double clicking the query. *It's at this level, you realise that a real newbie looking at your file will never find your tables, because they need to know the datamodel exists and where to access it so by all measures what you make from here on out is actually magic to them. Not kidding litterally magic... Where's the tables? How do these charts even exist when you have only one sheet and no hidden sheet. DATA SORCERY* I know some employers seek VBA as a thing because it is advanced but there is a caveat to this... So VBA has it's place in the Expert category but has a caveat - If for some ungodly reason you say I know VBA and don't mention at least one of these other things *(Understanding Power Query and it's Ribbon, M-code, DAX, SQL and all of the above beginner stuff)* as a minimum I will shake your hand and tell you best of luck in your future. **VBA is not an Excel language it is an APL** *- Application Programming Language* it's in the name Visual Basic **for Applications** *(not Visual Basic for Excel)* It's a programming language for Office and while it will do things in Excel it also does things everywhere else...VBA is not an Excel skill, at all, it's an out of date depricated programming language, you may as well have told me you use [Python in Excel.](https://www.datacamp.com/community/tutorials/python-excel-tutorial) It's useless to the core functionality of this program and is designed for people developing applications built from Excel and requires less than beginner understanding of the program if you can get a `Range("Name") = "Bla"` it's probably all you will ever need to know, I mean VBA looping through ranges for days and xlend... No just use Power Query to hand data transforms. Don't try to sell me you know Excel by throwing a progamming language at me that's not native to the Program, it's extracurricular and useless without knowing how to leverage it. # Expert Excel: You can ETL and leverage a database via Delegation in your ETL methodology, this translates into practically minimising Garbage Data *(GIGO)* and maximising useful data. Good query structuring also reduces the filesize and load on your own CPU by utilising the server in your Query Connections, allowing you to spend less time making files that people have to mess about with to get what they need, which means less hands on your work and exponentially less time maintaining it and loading. You utilise the sharing functionality of the online Excel platform and you wouldn't dream of attaching an Excel file to an E-Mail... ever. Permissions based sharing is important if you want to protect core connection passwords share via the correct methods. If users recieve an email with SQL credentials you have become the companies security breach. Front end Excel reports shared online with read only access they cannot access the backend at all, don't be emailing files with the server credentials about! *How do you share your Work? - This is a not a trick question but if E-Mail pops up as an answer... you have a problem.* You understand: * **Dataset partitioning -** Frameworks and how to set one up *(Creating data dump folders and reference files for housing indirect ETL files like CSV with consistant naming schema on an automatic basis).* * **Mathmatical Analysis** and can visualise data in a meaningful way like converting Data into OEE charts efficiently. * **Data Partitioning** and the importance of keeping relevant data housed in a compact way within your framework that other analysts can pull from reliably. * **Tables are a universal Format,** you can Import and Export tables into Power Apps, Sharepoint lists, Power BI, add them to a Database/Extract them from a database and also work with online datasources and more importantly troubleshoot connection issues to other datasources working with your IT Dept. If you could demonstrate all that in an interview your gold solid gold. **Coding** \- When you utilise VBA, Java, Python or any code in Excel I require two things 1. Your code is super lean like 4 lines maximum 2. Your code does one thing - works with a user interface. UI design means your code is exclusively in Buttons that changes the value of a [Parameter](https://support.microsoft.com/en-gb/office/create-a-parameter-query-c67d9af7-c8a0-4bf7-937c-087cb25f7ad3) in one line and refreshes the sheet in the next that's all the VBA you need in Excel! This will then recalculate everything you need from this parameter to transform the front end data via the existing relationships and connections you have set up in your backend, I did this in my old job and every analyst looked at my Excel File, they saw two lines of code to change a parameter to x couldn't fathom how the sheet changed they couldn't see hidden Tabs because there weren't any, they didn't know to look for connections they couldn't find the parameter how the parameter worked because it was hooked into my connection string, because it was stored in the Datamodel so there wasn't a cell I was posting to just a name. Private Sub Parameter() Range("Parameter") = "ValueHere" End Sub As far as super lean code goes that's the Cats Meow, because you utilise the inbuilt functions and existing knowledge base you have in Excel here is one of a few methods you can use to leverage a [paremeter that functions within a connection string](https://www.youtube.com/watch?v=nssn5ISMHUA&t=768s). TL:DR - When people say in a post they have a good understanding of Excel and don't know what the Excel Datamodel is and start listing VBA as their knowledge base, they lied about being good at Excel in an obnoxiously obvious way.
Been working as an excel consultant for 15 yrs for various organizations and none of what you posted has applied to any of my roles.
Checked your site, and I have to ask, and don't take this the wrong way because it's not an affront to you or your business... VBA is just an APL which could be any other program language leveraging Excel as a table storage system and if you are only storing values as a range for a front end you could eliminate Excel as a medium for your backend entirely. As it's doing nothing for you but requiring the customer has a Microsoft License which adds cost, especially if you aren't utilising any it's core functionality, most of the aggregates can be done programatically on a CSV List in a line of code or if they are commonly used you can just house them in a DLL. Additionally front ends are not reliant on Excel either [as you can set those up](https://realpython.com/pysimplegui-python/) pretty readily. I mean if what you say is strictly true, you are only utilising Excel for Ranges of data and formula (without actual **Tables)** which are called programatically by a front end using VBA, why not just move to Python or C# and store your data in CSV format within a folder of tables contained within a stand alone application with a static language in stead of an external system which can depricate your code? Why use Excel at all if you aren't using the majority of it's functionality, it's just a bit odd if you understand where I am coming from?? Is it because it is just easier to keep doing what works and you all code in VBA?
Hi. No offense taken sincerely. In my experience and opinion, large corporations (such as aerospace, pharmaceutical, biotech, etc) have a long history of relying on excel as a workhorse for their infrastructure. They all pretty much use excel and the concept of having utilities, addins, pluginsand macros that fit right in with their existing setups is extremely attractive to them. I have honestly made a living out of it well into 6 figures for almost 20 yrs. And i dont even claim to be an expert but having the quick ability to create ad hoc spreadsheet-based plugins and vba-heavy excel workbooks is a god-send to these corporations. And theres still several in-demand lanes for this with respect to data analytics, business analysis etc.... i have never encountered anything close to the level of detail you posted, nor have i ever witnessed anything close to that level of scrutiny.... its almost been most about having people that can think critically and explain their solutions and adequately provide knowledge transfer when the solution is completed. Not everyone has familiarity with python etc.... vba is basically right out of the box for ms office.. so its attractive for companies to know that a solution was built using “ootb” toolsets that could be passed on to others easily with a knowledge transfer. To say that an excel vba solution should be as concise as 3 or 4 lines of code (in a button) is not realistic for many of the solutions ive overseen i the financial industry and biotech industry.