This is the worst type of input data to get. Each row has been split into two (or sometimes more) rows. We want those to be combined into a single row. In an earlier article, I have shown one method of doing this.
Now here is a simpler, faster and more powerful method using Power Query.
(Estimated reading time 12 min)
Continue reading Data Split into Two rows – Cleanup using Power Query
Just one button to press. Data – Remove Duplicate.
Data should be a table (or like a table – column headings with data below). If it is a table choose Table Tools – Remove Duplicates.
By default, duplication is checked for ENTIRE ROW.
Remove checkboxes to check for duplicates for specific fields only.
Be careful. Duplicate rows are DELETED.
This is unlike Advanced Filter – Unique Rows only – where duplicates are HIDDEN.
For large data – Power Query – Remove Duplicates is infinitely more powerful and faster. We will cover it in a separate article.
Single row of data which is split into two rows! Trying to clean-up such data is a frustrating experience. Here is how you do it – faster and smarter! 3 min video
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?
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
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.
Continue reading Data Clean-up: System Reports
Very common problem. Very easy solution. Deserves a separate article. This alone can save hundreds of thousands of person years of drudgery globally every day.
This article shows the problem and the solution in a concise way. Quick and Easy.
Continue reading Bad Data to Good Data: Pivot Copy Paste
Calendar view shows only Day, Week, Work Week and Month views. You can only select all appoints within a month. Even in the month view, CTRL A does NOT select all appointments.
In short, any bulk operation you want to do for appointments is a pain.
Solution is simple. Just change the view in Outlook.
Now all the appointments and meetings are shows as a simple list – like Inbox or Tasks..
Now you can sort, filter, multi-select any number of items and perform any operation you like – for example, Delete 0ld appointments or archive to another folder or PST.
AutoArchive does this automatically
Most of us don’t want old appointments to waste space in the mailbox – because the space is limited. The smartest way to clean this up is to run AutoArchive. We will cover this in the next article.