Troubleshooting and Errors

1. Troubleshooting Errors

Let's talk about a few common issues you might come across when using pivot tables.

2. Finding missing data pt 1

First is the problem of missing data. Here is an example of missing data. In this instance, Toyota does not have an Industry listed. You'll have to consider your original data, and decide whether or not it's ok to have certain values missing. For instance, maybe Toyota doesn't fit solely within a certain industry and you would prefer to leave that cell blank. That is a judgment call for you to make, however, the first step is identifying where a missing value exists in the first place. We can do that by using the COUNTA formula.

3. Using COUNTA to identify missing data

The COUNTA formula will count how many non-blank cells exist within a range. You can write this formula as equals COUNTA, open parentheses, then select the range and close parentheses. Let's use this formula to see if there is any missing data within our dataset. We'll type the formula into a blank row below our data, and select the range of cells directly above it. Then we will copy and paste this formula across for all of the columns. Once we do, we can see that some of the columns have a count of 31, and some have a count of 30. It looks like the two columns with a count of 30 are missing some data.

4. Finding empty cells

The quickest way to find the blank cell within a column is to select the header of that column, then hold the control key (or the command key on a mac) and hit the down arrow. This will take you to the first break in the data. Then you can decide whether to fill in the data.

5. Fixing incorrect labels

A second common issue is having field labels that don't match exactly. This can be due to a misspelling, or a lack of controls when classifying the data. In this example, we can see that our data contains two similar industries, Petroleum Refining and Petroleum Refiner. If we determine that these should be the same category, then we'll need to fix this in the original dataset. We can go back to the dataset and use the Find function, by holding Control and hitting F. Then we can search for "refiner" and change it to the correct classification. On our pivot table, the extra category is now gone.

6. Numbers formatted as text

One final issue is caused by having improper formatting in your dataset. If you look at the data in this example, you can see that we have values entered in the Revenue column, but our pivot table shows zeroes. This is because the numbers are formatted as Text within our dataset. The pivot table can't add the text values together. The solution is to format the cells as Numbers so that the pivot table can perform calculations on it. One quick way to do this is to select all of the cells, click on Format on the ribbon, and choose Number. Now when we check the pivot table, the values are added correctly.

7. Let's practice!

Now it's your turn.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.