Opening up (or double clicking on) a database displays the selected database in a Spreadsheet Edit format, provides various tools for editing the data, and gives you access to all of ProVal's other database operations. Click an activity below to learn how to perform it in ProVal. To save a set of database commands together and run them as one process, see Data Scripts.

Getting started

• Create a new database
• Open a different database
• Import data from an Excel or text file
• Merge or append data from another database
• Print data
• Find values
• Find a field
• Get summary statistics

Editing

• Cut, copy, and paste data
• Undo / redo changes
• Save changes
• Delete records or fields
• Add records or fields
• Define fields
• Edit or clear data

Viewing and formatting

• Customize the view
• Style library
• Format numeric fields
• Selection expressions library
• Change a column's width
• Spreadsheet statistics

Screen for errors

• Find data errors
• View data errors
• Approve data errors
• Correct data errors
• Reconcile statuses from prior year

Review your work 

• Compare to an earlier version or prior year's data
• View the database's properties, change history, or notes

For forecasting

• Group data

Less common tasks

• Export data
• Resolve duplicate records
• Select a random subset
• Merge in prior year's individual aggregate results

Using shortcuts

• Keyboard shortcuts

 

Create a new database

Click File > New to create a new, empty, database with 0 records. This database becomes the current database (only one database can be open at a time). For more information, see New Database.

The next step will often be to Import data from an Excel or text file. Or, you might Add records or fields by hand.

 

Open a different database

Click File > Open to switch to a different, existing database. This database becomes the current database (only one database can be open at a time). For more information, see Open Database.

In the Open Database dialog box, you can also choose a previously established Style (display format), that you want to use, or you can select <all records and all fields>. For more information, see Style library below.

Import data from an Excel or text file

Click Home > Import Data. For more information, see Import Data.

 

Merge or append data from another database

You can merge data in from another database (e.g., prior year’s data), matching records based on a key field such as social security number. Click Home > Merge. For more information, see Merge Data.

You can append records from another database (as opposed to merging based on a key field such as social security number). Click Home > Append Data. For more information, see Append Data.

 

Print data

Data is printed as displayed in Spreadsheet Edit. To hide a field, select a subset of records, reorder fields, etc., use the View options to do so before printing.

Click File > Print Preview to view what will be sent to the printer, and if you wish, print it.

Click File > Print to send data to the printer.

Changes to the Page Setup (orientation, font size, paper, margins, and header and footer) are retained until you change them again. To change the Page Setup, click ​File​ > Page Setup. For more information, see Page Setup.

 

Find values

Click Home > Find to search a Field for a value (e.g., Service <= 5) contained on the records selected by the current Selection expression. ProVal will search only the fields unhidden in the current database view. Then enter the Condition and Value:

• For numeric and date fields, you may search for values that are = (equal to), <> (not equal to), < (less than), > (greater than), <= (less than or equal to) or >= (greater than or equal to) a specified value. To search for a missing value, leave the Value text field blank. To search for multiple values, select the "IN" Condition and enter or paste one value per line in the List of Values.

Numeric field searches for = or <>: The search is done on a rounded basis, based on the number of decimals entered for the value. Enter more decimals for a more precise match. For example, searching for “Age = 55.25” will match 55.249999 or 55.251111. This makes it possible to search for a value in calculated fields (e.g., via Define Field by Expression, Individual Results, Group Data, etc.). Note that this applies only to = and <>; full precision is used for <, >, <=, and >=.

