Hide Future Dates in Excel with Conditional Formatting

EasyExcel_35_1_Hide future dates in Excel

This is a very useful trick if you have a report showing dates and budget figures, and you want to make it more readable by making the future dates less visible.

Take a look at the report to the right. Assuming today’s date is 7/10, we want the dates from 7/1 through 7/10 to be easy to read, while the dates in the future should be hidden or greyed out. The trick is to not only dim the dates, but also the other columns on the same rows. This is an easy trick that you can apply on any report you get your hands on! Here’s how to do it:

READ MORE

Excel: 2 + 2 = 5

Can 2 +2 be 5?

Yes, at least if you look at this example in Excel:

EasyExcel_34_1_2+2=5

Of course, Excel doesn’t make mistakes like that, so there must be an explanation. Let’s try to increase the number of decimals:

EasyExcel_34_2_2+2=5

As it turns out, the real calculation wasn’t 2 + 2; it just looked like that. It was actually 2,4 + 2,4 = 4,8, but when you decrease the number of shown decimals to none, Excel displays the nearest integer in the cell, and it looks like 2 + 2 = 5. The actual number behind doesn’t change.

More Excel oddities:



Are you using a non-English version of Excel? Click here for translations of the 100 most common functions.

Did you enjoy this article? Get free email updates! Enter your email address here:

Delivered by FeedBurner

Boolean logic in Excel: TRUE/FALSE instead of IF functions

Most of us use the IF function all the time, for example to test if one value is larger than another value. But many times there is an easier way to do it! Let’s use Boolean logic instead – it will save you a lot of time and struggle. Boolean logic, or Boolean algebra, is a kind of algebra where you’re looking for a TRUE or FALSE result.

Let’s look at an example. In this sales report, the sales reps get a bonus if they reach $ 15,000 in sales:

READ MORE

Remove Duplicates in Excel

How can you remove duplicates from a list in Excel?

Easy! Excel has a built-in feature for this. Just select one of the cells in the list and click on Remove Duplicates on the Data ribbon.

EasyExcel_31_1_Remove Duplicates in Excel

EasyExcel_31_2_Remove Duplicates in Excel

Voilà, the duplicates are gone:

For more advanced ways to handle duplicates (and triplicates, quadruplicates etc.), take a look at this article: How to Find Duplicates and Triplicates in Excel

More Easy Tricks:


Are you using a non-English version of Excel? Click here for translations of the 100 most common functions.

Did you enjoy this article? Get free email updates! Enter your email address here:

Delivered by FeedBurner

Use Excel to Count Words

In Word you can easily count the words simply by clicking “Word Count” from the Review ribbon. But what if you have your text in Excel? There is no built-in word count function in Excel, so we have to find another way.

In a previous post I showed how to count characters in a text string using the LEN function. We will use the same function, with a twist, to do a word count.

READ MORE

Use a wildcard in Excel to make SUMIFS and COUNTIFS more flexible

What is the extra S in the function name?

The old functions SUMIF and COUNTIF let you add or count a range of numbers based on one criterion. For example, you could count all the members who have paid the annual fee in the table below. With SUMIFS and COUNTIFS you can have multiple criteria, e.g. count all the members who have a Silver membership and who have paid the annual fee.

How to use a wildcard

In the example below I have used the COUNTIFS function to count the number of members who fulfill the two criteria. But if you also want to be able to apply only one criterion without changing the setup, you can use a wildcard. Here’s how I’ve done it:

READ MORE