How to Replace # Errors with Zero or Any Specific Value in Excel

This post will guide you how to replace #VALUE! Or #DIV/0! Errors with zero or any specific value in Excel. How do I replace all Formula Errors in your worksheet with Go to Special feature in Excel.

1. Replace # Formula Errors with Zero

Assuming that you have a list of data in range A1:B4, which contain some Errors, such as: #DIV/0!, #VALUE! Or other Errors. And you need to replace all Error message with zero or any other specific value in your selected range in Excel. How to do it. You can use the Go To Special feature to select all cells that contain Error value. Then you can type zero in formula bar, and press Ctrl + Enter keys to apply the same formula to replace errors with zero value. Just do the following steps:

Step1: select the range of cells that you need to replace formula errors.

replace formula error1

Step2: go to HOME tab, click Find & Select command under Editing group. And select Go To Special from the drop down menu list. And the Go To Special dialog will open.

replace formula error2

Step3: select Formulas radio button in the Select section, and only check Errors checkbox. And then click Ok button.

replace formula error3

Step4: all of the formula errors will be selected in the selected range of cells.

replace formula error4

Step5: type zero (0) value or any other specific value that you want to replace the errors in selected range. And then press Ctrl + Enter keys to replace all Errors with zero value.

replace formula error5

2. Video: Replace # Formula Errors with Zero

This video will demonstrate how to replace # errors with zero or any specific value in Excel using simple and easy-to-follow steps.

How to Delete All Comments in Excel

This post will guide you how to delete all comments in your current worksheet in Excel. How do I delete all comments from a worksheet with VBA code in Excel.

Delete All Comments with Delete Command


If you have a worksheet with lots of cell comments that you want to delete. And is there a quick way to delete all comments in your current worksheet without going from cell to cell and deleting them individually. You can use the Delete comments command to delete all comments in your selected range. Just do the following steps:

#1 If you want to delete all comments in current worksheet, press Ctrl + A to select all cells.

delete all comments1

#2 go to REVIEW tab, click Delete command under Comments group. And then all comments would be removed in your current worksheet.

delete all comments2

delete all comments3

Delete All Comments with Go To Special Feature


You can also use the Go To Special Feature to select all cells that contain comments. And then delete all comments. Here are the steps:

 

#1 go to HOME tab, click Find & Select command under Editing group. And select Go To Special from the drop down menu list. And the Go To Special dialog will open.

delete all comments4

#2 select Comments radio button in the Go To Special dialog. And click Ok button. Then all the cells that contain comments should be selected.

delete all comments5

#3 right click on it, and click Delete Comment from the popup menu list. And all the comments would be deleted in the selected cells in your worksheet.

delete all comments6

delete all comments7

delete all comments3

Delete All Comments with VBA


You can also use an Excel VBA macro to achieve the same result of deleting all comments in the current worksheet. Just do the following steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

convert column number to letter3

#4 paste the below VBA code  into the code window. Then clicking “Save” button.

delete all comments8

Sub DeleteAllCommentsFromWorksheet()
   Cells.ClearComments
End Sub

#5 back to the current worksheet, then run the above excel macro. Click Run button.

delete all comments9

delete all comments3

How to Unmerge Cells and Fill Down Values in Excel

This post will guide you how to unmerge cells and fill with duplicate values in Excel 2013/2016. How do I unmerge multiple cells and copy the content in each previously merged cell in Excel. How to unmerge cells and fill down duplicate values with VBA Macro in Excel.

Unmerge Cells and Fill with Duplicate Values


Assuming that you have a list of data in range A1:C4, and some cells are merged in your table. And you want to unmerge cells in this range and fill each cell with the original value. How to achieve it.  You need to unmerge cells in selected range firstly, then select all blank cells with Go To Special feature, next you need to use a formula to fill down value in each blank cell. Just do the following steps:

#1 select the range of cells which contain merged cells.

unmerge cells fill values1

#2 go to HOME tab, click Merge & Center command under Alignment group, and select Unmerge Cells from the drop down menu list.

unmerge cells fill values2

#3 all merged cells in the selected range have been unmerged. And keep to select the range.

unmerge cells fill values3

#4 go to HOME tab, click Find & Select command under Editing group, and select Go To Special menu from the drop down menu list. And the Go To Special dialog will open.

unmerge cells fill values4

#5 select Blanks radio button in the Select section, and click OK button.

unmerge cells fill values5

#6 all blank cells have been selected. And then enter =, then press Up arrow in your keyboard.

unmerge cells fill values6

#7 press Ctrl + Enter keys on your keyboard, and all blank cells have been filled with the original merged value.

unmerge cells fill values7

