Hey! Welcome to the Coupler.io Academy, where industry experts share tips and tricks on working with data. In this video, we will learn how to create a dashboard in Google Sheets. Not only that, weāllĀ see how to add a layer of interactivity by using filters. And āll also share a secret techniqueĀ
to keep our data fresh automatically. If you wanna follow along as IĀ
prepare a Google Sheets dashboard,Ā Ā the link to the spreadsheet Iām using will popĀ
up in the corner of your screen. And you can also find it in the video description below. Iām looking at a sheet containing information from multiple flower shops across theĀ United States. But in this current format, the data is really hard to consume. This is whereĀ a Google Sheets dashboard comes in handy. Letās start off by creating aĀ new sheet called Dashboard.Ā In this new sheet, Iāll add our firstĀ
chart, and to do so, Iāll go to the Insert menu and select Chart. In the Chart Editor, Iāll choose the chart type and for the first oneĀ
letās go with a scorecard. Next up, youāll wanna provide some dataĀ for our dashboard in Google Sheets. To do so, click the Data range button which willĀ
allow you to navigate through the fileĀ and select the data. For this example, letāsĀ select all the online sales starting withĀ the first row all the way to the bottom. Iāll want to aggregate the total sales numberĀ and use the SUM operator to do so. Since Iām looking at a big number,Ā Ā letās shorten it a bit so go to the CustomizeĀ tab and under the Key value, select the ScaleĀ Factor of 1 million. You can also add a customĀ number format and add a āMā as a suffix.Ā And just like that, you've built yourĀ first Google Sheets dashboard. Now to add a little context to the report,Ā
letās go back to the customize tab,Ā Ā and under Chart and Axis titles, IāllĀ
add āTotal Salesā as the title. Letās resize it and move itĀ to the top of the page. Next up is a line chart. I want to display the total sales of our flower shops each month. To make itĀ easier, Iāll jump back to my Online SalesĀ sheet and add a new column called āMonthā. I will then use an ARRAYFORMULA with the TEXTĀ Ā function inside that will take the month out ofĀ our order date in the second column. As a range, letās specify B2 to B4977 andĀ filter by month using āMMMā. Close the parentheses, andĀ
you should be good to go. Letās switch back to our Dashboard sheet, and nowĀ I get to finally add the new chart to my Google Sheets dashboard. Just like before, I select theĀ chart from the Insert menu, and this time IāllĀ select the line chart in the editor. Iāll select the two inputs for our Axis. In my case, it will be Month and Order total. For the Axis, youāll select Month,Ā Ā and also tick the aggregate button.Ā Under Series, select Order Total. Iāll resize and move it so IĀ can create another chart. For this third one, letās use a table chart. Click the Insert button and select Chart. From the editor, you want to select the Table Chart. Now in the data range field, select your sheet and then the Product Category
and Order Total columns. Iāll aggregate the results, resize andĀ
move them next to the line chart. Now itās starting to look like aĀ
proper dashboard already. But now onto the cool part. Letās make our dashboard in Google Sheets interactive by adding a slicer filter. Go to Data and select Add a slicer andĀ Ā in the data range select the entireĀ
file by going from A1 to N4977. From the Data tab on the right, you can chooseĀ
to filter by anything you want. In my case, Iāll pick OrderType so I can see the salesĀ
numbers for Retail ordersā¦. or wholesale. And there you have it. A fullyĀ
interactive Google Sheets dashboard. And before I wrap up, Iāve mentioned aĀ
little secret I was going to tell you.
Ā This Google Sheets dashboard example isĀ
based on static data thatās been addedĀ to the file manually. In some casesĀ thatās fine but more often than not,Ā Ā youāll want to see an up-to-date version of yourĀ data so you are left with two options. You either keep adding new recordsĀ
every time a sale happens or you can use Coupler.io which will automatically importĀ
the data for you on a predefined schedule,Ā Ā keeping your dashboard up to date. Sign up for a Coupler.io account and start a free 14-day trial, no credit card is needed. UseĀ it to import your data from dozens of availableĀ Ā integrations, transform it on the spot, and buildĀ a fully automated Google Sheets dashboard. And that's it! Now you know how to create a dashboardĀ
in Google Sheets but if you have any questions,Ā Ā feel free to ask them in the comments. If you enjoyed this video, please give us a like, share the video with others, andĀ
subscribe to our channel so you never miss a thing.
Thanks for watching and seeĀ you in the next one!