Google Sheets - Pivot Tables Tutorial - includes Grouping by Date using Year, Month Functions
Vložit
- čas přidán 18. 05. 2017
- Video tutorial Pivot Tables in Google Sheets. You will learn how to analyze data using Pivot Tables in Google Sheets using different functions, group years and months and more.
Google Sheets
www.google.com/sheets/about/
Website:
www.chicagocomputerclasses.com/
Things to Learn in this video:
F4 to repeat the last action.
SUM Function
SUMIFS Functions
F4 inside the formula to lock the range selection.
Things in SQUARE Brackets are optional
Using Format painter to copy formats from one cell to others.
YEAR Function
MONTH Function
TEXT Function (convert numerical Months to names)
:: PIVOT Tips::
No blank roles.
What to put in Values, Rows, Columns and filter.
More options go in Rows and lesser options go in Columns.
We can drag and drop from ROWS to COLUMNS directly, in the configuration.
Getting SUM, AVG, Count
My mind is completely blown by this tutorial. I had no idea all of these shortcuts existed for Sheets. You are a God!!
I feel grateful for your teaching efforts. I’m really enjoying the process of learning more about Google Sheets and this video was very helpful. Thank you!
Thank you, THANK YOU, for this video. Pivot tables were among those I found intimidating when I first learned Sheets, but you explained it so simply.
Excellent explanation of PIVOT tables. Your delivery was clearly communicated!!! Thanks so much!
Wow this is great. You have GREAT teaching abilities. Thanks a lot!
You've done such a great job in structuring your traching here to walk us through the concepts step by step. Thank you!
Glad to hear!
This is, and other videos from you, are a speed course to knowledge. Super well done with my thanks and appreciation.
very concise and clear. Keep up the good work. I will send anyone looking for a good tute here.
have browsed other video but did not get the clear idea of the function of pivot table. This video is very helpful. I now understand completely what pivot table is. Thank you so much!
Bro! You are the MAN! I feel unstoppable now!
You sir are a gentleman and a scholar. Thank you.
You have really simplified the learning experience. Thank you.
Excellent. You have taken me out of the fog regarding pivot tables.
Your explanation made this easy to learn. So helpful! Thank you so much!
Thank you for this video. This helped me so much.
So helpful, great explanation of what pivot tables are for, thank you ♥
Very clear and patient!
This is so helpful. Knowing the value needs to be added first saved a lot of headache. Thank you so much ❤️
👍
I Love your teaching style. Thanks! for your Work . Looking for more interesting Videos
Love this channel!
Incredibly helpful!
You are the best teacher! Thank you!!!🙌
Thank you for this. Would anyone know if there is a way to sort data with merged fields in Google Sheets? I see pivot tables as the only alternative to viewing multiple duplicates on rows and merging their data.
After using spreadsheets on rare occasions for over20 years, I just learned that you can double click the expand square to auto copy the formula down the entire column. Wooooooooowwwww.
what do u mean? please enlighten me.
Simply thank you.
Is there a function in Google Sheets similar to the one in MS Excel that changes the Report Layout of a Pivot Table to a Compact view?
Awesome video, thanks for the help!
It is very amazing tutorial and this is very helpful tutorial.
Thanks a lot of Sir
Excellent!. Thank you.
This is very helpful! Although I don't have the datasets, I'm using a fiture called randomized range and a function called RANDBETWEEN to make a similar datasets to yours! Thanks a lot, you're such a great teacher! Keep up the great work:)
@sofus Axelsen and for anyone who may come across this comment in the future, this video is a part of the playlist titled Google Sheets Intermediate Tutorials. Find the first video in the playlist. When you click show more under the video, there is a link that allows you to download the dataset used in the video.
Thanks for the help. Its very helpful for us. That's great 👍
Well done, thanks!
Excellent vid. Quick and easy to digest
Glad you liked it
is it possible to have the pivot table split up into to tables ? One Table exclusively for database and an evaluation table ?
Really great tutorial. thank you!
Thank you this was great ! if I want to out the sales peoples name when using the filter would I add Sales_REP in the column section of the pivot table?
Great job, number you videos !
Hello there,
Great Tutorial, I need this data set you are using for your example, can have it for my practic?
Regards!
Very nice video !! Great work !!
Great! As usual. Thanks
Thank You. This is so useful :)
really made my concepts clear
Great Tutorial !
Wow, amazing function!
Learnt so much on the shortcuts and it’s really explanatory. I just subscribed to your Chanel
Excellent job!
Thank you very much
f4 to repeat last action!! Did not know about this one! Rest of video good too...
What a great tutorial, thanks!
Glad you enjoyed it!
Do you have a sample data so we can practice it at the same time?
Thank you this was so helpful 😊😊😊
Thank you!
If it weren’t u Idk how I’m gonna learn this tysm 😭
Can you tell me your method for collecting all of your data? I am assuming you did not manually input a bunch of random dates and sales and it would be really helpful to understand how you kept track of each reps sales. I am making a similar spreadsheet to track pay period and project hours and don't know how to keep all of the data per project after the pay period ends. Thanks for any advice!
Hi, you might find some examples from the web. An example that I am using is "Sample - Superstore Sales (Excel).xls" downloaded from community.tableau.com/docs/DOC-1236
Informative video
Well explained
Thank you
Glad it was helpful!
Great video. Thank you.
:)
FYI, if you select the number of rows you want to add from the existing rows, when you right click it will ask if you want to add that number of rows instead of just one.
True!
Love you!
Thank you buddy
Thank you.
The quality of the content is good, but if you're looking for the bit on pivot tables, which is what's featured in the video title, you'll want to skip the first 15 minutes, which is an explanation of SUM and SUMIF suitable for people with very little computer experience, which is great, but likely an unwise use of time for someone that could glean the same information from a 30 second skim of a support page. If you're comfortable with both Google Sheets and English, you'll likely benefit from using x2 playback on this one.
What about if I want to see "Month Name" instead of "Month" in pivot table? OK, it is easy. BUT, what about sorting the "Month Name" in pivot table, such Jan, Feb, Mar???
Try using this formula instead: =text(A2,"MM-MMM") it will give you this format: 07-Jul
SO this is a great video. SO you taught me a lot about pivot tables. SO I will be able to use this a lot. SO there was just one thing wrong. SO I won't get into that right now. SO please keep up the good work. SO thanks again!
many thanks
Hi,
Is it possible to create a pivottabel within the same sheet as my datatable, as it is with excel?
Br
Anders
I don't think so.
Good video. Thank you. Qluestion: is there a way to move the Grand Total column to the left? (So that it becomes the 2nd column instead of the right-most column)
great tutorial. Now gsheet has a group date function. Just right click on any date cell on pivot table, then select "Create pivot date group"
HI! Great Video! Is this sheet available for practice? Where can I find it? Thanks!
you are a genius !!!
Thank You!
Can you pivot after group and order? I have data that I've got queried up (anonymized below), it's messy, but it's working (CR are salespeople, CP product names, CT is whether those products were sold or not) I'm trying to display the Salesperson name in pivoted columns that count up the products they sold:
=query(TempDataSet!CI:CY, "SELECT CR, Count(CR) WHERE (CP='Condition 1a' OR CP='Condition 1b' OR CP='Condition 1c' OR CP='Condition 1e') AND CT='2a' GROUP BY CR ORDER BY Count(CR) desc")
BUT, when I add Pivot to display the counts of the conditions better;
=query(TempDataSet!CI:CY, "SELECT CR, Count(CR) WHERE (CP='Condition 1a' OR CP='Condition 1b' OR CP='Condition 1c' OR CP='Condition 1e') AND CT='2a' GROUP BY CR ORDER BY Count(CR) desc pivot CP")
I get a #VALUE stating
Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "pivot" "pivot "" at line 1, column 257. Was expecting one of: "skipping" ... "limit" ... "offset" ... "label" ... "format" ... "options" ... "," ...
any advice/help would be appreciated!
Is there anyway that we can get this data for practice purpose?
Hi,
Thank you for the video. Would you please assist me here. I have a situation where I wanted to subtract the respective row value from grand total in a pivot by using calculated field. Is it possible?
Depends, if it's a simple table with just one grand total then you can do it by including an external calculations, You can see an example of this type of calculations here czcams.com/video/kRmHqE5vDlo/video.html
Thanks for such a useful tutorial. I was learning QUERY function before I knew of pivot tables. Are there major advantages in using QUERY over just pivoting tables?
Whichever gets the job done. There is no advantage or disadvantage.
QUERY can handle blank rows with a “SELECT and WHERE” function while PIVOT tables requires data without blank rows.
Nice video
can I have the copy of the above sample data. i want use for my practice .
no sample sheet that we can also work on?
What kind of sorcery is this? Thank you!
Please send me that sheet for training... thanks
I learnt more in 35 mins than I did in the last few hours
👍
can I have the data example? so I can practices
from where to get this data ?
20:06 Dates function tutorial starts
🙏🏻
👍
Can you give link of data please
How to get data by exact day?
f4 doesnt work for windows please suggest alternative
might be fn+F4 for your computer
Thanks:)
Why was so much time spent on Sum, SumIf? Does that have anything to do with pivot tables?
UPDATE _ GOOGLE HAS NOW ADDED GROUP BY MONTH etc in its DATE functionality in PIVOT TABLES - so this "Month Name" part is now THANKFULLY REDUNDANT!
So question - you went through getting the month name which is great and exaclty what I wanted (can't believe Google can't include this grouping functionality! ..anyway..) however in your table you didn't use it at all, just the month numbers.
I've tried using it, and unfornutaly it will only sort them by ascending or descending - which jumbles all the months! Useless. How do you get the month names in order in the pivot table, plEASe!
can anyone of u guys give me a free editable budget or sales template please?
I have an issue that query function returns wrong date in numbers
Try this czcams.com/video/jWjP71xuYQE/video.html
Nearly 36 mins long?? The first 1m 6s is spent explaing what I can read on the screen. Brevity is the success of good You tube tutorials.
One solution is required. I have to consolidate the data from 18 persons. please guide me how to consolidate from 18 persons, without editing by the other person's data. Please
too bad I cant get sample data. rather than entering the data for the exercises. I love to Attempt to follow along. I am sorry. I cannot afford the classes. I am self teaching . I do use google sheets for work though
Hi, share file
Nice, but it wont be easy to memorize everything on here.
Right which why he should have let us copy his sheet then we could have worked along with him
Why is it called a "pivot" table?
Frankly, I don't think it's a great name. But the idea is you can pivot your data around values.
I don't even know what a pivot table is or what it is for. Ironically, It seems like to missed the most importaint detail of the tutorial.
I have to admit, I'm not good with intros.
Sterling video