Printable Excel Contact List With Dynamic Dotted Underlines

Excel Contact List Template With Dynamic Dotted Lines

Everybody Should Have One!

I'm currently going through a reorganization with my day job and I'm going to be interacting with a slew of new people on a regular basis.  I have always believed that in-person and vocal methods of communicating are a huge contributor to corporate success as it builds relationships much more quickly than written forms.  I have found that simply printing out someone's phone number will make you much more likely to pick up the phone as oppose to typing out an email.

So being the Excel nerd that I am, I set out to make a well formatted, professional-looking contact sheet that I could hang up next to my office phone.  Below is what I initially came up with:

Contact List With Static Underlines

Something Looked Off...

I sat back and took a look at my creation, but soon became disgusted with the aesthetics.  Something didn't look quite right.  It soon became apparent that the dotted line feature was the feature that was bugging me!  They needed to be different lengths.  After playing around a little bit, I soon got my contact sheet to look like this:

Contact List With Dynamic Underlines

*Alternative Solution

Sumit Bansal from TrumpExcel stopped by and provided a much simpler solution than what I had originally come up with.  He suggested to simply change the cell format to the custom code:


With this solution, you no longer will need to change the Horizontal Text Alignment to Fill as the custom format automatically adds period symbols all the way through the end of the cell width. If you would still like to see the way I approach this problem, you can continue reading on.

How I Did It

In my original contact list, I had two cell columns: one to hold the person's name/title and another that was blank with a dotted bottom border.  Hopefully, you agree with me that this layout is not too pleasing to the eyes!

In my final layout (pictured directly above this section), I only used a single column; which housed both the person's contact data and the separating dotted line. So how did I do this? There are two parts to the solution.  The first is an Excel formula and the second is a cell formatting tweak.

The Excel Formula

Here is an example of the formula I used to display the CEO's contact information

="Satya Nadella, CEO"& REPT(".",1000)

There are two parts to the formula, the text in which I want to display (surround in quotations) and a repetition of the period symbol one thousand times.  You can pick any number for the repetition, just make sure it's big enough to fill your entire column width with periods.  If you want your dots separated a little bit more you can add a space after the period in the REPT() function.

="Satya Nadella, CEO"& REPT(". ",1000)

The Cell Formatting

One little tweak to your cell's formatting that I highly recommend you add to your spreadsheet, is an adjustment to the horizontal text alignment.  To do this you will simply need to:

  1. Highlight all cells with your contact name
  2. Right-click and select Format Cells...
  3. Navigate to the Alignment tab in the Format Cells dialog box
  4. Open the Horizontal Text Alignment drop down box
  5. Select Fill
  6. Click OK

Now instead of having your dotted line go all the way through your blank phone number cells, the repeated dots will simply end where the cells column width ends.  This makes for a much clean interface while you are collecting all those phone numbers!

Of Course, I Have Some VBA To Make Life Easier

So you might be thinking, "Chris! It's going to be ridiculously annoying to type in a formula every time I want to update or add a contact."  And I completely agree with you!  That's why I made two VBA macro routines that will automatically convert and un-convert your text into the "contact list formula" I explained in the previous sections.  Check it out the VBA code in action below!

Add Dotted Line Formula

Sub AddDottedLineFormula()
'PURPOSE: Add dynamic dotted line formula to contact list names

Dim cell As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For Each cell In Selection.Cells

  If cell.HasFormula = False And cell.Value <> "" Then
    cell.Formula = "=" & Chr(34) & cell.Value & Chr(34) & "& Rept(" & Chr(34) & "." & Chr(34) & ",1000)"
    cell.HorizontalAlignment = xlFill
  End If

Next cell

Application.Calculation = xlCalculationAutomatic

End Sub

Remove Dotted Line Formula

Sub RemoveDottedLineFormula()
'PURPOSE: Remove dynamic dotted line formula from contact list names

Dim cell As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For Each cell In Selection.Cells

  If cell.HasFormula = True Then
    mySplit = Split(cell.Formula, "& REPT")
    cell.Value = Right(mySplit(0), Len(mySplit(0)) - 2)
    cell.Value = Left(cell.Value, Len(cell.Value) - 1)
    cell.HorizontalAlignment = xlLeft
  End If

Next cell

Application.Calculation = xlCalculationAutomatic

End Sub

Get The Sample File

If you would like to see how I set up my personal contact list Excel file or just want a head start on your project, you can download the sample workbook I showed throughout this article.

As always, in order to download this example file you will need to be a subscriber of my free newsletter. If you click the green button below you can easily sign up and you will be emailed the password to get into the subscribers-only area of this website.

                 Already Subscribed? Click HERE to navigate to the "Example Files" section

                 Already Subscribed? Click HERE to navigate to the "Example Files" section


Share This Post!

Did you find this post helpful? Do you want to support this blog because you're just that awesome?!  By sharing this post on FacebookTwitter, or Google+ you are not only providing others with great information, you are creating exposure for The Spreadsheet Guru website.  Growing this community is my number one goal as I have found learning to increase exponentially when lots of people are contributing to the conversation.  Learning is the whole reason why this blog exists!  If you want to spread the word just click on the Share button right below this paragraph (next to the Like button -- I enjoy "likes" too!) or on the Floating Share Bar to the left and select your preferred social medium.  Thank you so much for reading and I hope I can continue to provide you with great content in the future!  Cheers!