Excel: How to graph a multiple line chart by date and country

Question

I have the following dataset that I would like to graph in excel.Dataset The data is from OurWorldInData.org if you need the full dataset. It has a country, date, and new cases columns. I would like to make a line chart which shows each country's new case progression by date (each line is a country, see image below). This could be done, for example, for a number of developed countries. I can make the chart if I select the data manually. However, I need to update this on a daily basis so it would be very helpful if I can find a way to do this more easily. I tried vlookups, named ranges, etc.. but nothing has been working for me so far. Desired Result

Answer 1

Each day, I can download the file (manually or programmatically) to a location on my computer:

To make this easy, I'll just create another file that will analyse this data file. I create a new Excel file, then I use Data>Get & Transform Data>From Text/CSV. I select the raw data file and click Import. On the summary dialog, I click "Load":

This loads the data into the spreadsheet. From here, I can use Insert>Pivot Chart. I put location in Legend (series), date in Axis (categories) and 'new cases' in Values. After the chart is created, I found that the dates had been grouped to quarters and years. To undo that, I right-clicked the date field in the pivot table and chose 'Ungroup'.

I then right-clicked the chart and selected 'Change series chart type' and changed the chart type to a line chart:

Format and position your chart however you like it.

One last step to set up this analysis file is to go to the sheet with the data on it and right click the query name in the Queries & Connections tab and choose Properties:

In the properties dialog, ensure 'Refresh data when opening the file' is selected:

Each day, download the file and overwrite the file you downloaded yesterday. When you open the analysis file, the query will refresh and pull all the data into the file again, including the new day's cases.

You then simply need to refresh the pivot table to see the results:

This will be slightly easier than changing the pivot table data source range each day. Open the file, refresh the pivot.

Answer 2

1. Create a pivot table based on your table.
2. Add Location to the Legend (Series), add Date to Axis (Categories), add New cases to Values

This will be your pivot table's content.

3. On the PivotTable Analyze tab, click on Pivot Chart. Select Line chart.
4. To smoothen the lines, select a data series, right-click select Format Data Series.

Enable Smoothen line on the Fill & Line

5. Your chart will look like the picture below.

I have only use 3 countries and 3 dates based on the image from the OP. Add the others countries and more dates.