📊 Make Your Data Speak! Create an Interactive Google Sheets Dashboard

today I want to show you how you can take your  raw data like this in Google Sheets and turn it   into this an interactive dashboard that will speak  the results that you want to find hi I'm   Jamie and welcome to teachers Tech today I want  to show you how to create your own interactive   dashboard in Google Sheets and we'll go through  this step by step now if you're unfamiliar with   an interactive dashboard is here's a little  explanation we're going to start with this   data right here and you have access ACC Down Below  in the description you can click and follow along   and make the same dashboard I am and we're going  to take this data and bring it into the dashboard   so this dashboard includes charts pivot tables  and slicers which are kind of like filters so   we're going to go through all these things and  I'm going to show you how quickly you can put   together something like this and then as you pick  different things this becomes interactive so with   these slicers or these filters you can dig down  in see the information specifically this is a lot   easier than looking at this if you click the  link and are following along here's the data

that we're going to be using here you can see  there's lots of data from the years 23 24 this   is of a fictional company that sells hoodies and  t-shirts about superheroes now the first thing I   want to do is actually add another column and  I want this going to be this one's going to be   called year I want to pull the year out by itself  here just to help me in filtering as   I go along so I'm just going to use a function  here and it's going to be the year function so   equals year and if I select this all I have to  do is select the first one and hit enter and I'm   going to let autofill uh fill everything out and  now at towards the bottom you can see as it turns   to 2024 those years are taken out of it and this  is going to help uh when I do some filtering with   the slicers now let's move over to sheet one and  the first thing I want to do is change this from   sheet one and I want to change this to be called  dashboard because this is where we're going to be   building our dashboard and we're going to do some  simple stylizing here I'm going to highlight all   the way to O I'm going to merge and you can do  this a bit differently you don't have to follow   along exactly as I do it I'm going to stretch  the row height out and I'm going to give this a   title superhero Hub interactive dashboard because  that's what my pretend store is called now what   I want to do now is change this to a white font  and I'm going to change the background to Black   and now I'm going to increase the size let's try  40 and we're going to go a little bit more down   well to go 35 and bold it and let's do a center I  can change the size here still if I want to uh I   want to add a logo here so I'm going to go insert  and I'm going to image and if you don't have this   that's fine I just want to show you could  insert images into here as well and I'll just   grab this one here and this will upload to Google  Sheets and I have it selected to be on top there   the other option that would put it in the cell so  I can size this the way I need it and place it in   okay now what we're going to do is start with  our slicers on the dashboard sheet I'm going to

go up to data add a slicer so now it's going to  say select a range you can see the slicers right   here what do I want to have it filter so I don't  want it to have dashboard here I'm going to delete   this and I'm going to move with this open I'm just  going to click on the example data tab at the very   bottom here and I need to have all this selected  so when I click in this I don't want a single   cell but if I use controll a it selects everything  so use that shortcut to select everything inside   that range and that is correct A1 to H 337 I'm  going to go and hit okay now notice what happens   is with the data it will say choose a column and  the first one I want to have is the year and this   is why I wanted to create that extra column with  the year so I'm going to just click year and I'm   going to move this over here because this is where  I'm going to place it you can rearrange it to the   way you want I'm going to make this a little bit  smaller uh I'll talk about how we can change the   color of these later when we do a little bit more  formatting now at this point I could go insert   another slicer because I want to have three but  I'm going to do a little bit shorter way I'm going   to go and just go copy and I'm going to go control  V on my keyboard and place it again now I don't   want to have the exact same slicer so if I go  back and edit the slicer this time I want to have   product category so my product category is going  to be between my hats or my t-shirts and th this   just saves a step here so I'm going to just align  this and I like how Google Sheets you can see how   it will snap into line here and the next one I  want to do and I'm going to go copy slicer again   and I could pasted multiple ones and I did contrl  V all at once if I wanted to so all depends on how   you want to do it I'm going to go this time edit  slicer and I want to go by state so those are the   different ways I'm going to be filtering all these  are connected to this range up here automating and   visualizing data in Google Sheets is a great way  to stay on top of important insights but when it   comes to managing projects brainstorming ideas  and organizing workflows Google Sheets might   not be enough that's where taskade shines with  taskade you're up and running in three easy steps   taskade AI project studio is a game changer for  productivity allowing anyone to instantly create   structure projects with AI powered workflows  Automation and collaborative tools whether you're   starting from scratch importing existing files  and links AI project Studio organizes everything   into a clear actional plan within seconds do you  have files to start your project from Simply use   AI with import files with AI go and grab many  different types bring them in and you're going to   see how fast you can get started AI agents  in taskade is like having your own virtual   productivity team really easy to create just go  ahead create with AI describe the agent that you   want and what it can do for you but you can also  use the templates here that are available you need   a researcher just hit create agent you can start  chatting and you get an idea what it can do for   you right away to save time you need to try  automations now you can go ahead create the   automation you can generate with AI just from  the idea or you could use one of the templates   like this one you'll see that it's already set up  with a flow like task a new comment send Channel   message to slack at each of these points this  is where you're going to need to connect to your   different apps in this case slack and you can go  through add the action and send the message and   that's all going to happen automatically in the  background generate projects instantly and boost

