What does the error message #DIV/0 mean? Take a look at this table. It’s a sales report, with the total sales in column B, the number of hours in column C, and finally Sales per hour in column D.
Sales per hour is the total sales divided by the number of hours, and if the Hours field is empty, we are basically telling Excel to divide something by zero. Obviously, that’s impossible, and Excel returns an error message: #DIV/0.
The error message is there for a very good reason, but if we want to show this report to someone else, it would definitely look a lot better if it wasn’t there. Let’s see what we can do about 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.
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.
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.