No video
Learn How To Use Pivot Tables In Numbers By Example
macmost.com/e-... If Pivot Tables in Numbers seem intimidating, watch this tutorial to see how easy they are to use. You can get a lot of information without ever needing to using functions or formulas.
FOLLOW MACMOST FOR NEW TUTORIALS EVERY DAY
▶︎ KZread - macmost.com/j-y... (And hit the 🔔)
▶︎ KZread Email Notifications - macmost.com/ytn ⭐️
▶︎ MacMost Weekly Email Newsletter - macmost.com/ne... ✉️
SUPPORT MACMOST AT PATREON
▶︎ macmost.com/pa... ❤️
00:00 Intro
00:33 Example 1: Book Collection
01:54 Tip: Have a Clean Data Table
07:06 Example 2: Business Expenses
10:14 Example 3: Sales Tracking Data
13:57 Tip: Click To Update Pivot Table
14:12 Tip: Format Date Columns
14:45 No Formulas Required!
#macmost #mactutorial #pivottables
Пікірлер: 86
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!
@adco
Жыл бұрын
Gary has totally got a gift for teaching.
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.
Where were you, Gary, and all this great info, 22 years ago when I was still working?
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!
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!
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!
Fantastic! Thank you.
Thank you Gary! I never realized the value of Pivot Tables until now
A very useful and informative video tutorial today! Thank you, Gary! 👏❤️
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!
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!!!
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 is exactly what I needed right now!!!
What a wonderfully concise video. Thank you!
Thanks very much, Gary, for this immensely useful tutorial!
Nice- Like the charting of the pivot!
Great video, Gary. As always.
This was great! Thank you!
That was incredibly useful and interesting. Thanks!
Deserves a like! Thanks!
Thanks brother, you are so easy to follow... 🙏❤🌅🌈
Really a good tutorial. Thanks
Really handy video and good functionality
Excellent video - thank you
Great video, thanks!
Thanks for this great video.
Thank you!!!
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!
@macmost
6 ай бұрын
Sure, but typically you have one and keep making changes to examine the results. Either way though. Try it and see.
Thanks bunches
Excellent thanks Gary Hopefully apple will decide at some point to allow highlight colors which really is almost as useful as the pivot tables
@macmost
2 жыл бұрын
Highlight colors? Do you mean Conditional Highlighting? That's always been a part of Numbers. kzread.info/dash/bejne/n2mFzamSpsm_gs4.html
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).
@macmost
2 ай бұрын
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.
@carlodeluca
2 ай бұрын
@@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.
@macmost
2 ай бұрын
@@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.
@carlodeluca
2 ай бұрын
@@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.
@macmost
2 ай бұрын
@@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. kzread.info/dash/bejne/eXx-mNxsY6aooLg.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! 😀
@macmost
Жыл бұрын
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)
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
@macmost
10 ай бұрын
I don't think you can. You'd need to change the name in the original table.
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.
@macmost
Жыл бұрын
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?
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?
@macmost
Жыл бұрын
Some open source, but it was too long ago for me to remember. Sometimes I use random numbers: kzread.info/dash/bejne/nHqOx86Bk9aaYps.html
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?
@macmost
Жыл бұрын
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.
@MrHitthespot
Жыл бұрын
@@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.
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
@macmost
Жыл бұрын
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.
@Y-sport
Жыл бұрын
@@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.
@macmost
Жыл бұрын
@@Y-sport Sorry, I don't do consulting or anything like that
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
2 жыл бұрын
Not sure what you mean. Keep working with the Pivot Tables feature and hopefully you'll see how to get what you need.
@pedropuckerstein4670
Жыл бұрын
@@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.
@pedropuckerstein4670
Жыл бұрын
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
@macmost
Жыл бұрын
@@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.
@pedropuckerstein4670
Жыл бұрын
@@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 ?
Is it possible to incorporate data from other sheets in your pivot table?
@macmost
10 ай бұрын
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
@macmost
Жыл бұрын
I'm not familiar with that Excel feature so I'm afraid I can't be of much help.
How could I use this on multiple sheets lets say 100 sheets?
Can you create a pivot table that pulls info from multiple files and sheets?
@macmost
3 ай бұрын
No. Not sure of your situation, but if your data records are for the same kind of data, they should be in one table.
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 🙂
@macmost
Жыл бұрын
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.
when i copy and paste data from pivot table than font, text alignment, cell border etc changes what shall I do
@macmost
Жыл бұрын
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).
Oops I see I already commented once before😳
How do you pivot tables across sheets?
@macmost
11 ай бұрын
Not sure what you mean by "across sheets." By default a pivot table is created on a separate sheet from the source 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
@macmost
Жыл бұрын
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?
@d_rose_a
Жыл бұрын
@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.
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.
@macmost
7 ай бұрын
kzread.info/dash/bejne/np2OttuiqZy0nrg.html