PIVOT TABLE 0 VALUES

[GPRADO]

Hello,

I created a pivot table. The data has 2 columns, wk and villa number. The pivot shows me the week number and the villa number for that particular week. Now, I have certain weeks that do not have any data. So my pivot skips (doesnt' show) that week. Is there a way for the pivot table to show me that week and show a blank or a 0 value if the week doesnt have a villa number?

thanks,
George

 

[Mark W.]

You can specify the "Show data items with no data" for the 'wk' field, but the PivotTable must be pre-conditioned with all of the possible values for the 'wk' field. Assuming that your weeks are in the range 1-52, create a list of these values using the 'wk' fieldname. Reference this dataset from your PivotTable, then redirect your PivotTable's data range back to your original set of data. Now, your PivotTable "knows" all of your 'wk' values.

 

[GPRADO]

Mark,

Thanks for your response. I don't quite follow you on the reference part. Is there a way I can send you or post the file so you can show me?

thanks,
George

 

[Mark W.]

It's best to keep our exchange on this forum so others my benefit. By "reference" I mean data range that is specified at Step 2 of 3 in the PivotTable wizard.

Did I guess your 'wk' values correctly?

 

[GPRADO]

ADVERTISEMENT

yes you did.. Ok I do have the range for the weeks selected. Also have on that range 3 resorts. Not all of the resorts have a villa assigned to a week. Could this be the problem?

 

[Mark W.]

Yes, temporarily create a separate, single column range whose 1st cell contains the label, 'wk', and the remaining 52 cells contain the values 1 thru 52. Have your PivotTable reference this range (Step 1 of 3 in the PivotTable wizard). After you've produced a PivotTable using this range, change your PivotTable's data range back to the original range, and re-create your desired PivotTable layout. Because you've used the same fieldname, 'wk', your PivotTable will "remember" that 'wk' consists of the values 1 thru 52. Using your original range, and with the "Show items with no data" field option checked your PivotTable will produce row/column entries for all known values for the 'wk' field.

 

[GPRADO]

ADVERTISEMENT

I tried what you suggested. I can't seem to get it to work.

 

[Mark W.]

I tried what you suggested. I can't seem to get it to work.

Trust me... it works like a charm.

 

[GPRADO]

Thanks for your help Mark, but I can't seem to get it to work.

George