Cannot create a Pivot Table because source data has subtotals

Hi All,

The source data that I want to create a pivot table from has subtotals for every department which I need to keep in the source data but when I try to create a pivot table I get the error message below

"Because your source data has automatic subtotals, Microsoft Excel cannot create a Pivot Table report from it."

I can remove the subtotals and add a simple sum formula which then allows me to create a pivot table but i really need to have the subtotal formula rather than sum formula in the source data.

Does anyone know how i can overcome the issue?

Thanks

Hey Olly,

Thanks for your response, Can not really change the source data is that is manually typed in each cell. I am using the file for overheads budget planning and the source date is essentially all the budget owners typing the numbers in the source data / table manually depending on the desired budget needed for the year ahead. The source data is already populated with last years actuals and a draft budget for the upcoming year.

I wanted to pivot the data to start the discussion with the budget owners based on a simplified format and then go into each line to amend cost / budget as needed.

Thanks

Hi,

I have attached a sample workbook. The pivot has worked in the sample workbook attached but with the same data in my existing file the pivot does not work. So perhaps i just to start again and create a new workbook?

Thanks

Hi,

Sorry i was just seeing how best to share the workbook as it had alot of tabs. I have attached a simplified version. You will see the error in this workbook.

Thanks