Collaborate on Google Docs Free Invoice Template for Inventory with Ease Using airSlate SignNow

Google docs free invoice template for Inventory

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!