Creating a chart with only top 10 in a series

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

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

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 ?

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

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

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