Who is the odd man out? (Part 2)

To recap, the aim in finding an inconsistent cell is that the inconsistency may indicate an error, or an inconsistent design.

Excel provides a convenient way to identify a cell that is inconsistent with its neighbors. It puts a green triangle at the upper left corner.

This is shown in the following diagram. There are two disadvantages.
1. Inconsistent cells at the end of a column or row are not highlighted.
2. The green triangle (or error flag) is also used to indicate other types of errors. The different errors are listed in Home/Options/Formulas/Error Checking Rules.




There is another to identify inconsistent cells.
This is done by first selecting the "consistent" block of cells (C1:C6 in this case), then select
Home /Find and Select / Go To Special / Column Differences.

The result is as shown.

"Column Differences"

Note that two cells are highlighted (C4 and C6).
This method may be harder to use if the block of cells stretches over a screen.

In part 3, we show how trace precedents can be used to identify inconsistent cells.



Comments

Post a Comment

Popular posts from this blog

I want to trace precedents in Google Sheets

I want to trace precedents in Google Sheets. It is not possible to do that in Google Sheets. The closest way is to open the file with Excel or LibreOffice Calc. But there are compatibility problems with special formulas and macros. (Update: Please see the comments for this post)
Read more

LibreOffice Calc Users have Powerful Precedent Tracing Tools

LibreOffice Calc has a Detective menu to trace precedents and dependents. Similar to Excel's formula auditing tools, the tracings are limited to one cell at a time, and each click goes one level further. This new book,  Easily understand your spreadsheet model and detect errors with influence charts (For LibreOffice Calc), improves the detective tracing ability with LibreOffice Basic programming. Calc users can now trace precedents and dependents over many cells, or many levels at once. There are more functions described in the book. This unique spreadsheet book shows you how you can quickly and easily gain an overview of your spreadsheet model, with just a few clicks. The automatically generated overview, with the specialized software (VisualCheck, tested with LibreOffice 5.3) that comes with the book, allows easy understanding and error detection, as illustrated in the book. This book is meant for all types of spreadsheet users. It is meant for both novice and exp...
Read more