Excel Tip: Printing Dot Leaders Before Numbers | AccountingWEB

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!

    Wait, there's more!
    There's always more at AccountingWEB. We're an active community of financial professionals and journalists who strive to bring you valuable content every day. If you'd like, let us know your interests and we'll send you a few articles every week either in taxation, practice excellence, or just our most popular stories from that week. It's free to sign up and to be a part of our community.
    Premium content is currently locked

    Editor's Choice

    WHAT KIND OF FIRM ARE YOU?
    As part of our continued effort to provide valuable resources and insight to our subscribers, we're conducting this brief survey to learn more about your personal experiences in the accounting profession. We will be giving away five $50 Amazon gift cards, and a $250 Amazon gift card to one lucky participant.
    This is strictly for internal use and data will not be sold
    or shared with any third parties.