Common

Use of Pivot Tables for Reports

- 13. March 2017 - frank

Pivot tables are a perfect data summarization tool you can use for CRM reports.

Task: Compare yearly revenue data

Solution: Summarize revenues by Pivot Tables

Example:

Create 2 CRM invocie reports which include the revenue of 2015 and 2016. Make sure you that you include dates.

Revenue 2015
Date Customer Revenue
01.02.2015 Customer A 10100
04.03.2015 Customer A 20200
16.04.2015 Customer C 5000
30.05.2015 Customer 80000
11.06.2015 Customer C 12000
13.06.2015 Customer C 3200
25.08.2015 Customer B 200
03.12.2015 Customer A 1030

 

Revenue 2016
Date Customer Revenue
11.02.2016 Customer B 5100
14.03.2016 Customer B 200
12.05.2016 Customer A 6000
11.05.2016 Customer A 11000
17.06.2016 Customer C 17000
13.06.2016 Customer C 16200
25.09.2016 Customer B 200
07.11.2016 Customer A 2030
04.12.2016 Customer A 6000
06.12.2016 Customer A 4200

 

Put both reports into one table:

Revenue 2015 and 2016
Date Customer Revenue
01.02.2015 Customer A 10100
04.03.2015 Customer A 20200
16.04.2015 Customer C 5000
30.05.2015 Customer B 80000
11.06.2015 Customer C 12000
13.06.2015 Customer C 3200
25.08.2015 Customer B 200
03.12.2015 Customer A 1030
11.02.2016 Customer B 5100
14.03.2016 Customer B 200
12.05.2016 Customer A 6000
11.05.2016 Customer A 11000
17.06.2016 Customer C 17000
13.06.2016 Customer C 16200
25.09.2016 Customer B 200
07.11.2016 Customer A 2030
04.12.2016 Customer A 6000
06.12.2016 Customer A 4200

Follow the instructions you can find:

http://www.excel-easy.com/data-analysis/pivot-tables.html

or

https://support.office.com/en-gb/article/Create-a-PivotTable-to-analyze-worksheet-data-a9a84538-bfe9-40a9-a8e9-f99134456576