How to add a reference line in excel pivot chart
And drag the products from Pivot table field list to Column area.Now remove “Sum of Amount” in Values “Field”.First, click “ arrow” near “ Row Labels”.Here know which products show decline in 4 th quarter sales in 2011. You can see the values in the +/- column are changed! And compare the 2011 quarters to quarters 2009.You can quickly compare sales to year 2009 > collapse 2010 and expand 2009. Sales were pretty good in the three first quarters but the fourth quarter was terrible.You can now compare sales to year 2009 and collapse 2010 and expand 2009 You can see the sales were good in the first three quarters but the fourth quarter was poor.Now compare it with the year 2010 and expand year 2010. Note there are no +/- values in the quarters, because one year is expanded.You can see a sharp decline in sales in 2011 > expand 2011.Choose Show values as “ Difference From” > choose Years in Base field.Then, click “ Value Field Settings” > go to “ Show Values As” tab.First click on “ Sum of Amount” field > “ Values” area.Now add the pivot table field “ Amount” to the Values as done in step 3 and follow the below given steps. Compare Performance, year to year, quarter to quarter (previous year), month to month (previous year) 16 Advanced Excel Skills To Succeed at OfficeĤ.7 Underrated Excel Functions That I Wish Knew Before.8 Easy Excel Filters To Save Time, Money and Get Accurate Data.Now right click on any data > choose “ Expand Entire Field”. After that you can collapse and expand the entire fields with one click.Again click + sign on a quarter and you can see the months are displayed.Well as you grouped data into years, quarters and months, the next “ level” is quarters. And click on any + sign to expand the particular year data.Now the entire sales in each year are shortened. Then click > in the pivot table field list hold “ Amount“.First add “ Amount” to the Pivot table.
#How to add a reference line in excel pivot chart how to#
Well, in this way you can group the data, know follow the ways how to analyze data with a Pivot table. Now in the pivot table right click on any date.Then click > hold on Date in Pivot table field list.Your pivot table looks like the below-given diagram. Then know how to analyze trends using pivot tables. Very firstly, you need to create a Pivot table in Excel. Know Learn How to Analyze Trends Using Pivot Tables If you want to become more productive in Excel and want to learn Pivot Table the powerful Excel feature, then visit Learn Advance Excel Easily. But before moving further here I have something for you. So today in this article I’ll show you how to use Pivot table to analyze trends. The pivot table is very intelligent and very well knows to compare selected expanded quarters or months. With the help of pivot table, it is possible to spot sales trends and examine the underlying data fast, for example: on regions or product, particular years.