, ,

Excel Tips: Creating Data Tables Part 3: Two Variable Data Tables – How To Display More Than One Return Metric Within a Cell

In the third part of this data table series, I’d like to show you how you can create data tables with multiple return metrics within each cell of the data table as shown in the image below.


The way to do this is actually quite simple. If you are unfamiliar with how to create a two-variable data tables or need a refresher, please click here and read the post and/or watch the video in part 2 of this mini data table series.

Before we move forward, please download the template below as that is what I will be referencing to help explain how to do this in both the video and the post.

Template Download


Step-By-Step Guide

To begin, let’s click on the cell in the top left of the data table that is the reference cell for the table’s returns. In the template, this is cell F23.

Everything that you need to do in addition to the standard procedure for creating the data table will take place in this cell and you really only need to familiarize yourself with three excel functions.

The Ampersand (&), Quotation Marks, and  the =TEXT( ) Formula


Step 1: The Ampersand

The ampersand will allow you to combine and view multiple results from various cells within one cell. However, what it cannot do is format these multiple results in a way that the user can easily read it or in a way that the creator of the data table would want to display the info. For example, let’s combine the profit and IRR into one cell. To do this, type the following formula in cell F23:


What is returned is the following:


(Excel Tip: If you can’t see the complete number in the cell, click on cell F23 and type Alt > O > C > A)

If we look closely, you can see both return metrics:

0.243286199781149    1216214.4

The blue is the IRR and the red is the profit.

Step 2: Quotation Marks

The quotation will allow you to add text in between cell references. Using the same formula from above, insert the following:

=D14&” Hello “&D16

(Don’t forget to add the spaces between the quotes and ‘Hello’.)

The result should be:

0.243286199781149 Hello 1216214.4

Now that you see that you can add text between the quotes, erase Hello and in the quotation marks, insert a backslash ( / ), so that the result is:

0.243286199781149 / 1216214.4

Step 3: =Text(value, format_text)

The =Text( ) function will allow you to format the values from the different cells being referenced. =Text is a two part function that asks you to first link the cell you want to reference and then asks you how you want to format the result. In our example, let’s change the current formula from:

=D14&” / “&D16

to this:

=TEXT(D14,”##.#0%”)&” / “&TEXT(D16,”#,###,###”)

The formula should result in this display: 24.33% / 1,216,214

As you can see, we formatted the IRR to show four numbers with a decimal point and a percent sign (“##.#0%”) and we formatted the profit to have commas (“#,###,###”) to display the number properly.

If you would like to display the profit, which is in the millions, in a shorter format, as shown in the image above, you can replace the




and add


onto the end of the formula:

=TEXT(D14,”##.#0%”)&” / “&TEXT(D16,”$#,###.0,,”)&”MM”

Which will give you the following result:

24.33% / $1.2MM

About the Author: Michael Belasco has seven years of real estate and construction experience. He currently works for a global real estate investment, development, and asset management firm in San Francisco managing large scale development projects in the city. Michael has both an MBA and Master in Real Estate with a concentration in Real Estate Finance from Cornell University.