Seemingly elegant solutions for excel intermediate

  • Thread starter edward.montague
  • Start date
E

edward.montague

Hi. I have a list of data that is about 350 rows dwon by about 100
across, and need elegant ways to present this data. The data is of a
list of stocks from about 10 countries and from about 10 sub-sectors.

If using the data in one worksheet and using dropdown filters, is
there formula that can average/sum etc only data that is presented on
the, and which ignores the hidden lines?

If I would like to replicate the data from this front sheet in
seperate worksheets divided up by country and subsector, what would
you recommmed to do this?

Many thanks for any help - much appreciated!
 
K

KC Rippstein

Pivot tables is certainly a great way to go, but if for some reason you
don't have time to learn them, the function you are looking for is called
SUBTOTAL. Excel's help gives you a list of the available functions in
SUBTOTAL, which can do sum, average, min, max, count, etc. of just the
visible rows.
If you're using Excel 2003, you can just highlight your entire data table
and go to Data -> List -> Create List. If you set it up this way, then just
go back to Data -> List -> Toggle Total Row and you will see that they have
already built in that SUBTOTAL function for you into the totals row and you
simply select the word for what you want the total to be (Average, Count,
Sum) instead of trying to remember the formula's syntax.
- KC
 
Top