A Line chart is easy to plot if the data series are correctly formatted. Here are the things you must know before trying to plot a Bar, Column or Line chart using lap times in milliseconds in Google Sheets.
Please note that the below points are applicable to any chart that uses milliseconds as data series or even in a calculation.
- Make sure that you have entered the lap times in the proper millisecond format. I mean it must be in HH:MM:SS.000 format.
- If the lap times are formatted like MM:SS.000 (probably you have), you can use a formula to format it correctly. Attempting to format using the Format menu won’t help you. I have explained this in the later part of this post.
When it comes to plotting charts, data formatting is an art in Spreadsheet applications like Google Sheets.
I have given special attention to this in my earlier chart related tutorial here – How to Format Data to Make Charts in Google Sheets.
In the above-shared guide, I didn’t include how to format the data series. My focus was mainly on the data orientation and order.
In this Google Sheets tutorial, let me help you to plot a line chart using lap times in milliseconds in Google Sheets.
As a side note, the same formatting is also applicable to Bar/Column graphs.
Formatting the Lap Times to Create a Line Graph
As I have said above, MM:SS.000 is wrong formatting of the lap time. It looks like a correct format. But Google Sheets will treat these time values as pure text.
If your lap times are entered in the above format, you must convert that to HH:MM:SS.000 format. How?
In the beginning, I’ve mentioned about a formula to do that. find that in this guide – How to Format Time to Millisecond Format in Google Sheets.
The formatted data must look like as below. That means you must include the hours’ digit “00:” with the lap times.
Using this data you can easily plot a Line graph in Google Docs Sheets. See the below step by step instructions.
Steps to Create a Line Chart Using Lap Times in Milliseconds Format
Step 1: Data Selection and Nature of the Data.
Select the data in the array A1:C6. In this, column A contains the circuit numbers, column B and C contains the time taken by the first and second driver in each circuit respectively.
Step 2: Chart Editor Panel Settings.
Open the chart editor panel. To open, go to Insert > Chart. Then select the chart type that you want. You can look that for under the “Setup” tab.
Recommended chart types are Column, Bar or Line chart. I am creating a Line chart here.
Step 3: Other Chart Settings.
Make sure that the other settings under the “Setup” tab are as as follows. Google Sheets will automatically set these correctly. If not, adjust it manually.
Step 4: Finished Chart.
Follow the above instructions to create a Line chart using lap times in milliseconds format in Google Sheets.
Chart Related Tips and Tricks:
- How to Get Dynamic Range in Charts in Google Sheets.
- Get a Target Line Across a Column Chart in Google Sheets.
- How to Create a Multi-category Chart in Google Sheets.
- How to Move the Vertical Axis to Right Side in Google Sheets Chart.
- Bar or Column Chart with Red Colors for Negative Bars in Google Sheets.
Hiya, nice post thank you. Can you help, my charts lose the info when shared or published and can’t chart the lap times in data studio either?
Many thanks