Tag Archives: data cleanup

DO NOT copy paste data from browser. Use Power Query.

This is a common activity. Go to a browser page, find some tabular data or report and copy-paste it into Excel. Now you waste a lot of time cleaning up the unwanted things which also got pasted.
If you have Excel 2010 or above (Professional Plus or Office 365 edition),
you have a miraculously simple method available now : Power Query

stop copy pasting from browser - by Dr. Nitin Paranjape

Continue reading DO NOT copy paste data from browser. Use Power Query.

Worst Practice: Using formatting as a substitute to information

Experts tell us the best practices. But we do lot of mistakes before we can find the best way. Documenting the mistakes – which is what I call “Worst Practices” is also important.

This worst practice is very common. I have found it across the world and across industries. Read on and confirm that you are not making this mistake.

image

Continue reading Worst Practice: Using formatting as a substitute to information

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

Miraculous Automatic Data Cleanup in Excel

This is the continuation of data cleanup series. In this article we discuss a newly introduced feature “Flash Fill” which is almost miraculous and difficult to believe. When I demonstrate this feature during my seminars, most people suspect that I am playing some kind of trick!

It opens up a new way of handling data cleaning tasks which would have either been very time consuming or impossible using existing methods.

Data Cleanup

Continue reading Miraculous Automatic Data Cleanup in Excel