10 Ways to Add Borders in Microsoft Excel

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.

Highlight target range
Highlight target range

To use this technique, highlight one or many cells around which you want to add the outside border style.

Add border in Excel using hotkey
Add a border in Excel using a hotkey

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.

Click Format Painter
Click Format Painter

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.

Applying Format Painter
Applying Format Painter

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!

Double click Format Painter
Double-click Format Painter

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.

Create Table dialog on Excel
Create Table dialog on Excel

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.

Table design and style expansion arrow
Table design and style expansion arrow

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.

Table style light bordered
Table style light bordered

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.

Borders context menu in Excel
Borders context menu in Excel

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.

All borders
All borders

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.

Applied all cell borders using Border tool
Applied all cell borders using the Border tool

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.

Enabling Border tab in Format Cells
Enabling the Border tab in Format Cells

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.

Select Line and Color
Select Line and Color

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.

Format Cells Presets
Format Cells Presets

For a basic border around the entire selection, click the Outline button in the Presets section.

Top borders
Top borders

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.

Manual border in Format Cells
Manual border in Format 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.

Draw Border in Excel
Draw Border in Excel

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.

Draw Border in Excel
Draw Border in Excel

Now, click the gridlines surrounding the cells to manually create a border structure.

To disable the border drawing pencil, press the Esc key.

Draw border grid context menu
Draw border grid context menu

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.

Draw Border Grid
Draw Border Grid

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

Styles expansion menu
Styles expansion menu

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.

Cell styles items
Cell styles items

To use this method, highlight your dataset and click on the Cell Styles drop-down menu on the Styles block.

Applying Output style in Excel
Applying Output style in Excel

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

New Cell Style option
New Cell Style option

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.

Style dialog
Style dialog

On the Style dialog, enter the name of the style into the Style name field. Also, uncheck all the checkboxes except the Border checkbox.

Format Cells for border template
Format Cells for border template

Now, click the Format button and set your cell borders inside the Border tab of the Format Cells dialog.

Applying border template
Applying border template

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

No gridlines
No gridlines

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.

Activating Gridlines
Activating Gridlines

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.

Conditional formatting
Conditional formatting

Highlight the target cell range to which you want to apply certain cell border styles and go to the Conditional Formatting menu.

More Rules of Conditional Formatting
More Rules of Conditional Formatting

From the Highlight Cells Rules, click on the More Rules button.

New Formatting Rule Format
New Formatting Rule Format

Set up a Conditional Formatting rule in the New Formatting Rule dialog.

Format cells Conditional Formatting
Format cells Conditional Formatting

Now, click the Format button to create a cell border style for the selected dataset inside the Format Cells > Border dialog.

Apply rule
Apply rule

Click OK twice on the Format Cells and New Formatting Rule dialog to apply the changes you’ve made.

Added border in Excel using Conditional Formatting
Added border in Excel using Conditional Formatting

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:

VBA script in Excel
VBA script in Excel
Sub ApplyCustomBorders()
    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)
    End Select
    
    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
    End Select
End Sub

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.

Input box cell range
Input box cell range

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.

Input box border style
Input box border style

Now, another input box asks you to select the cell border type you want to apply.

Input box line thickness
Input box line thickness

Then, you need to choose anyone between the thin and thick border lines.

Input box color choice
Input box color choice

Finally, you must choose a line color.

Add border using VBA
Add border using VBA

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

Conclusions

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.

About the Author

Tamal Das

Tamal Das

I'm a freelance writer at HowToExcel.org. After completing my MS in Science, I joined reputed IT consultancy companies to acquire hands-on knowledge of data analysis and data visualization techniques as a business analyst. Now, I'm a professional freelance content writer for everything Excel and its advanced support tools, like Power Pivot, Power Query, Office Scripts, and Excel VBA. I published many tutorials and how-to articles on Excel for sites like MakeUseOf, AddictiveTips, OnSheets, Technipages, and AppleToolBox. In weekends, I perform in-depth web search to learn the latest tricks and tips of Excel so I can write on these in the weekdays!

Related Posts

Comments

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