Pivot table-finding conversion ratio

Finding Conversion ratio using Pivot Table Calculated Items

Today, lets understand how to use Calculated items feature in Pivot tables. We will use a practical problem many of us face to learn this feature – ie calculating conversion ratio from a list of sales calls.
I have a spreadsheet source data full of sales enquiries which have the Status – Lost, Booked or Pending. Each sales enquiry relates to a particular location. I have created a pivot table which counts the enquiries and displays them with the Locations in rows and the Status in the columns. I have got a row total showing the total number of sales enquiries for each location. I also want my table display the sales conversion number, ie the booked enquiries as a % of the total enquiries. How do I do this?
 
A look at the data
Lets say, you have some data like this and you want to understand what is the conversion ratio by location.

Calculating conversion ratios from sales enquiry data

Setup a pivot table

The first step is to just create a pivot table from this data. Put locations in row labels area, status in column labels are and ID in values area. Now you will have a count of items for each status in each location. Something like this:

Frist version of pivot table - showing distribution of items by status

Add a calculated item to get conversion ratio

Now we want to calculate how much percentage is “booked” status items in all items for a location. To do this,

  1. Select any column label item in the pivot table.
  2. Click on Pivot Options > Fields, Items & Sets > Calculated item
    Inserting calculated item from pivot table options ribbon
  3. Give your calculated item a suitable name like Conv. %
  4. Write the formula = Booked / (Booked + Pending + Lost)
    Calculated item for conversion percentage - Excel pivot tables
  5. Click ok.

Now you should see another column in your pivot table with calculated item – Conversion %.

Conversion ratio calculated - but shown as number...!

Formatting Conversion % in Percentage format

While we got what we wanted, it is not looking alright. We need to format the conversion % so that it looks alright. For this,

  1. Right click on any value in pivot table
  2. Custom number formatting rule to show conversion ratios in %sGo to value field settings
  3. Click on number format
  4. Select custom
  5. Type the custom formatting rule [>=1]0;[<1]0%;”"
  6. This will automatically transform all numbers smaller than 1 (ie all conversion %s) to percentage format while keeping everything else normal.
  7. Done!

Attached File : 304961 1206232 conversion ratio.xlsx downloaded: 211 times
Replies (1)

Thank u for the useful post....keep sharing :)

Leave a Reply

Your are not logged in . Please login to post replies

Click here to Login / Register  

Company
12 November 2026
Auditor

Oz Taxation

Vijayawada

Graduate (Any)

View Details
Company
20 November 2026
Charted Accountant

Cothas Coffee Co.

Bengaluru

CA

View Details
Company
Featured ARTICLESHIP 31 October 2026
CA Articleship

apricus india

Navi Mumbai

CA Inter

View Details
Company
16 November 2026
Indirect Tax Associate

Sekhri Kanodia & Associates

Mumbai

B.Com

View Details
Company
ARTICLESHIP 20 November 2026
Article clerk

Jain Sanjay Kumar & Co

Jaipur

CA Inter

View Details
Company
13 November 2026
Chartered Accountant - Fresher

Neeraj Bhagat & Co

New Delhi

CA

View Details
Company
13 November 2026
Accountant

New Mobile India

Muzaffarpur

B.Com

View Details
Company
Featured 17 November 2026
Chartered Accountant

M/s. A V C & Co.

New Delhi

CA

View Details