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.

image

Continue reading

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.

image

Continue reading

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!

image

Continue reading

Green Marks Part 2: Formulas showing wrong results!

Green mark in top left corner of Excel cells is an indication that something is wrong with the data you see. Read this article first to understand the concept: Auditors, Risk Managers, Everyone: Did you know? These green marks are WARNINGS!!

In this article, we will discuss the most important and potentially dangerous error which is commonly ignored. Read on and share it with as many people as you can.

image

Continue reading

Auditors, Risk Managers, Everyone: Did you know? These green marks are WARNINGS!!

I am sure we see these everyday across almost all Excel files. But do you know the meaning and significance of these green marks?

These are WARNINGS – it is Excel’s way of telling you NOT to trust what you see. Billions of people are ignoring these green marks for over a decade. If you are one of them, please read this article and share it with everyone who matters to you.

image

Continue reading

Auditors, Risk Managers: The ultimate Spreadsheet Comparison tool in Excel 2013

This is the third article in this series. Please read these articles as well:
Auditors, Risk Managers: Excel spreadsheet analysis using the new Inquire tool
Spreadsheet Analysis using Inquire – Relationship Diagrams

Spreadsheet Risk is very important for everyone – from a single user to a large organization. Because an interpretation mistake or a wrong value can be very costly indeed.

In this article we will see how to compare two spreadsheets. Using the old method and the new amazing tool which comes with Office 2013.

image

Continue reading

Auditors, Risk Managers: Excel spreadsheet analysis using the new Inquire tool

These two roles require extensive work with complex workbooks. Auditors need to understand and validate large, complex workbooks in a time bound manner. Risk managers work with complex models with large data sets. They want to implement Spreadsheet Controls in place to reduce the operational risk originating from spreadsheet errors. In either case, you need to understand, validate and troubleshoot spreadsheets in a time bound manner.

In this article, I am not going to cover the regular features of native Excel which include GoTo Special, Trace Precedents, Watch Window, Evaluate Formulas and so on.

Since Office 2013, Microsoft introduced a new add-in called Inquire – which is very powerful. But still many people do not know about it. We will explore this tool in this article.

image

Continue reading

Excel Tables

If I have to teach ONE thing to the world, it would be Excel Tables.

I recently created this video for a Microsoft Webcast.
Excel Tables were introduced in Office 2007.
This video explains the importance of using Excel Tables.

Prevent Wrong Decisions using Excel Tables

Outdated formulas in Excel are a big operational risk

Tables eliminate that risk proactively.

Additional Benefits of Excel Tables

This in itself can become a series. But here is a short description of the benefits

Auto-update of formulas and references

All dependent formulas are auto-updated across sheets. If you have a linked file, the file must be open for the auto-update to work.

Automatic formatting of tabular data

If you have manual formatting applied, the Table formatting cannot override it. To override, right click on the desired format and choose Apply and Clear Formatting option.

Automatic copying of calculated column formulas

Any formula added in new columns in the table is auto-copied across the table. The formula is copied all the way to the end of the table even if the left column has empty cells.

This saves you the trouble of using time consuming DRAG or error prone DOUBLE CLICK for copying formulas.

If you don’t like this behavior then use the SmartTag to UNDO the auto-copy action.

Automatic copying of calculations when more data is added

This is very useful. If more data is added, calculated columns are auto-filled.

To take advantage of this feature, make sure that you add calculated columns at the end of the raw data. This way, when you get more raw data, you can simply paste it at the bottom of the Table.

Automatic display of headings

Headings are shown in place of regular column headings. It also offers Auto-Filter dropdown (2010 onwards). This eliminates the need for Split and Freeze Pane.

Automatic copying of formatting, conditional formatting and validations

All attributes of cells are auto-copied when you add more rows in a Table.

Ability to use Table and Column names in formulas

Ability to integrate with SharePoint and Visio

You can upload an Excel table to a SharePoint site. It automatically creates a list, creates relevant columns, uploads the data to SharePoint and finally creates a one way (SharePoint to Excel) link with the data.

In Visio you can create a visualization based upon a Visio based Pivot Table functionality.