# 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:

• Select a list of existing items to create the validation list. The items can be imported from another workbook, and Spreadspeed will sort and remove duplicates from the imported list.
• Alternately, instead of importing a list, you can enter a list in the dialog.
• Select where to store the validation list (and optionally hide the column it is stored in).
• Select the cell to apply the validation list to.
• Choose whether to display an input message when the cell is selected and an error message for invalid input.

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 Dropdown List

### 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

Sample Input

Sample 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

Sample 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:

• Spreadspeed will output the results as formulas instead of just values.
• For multi-column input, Analysis Toolpak outputs redundant row labels for each column of output. Spreadspeed has a single label column.
• For multi-column input, Spreadspeed allows you to treat the data as a single block, instead of calculating the columns separately.
• Spreadspeed allows you to select whether the results are generated using the .P and .S functions introduced in Excel 2010, or using the functions compatible with Excel 2007 and previous versions.
• Spreadspeed allows you to change the confidence level (the Analysis Toolpak has a fixed confidence level of 95%.)
• Spreadspeed lets you to choose which statistics functions to include in the output.
• Spreadspeed returns an empty cell instead of an error value for invalid MODE() results.
##### Descriptive Statistics Screenshot

Descriptive Statistics Dialog

Sample 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

After Remove Repeating Data

Before Fill Repeating Data

After Fill Repeating Data

### Random Sort

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