When cleaning up data you should adhere to a few basic rules. These are simple to implement but if you don't do them you could be in for hours of extra work
Data cleanup in Excel
The most common task Excel is used for is to ‘clean’ information received from other systems to be usable in the business.
Whether you are doing reconciliations or generating reports, you need data from external sources over which you have limited format and data type control.
By understanding all the tools and formula you can use to clean your data, you can half the time you spend on data and improve the accuracy of your work.
This course is an online training and reference guide. Work through it once to get an idea of what you can do, and when you come across a difficult situation use it to find the solution.
By the end of the course you will be able to:
- Reformat data to be able run reports or reconciliations.
- Split columns into names and surnames, product codes and product names
- Delete rows that contains unnecessary headers, footers, subtotals and other unwanted information.
- Change dates into any format even if Excel refuses to change the format.
- Fill in gaps in reports
Whether it is deleting unnecessary lines, converting numbers that are treated as text, correcting date issues or handling duplicates and missing data items, you will spend hours and hours just getting the data into an acceptable condition.
This course will help you will all these data cleanup in Excel issues.
Various methods to remove unnecessary rows in Excel, whether they are duplicates, blanks or unwanted formatting
Various ways to fill in gaps within cells to make them usable in pivot tables and sorting
- Introduction to filling in gaps in Excel Free Preview
- Filling in data gaps in Excel- Tool
- Filling in data gaps in Excel – Option 2- Tool
- Filling in data gaps in Excel – Option 3- Formula
- Filling in data gaps in Excel – Bring Sub Headings into different column
- Filling in data gaps in Excel – Exercises
Various ways to convert numbers that Excel wants to treat as text into a usable number.
- Introduction to converting text to numbers Free Preview
- Excel tests for numbers seen as text
- Convert text to numbers shortcuts in Excel
- Converting cells with the VALUE function
- Replacing the problem characters
- Click in the cell and press enter (but many times)
- Excel not recognizing a space
- Convert Text into Numbers Exercises
Various ways to correct dates in Excel so that they can be used in calculations
Various way to identify and remove duplicates in Excel. Learn how to work with duplicates either using some of the inbuilt tools or clever use of the formula and functions.
- Introduction to identifying and removing duplicates in Excel Free Preview
- Identifying Duplicates in Excel- Conditional Formatting
- Formula to identify duplicates in Excel
- Formula to identify duplicates in multiple columns
- Removing Duplicates in Excel- Tool
- Removing Duplicates in Excel- Advanced Filter
- Automatically remove duplicates in Excel with a formula
- Remove duplicates Exercises
A common problem is a single column which contains all your data but you now need it to be split over a number of columns. Excel provides a number of tools and formula that can be used for this