[Khoshravan]

I have a table and I filtered to get specific rows. Now I want to define a
formula in only visible rows. Like subtract the lower cell from the above
cell but this becomes: AI15=(L15-L4) in filtered table. Up till here it is OK
Now I want to drag this formula down but it perform the formula if the
hidden rows are visible. AI30=(L30-L19)
Is there any solution to this?
--
R. Khoshravan
Please click "Yes" if it is helpful.

[ExcelBanter AI]

Yes, there is a solution to this. When you drag a formula in a filtered table, Excel will perform the formula in all the rows, including the hidden ones. However, you can use the SUBTOTAL function instead of the regular function to perform calculations only on visible cells.

Here are the steps to follow:

1. Select the cell where you want to enter the formula (AI15 in your example).
2. Enter the formula using the SUBTOTAL function instead of the regular function. For example, if you want to subtract the lower cell from the above cell, you can use the following formula:
   Formula:

   =SUBTOTAL(9,L15:L4)-SUBTOTAL(9,L14:L3) 

3. Press Enter to apply the formula to the selected cell. The result will be displayed in the cell.
4. Now, select the cell with the formula and drag it down to the cells where you want to apply the formula (AI30 in your example).
5. Release the mouse button to apply the formula to the selected cells.

Excel will now perform the formula only on the visible cells, even if you have filtered the table. The SUBTOTAL function ignores hidden rows and performs calculations only on visible cells.

[DILipandey]

Hi Khoshravan,

You can copy the formula the select the filtered range, where you want to
paste this formula, now press alt+; on your keyboard and paste- special -
formulas. This will paste the formula only on visible cells.

Please click on "yes", if it helps. Thanks.
--
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)


New Delhi, India


"Khoshravan" wrote:

I have a table and I filtered to get specific rows. Now I want to define a
formula in only visible rows. Like subtract the lower cell from the above
cell but this becomes: AI15=(L15-L4) in filtered table. Up till here it is OK
Now I want to drag this formula down but it perform the formula if the
hidden rows are visible. AI30=(L30-L19)
Is there any solution to this?
--
R. Khoshravan
Please click "Yes" if it is helpful.