Time-Saving Tips for CostX®: Importing a Bill of Quantities from Excel
Each month we will be asking Exactal Product Specialists about time-saving tips and features within CostX® that allow users to work as efficiently as possible. For this month’s blog we spoke to Johnathan Mudrovcic, who works from Exactal’s Auckland office as a dedicated Product Specialist.
A huge time-saving tip for CostX® users is having the ability to import data from an Excel spreadsheet into a CostX® workbook, and vice versa. A typical example of such a requirement/workflow is when a contractor or a subcontractor receives an unpriced Bill of Quantities (BoQ) from a consultant Quantity Surveyor for pricing at tender stage. Gone are the days where contractors are required to manually write their rates down on a paper copy of a BoQ/Estimate – nor do they have to manually type their rates into an Excel spreadsheet. Instead, the unpriced Excel spreadsheet can be imported into a CostX® workbook and administered within CostX® thereafter.
Transfer of Excel data to a single workbook level can be easily done by using copy and paste. Functions and formatting are maintained when copying to CostX®, as shown below.
You can also import data from a single Excel file to multiple levels of a CostX® workbook. As there are many ways data might be stored in an Excel file, cleaning and organising your data before importing will streamline the process.
Below are a few steps on how to optimise an Excel file for import. Thereafter, I’ll provide a step-by-step process for importing an Excel file to multiple levels of a CostX® workbook.
Preparing Your Excel Import File
Structure and Layout
- The data to be imported can be located on one or more worksheets.
- It is possible to use the sheet names as level 1 group descriptions after import. If you intend to use the sheet names as group descriptions, ensure that the worksheet tabs in your Excel file are properly named.
- Do not merge any cells. Merged cells may cause some entries being imported into the wrong column or row. If your Excel file contains merged cells, unmerge them and place the contents in the appropriate cells.
- Hidden columns will not be imported. Ensure that all columns containing data to be imported are displayed.
If possible, use different text attributes (such as bold, italic and underlining) to style the item descriptions to be imported to different levels of the workbook. CostX® allows you to specify which workbook level an item should be imported to, based on the text formatting of its description. By applying different text attributes, you can quickly import all items to the correct workbook levels.
For example, by formatting an Excel file as shown below, and configuring CostX® to import items with bold and all-caps text to Level 1 of the new workbook, all the remaining text (other than Bold and/or CAPITALS) will automatically be placed at Level 2 of the new workbook.
Import Control Column
You can also use any column in an Excel worksheet as the Import Control column, and in the Import Control column, indicate which workbook level each item should be imported to. The Import Control column can also be used to exclude specific rows from being imported. Acceptable values for the Import Control column are 1-10 and x.
- Use 1 to 10 to indicate the workbook level to which an item should be imported.
- Use x to exclude a row from being imported.
The workbook levels indicated in the Import Control column take precedence over those indicated by using text formatting. For example, if a row is formatted the same way as the rows intended for workbook Level 1 but you wish to import it into Level 2, you can do this by adding the number “2” to the Import Control column for that row.
Importing Excel Data into CostX®
Step 1: Choose a File to Import
On the Workbooks ribbon, in the Workbook group, click the drop-down arrow under the Add button and select the ‘Import Workbook from Excel’ option.
In the Import Excel Workbook dialog, choose the required Excel file, and then click Open.
Step 2: Configure Import Settings
The Import Settings page is displayed after an Excel file is selected. You can configure the following settings:
Name of the workbook that will be created after importing the data. The Name field defaults to the name of the selected Excel file, but may be amended if required. The new workbook cannot have the same name as any of the existing workbooks.
Notes can be added to the workbook in order to easily identify it at a later stage. By default, the Notes field contains information about where the data was imported from.
Excel Worksheet Tabs
Select the worksheets that contain the data to be imported by checking the boxes next to the required sheet names. The sheet names shown in the Excel Worksheet Tabs field come from the worksheet names in your Excel file.
Import Control Column
If you’ve used an Import Control column in your Excel file, you’ll need to specify which column has been used as the Import Control column.
This field is mandatory. You must specify the Excel column to be mapped to the Description column in CostX®.
The Blank Rows field allows you to control what happens with blank rows.
- Import blank rows – import blank rows in the source Excel workbook to CostX®.
- Skip blank rows – do not import blank rows in the source Excel workbook to CostX®.
- Combine cells between blank rows – for each column in the source Excel workbook, combine the contents in the cells between blank rows. This option is useful when blank rows are used to separate items that span multiple rows, such as the example shown below.
In this example, if the Combine cells between blank rows option is selected, the lines of text between two blank rows will be concatenated to create a single description after import.
Skip Rows Containing Text
This option can be used to automatically exclude rows that have a description containing specific text. For example, when a worksheet contains multiple pages, this option can be used to exclude repeated headings with the word “Continued”.
Level x Tabs
As described before, you can use the text formatting of item descriptions to specify the workbook levels that different items should be imported to. The Level x tabs allow you to select the text attributes that will be used to determine what items will be imported to each level. To use this function, tick the Match from Formatting box, and then select the required text attributes. Rows whose descriptions do not match the formatting selected for any level will be placed on the lowest level.
The Use Worksheet Tabs option in the Level 1 tab allows you to use the worksheet names in your Excel file as the group descriptions on Level 1 of the CostX® workbook.
Step 3: Preview Import Results
After configuring the import settings, click the Preview button in the upper-right corner of the Import Settings page. All columns containing data in your Excel file as well as a Level and a Row column will be displayed in the Preview tab.
The Level column indicates the workbook level each item will be imported to, and the Row column shows the row number of each item in your Excel file. All other columns, except the Description column, have the heading “Skip”. Match each column in your Excel file to a column in the CostX workbook by right-clicking the column heading and selecting a CostX® workbook column from the context menu. If a certain column doesn’t need to be imported, leave its heading as “Skip”.
You can modify the import settings if necessary. Data in the Preview tab will update automatically.
Step 4: Import into CostX® Workbook
Once you are satisfied with all the settings, click the Import button in the upper-right corner of the Preview page to start the import. A new workbook will be created using the data from the Excel file.
In closing, the above points allow for so many combinations and permutations when importing Excel files into a CostX® workbook. This time saving tip is an extremely useful skill to add to your personal ‘CostX® Swiss Army Knife’. Whenever you are required to price an Excel file in future, this tip will save you so much time by simply importing it into CostX® for you to price, while combining the use of our world class measuring capability.
To learn more about the variety of time-saving features available with CostX®, feel free to contact your nearest Exactal office today.