Data Cleanup in Excel
This online courses addresses the following problems when dealing with data in Excel using the new PowerQuery tool (Get & Transform)
Removing Unnecessary Rows
In order to use data you need it to look like a database. But systems often export reports in ways that are not friendly to Excel use. In this section you will learn how to easily and quickly remove the rows that are unnecessary. So instead of spending a day manually deleting individual rows, click a few buttons and it is done.
You can go from the left side to the right side of the image below in a few clicks
Filling in the Gaps in Data Sets
Reports often look good with lots of white space, but this is not useful when you want to do some analysis in Excel. This section deals with quick and safe ways to fill in the gaps in a database. No more straining your eyes as you copy and paste each little section.
Converting numbers that are treated as text to numbers
There is nothing worse than finally getting some numbers into Excel and then realising that Excel is not treating them as a number. Whether it is a comma when there should be a space, unprintable characters or trailing spaces all these issues can be solved with some simple tools or formula.
So you won’t have to explain why the sum is showing zero even though there are numbers above.
Correct Date Issues
Dates are critical in the business world, yet Excel sometimes struggles to see cells that have dates in it as dates.
Some simple tools and formula allow you to easily convert cells that contain ‘dates’ into dates that Excel recognises and can use.
Identify and Remove Duplicates
Learn quick ways to identify duplicates in a dataset and where necessary quickly remove them,
Split Columns in Excel
Learn how to split text that appears in one column into multiple columns, whether it is nice and consistent or not.