Google Sheets: Intermediate Chart Building, workflow
This is Part 3 of this Google Sheets Intermediate Project Course. However, this part of the course can also stand on its own. If you are looking for part one or two, you can get it here:
In this tutorial, we will dive into how to build charts. We’ll look at how we prepared our data for our chart. We’ll then cover some more detailed ground so that we can get our chart just right.
We will create a chart displaying the day change of two USD-XRP currency gateway agencies Bistamp and Gatehub.
The data we will be using to build our chart can be found here:
In the first part of our tutorial, you made a copy of the raw Exchange Volume data. Then in a new Google Sheet tab called Tutorial, you made a column of UNIQUE Date Time Stamps.
Realising that this was not user-friendly you grabbed just the date data from Column Aand put it into Column Busing the LEFT function. You then used some conditional formatting to highlight every Sunday automatically.
Next, you added two columns to display the daily exchange rates for the two gateways using FILTER. You then used two colours to identify which gateway had the highest or lowest exchange rate for that day.
You then grabbed the average of the two gateways before displaying their percentage difference.
In this tutorial, we are going to make it even easier for our reader, by setting up a column to display the gateway name of the highest exchange for each day. We’ll then put in a block of columns that will display data about the day change of the average of the two gateways. Finally, we will add a SPARKLINE graph into each row so the user can, at a glance, see how the average exchange has been tracking for the past 7 days.
By the time you are done your chart should look like this:
In this tutorial, we are going to tackle some Intermediate level Google Sheets concepts including using functions like:
FLITER
ARRAYFORMULA
MIN and MAX
IFERROR
SPARKLINE
UNIQUE
WEEKDAY
LEFT
We are then going to make our Google Sheet more presentable by doing some neat tricks with Conditional Formatting.
However, probably the most important lesson is to consider throughout the whole process is the workflow of this project. I will try and get you into my headspace and talk about:
How I came to do things in a certain way.
What I tested before applying to my Google Sheet.
The order I did things to create the test.
When I am learning something, I find the most benefit in learning by following a project. As you follow along you don’t have to read everything. Actually, I encourage you to get the basic idea of the stage before giving it a try. Then come back to the tutorial for troubleshooting.
Picture this. You have set up your Google Sheet and attached your Google Form data to it so you might get a tab that looks a little something like this:
You probably don’t want to mess with this tab because the Form is still live.
Instead, you decide to create a new Google Sheet tab that you want to automatically transfer all the data into, including the current form response data and any new form responses you might get.
The Common Mistake
A common, though mistaken, approach to this is to do a cell-by-cell transfer of data. For example, we would grab the first data cell of our “Form responses 1” Sheet tab and in a new sheet tab cell we would write:
1
'Form responses 1'!A2
We would then drag that cell across to the right to cover all the columns. Then all the way down to the bottom of the page to cover the current responses and any new responses added.
That might look a little like this:
The problem
This looks like it might work, right? Let’s test it out by adding in a new form response. For me, it will be the 6th response and will appear on row 7 of the ‘Form Responses 1’ sheet tab.
