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....

    Already a member? log in here.

    Upcoming CPE Webinars

    Sep 24
    In this jam-packed presentation Excel expert David Ringstrom, CPA will give you a crash-course in creating spreadsheet-based dashboards. A dashboard condenses large amounts of data into a compact space, yet enables the end user to easily drill down into details when warranted.
    Sep 30
    This webcast will include discussions of important issues in SSARS No. 19 and the current status of proposed changes by the Accounting and Review Services Committee in these statements.
    Oct 21
    Kristen Rampe will share how to speak and write more effectively by understanding your own and your audience's communication style.
    Oct 23
    Amber Setter will show the value of leadership assessments as tools for individual and organizational leadership development initiatives.