🧙‍♂️ Pivot Table Excel Tips & Tricks

[Music] hey everyone kevin here today i want to show you my favorite top 14 tips and tricks for pivot tables in microsoft excel there are lots of great ones that i think will make data analysis even easier for you if you want to jump around this video feel free to use the timestamps down below also if you want to follow along i've included sample data in the description all right let's jump into microsoft excel and let's check these out tip number one you can use natural language to automatically create pivot tables and to answer business questions here in excel my manager recently came up to me and said hey kevin can you tell me how many cookies have we sold to the customer acme bytes in 2020 i've heard that pivot tables can help me with this but how do i use them well luckily excel can do the heavy lifting for me within excel up on the top tabs click on the one called home and all the way over on the right hand side there's a newer option called analyze data let's click on this this opens up a pane over on the right hand side where i can get insights on my data now before i ask my specific question down below i can see different recommendations or suggestions basically this is excel looking at all of my data over here and it comes up with interesting views of my data i could choose one of these and i could insert a pivot table i could insert a pivot chart and that's pretty easy but i came here with a specific question in mind i want to know how many cookies we sold to acme bytes so here i'll type in let's say units sold to customer acme bytes and let's say in 2020. so that's the question i have here i'll hit enter and whoa look at that it looks like excel automatically looked at my data and figured out that we sold 255 000 cookies to this customer and right here i can insert a pivot table when i click on this this opens up a new sheet with a pivot table created for me here i'll zoom in a little bit and we see that it filtered it down to the customer i was interested in it looks like it also figured out the date and here it tells me how many cookies we sold so here it created a pivot table on my behalf and i didn't have to do any of the heavy lifting of going in and choosing what filters or rows or values i want excel did all of that for me tip number two you can click into values in a pivot table for additional details here i have my first pivot table complements of excel and here i see that acme bytes we sold 255 000 units but let's say i want to see the specifics of some of those orders maybe i want to know the revenue the cost of some of those orders here i could simply double click on that value and here i get another excel sheet generated for me and i see every single order by the customer acme bytes i could see what product they ordered the revenue the cost so this allows me to very easily jump into the details directly from a pivot table tip number three you can create a pivot table using multiple tables of data how do you do this well first off you need at least two tables and you also need some common field between those tables here for example i have a table with customer information for the kevin cookie company and this table has a customer id here i'm going to jump to another table with all of our orders and here too i have a field with the customer id so i can connect these two tables together to connect them go up to the tab on top called data and within data within the section called data tools click on relationships here i can define relationships between these two tables to create a new relationship click on new and here i need to define what tables i want to relate here i'll select my customer table and i'll also select the order table over on the right hand side i need to specify what key i want to connect these on so what is the common value between these two tables and as we saw when we looked at these two tables it's the customer id so here i'll select customer id and in the order table i'll also select the customer id this looks good so i'll click on ok i've now defined all my relationships so i'll close this to insert a pivot table go up to the top tabs and click on insert all the way over on the left hand side you can insert a pivot table i'll click on this downward arrow and we want to insert from a data model when i establish those relationships between these two tables that created a new data model click on this i'll add my new pivot table to a new worksheet and then click on ok this now drops me into a new pivot table and i can now construct my pivot table using fields from both of these different tables so here for example maybe i'll expand the customer table and let me pull the customer name down to rows and maybe i want to know how many cookies did each customer order units sold and that sits within the order table here i could take units sold i'll drag it over to values and look at this i've now built a pivot table using data from two different tables tip number four you can change the field layout and sorting options here i can see all of my fields but to truly see this full list i have to scroll down let's say maybe i want to see all of these fields all at once i can click on this settings gear and here i can shift my field layout i'll take this one as an example and here i can see all of my fields side by side with my areas also when i look over at my fields finding a field might be difficult the order in which all of these appear match the original data source here i see all of my fields from left to right and that same order applies to the fields here i can click on the settings gear and then i can change that to a to z this might make it a little bit easier to find the field that i'm looking for tip number five i can add new calculated fields so right here you see i have all of these fields included but what if i want to add some additional fields here for example i have all of my customers and i have their revenue and also their cost but maybe i want to include another column with the profit now i could click in here and i could type in a formula but that's really not the best way to do it instead i'll click into my pivot table i'll go up to pivot table analyze and under fields items and sets i can add a new calculated field click on this within the calculated field prompt here i can type in a name for my new field and i'll type in profit down below i can enter in the formula and profit is the revenue minus the cost that looks good so i'll click on add and then click on ok back within the pivot table now you'll see now that i have a new field called profit and here i've already inserted it into values and here it shows up within my pivot table tip number six you can very easily sort values within your pivot table so right here let's say i want to know which customer is the most profitable over here i can right click and within this menu i can go down to sort and i can select largest to smallest and right here i can see that acme bytes is our most profitable customer tray delicious we need to work on driving some more profitability tip number seven i can show values differently within my pivot table now right here with this profit column it's just showing me the pure and total amount of profit for each one of these customers but maybe instead i want to know what was the profitability rank or what percent of the total profit does this customer make up i can very easily set this here i'll click into this cell i'll right click and write down within this menu i have the option to show values as over here i can choose percent of column total when i click on this this will show me that acme bytes makes up over 30 percent of our profit i should be really nice to this customer i could right click again go back to show values as and i have all of these other options as well right down here i could rank largest to smallest when i click on this i can choose the base field for the customer name i'll click on ok and here i can see that acme bytes is the number one customer right up here if i want to rename this header i could type in rank of profit so i could just click in and change this to whatever i want now let's say maybe i still want to see the total profit amount here i can click on this field again i'll drag it over to values and here i can still see the overall profit tip number eight i can also choose to summarize values within a pivot table so far as i've been adding items to this pivot table into values by default it'll sum up the values but what if i want to know a count of orders by customer here i can take the order id and i'll drag this into values now you'll see that it's summing up the order id here if i jump to the original data source you'll see that the order id is just an arbitrary number it doesn't make sense to sum that up back within the pivot table instead of summing it i want to count it right over here i'll click into any one of these cells and i can right click and i can go down to summarize values by and here you'll see that it's currently set to sum i could change its accounts there's also average max man and all these different options i'll select on count and here now i can see accounts of all of the orders associated with each customer so here i see acme bytes is not only the most profitable but they also order the most maybe those two go hand in hand tip number nine i can use slicers to very quickly and easily filter my data to insert a slicer go up to pivot table analyze and under the filter category click on insert slicer here i can choose what slicers i want to insert i want to insert a slicer on the customer name and also on the product next i'll click on ok and look at that i now have two new slicers that i can filter my data on so let's take an example let's say my manager comes to me and says hey kevin i want to know how much profit we earned from abc groceries for chocolate chip cookies and fortune cookies no problem here i'll click on abc groceries and look at that it filtered it down to abc groceries and here i'll click on chocolate chip i'll press ctrl and click on fortune cookies and here i can tell very quickly that we earned about a little over two hundred thousand dollars from that combination but maybe my manager comes back and says oh sorry made a mistake i didn't mean abc groceries i actually meant acme bytes now typically i'd be kind of mad because i'd have to go back and reconfigure my pivot table but here it's really not much of a problem i could just click on acme bytes and look at that my view is automatically updated using these slicers it makes filtering a lot easier tip number 10 we can insert a timeline to also very quickly filter our data based on dates once again right up on top under pivot table analyze under filter there's the option to insert a timeline over here i'll select date and then click on ok so let's say that maybe my manager comes up to me and he wants to know how much all of our customers earned between january and april in 2020 well luckily i now have this handy new timeline and i could use this to very quickly get that answer so here i'll select january and i'll go over through april and look at that it automatically updates the profit amount for each customer between that time range now my boss thinks i'm a superstar and i'll probably get a promotion tip number 11 you can use a pivot chart to visualize your data when you have your cursor within your pivot table go up to pivot table analyze and over under tools you can insert a pivot chart i'll click on that and i'll go with one of these column charts and then click on ok so here now you can see the profit by customer from january through april now the neat thing is when i use this in combination with my timeline here i can update my filter and look at that it automatically updates the pivot chart to reflect that now with these different tools of slicers and timelines and a pivot chart i could build some pretty fancy looking dashboards tip number 12 i can customize my pivot table layouts right here first off when i look at my pivot table you'll see that for each customer it shows the subtotal and when i go to the bottom of my pivot table here i see the grand total maybe i don't want to see that well up here i can go over to design when i have the pivot table selected and right here i can choose to not show any subtitles so there you see the subtitles disappeared i can always click back if i want to bring them back also when i go down here i see the grand total i could click over here and i could turn off the grand totals if i don't want to see them and once again i can bring them back if i want along with turning subtotals and grand totals on or off i could also change the layout of this pivot table right up here i could click on report layout and right now this is in the compact form here i see the customer and the products directly underneath the customer name now i could change this to outline form and here i get a separate column with the product now this could be beneficial if say maybe you want to filter the product separately this might make it a little bit easier and i have other options like tabular form you can go through here and you can experiment with these different formats to see which one you like the best now let's say you go through and you customize what your pivot table looks like you might not want to have to do this every single time that you launch a pivot table if you want to set the defaults you can go to the file menu go down to options and under data you can click on edit default layout and here you can choose what it should look like every time you create a pivot table tip number 13 you can group your row labels together let's say that for whatever arbitrary reason i want to know how my customers who start with a compare to other customers so abc groceries and acme bytes both meet that criteria here i could highlight those two customers i'll right click and i'll select group so now you see that they're part of group one here i'm going to collapse all of the fields and now i can compare this group to all of my other customers tip number 14 and unfortunately this is the last tip of today but hopefully you've learned some good ones already with tip number 14 you can update the row labels to appear how you want them to appear now right now this is just sorted alphabetically but for whatever reason my manager really loves tray delicious as a customer i don't know why they're our least profitable customer but you know whatever and he always likes to see tray delicious at the top of the list here i can select tray delicious i can right click and i can move this up the list if i'd like here i can move it to the beginning i can move it up down or to the end alternatively i can press control x on that item that'll cut it i can go to the top and press ctrl v and that'll paste it at the top so that's a very quick way that i can order this row label list how i want it to appear and in terms of how i want it to appear acme bytes actually goes by acme bytes llc now i could go back to the original source data i could update it there and then i could refresh my pivot table to factor that in alternatively i could also just come in here and here i'll type in llc hit enter and i can just update the value directly in here unfortunately i can't do that for the profit otherwise i would make this company look even more profitable than it already is by maybe adding a few more zeros at the end of these numbers alright well those are all of my favorite tips and tricks if you learned some new ones please give this video a thumbs up to see more videos like this please consider subscribing also if you want to see me cover any other topics on this channel leave a note down below alright well that's all i had for you today i hope you enjoyed and as always i hope to see you next time [Music] bye [Music] you

Channel: Kevin Stratvert

Views: 121,007

Rating: 4.981916 out of 5

Keywords: kevin stratvert, pivot table, excel, pivot table excel, pivottable, pivottables, pivotable, pivot tables, excel pivot table tutorial, excel pivot table, how to create a pivot table in excel, how to use pivot tables in excel, how to, use, create, make, how to create pivot tables in excel, in excel, pivot table excel tutorial, tutorial, advanced, tips, tricks, tips and tricks, beginners, how to make pivot table in excel, pivot tables excel, excel tutorial, excel pivot tables

Id: 3g1CSG5zvQ0

Length: 17min 23sec (1043 seconds)

Published: Wed Apr 21 2021