Excel Data Operations

The Spreadspeed has a number of handy tools for both manipulating and extracting information from your data.

Create Dropdown Validation List

To control cell input for a spreadsheet, you can restrict data entry on a field by creating a validation list. The Create Dropdown Validation List tool makes it easy to create a validation list in a few simple steps.

Dropdown Validation List Example

As shown in the screenshot below, the Create Validation List dialog allows you to do the following:

Dropdown Validation List

The screenshot below-left shows the input message that is displayed when the user selects the cell. The image below-right shows the dropdown list in action. (Not shown: Excel will display an error message if an invalid entry is made in the cell.)

Validation Message

Validation Message

Validation Dropdown List

Validation Dropdown

Subtotals

The Spreadspeed Subtotals tool is similar to built-in Subtotals tool in Excel, with several important distinctions. Instead of collapsing the data into groupings, our tool will extract the data from the input range and output the subtotals to any location that you specify. Also, our Subtotals tool allows you to output as formulas or values, plus it does not need recognizable headings and can operate on any input range.

Subtotals Dialog

Subtotals Form

Sample Input

Subtotals Input

Sample Output

Subtotals Output

Sum and Percentages

The Sum and Percentages tool will take an input column, place a sum equation beneath it, and then create a percentages column. You can optionally choose to have a cumulative percentages column created as well.

Sum and Percentages Dialog

Sum and Percentage Form

Sample Output

Sum and Percentage Output

Descriptive Statistics

The Descriptive Statistics tool calculates the statistics listed below for a specified input range.

Descriptive Statistics Output
Mean Kurtosis Minimum
Standard Deviation Skew Maximum
Standard Error Median Range
Sample Variance Mode Sum
Confidence Interval Count
Differences Between Spreadspeed and Analysis Toolpak

The Analysis Toolpak add-in distributed with Excel also includes a Descriptive Statistics tool, but there are several improvements the Spreadspeed version offers:

Descriptive Statistics Screenshot

Descriptive Statistics Dialog

Descriptive Statistics Form

Sample Output

Descriptive Statistics Output

Repeating Data

Spreadspeed has two tools for removing and filling repeating data from a selected column or row. The Remove Repeating Data tool takes a redundant listing and turns it into a hierarchical listing. The Fill repeating Data does just the opposite, filling in the missing gaps, as shown in the images below. The dialogs (not shown) for these tools will prompt for the range and let you choose whether to operate on columns or rows.

Before Remove Repeating Data

Repeating Data

After Remove Repeating Data

Non-Repeating Data

Before Fill Repeating Data

Non-Repeating Data

After Fill Repeating Data

Repeating Data

Random Sort

The Random Sort tool (shown below) will take a user-specified range and sort the contents in a randomized order.

Random Sort Dialog