Normalizing cross-tab data without Excel

Version 1

    Problem

    You want to do some visualisation magic in Tableau, but your spreadsheet looks like this:

    All those green columns are dependent variables: independent observations about one location defined by the white columns.

    Tableau would be so much happier if your spreadsheet looked like this:

    This is called “normalizing” the “cross-tab” format, or converting from “wide format” to “long format”, or “UNPIVOT“. Tableau provides an Excel plugin for reshaping data. Unfortunately, if you don’t use Excel, you’re stuck. It’s kind of weird.

    Solution

    Anyway, I’ve made a Google Sheets script “Normalize cross-tab” that will do it for you.

    As the instructions say, to use it, you:

    1. Reorganise your data so that all the independent variable columns are to the right of all the dependent ones; then
    2. Place the cursor somewhere in the first (leftmost) independent variable column.

    It then creates a new sheet, “NormalizedResult”, and puts the result there.

    How to use

    It’s surprisingly clumsy to share Google Scripts, at least until the new “Add-ons” feature is mature. Here’s the best I can do for you:

    1. Copy the script to the clipboard

    Go to https://raw.githubusercontent.com/stevage/normalize-crosstab/master/normalizeCrossTab.gs, select all the text, and copy to the clipboard.

    2. Upload your spreadsheet to Google Sheets

    Upload your Excel spreadsheet into Google Sheets, if it’s not there already.

    3. Tools > Script Editor…

    4. Click “Spreadsheet”

     

     

     

     

     

     

     

    5. Paste

    In the window labelled “Code.gs”, select all the text and paste over it the script from the clipboard.

    6. Save.

    You need to give this script “project” a name. It doesn’t matter.

    7. Select the “start” function.

    8. Click Run

    Click Continue and accept the authorisation request.

    9. Follow the instructions of the script

    Now, switch windows to your Google Sheet, and you’ll see the sidebar.

    10. Download your normalised spreadsheet

    On the NormalizedResult page, choose File > Download as…

    Screenshot 2015-01-06 20.53.36

    If you want to convert several spreadsheets, you can save yourself pain by loading them all into the same workbook. Just remember that the script will always save its output to NormalizedOutput.

    (Originally posted at Normalize cross-tabs for Tableau: a free Google Sheets tool | Steve Bennett blogs)