Microsoft Excel Tutorial: Creating Stratifications with a Pivot Table
Welcome to MrExcel netcast, where we bring you the best tips and tricks for mastering Excel. In this episode, we will be discussing stratifications with a pivot table. This is a topic that I often cover in my live Power Excel seminars, but surprisingly, I have not yet made a video about it on YouTube. So, let's dive in and learn how to create stratifications using a pivot table.
This topic takes me back to my days working in accounting, about 20-25 years ago. We had a new VP of Sales, John, who moved from California to our home office. He was surrounded by cubes of people doing order entry and every day, he would walk past them and see the payroll involved in this process. To John, our business was simple - we had a million dollar sale every month. He didn't understand why we needed so many order entry people for small orders like spare parts and batteries. He suggested we outsource these small orders to a value-added reseller and just have one person enter his big order each month. I wanted to show John that these small orders actually had a higher profit margin, and I did so using a pivot table.
To demonstrate this, I will be using a two-year invoice register with data on various orders. We will create a pivot table by selecting one cell and choosing "New Worksheet" as the destination. Now, this pivot table will be different from any other pivot table you've built before. We will take the Revenue field, which usually goes to the Values area, and move it to Rows. This will show us the dollar amounts for each order, from small to large. Next, we will take a Text Field, such as Customer, and move it to Values. This is a bit unconventional, but it will help us see the number of orders for each customer. We will also change the calculation to "Count of Customer" to see the number of orders instead of the sum of revenue.
Now, here comes the interesting part - creating stratifications. We will take the first revenue field and use the Group command to create buckets of a certain size, starting from 0 to 16000000 dollars with 10000 dollar buckets. This may require some experimentation, but you can always come back and change the grouping later. This will show us the number of orders and the revenue for each bucket. But we can take it a step further by adding additional fields such as Profit, Percentage of the Column, and % Running Total In. Just be careful to change the calculation from Count to Sum when reusing the Revenue field, or your numbers will be incorrect.
By doing this, we can see that although the small orders may seem insignificant, they actually contribute to a significant portion of our profit. In fact, in this example, the small orders account for over a third of the profit. This is something to keep in mind when considering outsourcing or eliminating certain processes. And remember, when reusing the Revenue field, make sure to change the calculation to Sum to get accurate results.
If you want to learn more about pivot tables and other Excel topics, check out my book "Power Excel with MrExcel" which covers 617 Excel mysteries. You can find the link to purchase the book in the top right-hand corner of this video. Thank you for watching and don't forget to subscribe to our channel for more Excel tips and tricks. See you next time for another netcast from MrExcel.
Buy Bill Jelen's latest Excel book: [ Ссылка ]
You can help my channel by clicking Like or commenting below: [ Ссылка ]
#excel
#microsoft
#microsoftexcel
#exceltutorial
#walkthrough
#evergreen
#pivottable
#pivot_table
#excelpivot
#excelpivottablestutorial
#excelnavigation
This video answers these common search terms:
how to bucket numbers in excel
how to group by price buckets in excel
how to add buckets or groups in excel
how to bucketize in excel
how to create data buckets in excel
how to do buckets in excel
how to do stratified sampling in excel
how to stratify a sample in excel
how to do a bucket analysis on excel
how to group pivot excel
how to create groups in pivot table in excel
how to group numbers in excel pivot table
how do you group numbers on excel pivot table
how do you group fields in an excel pivot table
how to create buckets in excel
how to group data into buckets in excel
how to make buckets for data in excel
how to make buckets in excel
how to make categorical variables for number buckets excel
how to set up aging buckets in excel
Table of Contents:
(00:00) Stratifications with a Pivot Table
(01:26) Building a Pivot Table to demonstrate stratifications
(03:24) Using Pivot Table calculations to show the impact of small orders on profit
(04:26) Watch out for default settings when using multiple fields in a Pivot Table
(05:22) Clicking Like really helps the algorithm
Join the MrExcel Message Board discussion about this video at [ Ссылка ]
Ещё видео!