Unmerge Cells and Fill with Duplicate Values using VBA Macro


You can also use an Excel VBA Macro to unmerge multiple cells in a selected range and then fill with duplicate values in each cell. Just do the following steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

convert column number to letter3

#4 paste the below VBA code into the code window. Then clicking “Save” button.

unmerge cells fill values8

Sub UnmergeMulCells()
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select one Range which contain merged Cells", "UnmergeMulCells", myRange.Address, Type:=8)

    For Each myCell In myRange
        If myCell.MergeCells Then
            With myCell.MergeArea
                .UnMerge
                .Formula = myCell.Formula
            End With
        End If
    Next
End Sub

#5 back to the current worksheet, then run the above excel macro. Click Run button.

unmerge cells fill values9

#6 Select one Range which contain merged Cells.

unmerge cells fill values10

#7 Let’s see the last result:

unmerge cells fill values11

Video: Unmerge Cells and Fill with Duplicate Values

How to Delete All Visible Rows in Excel

This post will guide you how to delete all visible rows or columns in Excel. How do I select only the visible cells in Excel 2013/2016. How to delete only visible rows without the hidden rows in Excel.

Delete Visible Rows with Go To Special Feature


Assuming that you have a list of data in range A1:C4, and you have hidden one row (Row 2) in your table, and you want to delete all visible data in your range, if you select range A1:C4, all hidden values also will be deleted. So how to only select the visible values and then delete only visible values without the hidden values in Excel. You just need to do the following steps:

#1 select the range of cells A1:C4 that you want to delete all visible values.

delete all visible rows1

#2 go to HOME tab, click Find & Select command under Editing group. And select Go To Special menu from the drop-down list. And the Go To Special dialog will open.

delete all visible rows2

#3 select Visible cells only radio button under Select section. And click OK button.

delete all visible rows3

#4 only visible cells will be selected, and if you delete those values, just press Delete key in your keyboard.

delete all visible rows4

#5 all visible values are deleted. And you can show all hidden values if they are still kept.

delete all visible rows5

delete all visible rows6

Delete Visible Rows with VBA Macro


You can also use an Excel VBA Macro to achieve the result of deleting all visible rows in your selected range of cells. Just do the following steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

convert column number to letter3

#4 paste the below VBA code into the code window. Then clicking “Save” button.

delete all visible rows7

Sub DeleteAllVisibleRows()
    Dim myRange As Range
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select one Range that you want to delete visible rows", "DeleteAllVisibleRows", myRange.Address, Type:=8)
    myRange.SpecialCells(xlCellTypeVisible).ClearContents
End Sub

#5 back to the current worksheet, then run the above excel macro. Click Run button.

delete all visible rows8

#6 Select one range that you want to delete all visible rows, such as:A1:C4.

delete all visible rows9

#7 let’s see the result:

delete all visible rows6

Video: Delete Visible Rows

 

How to Highlight Blank Cells in Excel

This post will guide you how to highlight blank cells in Excel. How do I use Conditional Formatting to highlight blank cells in Excel.

Highlight Blank Cells with Go To Special Feature


If you want to highlight all blank cells in your select range of cells, you need to select all blank cells firstly, then you can fill color in those selected blank cells. Just do the following steps:

#1 select the range of cells that contain blank cells to be highlighted.

highlight blank cells1

#2 go to HOME tab, click Find & Select command under Editing group, and then select Go To Special from the popup menu list. And the Go To Special dialog will open.

highlight blank cells2

 

#3 select Blanks radio button  , and click OK button. You will see that all blank cells will be selected in the selected range of cells.

highlight blank cells3

highlight blank cells4

#4 go to HOME tab, click Fill Color icon under Font group, and choose one color as you need. All blank cells will be highlighted.

highlight blank cells5

highlight blank cells6

Highlight Blank Cells with Conditional Formatting


You can also use the conditional formatting to highlight blank cells in your selected range of cells in Excel, just do the following steps:

#1 select the range of cells where you want to highlight all blank cells

 highlight blank cells1

#2 go to HOME tab, click Conditional Formatting command under styles group. And select New Rules from the popup menu list. And the New Formatting Rule dialog will open.

highlight blank cells7

#3 select Use a formula to determine which cells to format in the Select a Rule Type list box, and type the following formula into the Format values where this formula is true: text box.

=B1=””

highlight blank cells8

Note: the Cell B1 is the first cell of your selected range of cells.

#4 click Format button to open Format Cells dialog, switch to Fill tab, and then choose one color as you like to fill. Click Ok button.

highlight blank cells9

#5 click OK button. All blank cells in selected range of cells have been highlighted.

highlight blank cells10

Video: Highlight Blank Cells