Excel Tip: Printing Dot Leaders Before Numbers

Occasionally you may want to produce a column of text and a column of numbers with dots separating the text and the numbers. Here are two methods for producing dot leaders in Excel - one of them may work for you.

Method One:
This method produces dots that go from the left edge of the cell to one space before the number. The dots are in the same cell as the number. If you right-justify your column of text, then apply this number format to the cells containing numbers, it will look like your dots run from the edge of the text right up to the number, like this:

January ............ $1,584.25
February ............... $383.23
March ............ $2,837.33

To apply this method, follow these steps:

1. Right-justify your column of text by selecting the column, or the occupied cells in the column, and clicking the Align Right button on your Formatting toolbar (or choosing Format, Cells, Alignment, then choosing Right as the Horizontal alignment, then click OK).

2. Select the column of numbers or the cells containing the numbers to which you want to apply the dot leaders.

3. Choose Format, Cells from the menu, then click the Number tab.

4. Choose Custom from the Category list.

5. In the Type area of the Format Cells window, enter the following:

*._0.00

This code can be translated as follows:

  • The 0.00 is the number format, which is a standard two decimal place number format. Enter just a 0 if you don't want to display decimal places.
  • The underscore to the left of the zeros symbolizes a blank space, so that the dots won't run right into the numbers. You can enter more than one underscore of you would like to increase the number of spaces.
  • The asterisk followed by the period tells Excel to repeat the period from the left edge of the cell until it encounters the rest of the information (space and number) in the cell.
  • 6. Click OK when finished. The new number format will be applied to all selected cells.

    Method Two:

    If you would prefer, you enter the text left-justified in the first column, then provide dots from the text to the right side of the text column. The dots reside in the same cell as the text. The result will look like this:

    January............ $1,584.25
    February.......... $3,236.23
    March............... $2,837.33

    To apply this method, follow these steps:

    1. Enter your column of text.

    2. Select the column of text or the exact cells containing the text to which you want to apply the dots.

    3. Choose Format, Cells from the menu, then click the Number tab.

    4. Choose Custom from the Category list.

    5. In the Type area of the Format Cells window, enter the following:

    @ *.

    This code can be translated as follows:

  • The @ takes the place of text that you will enter. Following thi @ with a space will provide a separation between the text and the dots.
  • The asterisk followed by the period tells Excel to repeat the period from the right edge of the text until it encounters the right edge of the cell.
  • 6. Click OK when finished. The new number format will be applied to all selected cells.

    View more helpful tips!

    You may like these other stories...

    Earlier this week I presented the Chart Edition of AccountingWEB’s High Impact Excel webinar series. One of the many topics I covered was the Sparklines feature, which was first introduced in Excel 2010. Several...
    On January 30, I led a free, one-hour webinar, High Impact Excel: Pivot Table Edition. If you missed the presentation, it’s too late to get CPE credit, but you can watch an on-demand recording. After the webinar, I...
    By David Ringstrom, CPA In Part 1 of this series I showed how to use a custom number format to conditionally display decimal places. Although the technique is simple, the downside is it may not work in every situation....

    Upcoming CPE Webinars

    Apr 22
    Is everyone at your organization meeting your client service expectations? Let client service expert, Kristen Rampe, CPA help you establish a reputation of top-tier service in every facet of your firm during this one hour webinar.
    Apr 24
    In this session Excel expert David Ringstrom, CPA introduces you to a powerful but underutilized macro feature in Excel.
    Apr 25
    This material focuses on the principles of accounting for non-profit organizations' revenues. It will include discussions of revenue recognition for cash and non-cash contributions as well as other revenues commonly received by non-profit organizations.
    Apr 30
    During the second session of a four-part series on Individual Leadership, the focus will be on time management- a critical success factor for effective leadership. Each person has 24 hours of time to spend each day; the key is making wise investments and knowing what investments yield the greatest return.