Sales conversion

[nielf]

I'm trying to figure out how I can calculate certain sales conversions in my Sales Pipeline automatically. My pipeline has the following statuses: Lead -> Enquiry -> Tendered -> Awarded.

For instance, I would like to know which projects that has converted from Lead to Enquiry.

My data file is updated once every month with the latest data set as shown below. I'm hoping it's possible to create a solution that use slicers so it's easy to select what you want to see. E.g. you can easly switch between different conversions:

Lead -> Enquiry
Lead -> Tendered
Lead -> Awarded
Enquiry -> Tendered
Enquiry -> Awared
Tendered -> Awarded

Is what I'm trying to achieve possible at all in Excel? If yes, can you help me get started?

 

[Andrew Poulsom]

Have you tried a Pivot Table?

 

[nielf]

Yes, I'm working with a PivotTable.

If I do a count on Project ID with Status as column label and and Project ID as row label I can see which Project IDs that has been in which status. Something like this:

I'm looking for a solution where I can use a slicer to switch between the conversions. If that's possible at all, would that then require a nested IF statement of some sort?

 

[Andrew Poulsom]

If you select a cell in the Pivot Table you should be able to choose Slicer from the Insert tab on the Ribbon.