Numeric field searches for IN: rounding is based on the value with the maximum number of decimal places. For example, suppose the following values are in the database field named “Age” on three database records: 16.2, 19.78 and 27. If you enter 16 and 17.2 in the List of Values, ProVal does the following: (1) rounds everything in the List of Values to the largest number of decimal places used in the List of Values, so the list becomes 16.0 and 17.2; (2) rounds the values in the Age field to that same number of decimal places, so 16.2, 19.78 and 27 become 16.2, 19.8 and 27.0, respectively, and (3) tries to match the value in theList of Values to the value on the database record. “16.0” is not equal to “16.2”, so the spreadsheet’s data field value of 16.2 is not found. However, if you enter 16 and 17 in the List of Values, ProVal derives a different result: (1) rounds everything in the List of Values to the largest number of decimal places used in the List of Values, so the list becomes 16 and 17; (2) rounds the values in the Age field to that same number of decimal places, so 16.2, 19.78 and 27 become 16, 20 and 27, respectively, and (3) tries to match the value in the List of Values to the value on the database record. “16” is equal to “16”, so the spreadsheet’s data field value of 16.2 is found.

• For coded fields, you may search for any number of codes at once (e.g., Status equal to “Active”, “Retired” or “Vested”) by selecting them from the list that appears for the Value parameter. To search for a missing value in that field, select “<Missing Value>”.

• For character fields, you may type in an exact string to search for (e.g., “John” will match only “John”, not “John Smith” or “Smith, John”) or use “*” to represent any character (e.g., “Jo*” will match “Jo”, “Joe”, and “John Smith”, but not “Smith, John”). Multiple “wildcards” are allowed (e.g., “*Jo*” will match “Jo”, “Joe”, “John Smith” and “Smith, John”). The search is case-insensitive. To search for a missing value, leave the Value text field blank. To search for multiple values, select the "IN" Condition and enter or paste one value per line in the List of Values.

• For social security number fields, enter the exact value you are searching for. You may omit leading zeroes and dashes (e.g., 12345678 and 012-34-5678 are equivalent). To search for a missing value, leave the Value text field blank. To search for multiple values, select the "IN" Condition and enter or paste one value per line in the List of Values.

If the Condition is changed from “IN” to any other Condition, only the first non-empty (not blank) search value will be retained.

Note about "<>" (not equal) condition and missing values: A search such as "field <> 2" using the “Next” or “Previous” buttons will find empty values.  However, the same search using the “Find All” button will ignore empty values (and the records containing them will not be included in the resulting record selection).

Click Previous to highlight the previous cell that meets the criteria. Click Next to highlight the next cell that meets the criteria. The cells that are found will have a thick black border.

Click Find All to set the selection expression to the equivalent statement (appending to any existing selection expression) and evaluate it. Click Clear to remove a selection of Value or remove the selections/entries in the List of Values.

There is no find and replace option. Instead, click Find All after specifying the value to be found and then copy and paste, or use Define Field by Expression.

 

Find a field

Click Home > Go To to move the selection to a specified field. This is useful, for example, to find a field among numerous fields that are not sorted alphabetically.

 

Get summary statistics

Click Home > Frequency Tables to get headcounts for the current database grouped by one or more fields (e.g., by status, by age, by age and service). Checking headcounts for reasonableness is a common step after importing data. For more information, see Frequency Tables.

Click Home > Descriptive Statistics to get summary statistics (e.g., count, sum, mean, etc.) for fields in the current database, optionally grouped by one or more fields (e.g., by status, by age, by age and service). For more information, see Descriptive Statistics.

 

Cut, copy, and paste data

Click Home > Cut to clear the contents of the selected cells and put the values on the Windows clipboard. A rectangular selection region is required for this operation.

Click Home > Copy to put the values for the selected cells on the Windows clipboard. A rectangular selection region is required for this operation.

If pasting to another application (e.g., Excel), click Home > Copy with Titles to copy field names and RecIDs in addition to the data values.

Click Home > Paste to paste data from the Windows clipboard into the current database. If invalid values are encountered, a missing value is pasted in its place. Such cells are highlighted in pink until you close the current database or you enter valid data into them.

For coded fields, ProVal tries several different ways to find a valid value.

• ProVal first tries a case-sensitive match between the pasted data and the field’s labels

• If that fails, ProVal tries a case-insensitive match between the pasted data and the field’s labels

