Unbelievable analytics using Excel:
Here is a quick overview of how we can analyze data more effectively using new Excel tools. You can also refer to the collection of 51 articles about data analytics for a more detailed coverage. Data Analytics: Knowledge Pack.
Yes. You read it correctly. Everyone does it. But still.It is very dangerous.
DO NOT do it.
Read on to find out why.
Estimated reading time 7 min
Continue reading NEVER import CSV files into Excel – Part 1
Reports generated from business applications often become inputs to new reports.
You end up spending too much time cleaning them up. Here is a new and unimaginably faster way of doing it – using Power Query.
Estimated reading time 15 min
Estimated time saving huge!
Continue reading Miracle: Instant Report Clean Up using Power Query
Grouping in Pivot table is one method of spelling mistakes correction. Here is another option for correcting mistakes using VLOOKUP.
Estimated reading time 10 min
Estimated Time Saving many hours!
Continue reading How to correct spelling mistakes while analyzing data?
We take accuracy of Excel data for granted.
Often the raw data as well as results of formulas can be wrong, outdated or misleading.
Here is a collection of 13 data accuracy articles which will help you ensure the integrity of your Excel data with minimal effort.
Continue reading Knowledge Pack: Data Accuracy in Excel
Converting data from multiple columns to one column is a common requirement and we struggle to get this done. Very often we would have to resort to manual cut and paste. Here is how you can do it very quickly…
Estimated Reading Time 3 min
Continue reading How to convert data from multiple columns to one column
The query keeps cropping up very often while I work with customers. There are many ways to handle it. I will list down all approaches available as of now along with the pros and cons.
Continue reading Combine Multiple Sheets into One Sheet
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…
Continue reading DO NOT copy paste data from browser. Use Power Query.
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.
Continue reading Worst Practice: Using formatting as a substitute to information
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.
Continue reading Bad Data to Good Data: Fill gaps using Power Query