Sometimes Excel doesn’t seem to add up the hours correctly. In this table I have made a table of an employee’s working hours and used the SUM function to add them together. Look what happens:
The sum is 13:45 instead of the expected 37:45. That’s 24 hours less than we wanted!
Here’s why: The result is actually 1 day, 13 hours and 45 minutes, but when you use the SUM function, Excel assumes that you want to keep the same format as above, which only shows hours and minutes.
How to fix it:
If you delete the contents of a cell in Excel, there is a risk that other parts of your workbook are affected by it. The easy way to prevent that from happening is to check if any other cells depend on the cell you want to delete. Here’s how to do it:
Select the cell, go to the Formulas ribbon and click on Trace Dependents. The arrows show the dependent cells.
Other Easy Tricks:
Are you using a non-English version of Excel? Click here for translations of the 100 most common functions.
If you want to highlight a cell in Excel based on its value, it’s pretty straight forward: Just choose Conditional Formatting from the Home ribbon. But what if you want to highlight the entire row based on the value in just one of the cells? We’ll use Conditional Formatting here too, but with a slightly different approach than we’re used to.
This is what we want: Choose one of the names in the table below and highlight the entire row for each occurence of that name.
An Excel table with a lot of numbers in it can be difficult to read. Often, a lot of the numbers are zeros, and it is sometimes a good idea to hide them in order to make the important numbers more visible. Of course, you could write IF-formulas that return nothing if the result is zero, but usually the way I am going to show here is better and easier.
This is what my table looks like:
In a previous post we learned how to make an Excel drop-down menu in 3 minutes. In this post I will show a very easy way to make a drop-down list in Excel that changes depending on what you have chosen in another cell. It’s easy, and it only takes five minutes!
Step 1: Enter the data
It is absolutely critical that the data table is set up correctly: The main category (the countries in this example) should be in one column, and the sub-categories (the cities) in the other columns, with the countries repeated as headings.