• If that fails, ProVal tries a match between the pasted data and the field’s numeric codes

If only a single cell is selected, that becomes the starting point to paste in the block of data. If a multiple cells are selected, the pasted data is replicated to fill the selection region. If the selected region is r rows by c columns, then the rows and columns of the data being pasted must be an even multiple of r and c.

You cannot add fields or records by pasting. Pasted data which extends beyond the rows and columns of the current database is truncated. To avoid this, first use Home > New Records or Home > Add Fields to add blank rows and/or columns before pasting.

An alternative method of copying and pasting within ProVal is to use the Fill Handle in the bottom right hand corner of the selected range. If you drag and drop the Fill Handle, it is equivalent to copying the cells which are selected when you started dragging the Fill Handle and pasting it into the cells which are selected when you drop the Fill Handle. For example, if you start with one cell selected and drag the Fill Handle down three cells,

you end up with this.

Double-clicking the Fill Handle has no effect.

 

Undo / redo changes

 You can undo manual data changes (including changes made with cut and paste) as long as they were made since the database was last saved. (Note that a save is required to continue with many operations, such as importing data or defining a field, but ProVal will always give you the option to cancel the operation if you do not wish to save your data at that point.) Click Undo on the Quick Access Toolbar.

To undo multiple changes at once, click next to the undo button.

Undo does not apply to changes made through options other than manual editing (e.g., importing data, deleting records, deleting fields, and defining fields).

For changes that cannot be undone, you can see the previous values by clicking File > Change History. See View the database's properties, change history, or notes below for more information.

 If you undo too many changes, you can redo them. Click Redo on the Quick Access Toolbar.

To redo multiple changes at once, click next to the redo button on the toolbar.

 

Save changes

Click File > Save to save manual changes (including cut and paste) that have not been saved. The number of cells with unsaved changes appears in the status bar.

If there are more than 10,000 unsaved cells, a warning appears in this status bar to let you know that details will not be saved to the Change History. If you hover over this warning, more information appears:

The changes still will be logged in the Change History when you click Save, but without detailed previous and new values by record. The description becomes: “Manual edits to 10,000+ cells – no details logged”, as in this screenshot:

Unsaved cells can be located easily because of their pale blue background color. (An unsaved cell is defined as one whose value differs from the saved value.) Note that the Change History does not log edits that don’t change database values.

Also note that saving makes manual changes permanent - they cannot be undone once they are saved.

Database changes besides manual edits (e.g., importing data, defining a field by expression) are saved automatically; these changes will not appear as unsaved changes in the status bar.

Click File > Save As to save the current database, including manual changes, with a new name. The original database is not changed, and the new file becomes the current database.

 

Delete records or fields

To delete records:

1. Select the records you wish to delete (e.g., using the mouse).

2. Click Home > Delete Records. The Delete Records command will open with the selected records slated for deletion. For more information, see Delete Records.

To delete fields:

1. Select the fields you wish to delete (e.g., using the mouse).

2. Click Home > Delete Fields. The Delete Fields command will open with the selected fields slated for deletion. For more information, see Delete Fields.

To blank out the selected records or fields, but keep them on the database, select them and then click Home > Clear.

 

Add records or fields

Click Home > New Records to add a specified number of records to the end of the current database. New records are initialized with missing values.

Click Home > Add Fields to add specified fields to the current database. Click the Data Dictionary button to create new field names or unhide existing ones. New fields are initialized with missing values.

 

Define fields

Click Home > Data Dictionary to define the master list of fields for this client. Note that this is not the only means for adding fields to the Data Dictionary; fields are often added in other ways, such as through the data import process. The Data Dictionary also lets you change formatting for numeric and social security number fields and modify codes for coded fields. For more information, see Data Dictionary.

