Adding a Very Heavy Cell Border

by Allen Wyatt
(last updated April 3, 2021)

9

Steve can use the Border tab on the Format Cells dialog box to set a border around some cells. He would like to make a heavier border than what the Border tab allows, however. In the past he has inserted extra columns and/or rows and filled them with black to make a heavy border. In this case, however, Steve can't add extra rows/columns without messing up some calculations. He's wondering if there is a way to add a very heavy border of which he's not aware.

The Border tab of the Format Cells dialog box offers three thicknesses for a solid border. If the thickest border on the tab is not thick enough for your needs, then the simplest option would be to add a rectangle shape to your worksheet and position it over the cell, as desired. You can add the shape by displaying the Insert tab of the ribbon and then, from the Shapes tool, choose the rectangle shape. You can then click and drag to "draw" the rectangle in the worksheet.

A handy way to get the rectangle the size you want is to hold down the Alt key as you click and drag. This snaps the rectangle to the corners of the cell (or range of cells) over which you drag. Once placed, you can click on the rectangle and format it. You'll want to make sure that the fill color is set to "None" and that the outline width is set as heavy as you desire.

Finally, it is a good idea to set the properties of the shape so that the "Move and Size with Cells" option is selected. This allows your hand-crafted border to adjust to most changes that may affect the location and size of the cell to which you've attached it.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13843) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Sorting by Fill Color

Sorting data in a worksheet is easy, unless you want to sort by the color used to fill a range of cells. There are ways ...

Discover More

Assigning a Shortcut Key to Styles

Shortcut keys are a great way to apply styles to text in a document. You can easily create a shortcut key assignment for ...

Discover More

Leaving Minimized Documents Minimized

If you have open documents minimized on the Taskbar and then open another document, all of the previously minimized ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (ribbon)

Sending Drawing Objects to the Back or Front

Not only can you place drawing objects in your worksheets, but you can organize those objects so some are in front and ...

Discover More

Quickly Duplicating Drawing Objects

Excel provides a couple of different ways that you can quickly duplicate drawing objects in a worksheet. Committing these ...

Discover More

Flipping a Drawing Object

Don't like the way a drawing object looks? Perhaps flipping the object could help appearances. Excel allows you to flip ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 9 + 6?

2021-04-12 06:58:15

Mike D.

@Tomek
F5, I always forget about that one, thank you for reminding me.
Most often I am not working with large worksheets so what I need is usually within view.
The CTRL key is my biggest friend in Excel.


2021-04-09 03:12:46

Roy

Some others as well.

1) If you make a Named Range for the cell, it will show up in the box ot the left of the Formula Editor and can be selected, whereupon you are in the cell and ready to roll.

2) Speaking of that box, it is like a mini-GoTo. Just click in it, type a cell reference and press Enter. Boom, you are in the cell, ready to roll.

3) Just pick an empty cell and enter in it the formula "=(Cell Address for the cell you want to edit)"... so, say it is F5, just enter "=F5"... Then double click the cell you entered it in and boom, you are in the cell you want to be in. This one, by the way, has some pretty far-reaching capabilities. It will open files for you if you enter an address in a closed file. (Or, more likely, already have it entered!) And in a cell with a more complicated formula, double clicking selects all the cells addressed in it with the first one addressed being the "starting cell" of the selected (likley) discontiguous range.

I suggest using a Named Range for selecting such ranges for, say, editing on a screen form (i.e.: NOT a "real" form, just material laid out on the screen, probably arranged like, perhaps, an invoice, in which a few, or mor ethan a few, cells need to be navigated to in order to allow the user to edit only the desired cells. But without any heavy lifting by the author (you)). However, one could simply place a cell with a simple formula, perhaps adding all the cells, listing them in the order one desires them navigated, or better, concatenated in either of the old styles that required each cell to be listed, though if a range in the order Excel will takes its address is acceptable, then using CONCAT() could be good too, and perhaps formatted number-wise with:

;;;"Click to select data cells"

(Has to use a formula resulting in text. If numbers could result, the cell will be blank instead.)

To be more "inclusive", enter the text string for all four parameters in a number format. (Interestingly, if you do, and you used addition, not concatenation, when you apply the format, if the cell width is still the default width, Excel will expand its width to fit your text. It does not do that when it is the string shown above. Must be triggered by the return of a number, much like entering a long number in a fresh column. If the addition returns an error because one of the cells had text in it, there is no column widening.)

