Data Management & Cleaning

Data Management & Cleaning is about getting data into your spreadsheet and making it usable.

Why Data Management & Cleaning

Sometimes you need to analyze your data in Excel but the data resides someplace else. You need to get it into Excel and make it usable. Data imported into Excel from an external source can have problems such as numbers-as-text, spurious characters, all caps, trailing negatives, etc. These tools can help

Data Management & Cleaning Add-Ins

Data Manager by DataSoft Solutions
Data Manager is an Excel add-in that enables you to transform worksheets into tables and create customized forms all within the familiar Excel environment. Data Manager has a robust set of data validation options to ensure that only valid data is added to your database. With Data Manager you can leverage Excel's numerous database related features for further data analysis and reporting. If you currently use Excel Data Lists to store data, you can quickly and easily convert them into Data Manager tables.”

Data Cleaner by Add-Ins
Data Cleaner adds must have features to Excel that eliminate the manually cleaning of data. Save hours of time and ensure that all your modifications are done right.”

XL-DBQuery by SBS Development
XL-DBQuery is an extremely intuitive and simple to use query builder, that enables you to select and export data into Microsoft Excel. You can extract data from virtually any data source, including Excel, Access, QuickBooks, Sage, File Maker, SQL Server, DB2 and Oracle to name just a few. XL-DBQuery even allows you to merge data from different sources.”

Monarch Context by Datawatch Corporation
“An Add-in for Excel for Spreadsheet Compliance, Auditing and Report Navigation. Monarch Context allows you to compare the data in your Excel spreadsheet with the corresponding information in the embedded source report. You may also use Monarch Context to explore, copy and print the source report from within Excel.”

“With Monarch, you have the ability to embed a full-fidelity XML representation of the original source report within any Excel file (XLSX/XLSM) that you create from that report. In other words, the source data for the Excel file now travels with the Excel file — making it easy to compare and reconcile the spreadsheet and source.”

Dedupe4Excel by DQ Globals
Dedupe4Excel is an add-in for MS Excel, which identifies and manages duplicate rows and searches across grouped fields as specified by the user to find and remove duplicate data from MS Excel.  Sophisticated fuzzy and phonetic matching processes identify probable matching records in five international languages, English, French, German, Italian and Spanish.”

Server to Excel add-ins by various publishers
Editor’s note: The publishers of most server-based Business Intelligence and Database software products provide an add-in that connects the software to Excel.

GA Data Grabber by
GA Data Grabber is a new, advanced web analytics tool, which greatly speeds up the process of gathering metrics data from Google Analytics, and the visualization and analysis of this data. It's designed especially for people working with multiple GA profiles, as the regular GA interface isn't well suited for analysis of several profiles.”

"A solution for fetching a list of tweets by keyword from Twitter to Excel. There are lots of Twitter statistics apps out there, but I think it's quite nice being able to fetch tweets to Excel, as there you can conveniently do all kinds of additional analysis like sentiment scoring, either manually or with automation, and control all the steps in the analysis process yourself. There you can also merge the results with data from other sources, for instance into a web analytics dashboard created with my Google Analytics functions."

Editor's Note: Although this is freeware, it is so novel that we included it. 

Excel Features for Data Management & Cleaning
Remove Duplicates (an Excel feature)
Excel has a simple  tool that can remove duplicate records (rows) from a spreadsheet.
In Excel 2007:  Data \ Data Tools \ Remove Duplicates \. 

Conditional Formatting (an Excel feature)
The Excel Conditional Formatting tool can highlight duplicate values. 
In Excel 2007:  Home \ Styles \ Conditional Formatting \ Highlight Cells Rules \ Duplicate Values \.