Click ​Home​ > Define > Define Field by Expression to define fields using a mathematical expression (e.g., to define age based on date of birth) or click Home​ > ​Define ​> ​Define Field by Table to look up table values and place the result in a field. Multiple Define Field by Expression entries and/or Define Field by Table entries can be combined into a single “program” that runs with one click by using Home​ > ​Define ​> ​Expression Sets. For more information, see Define Field by Expression, Define Field by Table and Expression Sets.

 

Edit or clear data

Double-click or press F2 to edit the active cell and position the insertion point at the end of the cell contents or drop down the choice list for coded fields. For more information, see the Keyboard shortcuts for editing data below.

Click Home > Clear to blank out the contents of the selected cells (but keep the fields and records on the database).

 

Customize the view

You can use a variety of options to display the spreadsheet data according to your needs. Your customization can then be saved to the Style Library for use in another Spreadsheet Data session (see Style library, below).

Click View > Records in Rows to display records in rows and fields in columns or click Records in Columns to display records in columns and fields in rows. The Records in Columns orientation is useful to view a single record at a glance.

Fields:

• Click View > Reorder Fields to order the fields to your liking. For example, you might put common information such as Name, SSN, Status, DOB, and Sex first, followed by active-only fields and then by inactive-only fields. To find a specific field, type the first few characters of the field's name. This is useful, for example, if you want to find SSN in a long list of fields that is not sorted alphabetically. To restore the default of displaying all fields in the order they were imported (or created), click Reset.

• To keep some fields visible (e.g., Name, SSN, Status, DOB, and Sex) as you scroll through other fields, use View > Freeze Fields.

• Click View > Hide/Unhide to hide selected fields from display or to display fields that are currently hidden. If any fields exist in the database but are hidden, a warning appears in the status bar.

  

• Click Historical Data to view fields from other databases, such as prior year data, alongside fields in this database.

Records:

• Click View > Filter to enter a selection expression or recall an expression from the library. Spreadsheet Edit will then display only those records that meet the selection expression.

The selection expression appears above the data, along with a count of how many records meet the selection expression out of all records. You can edit the selection expression directly if you wish. To insert a new row into the selection expression, press Alt+Enter. To evaluate the selection expression, press Enter or click . Note that the selection expression is not automatically reevaluated after you make changes to the data; thus, after making data changes, you should again press Enter or Go to be sure that the records selected for display reflect those changes.

Click to recall or save a selection expression in the library.

• Click View > Sort Records to specify fields to be used as sort keys and whether to put records in ascending or descending order. You may specify any number of sort keys. The records will first be sorted by key 1. Where there are ties in key 1, the records will be sorted by key 2. And so on.

Note: The Sort Records command affects only the order in which records are displayed within Spreadsheet Edit. It does not permanently sort the database.

 Use View > Zoom to increase or decrease the font size. You can select from one of the preset choices or enter your own percentage.

The View settings can all be saved in the Style Library for later reuse. For more information, see Style library below.

 

Style library

All View settings can be saved in the Style Library for reuse. For example, you might save a Style for viewing active records and another for viewing inactive records. You might also save Styles for different types of databases: grouped vs. seriatim, hourly vs. salaried, and so forth. The name of the current Style appears in the status bar in Spreadsheet Edit, located at the bottom of the screen:

To save the current View settings as a Style:

1. Click View > Style Library.

2. Click New.

3. Provide a name and click Save As New.

4. Click Load (the saved Style should already be selected) to associate the Style with the current database.

To modify the current Style:

1. Click View > Style Library.

2. Click Edit (the current Style should already be selected)

3. Make changes. To change all the settings to reflect how the data is currently displayed, click Set to Current View.

4. Click Replace.

To reset the current view to “<all fields and records>”, click View > Clear Settings. The following View settings are reset when you click Clear Settings:

• All fields are visible, displayed in their original order.

• The Selection Expression is cleared, so that all records are displayed.

• The records are displayed in their original order.

The following View settings are not reset immediately when you click Clear Settings:

• Zoom

