How to show autosum when filtering data? (Office 365 for Mac)

[smithdan]

I just updated from Excel 2008 to MS365. Yes, a huge leap, I know!

My 2008 spreadsheets had a fixed/frozen row at top that I can use to FILTER my data. For example, I could click on the "clients" column header and a drop down list with all client values appears, and I select a name e.g. Jones and then see all entries (rows) for Jones. I also see my bottom row of AUTOSUM values, which now shows only the sums for these Jones entries. I think I had to make my spreadsheet a "list" or something like that to get this functionality.

Excel 365 is presenting me with a few problems.

1) I miss the easy drop-down list created just by clicking on the column header in 2008. But I can manage the multi-step filtering process in 365.

2) When I filter for a column such as "client", the autosum row that I have at bottom of my spreadsheet disappears, so I have to use a calculator to sum up the various columns for that client. How can I get an autosum of filtered results?

3) In Excel 2008 I always had a blank row above the autosum row, all I had to do to input a new entry was to click in that blank row and fill it out. Once I completed the row, a new blank row appeared below it. (This row had an asterisk for the row# until it was filled and a new blank row appeared.) Now, in 365, I have to right click on the last row and insert a new row above it. I can click on the autsum row and insert above that, but sometimes that new row will get omitted from autosum or not take on the formatting of my regular rows, so I click on the last entry and insert row above it to be safe. I wish there was a way to insert a row BELOW instead of above.

Can anyone help me fix these problems? Especially the autosum - I really need to see an autosum of my filtered results!  Thank you!

[#2]

@smithdan 

I suggest you should adopt the use of Excel Tables for input data.  The total row is a selectable part of the Table and will contain a formula such as

=SUBTOTAL(9,[value])

=SUBTOTAL(109,[value])

=SUM([value])

To ignore filters use the SUM.

[#3 · Accepted solution]

@smithdan 

Your first concern will be to get your existing spreadsheets up and going but, once that is done, there are many opportunities for refactoring the solutions to make then more transparent or flexible.  For example rather than hiding rows using a filter (rather like playing a baby game of peek-a-boo) it is now possible to build a new range containing the filtered records by using the FILTER function to extract the data.  CHOOSECOLS can be used to select any particular field you wish and SUM would produce a total.

Since the range will be dynamic, a totals row at the bottom will get in the way so it is quite common to place the totals at the top of the table.  Otherwise VSTACK will allow the totals row to be placed at the foot of the table but in such a way that it moves as the table resizes.

Don't let what you already know get in the way of what you could achieve.

[#4]

@Peter Bartholomew Thank you, Peter. I converted to a table and it is working great.

[#5]

@smithdan 

I am pleased that things appear to be working out for you.  As you said, it is a huge step and I don't think there is yet sufficient experience of the new methods to be sure they are being used to full advantage.