Creating Visual Breaks without using Cell Borders in MS Excel

Creating Visual Breaks without using Cell Borders in MS Excel

When setting up a worksheet where we need to separate one section of information from another, and present that separation clearly, we often use cell borders to accomplish the job.

Obviously that option is a great way to get the job done, but we tend to use those for everything… so making that visual break between data sections requires a bit of finesse with those borders.

What if we could create a visual break with a line created from a single character… let’s say an X or a ~ or maybe even a combination of X~? What about a word or phrase…

No, I’m not suggesting that you sit there and repeatedly hit a specific key or type a word over and over. If you’re going to use the line several times then you either do a lot of copy / paste work or end up with lines of different lengths… not necessarily the preferred situation.

Today I’m going to throw the use of the REPT formula out there for your consideration.

This formula allows you to state specific characters to be repeated and the number of times to repeat them without the hassle of actually entering each character separately.

Here’s how the formula is set up:

=REPT(“characters to repeat“, Number of times to repeat the characters)

For example, if I want a line of ~ marks that is 95 characters long, I would enter =REPT(“~”, 95) into the cell where the line should begin.

The result looks like this:

If you want a fancier line, try =REPT(“~**”, 60) to get this one:

Let’s look at the possibilities with actually marking it with text… such as confidential…

*** To get this line of characters into multiple cells at one time simply select all the cells where the REPT formula should be placed (use the Ctrl key to select non-adjacent cells), enter the formula then use Ctrl + Enter to put it into all selected cells at once.

Also, as I’m sure you noted when looking at the examples above, you can further change the appearance with the font type and other formatting tricks available to you; such as color, bold, size, etc…

Posted in: How to, MS Excel, Tips and Tricks