Laurie Anastasio tweeted that she was creating a line graph for tracking photoperiod/daylight to prepare for a project with Journey North. The problem was that the chart wizard was not able to read her data. View Laurie’s data: CLICK HERE
When you highlight your data to use the chart wizard in Google Sheets you want to exclude extra columns. In the case of Laurie’s data she has the hours of Sunrise and Sunset that are not graphed but are part of the range that includes date and hours of the photoperiod.
One solution is to duplicate the sheet and delete the extra columns to create the chart. The problem with this is if you update your data on the first tab that does not effect the second tab.
Another problem Laurie had was her time was written in hours and minutes separated by a colon. This is not a numerical value that can be charted.
When your data columns are not set up for charting (such as when you have information from a Google Form) create a new tab. CLICK HERE to see the solution data.
If you use the formula =importrange(“spreadsheet URL”, “Data Range”) you are able to write formulas on the data and in the case of your original data being from a Google Form that it will update as there are new form submissions.
In cell A1 I typed the formula
=importrange(“https://docs.google.com/spreadsheets/d/1k8fa13EcmFwVcmDODlH4lgyvNtHK3VAMz3TBio_yOXc/edit#gid=476577544″,”San Marcos Daylight!A2:D40”)
Remember to put the quotation marks around the spreadsheet URL. I am importing data from the same spreadsheet so I simply copied the URL from the top of the screen.
The data is on the first tab which has the name “San Marcos Daylight.” The data range is written in quotation marks with the name of the tab and then an exclamation mark and then the range of the data. In this case the data to be charted was from cell A2 to cell D40.
In column E I needed to split up the hours and the minutes that were in column D. Use the formula
This says to take what is in cell D1 and break it up wherever there is a colon. Make sure you place the colon in quotation marks. Copy the formula down so that all of column D is split up by hours and minutes.
To convert the minutes to hours, divide the number of minutes by 60.
Bring it Together
I now have a lot of columns that are not necessary for my chart. What I can do in this case is use the equals sign to copy the columns I need to chart to the end of my data. I need column A and column G for my chart.
In cell H1 I type
And in cell I1 I type
This simply takes whatever is in A1 and whatever is in G1 and puts it into the new columns. Copy and paste the formula down the sheet to reflect all of the data in columns A and G.
Instead of creating two new columns for the data you can select your ranges manually in the chart editor. Under the start tab next to the data range it says “Select ranges…” Click on this to edit the data range for the chart.
Edit the range to just be one column of data. Click on “Add another range” to highlight a second column of data. The first data range can be your x axis values and the second data range can be your y axis values.
Highlight the Data
To use the chart wizard first highlight your data range. In this case it is cells H1 to I40.
Use the chart wizard icon in the toolbar or use the Insert menu to insert a chart.
The chart will be inserted as a floating window on the sheet. If this is not what you want you can move the chart to it’s own sheet. Click on the chart and notice there is a small arrow in the upper right hand corner. Click on this arrow to reveal editing options. Choose “Move to own sheet…”
The chart will automatically update as data is added to the first tab.