• Records in Columns (if this had been the selection for display instead of Records in Rows)

These settings will remain in effect until either the Spreadsheet Edit session is closed or a different Style is loaded. If you do not load a different Style before closing the Spreadsheet Edit session, then the next time you open this database, the settings for Zoom and transposing (i.e., displaying records in columns vs. rows) will have reverted back to the default settings of 100% and Records in Rows, respectively.

When you select Spreadsheet Edit, the current database opens with whatever Style you last used with it (note that if you used this Style with a different database after it was last used with the current database, its settings may have changed since the last time you opened the current database).

Note that if you open a database within Spreadsheet Edit (i.e., by selecting the Spreadsheet Edit command first and then selecting a database, by clicking File > Open), you have the opportunity to select “<all fields and records>” in the Open Database dialog box. (By default, ProVal opens the database with the last Style used with it.)

If you open a database, customize the view and then either Close the database, Exit from Spreadsheet Edit or click Clear Settings, ProVal will prompt you to save the Style changes.

 

Format numeric fields

For numeric fields, you can control the number of decimals displayed and the presence of dollar signs and commas. The data itself is retained to full precision, regardless of the formatting style.

There are two methods available to change the formatting style for numeric fields:

1. Click Home > Data Dictionary and modify the formatting style for the fields in question. This change applies to all databases in this client.

2. Alternatively, use the formatting buttons on the toolbar to toggle the dollar sign, toggle the thousands separator, increase the number of decimal places, and decrease the number of decimal places for the selected fields. Only numeric fields are affected. These buttons affect the field’s formatting style in the Data Dictionary, i.e., they apply to the entire field (and all databases in this client).

   

Selection expressions library

Click View > Selection Expressions Library to manage saved selection expressions. For more information, see Selection Expressions.

You can also access the selection expressions library, retrieve existing expressions, or save the current selection expression in the library by clicking next to the selection expression that appears above the data.

 

Change a column’s width

• To change the width of one column, drag the boundary on the right side of the column heading until the column is the width that you want.

• To change the width of multiple columns, select the columns that you want to change, and then drag a boundary to the right of a selected column heading. All of the selected columns will have the same width.

• To change the width of columns to fit the contents (for the visible cells), select the column or columns that you want to change, and then double-click the boundary to the right a selected column heading. All of the selected columns will have the same width.

 

Spreadsheet statistics

You can select cells and get instant stats such as sum, count, average, min, and max for your selection.

   

If you have selected over 5,000 cells, you will be given the option to load the data to populate the stats as shown below:

The spreadsheet statistics turns off when over 1,000,000 cells are selected.

You can remove one or more of the stats by right-clicking on the status pane and unchecking the stat:

 

Find data errors

ProVal includes a number of data screening tools that you can use according to your needs. The most basic level of data screening is checking for duplicate key values (e.g., duplicate SSNs), since further data screening, as well as several other ProVal activities, require a database to have unique key values. To check for duplicates, click Screen > Find Duplicates. For more information, see Find Duplicates.

Most often, the existence of duplicate key values is an error, and you can resolve the situation by correcting the record(s) for which the wrong value is present. If the reason for the duplicate keys is that your data includes multiple records for the same person, you will need to consolidate those records in order to perform further data screening. If your data has duplicate keys, you can either resolve them by hand or use Screen > Resolve Duplicates. For more information, see Resolve Duplicates.

More extensive data screening tests can be applied using Screen > Screen Data. ProVal supplies many tests that you can start with. You can choose which of these built-in tests to apply to your data, and you can also specify for each test whether to use ProVal's default categorization of a failure as a Warning or Error. (The distinction between warning and error has no intrinsic meaning to ProVal beyond the purpose of helping you to gauge the seriousness of data problems encountered.) You can also add screening tests tailored to your data's requirements. For more, see  Screen Data and Custom Screening Tests.

 

View data errors

