Wondering how to change the border color in Excel?
Besides data analysis, Microsoft Excel is also widely used as a framework for dataset organization. When organizing your data cells in Excel, cell borders play a pivotal role by enhancing the visual appeal of your Excel tables, reports, dashboards, charts, and so on.
Borders help you give your data a structured layout. Without borders, the audience will face challenges when trying to figure out the structure of the data you’re presenting.
The default border color of all Excel cells is black, which is also the default color of the gridlines in the worksheet. If you don’t know how to change the border color of tables or ordinary Excel cells, this article will help. Find below a step-by-step guidance to change border colors in 9 different ways.
Why Would You Want to Change the Border Color?
before you learn how to change the border color in Excel, you must know why cell borders also matter in data analytics and visualization. Find below the major reasons to learn this Excel skill:
- By changing the border color, you can emphasize specific cells or ranges of cells, drawing attention to important dataset clusters or tables.
- You might use different colors for revenue-related cells, expense-related cells, or other categories to make the data more visually organized.
- Clear and distinct borders make it easier for users to understand the structure and relationships between different sections of your data. You can achieve this by changing the cell border colors.
- You can use border color changes to highlight cells with errors or exceptions.
- Changing border colors is a useful formatting technique when preparing reports or presentations.
- By using distinctive border colors, you can flag important information, deadlines, or specific instructions within your Excel file.
- Differentiating sections of your spreadsheet with varied border colors aids in navigation.
- If you’re working on a larger project or multiple interconnected sheets, consistent use of border colors helps maintain a unified design, making it easier for users to understand and work with your Excel files.
Change Border Color in Excel Using the Format Painter Tool
The Format Painter tool in Excel is useful when you want to clone cell formatting and style from one cell to another. So, you can use this method to alter the border color of cells by copying the border color from another cell or cell range.
Highlight the cell range from which you want to transfer the border color to another cell range. be mindful that other cell formatting elements will also be transferred to the destination cell range.
Click on the Format Painter button inside the Clipboard block on the Home tab of Excel. You’ve copied the cell formatting.
Now, highlight the destination cell range to change the border color of the cells in the selected range.
Change Border Color in Excel Using Line Color
You can trigger a border color modification pencil using the Line Color tool from Excel > Home > Font block. This pen helps you to apply the new border color on the automatic border, which is black.
On your Excel worksheet, click on the All Borders drop-down arrow. Now, hover the cursor over the Line Color menu to access the border color menu.
Choose any color from the sections like Theme Colors, Standard Colors, Recent Colors, and More Colors.
Your mouse cursor will change into a pen. Now, click on the cell borders to change the existing color to the new one.
To disable the Line Color pen, press the Esc key on your keyboard.
Change Border Color in Excel Using Format Cells Dialog
The Format Cells tool in Excel is a versatile feature that allows you to customize the appearance of cells, including their border thickness, style, and color. This tool can be accessed by right-clicking on a cell or a group of selected cells and choosing Format Cells from the context menu.
Below are the steps to change the border color using the Format Cells dialog in Excel:
- Begin by selecting the cells or range of cells for which you want to change the border color.
- You can now use the keyboard shortcut Ctrl + 1 to open the Format Cells dialog.
- In the Format Cells dialog box, navigate to the Border tab. Here, you will find options to customize the border of the selected cells.
- Within the Border tab, locate the Color dropdown menu, and click on it to reveal a palette of colors. Choose the desired color for the cell border.
- Below the Border section on Format Cells, you should see a preview of the cell borders you’re modifying. Click on the Outline and Inside buttons to apply the new color to all borders of the selected cell range.
- If you don’t want to apply the new border color to all borders, use other buttons to the left and bottom of the preview.
- Click OK to apply the changes you’ve made.
Change Border Color in Excel Using Gridline Color
If you don’t like the default cell borders – the gridlines, do the following to set up a color of your choice for a specific worksheet:
- Open the Excel Options dialog from File > Options on the Excel software.
- Click on the Advanced category on the left-side navigation panel.
- Scroll down the scroll bar on the right-side menu to find the Display options for this worksheet section.
- Click on the Gridline color drop-down arrow and choose a color palette of your choice.
- Click OK to save the changes you’ve just made.
- Excel will replace the default gridline color with the new one you’ve selected.
Change Border Color Using Conditional Formatting
You might already know that Excel Conditional Formatting allows you to automatically format cells based on specific conditions or criteria. This formatting can also include cell border colors.
For example, you can create Conditional Formatting rules to change cell border colors based on certain values or formulas.
Using a Custom Rule
- Highlight the cells for which you must change the border colors based on a formula.
- Click on the Conditional Formatting drop-down on the Home tab.
- Hover the cursor over Highlight Cells Rules.
- Click on the More Rules option on the overflow menu.
- The New Formatting Rule dialog will show up.
- Choose a rule from the Select a Rule Type dialog.
- Now, go to the Edit the Rule Description section and enter the formatting logic.
- Now, click the Format button above the Cancel button on the New Formatting Rule dialog.
- The Format Cells dialog will pop up.
- Go to the Border tab and choose a border color and preset.
- Click OK on Format Cells and New Formatting Rule dialogs.
- Excel will automatically change the cell border based on your Conditional Formatting logic and border color.
Using Data Bars
If the cells for which you want to change the border color contain numerical values, you can also use Conditional Formatting > Data Bars to create a contrast-based cell border.
Excel adds colored bars inside the cells depending on their values. For a value of 100, the bar will cover the whole cell while the value of 10 will cover 10% of the cell volume.
To apply Data Bars, highlight the target cell range and click on the Data Bars option inside the Conditional Formatting menu.
Change Border Color in Excel Using a Custom Cell Style
Do you often need to apply a specific set of cell borders to various cell ranges? You can create a custom cell style in Excel.
After importing datasets, you either analyze or transform, and then click on the custom cell style to instantly apply the custom cell border instead of the default black border.
This is a one-click solution to change border color in Excel of course after you’ve set up the custom cell style. Here are the steps you can follow to set your custom cell style:
- Go to your worksheet and click on the Cell Styles expansion arrow inside the Styles block on the Home tab.
- Click on the New Cell Style button inside the context menu that opens.
- Uncheck all the cell styles on the Style dialog except the Border: No Borders checkbox.
- Inside the Style name field, give the cell style a custom name you can remember.
- Click the Format button.
- Now, select a custom border color and preset.
- Click OK on Format Cells and Style to create the custom cell style.
Whenever you need to change the border color of one or many cells, highlight those. Then, click on the style in Cell Styles > Custom > Border 2 (or whatever name you used).
Excel shall change the cell border colors without impacting any other cell style properties like font, fill, etc.
Change Border Color of Multiple Cells in Excel
Use the Go To dialog to select multiple non-adjacent cells. You can press Ctrl + G to call the Go To dialog. You can enter the cell range references in the following ways:
- For adjacent cell range:
A1:C6
- For non-adjacent cells:
A2
,B3
,C4
,A5
Once you’ve selected multiple cells, press Ctrl + 1 to open the Format Cells dialog.
There, go to the Border tab and choose a new color of your choice. Don’t forget to choose a preset like Outline or Inside and hit the OK button.
Excel shall automatically change the border of multiple selected cells in a flash.
Change Border Color of a Chart in Excel
There are various border elements on Excel charts that you can modify. For example, Chart Area border, Axis Options border, Series Options border, Title Options border, etc.
Insert a chart of your choice in your Excel worksheet and click on a blank space on the chart to highlight it. Click the Format tab on the Excel ribbon and click on the Format Selection command inside the Current Selection block to bring up the Format Chart Area navigation pane.
On the Format Chart Area panel, click on the Fill & Line button if not selected already. Now, extend the Border menu and choose a more visible border type, like a Solid line.
You can now click on the Color drop-down menu and choose a shade from the context menu. Don’t forget to increase the value of the Width parameter to make the custom chart area border more visible.
You can follow the same steps to change the border color of Series Options, Axis Options, and Title Options borders.
Change Border Color in Excel Using Excel VBA
If you don’t want to change cell border colors in Excel manually, you can use a VBA macro. Find below a script you’re free to use:
Sub ChangeBorderColor()
Dim targetRange As Range
Dim borderType As XlBordersIndex
Dim colorIndex As Integer
Dim colorOptions As Variant
Dim selectedColor As Variant
' Input box for cell range
On Error Resume Next
Set targetRange = Application.InputBox("Enter the cell range:", Type:=8)
On Error GoTo 0
' Check if user pressed Cancel
If targetRange Is Nothing Then
MsgBox "Operation canceled by user.", vbInformation
Exit Sub
End If
' Input box for border type
borderType = Application.InputBox("Select border type:" & vbCrLf & _
"1 - All Borders, 2 - Outside Borders", Type:=1)
' Check if user pressed Cancel
If borderType = 0 Then
MsgBox "Operation canceled by user.", vbInformation
Exit Sub
End If
' Color options
colorOptions = Array(RGB(255, 0, 0), RGB(0, 255, 0), RGB(0, 0, 255), RGB(165, 42, 42), RGB(0, 128, 0), _
RGB(128, 0, 128), RGB(255, 165, 0), RGB(0, 0, 0), RGB(255, 255, 255), RGB(128, 128, 128))
' Input box for color selection
colorIndex = Application.InputBox("Select a color:" & vbCrLf & _
"1 - Red, 2 - Green, 3 - Blue, 4 - Brown, 5 - Dark Green, " & _
"6 - Purple, 7 - Orange, 8 - Black, 9 - White, 10 - Gray", Type:=1)
' Check if user pressed Cancel
If colorIndex = 0 Then
MsgBox "Operation canceled by user.", vbInformation
Exit Sub
End If
' Get the selected color
selectedColor = colorOptions(colorIndex - 1)
' Change border color
Select Case borderType
Case 1 ' All Borders
With targetRange.Borders
.LineStyle = xlContinuous
.Color = selectedColor
End With
Case 2 ' Outside Borders
With targetRange.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Color = selectedColor
End With
With targetRange.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Color = selectedColor
End With
With targetRange.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Color = selectedColor
End With
With targetRange.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Color = selectedColor
End With
End Select
MsgBox "Border color changed successfully!", vbInformation
End Sub
Here are the actions of this VBA script:
- An input box asking for cell reference or range for border color changing
- A dialog box asking you to choose between all borders and outside borders
- An input box for shade selection
Creating a macro using the above script is pretty straightforward if you follow the instructions outlined in this article How To Use The VBA Code You Find Online.
Conclusions
Changing Excel cell border colors serves as a functional enhancement, allowing users to emphasize, categorize, and highlight data effectively.
By thoughtfully employing the methods explained above, you can create more engaging and professional-looking Excel worksheets.
If the article helped or you have feedback for this Excel tutorial, you can write your comments below.
0 Comments