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:
Contents
Recap
If you’ve been following along from the previous tutorials, let’s quickly recap what we have done so far. As you are reading along, try and compare the original raw data in the Exchange Volume Tab to what we created in the Tutorial Google Sheets tab. Which one would you use to plot the daily exchange rate of the two USD-XRP gateways?
The aim of this project is to compare two USD-XRP currency exchange gateways( agencies) to see which one has the highest and lowest exchange rate day-to-day. Our secondary goal is to plot the average change in the value of the XRP cryptocurrency against the USD.
In the first part of this course, we grabbed the raw data that was fed daily via some Google Apps Script magic into the Google Sheet via the Exchange Volume tab.
We created a Tutorial tab sheet where we set up a sheet for our client:
We made each row a single date. We then made a column each for the two gateways, highlighting the highest and lowest exchange rate, before displaying their average. Next, we showed the difference between the two gateways as a percentage. This helps the client to see how far apart the two gateway exchange rates are.
Our guess was that users would like to see the highest exchange rate for each day to get an idea of which gateway would give them the best returns if they sold their XRP. We made this clear in column G.
We then changed track and focussed on the average daily change in the value of XRP in USD. Each daily exchange rate was compared to the previous day. The change in USD and the percentage of change was then displayed. Finally, the section was colour-coded to show an uptick or downtick along with the addition of an up or down arrow in column J. Just for pretties.
Lastly, we added a sparkline graph displaying the last 7 days of exchange rates so the client can see, at a glance, how the exchange is progressing.
Stage 3: Chart building
In this final stage, we are going to build a chart to display the daily exchange rate of the two USD-XRP gateways. This will allow the client to quickly see:
- How the average of the exchange is tracking.
- How far apart the two gateways are in their exchange rates.
- The trendline of the two exchanges.
- The average exchange rate rise and fall over time.
We want to ensure that the chart updates along with the data too.
By the time you get through this tutorial your chart should look like this:
Preparing data for a chart
When we were thinking about the design for the client-focussed Tutorial tab, we also had in the back of our minds that we would set up the data in a way that would make it easy for us to build a chart.
The Problem with Raw Data
To build our chart we need the date and the exchange rates for the two exchanges. If we were to use our raw Exchange Volume data we would have experienced some problems comparing the two gateways.
When building charts, you generally need to evaluate sets of columns or rows. However, with our raw data, we have one column for two data sets – our two gateways.
Our best possible outcome of using this data to build a chart would be to have a data point for each day AND each gateway on our horizontal access. Take a look at a sample:
Pretty useless, right?
While the line chart does indicate daily progress, the comparison between the two gateways is not very clear. Every two horizontal point is one day which is flattening out the chart too. The date-time-stamp is also a cluttered mess.
We can do better.
Getting Data Ready for a Chart
Contrast this to the data in our Tutorial Google Sheet.
Here we have the two gateways separated into their own column each. This will allow the Google Chart editor to plot the two exchange rates separately.
The date is also much more simplified and will make a better horizontal label for our line chart.
Now that we have set up our data for our line chart, let’s start building.
Creating the chart
Selecting the range
Select columns B, C and D. These contain the date and the two gateway exchange rates. You can select B and then Ctrl + D column. This will select all three columns all the way down to the bottom of your page. For me, that’s all the way down to row 1,000.
Note that this means that your chart range will contain a heap of empty rows. That’s fine, the Google Chart editor will ignore these until new data is added. In which case, the chart will update automatically.
Pretty cool huh?
After you selected your range, click the chart button or go to Insert > Chart.
By this stage everything should look like this:
Changing the chart type
We are going to focus on the Google Sheet Chart Editor now.
The first step is to change the bar chart to a line chart. Go into the Chart type dropdown menu and select the line chart:
This will transform your chart into this:
Well, at least it is resembling our end result now. However, there is an awful lot of unnecessary space between where the chart values start – around 0.35 USD-XRP and zero.
To get a more granular view, we could hide some of that empty space.
At the top of the Chart Editor, you will see the tab Customize to the right of Setup. Select Customize. A number of options will appear. Select Vertical axis, then go down to the Min. The Min and Max are the value ranges displayed in the chart. The Max range will generally display well automatically, however, the Min does often need some work for things to look good.
We are going to predict that the minimum USD-XRP exchange rate will not go below 0.30 USD for the foreseeable future. It’s just a guess. I don’t know all that much about cryptocurrency but from what I understand XRP does not fluctuate as much as others.
Have a look a the picture above. Much easier to see the difference there now, right?
I’ll make our chart a bit bigger so we can see some of the detail. Just click on the chart and then drag it to scale it to a larger size.
Horizontal Axis
Those dates on the horizontal axis aren’t the easiest to read when they are straight up and down. Let’s tilt them a bit.
In the same Customise menu, select the Horizontal axis dropdown. Down the bottom of this dropdown, you will see a Slant labels option. Go ahead and change that from auto to 60°.
Titles
Let’s improve our readability a little further by adding in the axis titles and the overall graph title. To do this, go to the Chart & axis titles dropdown in the Customise tab.
To edit the chart or axis titles you must first select the appropriate one from the first dropdown menu. We will work on the horizontal axis first.
In the Title text textbox name the horizontal axis simply to ‘Date’.
Next change the vertical axis title to ‘Exchange Rate’ and the overall chart title to ‘Daily USD-XRP Exchange Rate by Gateways’.
By this stage, your chart should look like this:
Working with the series data
We want to be able to see the general direction that the exchange rate is headed over our selected dates. We can do this by adding in a trendline for both Gatehub and Bitstamp.
To do this, go to the Series dropdown of the Customise tab. Make sure the Series is set to Apply to all series and then at the bottom select the Trendline checkbox. Two trendlines, one for each gateway will appear.
You will also see that the trendline is applied to the legend at the top of the chart.
Things are really looking good now, but we really should consider some design consistency. Our users are already conditioned to seeing Gatehub as green and Bitstamp as purple so how about we change those line colours.
Use the sub-dropdown menu and change it from Apply to all series to each of the gateway names and change their colour to correspond to headers in your data.
Finishing touches
This is a pretty big chart with about two months of data in it. It really deserves its own tab in the Google Sheet. You can give your chart it’s own tab by selecting the chart area and then selecting the three ellipses in the top right of the chart. A dropdown menu will appear. Select Move to own sheet… .
Your chart has now moved to its very own Google Sheet tab.
Now that we have more space. How about we add points to our graph lines so we can better see the point of change for each day.
Click on the chart again and the Chart editor sidebar will appear. Go back to Customise > Series and stay on Apply to all series.
Under the Format header, you will see that Point size is probably set to none. Click the dropdown and change that to 4px.
Conclusion
You are all done! Nice one!
If you close your Chart editor sidebar, you will be able to see our finished chart in all its glory.
Go run your mouse over the data points. You will be pleased to see little information windows open up to give you more details on each point.
Here is the end result:
Throughout this tutorial, we have covered how to prepare data before creating a chart. We then built the chart and added in some trend lines. Finally, we considered some design aspects to make the chart more presentable and easier for the client to read.
Final Assignment:
Think about everything you learnt in the past 3 tutorials. Do you think there is a way for you to create a chart that can look at selected 7 or 14 day periods in our data determined by a start date? How could you use the data in the Tutorial Google Sheet tab in a new Google Sheet tab to create 7 to 14-day glimpses of data that would automatically transform a chart? Could you use the FILTER and ARRAYFORMULA functions for this?
Give it a try on your own. If you do, you will really be solidifying what you learnt in the 3 Part course. I would love to see your results.
I’m a huge fan of Justin Mares, Mastering Google Sheets course.*
Did you enjoy the tutorial? Want to upskill and get a solid step-by-step course to become a pro at Google Sheets? Check out my course, Google Sheets: Learn the Essentials with Three Detailed Projects. Sign up today.
~Yagi