Construction Progress Visualisation in MS Excel

Table of contents for Construction Progress Visualisation Model in MS Excel-

1. Introduction
2. Prerequisites
3. Steps for Construction Visualisation Modelling- Create WBS, Make Model, Use of Conditional Formatting
4. More Ideas- Schedule Linked Model, Cost Linked

1. Introduction

The concept of developing construction models to visualise progress is not new. The model development leads to various advantages. The models create a different impression on the stakeholders and increase their interest in the project.

The models can be developed in 2D or 3D. The modelling is done by using software such as AutoCAD, Revit, Navisworks etc. However, without the knowledge of such sophisticated software, the model can also be developed on MS Excel which is basic in nature and doesn’t offer multiple views.

This article will explain the process of developing a 2D construction visualisation model on MS Excel.

2. Prerequisites

I have tried to explain the process in a very easy manner so that anyone who has no experience in such modelling can create whatever they want. However, the basic understanding of preparing WBS and basic to moderate level knowledge of MS Excel will definitely help in grasping the concepts quickly. Of course, imagination.

So, let us dive into it to make the dynamic construction progress visualisation model.

3. Steps for Construction Visualisation Modelling

3.1 Create WBS

The Work Breakdown Structure is the skeleton of any project schedule. It is the breakup of the project into activities. The activities add up to deliver the objectives of the project.

The activities in a WBS are arranged in a hierarchical manner. Each hierarchy level is simply called the ‘level’. The sum total of activities at any level is equal to their head.

The detailing in the model is defined by the levels of WBS. Higher the levels in a WBS, greater will be the details.

If interested in reading more about WBS, click here.

Example-1: Single Storey Building WBS

1. Excavation
2. Footing
3. Column
4. Plinth Beam
5. Column
6. Beam & Slab
7. MEP & Finishing

Example-2: Bridge WBS

1. Pile
2. Pile Cap
3. Column
4. Pier Cap
5. Pedestals
6. Girder Erection
7. Wearing Coat
8. Side Railing & Finishing

Example-3: Road Work WBS

1. Subgrade
2. Sub-base
3. Base Course
4. Wearing Coat
5. Marking & Signage

3.2 Make Model

Before starting the modelling, decide which side of the elevation you want to show. For multiple views, multiple models need to be created.

The model can be made by adjusting the width of rows and columns. The required shape and size can also be achieved by merging the cells. The borders are to be applied to mark the walls, columns, beams, slabs, course etc.

The borders can be applied by directly choosing from the home tab of MS Excel. For borders, other than the default click on the small arrow icon on the right-bottom end of Font group in Home tab or press Ctrl+1 and choose border tab.

After making the model, I recommend to turn off the gridlines to make the model more clearly visible. You can turn off the gridlines by removing the tick from gridlines checkbox in the View tab.

3.3 Use of Conditional Formatting

Now start linking each WBS activity with the respective cell of the model. Click on the cell where formatting is to be done and follow-

Conditional Formatting> New Rule> (Use a formula to determine which cells to format) then click on the cell in the model (the formula bar will take the address of selected model cell(s)) and type the formula. Now click on Format button and choose the desired formatting.

Here come more choices for you as a modeller. You can show in-progress activities or simply go for 100% completion of activities. It depends on you how dynamic you want your model to be.

Formula for 100% completion of activity-

(Selected Cell) = 100

Formula for In-progress activity-

(Selected Cell) < 100

Still confuse about conditional formatting? Click here to clear your doubts.

Download the sample file here-

4. More Ideas

4.1 Schedule Linked Model

The schedule can also be made in the already created WBS. Upon putting the actual start and actual finish the % complete can be linked with the model by conditional formatting for actual progress visualisation.

The planned start and planned finish dates can be used for lookahead or complete visualisation.

The schedule made on MS Project or Primavera p6 can also be linked to these excel model.

The schedule linked model works on the date input. It becomes a powerful tool for lookahead.

4.2 Cost Linked Model

The cost incurred and cost earned can be linked to calculated and put into a column in WBS which can be linked to the model to determine the required cashflow etc.

Liked the article? Or have more ideas on developing construction progress visualisation models? Let me know in the comments.

Let me also know in the comments if you have made a model or willing to make one.

Happy Engineering!