Excel 2010: Making a top 10 callers chart

Posted on 2011-10-26
Last Modified: 2013-11-05
I need to make a chart in excel that will show the top 10 callers. One side would be how many times they called, and the other would be who they are. anyone have any ideas?
Question by:bachopper
    LVL 27

    Accepted Solution

    Use a Pivot Table to summarize the data and then turn on a filter to see the top ten results.  See this example.
    LVL 33

    Assisted Solution

    You can do it with a Pivot Table and displaying only the Top 10, like on the attached file.

    LVL 50

    Assisted Solution

    by:barry houdini
    Here's a formula based solution - see attached

    Sample formula to get the top callers from a list of Callers in Calls!A2:A500


    confirmed with CTRL+SHIFT+ENTER and copied down

    regards, barry

    regards, barry

    Author Comment

    ok, I'm stuck on the bold part. What is this supposed to be referencing?


    Here is the translated version so far. Is this correct?:
    LVL 11

    Expert Comment

    Not for points:

    ISNA()  - this means that if this formula would normally give the #NA error in the cell.

    He's doing a basic error check to make sure the match is there, otherwise just setting it to 0.


    Author Closing Comment

    Thanks guys, including ScriptAddict =)

    Ended up using a pivot table. Was able to create the table and the chart I needed and the boss man is happy. =)
    LVL 50

    Expert Comment

    by:barry houdini
    The point of the ISNA part is to exclude names from the cells above. It isn't technically required to get the top name so this would suffice in B2


    MATCH is used to get the row number of the first match for each entry. The most common number (found by MODE function) is also the position of the most common name, INDEX gets the name from that number.

    ...but in the next cell down, B3, I don't want to find the most common name again.....I want to find the most common name excluding those in the cells I use MATCH to see whether the names match the cells above, if the result is #N/A then I do want to consider those names, because they haven't yet appeared on the list, ISNA isolates those names. It's possible to use COUNTIF too, i.e. this version in B2 copied down


    Of course the formulas are referring also to B1 - the contents of B1 doesn't long as it isn't one of the names in the list

    regards, barry

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now