Category: Search

With some Excel functions you can easily convert some of your worksheet as database

Filter your data in Excel

Filter in action

Filter is one of the most useful tool in Excel to visualize your data. In just one click on the arrow on the header and you can hide or un-hide data

Permanent link to this article: https://www.excel-exercise.com/filter-your-data-in-excel/

Anonymise your data

Discover how you can transform your worksheet to anonymise your data in the case you have sensitive data. 5 steps are needed

Permanent link to this article: https://www.excel-exercise.com/anonymise-your-data/

Compare 2 Columns

Missing value are in red automatically

You can easily compare the contain of 2 columns in Excel with the function VLOOKUP. You can also change the color for the missing values

Permanent link to this article: https://www.excel-exercise.com/compare-2-columns/

Calculate between two values in a range

In Excel, you can easily return a result between two values. Most of the people think that they have to do a lot of IF nested. But you can do the same job with the function VLOOKUP What is the problem? Let’s say you have this workbook where you want to calculate the commission for …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/calculate-between-two-values/

Create an Offset Vlookup

In the case you have a table that contains the same ID for many – or empty cells. It is strongly recommended to reorder your data To perform this modification, we will use 3 functions , INDEX , MATCH and OFFSET. Problem to solve We have a table (column A:D ) with the list of sales …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/create-an-offset-vlookup/

Vlookup to the left in Excel

Problem of the VLOOKUP function The normal behavior of the VLOOKUP function is to return data from a table. But you can only return data on the right of the column containing the ID. However, in some cases, you can not change the order of the columns. Like for instance in this document where the …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/vlookup-to-the-left-in-excel/

Function MATCH – Dynamic VLOOKUP

Presentation of the MATCH function The MATCH function returns the position of a value in a range of cells This function is very useful to create dynamic research with the function VLOOKUP or INDEX. MATCH function needs 3 parameters. The value to find Range of the cells where is the value (1 row or 1 …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/function-match-dynamic-vlookup/

Excel could be a database with VLOOKUP

Purpose of the function VLOOKUP Often, Excel files contain data like customers, prospects or products. The VLOOKUP function returns the value of one cell of your reference table. To build a VLOOKUP, you needs 4 parameters First parameter of the function contains the value to search. Second parameter is the range of cells where of …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/use-excel-like-database-vlookup/