Track the Important Numbers in Your Life : How to set up a Number Dashboard (#Dashboard) in Google Sheets

Number Dashboard Screenshot
Number dashboards (#dashboards) can be used to track numbers that are important to you.  They are built on Google Sheets but can be easily and quickly viewed on mobile browsers.  If you already have numbers calculated and stored in other Google Sheets you can easily copy a #dashboard into existing spreadsheets and plug in your numbers.  Or use standalone #dashboards to hook into multiple spreadsheets in a straightforward workflow and collate your numbers in a single location.

 

 

In the latest iteration of #dashboards I’ve made a small collection of background images that can be included in your #dashboard.  These images are designed to be used dynamically.  As your numbers change so does the image to reflect that change.  For example, say you are counting down to a target date, as you approach the date a circle outline can progressively be filled in.  Or say your crypto investment is on the up, then an upward graph can be displayed, or a downward graph if things aren’t going so well.  The choice is yours.  In most cases you will need to design your own spreadsheet logic to incorporate the images as you need them, but I have simplified the procedure for incorporating countdown and count up images.
Number Dashboard Images Screenshot
The following will walk you through the process of setting up your very own #dashboard.

Method 1.  Set up a#dashboard to collate numbers from multiple other Google Sheets.

  1. Make a copy of my publicly available number dashboard: https://docs.google.com/spreadsheets/d/1Yw7jcS8HT53zOZDz8YNnv10Q2WfNoLCX4kZRLtpgMZw/edit#gid=0
  2. On the Data tab give your number a name, e.g. “Fuel Range”, in the relevant row.
    Number Dashboard Data tab Screenshot.png
  3. Copy the spreadsheet key from the spreadsheet that hosts your number and paste it into the cell on the Data tab.
    Spreadsheet key is the string of random alpha numerals in the spreadsheet URL.  The key for the above spreadsheet is 1Yw7jcS8HT53zOZDz8YNnv10Q2WfNoLCX4kZRLtpgMZw
  4. Write out the address of the cell.  Cell Address is the name of the sheet (or tab) in the spreadsheet followed by an ! then the coordinates of the cell using column letter and row number, e.g. SheetName!B3
  5. The spreadsheet will then proceed to access the number.  But before it can display it you need to authorise the connection to the spreadsheet by hovering over the #REF error message that appears and clicking “Allow access”.
    You are only allowing the copy of the #dashboard that you created and own to access the data so you are ok here on a privacy front.
  6. Give a unit to your number if appropriate.
  7. Find an image on the web that you want to use to represent your number.  Copy the image address and paste it in.
  8. Everything else is optional: you can add a further written description, a link (to a graph or more information), and include a dynamic image (more on that below).
  9. On the Data tab you can add a link to an image that will display at the top of the #dashboard.  I find that a nice way to differentiate between my various #dashboards.  If you don’t want one, ignore that and shrink the row on the dashboard.
  10. Once you’ve added in your numbers publish the #dashboard to the web.
    File > Publish to the web > Change “Entire document” to “Dashboard”
    Also expand “Published content and settings” and change “Entire document” to “Dashboard”
  11. Copy the URL and paste it in the web browser ➝ that is your dashboard.
  12. One last bit of tidying up: add “&chrome=false” to the end of the URL and go there.  Much nicer hey?
  13. Send that URL to your phone and bookmark it or add it to your home screen for quick access.

Method 2.  Add a #dashboard to an existing Google Sheet.

  1. Open my publicly available number dashboard: https://docs.google.com/spreadsheets/d/1Yw7jcS8HT53zOZDz8YNnv10Q2WfNoLCX4kZRLtpgMZw/edit#gid=0
  2. Right click the Data tab.  Choose “Copy to …”
  3. Search for and select the Google Sheet you want to add the #dashboard into.
  4. Open that Sheet and rename the tab you copied back to “Data”.
  5. Now repeat steps 2 ➝ 3 for the Dashboard tab.
    (If you want to use the images in the Images tab then you’ll also need to copy that tab into the new Sheet too.)
  6. Now the dashboard lives in your spreadsheet.
  7. To add a number to the #dashboard simply overwrite the formula in the value column and make it point to the cell where the number is, e.g. type”=“ and navigate to the cell or type something like this “=sheetName!C3”.
  8. Type in all the details as in steps 2, 6 ➝ 9 under Method 1 above.
  9. To publish the #dashboard follow steps 10➝ 12 under Method 1 above.

Add a dynamic image to your dashboard.

  1. I have a number that is going to count up to a target value.  I want to use that fantastic circle to fill in as I approach the target!
  2. Type “Up” in the column headed: “Image counts up or down to target value?”
  3. Choose Circles from drop down menu.
  4. Type in the value to start counting from.  Usually this is the value as it is now, or 0.
  5. Then type the target value.
  6. Now the #dashboard knows to ignore the image link (if there is one) and use the dynamic circle image.
  7. The image appears automatically in column on the far right.
  8. There are ten chunks of the circle that get added as the target is approached.
    Screen Shot 2017-08-07 at 16.00.45
  9. If you are comfortable typing logical statements into spreadsheets then you can design formulae to add dynamic emoji or graphs to a number.  I included an example for you called : “Investment Gain / Loss”.  That number doesn’t change automatically so feel free so to play around with the value and see the image change.
I hope that you like the #dashboards and that they provide a useful service for you.  If you need any help setting yours up then feel free to comment below or contact me on Twitter: https://twitter.com/rapidreportsHQ
Advertisements