Have you seen the articles that blame Excel for all kinds of business errors? In some cases, problems occur because rows were hidden, and that distorted the data analysis. To help avoid those problems, I created a sample file that shows an Excel hidden data warning, if rows or columns are hidden.
Help, Not Prevention
This solution should help you spot hidden rows and columns, but don’t depend solely on this when you’re doing critical work.
Use visual checks for filtered or hidden rows, or hidden columns, and remember to look for hidden sheets too.
As always, our goal is to make things idiot resistant, not idiot proof.
Hidden Row Warning
Here’s how I set up the Excel hidden data warnings.
At the top of the sheet, in cell B2, there’s a formula to check for hidden rows. It counts the missing rows, whether they’re hidden manually, or by a filter.
In this screen shot, no filters have been applied to the table, but rows 12:13 were manually hidden, so the formula result is 2.
Hidden Rows Formula
Here’s the formula that’s in cell B2:
=COUNT(Table1[OrderCount]) – AGGREGATE(2,5,Table1[OrderCount])
First, the formula counts all the numbers in the OrderCount column. That column has a simple formula that returns a 1 in each row.
=1
Counting those cells should give an accurate count of the number of rows in the table.
=COUNT(Table1[OrderCount])
Count the Visible Rows
Next, the AGGREGATE function counts the visible cells in the OrderCount column.
AGGREGATE(2,5,Table1[OrderCount])
AGGREGATE uses function type 2 ( COUNT), and is set to ignore hidden rows (option 5).
Number of Hidden Rows
In this example, there are 21 rows in the table (COUNT) and 19 visible rows (AGGREGATE)
To find the number of hidden rows, subtract the visible rows from the total count, and the result is 2 hidden rows.
Hidden Column Warning – Attempt 1
Unfortunately, AGGREGATE doesn’t work for columns, just rows, so how can you tell if columns are hidden?
A hidden column would have zero width, so I used the CELL function to check the cell widths in the top row, cells A1:J1.
=IF(CELL(“width”,A1)>1,1,0)
Then, in cell B3, a formula subtracts the sum of those cells, from the count of the cells.
=COUNT(A1:J1)-SUM(A1:J1)
In theory, that solution works, but the results didn’t automatically update if columns were hidden or unhidden. In this screen shot
- D and E have been unhidden, but are still showing zeros
- F, G and H are hidden, but are still calculating as 1
- The Hidden Columns total shows 2, instead of 3
To see the correct number of hidden columns, you can press F9 to recalculate.
Hidden Column Warning – Attempt 2
An Excel hidden data warning isn’t too helpful, if you have to remember to recalculate.
But, as a conditional formatting rule in cells A1:J1, it seems to work nicely. I’ve only tested in a small file though, so your results might be different.
With cells A1:J1 selected, I created a new formatting rule, using this formula:
=CELL(“width”,B1)=0
A cell turns yellow, if the cell to its right is hidden (0 width).
In this screen shot, columns D:E and H:I are hidden. As a result, cells C1 and G1 have yellow fill colour, based on the conditional formatting rule.
Warning About the Warning
Charles Williams found that conditional formats “are not executed at a calculation unless they are on the visible portion of the screen“.
So, if you try this Excel hidden data warning technique:
- Be sure to lock the top row.
- Recalculate too, just to be sure that the correct cells are coloured.
- Before you do any critical data analysis, do a visual check to see if any rows or columns or entire sheets are hidden.
Remember, as the old saying goes, it’s better to be safe, than to read about your catastrophic errors on the internet.
Download the Excel Hidden Data Warning File
To download the Excel hidden data warning workbook, go to the Conditional Formatting Examples page on my Contextures site. In the Download section, click on the link for the Hidden Data Warning sample.
The zipped file is in xlsx format, and does not contain any macros.
________________
Excel Hidden Data Warning
________________________
The post Excel Hidden Data Warning appeared first on Contextures Blog.
Original source: http://blog.contextures.com/archives/2018/04/05/excel-hidden-data-warning/