How to add a reference line in excel pivot chart

#HOW TO ADD A REFERENCE LINE IN EXCEL PIVOT CHART SERIES#

Next, youвЂ™ll want to look up to the Ribbon, and Select the menu from Paste under the Home tab. Then head on over to Select your entire chart so that it is surrounded by the silver band like this: Now, youвЂ™ll want to highlight these 6 cells in the table above and either right click or use a keyboard command to copy them. Make sure that if you use a formula in place of a fixed value that you use the same one for both rows. You also shouldnвЂ™t be concerned with constraining the data in your columns to a fixed value like we did-it can also be a formula! Your new table should look like this: The header that you use at the top of your second column, which we called вЂњSales Goal,вЂќ is the header that will appear next to your reference line in the chart legend. Never Fear! You can be here in a few simple steps.ДЇirst, create a new table on a blank portion of your grid that contains the value you want to plot (weвЂ™ll call this вЂњSales GoalвЂќ in our example, with a value of 16) and dummy x values of 0 and 1.

#HOW TO ADD A REFERENCE LINE IN EXCEL PIVOT CHART SERIES#

Have no fear-you can simply create an additional data series to plot your lineДЇor a year-end review meeting, youвЂ™d like to be able to show how all of your salesmen did in comparison to the average target monthly sales goal. Perhaps you need to place your line in a very exact location on your chart. Never fear, a few extra steps in Method 2 and your line can stay in the perfect position!

Say you add a reference line to a chart like this at 3.5, which is an important target for your data:ДЄnd then a value in your data changes, changing the value of the axis and leaving the line in a not-so right position for you to have to replace manually: Since the line is separate from your chart, every time you move or resize your chart you must also move or resize your line! Even without physically dragging your chart around alignment problems can arise. While this method is easy, it does have its downsides. Simply click the Insert Tab on the Ribbon and select Shapes.ДЇrom here, click on the straight line and click and drag the object onto your chart in the location that you desire. If your line does not need to be exactly positioned, then the easiest method by far is to use a Shape to add a line to your chart. Method 1: Simply Adding an AutoShape to Your Chart Here weвЂ™ll give you the steps to make these exciting visualizations-but we also want to hear uses you have for them, so tell us in a comment below! There is currently no specific built-in functionality for creating Reference Lines in Excel 2010, but there are still a few ways to add them to your chart. Have you ever wanted to add a horizontal or vertical line to your chart to indicate a key value, sales threshold, important date, or the average of your data? Are you looking to impress managers and convince clients with your superb charts? Reference Lines (see the dotted black line on the chart below) can be powerful tools in effectively communicating important points in your data. TodayвЂ™s blog post is brought to you by Cathy Harley a Program Manager on the Excel Chart team.

How to add a reference line in excel pivot chart