Excel Hidden Data Warning

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.

hiddencolumns03

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).

hiddencolumns04

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.

hiddencolumns05

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.

hiddencolumns06

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.

hiddencolumns07

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.

socialmedia01

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

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/

Leave a Reply

Close Menu