Pivot Tables with Google Sheets & Looker Studio Dashboard Tutorial

Рет қаралды 22,961

Learn Google Sheets & Excel Spreadsheets

Күн бұрын

Support This Channel / chicagocomputerclasses
Tutorial: Create Pivot Tables with Google Sheets data and present in a Looker Studio (Data Studio) dashboard.
00:00 Pivot Tables - Introduction
02:54 Create a Pivot Table with Google Sheets Data
17:59 Add Multiple Metrics, Values, Measures to Your Pivot Table
20:44 Create a Dashboard with Multiple Pivot Tables
24:42 Group Dates in Pivot Tables by Year, Month and more
27:27 Sort Results in a Pivot Table
28:52 Add Filters, Slicer Controls to Pivot Tables
31:45 Filter Pivot Tables by Date Range using Date range control
33:58 Rename Looker Studio Dashboard Pages
34:48 Add Calculated Fields to Pivot Tables
37:38 Expand-Collapse Rows in a Pivot Table
38:36 Optional Metrics in Looker Studio
#pivottables #googlesheets

Пікірлер: 54

@F114able Жыл бұрын

Thx a lot, my friend! You are perfect teacher, so easy to learn with you. Keep going!

@lpanebr Жыл бұрын

Excellent! I've used this a couple years ago with Google analytics data. The interface seems cleaner and more responsive than what I remember. I'll check it out. Thanks!

@pekunoyeleke2614 Жыл бұрын

Brilliant presentation from an exceptionally sharp mind. I can't thank you enough for the invaluable impact this has made on me

@maximilianovazquez8433 Жыл бұрын

Very useful guide, thanks for the video

@duckhanh.404 Жыл бұрын

great video. Thank you very much. Keep it going!

@CarloLopez Жыл бұрын

Year after year always very useful, thanks!

@ExcelGoogleSheets Жыл бұрын

🙂

@pixelharmonie-webdesign 7 ай бұрын

Great explanation! And it's timeless too... Thank you for your clear English + your voice and of course the clearly conveyed content 🫡

@silvestrecamposano6317 7 ай бұрын

Thank you very much, Sir. Your videos are very helpful to me in my office job... I learned a lot...

@braczero Жыл бұрын

Me has convencido para usar en mis reportes de trabajo usar looker o google data studio tiene una mejor vision y presentacion gracias por el video megalike sigue subiendo mas material de looker. Saludos.

@LuisMG4022 Жыл бұрын

I love your channel! The value you bring to us on every video it's amazing! Would be cool if we can reach to you on linkedin

@lbath76 Жыл бұрын

Very well explained, thanks 👍

@ExcelGoogleSheets Жыл бұрын

🙂

@jonathanlance2166 Жыл бұрын

Excellent presentation.

@ExcelGoogleSheets Жыл бұрын

Thank you!

@thehangr Жыл бұрын

Thank you sooo much for this video. Very helpful..

@ExcelGoogleSheets Жыл бұрын

😊

@MrAstonmartin78 Жыл бұрын

Thanks for a good video!

@selcukmutlu1761 7 ай бұрын

Thanks a lot

@joseantonioubeira295 Жыл бұрын

Thank you very much for this excellent video ! I would be interested in creating graphics (bar/ pie charts for example) of the data in the pivot tables so that they appear in the same Looker report. Thank you.

@LotfyKozman Жыл бұрын

Expecting more Data Blending in Looker Studio using Google Sheets 🤞

@johnmillersr.colorado Жыл бұрын

Thanks again for your efforts to educate followers about helpful tools. I thought this particular tool would be helpful to me however I've discovered a problem when sharing a report using the "Viewer" option. The report used a date range control object which evidently would not filter the data in the report tables. I tested various ways, including granting Editor permissions, and never could get the data to filter using any selected date range from the test account. I should probably report this but haven't the time to focus on it.

@ExcelGoogleSheets Жыл бұрын

I use data range control and it works fine for me. Are you sure your date column was selected as the data range dimension?

@johnmillersr.colorado Жыл бұрын

@@ExcelGoogleSheets The report contains two charts (table format), each using the one date column in the dataset. The date range control evidently uses that single date column because there isn't an option to attach manually that I can see. In edit and view mode in my account where the report was developed everything works. In the test account it did not when the link was sent via email from the Share screen. However, I just tested again by simply copying the link and accessing the report in the test account browser...and it works perfectly. Why this happened I can't explain but nothing in the report had been changed. In any case it works in the test account and I'm satisfied it will be useful to utilize the tool. Again, thanks so much for your attention to this matter. You're so helpful!

@Omeromuscio 4 ай бұрын

many thanks for explanator is possible download file so i can practice ? thanks Alberto

@user-yp8hf1xd2r 8 ай бұрын

Instead of a Grand Total Row at the bottom, is there a way to display average for each region? For example NorthEast $982k divided by number of years and every time number from 2022 changes, it will reflect on our formula as well?

