How to subtotal only the visible cells after filtering in Excel?
Supposing you have calculated the total value from a column with the Sum function. When filtering data in the column, the Sum function adds both the visible cells and the hidden cells. If you only want to subtotal the visible cells after filtering, methods in this tutorial will help you.
1. Select a blank cell to output the result, click Kutools > Kutools Functions > Statistical& Math > SUMVISIBLE. See screenshot:
2. In the Function Arguments dialog, select the range you will subtotal and then click the OK button.
Then you can see a formula is created in the selected cell. When you filter the column data, only the visible cells are summed.
If you want to have a free trial (30-day) of this utility, please click to download it, and then go to apply the operation according above steps.
Only delete visible rows or columns in Excel In Excel, we usually hide some important rows or columns of information, sometimes, we need to delete the visible rows or columns only, if you delete them with Delete key directly, the hidden values will be deleted at the same time. How do you delete the visible rows or columns without deleting the hidden rows or columns in Excel? This tutorial will introduce the ways to delete visible rows only.
Sum values without or exclude subtotals in Excel Supposing you have a list of data mixed with several subtotal cells, when you need to sum the total, all the subtotals are included in the final summing. See screenshot below. For summing values without the subtotals in Excel, what would you do? In this article, we will show you a quick method to achieve it.
Filter multiple columns simultaneously in Excel When you apply the Filter function, after filtering one column, the next columns will be only filtered based on the result of the previous filtered column. It means that only AND criteria can be applied to more than one column. In this case, how could you apply both the AND and OR criteria to filter multiple columns simultaneously in Excel worksheet? Thy the method in this article.
Filter or select cells by cell color in Excel Normally you can fill cells with different colors for any purposes in Excel. If you have a worksheet with using different colors to indicate different type of contents and you want to filter or select those cells by the cell color, you may get it done with the methods in this tutorial: