Excel Pivot tables can be a pretty handy tool for summarizing and making sense of your data. In this example, I’ve built a small table to show you how you can use pivot tables to calculate value differences between different dates. This can come in handy especially when capturing and reviewing day over day, month over month or year over year changes. In the case below, I’ve shown how you can monitor daily changes in your inventory valuation levels to capture changes in stock levels or value. You can then use this information to do further investigation from a transactional level to determine why your stock levels may have changed in that day (i.e. cycle counts, purchase order receipts, work order completions, customer RMAs/returns etc.).
Here are a couple of ideas on where you could apply this pivot table concept in everyday business practices (I could spend a long time putting together a comprehensive list so I’ve only added a few here):
- Reviewing production performance between any two years/quarters/months/days.
- Checking for value change between dates for GL accounts.
- Reviewing the total number of orders shipped between two days for performance metrics.
Let’s have a look at how we can do this!
That’s it! Let me know in the comments where else you think this functionality would be useful and start thinking about what other types of calculated fields you could use to get some important information out of your data using pivot tables. Thanks for stopping by!
Cheers,
Download the Free Excel Workbook!
Hi Brad –
I’ve attempted to download the file three times now and nothing happens.
It seems that once the button is selected, the original website page begins to ‘float’ over the entire screen. Trying to select anything at that time returns an ‘Error on Page’ notice.
Would you please email a copy to me?
PS> I have signed-up for the newsletter. 🙂
Cheers!
Joe
Hi Joe,
I will be sure to send you a copy later today. Thanks for having a peak!
Cheers,
Brad
Hi Brad –
Apparently, I must hold down the Cntrl key when selecting the ‘Get It’ and “I Want It’ buttons.
Unfortunately, I was still unable to download the file at the site, the download file emailed to me.
At this point, I’ll wait for your file.
Hope you’re having a great day,
Cheers!
Joe
Hi Joe,
I’ve forwarded you an email with the workbook. Let me know if you have any issues. I’ll have to look into why you are having issues with getting the file.
Brad
please did you can send to me copy , Ican’t downloaded
best regard
Hi, Thanks for the above. With regards to Inventory…if you have inventory by day but when we are looking at the total month the Inventory at the grand total level..sums for all of the days in the month. Is there any way to have the grand total capture the last day of month or of the filter for inventory.
Thanks,
Hardik
Hi Hardik,
I just want to make sure that I am not missing what you are asking. Would you rather instead of filter by day, filter by the last day of every month and then look at the difference between each of those days?
If this is your question, one way of doing this is by manually selecting and filtering by the last day of each month within the column section of your pivot table. If you are looking for an automated filter to do this or if you are looking for something completely different let me know.