How to Make a Building Stacking Plan using Excel in 6 Steps

Building Stacking Plans For Data Visualization

Everyone who is responsible for multi-story buildings that house multiple tenants values a great stacking plan. They provide easy to understand data visualization as to which tenants are where, how much space they occupy and what their occupancy status is. They are much easier to review than a detailed rent roll and help identify basic statistics quickly and efficiently. However, creating a versatile stacking plan can be a challenge. Many firms will use graphic programs such as Adobe Illustrator to create 'pretty' stacking plans which look great in offering memorandums, but often these can prove ineffective when they need to be updated or altered quickly. Maintaining stacking plans can be problematic especially when each suite status or size changes, or if users need to show different variations of the stacking plan layout. But do not fret, we have a solution to help create a versatile and auto-updating stacking plan using Excel.

Below are the six (6) basic steps to create a stacking plan using excel. Please note that these are the simple essentials for creating a stacking plan based on a simple rent roll (please note that additional customization ideas and features will be described in a subsequent blog). In addition, there are example files available to download below.

Create a Simple Rent Roll Spread Sheet

First open excel and create a simple rent roll table on its own sheet. You can name the sheet/tab 'Suites_Rent_Roll' or something like that to help keep it organized. The columns (and their formatting) should include the following

• Floors: Format as numbers with 0 decimals. You can use general text and if necessary (Example: Basement), but the end results will sort better if they are formatted as numbers.
• Suite Labels: You can format these as general text because you may have suites with lettering (Example 200 A or 200 B). Again, the end results will sort better if they are formatted as numbers but this is not as important.
• Size: Format as numbers only with 0 decimals. This is the square footage (SF) of the suite. This is going to be a single number such as 2,000.
• Status: Text only and we suggest adding Data Validation or a drop menu with the options: Occupied, Vacant, or Other). Additional options can be added based on your needs, but again it is best to use Data Validation or a drop menu list to keep the options consistent.
• Include: (or Include in Calcs). This is going to be a 'Yes, No' option only. Thus use text only for formatting and again using Data Validation with Yes / No as the options. This column is great so that variations of the stacking plan can be included. For example Suite 200 and Suite 210 can each be included in one stacking plan, but another Suite Label of 200-210 can also be created and not included in one plan but yet included in another plan.
• Lease Start: Date formatting
• Lease End: Date formatting
• Tenant Name: Text formatting.

Additional information can be tracked by adding more columns, but the above are the basics. Select here if you need a reminder on how to create Data Validation or drop-down lists for excel.

Convert the Rent Roll Range into an Excel Table

Once the data has been entered, we then need to convert it into an Excel Table. Excel tables are excellent for keeping data and formulas accurate, plus they are great for sorting and formatting. You can learn more on converting data to tables here. As noted on the example image, it is a good idea to extend the range of rows so that the table area is actually larger than the information inputted. This way additional suites can be added if needed.

Identify the Table Name for the Rent Roll

Once the Excel Table has been created, we need to determine or change the name of the table. The table name can be found by first selecting any cell within the table, then by clicking in the range finder in the top left above Column A and Row 1. When you click the input box a drop-down menu will appear showing you the table name. You can select the table and rename it if you so choose. Just remember the table name (case sensitive) for Step 4.

Create a Pivot Table with Rent Roll Table Information

Pivot Tables are the quickest and most efficient way to sort and tally information within a table. If you are not familiar with Pivot Tables we would highly recommend searching on YouTube for a quick video about them as they are great for organizing your data. That said, you do not need to be proficient in Pivot Tables in order to proceed with the next steps.

First make sure you have a new sheet / tab and label it 'Pivot Table' or something like that, again to help keep the data organized. Then on that sheet, select where you want to place the pivot table (we used A1 in this example). Next, go to Insert > Pivot Table. Here you will see the options to create a pivot table. In the Select Table or Range option, simply type in the table name as shown in Step 3 and then hit OK. Again in our example the Table Name was Table2.

Organizing the Pivot Table To Make a Stacking Plan

Once the pivot table has been created, the pivot table options then need to be confirmed. First make sure the Pivot Fields option is open. Typically if you click on the cell that you inserted the pivot table into, the pivot fields will appear on the right of the screen. Next, you are going to drag the following columns to the following areas:

