Excel is the famous application of Microsoft Office. And the useful Excel feature is the Pivot Tables. This helps you to extract the impact from a detailed, and large data set. You can quickly sum up many table records in single report. For help, you can contact Microsoft via Office.com/setup. In this blog, you will read how to analyze Trends by using Pivot Table.
Method to Analyze Trends By Using Pivot Tables : Www.Office.com/setup
1. Create a Pivot Table: First, in the table you should click on any Cell. After this, you should go to “Insert” tab. Then, you have to click “Pivot table” button. At last, you should click on OK button.
2. Group Data: You should click and hold on Date in Pivot table field list. Now drag and then release “Row Labels” area. After this, in the pivot table you should right click on any date. Then, you have to click on Group. At last, you should choose months, quarters and Years and then press OK.
3. Analyze Data (Pivot Table): First, you should add “Amount” to the Pivot table. Then, you have to click and then in the pivot table field list you should hold “Amount“. And then drag and release over Values area. Here, the entire sales in each year are shortened. You should click on any + sign just to expand the particular year data. At this point, you have to grouped data into years, quarters and months, and the next “level” is quarters. Again you have to click + sign on a quarter and then you will see the months are displayed. Now you can collapse and expand the entire fields with one click. After this, right click on any data and then choose “Expand Entire Field”.
4. Compare Performance, year to year, quarter to quarter, month to month: First, you should click on “Sum of Amount” field and then click on “Values” area. After this, you should click on “Value Field Settings” and then go to “Show Values As” tab. Here, you should choose Show values as “Difference From” and then choose Years in Base field. You should select (previous) in Base item and then click on OK. At this point, you should see a sharp decline in sales in 2019 and then expand 2019. Remember, there are no +/- values in the quarters because one year is expanded. Here, you have to compare it with the year 2018 and expand year 2018. Now, you can view the sales were good in the first three quarters but the fourth quarter was poor. At this point, you should compare sales to year.At last, just see the values in the +/- column are changed! And you can compare the 2019 quarters to quarters 2018.
5. Product Trends: First, you should click “arrow” near “Row Labels”. Then, you have to choose the year 2018 and 2019 only. Now, you should remove “Sum of Amount” in Values “Field”. And then you have to drag the products from Pivot table field list to Column area. Here, you can see the “Access points” and “Modems” are having the largest declines in 4th quarter sales.
6. Region Trends: In the values area, you should click on “+/-“. Then on Value Field Settings, you should click on change custom name to “%”. And then you should change Show values as: % Difference From. You should click on OK and then remove Products from Column Labels area. At last, you should Add Region to Column Labels area. Now, you will view the entire markets show a decline in sales. The above method helps you to analyze trend by using Pivot Table. For more details about Excel, just go to site of MS Office via Office.com/setup.