Jeff Prom's SQL Server Blog

Sharing knowledge and tips on SQL Server, Business Intelligence and Data Management.

Adding a 12 Month Trendline in Excel 2013

Posted by jeffprom on March 21, 2013

Trends over time can be a great way to track business health and goals. It’s common to track such metrics on a daily basis throughout the year. Week by week you may have several peaks and valleys. Adding a trend line is a good idea to help smooth out the spikes and better analyze the trending values throughout the year. In this post, I will show how to add a trend line.

First let’s start with collecting data. You should have a column for each day of the year, and another column for metric values you want to track.
Trend Line Data

Next, highlight all of the data values but do NOT highlight the column names. On the ‘Insert’ tab, click on the ‘Line Chart’. Your chart should like the one displayed here.

Insert Line Chart

Now that we have a line chart, let’s add the trend line. Right click on the chart line and click ‘Add Trendline’.

Add Trendline

The Trendline Options are now displayed. There are three tabs to work with; ‘Fill & Line’ (Left), ‘Effects’ (Middle), and ‘Trendline Options’ (Right).
We’ll take a look at each tab, but let’s first start on the right most tab (Trendline Options). Since we are adding an annual trend line, select the ‘Moving Average’ option and set the Period to 12 for 12 months.

Moving Average Trendline

Next, let’s jazz up the line a bit. Click on the left tab for ‘Fill & Line’ options. Here you can select all kinds of line options such as style, size, and color.

Trendline line options

The middle tab is for ‘Effects’ where you can add things such as glow, shadow, and soft edges. A couple of nice ones to set here are the Presets and Color. On Presets, select the top left Outer option called ‘Offset Diagonal Bottom Right’. On Color, select Black to give a nice drop shadow effect when combined with the above mentioned Presets option.

Trendline effects

After you are done formatting the trendline to your liking, simply close the Format Trendline options and you should now have a nice annual trendline.

Annual Trendline

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: