Google Sheets - Pivot Tables Tutorial - includes Grouping by Date using Year, Month Functions
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/
Пікірлер: 129
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.
This is, and other videos from you, are a speed course to knowledge. Super well done with my thanks and appreciation.
Excellent explanation of PIVOT tables. Your delivery was clearly communicated!!! Thanks so much!
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!
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.
@animate9612
8 ай бұрын
what do u mean? please enlighten me.
You've done such a great job in structuring your traching here to walk us through the concepts step by step. Thank you!
@ExcelGoogleSheets
2 жыл бұрын
Glad to hear!
Wow this is great. You have GREAT teaching abilities. Thanks a lot!
Bro! You are the MAN! I feel unstoppable now!
You sir are a gentleman and a scholar. Thank you.
very concise and clear. Keep up the good work. I will send anyone looking for a good tute here.
Very clear and patient!
Incredibly helpful!
Love this channel!
Thank you for this video. This helped me so much.
You have really simplified the learning experience. Thank you.
Excellent. You have taken me out of the fog regarding pivot tables.
Excellent vid. Quick and easy to digest
@ExcelGoogleSheets
Ай бұрын
Glad you liked it
Your explanation made this easy to learn. So helpful! Thank you so much!
You are the best teacher! Thank you!!!🙌
So helpful, great explanation of what pivot tables are for, thank you ♥
Awesome video, thanks for the help!
Simply thank you.
This is so helpful. Knowing the value needs to be added first saved a lot of headache. Thank you so much ❤️
@ExcelGoogleSheets
Жыл бұрын
👍
Well done, thanks!
Excellent job!
f4 to repeat last action!! Did not know about this one! Rest of video good too...
Great job, number you videos !
Very nice video !! Great work !!
Wow, amazing function!
Great Tutorial !
Excellent!. Thank you.
I Love your teaching style. Thanks! for your Work . Looking for more interesting Videos
Thank You. This is so useful :)
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:)
@pon00050
Жыл бұрын
@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.
Thank you very much
Great! As usual. Thanks
Learnt so much on the shortcuts and it’s really explanatory. I just subscribed to your Chanel
Really great tutorial. thank you!
Love you!
What a great tutorial, thanks!
@ExcelGoogleSheets
4 жыл бұрын
Glad you enjoyed it!
really made my concepts clear
Thanks for the help. Its very helpful for us. That's great 👍
It is very amazing tutorial and this is very helpful tutorial. Thanks a lot of Sir
Hello there, Great Tutorial, I need this data set you are using for your example, can have it for my practic? Regards!
Thank you this was so helpful 😊😊😊
Thank you.
Thank you!
Informative video Well explained Thank you
@ExcelGoogleSheets
3 жыл бұрын
Glad it was helpful!
If it weren’t u Idk how I’m gonna learn this tysm 😭
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.
Thank you buddy
Great video. Thank you.
@ExcelGoogleSheets
2 жыл бұрын
:)
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.
@ExcelGoogleSheets
6 жыл бұрын
True!
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?
you are a genius !!!
@ExcelGoogleSheets
Жыл бұрын
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?
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.
I learnt more in 35 mins than I did in the last few hours
@ExcelGoogleSheets
Жыл бұрын
👍
many thanks
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"
is it possible to have the pivot table split up into to tables ? One Table exclusively for database and an evaluation table ?
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!
Do you have a sample data so we can practice it at the same time?
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!
@samyip3868
6 жыл бұрын
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
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)
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?
@ExcelGoogleSheets
4 жыл бұрын
Whichever gets the job done. There is no advantage or disadvantage.
@rajeshr8887
2 жыл бұрын
QUERY can handle blank rows with a “SELECT and WHERE” function while PIVOT tables requires data without blank rows.
Nice video
HI! Great Video! Is this sheet available for practice? Where can I find it? Thanks!
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???
@toyclyde
6 жыл бұрын
Try using this formula instead: =text(A2,"MM-MMM") it will give you this format: 07-Jul
Hi, Is it possible to create a pivottabel within the same sheet as my datatable, as it is with excel? Br Anders
@ExcelGoogleSheets
6 жыл бұрын
I don't think so.
🙏🏻
Please send me that sheet for training... thanks
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?
@ExcelGoogleSheets
2 жыл бұрын
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 kzread.info/dash/bejne/nYahqtN-ZdmqnNI.html
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?
What kind of sorcery is this? Thank you!
can I have the data example? so I can practices
no sample sheet that we can also work on?
👍
can I have the copy of the above sample data. i want use for my practice .
How to get data by exact day?
from where to get this data ?
20:06 Dates function tutorial starts
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!
f4 doesnt work for windows please suggest alternative
@ExcelGoogleSheets
6 жыл бұрын
might be fn+F4 for your computer
@minalmittal687
6 жыл бұрын
Thanks:)
Can you give link of data 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
@ExcelGoogleSheets
2 жыл бұрын
Try this kzread.info/dash/bejne/nIuesplqqNi_gag.html
Nice, but it wont be easy to memorize everything on here.
@ehogshire
6 жыл бұрын
Right which why he should have let us copy his sheet then we could have worked along with him
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.
Hi, share file
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
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
Why was so much time spent on Sum, SumIf? Does that have anything to do with pivot tables?
Why is it called a "pivot" table?
@ExcelGoogleSheets
2 жыл бұрын
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.
@ExcelGoogleSheets
4 жыл бұрын
I have to admit, I'm not good with intros.
Sterling video