Skip to main content
Chemistry LibreTexts

A1.6: Google Dashboard

  • Page ID
    373791
  • \( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \) \( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)\(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\) \(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\)\(\newcommand{\AA}{\unicode[.8,0]{x212B}}\)

    Google Sheets Dashboard and other tips

    General google sheets tutorials and other basics

    Creating a Dashboard & preparing Data

    Create a new tab and right click to rename it Dashboard. Usually its best to click and drag to reorder tabs so this one is first

    Note about naming tabs in google sheets. In general it is best to avoid spaces in tab names. When you reference a cell in different tabs you use the tab name!cell reference (Ex. DHT22!B2) if there is a space you have to use quotations (Ex. ‘data DHT22’!B2) but that means formulas will not automatically update or be repeatable

     

    • Before inserting chart freeze the headers of your data

      • Open your tab with the data and look for a thick gray line in the sheet’s upper left corner

      • Click and drag it to below your headers

      • This tells the sheet that these are your labels, it will then automatically use them in your charts

      • It’s also good if you have a lot of data because the labels will move with you as you scroll

    • Make sure that data is in proper format, not strings

    • Most commonly an error with date or times, you want charts to treat as values not text

    • Highlight column then choose Format>Number>Date time

      • You can also make a custom format to match whatever data you have

    Adding Charts

    Insert Chart

    Go to the menu bar on top and select Insert >chart

    On the right will be Chart Editor, under set up choose chart type you would like

    To open Chart Editor you can also click the three dots in the top right corner of your chart

    Scatter will plot only the points

    Line will connect the dots

    Smooth line will connect the dots with a gentle curve

    Click the box with four squares to open select a data range

    Go to the tab with your data

    You have a few options for methods of selecting the data listed below

    Types of Data Range

    • Whole Column

      • Click on the letter on the top of the column

      • Click and drag to select other columns as well

        • If you are having trouble you can also hold shift and click on the letter instead of dragging

      • You should see a range like A:B, note that there are no numbers with the range

        • Sometime it will show up as A1:B1000, as long as it is more rows than your data will be this will still work

      • This method will automatically update the chart as new data is inserted 

    • Specific Range

      • Click and highlight the exact cells you want to have in the chart

      • Example: A1:C10

      • Note that for this method when new data is inserted, the range will move to be with the same data, aka your chart will not change

    • Lock a specific range

      • If you are wanting to do a chart of only the most recent data you can lock a range

      • A $ sign is used to lock a cell reference, meaning that even if data or formulas change, the reference will stay the same

      • Example: $A$1:$C$10

      • Note as of May 2021 it appears google sheets has removed the ability to use locked references in a chart’s data range, now use indirect method explained below

    • Multiple Series

      • If you want more than one line charted with the same x axis click “Add Series”

    Make a Gauge Chart

    • Just like the regular chart, we need to work around that the cell references will change when we add data

    • To do this we will use indirect

      • Choose a cell that will not be in your way

        • I often choose a cell that will be hidden by the chart

      • Start a formula by entering an equal sign then type indirect

        • =INDIRECT

      • Go to your data tab and select the cell where the new data is inserted that you want to have on the gauge

      • Add quotes around the range

        • =INDIRECT("DHT22!B2")

    • Insert a new chart and under chart type select “Gauge Chart”

      • Change the data range to the cell you put the indirect in

      • Select Add label and choose header

      • Go to Customize >Gauge to add ranges and colors

        • Note that google sheets limits you to three colors

        • If you want more than three you can make the background transparent (none) under chart style and add an image behind the chart 

    • Live Chart of limited range

      • A live chart of the data is very useful, but if your sensor has been running a long time it can be hard to tell what's going on with so many data points. You may want to set up a live chart of the past x data points

      • Since they removed the ability to lock the cell reference of a chart range we have to be a little creative to work around

      • Add a new tab and give name

      • Use the function indirect and select your range, make sure its in quotes

        • =indirect("DHT22!A1:B21")

        • You may have to do some math to figure out how many rows to include, for example if your upload a reading every 15 seconds and you want the chart to be of the last 5 minutes you’ll need 1+(5*60)/15=21, so 20rows of data (remember to also add one for the header)

      • Set up your chart as normal but use this tab as the range

      • If you want to avoid having your tabs too cluttered you can right click and hide this sheet, you can unhide it by going to view>Hidden sheets

    • Conditional Formatting

      • If you have a large amount of data it can be hard to notice specific trend, conditional formatting can help you pick out points of interest

      • Select your range (using whole column is helpful)

      • Right click and choose conditional formatting

      • Choose color scale

      • Select what options you want, there are preset range or you can choose custom colors

        • Note the default three color palette is not color blind friendly so you may want to choose other colors to be more accessible, in general blue and orange have strong contrast for most viewers. 

     


    A1.6: Google Dashboard is shared under a not declared license and was authored, remixed, and/or curated by LibreTexts.

    • Was this article helpful?