Do you need to remove cell borders in your Excel sheet?
Though cell borders in Excel help you emphasize and highlight a tabulated dataset, sometimes you may feel your data is boxed in when you see endless thick and dark borders on your worksheet.
Read this super-quick and easy Excel tutorial to learn various clever methods to remove borders in Excel.
Remove Borders in Excel Using a Keyboard Shortcut
This is the coolest and quickest method you can use. It works when you select the whole cell range or at least two cells with borders.
Highlight the target cell range on your worksheet and press Ctrl + Shift + - to remove cell borders instantly.
This method only removes the internal cell borders if you select a few cells inside a table. However, you can remove all borders by highlighting the whole table.
Remove Borders in Excel Using the No Border Command
No Border is a border style of the Borders menu on the Excel Font commands block. You can access this feature from the Home tab.
Highlight the cell range for which you want to get rid of the cell borders. Now, click on the Borders drop-down menu on the Home tab and choose the No Border option.
By doing so, you can remove the borders from the selected cells. This feature removes all the borders irrespective of the location of the cells, whether a couple of cells within the table or the whole table.
No Border Command in Context Menu
You can also bring up the No Border command in Excel by right-clicking on the selected cell range.
A mini menu bar will pop open above the right-click context menu. There, you should find the Border drop-down to the left of the Font Color drop-down.
Remove Borders in Excel Using the Clear Formats Tool
Highlight the target cell or cell range and press the Alt + H + E + F keys one at a time to clear the borders. However, this method also removes all custom cell formatting. So, if you want to remove cell borders without changing the cell formatting, you can’t use this method.
Remove Borders in Excel Using the Format Cells Dialog
Open your Excel spreadsheet and locate the cells with borders that you want to remove.
Highlight the cell range using mouse or keyboard actions. Right-click on the selected cells if using a mouse and a context menu will appear. From the context menu, choose Format Cells.
Alternatively, you can press Ctrl + 1 on your keyboard to open the Format Cells dialog box directly if you’re not using a mouse.
In the Format Cells dialog box, the default selection is the last used tab or the Number tab. Navigate to the Border tab.
Now, you should see the Presets and Border menus. On Presets, there are three options for cell borders: None, Outline, and Inside.
On the Border menu, you’ll find six border styles. These are horizontal top, horizontal middle, horizontal bottom, vertical left, vertical middle, and vertical right borders.
There is a preview below the Presets menu so you can check the output cell border style after adding or removing cell borders.
To remove all borders of the highlighted cells, click the None option on the Presets menu.
Alternatively, to remove custom borders, click on left, right, top, bottom, etc. Click OK to apply the changes made.
Remove Borders in Excel Using the Normal Cell Styles
The Cell Styles menu enables you to quickly apply professional and stylish formatting to a single or range of cells in Excel. You can access the full list of different pre-set cell formatting by clicking the Cell Styles expansion arrow. For your assistance, I’ve marked it on the screenshot.
Highlight the target cell range and click on the Cell Styles expansion arrow. click on the Normal option below the Good, Bad and Neutral section.
This method also deletes all cell formatting including the cell borders.
Remove Borders in Excel Using a Custom Cell Style
You can also create a custom cell style to delete cell borders without affecting the cell formatting.
Expand the Cell Styles menu and click on the New Cell Style option.
On the Style dialog box, uncheck everything except the Border checkbox. Don’t forget to give the custom cell style a name you can remember.
Now, click on the Format button on the Style dialog to access the Format Cells > Border tab.
You can now choose which borders you want to remove or simply select the None option to delete all borders. The Preview panel will show how the cell will look after applying the formatting.
Click OK twice to complete the custom cell style creation process.
Now, highlight a cell range, go to Cell Styles > Custom, and choose the cell style you’ve just created to delete the cell borders in Excel.
Remove Borders in Excel Using Paste Special
You can copy any blank cell on the worksheet and paste its formatting onto a group of bordered cells to remove cell borders.
You’ll also be importing cell-style formatting from the blank cell. So, Excel will change the existing custom cell formatting to default. For example, the currency symbol shall disappear from the Sales column and percentage values will become decimal values like 2.30% will be 0.023.
After copying a blank cell, highlight the cell range from which you want to remove existing cell borders.
Press Ctrl + Alt + V to launch the Paste Special dialog.
On the Paste Special dialog, click the Formats option. Select OK to save and apply the changes.
Excel will remove all borders if you’ve selected the whole table.
However, if you choose a cell range within a bordered dataset, this method will only remove the inner borders leaving outside cell borders intact.
Remove Borders in Excel Using the Erase Border Tool
The Erase Border tool is suitable for fine adjustments of cell borders in Excel. Suppose, you don’t want to delete all the borders. Instead, you want to selectively remove the top, left, right, and bottom borders from a few cells to give the existing data organization a better look.
So, access your worksheet and click on the Borders drop-down on Home > Font commands block.
Choose the Erase Border option from the context menu that shows up.
Your Excel cursor will change to an eraser icon. Hover the icon over the target cell border and left-click once to delete the highlighted border.
Repeat these steps to delete a bunch of borders as you deem necessary.
When you no longer need the Erase Border tool, press the Esc key on the keyboard to get back the default Excel cursor.
How to Delete Conditional Formatting Borders
If you’re unable to remove cell borders in your worksheet by following any of the above methods and you know that the workbook isn’t locked for editing, don’t worry!
These cell borders are the results of Conditional Formatting rules. You must remove these Conditional Formatting rules to get rid of the borders.
To delete these cell borders, highlight the cells from which you need to remove borders.
Click on the Conditional Formatting drop-down and hover the cursor over the Clear Rules option.
Choose Clear Rules from Selected Cells option on the overflow menu.
Remove Borders in Excel Using Excel VBA
If you’re automating most of the repetitive tasks on Excel using VBA programming, you’ll like this method. Find below a script you can use in your VBA macro to delete all cell borders in your active worksheet in a single click.
Dim ws As Worksheet
Dim cell As Range
' Set the active worksheet
Set ws = ActiveSheet
' Loop through each cell in the worksheet
For Each cell In ws.UsedRange
' Clear the cell borders
cell.Borders.LineStyle = xlNone
Here’s how you can create a VBA macro using the above script.
- Bring up the Excel VBA Editor tool by pressing the Alt + F11 keys together.
- Click the Insert button and choose Module.
- Copy and paste the above script inside the module.
- Click the Save button to save your Excel workbook as a Macro-enabled workbook or in XLSM format.
- Close the Excel VBA Editor.
- Press Alt + F8 to bring up the Macro dialog.
- Choose the DeleteAllCellBorders macro from the list.
- Hit the Run button to execute the macro.
Here’s another VBA script that’ll show prompts so you can enter the cell ranges and border types to remove custom cell borders in Excel.
' Get multiple cell ranges from user input
Dim cellRanges As String
cellRanges = InputBox("Enter cell ranges separated by commas:", "Select Cell Ranges")
' Split the ranges into an array
Dim rangeArray() As String
rangeArray = Split(cellRanges, ",")
' Get border type selection from user input
Dim borderType As Integer
borderType = InputBox("Select border type to remove:" & vbNewLine & _
"1 - Top" & vbNewLine & _
"2 - Bottom" & vbNewLine & _
"3 - Left" & vbNewLine & _
"4 - Right", "Choose Border Type")
' Iterate through each range and remove the selected borders
For Each rangeString In rangeArray
Dim rangeToModify As Range
Set rangeToModify = ActiveSheet.Range(rangeString)
Select Case borderType
rangeToModify.Borders(xlEdgeTop).LineStyle = xlLineStyleNone
rangeToModify.Borders(xlEdgeBottom).LineStyle = xlLineStyleNone
rangeToModify.Borders(xlEdgeLeft).LineStyle = xlLineStyleNone
rangeToModify.Borders(xlEdgeRight).LineStyle = xlLineStyleNone
Once you run this macro, you get an input box where you can enter the cell ranges separated by a comma.
Then, you need to enter the types of borders you want to delete in numbers between 1 to 4.
Excel deletes the border you choose instantly.
You can remove cell borders effectively from your Excel worksheet by following any of the above methods.
The user interface-based methods like No Border, Clear Formats, Format Cells, Cell Styles, Erase Border tool, and Paste Special let you remove borders visually. However, you might be slowed down due to the manual actions you must make.
On the contrary, the Excel VBA method opens the door for script-based automation on your worksheet. The scripts in this article have been coded thoughtfully to help you remove cell borders from the whole worksheet or partially by selecting cell ranges.
Try these methods and tell us which one you liked the most. If you know a better way to get rid of Excel borders, don’t forget to mention that in your comment.