Working With Pivot Tables In Numbers

hi this is gary with macmost.com in this special live episode i'm going to take a close look at the new pivot table numbers feature [Music] so macmost is brought to you thanks to a great group of more than a thousand supporters you can go to macmost.com patreon there you can read more about the patreon campaign join us and get exclusive content and course discounts so there's a new feature in numbers version 11.2 so make sure you've got that and if you have that you'll be able to use pivot tables pivot tables is something that numbers users have been dying for for years and it's finally here and there's a lot you can do with it it's a really interesting feature a lot of people are going to be using it coming over from excel they use pivot tables there and now they can finally use them in numbers and a lot of people i've been using numbers all along so it's a very new feature to them so let's take a look so here i've got a spreadsheet just a simple thing here i am in numbers version 11.2 and let's say i want to analyze this data now i've always said that the best way to have your data in numbers is in kind of a database format don't try to do anything fancy with layout and design or anything like that you just figure out what your your key piece of information is in this case there's sales here and the fields for sales are the date the product and the amount of the sale and there could be more there the price and all sorts of other things but each one of these is like a record in a database and the database just grows and grows and grows and you can keep adding to it you can have hundreds or thousands of things in this eventually so that's great but how do you look at this how do you like figure out like what's going on here like what products are selling the most right what what's our best sales day that kind of thing like how do you figure that all out and you could use charts and you could do functions like you could uh you know do some if to get the sum total of all the banana sales or all the apple sales you can do countif to get like how many times were a group of these products sold and all of that but pivot tables make it really easy to visualize things and that's the key here pivot tables are a visualization tool so they're not going to change the data they're not going to add anything to the data there at all they're going to let you see things in the data to figure things out based on the data you've got so to create a pivot table all you need to do is select anything in the table that has the data and then click this button here however i'm not going to use that button because if you go to organize create pivot table you get the same function as that button here on new sheet but i'm going to use on current sheet now i could see why you would want a pivot table to be on a new sheet but if you have it on the current sheet it's easier for me to demonstrate in a tutorial like this because we can see things side by side so on current sheet and it creates this pivot table and it's an empty pivot table to begin with so there's nothing in here but it takes us right to the organize sidebar pivot options and this is where we set up the pivot table so we've got the fields here and these are the fields from the original table the one that's linked to this pivot table and then we could see down below here we've got columns rows and values and they're all empty now which is why this is empty so let's go and add something to this i'm just going to add the product and if i do that i already get something very useful i click product and it will group the products together so before maybe i didn't know for instance um you know how many products were there in this i mean i see apples bananas and oranges are there other things well it turns out just those three things so i can see there actually three items in here now let's select something else let's say amount and now we see the amount some here to the right so it's actually totaling up all of the bananas the apples and the oranges and we see the sum total if we look at the sidebar here we see product in the rows and amount this amount column here in the values so you've got rows here and values and this is what we get in the table so this is what happened when we simply checked these things i could have dragged and dropped these here instead of checking them but by checking them it basically adds them to the place where they make the most sense so let's go and change what we have here we have the amount sum maybe that's not what we want so one of the cool things that we do is we can click on the i button here and we can see a bunch of stuff we can see summarize by so it's going to choose sum automatically but let's say i want the average and you can see now i get the average sale so i can see the average sale of apples is about 60 apples whereas the average held oranges is 45 oranges that could be very interesting information to me and something that's very hard to get by looking at these numbers i could look at the count so how many total sales forgetting about the amounts there were 17 sales of apples 23 sales of oranges so buyers more frequently buy groups of oranges than they do apples but they're buying more apples in general and then you could do things like show as a base value or maybe the percent of the grand total so let's do that and we could see that 38 of all sales are oranges would have been hard to actually figure that out here let's do the sum if we do sums we can see they're actually pretty evenly divided right so the total number that i sell are actually pretty equal so this is the key thing here we're visualizing data we're getting information from this table we don't have to do anything fancy with the table here we can just use these pivot tables to kind of extract the information and while you may actually see this and say oh i want this here i want this to be the thing that i see when i come to this document all the time another way to use pivot tables is simply to be altering them all the time and doing exactly what i'm doing now is to be playing around with them changing all the things trying to figure stuff out you know if i want to increase my sales and maybe i'm just constantly playing around looking at averages sums percentages trying to figure things out and maybe i'll come up with an insight for my business let's go and continue here and let's add date so i'm going to date here and it's going to put dates across in columns because that's all it has left i was already using rows but that might not be the most useful way to do this after all we end up with you know 12 columns going all the way across one for each month if i click here it's one of the many places where i can swap column and row fields and now this is a little more useful since there's only three products having apples bananas and oranges across like that makes more sense and having the months going down makes more sense as well now i'm still in percentages here let's go and switch back to base value here so i can see here the grand total for january is 276 items were sold and i could see there were some apples there were many more bananas no oranges in january so i can visualize this data like this now notice how it took the dates which are actually real dates and it grouped them together by month that's incredibly useful and actually difficult to do possible but difficult uh regularly so it was nice that it did that but what if we don't exactly want that well here it says rows and says date it says ym year in month so it's grouping them by year and month if we actually went to january 2022 here it would actually be separate from january 2021. i could click i here and then do another group by so maybe i want to do group by quarter and it's going to put them together by quarter maybe i want to do the actual values and actually have all of the dates there so i could do that that might make sense i can do weeks so you have a lot that you can do i could even do months and then it would group all january sales together regardless of the year so if i had 10 years worth of sales i could see like how well do my apples sell in january as opposed to may in any year so it makes sense here to do like maybe like year and month is something that really helps out here i can then go and try other things to extract information let's look at the values here and maybe we'll do percent of grand total so we can see percents here that actually makes it easier to see that october was my best selling month um and then we could uh you know choose other types of things to do there's lots to do it really depends on what type of data you've got as to how you're going to use this to kind of extract information let me go and take a look at the chat here no shortcut do you mean shortcut to actually create a table now there isn't under um if i select this table here and i go to organize organize create pivot table so on new sheet and cr on current sheet are menu items so you can go into system preferences keyboard shortcuts and create keyboard shortcuts for on new sheet on current sheet so you have that ability if you want to and somebody asked if i intended to add pivot tables to my numbers course so my idea of the idea of the course is to learn all the core stuff and then things like new features i usually put them in regular videos i wasn't going to just uh restrict my pivot tables thing to the course so i actually did a video last week on using pivot tables it's the same video i would have done and put it in the course except everybody can see it so if you've taken the course go watch that video and it kind of adds pivot tables to the course and it's also available for those that haven't taken the course all right let's go and look at another example here so great so let's say completely different situation here get away from sales and all that let's say you're a teacher you've given out you know quizzes throughout the entire semester you've got certain quizzes on certain subjects on days you've got your students and you have their scores compiled now what's nice about doing it like this is you can just record each score just you know as they come in they don't even have to be sorted in order you know somebody uh you know you get a quiz result somebody makes up a quiz or whatever you can go and add the the test name the date the student and the score and you've got this database here but this database really doesn't give you information about like which student is doing best in the course or which one is maybe falling behind that needs some help which test that you gave out may be harder than the rest or easier than the rest that kind of thing so you can use pivot tables to do that so with this table selected anything in it let's go and create a pivot table i'll do it on the current sheet here and let's add some things to this so you could do this a lot of different ways let's say we want to focus on students so i'm going to add student first here and i can see there there are my students and let's add the scores here and it's going to give us the sum of their scores which is not what we want right scores aren't summed they are average so we get the average there now you could format the cells here just like you could in a regular table so i'm going to double click there just select these actually i want to make sure i select the footer row there too and i can go into format cell and let's uh do um no decimals there so now i can see that so the average score there i could even go and say percentage and um that's actually that's not going to be very compatible with pivot tables i see here because pivot tables are already you know not using decimal places like that that's interesting so we could do a custom format let's just stick with number now and i'll just do like that and we can go back to organize pivot tables there so now we can see who's doing best in the class i can actually sort these in my example here are five students but maybe you have 30 students in a class maybe it's a college course and there are 300 students so be nice to sort them by average you can't can do that let's what you want to do is we're going to sort the students by the score so we actually want to click on students here not scores we want to sort the students not by name but by score average and then we want to do that descending now we can see finally that mark is well mark and steve are doing well in the course and uh dawn and kelly not as good so we want to you know if we want to use the data that way we can but let's go and add more stuff to this let's for instance add the tests so i'm going to add that and it's going to add the tests going across like that so now i can look at a lot of things like for instance i still have all their scores here for the students but i can look at the tests themselves and i can see what we've got here like for instance this test the average score is much higher than this test by a lot so i can see here that there are a couple tests here that maybe were a little easier and some tests that were harder for students so maybe not useful this for this class but maybe i want to you know think about that you know why is that the case look at those tests maybe um next year or next semester um i want to kind of take that into consideration maybe it's not the test themselves you know maybe i need to spend some extra time going over this subject and this subject with the students so they understand it a little bit better so you could come up with a lot of things like that you can also easily flip how things are working here in terms of columns and rows way to do that is click here there are actually a bunch of ways we're going to swap and now we have the tests going this way and students going that way you know if you have 100 students obviously the students would want to be vertical and the tests across but you can do either one and we also had the date and each test had its own date so sometimes you may want to think of like okay is you know which which date would you each test let's go and add date here and it's actually going to add it you can see it's going to add it to row so rows are test and date let's make the date the first thing and i don't want to group them by year and month i want to group them by value so now each date corresponds to a test but there's like way too much information here and it's repeating things so we don't need to show for dates the total rows when i remove that notice now it's a lot neater it's date test and it goes across it looks like the table before but now i actually have the date next to the test which is kind of nice so you can really massage things to get what you want here in terms of the you know the you know how these fit into columns rows and values and you can move these around and try different things you could also add multiple values so let's do something like let's add actually i'm going to show you multiple values in the next example let's go to the next example i'll check the chat here briefly yeah i'm going to show charts here how to do charts i'm going to get to that yeah joel i mentioned earlier that i like to use the organize you know to add this because i can add pivot tables to the same uh sheet here if i use this one it creates a new sheet that's perfect for what most people need but for demonstrating things tutorials it's really useful to actually put them side by side yes you can make as many tables as you want so i have this sales pivot table here i can select this table again and i could go and say organize create pivot table on current sheet and you can see i've got two pivot tables now and i could have them showing different things and i can create five six seven pivot tables so if i like how something looks i can leave it there create a new pivot table and then go and have that arranged in a certain way and say oh i like that i want to keep that too and then continue to go ahead and add uh you know more and more pivot tables you know on the same sheet on different sheets that kind of thing yes so uh pivot tables are kind of like read-only they're for visualizing the data so the data is here this is how you would edit the data and then the pivot table shows the results so going back to here we had all these sales let's um let's say we have a new sale let's say on 12 11 21 there's more oranges and to make sure that we can see it let's say they're going to be a thousand oranges sold huge sale now the table here is read-only it's pulling from this one and you actually have to update it so instead of taking out the processor time of constantly remaining up to date every time something's changed you actually select the pivot table and you click here and we're going to see oranges jump right we have 1051 sales of oranges 12 in the month of december if i refresh there we go it adds those thousand in it's not 2051 and all the stuff in the chart here are in the pivot table updates so yeah update the data here and then the pivot table will reflect that and remember to use this update button here so let's go into into this one for a bit so this is another sales table and this shows you when you have more complex data how you can use pivot tables in various ways so i'll go in here and i'll say organize and create pivot table and again on the current sheet which the button up here wouldn't allow me to do and then let's add some things let's say let's focus on sales people first so i'm going to focus on the sales person even though the sales person is the last column here i'm going to focus on the sales person and i'm going to say let's find out how much each sales person sold so i'll do a mount okay so now i've got i've got that here so that's uh you know some interesting data right there i can already tell who has the most sales who has the least sales but let's add another element into this in this chain of stores i have various stores they're five different stores the sales people rotate between them so let's go and add the stores to it i'll do that so now i've got the stores here across the top the sales people here and it's broken down and i can see for instance how many sales did mark make in the south store and then i've got the grand total for mark but also the grand total for the south store so at this point i can see that the west and central stores don't perform as well as the other stores and the south store performs the best so lots of information i can get out of this one table it's the same data here but it's easy to see this information here now let's say i wanted to add another element to this product let's look at the different products i'll select those now there are only two different dimensions there's only horizontal and vertical rows columns we don't have you know three-dimensional displays so we have to put this new thing the product underneath one of these so in this case it's under the sales person so i could see for amy all the different products and how she sold in each store i can also do it the other way i could take product and i could drag it under columns and now you can see the columns they're central and there's all of the products under central and there's central total then east and east total it probably looks a lot better if i were to click here and go swap column and row fields and now i could see central and i could easily collapse central collapse east i could actually do option click and it collapses them all and then i could expand one so i could examine the east store i could look at you know the sales for the sales people and for products there and see what's going on and get the grand totals i can see the east totals here and compare them to the grand total there so there's a lot that i could do here even beyond those two dimensions and then i could also let's go and take away the let's let's do store and sales person so i'll take away the product i'll select it here i'm just going to the delete key so i've got store and sales person i've got the grand total but what if i wanted the averages so what i would do here is go to values and amount and change to the average okay what if i want averages and the total i could do that because i can add multiple things in values here it's a little tricky to do you have to drag and drop but let's go and we already are using amount but i could drag them out two values and add a second amount so i have amount average and amount sum i can change that to whatever i want but sum is what i want so now i could see it here for each sales person which isn't really what i want so let's go in and for sales person i can or i'm sorry for amount average let's see it's in here somewhere there's a way to get rid of these amount average amounts no i guess not because i'm actually grouping these together so i'm thinking of something else so here i've got amount average and amount sum for each thing and i've got it over here on the right so now i can look at things like averages and sums let's we can go in here and make this much more readable by going to format and then no decimals so now we can see things like that so i could see for amy her average was 28 her total was 20 65 mark 29 that and i could look at each individual thing here and say okay for amy in the north store here's average and here's some so you're not restricted to just having one thing that's a value you can have multiple values in some ways it helps and sometimes it doesn't you may actually want to do what we um what i showed before i have multiple pivot tables have one pivot table literals averages one pivot table that shows sums so there's a lot of different things that you can do here we can let's go and get rid of the average go back to this we can do dates and we can have okay central store and we have all these dates here range by month let's go and change it so dates is first i'll drag it to the top and now we have january and then the stores here i can option click on that and now it looks like we just have the months and then the sales for each of the salesperson but i can easily expand to see the stores which is really handy you can actually go deeper than that i could add product to that as well so now i can do here i have the month then i have the store and then i have the product so you can go really deep in here and i can collapse all of those and i can decide whether there's a total here or not so for dates i can have no totals shown for store i could have no totals shown and then of course the last item here product doesn't have any totals because each one its own individual thing so i've eliminated those totals to make this a little bit easier to read here you can get really detailed and get really lost in the data if you want so there's a lot of cool stuff you can do let me check the check the chat here could you fit the table so let's see you can you can definitely shrink these columns here let's see if we select multiple here let's see i try to remember resize row to fit content yep so or resize columns in this case since i have column selected so we go to table like that and everything snaps into place so you definitely can and i can see that definitely being very useful for things also you know another thing i want to show let's go back to um actually i want to i'll just delete this one here and another useful thing that you can do with pivot tables is not have that extra dimension so let's create a pivot table on this sheet and what i'm going to do is i'm going to start i'm going to i'm going to dig down i want to do store first and then i'm going to do the sales person but i don't want salespeople in the columns i want them to be in the rows i'm going to drag to rows so now i've got every store with sales people under it let's get a value in there i'm going to do amount and it will put amounts here on the right so now i have this handy thing here where i've got okay total sales at the central store open that up now i have total sales for each sales person i could add product and it's going to try to put columns but maybe i don't want that maybe i want to put that under rows so now i've got this thing where i've got okay here's a store here's a sales person and here's for each product and the total for that sales person so this is kind of a linear thing it's like sorting everything and summing it up at the same time so i can easily just dig down to this and i could sort this as well like maybe i want to sort let's sort the store here this is by name ascending that's right so let's go and maybe have it sort instead by the total and descending so i see my top store here right and then it digs down and let's say i want to actually have sales people i want them sorted also by amount and descending so now when i dig down into any store i'm going to see the top sales person for that store and then of course i could actually sort the products too and see the top product for each salesperson so you end up with this long table that doesn't have the extra dimension the horizontal dimension of columns it just has this vertical dimension but it's still very useful it's a great way to dig down and look at the data and get sums get averages i could add another thing here i could add averages here let's do that this is a more useful thing you know thing to do with values i can add the values i'll add a new thing up here so i have two amount sums let's change the first one to average and then i've got that so i can uh let's format that better i'm going to just grab those and i'll do 0. let's also add another one and you might think what else could i possibly want to know i'm going to add another amount sum and i'm going to leave it at sum so here i have amount sum amount sum but i'm going to go in here and leave it at sum but show as percent i could show as percent of grand total column total row total lot of different things i'll just do percent of the grand total here so now i can see for each store this one's 23 west is 16 or almost 17 here and i see the numbers i see the averages for each sale um and i can you know expand a store i could option click here to close all those open this up see you know each sales person's thing i still have them sorted by sales person so i see the top sales person in each one i mean this is this isn't a way to just glance and you see everything or you print it out this is a way for you to analyze what's going on with your data and can cells be used in calculations for formulas yes i'm actually going to show that in the next example and i'm also let's uh let's go ahead and show charts let's do that first and then i'm going to show you my last example and i'm going to use formulas in there so let's get rid of this i created this extra pivot table let's say i have all of this the sales pivot here you know there's products there's months let's make a chart out of it i could of course you know do all sorts of things to make a chart out of this but this is more interesting so i'm going to select the entire table here and i'm going to insert a chart and i can create let's just create the simplest kind of chart here and now i see the data there oh yeah we added that huge sale for oranges let's delete that to make our table make much more sense delete row let's update this there we go so we're back to where we were before now we have this chart here from this pivot table so it's going to group our things together let's go and expand this so now we can actually see things and we can see you know there's january sales february sales marches you don't see apples bananas oranges this will automatically update with everything that goes on in this table here so we've got that you could create a table from other things um if you remember i let originally had this table just showing each sales person and their total sales that would make a great pie chart so you could easily create charts from the pivot table that are more useful than creating charts from the original table so let's take a look at this example here so this is something you might typically find people using you have a freelancer let's say they're a graphic artist it's a graphic designer and they have uh you know clients that come in okay they're going to record on this day they did some work for client a it was this much money there must be a pretty good graphic designer and this is how much they got paid so far right so every time they get paid they record it here great so we can create a pivot table from that to do all sorts of things let's go and with this selected i'll organize create a pivot table there and let's let's say clients first and then we'll use uh you know the amount charge so we can see who our biggest client is pretty easily this way whereas looking here it's going to be tough to do maybe we can change it to percentages right so let's go to this as percent of grand total and we can see who the biggest client is if we want i'll undo that we can also look at the price paid so let's we can add that in and it's going to add that it's smart enough to know to add that to the values here so we've got both the price total and the price paid so we have that and that and we can see that okay this is how much money we should have made and this is how much money actually came in because people haven't paid their invoices yet so what would be really cool is to be able to create an invoice from this now i've done a video in the past on creating invoices and you can do all sorts of cool tricks to have in one sheet your database like this and another one you have filters and things that create a nice invoice that show um you know exactly how much somebody owes you and everything and it's a lot of work you set it all up and it's great once you have it set up but it occurred to me that pivot tables can be actually be used for that it's not a normal use for pivot tables but let's look at actually doing that i'm going to delete this pivot table here and i'm actually going to use this button because i'm going to create a pivot table of this on a new sheet and let's say this is going to be for invoices so what we want first is client and then we want to have these two uh price and paid so this is what they owe us this is what they paid that's also a date here but instead of having date going across i want to group date with client like this so we have client a and we have all the dates here let's not have them group by month let's actually have values so we have all the dates of their invoices maybe and you know you send out your invoices with the date on them instead of an invoice number and you've got all of that now we don't need to have a total here so let's go and get rid of that let's say the client a total we'll click there and say total no so we have that now we want to create an invoice we don't want client a to see what all the other clients have paid us and what they owe us so we're going to create a filter because pivot tables have filters so i'm going to add a filter and say add per client and i'm going to deselect everything and only select client a so now i've got just client a i've got all the dates here and prices and what they've paid and look at that this is like kind of like an invoice it really is very close to what i did with a lot more steps to create an invoice for this client and yes you can perform functions using these cells let's create another table here that's going to have the total amount that they owe us so i'm going to create a simple table and shrink it down to one cell because i just need that one value and let's call it total owed or total do or whatever you want we can put this wherever we want and we're going to do a formula here and say this amount minus this amount so it's going to actually go and say yeah the work pivot table and c 42 d 42 and give us the total there so i can you know make that bold and you know bigger and do all that stuff you know put that down here you know create a text some text here you know invoice and put that at the top and put a box around it and format things and do all sorts of stuff you can even you know do things a little bit different here like you can't hide a column i don't think but you can go and do things like uh let's uh take this text here and i'm going to change the text color to you know figure out whatever the gray is that that gray is right there and you know i didn't quite get that same gray but you know you can kind of hide it like that and then you know collapse this down or something like that um and then create like a kind of a cute little invoice type of system here and the great thing is is that if i wanted to actually go and produce an invoice for another client all i would need to do is select the table go to organize change the filter so i've got this quick filter here client a change to client b and there we go change to client c and there we go maybe best to have this at the top or over to the right or something really easy way to actually create a filter i could also filter out all the times that they've paid so i could do add a filter and say for the paid column if the number is greater than 0 then get rid of it or actually if it's is equal to 0 it's probably better there there if it's equal to 0 then have it there so we have a total of all this i could even you know hide this column somehow or just leave it there that's probably what you want an invoice right to show that um so you could you know this is a creative use i thought of pivot tables people often ask me like i want to have a table that pulls data from this other table and tables don't pull data in general they have functions that can reflect the data in another table but they don't actually like pull data or you change make changes one table it doesn't push those changes you have functions in one table and those functions can actually reflect the data in another table so pivot tables are kind of go against that they build themselves based on in this case this so using pivot tables in this way it's kind of unorthodox probably there's probably some like excel experts watching this that are like no you don't use pivot tables for that but you can you can do that and i was able to demonstrate using a uh a formula there as well from these unfortunately you can't put formulas inside of pivot tables i mean they're basically like you know read-only there's nothing you can do in here i can't like add a column and uh you know go ahead and you know say oh give me the you know this minus this can't really do that so um you can't perform that kind of thing in here but uh you do have some ability to like perform that outside of pivot tables and you can't you know the other frustrating things is like i would love to change from grand total to something else grand total seems to be a pivot tables thing like you know i see that in other spreadsheets they call it grand total and all that still would be nice to customize that somehow if there was some way to do it so so anyway there's some examples you know what i'm gonna do i'm going to go and make sure that i save this and i'll upload this and include it with the post at macmost.com when i get that posted later today so you can download this if you want and check it out let's see what's going on in the chat so yeah so if you have any questions you can ask them the uh they're great new tool i would suggest uh playing around with them if you aren't familiar with pivot tables to see what they might do for you um certainly if you are familiar with pivot tables using them in excel or google sheets or something like that then you know it's like anything when you go across platforms this works differently than on those other apps so don't expect it to look the same work the same have exactly the same features and things you can you know use in the same way but certainly experiment i mean the way i figured out that you can have multiple values like this was by experimenting i thought there would be a way that i could click here and say add another value and then it just took me you know 10 seconds to figure out oh you can drag and drop things here and then have those included and that's kind of really cool that you know you could use that and have these multiple values so it took some experimentation to do that i didn't get that from just reading the few lines of documentation that apple provided here so it's going to be kind of the same situation here just taking your time to actually try things out and experiment with pivot tables to you know get them to do what you want and and you know help you visualize your data and definitely this is something that has uh been you know people been asking for this i get asked this all the time and in the past i've done videos on how to actually simulate pivot tables using categories in categories similar to pivot tables what it doesn't do is allow you to change how it looks this quickly i mean by being able to select deselect drag and drop and change how this table works categories didn't really have that categories you had to think a lot more about how to set things up you got them set up and then you're altering your original table to do that and then you know you would go back to have the original table looked and then if you wanted to go back to visualize your data you had to go and turn the categories back on and go and set things up the way you wanted pivot tables allow you to really quickly dig into your data so i could see why you know people that like to crunch numbers and like to examine things about all their data why they love them so so yeah so thanks for watching this live stream i hope you got something out of it ask if you're watching later certainly ask questions here in the comments on youtube or at macmost.com um so until next time thanks for watching [Music] you

Channel: macmostvideo

Views: 38,757

Keywords: Mac pivot tables, pivot tables, numbers pivot tables, Mac numbers pivot tables

Id: yj6BANGAyDQ

Length: 42min 45sec (2565 seconds)

Published: Fri Oct 08 2021