🟢 LEARN EXCEL WITH ME IN THE COMPLETE EXCEL COURSE WITH CERTIFICATE: http://excelentejoao.com.br/sejaexcel... 🔵 DOWNLOADABLE SPREADSHEET: https://excelentejoao.com.br/wp-conte... In this Excel video lesson, we will learn how to solve an Excel test for a job interview, an Excel test that was included in a job interview. If you are studying how to pass a job interview, or if you are interested in taking a basic Excel course for job interviews, this video can help you a lot. It presents the most important Excel formulas for the job market, functions to save time in your daily life and how to solve an Excel test in a job interview. With practical examples from everyday life, we will solve several Excel challenges that were included in an Excel test for a job interview. From a report extracted from a database of a company system, we will need to adjust the information in the table to be able to solve the challenges. In this first step, which is to adjust our Excel database into an organized table, we will need to use several important features in Excel, such as the Flash Fill tool so that we can automate tasks and processes, and thus save time with Excel. You can also use the Excel shortcut Ctrl E to access Flash Fill. One of the adjustments we will need to make is to break a column into two, separate the information from one column into two columns, take a text from this column and divide it into two columns. After the necessary adjustments to the table formatting, increase the column size, insert new columns, etc., we can solve the problems. In the first challenge, we need to add up the total salary of all employees in the company; for this we can use the Sum function in Excel. In the second challenge, we need to add up the salary of all employees by category or position. This means that we cannot use the SUM function, as it will add up all the values ​​in the selected range. Instead, we need to use a sum function with criteria. In this case, we can use the SUMIF function, which adds up the values ​​that meet a certain criterion. In the third challenge, we will need to calculate the averages of a range if the criterion is met. The function we can use is the AVERAGEIF function. The AVERAGEIF function is very similar to the SUMIF function in Excel. In this way, we can calculate the average of the selected range if a criterion is met. In this case, the criterion we will use is the name of the store. In other words, we will need to calculate the average sales per store. The fourth challenge is to check which is the highest value in our salary range, and we also need to check which is the lowest salary of all. To do this, we can use the HIGHEST and LOWEST functions in Excel, respectively. In addition, after finding the highest and lowest salaries, we need to automatically return the name of the employee who has the highest salary and the name of the employee who has the lowest salary. To do this, we can use a search function, such as VLOOKUP, HLOOKUP or INDEX and MATCH. These functions are nested in Excel, which means we can combine them to create a more complex function. But in this last challenge, we have a problem. The VLOOKUP function doesn't work because our criteria column is to the right of the result return column. Therefore, we can't use the VLOOKUP function to solve this problem. We'll need to use another search function, such as HLOOKUP, or even XLOOKUP, but if your Excel doesn't have the XLOOKUP function in Excel, you can use the INDEX and MATCH functions in Excel. 2:06 - Challenge 000 6:38 - Challenge 001 7:56 - Challenge 002 12:16 - Challenge 003 15:41 - Challenge 004 #ExcelenteJoão #Excel #Dashboard