Your Pivot Table will now display, as can be seen below, which is not a true reflection of how many customers have been invoiced, but a count of how many transactions took place. JosephP, not if the pivot table is filtered to exclude the rows. ... That way it reads blanks and 0s as non-unique values in the list, and they are ignored. 28 Responses to “Extract a unique distinct list and ignore blanks” ... Pivot tables lets you create unique values and sum corresponding values. Fields. I’m talking about PivotTable cells containing the (blank) placeholder. Automatic count of distinct values in a pivot table; How to count unique values in Excel. This is set up with the page filter. Use this method if the blanks are in the values area of the pivot table. ... I’m trying to set up a pivot table with a distinct count, and then the percent of column total. If I was suppose to have 4 unique I get 5 (as there also are blanks) Does this makes sense Thanks Kristoffer To show a unique or distinct count in a pivot table, you must add data to the object model when the pivot table is created. However, on trying to use the new function, I've hit one problem in particular. My data, in a simple form could be described as : Item, Month, Value1, Value2. Put the District field in the Row area, and another copy of the District field in the Values area. It is really easy. I will then hide rows 1:2. Refresh your pivot and, waalaa. Count Distinct - counts blank as 1 I was jumping for joy when I read that Excel 2013 was finally going to include a Count Distinct for pivot tables. 4. We might as well shave our head with a shovel before manually counting values. Click the PivotTable Tools Analyze tab in the Ribbon. The problem is … And that brings us to 2 distinctly simple solutions: Using Power Pivot & Excel 2010; Using regular pivot tables in Excel 2013 The pivot table shows only the records where the "report" field equals 1. Hi I'm using the powerpivot add-in for my excel 2013, as I want to have the distinct count function in my pivot tables. Click Options in the PivotTable group. 5. I'm however experiencing that when I make a pivot table from my data table it counts the blank/empty cells also. ... As you can see in the screenshot above, the formula returns the total number of unique text values, excluding blank cells, numbers, logical values of TRUE and FALSE, and errors. I have a feeling that those cells are not empty :) To confirm this, select the blank cells and manually press "Delete" button. We can use Pivot tables, but it only gives half of what we want ie each y part, but not distinct count of x. For example, if the source data has the District name missing in some records, we can use a pivot table to count those records. In the example shown, the pivot table displays how many unique colors are sold in each state. To get a unique count of customers, click on the “count of CustomerNames” drop down and select “Value Field Settings” 6. Perhaps a simpler solution. Leave Find value blank and enter apostrophe [ ‘ ] as the replace value. E.g. By default, a Pivot Table will count all records in a data set. Select all cells in range containing blanks you want to ignore on pivot then do find replace. To set pivot table options for empty cells: Click in the pivot table. Hit Find Next to check that it’s finding blank cells, then once you’re comfortable hit replace all. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)… Excel ROW function Hide blanks in Excel PivotTables caused by empty cells in your source data. You can also right-click in the pivot table and select PivotTable Options from the drop-down menu. See attached file created in Excel 2003. The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. Excel doesn’t count empty cells when summarizing in a pivot table, so you might not get the result that you expected. There are 19 records. I use distinct count a LOT! Now refresh your Pivot.