Errors and warnings identified by the Find Duplicates and Screen Data commands are recorded in the database’s error log by clicking Log Errors when viewing the output within these commands. (During the data import process, you can also write errors to the database’s error log. Since import errors are either invalid values or unknown codes, they cannot be stored in the database, and the error log and the database will display the item values as blanks.)

Once errors and warnings are present in the current database’s error log:

• The data values relevant to the errors and warnings are highlighted with a red triangle and yellow background in Spreadsheet Edit.  For example:

All fields involved in a failed test are highlighted. Thus, for example, if the error condition failed is "Hire age outside of valid range", and if DOB and DOH are the data fields involved in determining hire age, the highlighting will be applied to both fields. The highlighting of a field is removed if the value is changed, and, when that change is saved, the highlighting of any other field involved in that error is also removed. Note that error highlighting resulting from the Find Duplicates or Screen Data process is removed regardless of whether a change that is made actually corrects the error. (The validity of the change will not be reassessed until you rerun the relevant screening process.)

Hovering your mouse over a highlighted cell shows the errors and warnings related to that cell. Hovering your mouse over the RecID row heading with a red triangle shows all errors for the record. For example:



• To view only those records with errors or warnings, click Screen > Select Records with Errors. Initially, you'll see records with <All errors>, but you can narrow this down by selecting a specific error or warning from the list. Alternatively, you can click Choose Multiple Errors to select a set of errors or warnings, such as errors for active participants or errors from custom screening tests.

• To see a report of all errors and warnings, click Screen > View Error Log. From here, you can print the list or save it to Excel. You can also purge the error log of all or selected errors. For more, see View Error Log. Alternatively, to produce a client-friendly version of the error log to ask your client to review and provide corrections, click Screen > Data Questions. For more, see Data Questions.

• To see a report of errors and warnings for selected records, first select the records, e.g., with the mouse. Then, either right click and select View errors for selected records from the shortcut menu, or click Screen > View Error Log > Errors for Selected Records.

 

Approve data errors

In some cases, errors and warnings flagged by Find Duplicates or Screen Data may be for legitimate, but unusual, data. For instance, a large pay drop due to transfer. In these cases, you can mark errors for this unusual data as approved and the reason why. Approving an error is useful because it excludes it from downstream activities, such as preparing a list of data questions.

To approve an error, first select the records, e.g., with the mouse. Then, click Screen > Approve, select the errors you want to approve, provide a reason, and click Approve. The approval will be logged with the current timestamp and your username. If you find that you are approving all errors flagged by a particular screening test, that's probably a sign that the test is flawed; consider modifying or turning off that test.

You can also import approvals from another database, such as from a prior year, by clicking Screen > Import Prior Approvals, selecting the prior year database and approvals to import, and clicking ​Import.​ By default, approvals that involve comparisons between current year data and prior year data are not selected, since those comparisons are likely to be different in the current year.

Once approvals exist in the current database:

• The data values involved in the approval (i.e., formerly involved with the error) appear with a green triangle in Spreadsheet Edit. If you hover your mouse over a highlighted cell or the RecID row heading, you can see a summary of the approvals. For example:

  

• To remove an approval, first select the records, e.g., with the mouse. Then, click Screen > Approve > Remove Approvals. Alternatively, view the error log by clicking Screen > View Error Log, and then click Approve > Remove Approvals. 

• To view only those records with approvals, click Screen > Select Records with Errors. Initially, you'll see records with both Errors and Approvals, but you can narrow this down to Approvals Only.

• To see a report of all approvals, click Screen > View Error Log and choose Approvals Only once the report displays.

• To see a report of approvals for selected records, first select the records, e.g., with the mouse. Then, either right click and select View errors for selected records from the shortcut menu, or click Screen > View Error Log > Errors for Selected Records, and choose Approvals Only once the report displays.

Note that approvals persist when re-running Find Duplicates or Screen Data. For example, if you re-run screening tests, you won't lose any previously entered approvals.

