blog banner
How To Pass Excel Test. Top Excel Questions Asked in Job Interview

How To Pass Excel Test. Top Excel Questions Asked in Job Interview


hi there this is Vadim Mikhailenko and in this quick video we’re going to look at the most typical excel interview questions We’re gonna start by looking at vlookup formula, then we we’ll look at the differences between desktop version of Excel and Excel for Office 365 we’ll also look at the different functions and formulas what are they how they used what is the sequence of evaluating formulas in Excel and a lot more so stay here with me on this channel [Music] let’s start by looking at vlookup formula because this is one of the most frequently asked Excel interview questions but snart was the quick example in this example we need to find order amount based on the known order ad and we have a table of existing orders to do this old type equals vlookup and then provide four values first value is the known value that we’re searching for in our case this is cell a7 was the order ID number 839 293 second value is the range in which we’ll be searching third value is the column index for the column that we need and in our case this is column D which contains amount but numerically this is column number four because order ID is column number one name is column number two email is three and an amount is column number four and then we’ll provide the last value false for exact match once we finalize the formula you see that the value found is four hundred twenty three dollars and 38 cents and as you can see this is the amount for the order eight three nine two nine three the view cut function in Excel performs a vertical lookup by searching for a value in the first column of the table and returning the value in the same row based on the index number position for example if your table contains list of orders you can find customer name email amount ship date or other available attributes of the order based on a known order ID for example if your ID is eight three nine two eight seven you can run look up to find customer name which would be Ivana Weber or email which would be Ivana Weber at trade entered calm or find the order amount in amount of four hundred and six dollars and ninety two cents which was shipped on September 7th 2018 you [Music] to add vlookup function you need to navigate to formulas tab and then click on an insert function button then you type vlookup and find the formula there are 4 values we need to provide lookup value is the known value we’ll be searching for table array is the area which will be searched lookup index number is the index of the column which will be returned that contains the value we’re looking for and then range specifies the way we will be searching false indicates exact match and true would be not exact match we will be using false let’s add vlookup formula using this functions arguments dialog box for the lookup value will provide a 7 for table array we’ll select the range between a 2 and a 51 for the column index number since we’re looking for amount we’ll select 4 and for range lookup will select false for exact match and you can see that we’ve selected order 8 3 9 2 9 3 in the amount for this order is 423 dollars and 38 cents the hookup formula in Microsoft Excel helps you find specific value based on a one non well for example we’re looking at the list of orders in this table we have already named email amount and should date in our business case is we need to find customer email based on a specific order number or we need to find order amount based on a specific order number using vlookup formula we can supply order ID and find any of the remaining attributes here listed here in this table this is the syntax of vlookup formula you need to supply known value in our case it would be order ID then you need to supply range for searching and you need to identify that they need to specify the column we’re searching for because different column represents different value and then you need to define the match type how exactly are you gonna match the value now as we know the syntax let’s use vlookup formula to find email address of the customer for the order eight three nine two eight four to do that I’m gonna type vlookup then the order ID 839 to 84 then I need to identify the range of the cells where I will be searching for the information and the ranges between the cells of a two to e twenty-three then I would need to specify the email column in this range and the email column is number three because the first one is order ID second one is the name and the third one is the email and then I need to specify the type of net true is an approximate match and false is the exact match so I’m gonna use false for exact match if you’re interested to learn more about Microsoft Excel I recommend training course on udemy just navigate to how to analyze data that met slash Excel to learn more I’ve carefully selected this course and you get substantial discount from the retail price just to let you know this isn’t a few a blink and they get small commission if you make the order don’t make purchases though if you don’t need it but thank you for your consideration and support of this channel based on the information I provided system found email for Katherine Morfitt let’s look at how vlookup can help us solve specific business problems for example how can you find customer name or email based on specific order number or how can you find order amount based on order number or how can you find shit babe first let’s look at name and email below cap can only return one value so we would need to do name first and then email as I started typing please pay attention that instead of putting specific order number I put in the cell reference which is a six in this case and we’ll see how this will impact copying and pasting of vlookup formula in solving later business problems we supply order number by putting the reference to a six cell then we specified the range and then we specify the column ID for where the information should be taken from and the last step is we specify how we’re gonna match the data we picked an exact match by supplying the value falls in the name of a Leah Reeve has been returned by the vlookup which is the exact correct value question number five what is the difference between Microsoft Excel for the desktop and Excel office 2016 for desktop is the newest version of office productivity suite which includes Excel Word PowerPoint and other office applications which is which are typically accessed via the desktop office 365 on the other hand is the cloud-based subscription to a suit of programs including office 2016 of his files can be accessed in the browser and on mobile devices which allows businesses and individuals for more flexibility you can also download and install office 2016 including Excel 2016 on your desktop as part of office 365 subscription full desktop version of office which includes Word PowerPoint Excel OneNote and publisher is included with license for up to five computers per user another big difference isn’t pricing office 2016 is the standalone version was the one-time cost per user so you just make your purchase one you pay a large amount of money up front but you don’t have to pay monthly fee after office 365 on the other hand is the subscription plan meaning that you won’t pay anything upfront but you pay a set of monthly fees per user per month and if you don’t want to spend a lot of money upfront paying monthly might be a better option for you because you can cancel at any time also question number four what are the most useful functions one of the most useful excel functions is some which allows you to quickly sum a range of cells by using autosum button it automatically enters the some functions in the selected cells the sum function tolls one or more numbers in the range of cells other useful functions are average Max and min average calculates average value from the range of cells max calculates the maximum value and min calculates the minimum value in the range of cells Excel has a lot of functions available to you and based on the work that you’re doing you will be selecting and favoring some of the specific functions based on the job you also will be able to write your own functions and calculate some specific values based on the work that you need to accomplish number three what is the function in Microsoft Excel functions are pretty fine formulas that allow you to do calculations and they are already available in Excel formulas and functions in Excel are useful to perform various mathematical statistical and logical operations you can type in formula though you have to be sure it’s exactly right or you can use excels preset formulas called functions if you type in formula you must start with an equal sign so Excel knows that the data in the cell is a formula after the equal sign what comes next depends on what you’re trying to do Excel offers you some suggestions and shows syntax for a given function Excel also corrects you and provides suggestions if you’ve made a mistake while typing question number two what is the sequence of operations being executed well formalists are executed in Microsoft Excel Excel uses specific order when it evaluates operations in formulas first it goes and looks at the parentheses then it looks at exponents multiplication division addition and then subtraction you might recognize the term PEMDAS PE m da s and that stands exactly for what we just described when evaluating formula Excel always processes operations in this order if you find yourself receiving an unexpected result from the mathematical operation double check to make sure that parents is’s are used properly to achieve the results that you want in question number one what is the most important data types used in Microsoft Excel there are four important data types in Microsoft Excel and they are numbers dates percentages and strings numbers are one of the most frequently used data types in Excel they can be formatted with specific number of decimal places and they can appear with or without commas separating the thousand digits numbers can also be added subtracted divided multiplied or included in formulas and functions that accept numerical inputs dates represent specific date and time dates are sorted differently than numbers because they represent different values percentages are very special numbers that multiply given number by 100 and add percentage sign in then in strengths represent freeform text and they’re stored as text could be letters and numbers and punctuation signs and also could be stored in your cells Excel needs to be able to differentiate between those key data types because it needs to know how to sort the data and how to do calculations and they are all done differently for those different data types have you enjoyed this episode make sure to subscribe so you will not miss the next one and tell your friends about it you can find links and downloads in the description section of this video there are a lot of helpful links and a lot of other helpful information you will benefit from make sure to check out my other relevant videos and subscribe to my youtube channel we have a lot of great stuff planned in the pipeline and I don’t want you to miss any of it and if you’d like to get notified about all the new stuff that are coming out make sure to subscribe to my email list as well all links are here in the screen make sure to click to stay in touch thanks again for watching

  • I passed my Excel test at 87% from not using Excel and almost 20 years with your videos! I also got the job and I get my badge this week and go to orientation this week and I'm very excited thank you so much! The part that helped me the most is when I had to refer back to your videos to find information and you stamps the time and the title would that I could click on to find what I needed to find in reference to my test thank you so so much

Leave a Reply

Your email address will not be published. Required fields are marked *