The Art of Spreadsheet-Fu – tips for manipulating data with Excel, OpenOffice, or Google Drive Spreadsheets

This post demonstrates 5 examples of Spreadsheet-Fu to make quick work of menial data formatting tasks. Spreadsheet programs have useful functions built into them for string manipulation (concatenation, search and replace, formatting, etc).   It used to be just Excel and Open Office Calc, but now Google Drive Spreadsheets has functions too!

The key, grasshopper, is what I call the ‘pull down’ trick, by which the spreadsheet program will substitute relative cells or follow through with patterns. Starting with A1 = 1, A2 = 2, and A3 =3, selecting that range and pulling down will start numbering from there. Nice way to get a range setup quickly:

excel pull down trick 1

The pull down trick can also be combined with equations that do string concatenation.  The concatenation operator is the &.  To put two columns together, with a space between them, add the following equation to a cell by starting it with the equal sign:  =A1 & ” ” & B2.  Tip: The F2 key is a handy way to jump into cell edit mode.

excel pull down trick 3

This speeds up bulk data manipulation. The nice thing is, spreadsheets are a non-programmer friendly format, and I can often arrange for non-technical team members to take care of filling out the data. I take it from there and build some equation to get the desired result.  This technique was originally valuable to me in crafting SQL updates and inserts, but on a recent project I have found it a gold mine for generating JSON.

Story 1 – bulk file rename:

I ran into a situation where I needed to bulk rename thousands of files to lower case.  First I got the list of files as a text file.

$ ls -1 > file_list.txt

Then, I opened that file and pasted its content into my spreadsheet. It automatically created a new row for each line.

Then I added this equation into B1:

="mv ./" & A2 & " ./" & LOWER(A2)

After using the pull down trick, I had a list of commands I could paste into the console, or make into a little shell script and execute.  I’m sure there is more than one way to skin a cat when it comes to bulk file re-names, but this worked for me.

Story 2 – text to columns:

Sometimes you are given data that is delimited somehow, by underscore, pipe, or some random character, and that needs to be broken up into multiple rows. Check out the  Data -> Text To Columns… feature.

excel pull down trick 4

From there, you can go on your merry way with the data, slice and dice as needed. Note – this feature was not originally part of the default Calc install, but now it is. It might be called something else in Excel. It doesn’t appear to exist yet in Google Drive Spreadsheets.

Story 3 – database update to lots of records:

Imagine a database where arbitrary updates need to be applied to several hundred records. Don’t ask me why, but sometimes it comes up. The admin page was never written, the DBA is long gone – who knows… I’ll show you how to get it done quickly:

Use the concatenation technique from story 1 above to build your UPDATE statements:

="UPDATE devices SET category = '" & B2 & "' WHERE id = " & A2 & ";"

excel pull down trick 2

Story 4 – JSON powering JavaScript app:

Some JavaScript apps don’t really need a backend database with a REST API that emits JSON.  In fact, it often makes sense to just embed the JSON directly as a JavaScript object. I’m really loving this approach on a recent project, because the customer can maintain the spreadsheet on their own. I push changes into the code base with a simple copy and paste. Note you can always build out a REST API and database back end later – and we intend to. For now, this is working wonders and saving lots of time and money:

="PRODUCTS['" &A2 & "'] = {name: '" &B2& "', color: '" & C2 & "', price: " & D2 & "};"

excel pull down trick 5

Examples available:

The examples above are available in this practice sheet I made in Google Drive.

Taking Spreadsheet-Fu Further:

Here are links to all of the functions available by program:

Aside from typical string functions, IF/THEN is supported. The syntax is awkward as the semicolon is the delimiter between arguments. If you need a lot of IF/THEN statements, or nested IF/THEN it starts to get hard to read so it may be better to write a script.

Have fun practicing your spreadsheet-fu!

This entry was posted in Data, For New Developers and tagged , . Bookmark the permalink.

Comments are closed.