If you wish to improve the visibility, readability, and organization of your Excel worksheet datasets, you must learn how to add borders in Excel.
Navigating through dense data and intricate structures within Excel worksheets can often feel like deciphering a complex puzzle. However, there’s a simple yet effective solution to enhance clarity and organization: adding borders.
Borders serve as the delineating lines that not only separate sections but also accentuate vital data points like column headings or total rows. Beyond just functionality, they contribute to the aesthetic appeal of your worksheets, making them more visually appealing and professional.
In this guide, I’ll demonstrate various techniques for adding borders in Excel with steps and illustrations, so you can streamline your data presentation and optimize readability effortlessly.
Add Borders in Excel Using a Hotkey
The keyboard combination Ctrl + Shift + 7 adds an outside border to any cell or cell ranges in Excel.
To use this technique, highlight one or many cells around which you want to add the outside border style.
Now, press Ctrl + Shift + 7 once to add the border.
📒 Read More: Easy Ways To Change Border Color in Microsoft Excel
Copy Borders in Excel Using the Format Painter
If your Excel workbook already contains a border style in another worksheet or the same worksheet, you can save time by copying it. When copying, be mindful of the dataset structure. If the source and destination cell ranges don’t follow the same structure, copying the border style may degrade the readability instead of enhancing it.
For example, you can copy cell borders in Excel from 5 columns by 9 rows dataset to another dataset of the same structure easily by using the Format Painter tool. You can find this Excel command inside the Clipboard block on the Home tab.
Format Painter works within a workbook and between multiple workbooks.
To use it, go to the source dataset and highlight it. Now, click the Format Painter button once to copy the formatting. Along with the cell borders, Excel will also copy other cell styles like typefaces, number formatting, fill color, cell protection settings, column width, row height, and so on.
Now, go to the destination dataset and click on the first cell at the beginning of it. This action would copy the source cell formatting to the destination up to a range that exists in the source. For example, if the source dataset is
A1:E9, Excel will format the same number of cell ranges.
Suppose, there is more than one dataset in a worksheet where you’d like to copy cell borders from another worksheet using Format Painter. So, do you repeat the same steps? Of course not!
For multiple cell ranges, double-click on the Format Painter button. The button will now show an emboss effect on the backdrop.
Now, you can apply the cell formatting in new cell ranges as long as you want. Once done, press Esc on the keyboard to clear the Format Painter clipboard.
📒 Read More: Ways to Remove Borders in Microsoft Excel
Add Borders in Excel Using the Table Tool
If you want to make your dataset stand out with borders for all the cells, like an Excel table object, you can try the Table tool as well.
Highlight the target dataset and click Ctrl + T.
The Create Table dialog will pop up. Click OK to convert the basic dataset into a table.
Now click on any cell of the table and navigate to the Table Design tab on the Excel ribbon menu.
You should see the Table Styles block. Click on the expansion arrow in the lower left corner of Table Styles to open a drop-down menu with more table designs.
You must now find bordered tables below the Light section of Table Styles. Hover your mouse cursor over any style you like to see the preview on the dataset you’ve selected on the worksheet.
If you like the design click on it and make it effective throughout the dataset.
Add Borders in Excel Using the Borders Tool
Borders is the default tool controlling all the cell border formatting activities in Excel. It comes with 13 different border settings separated into three sections. You should easily locate the tool inside the Font block of the Excel Home tab.
To use this tool to add borders in Excel cells, highlight the target cell or cell ranges in your dataset.
Now, click on the Borders drop-down arrow and choose the type of borders you’re looking for.
In the present tutorial, I intend to apply all cell borders in my dataset. So, I’ve chosen All Borders from the Borders context menu.
As soon as you click on any of these border styles, Excel applies the same formatting on the highlighted dataset as shown above.
Add Borders in Excel Using the Format Cells Dialog
The Border tab in the Format Cells dialog offers extensive customizations to cell borders. Here, you can change the Line style, Color, Presets, and Border of the selected cell range. besides granular modifications, you also get a display preview of how the cells would look like.
So, to use this technique, highlight the target cell range and press Ctrl + 1 to bring up the Format Cells. There, navigate to the Border tab.
Under Line, select the desired line style and thickness from the menu box. An underline shall flash below the selected line style.
Under Color, choose your preferred border color for your dataset by clicking the relevant drop-down menu.
For a basic border around the entire selection, click the Outline button in the Presets section.
To add borders to specific sides or between cells, click the individual border buttons under Border. For example, clicking the Top button adds a border just to the top of the selected cells.
You can also create custom borders by clicking directly on the Preview box and drawing your desired border lines.
Click OK on the Format dialog to apply the changes you’ve just made.
Add Borders in Excel Using the Draw Border Tool
The Borders tool also contains the Draw Borders section where you find two tools to draw borders manually in Excel. The first one is the Draw Border which allows you to draw one line around a cell at a time. The second one is the Draw Border Grid which allows you to add borders in cells by batches with drag-and-drop actions.
To use this technique, simply go to Borders and expand its context menu. Now, click the Draw Border option to convert your mouse cursor to a pencil icon.
Now, click the gridlines surrounding the cells to manually create a border structure.
To disable the border drawing pencil, press the Esc key.
If you want to manually add all outer borders to cells in various segments of a large dataset, you can use the Draw Border Grid option.
From the Borders context menu, choose Draw Border Grid. Now, click on any cell and drag up, down, left, and right to apply cell borders quickly.
Add Borders in Excel Using the Styles Tool
You shall find the Styles block in the Home tab of the Excel ribbon menu. After expanding the context menu, you’ll find various bordered cell styles below the Data and Model section. Also, if you often need to apply a similar cell border pattern in your Excel worksheet, you can create a template by using the New Cell Style tool.
To use this method, highlight your dataset and click on the Cell Styles drop-down menu on the Styles block.
When the expanded Styles menu pops up, hover the cursor over the templates of the Data and Model, like Calculation, Check Cell, Input, Linked Cell, Note, etc., to add cell formatting with different border styles.
Custom Border Using New Cell Style
If you want to set a custom border style as a template, bring up the Style dialog box by clicking the New Cell Style option. Make sure you’ve highlighted the target cell ranges before going to Style.
On the Style dialog, enter the name of the style into the Style name field. Also, uncheck all the checkboxes except the Border checkbox.
Now, click the Format button and set your cell borders inside the Border tab of the Format Cells dialog.
The new style, Style 1, should show up in the Styles block of the Home tab. Click on it to apply border formatting to the selected dataset.
The custom border template would only show up on the workbook where you’ve created it in the first place.
Add Borders in Excel From the Show Block
Is your Excel worksheet missing the default borders of cells, like the picture shown above?
That’s because the Gridlines feature isn’t active on your worksheet. This is however not the cell borders explained so far.
To enable default cell borders or gridlines, go to the Show block of the View tab and checkmark the Gridlines checkbox.
Add Borders in Excel Using Conditional Formatting
If you’re applying a Conditional Formatting rule in a dataset, you can apply appropriate formatting to add cell borders in Excel.
Highlight the target cell range to which you want to apply certain cell border styles and go to the Conditional Formatting menu.
From the Highlight Cells Rules, click on the More Rules button.
Set up a Conditional Formatting rule in the New Formatting Rule dialog.
Now, click the Format button to create a cell border style for the selected dataset inside the Format Cells > Border dialog.
Click OK twice on the Format Cells and New Formatting Rule dialog to apply the changes you’ve made.
Excel shall automatically apply the selected cell border in the rows that meet Conditional Formatting rules.
Add Borders in Excel Using VBA
If you wish to automate the cell border formatting process in Excel, you can use the following VBA script:
Dim rng As Range, borderChoice As Integer, thicknessChoice As Integer, colorChoice As Integer
Dim borderColor As Long
On Error Resume Next
Set rng = Application.InputBox("Select a range:", Type:=8)
On Error GoTo 0
If rng Is Nothing Then Exit Sub
borderChoice = Application.InputBox("Choose border style:" & vbCrLf & _
"1 - All Borders" & vbCrLf & _
"2 - Outside Borders" & vbCrLf & _
"3 - Inside Borders" & vbCrLf & _
"4 - Left Border" & vbCrLf & _
"5 - Right Border" & vbCrLf & _
"6 - Top Border" & vbCrLf & _
"7 - Bottom Border" & vbCrLf & _
"8 - Diagonal Up" & vbCrLf & _
"9 - Diagonal Down", Type:=1)
thicknessChoice = Application.InputBox("Choose border thickness:" & vbCrLf & _
"1 - Thin" & vbCrLf & _
"2 - Thick", Type:=1)
colorChoice = Application.InputBox("Choose border color:" & vbCrLf & _
"1 - Green" & vbCrLf & _
"2 - Red" & vbCrLf & _
"3 - Blue" & vbCrLf & _
"4 - Black", Type:=1)
Select Case colorChoice
Case 1: borderColor = RGB(0, 176, 80) ' Green
Case 2: borderColor = RGB(255, 0, 0) ' Red
Case 3: borderColor = RGB(0, 0, 255) ' Blue
Case 4: borderColor = RGB(0, 0, 0) ' Black
Case Else: borderColor = RGB(0, 0, 0) ' Black (default)
Select Case borderChoice
Case 1: rng.BorderAround LineStyle:=xlContinuous, Weight:=thicknessChoice, Color:=borderColor
Case 2: With rng.Borders: .LineStyle = xlContinuous: .Weight = thicknessChoice: .Color = borderColor: End With
Case 3: With rng.Borders(xlInsideHorizontal): .LineStyle = xlContinuous: .Weight = thicknessChoice: .Color = borderColor: End With
With rng.Borders(xlInsideVertical): .LineStyle = xlContinuous: .Weight = thicknessChoice: .Color = borderColor: End With
Case 4 To 9: With rng.Borders(borderChoice - 3): .LineStyle = xlContinuous: .Weight = thicknessChoice: .Color = borderColor: End With
Case Else: MsgBox "Invalid choice. Exiting macro.", vbExclamation
The above script enables you to apply 9 different cell border styles along with line colors and thickness configurations.
The best thing is you can run the VBA macro on any dataset without modifying any part of the script. I’ve scripted it in a way so that the macro is dataset agonist.
Once you execute the script, an input bot shows up so you can select the target cell range on the worksheet using your mouse cursor.
Now, another input box asks you to select the cell border type you want to apply.
Then, you need to choose anyone between the thin and thick border lines.
Finally, you must choose a line color.
The above screenshot shows that the VBA script changed the cell border from red to black.
Now, if you don’t know how to use this script to create a VBA macro, there’s a quick article you can read below:
📒 Read More: How To Use The VBA Code You Find Online
Now you know all the best methods to add borders in Excel. You can try the methods based on user interface commands if you’ve just started using Excel and still learning its nuances.
Contrarily, if you’re an expert in Excel, I suggest you follow the VBA-based method to save time for things that matter in your business or career.