“Table Tools”

On-line tools to speed up your manual spreadsheet work

Documentation

Table of Contents

  1. Introduction
  2. Specifying Delimiter
  3. Creating Look-Ups
  4. Converting Dates
  5. Combining Series
  6. Clearing White Space
  7. Quick Diagnosis
  8. Interpreting Social Security Numbers
  9. Acknowledgements

1.  Introduction

Back to the top

A drawing of a crankshaft and a screwdriver You are an expert spreadsheet user. Few of your peers make the figures behave the way you do. Still, you are concerned about the way the spreadsheet locks the data in its grid system, making manual tasks such as removing empty data cells more time-consuming than necessary.

“Table Tools” is an array of ready-made functions that enable you to perform spreadsheet operations outside the grid you are using. The tools range from basic tasks, such as removing empty data cells in a column, to more advanced data management tasks, such as creating automatic look-ups for various data-based columns.

Try it out in 4 easy steps:

  1. Select a tool by clicking the tool name in the left-hand menu.
  2. Make a copy of a single column (or even a whole table) in your spreadsheet and paste it directly onto the yellow text area in the production window, or click "Import from clipboard".
  3. Activate the tool you want by clicking one of the buttons marked ">" under the yellow text area.
  4. Copy the contents of the yellow text area, or click "Export to clipboard". Insert into your spreadsheet your results.

You will also find a custom demo column (or whole table) in connection with each tool. Activate the demo by clicking the 'Import demo' button.

A complete description of all tools is found further down on this page. Quick-glance documentation is also available by directing the mouse toward the tool name in the left-hand navigation pane in the production window.

Note that Internet Explorer is the only web browser that allows you to import clipboard contents automatically onto the yellow text area or, conversely, to export the contents of the yellow text area automatically to the clipboard. If you are using any other web browser (such as Firefox or Google Chrome), use the right-click and 'Copy' or the right-click and 'Paste' option.


2.  Specifying Delimiter

Back to the top

Specifying Delimiter Between Columns

This tool specifies the delimiter between columns, and you can toggle between two commonly used delimiters:

  • tab sign
  • comma

3.  Creating Look-Ups

Back to the top

Creating Automatic Look-Ups for a Column

This tool creates a look-up for data in a column by:

  • counting the number of unique data instances
  • displaying and enumerating the unique data instances

You can choose between:

  • table format (with key numbers attached)
  • string format

You can also create a standardization for any column that automatically inserts the same enumeration as in the corresponding look-up.


4.  Converting Dates

Back to the top

Date Conversion Format in a Column

This tool insures correct dating between two commonly used formats:

  • YYYY-MM-DD
  • MM/DD/YYYY

5.  Combining Series

Back to the top

Combining Columns of 0 to 1 Series or White Space

This tool combines multiple columns of 0 to 1 series (or white space) in a single column.

The main function of this tool is to create a multiple-layer prioritization in which the figure '1' always has a higher priority than the figure '0'. In turn '0' has a higher priority than white space.

This tool also allows you to clear any rows that are incomplete.


6.  Clearing White Space

Back to the top

Clearing White Space in a Column

This tool clears any positions in a column that holds no data (only white space).


7.  Quick Diagnosis

Back to the top

Diagnose Quickly the Main Parameters in a Column

This tool gives you instant access to an array of common parameters:

  • number of positions in the column of data
  • number of positions that hold data (as opposed to white space)
  • number of unique data instances
  • column total
  • column mean value
  • column minimum
  • column maximum
  • column median

8.  Interpreting Social Security Numbers

Back to the top

Interpreting Information in Swedish Social Security Numbers

This tool extracts 3 dependent attributes from Swedish social security numbers:

  • year of birth
  • date of birth
  • gender

9.  Acknowledgements

Back to the top

We thank Docent Brian B. Magnusson and Mr. Mikael Sköld for their assistance i pursuing this project.