How to update Pivot Table/Charts when raw data table are added in?

Hi guys,

I have included the 'example' workbook for you guys too see.

Basically, I have the raw data sheet that has small 'tables' for each week. I want to have a pivot table and/or chart that allows me to sort by week and see in a particular week, how many are late because of 'sick', how many are 'LS'... And ultimately create a chart that I can filter by week to see the percentage of each late reasons given the particular week.

Please let me know if this make sense!!

Thanks again for you guys' help!

example.xlsx

Originally Posted by amphinomos

Attachment 307745
Here you go. Your data should be contiguous so you can insert a pivot table. Also I've inserted a table which means that when you add lines to your data as the weeks go by it will automatically update your pivot table.

Thanks for the quick reply.

1) Is there a way to keep the space?

2) When in add more data at the bottom, do I have to re-adjust the range everytime?

1) You can use the Pivot Table Wizard which you can access with ALT+D+P. Choose "Multiple consolidation ranges". However when you add a week you will have to manually add the new range through the wizard.
2) If you choose to keep your data as a table, when you add new rows the table will automatically update the pivot with no action on your part. (Just make sure the little ticker on the bottom right goes to the last row, you can click & drag it).
Both are adequate solutions.