For a while now I’ve wanted to better understand simple data and see it charted out over time.

Imagine you are manually writing down your energy levels on a scale of 1-10 over the period of three months. You then want to take all that and quickly chart it out to see how you felt and figure out if there are any trends.

I manually did something similar and then had no easy way to interpret the data.

In this case I wish I’d have gone back and used a pivot table. As a designer without a strong background in math I’ve always been a bit overwhelmed by these.

Following are the steps to create a pivot table and hopefully have a better way of charting things.

First get the data in a consumable format. Column A should be the Dates, column B should then be the Scores.

Select the entire range and, using Google Sheets, go to Insert > Pivot Table.

Note: If Sheets suggests something, go with the first option, “Sum of Score, confidence for each created_at”

Under Rows add Dates and uncheck “Show Totals”.

Under Columns add Score, and select AVERAGE.

Then go to Insert > Chart in the menu.

You’ll now have a draggable chart that you can move around in your sheet. You can then double click it to change the Chart Type. My favorite is Scatter so that I can better understand trends and ignore outliers.