Category: Expert

How to Highlight Birthday Automatically

In this article, you will see how you can change the color when a Birthday is coming up in few days. No macro has been used. Step 1: Add the current date First of all, add the current date with the function TODAY() in a new column. =TODAY() Step 2: Gap for the months In …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/how-to-highlight-birthday-automatically/

Wildcard with COUNTIFS or SUMIFS

Greater than / Less than You can create complex conditions with the COUNTIFS function. You can enhance your criteria by adding a logical operator. In other words, you can select all the rows where one criterion is greater than a value. For example, if we want to determine the number of men with incomes greater …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/wildcard-with-countifs-or-sumifs/

Pivot Table – Presentation

A Pivot Table is a powerful tool which calculates or aggregates data very easily. For many people, a pivot table is a complex tool for advanced users. But not at all. It’s really simple and all the calculations are made by the tool itself. You just have to drag an drop your data. 😎😃😍 Before creating …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/pivot-table-presentation/

Gantt Chart

What is a Gantt chart? A Gantt chart helps you to visualize your project schedule containing different tasks and their deadlines. Your data To create a Gantt chart, you must have a table with: Task names Start dates Effort (number of days needed to carry out each task) Step 1: Return the End Date (based …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/gantt-chart/

Dynamic SUM in Excel

Creating a SUM is very easy to do in Excel. But when you want to add only specific cells and not all of them, the formula is not as easy. But it’s possible! Let’s see how to create a dynamic SUM. Insert your data into a Table But before we explain how to create the …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/dynamic-sum-in-excel/

Anonymise your data

If you are working on a workbook containing confidential data, you need to anonymise your data if you are collaborating with other people. The technique is not really complex but you have to respect the following steps. The initial document Imagine you are a journalist and you receive the following file in your mailbox (it’s all …

Continue reading

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

Keep your last updated data

Keep the last updated row Let’s start from a file where we store customers informations. Some customers are many times in your file when they change their phone number or address How do you keep only the last updated row? Remove Duplicates not applicable With a such file, you can’t use the tool Remove Duplicate. …

Continue reading

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

Function XOR

Function XOR is a logical function integrated with Excel. What is the XOR function? In logic, there are two ways to specify an OR context The inclusive OR. One or more of its parameters are true and the result of the test is TRUE, it is the function OR The exclusive OR. One and only one parameter is true and …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/function-xor/

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/

Using conditional formatting to highlight dates

Date functions in Excel make it is possible to perform date calculations, like addition or subtraction, resulting in automated or semi-automated worksheets. The NOW function, which calculates values based on the current date and time, is a great example of this. Taking this functionality a step further, when you mix date functions with conditional formatting, …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/using-conditional-formatting-highlight-dates/