How to succeed in office politics using Spreadsheets!

Simple. Find glaring mistakes in spreadsheets created by other people. Obviously you have an upper hand now! Of course, you need to know how to find mistakes in spreadsheets. Read this article and learn the skill.

Photo credit: Alex E. Proimos / Foter / CC BY-NC

I am not a proponent of playing politics with your colleagues. I just used this title to attract your attention so that you learn some really useful – and critical skills.

How to find mistakes

Mistakes can happen at three levels – input data (raw data), processing and output (final report or chart or pivot tables). I am going to list all methods in this article and provide references to related articles.

Input data itself has mistakes

Mistakes could be different data types or missing values or all kinds of data entry or copy paste errors. For large data it is impossible to visually scroll and find out mistakes. The best way is to use the Pivot Table.  Read this article for details: New use of Pivot Table – Data Quality Audit

Look at potential formula errors

These green marks in the top left corner of cells means potential errors. Ideally, go to the final report and look for these marks. Each one could be a different error. You must ideally correct all of them before interpreting the formula results. “Formula Omits Adjacent Cells” is a common error. This occurs because originally there were few rows of data and the formula was added that time. Later someone added more rows to the data but forgot to update the formula.

These green marks is a goldmine for finding errors. Read these 5 articles to protect your interests while interpreting Excel files. Knowledge Pack: Green Marks in Excel

Look at the source data for Pivot Tables and Charts

Often, the pivot table is created once but data is added (appended) multiple times. Often, people forget to update the new, expanded range of data in Pivot table Source. This leads to outdated (and inaccurate) reports.

The solution is to use tables for raw data. In fact Tables are so important that I have written 13 articles about them. Knowledge Pack – Excel Tables

References to data inside pivot tables

Often we have final reports which contain direct references (e.g. =F10) to a Pivot Table. These are extremely error prone. Why? Because originally the reference may be pertaining to the desired output. But as Pivot Table changes or expands, the same cell may contain different (and wrong) data. This type of mistake is very rarely noticed because there is no error mark shown.

The solution is to use GetPivotData function while referring to any cell inside Pivot Table Data area. (Irritating feature = Extremely useful 🙂 GetPivotData, Paste Special Pivot – New way to use Pivot table data)

Check Vlookup formula range

Like Pivot Table, Vlookup ranges may also go outdated over time when more data is added. Check the range and you are highly likely to find omitted areas.

The solution: You already know! Excel Tables.

For key inputs and outputs check all linkages

Important inputs may be referred to in many formulas. The way to find these is to use Trace Dependents and Trace Precedents. Here is a good article to learn about these powerful features.

Try all this with YOUR spreadsheets as well

Of course, you don’t want others to catch you with wrong data and formulas in Excel files which you have created. Therefore, check all these things for every file you work on!

Enjoy