Excel Pivot table functionalities

Question

I have a table with thousands of rows of data regarding sold properties spanning over the following columns: Ref. number (starting from 1 and ending with the last entry) Agent Postcode Price Date

I am required to display the top 5 agents by sales of properties in the price range £500,000 - £1,000,000 in NW3 in the period 1st April 2018 – 31st March 2019.

How should I go about this? My pivot table does not help me with filtering the price range, unless I put that as a row instead of a filter. The date would also require that and together they are a mess. Not to mention that it displays them all rather than applying that only as a filter.

Answer 1

• Add a 6th column named "Price range" to your raw data.
• Then go to the "Price" column and use 'Number Filter' > 'Between' > is greater than or equal to £500,000 And is less than or equal to £1,000,000
• Enter "£500,000 - £1,000,000" in the newly added column "Price Range" for the filtered entries
• Create a new pivot for the 6 columns of raw data and use "Price range" under Report Filter to list the properties you wanted to see
• Add "Date" to the Report Filter as well and select the dates.

This should give you the results you need