Getting Started #
Before importing or updating records, always use the Format Table button. This ensures that:
- Missing default values are added where applicable.
- Cell values are properly formatted to match the database requirements.
Using Format Table helps prevent errors and ensures data integrity during the import or update process.
Format Table Options #
The Format Table button has several options, which can be individually controlled under the Options button on the ribbon.
Apply Conditional Formats
- If you want conditional formatting applied to the sheet, leave this option checked.
Add Default Values
- Field default values will be applied to empty cells only.
- Checkbox fields (Y/N) will default to N.
- Most numeric fields will default to 0.00.
Add Missing Co/Group Values
- Company/Group values will be applied to empty cells only.
- These values are controlled by the Destination Company field under the Options button.
- (See Replace Co/Groups button below for more details.)
Change Data Format
- Applies Code Formats to fields such as Job, Contract, Phase, GL Accounts, and Equipment to ensure consistency.
By default, the Options Profile has:
- Apply Conditional Formats unchecked
- All other options checked
These settings help standardize data before importing or updating records, reducing errors and improving data accuracy.
Conditional Formats #
The Conditional Format option helps identify data errors before importing records. The available conditional formats include:
- Required Cells – Missing required values will be red.
- Max Length Exceeded – Values exceeding the field’s allowed length will be green.
- Invalid Number or Date – Invalid number or date entries will be orange.
- Number Outside Allowable Range – Values outside the accepted range will be purple.
- Duplicate Records – Duplicate records in the table will be gray.
⚠ Recommendation: It is best to leave Apply Conditional Formats off when working with large datasets, as it can slow down Excel.
Sub-Menu Buttons #
The Format Table button includes a sub-menu with the following options:
- Replace Co/Groups – Replaces all existing Company/Group values instead of only filling empty cells. Useful when copying records from one company to another.
- Delete Empty Columns – Removes all columns that contain no data. Be cautious, as this may remove columns you need. Consider using Move Empty/Optional Columns instead.
- Format Selected Column – Formats a single column instead of the entire table, providing a faster option when only one column has changed.
- Move Empty/Optional Columns – Moves empty and optional columns to the end of the table, keeping required and populated columns at the beginning for easier analysis.
- Copy Identified Rows – Duplicates selected rows for importing multiple copies of the same setup. Assign Copy in the *Action column before using.
- Auto Size Columns – Adjusts the width of all columns to fit the content.
- Apply Default Row Height – Sets the row height to 15 for all rows in the table.
- Hide Current Sheet – Hides the active sheet and toggles the Hidden value on the TAD sheet. This feature is mostly obsolete.
- Add Column Properties Table – Inserts a table displaying column properties such as Required status, Field Length, and Default Values.
- Regenerate Excel Formulas – Restores Excel formulas in columns that had them removed by formatting actions, preventing manual re-entry.
These options provide greater flexibility and efficiency when working with table formatting and data preparation.
Replace Co/Groups
The Replace Co/Groups button updates all Company/Group values, replacing both existing and empty fields. This differs from the Format Table button, which only applies values to empty cells.
This feature is particularly useful when copying records from one company to another. Instead of manually adjusting each row, clicking this button will automatically update all Company/Group columns based on the Destination Company setting under the Options button.
Delete Empty Columns
The Delete Empty Columns button removes any columns that contain no data.
This can help clean up your layout by eliminating unnecessary columns, making tables more readable and manageable. However, be cautious—deleting a column removes it entirely from the table. If you are unsure whether a column is needed, consider using the Move Empty/Optional Columns button instead, as it provides a safer way to reorganize columns without losing data.
Format Selected Column
The Format Selected Column button allows you to format a single column instead of formatting the entire table.
This is particularly useful when only one column has been modified and needs reformatting. Instead of applying Format Table to all columns, which can be time-consuming, you can select any cell within the column and click this button to apply formatting changes quickly.
Move Empty/Optional Columns
The Move Empty/Optional Columns button reorganizes the table by moving empty and optional columns to the end while keeping required columns and populated columns at the beginning.
This can improve table readability, making it easier to analyze important fields without excessive scrolling. Unlike Delete Empty Columns, this option retains all data but optimizes the table structure for efficiency.
Copy Identified Rows
The Copy Identified Rows button allows you to duplicate one or multiple rows within the table.
This is useful when importing multiple copies of the same setup, reducing the need for manual copying and pasting. To use this feature, first assign Copy in the *Action column for the rows you want to duplicate. After clicking the button, a prompt will confirm the operation before the records are copied.
You will receive the prompt below when you click the button.
Auto Size Columns
The Auto Size Columns button automatically adjusts column widths to fit the content within each column.
This improves readability and prevents data from being cut off due to default column sizing.
Apply Default Row Height
The Apply Default Row Height button resets all row heights in the table to 15.
This ensures a consistent and uniform appearance, especially if row heights were manually adjusted or altered due to formatting changes.
Hide Current Sheet
The Hide Current Sheet button hides the active worksheet and toggles the Hidden value on the TAD sheet.
This was originally included for specific legacy features but is now rarely needed.
Add Column Properties Table
The Add Column Properties Table button inserts a reference table that provides key information about each column in the dataset.
The properties displayed include:
- Required status – Indicates whether a column must have a value.
- Field Length – Shows the maximum character length allowed.
- Default Values – Displays predefined values applied when no data is entered.
This feature is useful for verifying field constraints before importing or updating data.
Regenerate Excel Formulas
The Regenerate Excel Formulas button restores Excel formulas that were removed during formatting actions.
When using Format Table or other buttons, formulas may be replaced with static values. This option helps reapply the formulas, eliminating the need to manually enter them again.