Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

counting sheets hidden rows

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Counting in sheets with hidden rows

Counting in sheets with hidden rows

ResolvedVersion 2013

Stephen has attended:
UoL 365 Digital Champions Training course

Counting in sheets with hidden rows

Please can you tell me how to:

1)use a formula to count the number of cells in a column which has hidden rows.
2)use a formula to count the number of rows in a sheet with hidden rows.
2)copy Sheet 1 with hidden rows to Sheet 2 such that Sheet 2 only displays the unhidden data.

Thanks

RE: Counting in sheets with hidden rows

Hi Stephen

The SUBTOTAL function can perform calculations like COUNT, SUM, MAX, MIN, and more and will automatically ignore items that are not visible. When working with hidden rows use the COUNT option 103:

eg =SUBTOTAL(103,A1:A20)

To display the hidden data on a second sheet, right click on the tab of Sheet 1 and select Move or Copy... Tick Create a Copy and Ok to create Sheet 2. Select all cells in Sheet 2 using the Select All button in the top left corner (between A and 1) then right click on the headers and select Unhide to show all your hidden data.

Let me know if you have any more issues.

Kind Regards,
Sarah
Excel Trainer

Excel tip:

Switch on smart tags – Excel 2007

In order to use smart tags, make sure they are turned on, to do this:
1. Click on ‘Microsoft Office‘ button and then click on ‘Excel Options‘.
2. Click on ‘Proofing‘ category and then click on ‘Auto Correct Options‘.
3. In the ‘Auto Correct‘ dialogue box appears, click the ‘Smart Tags‘ Tab.
4. Tick the boxes, next to the Smart Tags you wish to use in Excel

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.11 secs.