Category Archives: Data

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!

Green marks in Excel - more error handling by Dr. Nitin Paranjape

Continue reading Green Marks Part 3: More error handling

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.

green marks in Excel by Dr. Nitin Paranjape

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

Bad Data to Good Data: Fill gaps using Power Query

This is a continuation of a series on how to convert badly formatted data into an easy to analyze – tabular format. In this article, we are talking about data which has gaps and how to fill gaps efficiently. This is a new approach available using Power Query – it is fast and effective. Have a look and save hours of your precious time.

fill gaps in data using Power Query

Continue reading Bad Data to Good Data: Fill gaps using Power Query

Consolidating crosstab data

This is a continuation of the Data Cleanup series. One common form of bad data is called crosstab. Consolidating crosstab data requires lot of manual effort. Pivot – Multiple Consolidation Ranges is a fantastic feature available in Excel which can do this job in a snap. Here is an example of cross tab data…

Data

Let us see how to get this done.

Consolidating crosstab data
Continue reading Consolidating crosstab data

Data Clean-up: System Reports

Data Clean-up: One of the worst formats to get raw data is the system reports. These are reports you generate from business automation software. Export to Excel / CSV option dumps the report into a file instead of a printer. But the report is originally designed for printing. Now you waste too much time removing unwanted headers and footers.

This article shows a quick method, and more importantly, a concept which can be used in various clean-up scenarios as well.

Data Clean-up

Continue reading Data Clean-up: System Reports

Good Data vs. Bad Data: How to identify?

This article shows you simple and immediately usable methods of understanding whether the data you have is good or bad.

This article has intentionally been kept short and to the point so that it is practically usable as a set of best practices.

A more detailed version of this article is being written by an expert in the field, which will be also be published as a guest post soon.
Continue reading Good Data vs. Bad Data: How to identify?

Copy formulas automatically and accurately (even with blanks)

Imagine that you have 50,000 rows of data. You want to add a new calculated column. Adding the formula is easy. But copying it to 50,000 rows is nothing but frustration.

Three methods are typically used: All of them are inefficient!

Copy and Paste, Drag and Double Click. Drag is lengthy – so is Copy Paste. Double Click is DANGEROUS because it stops if the column on the left has a blank cell. And usually we have lots of blank cells – at least we have to assume so!

Of course, there is a solution – a magical solution!

image

Continue reading Copy formulas automatically and accurately (even with blanks)

9 benefits of using Excel Tables

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

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.