Highlight Weekends in Excel with Conditional Formatting


EasyExcel_37_1_Highlight weekends with conditional formatting

The picture to the right shows a table with some sales figures for July. There’s nothing wrong with the table as it is, but I find it very hard to read and make sense of it. There are just a lot of numbers and dates, and you can’t even distinguish between weekdays and weekends. If we could highlight the weekends (or weekdays) it would be a lot easier to read these numbers. In this example I want to highlight the Saturdays and Sundays. Here’s how we’ll do it:

READ MORE

How VLOOKUP can get you in trouble and how to solve it

An Extra Column Means Trouble

If you want to find a value in a table in Excel, a simple VLOOKUP function is usually a good and easy way to do it. But you have to be careful – if you insert a new column in your table, the function might not work anymore, and we have to find another approach. Here’s why:

READ MORE

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

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

Convert numbers to Thai text with the BAHTTEXT function (?)

Everybody knows that you can do amazing things with Excel, but who would imagine a function like BAHTTEXT? According to Microsoft, this function “Converts a number to Thai text and adds a suffix of Baht.” There are no functions that convert numbers to text in any other language, so why Microsoft chose to develop this function remains a mystery. I’m guessing that one of the developers did it to impress someone who speaks Thai…

Sadly, I don’t speak a word of Thai, so in order to determine whether or not the BAHTTEXT function is good enough to impress anyone, I checked the results in Google Translate. This is what I got:

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