LINK SCHEDULE EXPORT TO EXCEL

Many spreadsheet programs allow you to link files to them. In Revit we can use this to link Revit schedule exports to an Excel file.

The process is to create separate Worksheets in Excel for each Revit schedule export, link the export files, then create a formula in Excel to read data from the linked Revit export files.

This instruction shows the simplest way to do this using simple cell references. It relies on cell locations not changing between updates.

If this happens (for example additional floor levels being introduced or removed) you will need to check all the data has followed through and restructure your Excel schedule.

It is possible to do more elaborate Excel formulas that do checking but that requires some expertise in Excel, and is only worth doing if the schedule is going to be used over a long period, or changes to the structure of data is anticipated. The process described here is the same, just the formula used in cells would be different.

DESIGN YOUR EXCEL SPREADSHEET

First identify which folder the Excel file and schedule exports will live in. As we are dealing with linked files location and files can NOT be changed.

(e.g. RevitMaster/Schedules)

DON'T use dated folders. If you need to keep record copies make a copy of the Excel file and place it somewhere else. See DETACHING REVIT LINKS below.

Start an excel spreadsheet and create the schedule you want using dummy data.

CREATE SCHEDULES IN REVIT

Based on the data you need for your Excel schedule work out what schedules you need to create in Revit and what they contain.

For example if your Excel schedule contains GFA and Car Park numbers you will need to create separate schedules for GFA (an Area category schedule) and Car Parks (a Parking category schedule).

• The schedules can contain more data than you need for your Excel schedule if it makes it easier to manage.

• Use the same row order (Sorting) as your Excel schedule uses (e.g. Level ascending)

• Note that the order of columns and rows must not be changed at a later date as it will mess up the Excel schedule.

• Use a name for the Revit Schedule that identifies it as being used by Excel (e.g. prefix with identifier)

EXPORT SCHEDULES FROM REVIT

To export Revit schedules:

R > Export > Reports > Schedule

Repeat for each schedule created.

LINK REVIT EXPORT TO EXCEL

Open your Excel schedule file.

Create a new Worksheet, name it so it is the same as the Revit schedule export file name.

Highlight top left cell (cell A1).

Import the Revit schedule export:

Data > From Text

In the next dialog box select Tab delimited (it should be selected by default, but check).

Then hit Next > button.

Select each column and change the Column data format.

This will force the data to be the correct format in Excel.

As a minimum make any column that has numbers in it but are not going to be part of any calculation Text.

e.g. Sheet Numbers, Scale.

For columns that will be calculated (e.g. added up) make them General.

Note that calculations from Revit, like totals, come through as raw numbers, not Excel formulas.

When done hit Finish button and place at default cell A1.

Once all the data appears change the data properties so the data updates:

While in the same worksheet Data > Properties.

Untick Prompt for file name on refresh

Tick Refresh data when opening the file

Select Overwrite existing cells with new data, clear unused cells

Repeat for other Revit schedule exports (i.e. create new Worksheet etc.)

CREATE FORMULA LINKS

Go back to the worksheet with the main Excel schedule.

1. Highlight the cell you want to put data in (start at top of a row).

2. Type equal sign (=)

3. Go to the Revit schedule export worksheet

4. Select the cell with the data you want.

5. (the formula will be in the form: = 'worksheetname' ! cell )

6. Hit the tick in formula bar.

Back in the main Excel schedule highlight cells in the column from the one you just created downwards.

Then fill down:

Home tab > Fill > Down (or Ctrl D).

This will copy the formula down to the other values.

Repeat for other data.

For more elaborate formulas refer to:

OTHER SOFTWARE > EXCEL > EXCEL VLOOKUP

and

OTHER SOFTWARE > EXCEL > EXCEL USEFUL FORMULAS

The Excel file is now setup.

UPDATE EXCEL SCHEDULE

Make sure the Excel file is NOT open.

First re-export all export Revit schedules as per EXPORT SCHEDULES FROM REVIT above.

Open the Excel file.

Check the data in your excel schedule is as you expect.

Done!

Revit schedule exports can also be updated while the Excel file is open.

Data > Refresh All

Just say OK to security warnings.

DETACHING REVIT LINKS

If you need a record copy of your Excel schedule, or want to do some manual editing in it copy the file to another location.

Open it and Remove all linked files:

Data > Connections

Select all the links.

Hit Remove button.

Save the file.