​

Correct data errors

After you've found (and possibly approved some) data errors, it's typically time to ask the client about those errors.

• To produce a client-friendly spreadsheet of errors for your client to review and provide corrections, click Screen > Data Questions. For more, see Data Questions.

• To merge in data corrections from your client from a spreadsheet originally generated using Data Questions, click Screen > Import Corrections. For more, see Import Corrections.

 

Reconcile statuses from prior year

Click Screen > Status Reconciliation to analyze differences in a coded field (e.g., Status) between two database files (e.g., for the current and prior year). For more information, see Status Reconciliation.

Alternatively, use Screen > Screen Data to identify unexpected changes from a prior year. For more information, see Screen Data.

 

Compare to an earlier version or prior year's data

Click Review > Compare Databases to view changes made between one version of a database and another. This has some very useful applications:

• Reviewing data changes. While a database’s change history is an important tool for reviewing data changes, it doesn’t provide the new and old data values for every change. If you make a copy of your database after importing, a simple Compare between the original and current database will reveal the old and new values, plus added and deleted records.

• Finding records affected by a change. If you make a change in assumptions or plan provisions that only affect a few participants, a compare of two individual results databases (before and after the change) will reveal the records that were affected.

• Quickly comparing year-over-year data. A comparison between the current and prior year’s valuation data can reveal unexpected changes (e.g., changes in dates of birth) or changes you might not otherwise look for (e.g., a change in someone’s name) before you engage in more rigorous data screening. While not a substitute for data screening, this comparison is an easy initial check on small- or medium-sized databases to see if the data import looks reasonable.

The report can be tailored to your needs by clicking Customize to select fewer fields or omit record-by-record details.

 

View the database’s properties, change history, or notes

Click Home > Properties to see basic information about the current database (e.g., number of fields, number of records, etc.).

Click Home > Change History to see a history of changes made to the current database.

Click Home > Notes to see or modify notes for the current database. Notes are generally entered by a ProVal user. However, sometimes ProVal fills them in for you, like when you create a database using the Group Data command.

Properties, Change History, and Notes lead to the same Properties dialog, but start on a different tab. For more information, see Database Properties.

 

Group data

Click Home > Group Data to produce a database in which each record represents a group of employees having similar characteristics. A common use for grouped data is it to reduce the total number of records, and hence the runtime, for Core Projections without having a material effect on results. For more information, see Group Data.

 

Export data

The quickest way to export data as it’s displayed in Spreadsheet Edit is to copy and paste it into Excel.

1. Select the cells you wish to copy (e.g., using the mouse). To select all cells, click “RecID” in the upper left hand corner or press Ctrl+A.

2. Click Home > Copy with Titles (this copies field names and RecIDs in addition to data values).

3. Open Excel and paste.

Another quick way to export data is to use File > Import / Export Data > List Data. For more information, see List Data.

You can also export data with a Record Layout that also allows you to modify the content (e.g., to divide an annual amount by 12). For more information, see Export Data.

 

Resolve duplicate records

Click Screen > Resolve Duplicates to condense multiple records with the same key field value (e.g., SSN) into a single record. For more information, see Resolve Duplicates.

 

Select a random subset

Click View > Filter > Subset Selection to select a specified number of records at random from the database or select every nth record starting with the first. After using this command, a selection expression of “#SUBSET” can be used to view these records. For more information, see Subset Selection.

 

Merge in prior year’s individual aggregate results

Click Home > Merge > Merge Individual Aggregate to merge in a prior year’s results for allocating assets to individual records. For more information, see Merge Individual Aggregate.

 

Keyboard shortcuts

