Swivel Toolbar for Excel

• Excel 2003 >
• Excel 2007 >

• Copy Selection to Scratch Workbook: To use this button, first select a region of cells that you want to work with. (If you don't select a region, the full range of non-empty cells will be auto-selected.) After clicking the button, new Excel workbook will be created, containing a copy of the selected cells. Note: cell formatting is not copied over -- this is often useful, since formatting can often hide data and its placement on the sheet.
• SuperFill Right: For the currently selected row, empty cells are filled by copying values rightward to the edge of the active region of the sheet.
• SuperFill Down: For the currently selected column, empty cells are filled by copying values downward to the edge of the active region of the sheet.
• Convert Matrix to Table: This button is used to copy a matrix (grid) of numbers into a tabular format in a new sheet. For the tabulation to work correctly, the row-headers and column-headers of the matrix need to be specified. To use the button, first select the cells that form the matrix, including the row and column headers as well as the matrix cells. (If you don't select a region, the full range of non-empty cells will be auto-selected.) Then click the button. Two things happen:
  1. A dialog box appears to ask for input in identifying the headers of the matrix
  2. The top row and leftmost column are colored to indicate a first guess at the extent of the row and column headers.
  By clicking in any cell in the matrix, the header coloring will be expanded to the edge of that cell. When you have the proper headers identified, click OK. A new sheet will be created containing a tabular representation of the matrix. This new sheet contains one row for each cell of the matrix; the row contains the matrix's row and column headers as well as the cell value.
• Save Worksheet as CSV: Opens a Save As... dialog for saving the topmost sheet in the current workbook as a CSV file.
• : Uploads the selected cells to swivel.com, and launches Internet Explorer to allow you to finish the upload. (If you don't select a region, the full range of non-empty cells will be auto-selected.)

• FillRight: Shortcut for Edit->Fill->Right
• FillDown: Shortcut for Edit->Fill->Down
• Delete Row: Shortcut for Edit->Delete...->Entire Row
• Delete Column: Shortcut for Edit->Delete...->Entire Row
• Replace...: Shortcut for Edit->Replace...

To get a feel for the Swivel Toolbar in action, load this spreadsheet from the OECD into Excel. Data in this spreadsheet is not arranged into neatly-typed columns the way Swivel would like it. We will use the Swivel Toolbar to convert it into tabular format in a few simple steps.

Before we begin, notice that the data on this sheet is in a matrix format: there are row and column headers, with a grid of numbers in the center. If we look at any number in the center, it corresponds to matching values in the row and column headers. Our goal is to get each data point like that to appear on its own row of a table, with the columns of the table looking uniform across rows.

So here we go!

1. Make a scratch copy. Before making any changes to the spreadsheet, it's a good idea to copy the data of interest into a scratch workbook. This ensures that the original data remains untouched, and also removes potentially confusing formatting. To do this, click on the leftmost button in the Swivel Bar to make a scratch copy of this data in a new workbook.
2. Delete extraneous spacers. If you didn't select any rows in the previous step, your copy contains all the data from the original spreadsheet, including four rows of text. To delete those rows, drag your mouse across all four of them and press the "Delete Rows" shortcut button in the Swivel Toolbar. Now, if you look back at the original spreadsheet, you'll notice that column J was hidden. In the scratch copy, that column is shown, and is redundant to column A. We can delete column J by clicking anywhere in the column, and pressing the "Delete Columns" shortcut button on the Swivel Toolbar. Similarly, row 4 of the scratch worksheet is empty; deleted it by clicking anywhere in the row and then clicking the "Delete Rows" shortcut button.
3. Superfill the headers. Notice that after the copy, the column headers that appeared to be centered in the original spreadsheet are now clearly located at the left of their relevant range of columns -- e.g. in cell B1. In order to correctly label all the data points, these headers should be copied rightward into the blank spaces. To do this, click in cell B1 and then click the "SuperFill Right" button in the Swivel Toolbar. Notice how the value in cell K1 also gets copied rightward. Do the same in cell B2 to fill out the 2nd line of column headers.
4. Convert Matrix to Table. At this point we have a scratch worksheet with a tight matrix: 3 rows of column headers on top, and a 1-column row header on the left. We can now convert this matrix into Swivel's tabular format easily. Click in any single cell on the worksheet (to ensure that you haven't selected a range). Then press the "Convert Matrix to Table" button in the Swivel Toolbar. You are presented with a dialog asking you to click on the first entry in the upper left of the matrix just below the column headers and just to the right of the row headers. In our case, this is cell B4. Notice how the row and column headers have been colored. Then click "OK" in the dialog box. Voila! A new worksheet has been created in the same workbook, with a tabular version of the matrix. Note how each "data point" is in its own row, and the matrix's column and row headers have become columns of their own with corresponding colors.
5. Create column headers (optional). At this point, we can add our own column headers here in Excel; we could also postpone this step and do it online at swivel.com. Let's do it now -- we'll label our columns Country, Gender, Birthplace, Year and Unemployment Rate.
6. Save a copy (optional). Now that we have the data properly formatted, it's a good idea to save a copy on your local disk. Click on the "Save Worksheet as CSV" button in the Swivel Toolbar. Choose a location and a meaningful filename for saving.
7. Upload to Swivel! OK, now we're ready to swivel the data. In the final Excel worksheet, click the "Upload to swivel.com" button in the Swivel Toolbar. This will pop up an Internet Explorer window. If you're connected to the Internet, you will be able to complete the upload process at swivel.com.