How-to Make a Tenant Timeline Excel Dashboard Chart

So last week, I posted this question that I responded to in an Excel forum.  I also asked you how you would solve this problem using Excel charting tools.  I had one great response from Pete that looked like it would work great.  Here is my solution which is very similar.  But first, in case you missed the last post, here is the question again:

 

Names and lease expirations

I’m trying to figure out how to create a chart showing names and the corresponding lease expiration in Excel 2007.
There is 1 column with the name of tenants and the next column has the date the lease expires. They are labeled “tenant” and “lease expiration”, respectively. Out of the 15 tenants a couple have text instead of dates.
I was thinking of a scatter plot, with the names of the vertical access and dates on the horizonal, but can’t figure out how to do this. Any help would be greatly appreciated

Here is the chart that I created for the Tenant Timeline dashboard chart:image

I really liked it.  What do you think?  Let me know in the comments below.  Here is how I built this Excel dashboard chart:

 





The Breakdown

1) Set up your Data Range

2) Set up your Chart Data Range Calculations

3) Create Stacked Bar Chart

4) Change Fill of “Lease Expiration Fill” Series



5) Add Data Labels

6) Remove Vertical Axis

7) Change Horizontal Minimum and Maximum and Major Unit

8) Change Vertical Grid Line Style

 





Step-by-Step

1) Set up your Data Range

Here is how I set up my data for the chart:

AB
1
2TenantLease Expiration Date
3Mike11/16/2013
4Mary8/5/2013
5John10/17/2013
6Jack12/18/2013
7Justin8/26/2013
8Steve8/14/2013
9Mark7/28/2013
10Tony9/24/2013
11Sam11/8/2013
12Steph10/30/2013
13Joe9/13/2013
14Frank8/6/2013
15Monty9/9/2013
16Macy12/15/2013
17Yani11/14/2013

Sheet1

You should be able to copy and paste this data range directly into Excel to try it yourself as you go through this tutorial.



 

2) Set up your Chart Data Range Calculations

Now I like the data in the graph to represent the actual lease termination date.  If I used the dates in Step 1, then I might be off 1 day.  So I like to do a few things for my chart.

a) I like to create a new column of data that I titled:  “Tenant + Date”

This is what I will use for the data label.  It is a combination of the tenant name and the date of their lease termination.  So what we want to do is to create a formula in cell D3 that looks like this:





D3     =A3&” “&TEXT(B3,”m/dd”)

What this is doing is concatenating the tenant name with the lease date.  You can read more about the concatenation technique in this post:

 

image

I will show you more about the Text formula in a future posting.



After you put the formula in Cell D3, copy it down to D17.

b) Now we need to create a series that we will use to put a spacer from the left vertical axis to one day before the tenant’s lease expires.  This is a pretty simple formula that you need to put in cell E3 and then copy down to E17

E3    =B3-1

This is simply subtracting one day from the lease expiration date located in cell b3.

image





 

c) Finally, we need to put in a data point that will represent the lease termination date.  Just highlight cells F3:F17 and type in the number 1 and then press CTRL+Enter to put a value of 1 in the “Lease Expiration Date Marker” chart series.image

Your final Excel Chart data series should now look like this:image

 

3) Create Stacked Bar Chart



Now that we have set our data up the way we need, now we should create the Excel Stacked Bar Chart.

Highlight the chart data in the range of D2:F17.  Then go to your insert menu and choose a 2D Bar Chart from the Chart group.  BUT, before you do this, you should first delete the column header in cell D2.  If not, you will have a problem as Excel won’t know what are your categories versus data points.  You can always add the column header back after you make your chart.  Check out this quick post about this topic:

image

Your chart should now look like this:image

Not too bad for a first try, but we need to clean it up a little bit.





 

4) Change Fill of “Lease Expiration Fill” Series

The chart I wanted to create was a one that only showed when the lease terminated.  To do this, we need to hide the “Lease Expiration Fill” Excel chart data series.  To do this, simply select the chart, then select the “Lease Expiration Fill” series (in blue) then press CTRL+1 keys to bring up the “Format Data Series” dialog box.  From there, select the Fill menu on the left and choose “No Fill” from the fill options:SNAGHTMLfae7984

Your chart should now look like this:image

You can see that each of the Leases are now floating over the corresponding date from the horizontal chart axis.



 

5) Add Data Labels

In this step we will make it so that we can get rid of the vertical axis and see each data point more clearly.  To do this, select the chart then select the “Lease Expiration Date Marker” series as you can see here:image

Then go to the “Layout Ribbon” and choose “More Data Label Options” from the “Data Labels” button:image

Then from the “Format Data Labels” dialog box, choose Label Options of “Category Name” only and a Label Position of “Inside End”:SNAGHTMLfb6f45e





Your chart should now look like this:image

It is starting to look very good now, just a few more things to clean up the chart junk.

 

6) Remove Vertical Axis and Chart Legend

Click on the vertical axis and press your delete key.  Your chart should now look like this:image

Almost there, just 2 more steps.

 

7) Change Horizontal Minimum and Maximum and Major Unit

I don’t like the way Excel guesses at the way that I want my horizontal axis to display, so click on the horizontal axis and then press CTRL+1 to bring up the Format Axis dialog box.  From there, change the minimum to 41470, the maximum to 41639 and the major unit to 14:SNAGHTMLfbd7715

Your chart should now look like this:image





 

8) Change Vertical Grid Line Style

Now just one last thing to do and that is to change the major vertical gridlines to a dotted line so that it doesn’t over power the data points.  To do this, click on the vertical gridlines and then press CTRL+1 to bring up the Format Major Gridlines dialog box.  Then change the line style dash type to a Dash format:image

Your final chart should now look like this:image

This is a very simple Excel timeline graph that can be used in lots of application.  Perhaps you can use it in your next project dashboard.

 

Video Tutorial

Here is a video demonstration on how-to make this simple tenant timeline:

 





Don’t forget to subscribe so that you can get the next post and upcoming specials delivered directly to your inbox.  Also, let me know what you think about this timeline technique in the comments below.

Steve=True





4 COMMENTS

  1. Your solution is amost exactly what I came up with. My only large difference, is that I sorted the tenants in the order of lease expiration date. That made my chart much easier to read and gave a better sense of time.

  2. Just in case you are not aware, Excel 2013 allows for data labels from a separate source, other than the data series. That would allow the use of a scatter chart.

    Send an e-mail address and I will send an Excel 2013 version of Tenant Timeline.

    • Thanks Don,

      I sent you an email. I haven’t played around with 2013 enough yet. Thanks. I will have to post it to the page. Much appreciated.

      Steve=True

LEAVE A REPLY

Please enter your comment!
Please enter your name here