×

Printable Excel Contact List With Dynamic Dotted Underlines

By Chris Newman •  Updated: 01/15/15 •  6 min read
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 simply changing the cell format to the custom code:

General;General;General;General*.

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 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 dropdown box
  5. Select Fill
  6. Click OK
How To Add Fill Horizontal Text Alignment To Excel cells

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!

Dotted Line separator with Cell Fill formatting

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!

Use VBA to add and remove contact list dotted line separator

Add Dotted Line Formula

Sub AddDottedLineFormula()
'PURPOSE: Add dynamic dotted line formula to contact list names
'SOURCE: www.TheSpreadsheetGuru.com

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
'SOURCE: www.TheSpreadsheetGuru.com

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.

If you would like to get a copy of the Excel file I used throughout this article, feel free to directly download the spreadsheet by clicking the download button below.


Keep Learning

Chris Newman

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.

[FREE Training] 10 Amazing Excel Efficiency Tricks. Why Don't People Know These?!

X