Then we will declare our Pivot table and refer it as well wit the worksheet. If you look at the broken lines moving around the block of data, you will see that they are enclosing a blank row or column . Hello! A pivot table is an easy way to count blank values in a data set. Step 3: Once your pivot table is created, highlight some of the cells you want to group. A handy feature of Microsoft Excel is the Slicer which allows the user to quickly filter PivotTables. Furthermore, you can group the store sizes into bins. Right-click on it and select group. Thus you have combination of dates, blanks and texts ("" in your case). Then, select the following options in the dialog box as shown in the picture below. the double quotes around anything tell excel to read it as text. ; In the box that opens, select Format only cells that contain. Right-click on any of the cells of the "Date" column and choose the "Group" option. #2) On the context menu, click Group. As a note, you can multiple items by holding the, In a similar fashion, you can group other. So, just follow along. Welcome to my Profile. We can press the, All cells that contain text or error values will be selected. It will select the complete row range values. Grouping in a pivot does not work if there are text values or blanks in the date column in the source data. Download this practice book to exercise the task while you are reading this article. I have to check if I have it and how it works to put real blanks. #3) Grouping dialog box appears, in this example, the least number is 25, so by default the Starting number is entered as 25, and you can change if necessary. Just enter the start value (. We can then apply a different fill color to highlight these cells so that we can fix them in the next step. The problem is that whatever method I use to convert the blank cells to numbers they are not working, if I use the ISNUMBER() it keeps telling me FALSE. In the Grouping dialogue box, select Quarters as well as Years. Register To Reply. If you group the field again after changing the formatting, the formatting for the items in the Days . We will use the Order Date and Ship Date column of this Excel to solve the group dates problem in the pivot table.