and move down to chart and the first three  charts we want to create are all going to be   the same and I'm going to do something similar to  the slicers with copying pasting after I have one   done it's going to be the scorecard chart and I  need to select my data range so we click on this   I don't want it to be E4 I'm going to move over  back to the example data and I'm going to go and   just select this column right here so units sold  I'm going to click okay so this isn't correct and   this is where Google Sheets is actually powerful  because we can aggregate The Columns that we   chose and just by selecting this and look at the  different options that you have over here some   is correct what I want but I could select others  as well this can be changed the size of this so   just like when we're setting up our dashboards  and formatting you can grab uh the corners of   this and start moving uh the handles around  to get to the exact size that you would like   now if this closes down on the right hand side  just double click on it and it will open back up   the other thing is if I go to customization chart  Style let's say if I want it to be this font here   and I want a title on it so if I go to chart title  and I want to call this one units sold and I can   make sure that my font's the same maybe I bold  it and you can make all these quick adjustments   to it now these already work with the filter look  at this so if I drop down here and pick just 2024   watch the number change so it's going through and  pulling the data out of here for unit sold that's   only based on 2024 then I can go and select let's  say a uh maybe these uh States here these   three states and if I hit okay now it's only  going to pick up for 2024 to those States so   these filters are all already working and now I  can go ahead and let's go select all again and I   can go ahead and select all again oops I don't  think I selected all that time so it Go select   all and then hit okay at the bottom then you're  going to see the number go back let's make two   more of these types of charts and I'm just going  to go control C to contrl V to copy paste contrl   c contrl v because now all I need to do is change  uh the column so if I double click on this one and   go back to setup and it's not going to be the data  range here I'm going to delete this one I'm just   going to open this back up we can delete this  here and go back over to example data let's say   I want Revenue this time I'm going to hit okay  now it didn't look like it changed but if I go   to example data and just come back it updates  there now the other thing is if I go to example   data notice the dollar signs weren't on this I I  want it to be formatted with currency so I'm   just going to highlight these three columns right  here and just click on that so it's all currency   and now if I go back over to dashboard you can see  that it's here I don't want this to be called Unit   sold I'm going to double click on this one under  customization and I'll call this one uh Revenue   now so just like that you can in this way it's a  little faster because it will use the formatting   that we copy paste from the first one I'm going  to go contrl c contrl v and I want to also have   profit so I doubleclick and I'm going to go to  setup and choose my range or my column this time   is going to be the profit one which is going to  be right here and hit okay so again if I just go   back example data dashboard you can see how it  updates if I wanted to see maybe a certain State   let's go clear and I pick California hit okay so  we know in C California that this many units this   much revenue and need to change the name of this  one was going to be profit so I can go and change   under customization and then just move down to  chart title and we'll put profit in here I'm going   to go and move some of these around and get them  organized and then we'll create a few more charts   I moved a few things around I made sure everything  was selected again to get all the totals so let's   insert a line chart that shows Revenue over month  now so just a trend line of what's happening with   the revenue over all the different months so I'm  going to go insert a chart and we're going to go   and pick this one right here this is going to  be a smooth line chart again you could do this   differently if you want what's going to be the  data range so if I select data range I don't   want it to be a17 because that's the cell I have  selected here I'm going to go over to my example   data and I'm going to select this colum right  here so G these are my months here and I also   want Revenue so I'm going to hold down control  on my keyboard and hit okay and just like that   we have this chart and this doesn't look correct  at all because I need to make some adjustments in   the setup so first of all under xais I just want  that to be the month here so if I go here I have   now this month and I want to click on Aggregate  and that's looking a little better but I need to   remove some of these things as well because I just  want Revenue I don't want month here so I'm going   to go and re move this one this is looking like  what I want I see the total amounts I see month   after ma month I'm probably going to stretch  this out place it underneath the other thing   I'm thinking uh I want a trend line on this as  well so where you can find this is under your customization up top here notice that we have  series and I'm going to go and click on trend   line and you can see the other options  you have and I'm going to change this to   red and you can play around with how you want  things to look on this but I'm just going to leave   this uh up here as this for chart title and access  well this is going to be Revenue by month and I'm   going to make sure it matches my other uh fonts  and just like that I have a trend line inside   this where I can kind of see month over month I  can see it's starting with January 23 going over   to uh 2024 so you can size this the way you would  like so if I wanted to stretch this out AC cross   here and have this in place let's make this a bit  smaller here this way if I drop down let's say on   a different year now just 2024 you can see how  this is adjusting all of these are adjusting at once I just moved our Revenue by month down a bit  here I want to insert a revenue by state here as   well so let's go insert a bar chart this time  we're going to go put chart column chart here   that's the correct one let's get our data range  we don't want it to be a17 we want it to be our   state so I'm selecting the column here in a  and going over to revenue again you can pick   different ones if you would like I'm going to hit  okay now this isn't correct we need to aggregate   this as soon as we aggregate this so if I click  over here you can see this it looks more of what   we want so let's do a quick resize of this one  I also want to add a title to this so if I go   to customize let's go to chart access and title  and I'm just going to go ahead and put in Revenue   my by state make sure that we have the kind of the  same format as the other ones all right so we have   a bunch of charts in here we're going to add two  more but these next two I want to do it by pivot table for this next chart I want to be able to  compare hoodies and t-shirts and also the years

