How to Get Rid of Dotted Lines After Copying in Excel [5 Ways]

Don’t know how to get rid of dotted lines in Excel after copying? Try the techniques mentioned here and give your Excel spreadsheet a professional look.

Dotted lines are a formatting feature in Excel that can be used to surround the border of a cell. Users often use this to isolate and organize their Excel spreadsheet data. 

These lines can also appear in Excel if you copy some data from other sources and are commonly known as the marching ants border. The problem with these lines is the unintentional appearance of these lines can confuse the users, especially if they don’t know how to get rid of these lines.

Read this article till the end to know how to get rid of dotted lines in Excel after copying from other sources. Here, I’ll include multiple methods so that you can try the method you’re most comfortable with.

Reasons for Getting Dotted Lines in Excel

Before removing the dotted lines from your Excel workbook, you might want to know why these lines appear on the spreadsheet in the first place.

• You copied a cell range and pasted the data somewhere else within the same or different workbook. This action will create a dotted line around the copied cell range.
• One reason is copying data from a source that has the dotted line formatting.
• Another reason behind dotted lines in Excel is conditional formatting, which allows users to apply particular formatting on cells that meet certain preset criteria. If you have applied the conditional formatting of dotted lines, it’ll appear when the criteria are met.
• If you save a spreadsheet in formats like CSV that doesn’t support dotted lines, opening that CSV file in Excel might make the dotted lines appear. 
• Sometimes, Excel automatically assigns dotted lines to indicate a page break for printing or other purposes.

Why Do You Want to Get Rid of the Dotted Lines in Excel After Copying

• One of the negative effects of dotted lines is they often interfere with Excel data readability. While you may not have any issues with these lines, others might find it challenging to understand and analyze during a presentation.
• Dotted lines make your Excel data look unprofessional and cluttered, leading to undermining its credibility.
• Excel dotted lines can also cause confusion while you try to print the spreadsheet. If you have included it intentionally to understand the page breaks, partial printing of dotted lines or not getting printed at all will lead to misunderstanding.

Let’s explore below a few methods to get rid of dotted lines in Excel after copying. You’ll also learn how to remove dotted lines in Excel that are not linked to a copy-paste action.

Get Rid of Dotted Lines in Excel Using Simple Actions

The moving dotted lines or marching ants border in Excel around the copied cell or cell range is an intentional feature from Microsoft.

It gives you a sense that you have selected a dataset, and it’s currently in the clipboard. So, unless you deselect the cell range and copy another dataset, Excel will keep copying the previously selected cell range.

However, there are a few straightforward tricks to get rid of this annoying dotted line with moving animation. Here’s how.

1. Select the dataset you want to copy to another location on the worksheet or workbook or to a different workbook.
2. Press the Ctrl + C keys together to give the copy command to Excel.
3. You should see the animated dotted lines around the selected cells.
4. Go to the destination and press Ctrl + V to paste the dataset.
5. The dotted lines around the source dataset won’t go away yet.

6. Now, press the Esc key on the keyboard to get rid of the dotted lines.

Alternatively, you could just save the file to remove the dotted lines.

Click the Save button above the Excel ribbon in the top-left corner. Or, you can hit the Ctrl + S keys together to give the save command. If you didn’t save this Excel file before, you’ll see the Save this file dialog box.

Other actions that you can make to remove the dotted lines in Excel after copying are as below:

• After copying the dataset, hit Enter to clear the source data selection.
• Copy data by pressing Ctrl + C on the source cell range and pasting by simply pressing Enter on the destination cell.
• After copying and pasting, double-click any blank cell on the worksheet.
• Start editing a cell or typing values in a blank cell after pasting the copied data.

Clear Marching Ants Border in Excel Using Clear All Command

This is another effortless method to get rid of dotted lines in Excel after copying a cell or cell range. Here are the steps you must follow:

1. Copy the source data and paste it into the destination cell, worksheet, or workbook.
2. Now, go back to the source dataset worksheet, highlight any blank cell, and click the Home tab.
3. There find the Editing commands block.
4. Inside the Editing block, you should see the Clear drop-down menu.
5. Click this button and choose Clear All from the context menu.
6. The marching ants border will go away.

Remove Dotted Lines in Excel Using Excel VBA Code

When the dotted lines after copying troubles you, get rid of them completely using this neat Excev VBA code.

It will copy data from a given cell range to another cell without leaving any marching ants border in the source dataset. Find below the code along with the steps to use it:

1. Call the Excel VBA Editor by pressing Alt + F11 keys together.
2. Now, select Insert on the toolbar and click Module.
3. Into the new module, copy and paste the following VBA script:

Sub removedottedlines()

'copy the data
Range("A1:A5").Copy

'paste the data
Range("D1:D5").PasteSpecial

'remove the dashed line
Application.CutCopyMode = False

'select a single cell (deselects the copied range)
Range("K1").Select

End Sub

4. Click the Save button and close the VBA Editor.

5. Now, call the Macro dialog box by pressing Alt + F8 altogether.
6. Click the removedottedlines macro and hit the Run button to execute.
7. Excel will copy the data to the destination and move the cell selection to K1.

In the above code, you must modify the following code elements to let the VBA script work for your own dataset:

• Range("A1:A5"): the cell range for the source dataset
• Range("D1:D5"): the cell range for the destination
• Range("K1"): move the cell selection away from the copied dataset

How to Remove Dotted Lines in Excel Using Office Scripts

If you got Microsoft 365 Business Standard or above subscription for Microsoft 365 apps, you can use the Office Scripts coding method to get rid of dotted lines in Excel. Here’s how it’s done:

1. Click the Automate tab on the Excel desktop or web app.
2. Select the New Script command.
3. On the Code Editor that opens on the right side, copy and paste the following Office Scripts code:

function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();
	// Paste to range F1 on selectedSheet from range A1:B6 on selectedSheet
	selectedSheet.getRange("F1").copyFrom(selectedSheet.getRange("A1:B6"), ExcelScript.RangeCopyType.all, false, false);
}

4. Click the Save script button.
5. Now, hit the Run button to execute the code.

Here’s how you should modify the above code to make it work for you:

• Change getRange("F1") to the first cell address of the destination like getRange("G1") if copying data to D1.
• Change getRange("A1:B6") to a different cell range like getRange(“C1:D6”) if copying values from the cell range C1:D6.

How to Get Rid of Other Common Dotted Lines in Excel

Apart from copying, there are other causes of dotted lines in Excel. Find below how to get rid of those:

Remove Dotted Lines Due to Page Break

1. On your Excel worksheet, click the File tab.
2. Click the Options button on the sidebar that opens.
3. You should now see the Excel Options dialog box.
4. There, select the Advanced category on the left side.
5. Scroll down the right side menu until you find Display options for this worksheet.
6. Uncheck the checkmark for Show page breaks.
7. Click OK to save the changes.

Conclusion

While the intentional use of dotted lines can add variety to the formatting of your spreadsheet, unintended or involuntary dotted lines could be frustrating.

Whenever in this situation, all you can do is get rid of dotted lines after copying the data.

Check out the methods of how to get rid of dotted lines in Excel after copying mentioned above, and tell us which one you like most. If interested, you can also read this article on reversing data order in Excel.