Files, printing, and help
To do this	Press
Create a new database.	CTRL+N
Open an existing database.	CTRL+O
Close the current database.	CTRL+F4
Save manual changes (including cut and paste) to the current database.	CTRL+S
Save the current database (including manual changes) with a new name	F12
Print.	CTRL+P
Get to the Help (for the selection expression if the cursor is there, else for Spreadsheet Edit).	F1
Enter data
To do this	Press
Edit the active cell and position the insertion point at the end of the cell contents or drop down the choice list for coded fields.	F2
Drop down choice list for coded fields.	ALT+down arrow
Edit the active cell and then clear it, or delete the preceding character in the active cell as you edit cell contents.	BACKSPACE or SHIFT+DELETE
As you edit cell contents, delete the character to the right of the insertion point or delete the selection.	DELETE
Delete text to the end of the line.	CTRL+DELETE
Complete a cell entry and select the next cell below.	ENTER
Complete a cell entry and select the previous cell above.	SHIFT+ENTER
Complete a cell entry and select the next cell to the right.	TAB
Complete a cell entry and select the previous cell to the left.	SHIFT+TAB
Undo the last manual change (including cut and paste).	CTRL+Z
Redo the last undo.	CTRL+Y
Cancel a cell entry.	ESC
Copy and paste cells
To do this	Press
Copy the selected cells.	CTRL+C
Cut the selected cells.	CTRL+X or SHIFT+DELETE
Paste copied cells.	CTRL+V
Clear the contents of the selected cells.	DELETE or CTRL+DELETE or SHIFT+CTRL+DELETE
Move and scroll
To do this	Press
Move one cell up, down, left, or right.	Arrow keys
Move one cell to the right.	TAB
Move one cell to the left.	SHIFT+TAB
Move to the edge of the current data region (data region: A range of cells that contains data and that is bounded by empty cells).	CTRL+arrow key
Move to the beginning of the row.	HOME
Move to the upper left hand cell of the data.	CTRL+HOME
Move to the bottom right hand cell of the data.	CTRL+END
Move down one screen.	PAGE DOWN
Move up one screen.	PAGE UP
Move one screen to the right.	ALT+PAGE DOWN
Move one screen to the left.	ALT+PAGE UP
Scroll to display the active cell.	CTRL+BACKSPACE
Display the Find dialog box.	CTRL+F or SHIFT+F5
Display the Go To Field dialog box.	CTRL+G or F5
Move from the data to the selection expression.	ALT+S
Move within a selected range
To do this	Press
Move from top to bottom within the selected range.	ENTER
Move from bottom to top within the selected range.	SHIFT+ENTER
Move from left to right within the selected range. If cells in a single column are selected, move down.	TAB
Move from right to left within the selected range. If cells in a single column are selected, move up.	SHIFT+TAB
Move clockwise to the next corner of the selected range.	CTRL+PERIOD
Move and scroll in End mode
“END” appears in the status bar when End mode is selected.
To do this	Press
Turn End mode on or off.	END key
Move by one block of data within a row or column.	END+arrow key
Move to the bottom right hand cell of the data.	END+HOME
Move to the end of the current row.	END+ENTER
Select cells, rows and columns
To do this	Press
Select the entire column.	CTRL+SPACEBAR
Select the entire row.	SHIFT+SPACEBAR
Select the entire data grid.	CTRL+A
With multiple cells selected, select only the active cell.	SHIFT+BACKSPACE
Extend a selection
To do this	Press
Extend the selection by one cell.	SHIFT+arrow key
Extend the selection to the last nonblank cell in the same column or row as the active cell.	CTRL+SHIFT+arrow key
Extend the selection to the beginning of the row.	SHIFT+HOME
Extend the selection to the upper left hand cell of the data.	CTRL+SHIFT+HOME
Extend the selection to the lower right cell of the data.	CTRL+SHIFT+END
Extend the selection down one screen.	SHIFT+PAGE DOWN
Extend the selection up one screen.	SHIFT+PAGE UP
Modify the view
To do this	Press
Hide the selected fields	CTRL+0 (zero)
Unhide fields	CTRL+SHIFT+0 (zero)