# Microsoft Office Application Help - Excel Help forum > Excel Charting & Pivots >  >  Creating a chart with only top 10 in a series

## AmberCockrell

I would like to create a chart where the chart only shows the top 10 items in the series. Currently, I am doing it "manually" where I determine the top 10 results, but I am sure there is a different way.  :Confused:

----------


## etaf

you could try a pivot table/pivot chart -where you can select the top ten

perhaps a sample of your data and the result you are trying to achieve would help us

----------


## AmberCockrell

Example of Top 5.xlsx

This is the data I was given. I need to have a daily chart of the top 5 issues. (the ones with the greatest value as the numbers represent minutes) I also need to do a chart for the top 10 issues for the week. I would like to make it to where I do not have to choose the top 5 or 10 each day/week.

----------


## etaf

i'm sure someone will give a better solution,
but with the way your data is structured , I would simply sort it horizontally

so select B2:W166 
Sort>custom> options > left to right 
and then choose 
row166
and Value 
High to low 
or 
low to high
now you have your top5 and top10
and can graph 

also the graph will be in descending or ascending order

see attached - took a few minutes to do

you could copy the data and transpose and then you would be able to create top10 and top5 charts using a pivot table- but that would probably take longer than just doing the sorting.

i transposed the data into a new sheet and then created a top5 and top10 chart 
see attached sheet with top10 and top5 charts

Now anyupdate to the transdata sheet could be quickly and simply refreshed on those two pivot chart sheets


depends on the process you have for importing the information into excel and if you could have a couple of tables that you update and then the charts would automatically be created

----------


## AmberCockrell

I would have to "sort" this everyday doing it that way, right? What I need is to be able to enter data each day on the corresponding row and chart what the top 5 issues are daily and weekly. I hope that I am articulating well what I need. Have a hard time putting it into words sometimes. Should I set up the data entered differently?

----------


## etaf

yes see my edit on my post #4

you may need a macro to extract the data 

you are physically typing this information into your table - is that correct ?

how do you show daily and weekly 
next update on your sample would be 6/8/13 which is tuesday

so you want a graph for tuesday 
how would you now define the weekly chart - do you wait till the end of the week is completed ?

----------


## etaf

OK 
so i had a play 
and not doing this by using a macro

in trandata sheet - i have simply transposed you data 
and now you enter the data in the column rather than the row

in the row however, you increment the number so that my formulas can pick up the latest day that you have completed 
this will now transfer the daily information across to the new sheet 
trandata (2) in the daily column 

the weekly also uses the number on row1 ofthe trandata sheet and then goes back 7 columns and adds up those numbers - so its always a running 7 days and not a week

now the sheet top5-10 will show a pivot table and chart - for the top10 items (and therefore the top5 also ) 
for weekly and for daily data 

we could have separate sheets for
top5 daily 
top5 weekly
top10 daily 
top10 weekly

so in theory
you update the column in trandata
you add the next number in row 1 of that column
and then the sheet trandata (2) automatically updates

you goto the sheet top5-10
goto pivot options and refresh 
now the weekly and daily data is updated and so is the chart 

anyway probably not what you want 

but it least a start

see attached

i'm sure someone will come up with some VBA/Macro to do this in much more robust and nicer way

----------


## AmberCockrell

Thank you. I was hoping it wouldn't be so difficult and involved.  :Smilie:  I will give this a try and see if it is what they are wanting. If not, they'll just have to do it manually. Which, if you think about it, is not all that difficult to begin with.  :Wink:  I apprecitate you taking the time to help!

----------


## AmberCockrell

Thank you. I was hoping it wouldn't be so difficult and involved.  :Smilie:  I will give this a try and see if it is what they are wanting. If not, they'll just have to do it manually. Which, if you think about it, is not all that difficult to begin with.  :Wink:  I appreciate you taking the time to help!

----------


## etaf

well , not much to do here

just enter the data - which they had to do anyway
then add one more entry in row 1 a number 
then goto a sheet and hit the refresh button 

so thats just 2 steps - add a number and refresh - a lot less that sorting and selecting the range and drawing a graph

----------


## GeneralDisarray

What's this data rolled up from?  Can you access the raw data (assuming there is in this case such data)? 

Seems like you could pivot the data to show a count and use built in excel filters to see the top 5, top 10, etc. (described here:http://www.contextures.com/excel-piv...p10.html#Using).  You could set up a pivot chart for the day or week..

Also, for weekly report you could assign each record another field for the weeknum (assuming there is raw data) by adding a column to use this formula:  =weeknum(date_field)  THat would give you the option to pivot out by Week.


--
If this data is being rolled up for you from some other set, and you can post that set I can show you what I'm talking about. :D

----------

