Tag Archives: SUBTOTAL

Put a dynamic hint for showing or hiding rows or columns hidden by Data Group

This is about using SUBTOTAL and CELL function to detect whether a row and a column is hidden respectively. When rows are hidden by Data–>Group, you will see a +/- button on the leftmost of  spreadsheet to remind you that … Continue reading

Rate this:

Posted in Excel Tips, Formula | Tagged , , | Leave a comment

Sequential number for visible rows only

How to display a column of sequential number to visible rows only? Got this question from a friend.  Her task is simple as shown below: I guess many people wanted to achieve the same thing but have no idea at all.  Are … Continue reading

Rate this:

Posted in Excel Tips, Formula | Tagged , | 11 Comments

Data ==> Subtotal

How to insert thousands of rows for each change in item in a column?  And then give a subtotal of each item? Long long time ago (>10years) when I was only an Excel newbie, a friend asked for help.  She … Continue reading

Rate this:

Posted in Excel Tips | Tagged | Leave a comment

SUM vs. SUBTOTAL

How to SUM a range but ignores SUM result in between?, i.e. How to get a Grand Total with SubTotals in between? How to SUM but ignores hidden cells or filtered cells? SUM could be the most popular function used in … Continue reading

Rate this:

Posted in Excel Tips, Formula | Tagged , | 9 Comments