Tag Archives: SUBTOTAL

Apply Conditional Format for alternate row color based on groups…

Posted on June 8, 2019 by MF

…that works even when filter is applied. The situation: We have a table that we would like to apply color banding based on groups.  We can achieve this by inserting a helper column to identify the sequence of each group, … Continue reading →

Rate this:

Posted in Formula | Tagged , , , , | 2 Comments

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

Posted on August 22, 2015 by MF

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

Posted on May 9, 2015 by MF

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 , | 24 Comments

Data ==> Subtotal

Posted on March 28, 2014 by MF

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

Posted on March 26, 2014 by MF

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 , | 11 Comments