Pivot Table - monthy dates

[jimt]

I'm using Excel 2000. I have financial data with expenditures by date. I've
added a column to calculate the month and year of the expense. A pivot table
has been built over the data to show the expenditures by year (rows) and
month (columns).

I need to have all months (Jan...Dec) listed to be able to link from another
spreadsheet. However not all months has expenditure.

Is there a way to "force" the columns to include all months regardless of
whether there is data for those months? I could add a row with zero expense
for each missing month in the data; however I have about thirty workbooks
(one for each account) which would require a lot of entries.

Thanks
Jim T

 

[Debra Dalgleish]

In the pivot table, right-click on the Months field button
In the popup menu, click on Field Settings
Add a check mark to 'Show items with no data'
Click OK.

 

[Debra Dalgleish]

Another option is to add the date field to the pivot table, in the row area.
Then, right click on the Date field, click Group and Show Detail, then
click Group
In the Grouping dialog box, click on Months and Years.
Click OK
Leave Years in the Row area and move the Months to the column area

Then right click on the Months field button
In the pop menu, click on Field Settings
Add a check mark to 'Show items with no data'
Click OK.

 

[Shane Devenshire]

Hi,

Right-click the field button for the Month field and choose Field Settings.
In the lower left corner of the dialog box turn on the Show Items with no
data.