# Formula to calculate most common occuring cell value

#### jayneinaccounts

##### New Member
Hi

I have downloaded our phone bill into a .csv file. I now would like to analyse it, and find out the most commonly called number.

Does anyone know which formula I would need to do this?

Thanks for looking

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

#### Chantty

##### Board Regular
Try pivot table

Drag Telephone no. in row filed
Drag Telephone no. in data filed, then u will count of telphone nos' that may help you for analysis.

#### Richard Schollar

##### MrExcel MVP
Hi Jayne

The easiest way to do this would probably be to construct a pivot table from the data using the Telephone number as both the row field and the data field and set the data field to summarise by Count. Then you will immediately see how many times each number has been called.

#### jayneinaccounts

##### New Member
Thanks guys! First time I have used pivot table! Its now got me thinking! My pivot table has a list of numbers in column A and the number of times each number has been called in column B. Could I add in two further columns from my data for total duration of calls and total costs? If so, where would I need to drag those fields to? left, top or data section?

##### Board Regular
Hi
You could try the MODE function on your worksheet - it should tell you your most frequently occurring entry

#### Richard Schollar

##### MrExcel MVP
Jane

Drag them into the Data section, make sure they are summarised by Sum (rather than Count). To make them appear in adjacent columns, once you have dragged them into the pivot table, drag the Data button on the Pivot Table over the Total heading in the pivot table.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,168,184
Messages
5,857,827
Members
431,900
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back