Like column names, table names can also be used in formulas. Read the previous article to understand the context.
Tag Archives: Formulas
Excel Tables 9 – Unbelievable simplicity – Calculated Columns
This one is so nice, you will curse yourself for not noticing it earlier (since 8 years!). Teach this to everyone around you – NOW! Show off
Excel Tables 6 – Miraculous Formula Auto-Copy
Most probably, you have been missing this feature for the last 8 years. Take 3 minutes to find out how it can simplify your life and increase accuracy.
Excel – Impact Analysis – Trace Dependents
The Need
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!
Efficiency Test 6: Excel : Copying Formulas
Yes, you have been copying formulas for decades. I know. But you don’t know the most efficient method – most probably! Try it out. Spend just 4 minutes of your time.
Using Formulas in Word tables
Did you know Word can contain formulas? Yes. It can.
For simple calculations you don’t need to use Excel. Estimated learning time 5 min |
Green Marks Part 5/5 (Background Error Checking in Excel)
Fourth article in Error Handling (Background Error Checking or Green mark) series. Read these articles first to maintain continuity.
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 and Green Marks Part 4: Inconsistent Formulas
In this article, we cover the remaining three types of errors: Unprotected Formula, Formula refers to empty cells and Data Validation Error.
Green Marks Part 4: Inconsistent Formulas
Fourth article in Error Handling (Background Error Checking or Green mark) series. Read these articles first to maintain continuity.
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
In this article, we will cover two more potential errors related to Inconsistent Formulas.
Green Marks Part 3: More error handling
This is a continuation of the series on error handling – green marks. We will see how to handle Excel formula errors and two Text related (non-formula) errors in this article.
Please read these two articles before reading this article to maintain continuity.
Auditors, Risk Managers, Everyone: Did you know? These green marks are WARNINGS!!Green Marks Part 2: Formulas showing wrong results!
How to highlight entire row based upon value in a single column
This is a common problem – which conditional formatting apparently does not solve. But that is not true. If you use FORMULA based conditional formatting, this problem has been solved decades back. Here is how you do it.