VideoPhoto

28 September, 2021

TIPS for CLICKS: Cell Borders with Shapes

Borders around Excel cells make worksheet contents visually enhanced and easier for evaluation. They clarify the format and layout of the data, make it appear more orderly and also highlight important information. Drop-down menu of Borders provides multiple pre-built options for that, as you can see some of them here:

However, some Excel users sometimes would like to apply different type of borders (e.g. rounded-corner borders) instead of the square-cornered ones. There is no such an option in Excel. Fortunately, there is a workaround available for such unconventional type of 'borders'.

So, if you see any good reason for using a different kind of cell 'borders' (e.g. for presentations, reports) , you can create them quite easily with the help of many different Shapes provided by Excel. Here are some of my creations:

Shape with rounded corners

Octagon Shape

Beveled Shape

Oval Shape

Frame Shape

What does it take to place such cell 'borders' in your worksheet?

It is possible to create a Shape/drawing object the same size as your cells and format it so it has no fill colour. Then, you can copy the 'border' to as many cells as you need to.

Here's how to format your selected 'border':

  1. Click on Insert tab > Shapes and select the shape you want from Rectangles, Basic Shapes, etc., e.g. Plaque.
  2. Go with cursor (showing '+' sign) to a cell of your choice, press and hold the ALT key and resize the shape so it covers exactly the cell size.
  3. Right-click on the shape and select 'Format Shape...' from drop-down menu.
  4. Click on 'Size & Properties' in 'Shape Options', then on 'Properties' and make sure that 'Move and size with cells' option is selected.
  5. Click on 'Fill & Line' in 'Shape Options', then on 'Fill' and select 'No fill'. You can also select there 'Line' properties (e.g. colour and width), if you want.

Your cell 'border' is ready.

Now you can select and copy the cell with your 'border'. Be careful, select the whole cell, not the shape only, to copy it (you may need to use arrow keys to select the whole cell). Use CTRL+C and CTRL+V to paste your cell to other cells in your worksheet.

IMPORTANT: You'll notice at some point that the 'border' is floating (can be freely moved around) in a layer above the cell layer, so leave it alone. If you want to fill the underlying cell with a specific colour or pattern, you can do this separately by selecting the cell itself, not the 'border'. Remember also that you can't add fill colour to your shape. Only the shape's line can be formatted (width, colour) if needed.

To enter any value or formula to your 'bordered' cell, click on the cell and enter what you want. Your typing should appear in the Formula bar. If it is not there, then it means that you are entering data into the 'border' layer instead into the cell layer. You couldn't use such entries in any calculations.

The entry into the cell layer can be referred to by other cells, and formatted (colour, font, alignment) to your liking, as usually.

That's about it.

 




No comments:

Post a Comment

All comments are held for moderation. I reserve the right to edit, censor, delete and - if necessary - block comments.