How to Plot a Time Series in Excel

This guide will explain how to plot a time series in Excel.

Excel is an excellent tool for several situations, such as organing data, summarizing data, and displaying data. Since it has many tools and features, we can utilize the built-in charts and tables to create a graph representing our data set.

And this guide will focus on learning how to create a time series in Excel. A time series is essentially a series of data points plotted in time order at successive equally spaced points in time. 

Furthermore, a time series is extremely useful when we want to show how our data set has changed over the course of time. And Excel allows us to easily customize our time series to fit our preferences and situation. 

Let’s take a sample scenario wherein we need to plot a time series in Excel.

Suppose you are tasked to create a sales report for a meeting. So the data you have contains the date of each sale made. Since you want to present your data visually, you opted to plot a time series in Excel based on the values found in your data set.

Great! Now we can dive into a real example of plotting a time series in Excel.

 

A Real Example of Plotting a Time Series in Excel

Let’s say we have a data set containing two columns. Firstly, the first column is the dates of the sales. Then, the second column shows the sales made on each date. So our initial data set would look like this:

Initial data set

 

In this case, we have two factors in our data set, which are the dates and the number of sales. And these two values will be used in plotting our time series in Excel. 

So a time series is a graph that shows data points plotted over time. Additionally, a time series comprises data points in time order, plotted at successive equally spaced time points such as daily, weekly, yearly, etc. 

Fortunately, it is effortless to plot a time series in Excel. Since Excel already has different ready-made and available chart types, we can simply choose an appropriate chart and plot our time series. 

Furthermore, there are so many options for adding a chart in Excel as the chart types are divided into seven. For instance, we have the column, the line, the pie, the bar, the area, the scatter, and the other charts. 

We can easily choose the appropriate chart type among the seven and choose a specific chart to use for our time series. 

To plot our time series based on our data set in Excel, we can simply head over to the Insert tab. In the Insert tab, we can find the chart tool that allows us to simply insert a specific chart in our worksheet. 

In this case, we will choose the scatter chart type, specifically, we will insert the scatter with smooth lines and markers chart. Then, we can move on with customizing our time series.

One of the best features of Excel regarding its charts is the ability to customize them. So we can customize the chart title, legends, and the series within our chart according to our preference. 

And the first thing we need to customize our time series to make it easier to read and understand is to change the format axis. To do this, we can simply head to the size and properties section and input a custom angle. Doing this will switch the x-axis around and make it easier for someone to read the chart.

Additionally, we can edit the chart title to whatever is more appropriate to our data set. Next, we can also add legends and change the colors of our time series. Lastly, we can choose to display the values of each data point. Otherwise, we can choose to display the category name or series name.

So all of this customization is easily done to make our time series easier to understand and personalized to our situation. And our final data set would look like this:

Final data set

 

You can make your own copy of the spreadsheet above using the link attached below. 

Amazing! Now let’s move on and explain the steps of how to plot a time series in Excel.

 

How to Plot a Time Series in Excel

In this section, we will discuss the step-by-step process of how to plot a time series in Excel. Additionally, each step contains detailed instructions and pictures to guide you through the process.

To apply this method, simply follow the steps below.

1. Firstly, we will insert our chart. To do this, we must first select our entire data set and go to the Insert tab. Then, we will select the Scatter icon found in the Charts section. Lastly, we will choose the Scatter with Smooth Lines and Markers icon in the dropdown menu.

Plot a Time Series in Excel

 

2. Secondly, the chart would automatically display the values found in our data set. Then, we can start customizing our chart. To do this, we can right-click anywhere on the chart and click Select Data. Alternatively, we can also double-click any part of the part.

Format chart

 

3. To make it easier to read, we need to switch the x-axis and y-axis. In the Format Axis panel that opens at the side, we will click the Size & Properties icon.

Plot a Time Series in Excel

 

4. Next, we will type in “-45” in the space beside the Custom angle. And this will rotate the x-axis labels to make them easier to read.

Plot a Time Series in Excel

 

5. If we do not need to adjust the labels, we can try customizing other parts of our chart. For instance, we can change our chart title. To do this, go to the Format tab. Under the Chart Title section, we will type in “Sales Report” for our new chart name. Additionally, we can also bold or change the font name and size.

Plot a Time Series in Excel

 

6. Next, we can also turn off the labels to declutter our time series. To do this, we will simply turn off the Legend button.

Plot a Time Series in Excel

 

7. Then, we can also add or display data labels to visualize the values of each data point. To do this, we will go to the Data Labels section. Next, we will check the box beside Value to display the sales value of each point.

Lastly, we will choose a Label Position in the dropdown menu. In this case, we will choose Top.

Plot a Time Series in Excel

 

Furthermore, there are still many ways we can customize our time series. To do this, we can simply check the different sections under the Format tab. So customize your time series as much as you need to.

8. And tada! We have successfully plotted a time series in Excel.

Final output

 

And that’s pretty much it! We have successfully explained how to plot a time series in Excel. Furthermore, we also discussed how to customize the time series to our preferences. Now you can simply follow the steps discussed to plot a time series in your work whenever you need it.

Are you interested in learning more about what Excel can do? You can now use the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like