FILE TO DOWNLOAD: https://docs.google.com/spreadsheets/d/1GYTeYoBJtsyXhJX-DOPYeW_WowNOay2b

In this Excel video tutorial, we are going to learn how to solve an Excel test for a job interview, an Excel test that fell 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 features the most important Excel formulas for the job market, functions to save time in everyday life and how to solve an Excel test in a job interview.

With practical day-to-day examples, we will solve several Excel challenges that fell on an Excel test for a job interview.

From a report extracted from a database of a company's system, we will need to adjust the information in the table in order 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 Lightning 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'll need to make is to break a column in two, separate the information from one column into two columns, take a text from this column and split it into two columns.

After the necessary adjustments of table formatting, increasing the column size, inserting 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, so 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 title. 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 SUM IF function, which adds the values that meet a certain criterion.

In the third challenge, we will need to take the averages of an interval if the criteria is met. The function we can use is the AVERAGE IF function. The AVERAGEIF function is pretty similar to the SUM IF function in Excel. In this way, we can take the average of the selected range if a criterion is met. In this case, the criterion that we will use is the name of the store. That is, we will need to average sales per store.

The fourth challenge is to check which is the highest value of our salary range, and we also need to check which is the lowest salary of all. To do this, we can use the functions MAX in Excel and MIN in Excel respectively.

Also, after finding the highest salary and the lowest salary, 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. For this, we can use some 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 cannot use the VLOOKUP function to solve this problem. We will need to use another search function, such as HLOOK, or even XLOOKUP, but if your Excel does not have the PROCX function in Excel, you can use the INDEX and MATCH function in Excel.

2:06 Challenge 000
6:38 Challenge 001
7:56 Challenge 002
12:16 Challenge 003
15:41 Challenge 004

#JopaExcel #Dashboard #Excel