so I'm going to do this with a pivot table and I'm  going to put the pivot table right inside of here   when we're working with slicers I find they're  really finicky and it you'd want your pivot table   on the same sheet that you're working with with  the slicers so let's go up to insert pivot table   if you want to learn more about pivot tables I  have a different video all about that I'll put   the link Down Below in the description so what's  going to be the range of our pivot table well it's   going to be the entire sheet of this first example  data and I know I can see it right here this A1 to   337 so I'm just going to select it uh rather than  selecting everything I'm going to go before I hit   create I want to create it on the same sheet as  the dashboard I'm going to hide it underneath the   chart so where am I going to put it so I'm going  to put select what location I'm going to go back   to dashboard and I'm going to place it right under  here because I'm going to hire put the chart on   top of it but you could place it somewhere else  if you'd like I'm going to hit okay and I'm going   to hit create so this is where I'm going to put  my pivot table and what's going to be in my pivot   table so for the rows I want to have product  category so I'm just dragging and dropping this   right in here so I have hoodies and t-shirts and  the columns are going to be the years so here's   the columns over here and I'm going to go and get  the years remember that column I made at the very   beginning so I'm going to put that into columns I  don't want to have totals on these so I'm going to   turn these off like this and what are the values  you could do different things but I mentioned unit   sold so that's what I'm going to go do so I'm  going to drag this into values just like this   I have a pivot table now look at this if I go and  start filtering by category and hit okay this is   interactive this pivot table so if you wanted to  have the pivot table as part of your dashboard you   could I can search as you can see if I change by  state all of this adjust but I'm going to create   my chart now based on this pivot table so I'm  going to select all again so I'm going to go up to   insert chart so what's going to be my series here  so I'm going to go over to data range and not l14   I need to select what I want from the pivot table  so I'm just going to move this over I'm going to   select this right here I'm not selecting that top  column where it says sum I'm selecting this within   here and I'm going to hit okay now this isn't  quite what I want because the product category I   don't want right here so I can change that though  because use row five as headers if I click it   this is what I want right here so I have hoodies  t-shirts 23 24 everything is looking exactly the   way I'd want like I said I had troubles producing  this just by using the example data and how I was   showing you to create the charts and this is where  pivot tables come in handy but remember to put   them on the same sheet so I can size this up now  and place this where I would like and I'm putting   it on top of my pivot table to hide it but you  can do something different with it now I'm going   to insert one more pivot table with the map where  things are being sold now I want to place in a map   of the United States showing me what state all my  different sales are happening in and how much so   I'm going to place it here and I'm going to go  insert and I need to do a pivot table again and   I'm going to go and select my example data here so  contr a select everything hit okay on an existing   sheet like I showed you in the last one select my  data range going to go back to the dashboard and   I'm going to select this spot right here and  hit okay and let's create so here's my pivot   table you can sort of create this with just doing  it the first ways I showed you to create charts   but it didn't quite work perfectly with the pivot  table it does so now I want to take the state and   I'm going to bring it down into my rows so that's  what I want there I don't want to have total so   I'm just going to check this off I can also add  my values this way rather than dragging I'm going   to go with unit sold you could pick something else  if you would like so here's my information now I'm   going to chart with this so I'm going to insert  my chart don't want to pie I want to drop down   and it's going to be and I'll show you jump back  and forth between them let's start with the with   the markers right away I can see as I hover and  it's showing me the great amounts as I go through   each one that's a little small I want to focus so  if I go to customize and Geo drop down and go to   the United States this is a better way to see it  here so I can go and customize the colors I'm not   going to worry about that with here if I go back  over to my setup and drop down and I'm going to   show you the other map so as I click it then this  is kind of what I want here to show the states   like this it's still shows me the correct numbers  so at this point I can size this place this just   like I did with the other charts I can stretch it  out to fit it so now if I was going to go through   and filter any of this information so if I was  looking for a specific year like 2023 everything   adjusted right here with the totals everything  on this uh interactive chart uh interactive   dashboard has changed based on what I'm asking  for if you want to do a little bit more Style   customization now what you can do I'll give you  an example if I highlight let's say behind here   just those cells I can go and color those cells  and you can adjust what you want I can go and   change the color of these so if I go back to edit  slicer customize maybe I want this to be a white   and I want the font to be so you can just choose  here and I want this to be black so it changes it   up so I can go through and make those adjustments  I can also I can go through to these other colors   here and make adjustments to the background so  let's say if I wanted to have a we'll go with this   color and I could also change it behind here so we  can make those quick changes to it and the other   thing is maybe you don't want the grid lines so if  I go to view and go show Turn Off the Grid lines

now this is what gets to when I was showing you at  the very beginning so you can go through and move   things around to get things exactly the way you  want but right now we have this entire interactive   dashboard that's going to work from this  original data over here when with the use of some   pivot tables I hope you found this interactive  dashboard for Google Sheets tutorial helpful let   me know what else you'd like to learn about Google  Sheets or other Tech products thanks for watching   watching this time on teachers Tech I'll see  you next time with more Tech tips and tutorials