Printable Excel Contact List With Dynamic Dotted Underlines
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:
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:
*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:
- Highlight all cells with your contact name
- Right-click and select Format Cells…
- Navigate to the Alignment tab in the Format Cells dialog box
- Open the Horizontal Text Alignment dropdown box
- Select Fill
- 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
'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 SubRemove 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 SubGet 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.
Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!
Keep Learning
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.