One of your best ever. I was so glad Apple added pivot tables, I used them a lot in Excel. Seems much easier in Numbers but you really showed how versatile it could be. Thanks!
I inherited a Pivot table from a coworker in Excel. I love it… but am too scared to try to invent one from scratch. I find your techniques here in Numbers much more intuitive. So I will begin to explore creating a Pivot table to track my e-bike riding… where I routinely document my rides… miles, climbing, # of battery used, effort level, etc. Would also like to make graphs and charts from it too. Thanks!
This was awesome. As a SQL programmer I hated pivot tables because I didn't do them ever. Now I see they are just fancy SQL statements. Thanks for an a terrific video - it all makes sense now. Pivot tables are just GROUP BY SQL
Although I’m going to have to watch this a few times at least to really wrap my head around how it works, this is extremely useful. thank you again very much.
This is great. Pivot tables is exactly what I needed…only I didn’t know that there was such a thing. Exactly what I need for my monthly spreadsheet so I can total my expenses in each category (food, fuel & etc)and deduct the amounts. Thank you 🙏 very much…great video as usual!
Terrific explanation, Gary. Thank you. I'm bad with formulae and functions ... so Pivot tables work very well for me. I've been using Excel but wanting to learn how to use Pivot tables in Numbers. Your video did the trick! I have tinkered with opening my Excel Pivot table in Numbers ... without extensive testing, it does seem to port over well. Thank you for another very useful tutorial!
I just discovered your pivot table video…amazing! I have been looking for a way to total my expenses for categories in my number’s monthly spreadsheet. Thanks so much! Another outstanding video…you’re the man!
Wow, this is exactly what I was looking for. Thank you, Gary! One question though before I dive into making Pivot Tables: Can you have multiple pivot tables, based on the same database information? Basically, I’m trying to create different “Views” (like AirTable) of the same data. Thanks in advance!
Gary, your videos are always so helpful. Thank You! I have 2 questions: 1- Can we sumarize a bunch of sheets like excel? 2- If have a formula to sum lines 1, 2 and 3 how can I ad a line 4 and the results include this new line? (I can't use the formula to sum the entire column, because I have another data on line 10, 11 and 12).
No, to summarize multiple tables you would first build the table that contains the data you want, presumably by calling on data in the original tables, and summarize that one table. I'm not sure what you are asking in the second question, but if you have rows in your table that are not data rows, then make them header or footer rows to be excluded from things like sums.
@@macmost For exemple: 1st line = 10.00 2nd line = 20.00 3rd line = 20.00 4th line TOTAL ONE = 50 (sum formula) If I add a line between 3rd and 4th the total doesn’t update.
@@carlodeluca But what you your sum formula, exactly? If this is column C, and there is a header row C1, and the values are C2-C4 with the sum formula in C5, is the formula SUM(C) or SUM(C2:C4)? Use the first one.
@@macmost the problem is: I have line 6, 7, 8 and a sum. Than I have 11, 12, 13 another sum. How can I have 3 subtotals that update when I insert adicional line at the botton of each one? Sorry for my english.
@@carlodeluca Try to keep your tables where the regular rows represent "records" in a database, and the footers or other tables are where you do calculations. It sounds like you are trying to cram more than one data set into a table. Use multiple tables or come up with another design for what you are trying to do. ua-cam.com/video/GHJ6z33CBpU/v-deo.html
Thank you very much! I was wondering if it is possible to make a table with groceries from different markets. And then showing/sorting easy way per groceries where cheapest is to buy. And yet better when you make a new sheet or table and you fill in the groceries you want, it shows the supermarket with lowest price. So that you can make a shopping list per vendor. Any ideas how to do that! 😀
YesMake a table, columns with stores and rows with products, then cells contain the price. Then in an extra column use LOOKUP and MIN to get the name of the store with the lowest price. For example if there are three products (columns B, C, D) then in column E on row 2 you would have LOOKUP(MIN(B3:D3),B3:D3,B$1:D$1)
Thanks for the very useful video. I often find it very difficult to create sample data. Did you setup your data by yourself or do you get this from an open source?
Another great video. I've been using numbers pivot tables for some time now as I migrate away from excel. Speaking of which, I really need the horizontal and vertical split supported by excel. Numbers headers provides the horizontal split, but how to do the vertical split ? - Thanks !!
@@macmost I confused you, sorry. when you showed curves, I thought of the feature I wish numbers had, that doesn't necessarily relate to pivot tables and that is splitting the table so that rows and columns could be scrolled by leaving the header row(s) and one or more column(s) intact. I now how to split horizontally by assigning one or more top rows as header, but I haven't figure out how to split vertically, by selecting one or more left columns, so that I can scroll across columns and leave the "title column(s)" static. I use these features in excel after creating a pivot table, when the number of ensuing rows and columns exceed the screen size. In excel it is under view and called split.
Excel of course also has freed top row and separately, but not at the same time, freeze first column. I want the split feature in numbers as the next step in work flow after pivot table
@@pedropuckerstein4670 Numbers doesn't have that. Numbers is fundamentally different than Excel as it has multiple tables on sheets. Excel is basically one big table on each tab. So freezing a table would be awkward. But you do have the ability to keep header rows and columns in place as you scroll.
@@macmost Yes, multiple tables per sheet, but your last few words stimulated me to research freezing of header rows and "header" columns - under the table tab - been searching for this feature for weeks ! Now to figure out how to write "macros" for numbers similar to excel - have you done a video on that ?
Good presentation and now feel comfortable in moving from excel to numbers for Pivot table. How can I change the field name in pivot table. eg: Cost(sum) to Cost or any other preferred name
I Absolutely loved this video. Can you do a pivot table based on more than one table? I used your video to set up my checkbook register but want to do a table for each month of the year. At the end of the year i would like to do a pivot table based on all 12 tables. Is that possible?
No, you'd need to create a table with the data you want from the 12 other tables first. BTW, I wouldn't do a table for each month. Why? Keep similar data in one table. Just have a single table for all checkbook entries.
@@macmost Thanks, that is the way I set it up so i should be good to go. I have been a Mac user for 8 years now, just upgrading from a iMac 2015 to the Mac Mini M2 Pro. Your videos have renewed my interest in new ways to use my computer. Thank you very much for all you do for the Mac community.
Thanks for this interesting video. Would you know how I can change the data source (like Excel allows) ? I can't find anything else than creating a new Pivot Table. I'm trying to use numbers for personal stuff while using Excel for work; however the more I try the more I find limits in Numbers when I need to do "advanced" calculations. Thank you Gary 🙂
A pivot table is tied to a regular table. So yes, you would create another pivot table to do something with another table. Or, change the data in the original table.
Great stuff . You’ve been such a help. I have a ? I have a sheet with 5 tables on it. Can you create a pivot table for multiple tables to one pivot table. Also when I create a pivot table, it is alphabetizing the original and I have it in a certain order for a reason. Thoughts? Thanks in advance
No. Pivot tables draw their data from a single table. Do these tables contain the same type of data records on each row? If so, combine them into one table. Not sure why it is sorting the original table, I've never seen that. Do you have a Sort set up on that table?
@macmostvideo ok I will combine the tables. They are inventory tables for my restaurant. I have it set up as product/quantity needed/in stock/order as the 4 columns with formulas I learned from you for simple subtraction. That way anyone can do inventory and shoot it to a pivot table that has just product and order quantity. But for some reason it sorts it on the pivot table which is a pain because I have the list in the order I need.
Hello. Thanks for the videos, they are really good! I have an Excel file and I want to convert it to a numbers file. The file has many complicated functions. can you help me with this Thank you very much
Just open it up in Numbers. That converts it. If it uses something that Excel has that Numbers does not, then you'll need to come up with a new method of doing that.
@@macmost thanks for the reply. Can I email you the file? See what I'm talking about. Because it's not work for me. I don't understand it too much. I am willing to pay if necessary. Thank you.
The main inconvenience of pivot table for me is manual update. I hope in the next update they will add automatic update option, so currently I still use formula-based approach.
What's the problem with having to click on button to have it update? (Trying to understand your point of view). I mean if the table changes, presumably you have taken many actions to change it, adding values, changing values, etc. So what's wrong with exactly one more click?
Do you know if I can create calculated fields in numbers like in excel? I mean, creating a field that is the calculation of 2 (or more) fields of the pivot table
Not sure what you are experiencing there. Just change it after, I suppose. Or experiment with different paste options in the Edit menu (like hold down the Option key in the Edit menu).
Gary obviously knows his topic, but his lessons move a bit too fast for me. I taught before retiring and I think most students might have trouble following along. LJusdt a thought.
You can… in a way, by sorting a database. But you really can’t get them to group items in this way at all. You’d have to use formulas for adding up values, for example. I’m using it for budgeting reasons, putting info in a database and pivot table to see what I spent each month. Much easier in my opinion. Click a few times and the table is made.
One of your best ever. I was so glad Apple added pivot tables, I used them a lot in Excel. Seems much easier in Numbers but you really showed how versatile it could be. Thanks!
Gary has totally got a gift for teaching.
Where were you, Gary, and all this great info, 22 years ago when I was still working?
I inherited a Pivot table from a coworker in Excel. I love it… but am too scared to try to invent one from scratch. I find your techniques here in Numbers much more intuitive. So I will begin to explore creating a Pivot table to track my e-bike riding… where I routinely document my rides… miles, climbing, # of battery used, effort level, etc. Would also like to make graphs and charts from it too. Thanks!
This was awesome. As a SQL programmer I hated pivot tables because I didn't do them ever. Now I see they are just fancy SQL statements. Thanks for an a terrific video - it all makes sense now. Pivot tables are just GROUP BY SQL
Although I’m going to have to watch this a few times at least to really wrap my head around how it works, this is extremely useful. thank you again very much.
Thank you Gary! I never realized the value of Pivot Tables until now
This is great. Pivot tables is exactly what I needed…only I didn’t know that there was such a thing. Exactly what I need for my monthly spreadsheet so I can total my expenses in each category (food, fuel & etc)and deduct the amounts. Thank you 🙏 very much…great video as usual!
Terrific explanation, Gary. Thank you. I'm bad with formulae and functions ... so Pivot tables work very well for me. I've been using Excel but wanting to learn how to use Pivot tables in Numbers. Your video did the trick!
I have tinkered with opening my Excel Pivot table in Numbers ... without extensive testing, it does seem to port over well.
Thank you for another very useful tutorial!
I just discovered your pivot table video…amazing! I have been looking for a way to total my expenses for categories in my number’s monthly spreadsheet. Thanks so much! Another outstanding video…you’re the man!
Wowwwww such a great video!! You really helped me complete my survey task for university. Please keep doing more videos. :)
Best. Video. Ever! Thanks, Gary!!!
This is exactly what I needed right now!!!
Thank you, great help.
Fantastic! Thank you.
Yet another very useful video, Gary! I've used pivot tables before, but I didn't know even half the tricks you showed in this tutorial. Thanks!
What a wonderfully concise video. Thank you!
Nice- Like the charting of the pivot!
Thanks very much, Gary, for this immensely useful tutorial!
Thanks brother, you are so easy to follow... 🙏❤🌅🌈
Excellent video - thank you
That was incredibly useful and interesting. Thanks!
Really a good tutorial. Thanks
This was great! Thank you!
Great video, Gary. As always.
Great video, thanks!
Deserves a like! Thanks!
Wow, this is exactly what I was looking for. Thank you, Gary! One question though before I dive into making Pivot Tables: Can you have multiple pivot tables, based on the same database information? Basically, I’m trying to create different “Views” (like AirTable) of the same data. Thanks in advance!
Sure, but typically you have one and keep making changes to examine the results. Either way though. Try it and see.
Thank you!!!
Excellent thanks Gary
Hopefully apple will decide at some point to allow highlight colors which really is almost as useful as the pivot tables
Highlight colors? Do you mean Conditional Highlighting? That's always been a part of Numbers. ua-cam.com/video/m5QkGYvfYRk/v-deo.html
A very useful and informative video tutorial today! Thank you, Gary! 👏❤️
Thanks bunches
Gary, your videos are always so helpful. Thank You!
I have 2 questions:
1- Can we sumarize a bunch of sheets like excel?
2- If have a formula to sum lines 1, 2 and 3 how can I ad a line 4 and the results include this new line? (I can't use the formula to sum the entire column, because I have another data on line 10, 11 and 12).
No, to summarize multiple tables you would first build the table that contains the data you want, presumably by calling on data in the original tables, and summarize that one table. I'm not sure what you are asking in the second question, but if you have rows in your table that are not data rows, then make them header or footer rows to be excluded from things like sums.
@@macmost For exemple:
1st line = 10.00
2nd line = 20.00
3rd line = 20.00
4th line TOTAL ONE = 50 (sum formula)
If I add a line between 3rd and 4th the total doesn’t update.
@@carlodeluca But what you your sum formula, exactly? If this is column C, and there is a header row C1, and the values are C2-C4 with the sum formula in C5, is the formula SUM(C) or SUM(C2:C4)? Use the first one.
@@macmost the problem is:
I have line 6, 7, 8 and a sum. Than I have 11, 12, 13 another sum. How can I have 3 subtotals that update when I insert adicional line at the botton of each one? Sorry for my english.
@@carlodeluca Try to keep your tables where the regular rows represent "records" in a database, and the footers or other tables are where you do calculations. It sounds like you are trying to cram more than one data set into a table. Use multiple tables or come up with another design for what you are trying to do. ua-cam.com/video/GHJ6z33CBpU/v-deo.html
Thank you very much! I was wondering if it is possible to make a table with groceries from different markets. And then showing/sorting easy way per groceries where cheapest is to buy. And yet better when you make a new sheet or table and you fill in the groceries you want, it shows the supermarket with lowest price. So that you can make a shopping list per vendor. Any ideas how to do that! 😀
YesMake a table, columns with stores and rows with products, then cells contain the price. Then in an extra column use LOOKUP and MIN to get the name of the store with the lowest price. For example if there are three products (columns B, C, D) then in column E on row 2 you would have LOOKUP(MIN(B3:D3),B3:D3,B$1:D$1)
Thanks for the very useful video. I often find it very difficult to create sample data. Did you setup your data by yourself or do you get this from an open source?
Some open source, but it was too long ago for me to remember. Sometimes I use random numbers: ua-cam.com/video/jFZelHcs428/v-deo.html
Another great video. I've been using numbers pivot tables for some time now as I migrate away from excel. Speaking of which, I really need the horizontal and vertical split supported by excel. Numbers headers provides the horizontal split, but how to do the vertical split ? - Thanks !!
Not sure what you mean. Keep working with the Pivot Tables feature and hopefully you'll see how to get what you need.
@@macmost I confused you, sorry. when you showed curves, I thought of the feature I wish numbers had, that doesn't necessarily relate to pivot tables and that is splitting the table so that rows and columns could be scrolled by leaving the header row(s) and one or more column(s) intact. I now how to split horizontally by assigning one or more top rows as header, but I haven't figure out how to split vertically, by selecting one or more left columns, so that I can scroll across columns and leave the "title column(s)" static. I use these features in excel after creating a pivot table, when the number of ensuing rows and columns exceed the screen size. In excel it is under view and called split.
Excel of course also has freed top row and separately, but not at the same time, freeze first column. I want the split feature in numbers as the next step in work flow after pivot table
@@pedropuckerstein4670 Numbers doesn't have that. Numbers is fundamentally different than Excel as it has multiple tables on sheets. Excel is basically one big table on each tab. So freezing a table would be awkward. But you do have the ability to keep header rows and columns in place as you scroll.
@@macmost Yes, multiple tables per sheet, but your last few words stimulated me to research freezing of header rows and "header" columns - under the table tab - been searching for this feature for weeks ! Now to figure out how to write "macros" for numbers similar to excel - have you done a video on that ?
Good presentation and now feel comfortable in moving from excel to numbers for Pivot table. How can I change the field name in pivot table. eg: Cost(sum) to Cost or any other preferred name
I don't think you can. You'd need to change the name in the original table.
I Absolutely loved this video. Can you do a pivot table based on more than one table? I used your video to set up my checkbook register but want to do a table for each month of the year. At the end of the year i would like to do a pivot table based on all 12 tables. Is that possible?
No, you'd need to create a table with the data you want from the 12 other tables first. BTW, I wouldn't do a table for each month. Why? Keep similar data in one table. Just have a single table for all checkbook entries.
@@macmost Thanks, that is the way I set it up so i should be good to go. I have been a Mac user for 8 years now, just upgrading from a iMac 2015 to the Mac Mini M2 Pro. Your videos have renewed my interest in new ways to use my computer. Thank you very much for all you do for the Mac community.
Thanks for this interesting video. Would you know how I can change the data source (like Excel allows) ? I can't find anything else than creating a new Pivot Table. I'm trying to use numbers for personal stuff while using Excel for work; however the more I try the more I find limits in Numbers when I need to do "advanced" calculations. Thank you Gary 🙂
A pivot table is tied to a regular table. So yes, you would create another pivot table to do something with another table. Or, change the data in the original table.
Great stuff . You’ve been such a help. I have a ? I have a sheet with 5 tables on it. Can you create a pivot table for multiple tables to one pivot table. Also when I create a pivot table, it is alphabetizing the original and I have it in a certain order for a reason. Thoughts?
Thanks in advance
No. Pivot tables draw their data from a single table. Do these tables contain the same type of data records on each row? If so, combine them into one table. Not sure why it is sorting the original table, I've never seen that. Do you have a Sort set up on that table?
@macmostvideo ok I will combine the tables. They are inventory tables for my restaurant. I have it set up as product/quantity needed/in stock/order as the 4 columns with formulas I learned from you for simple subtraction. That way anyone can do inventory and shoot it to a pivot table that has just product and order quantity. But for some reason it sorts it on the pivot table which is a pain because I have the list in the order I need.
Hello. Thanks for the videos, they are really good!
I have an Excel file and I want to convert it to a numbers file.
The file has many complicated functions. can you help me with this
Thank you very much
Just open it up in Numbers. That converts it. If it uses something that Excel has that Numbers does not, then you'll need to come up with a new method of doing that.
@@macmost thanks for the reply.
Can I email you the file?
See what I'm talking about. Because it's not work for me. I don't understand it too much.
I am willing to pay if necessary.
Thank you.
@@Y-sport Sorry, I don't do consulting or anything like that
The main inconvenience of pivot table for me is manual update. I hope in the next update they will add automatic update option, so currently I still use formula-based approach.
What's the problem with having to click on button to have it update? (Trying to understand your point of view). I mean if the table changes, presumably you have taken many actions to change it, adding values, changing values, etc. So what's wrong with exactly one more click?
Is it possible to incorporate data from other sheets in your pivot table?
Yes, as long as you built a single table with that data first then create the pivot table from that single table.
Do you know if I can create calculated fields in numbers like in excel? I mean, creating a field that is the calculation of 2 (or more) fields of the pivot table
I'm not familiar with that Excel feature so I'm afraid I can't be of much help.
Oops I see I already commented once before😳
Can you create a pivot table that pulls info from multiple files and sheets?
No. Not sure of your situation, but if your data records are for the same kind of data, they should be in one table.
How could I use this on multiple sheets lets say 100 sheets?
when i copy and paste data from pivot table than font, text alignment, cell border etc changes what shall I do
Not sure what you are experiencing there. Just change it after, I suppose. Or experiment with different paste options in the Edit menu (like hold down the Option key in the Edit menu).
How do you pivot tables across sheets?
Not sure what you mean by "across sheets." By default a pivot table is created on a separate sheet from the source table.
Welp, time to organize my board game collection.
Good video but way too fast starting at the produce store... No time to think, just click, click, click. But useful, just the same. Thanks.
Gary obviously knows his topic, but his lessons move a bit too fast for me. I taught before retiring and I think most students might have trouble following along. LJusdt a thought.
ua-cam.com/video/liZTyiy9NnU/v-deo.html
I don’t. Perfect speed for me 😊
You can do all this without pivot tables. I think pivot tables just complicate the situation.
You can… in a way, by sorting a database. But you really can’t get them to group items in this way at all. You’d have to use formulas for adding up values, for example. I’m using it for budgeting reasons, putting info in a database and pivot table to see what I spent each month. Much easier in my opinion. Click a few times and the table is made.
Really handy video and good functionality
Thanks for this great video.