• Floors: Drop into Rows section
• Suite Labels: Drop into Columns section
• Size: Drop into Sum of Values section. Make sure Sum is selected.
• Included: Drop into the Filters sections. Here you will then select the filter for Included to show only 'Yes'. This filter will then hide the suites that you tagged as no on the prior rent roll table.

When completed, the pivot should look like the example image with a single row for each floor and column for each suite with the size in it. Additional information / fields can also be included such as 'Status' or 'Lease Expirations'. Those options should be dragged into the columns option below the Size field. It will add another column for subtotals, but that column can be removed by right clicking on one of the subtotal headers and then uncheck the subtotal option.

Selecting a Stacking Plan Chart Type

Once the pivot table has been completed and it properly formatted, simply select the data to graph it. Start with the Floors Column and highlight all the way until the last suite in the last row. Next you will go to Insert > Bar/Column Graphs. You have two options to choose from. Stacked Bar Charts or Stacked 100% Bar Charts. The Stacked Bar chart shows the suites based on their size, thus if you have different floor sizes it will show on the stacking plan. The Stacked 100% Bar Chart will show all the suites stacked at 100% of the width of the chart and the SF will dictate the percentages.

Once your graphs have been created, you can format them as you would any other type of graph, add data labels, format the SF number, add a title, hide the legend etc.

Warning!! You will need to refresh your pivot table if you make changes to the data.

To refresh you pivot table, simply click right click on the cell that you inserted the pivot table to and hit refresh. This will update all the pivot information as well as the graph. This is a manual feature that needs to be done when updating stacking plans. For your convenience, we did make an example stacking plan file below called the 'Full Version File' that includes buttons and features that handles all of the refreshes for you.

Download RCS Excel File to Create a Stacking Plan

Download RCS Full Version xlsm File Download The Simple xlsx Version From Above

Feel free to download the excel files using the links above. We offer two free versions of it. The simple version has all the features noted above. The Full Version provides additional options, short cuts, options and more. This file can automate and created the stacking plan application noted prior. However please note that the Full Version file is an excel macro file and thus does use macros. Some virus scans or corporate IT systems will disable macros, or they may be flagged as potential virus issues. Often times email systems will also scrub files that contain macros from emails thus emailing this file may not be feasible. If you are familiar with excel files that contain macros, you will have no issue with this. For more information on macros, check out Microsoft's site about them simply google 'using macros in excel' for more details. Just for basic understandings, macros are simple lines of code that automate functions on this sheet, and they must be enabled in order for this sheet to work. In addition, the file must be saved as an XLSM file. Notice the (m) at the end of the file name. This means macros are included in the excel sheet. If this sheet is altered or saved as a different type of excel document (such as XLS) it will not work properly. It is best to create the stacking plan on this file and it then can be copied and pasted into another spreadsheet if needed. One last thing, please provide coding credits to Real Clear Software if/when using any code used from this system on other applications.

Going Beyond Excel for Stacking Plans and Information

Even though we quoted in a recent blog The Slow Death to Excel, that more real estate firms are using better software applications to help with real estate tracking, excel will always have a purpose. Being able to use excel efficiently is essential to being successful with it. That noted, creating stacking plans either via your own excel file or from the file we provided above, still only results in a single building being tracked with a single excel file. If you have multiple properties that means you are going to need multiple excel files. In addition, if you have multiple team members referencing and using the information there is always the issue of confirming which file is most up to date and accurate. When you have multiple locations and multiple people accessing that information, the data must be centralized and accessible by all. This is where Real Clear Software provides an ultimate solution and value. Real Clear Software tracks all the bulk portfolio information as well as the granular single location data. Stacking plans, custom data points, images, tasks, and a variety of additional information is centralized and organized for its users, making it easy and accessible to visualize and understand your real estate portfolio better.

Real Clear Software's commercial and cloud based real estate management software offers many advantages over excel spreadsheets and standard lease administration programs. RCS integrates portfolio, transaction and document management, project management, data security, performance measurement, data analysis & comparison, real time data access, stakeholder collaboration, CRM, demographics, and generation of various reports. For more information on RCS Portfolio from Real Clear Software, visit https://realclear.software, email support@realclear.software, or phone (949) 445-6220.