Cleaning Up Messy Spreadsheets with Tony Phillips' Top Tools
As an experienced user of Microsoft Office, Tony Phillips has developed a set of tools to help him tackle messy Excel spreadsheets. When he inherits a spreadsheet from someone else, it's not uncommon for the data to be disorganized and difficult to work with.
The problem is that the original creator may have crammed too much information into single cells or used confusing formulas. To deal with this, Tony uses a variety of Excel tools to clean up the data and make it more usable.
One of his favorite tools is Go To Special, which allows him to quickly identify blank cells in a spreadsheet. By using this tool, he can highlight those cells in yellow and then fill them in with the missing information.
Tony also uses Conditional Formatting to spot duplicate records and highlight inconsistent entries. This helps him to identify areas that need attention without changing the underlying data.
Another useful tool is TEXTSPLIT, which separates information that's been crammed together into multiple cells based on a delimiter. For example, if someone has entered an email address, phone number, and ZIP code all in one cell, separated by a pipe symbol, TEXTSPLIT can break them out into separate columns.
Once he's finished cleaning up the data, Tony turns it into an Excel table to give it some structure. This allows him to use built-in filtering, automatic expansion, and calculated columns to make the data more usable.