Data
Cleanup
|
We also have Free
training videos on this site ( visit the
Home page
or the
Training Page)
Cleaning up data in Excel spreadsheets
Excel is a package that is designed to make the analysis
of data as convenient as possible however when you import
data from another location you will often experience problems
with the data and it will usually look untidy. The following
data cleanup tips can help you to make your data more user-friendly.
|
|
| Data cleanup tips and tools
The following tips and tools are good facilitators
in data cleanup, but always remember to save your work before
you employ any of these tips. It is more difficult to return
to the original form of the document then sorting or filtering
it.
|
|
- Data cleanup tip 1: Data is not recognized properly
This usually happens when you import data, when you import figures
Excel might not recognize the value as a figure but rather regards
it as text. Therefore when you include that cell in formulas you
will not get the right result. To sort this problem you can copy
the cell and right click on a blank cell. Select Edit, Paste Special
and then Add. You will now have to replace the new cell with the
old cell that was not recognized.
- Data cleanup tip 2:
Hidden Rows can result in you thinking that the formulas calculated
by Excel are incorrect because they are not the same as your 'manual'
results. Make sure that there are no hidden rows or columns that
might give unexpected results.
- Data cleanup tip 3:
Often in text people make the mistake of adding an extra space
between words. For anyone to sit and manually adjust two spaces
to one space will be very time-consuming and tedious; rather use
the Find and Replace function that Excel provides. Type Ctrl +
H, this will bring up the Find and Replace dialogue box, in the
field for 'Find what' you type two spaces and in the 'Replace
with' field you type just one space. Then click Replace All and
your problem is solved.
- Data cleanup tip 4:
Sorting: When you type lists into an Excel spreadsheet very few
times is the list pretty in the sense that it is not easy to find
a specific value or word. Excel has a sorting operation that can
sort the data. Figures can be sorted in ascending or descending
order and text can be sorted alphabetically, either from A –
Z or from Z – A.
- Data cleanup tip 5:
Imported phone numbers, often when phone numbers are imported
you will have a variety of different ways that the numbers are
written, you can use the Find and Replace tool to have all the
phone numbers look the same. Firstly you need to remove all the
extra characters so that you only have nine digits left. Press
Ctrl + H, in the 'Find what' box you type a left or right parenthesis
or a hyphen or a period; in the 'Replace with' box you do not
type anything, click Replace all. To ensure that Find and Replace
only works on the telephone numbers highlight the area you would
like to adjust before typing Ctrl + H.
Data cleanup is not something that can be defined once, there are
constantly new tools that are being developed to assist you in your
data cleanup process. Miricle Solutions is a company that has expertise
in Excel and have numerous courses to help you to maximize your
usage of the Excel package from basics to power courses and fun
and useful stuff. Contact Miricle Solutions to aid you and your
business by learning the art of Excel.
|