@devsharma5669 Жыл бұрын

Hey if i want to break the data like one customer purchased many items can i put customer name in dimension and in drilldown can i put item purchased as one customer can purchase many items

@kishansaria8179 11 ай бұрын

How can we add matrix for calculating XIRR for group total

@rahulpathare2664 Жыл бұрын

Thanks for this video. How to show ... 1) How many days has past from the month and 2) How many days are remaining from the month. The value should change automatically as per todays date. I have tried to search eomonth function in the calculated field but no use. Your help will be apreciated. 🙏

@ExcelGoogleSheets Жыл бұрын

CURRENT_DATE DATETIME_ADD DATE MONTH YEAR That would require a combination of the functions above. The logic would be to get the first day of next month and subtract one day. Or you can do the math in the source spreadsheet.

@ademolabangura4894 4 ай бұрын

how can i get these datasets please?

@wskong Жыл бұрын

why dont i see column and row dimension option in my setup panel? I just see dimension

@ufuksakar6080 Жыл бұрын

Hello, I am following your channel with great interest. Is this google data studio paid?

@ExcelGoogleSheets Жыл бұрын

No.

@training1562 Жыл бұрын

Is it possible to import a pivot table available in data studio to google sheets?

@ExcelGoogleSheets Жыл бұрын

No.

@gmixgmix2647 Жыл бұрын

Sir I know this not right video to put this question but please let me know how can we paste data from sheet 1 to sheet 2 but condition is that col1 data from sheet 1 pasted in col1 of sheet2 but col2 data from sheet 1 pasted in column 3 of sheet2...And going on col3 data from sheet 1 pasted in col5 in sheet2. Please give your thought about that also

@ExcelGoogleSheets Жыл бұрын

Maybe copy and paste each column separately?

@johnmillersr.colorado Жыл бұрын

Exploring Looker Studio further I have an issue with formatting numbers, specifically currency format that truncates trailing decimals if the value is zero, e.g. $99.99 displays okay but a value like $99.90 displays as $99.9 in the report. I noticed a number of currency values in this tutorial that displayed the same way, but nothing was mentioned about it. I've spent too much time searching Looker Studio documentation and forums with no solution. Am I missing something? Is it acceptable to display U.S. currency this way? Is there a function such as CURRENCY() that can be used in a created metric field?😵‍💫

@ExcelGoogleSheets Жыл бұрын

Under Style Change Decimal Precision to 2.

@johnmillersr.colorado Жыл бұрын

@@ExcelGoogleSheets 👍

@ExcelGoogleSheets Жыл бұрын

Let's be honest, it's a stupid way of handling this. But hey, it works.

@user-fp6zw7jt9o Жыл бұрын

Hi! I wasn't able to see the optional metric button eventho the formula I've added was accepted

@user-fp6zw7jt9o Жыл бұрын

It's ok now. I had the chart header on "do not show"

@ExcelGoogleSheets Жыл бұрын

👍

@jomarhulipas3809 Жыл бұрын

sir once the user aceess is only viewer. They can't refresh data :( The refresh data function only available to the user with edit access.

@ExcelGoogleSheets Жыл бұрын

You can set the report to auto-refresh the data every 15 minutes. support.google.com/looker-studio/answer/7020039?hl=en#zippy=%2Cin-this-article

@Allen-L-Canada Жыл бұрын

Can Google Sheets also do calculated fields?

@ExcelGoogleSheets Жыл бұрын

Yes kzfaq.info/get/bejne/psp8n5h6sZO-d40.html

@JCEurovisionFan1996 Жыл бұрын

Can you do a video on how to do Excel 2021 without LAMBDA helper functions? I want to know what is the Excel 2021 equivalent of the formula being used: =ARRAYFORMULA(IFERROR(SUM(IF(BYROW(FILTER(Ballots!$C$2:$T$1001,ISNA(XMATCH(Ballots!$C$1:$T$1,$B$39:B$39))),LAMBDA(x,IFERROR(INDEX(FILTER(Ballots!$C$1:$T$1,ISNA(XMATCH(Ballots!$C$1:$T$1,$B$39:B$39))),XMATCH(MIN(x),x)),"|")))=$B2,1,0)),0)) Also, how can I transform from Google Sheets to Excel 2021 if there is ARRAYFORMULA and ARRAY_CONSTRAIN? I don't have Office 365 as of now.

@ExcelGoogleSheets Жыл бұрын

I don't think you can do an array equivalent for this prior to BYROW LAMBDA. Maybe some weird & complicated MMULT, but I'm not even sure without giving it a try. But in general equivalent to ARRAYFORMULA would be to press ctrl+shift+enter to apply the formula.

@ExcelGoogleSheets Жыл бұрын

It's very likely that instead of doing everything in one cell, you'll need multi-formula approach.

@JCEurovisionFan1996 Жыл бұрын

@@ExcelGoogleSheets Hoping for a video on that.