Pivot table with automatically updating formatting or other tools

pcarpilot

New Member
Joined
May 3, 2013
Messages
1
I have two questions I was hoping I could get some help from the experts here. I have unit sales data for the whole country that I add to my raw data file monthly as additional rows, see below. My pivot table is linked to the columns of the raw data so it automatically adds each months unit sales to the pivot table. This makes it pretty seamless and easy to update, but I'm lacking some analysis tools that would update automatically with the pivot table.

1) How can I add some functionality to my pivot table to alert me to when a customer's latest month is above or below their average month? ie in column T as a % or using conditional formatting

2) We are growing quickly so I need a tool to analyze how many new customers each product has in a month. Can I put a conditional formatting rule that looks at each customer's preceding monthly sales, if there is nothing there it highlights the 1st sale in green or something?

Or, is there another way to create a pivot table that would count the number of new customers per month, keeping in mind that some customers order every month and others only order once every 6 months?

Really appreciate any help!


Raw Data:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Month[/TD]
[TD]Year[/TD]
[TD]Customer[/TD]
[TD]Product[/TD]
[TD]Qty[/TD]
[TD]Rep[/TD]
[/TR]
[TR]
[TD]Jul[/TD]
[TD]2012[/TD]
[TD]Account 2[/TD]
[TD]XYZ[/TD]
[TD]6[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]Jul[/TD]
[TD]2012[/TD]
[TD]Account 5[/TD]
[TD]XYZ[/TD]
[TD]12[/TD]
[TD]Ryan[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]2013[/TD]
[TD]Account 2[/TD]
[TD]ABC[/TD]
[TD]6[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]Dec[/TD]
[TD]2012[/TD]
[TD][TABLE="width: 119"]
<tbody>[TR]
[TD="class: xl65"]Account 6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]XYZ[/TD]
[TD]18[/TD]
[TD]Paul[/TD]
[/TR]
[TR]
[TD]Sep[/TD]
[TD]2012[/TD]
[TD]Account 3[/TD]
[TD]ABC[/TD]
[TD]6[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]Sep[/TD]
[TD]2012[/TD]
[TD]Account 5[/TD]
[TD]ABC[/TD]
[TD]6[/TD]
[TD]Ryan[/TD]
[/TR]
</tbody>[/TABLE]


Current pivot table:
Capture pivot table | Flickr - Photo Sharing!

8713679145_02ee12dd2b.jpg
 

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top