I wrote an article about Data Audit using Pivot long back. Now, I have converted it to a comprehensive video. Have a look:
How to perform Data Audit using Pivot
Raw data can contain many columns and thousands of rows. Checking the consistency and accuracy of data cannot be done by simply scrolling and eyeballing the data. Too time-consuming.
Continue reading Data Audit using Pivot tables
FormulaText function in Excel was introduced in version 2013. It requires just one parameter – the cell from which you want to display the formula. Even if you select a range, it will return the formula in the top left cell.
When to use the FormulaText function in Excel
Here are three practical scenarios.
It is very helpful in documenting important formulas.
It can also be used as a part of an audit process.
Trainers can use it to teach formulas more effectively.
I find it useful for creating more readable screenshots while creating learning content for Excel.
This is the continuation of the earlier article about Validations. Data Validation using Formula is not very well understood. Hence this article. Try it out and you will immediately know where you can use it. Reading time 9 min.
Continue reading Data Validation using Formula
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.
Continue reading How to succeed in office politics using Spreadsheets!
These marks indicate some potential error. Outdated formulas, numbers stored as text and so on. If you ignore these errors, you are at a significant risk of viewing and using wrong data for your decision making. Here is a set of articles which explain exactly how to utilize these green marks to your advantage. These are your best friend.
Never interpret anything in any Excel file unless you are sure that all the green marks are handled (all potential errors are corrected!).
- Auditors, Risk Managers, Everyone: Did you know? These green marks are WARNINGS!!
- Green Marks Part 2: Formulas showing wrong results!
- Green Marks Part 3: More error handling
- Green Marks Part 4: Inconsistent Formulas
- Green Marks Part 5/5 (Background Error Checking in Excel)
Share this critical information with your boss, colleagues, subordinates and loved ones. Everyone needs to know this!
The Need: Trace Dependents
Excel workbooks are complex. Multiple persons share and handle the same file. We keep mailing them to each other. Often the person who created the file has left the organization long back. There is never any documentation available about exactly how a file works.
Due to all this, it is almost impossible to remember which value is used in which formula. When you are about to change a value or add more data to existing block, it is absolutely necessary to ensure that all the related formulas are updated. If you forget to update the formula, the results shown there will be outdated and WRONG. This can hamper decisions and can have severe side effects.
What we need is a way to find out which formulas to update when any data changes or grows. Excel has provided this facility since 25 years. Unfortunately, very few people are even aware of it – leave alone using it!
Continue reading Excel – Impact Analysis – Trace Dependents
Shared OneNote notebook is a very effective method of working in a team and keeping everyone informed – without sending a single mail. However, I have noticed that people are worried about sharing a notebook where everyone can edit everything – and potentially overwrite what you have written.
Don’t worry! OneNote has thought of this issue and it provides extensive solution to this issue of tracking changes and audit trail. Estimated reading time 7 min.
Continue reading Shared OneNote: Worried about who has edited what?
In the previous article we saw the problem with importing CSV files.
Here is how the new Power Query can be used to clean the data while importing.
Estimated reading time 10 min
Continue reading CSV import problems solved with Power Query
This is a very common and dangerous inaccuracy.
Big operational risk!
Check all your dates imports after reading this article.
Share it with your colleagues as well.
Estimated reading time 15 min
Continue reading Global Alert: CSV import Date Misinterpretation Problem – Part 2