Pivot table help for n00b
September 21, 2014 6:37 AM Subscribe
I need to provide some sales data but am unsure how to manipulate the data in a pivot table to get the specific details requested. I'm sure this is simple but my pivot table skills are, admittedly, terrible. Details inside.
The ask: "Can you please run sales numbers for Vendor Z and showing the top 25% customers (in sales) of County A and top 25% of County B? Is there a way to include contact info in the report?"
The sales report I can run allows me to select the invoice dates and selected vendor. The results will display customer type, customer, customer info, invoice date, SKU, qty sold, cost, extended cost, margin, and a bunch of other garbage irrelevant in this case. Regarding customer type: our company assigns each customer a type based on various factors. For this example let's say: Type 1, Type 2, Type 3 = customers living in County A; Type 4, Type 5, Type 6 = customers living in County B; and Type 7, Type 8, Type 9 = customers living in County C.
The report will be giving me data on customer types I don't need which will have to be filtered out. So far, so good, this I can do. But - how do I determine/indicate the top 25%? Usually I am asked for Top 10 or Top 25 customers (this I do by displaying Sum of Extended Cost), not a percentage.
Also, to include contact info, I will need to include data from several columns, i.e. Address 1, Address 2, City, State, Contact name, Phone, etc. and I can't fathom how to display all this without it looking like a hot mess.
Again, I'm sure this is very basic stuff but I am an idiot when it comes to creating pivot tables other than the few I do regularly. If further clarification is needed I can provide - thanks in advance for any help!
The ask: "Can you please run sales numbers for Vendor Z and showing the top 25% customers (in sales) of County A and top 25% of County B? Is there a way to include contact info in the report?"
The sales report I can run allows me to select the invoice dates and selected vendor. The results will display customer type, customer, customer info, invoice date, SKU, qty sold, cost, extended cost, margin, and a bunch of other garbage irrelevant in this case. Regarding customer type: our company assigns each customer a type based on various factors. For this example let's say: Type 1, Type 2, Type 3 = customers living in County A; Type 4, Type 5, Type 6 = customers living in County B; and Type 7, Type 8, Type 9 = customers living in County C.
The report will be giving me data on customer types I don't need which will have to be filtered out. So far, so good, this I can do. But - how do I determine/indicate the top 25%? Usually I am asked for Top 10 or Top 25 customers (this I do by displaying Sum of Extended Cost), not a percentage.
Also, to include contact info, I will need to include data from several columns, i.e. Address 1, Address 2, City, State, Contact name, Phone, etc. and I can't fathom how to display all this without it looking like a hot mess.
Again, I'm sure this is very basic stuff but I am an idiot when it comes to creating pivot tables other than the few I do regularly. If further clarification is needed I can provide - thanks in advance for any help!
I got this!
First of all, the more you manipulate your data before you pivot, the easier it is!
So first, reduce your report to just the Counties you want.
Then do a sort by each county. This part is simple math. Sort by Descending value. Then count the number of deals, and take the top 25% of the number and remove the rest of the deals.
Example. There are 100 Deals. Remove the bottom 75 of them.
Now you can pivot.
You want to present your data in TABULAR form.
Get the basic data first, Company Name, then in the value section do the Dollar amount. Click in a cell. At the top of the report, you'll see pink highlight and Pivot Table Tools. Options and Design. Pick Design.
On the left hand side you'll see a drop down in the Ribbon (I hope to Christ you have Excel 2010!) Drop down on Report Layout, and select Show in Tabular Form.
Now, you can click the fields you want to display, Address, City State, Blood Type, and they'll display side-by-side, instead of the default cascade.
Now at each item it may try to subtotal. So right click on the items with Minus signs (-) next to them and deselect Subtotal "customer". The stuff between the " " will be whatever the column name is. This will give you clean lines all the way across. The bummer is, you'll do it for each column until you get to the money.
There are WAY more sophisticated ways to do this, but I'd need screen shots and hoo-ha and you just want to get this done.
posted by Ruthless Bunny at 7:22 AM on September 21, 2014 [1 favorite]
First of all, the more you manipulate your data before you pivot, the easier it is!
So first, reduce your report to just the Counties you want.
Then do a sort by each county. This part is simple math. Sort by Descending value. Then count the number of deals, and take the top 25% of the number and remove the rest of the deals.
Example. There are 100 Deals. Remove the bottom 75 of them.
Now you can pivot.
You want to present your data in TABULAR form.
Get the basic data first, Company Name, then in the value section do the Dollar amount. Click in a cell. At the top of the report, you'll see pink highlight and Pivot Table Tools. Options and Design. Pick Design.
On the left hand side you'll see a drop down in the Ribbon (I hope to Christ you have Excel 2010!) Drop down on Report Layout, and select Show in Tabular Form.
Now, you can click the fields you want to display, Address, City State, Blood Type, and they'll display side-by-side, instead of the default cascade.
Now at each item it may try to subtotal. So right click on the items with Minus signs (-) next to them and deselect Subtotal "customer". The stuff between the " " will be whatever the column name is. This will give you clean lines all the way across. The bummer is, you'll do it for each column until you get to the money.
There are WAY more sophisticated ways to do this, but I'd need screen shots and hoo-ha and you just want to get this done.
posted by Ruthless Bunny at 7:22 AM on September 21, 2014 [1 favorite]
Also, as a fallback: once you've categorized the customers by county and selected the ones you want, if you're in spreadsheet format (not pivot table) you can use conditional formatting; there's a preset function that picks out the top and bottom 25%.
posted by GrammarMoses at 7:28 AM on September 21, 2014 [1 favorite]
posted by GrammarMoses at 7:28 AM on September 21, 2014 [1 favorite]
Yeah, and that 25% is misleading. Is it 25% of the total dollars, or the top 25% by simple count?
Example one:
100 customers, $100,000 in revenue. 15 of them account for $25,000 in revenue, and the other 85 comprise the remaining $75,000.
Example Two:
100 customers, $100,000 in revenue. 25 of them account for $40,000 in revenue and the other 75 customers comprise the remaining $60,000.
Sales figures are VERY tricky things.
posted by Ruthless Bunny at 7:53 AM on September 21, 2014
Example one:
100 customers, $100,000 in revenue. 15 of them account for $25,000 in revenue, and the other 85 comprise the remaining $75,000.
Example Two:
100 customers, $100,000 in revenue. 25 of them account for $40,000 in revenue and the other 75 customers comprise the remaining $60,000.
Sales figures are VERY tricky things.
posted by Ruthless Bunny at 7:53 AM on September 21, 2014
Response by poster: Thanks for the advice this far! Yes, with regards to the to 25%, it is in total dollars spent, so the first example in your second post above, ruthless bunny. That's the part throwing me off - top 25 by count I think I know how to do.
GrammarMoses, you raise a good point - I don't think this necessarily has to be presented in a pivot at all. Can you explain the preset function / conditional formatting?
posted by Bretley at 8:34 AM on September 21, 2014
GrammarMoses, you raise a good point - I don't think this necessarily has to be presented in a pivot at all. Can you explain the preset function / conditional formatting?
posted by Bretley at 8:34 AM on September 21, 2014
Why don't you show values as a % running total and then sort from largest to smallest? Or show values as a % of total and sort/calculate the 25% yourself.
posted by acidic at 9:07 AM on September 21, 2014
posted by acidic at 9:07 AM on September 21, 2014
Okay, here's how you figure out the top 25%.
Total dollars spent X .25. That will give you the dollar amount. Then, since you've sorted your deals in descending order, just highlight the first cell, and slide it all down watching the calculator at the bottom of the sheet. Stop when you get close to the number. It won't be exact, because it can't be. But you can be, and remember this phrase, 'directionally correct.'
posted by Ruthless Bunny at 9:10 AM on September 21, 2014 [1 favorite]
Total dollars spent X .25. That will give you the dollar amount. Then, since you've sorted your deals in descending order, just highlight the first cell, and slide it all down watching the calculator at the bottom of the sheet. Stop when you get close to the number. It won't be exact, because it can't be. But you can be, and remember this phrase, 'directionally correct.'
posted by Ruthless Bunny at 9:10 AM on September 21, 2014 [1 favorite]
Sorry to be so late in rejoining the conversation.
Conditional formatting: in the ribbon across the top of the window, on the Home tab, just right of center you'll see a menu called Conditional Formatting. Select the Top/Bottom Rules menu item from the dropdown. You'll see a submenu drop down that offers "Top 10%" and "Bottom 10%," among other things. Select either Top 10% or Bottom 10%. The message box will suggest classic style, and that you format only top or bottom ranked values. Keep those settings. Just below those items you'll see [[Top/Bottom]] __10__ [] Percent. That's the tool you need.
Once you've got a column for each county, use this function according to your needs. Change the 10 percent to 25 percent. For the top 25% use one color style and for the bottom 25%, use another. You won't need to do any further sorting at that point -- just apply the conditional formatting column by column.
I hope this explanation is clear. Feel free to MeMail me if you want further tips about this technique.
posted by GrammarMoses at 6:30 PM on September 21, 2014 [1 favorite]
Conditional formatting: in the ribbon across the top of the window, on the Home tab, just right of center you'll see a menu called Conditional Formatting. Select the Top/Bottom Rules menu item from the dropdown. You'll see a submenu drop down that offers "Top 10%" and "Bottom 10%," among other things. Select either Top 10% or Bottom 10%. The message box will suggest classic style, and that you format only top or bottom ranked values. Keep those settings. Just below those items you'll see [[Top/Bottom]] __10__ [] Percent. That's the tool you need.
Once you've got a column for each county, use this function according to your needs. Change the 10 percent to 25 percent. For the top 25% use one color style and for the bottom 25%, use another. You won't need to do any further sorting at that point -- just apply the conditional formatting column by column.
I hope this explanation is clear. Feel free to MeMail me if you want further tips about this technique.
posted by GrammarMoses at 6:30 PM on September 21, 2014 [1 favorite]
« Older My wife is overworked and stressed and it's making... | Eating healthy on a tiny budget in Superior-Duluth Newer »
This thread is closed to new comments.
posted by GrammarMoses at 7:12 AM on September 21, 2014