Speaking of errors returned by that formula you use, one could wrap it in an IFERROR() returning "" so it returns text instead and your helpful label remains.

4) Select a cell, same row, left or right of it and use the appropriate arrow key to Ctrl-Arrow over to it. Same idea, but use the same column, select a cell above or below. Of course, it would only work if that would work if the graphic were not there. Various other navigation key combos (Mr. Wyatt has addressed a few oddballls) ought to work similarly, if they too would work without the graphic in place.

5) VBA. Either a dedicated macro, fearsome thought that, talk about "If you only have a pile driver, everything looks like a nail." But easy enough to do. Though one hopes you only need one, 'cause one would like a shortcut key combo for something like this and having 20 border graphics to get under might stress one's imagination and recollection in building and using them.

6) VBA but not a macro: Use the "Immediate Window"... it is remarkably easy and you don't have a .XLSM file. Not easier than Goto!!! Just listing it for completeness and to "raise everyone's conciousness" concerning all the awesome things one can do via the Immediate Window.

7) Highlight a region just a bit larger than the border graphic but that includes the cell it is laid upon. Make sure that cell is the "lower right corner" (or the "upper left corner"). Then use Ctrl-End (or Ctrl-Home, if "upper left corner") and you're in the cell.

Personally, I'll say I think the box to the left of the Formula Editor seems easiest if your hand is on the mouse as one simply clicks, types the address, and presses Enter. If your hand is off the mouse, "F5-type the address-press Enter" is the easiest. If you don't know or want to know the address one of the Arrow Key approaches seems easiest.

Naturally, that'd be modified by your version of Excel not supporting one or more of the thoughts folks have presented. But F5 (GoTo) and that box I mention are in all versions I can imagine, though I've not personally seen the online version which is said to lack many things (though to lack a simple to access GoTo seems just impossibly unlikely, even if it does lack the box I like). So, they ought to be reliable "go to" choices. (I could resist the sort of punning there.

The above said, and the prior comments said, who wants to bet someone knows or can figure out more ways?


2021-04-08 09:32:27

Tomek

@Alec, @Mike, @ Roy: There is another way to get to a cell under a shape, if you can't click on it - use F5 (goto) and specify the cell's addres ;-)


2021-04-06 06:43:20

Mike D.

@Alec, @Roy: Roy's advice works perfectly and is the only way I have found that works if you use a text box to cover a cell.
However; if you use a shape (rectangle, circle) you can set the shape properties to 'No Fill' , this will allow you to click through the shape into the cell. It is like the shape is not there. If you need to format the shape you will need to click on the border to select the shape.
You can also add text to the shape and it will lay on top of anything in the cell.
This is how my Excel works, hope it helps you.


2021-04-05 17:24:32

Alec Whatmough

@Mike D. Interesting - what you and Roy suggest is exactly what I have done and submitted same as an answer last week. And it works fine on the PC I am using right now, yet on another machine, I find I cannot click into a cell covered by a shape, fill or not. Maybe a system fault on the other PC??
Anyway, it is pretty clear that big, bright borders can be easily achieved, so Steve should be a happy camper. :)


2021-04-05 07:28:01

Mike D.

@Alec, @Roy: Roy's advice works perfectly and is the only way I have found that works if you use a text box to cover a cell.
However; if you use a shape (rectangle, circle) you can set the shape properties to 'No Fill' , this will allow you to click through the shape into the cell. It is like the shape is not there. If you need to format the shape you will need to click on the border to select the shape.
You can also add text to the shape and it will lay on top of anything in the cell.
This is how my Excel works, hope it helps you.


2021-04-05 03:39:40

Roy

@Alec: It covers the cell, and makes the mouse no good for selecting the cell, but select a cell outside the border (solid color or gradient) and arrow into the cell. You slip right under the shape and can edit the cell as you please.


2021-04-03 15:14:42

Alec Whatmough

@Tomek - you learn something new every day! Thanks for that. I’ve always used shapes, but of course that blocks editing the cell.


2021-04-03 11:05:18

Tomek

You can also create colourful borders by formatting the border as gradient line.

I am attaching a screenshot of two examples below (see Figure 1 below) :

Figure 1. 


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.