Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
When using Autofilter on Excel, how do I ignore 1st 1 or 2 rows
I want to use Auto filter to display certain categories of data, but ingonre
the first row beneath the Headings. Also can I display totals for each view of Auto filter I choose? ie where the list of deals displayed varies the total profit etc will also vary. At the moment all I get is the full totals etc Apolagies if this is a bit basic but have recently started own business and am finding difficulty innot havinf instant access to greater jnowledge then my own! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
When using Autofilter on Excel, how do I ignore 1st 1 or 2 rows
Although it might seem more natural to put totals at the bottom of a
list, I find it better to put them ABOVE the headings - that way, you can always see them when filters are selected. To do this, highlight the row containing headings and click Insert | Rows. You can then highlight the row with the totals in and cut/paste it to the row you have just inserted. To have the totals show only the total of the displayed items you should use the formula =SUBTOTALS(9,your_range) instead of =SUM(your_range) - instead of retypeing these, you can highlight your total row, then CTRL-H (Find & Replace) andy type SUM( in the box to find and SUBTOTAL(9, in the box to replace. Hope this helps, Pete |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
When using Autofilter on Excel, how do I ignore 1st 1 or 2 rows
To follow up, John emailed me directly asking how to treat
=COUNTA(D3:D29) and =AVERAGE(H3:H29) in a similar way. There are a number of functions that can be used with SUBTOTAL(x,range) - some of the common values of x a 1 - AVERAGE 2 - COUNT 3 - COUNTA 4 - MAX 5 - MIN 9 - SUM You can use Help - SUBTOTAL_worksheet_function to find the other values. Pete |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Isolate rows based on highlight (Excel 2003) | Excel Worksheet Functions | |||
How can I delete rows from Pivot Tables in Excel 2000 as in 97 | Excel Discussion (Misc queries) | |||
change excel row height without showing hidden rows | Excel Worksheet Functions | |||
Can Excel "slide up" rows with content thru empty rows to condense | Excel Worksheet Functions | |||
How do I remove blank rows in Excel? | Excel Discussion (Misc queries) |