The necessary evil of spreadsheets: 4 easy Excel tips for database management that save HOURS of time.

Here at the Hustle, we often help our clients move away from spreadsheets – there is almost always a free software application that just gets the job done faster and easier.  But spreadsheets can be a great way, and sometimes the only way, to migrate data from one system to another.  We’ve been working with Oxygen Tango to move their system from Mind Body Online to Mobilize; this involves exporting  4+ years of data for more than 3000 people!  Excel is the most efficient way available to migrate between these 2 platforms; here are 3 easy tips we’ve used to save us hours of time compiling and distilling the data.  Click on the bold header for step-by-step instructions:

1. Freezing Cells:  Sometimes you need to scroll through hundreds of lines of data on a spreadsheet; if there are also many columns, you can easily lose your place in the information.  Freeze your header row so you can see it no matter how far you scroll down.
2. Split text into columns: If you have data which imports a full name, say “John Doe” or “Doe, John”, you may need to split that name into 2 separate columns for first and last name.
3. Sort data: With this function, you can quickly find duplicates, as well as re-group information in ways to provide further data insight.  For Oxygen Tango, we needed to figure out the last date of their members’ autopay plan; Mind Body Online only offered a report which would individually list every autopay transaction for every member, by date.  With the sort data function, we were able to upload export the MBO report to Excel, then sort the data by last name, with a secondary sort on date so we’d see the most recent transaction first.  We then used the Conditional Formatting function (see next item) to remove all duplicates and voila!
4. Remove or highlight duplicates: this function, under Conditional Formatting, allows you to quickly remove any duplicates in your data, as well as highlight duplicates.

Let’s get down to business.