Sometimes some of the data in a table have the wrong format. Take a look at this table, where some of the records are in hh:mm format, and others in hh:mm:ss format.
Here is an easy way to fix it:
How to Copy Format in Excel
Reply
Category Archives: Easy Tricks
Highlight Weekends in Excel 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 MOREHow 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:
Excel: 2 + 2 = 5
Can 2 +2 be 5?
Yes, at least if you look at this example in Excel:
Of course, Excel doesn’t make mistakes like that, so there must be an explanation. Let’s try to increase the number of decimals:
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:
- Convert numbers to Thai text with the BAHTTEXT function
- Use Excel to convert Arabic Numbers to Roman and back
Are you using a non-English version of Excel? Click here for translations of the 100 most common functions.
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