Imagine you are working on a very important worksheet, and all of a sudden, you are stuck with the no cells were found Excel error. It typically arises when Excel is unable to find the specified cells due to issues with references, formulas, or data validation. This cryptic message sends a wave of panic through users. But don’t fret, understanding the error is the initial step to solving it.
Screenshot of the error:
In this blog, we will delve into the details of this error, providing insights, and troubleshooting methods to outcome no cells error and keep your Excel error-free.
To Fix corrupt Excel workbook, we recommend this tool:
This software will prevent Excel workbook data such as BI data, financial reports & other analytical information from corruption and data loss. With this software you can rebuild corrupt Excel files and restore every single visual representation & dataset to its original, intact state in 3 easy steps:
- Try Excel File Repair Tool rated Excellent by Softpedia, Softonic & CNET.
- Select the corrupt Excel file (XLS, XLSX) & click Repair to initiate the repair process.
- Preview the repaired files and click Save File to save the files at desired location.
Insight of Conditional Formatting No Cells Were Found Error
Excel no cells were found error occurs when attempting to clear out the data from cells in spreadsheets that are using formulas. It simply indicates that there are issues with references, formulas, or data validation.
Many users have reported that they received this warning message when using the ‘Go to special’, and ‘constants’ options.
Error Details:
- Error code: Runtime Error 1004
- Error name: no cells were found
Why Is My Excel Formula Not Detecting Cell?
Oftentimes, the root cause of this error lies within the formulas & references used in your worksheet. Besides, there are some other factors as well that can lead to this error, they are as follows:
- Improperly using the Go To command
- Maybe the selected cells are empty
- Excel workbook corruption
- Using invalid formulas in the cells.
How to Fix No Cells Were Found Excel Error?
Try the following fixes to troubleshoot it.
So, let’s start knowing about each of these fixes in detail.
Method 1- Locate Hidden Cells on a Worksheet
This issue can also be encountered when the cell of your Excel worksheet may reside within the hidden columns or rows. Well, it’s a quite tough task to find them out but not impossible.
So, try the following steps to find hidden cells on the Excel worksheet:
1. Open the Excel worksheet which is showing the runtime error 1004 no cells were found. Now you need to access the Excel feature “special” through one of the following ways:
- Go to the Home tab then to Editing After then hit on the Find & Select icon side arrow key.
- This will show a drop-down menu, choose the Go To Special option from it.
- Or on your Excel worksheet simply press Ctrl+G button from your keyboard. This will open the go to a dialogue box in which you have to press the Special button present at the bottom.
- Press F5> Special.
- Or just press the F5 key from your keyboard in your opened workbook. This will also open the go to dialogue box from which you have to press the Special
2. In the go to special box hit on the Visible cells only After that, tap on the OK option.
3. You will see that all visible cells are been selected now. Besides that, the column/row borders adjoining to hidden rows and columns will seem with a white border.
Also Read: 6 Methods for Excel Not Recognizing Numbers in Cells!
Method 2- Deleting Empty Rows in Excel using VBA
In my Excel macro, there is a segment that appears at the range which searches the blank cells and fills it up. Run the below code:
Range(“E10:A” & CStr(bottom – 1)).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Value = “N/A”
where
bottom = ActiveSheet.Cells(Rows.Count, “B”).End(xlUp).Row
Well, the above macro code works correctly when there is a blank cell within the range. But it will show “1004 Error: No cells were found error” in the line “specialcells.select”.
After checking my complete Excel macro coding, I have concluded to one point. Throughout the range, I need to run a loop that will check for the blank cells. But when I tried that, I noticed that this method is quite slow and clunky.
At last, I found that this error can easily be fixed just by adding an error handler.
For Example:
Sub Main()
On Error GoTo NoBlanks
Range(“A1:A10”).SpecialCells(xlCellTypeBlanks).Value = “N/A”
NoBlanks:
Resume Next
‘ or just add code here to run whenever there is no empty cells found
End Sub
Method 3- Use The Notepad Or Notepad C++
One of the simplest ways to fix Excel No cells were found error is just making use of the notepad or notepad application. Learn how it is to be done:
1: First of all open the Excel worksheet in which you are getting the error.
2: After that, make selections of the cell meanwhile entering any formula. Copy all the selected cells from the Excel worksheet and then paste them into the Notepad or Notepad++ application Window.
3: Now once again copy the entire cells from the Notepad or Notepad++. Then, paste it into your active Excel worksheet.
4: It’s time to check whether you are getting the same error again or if it gets resolved.
Method 4: Move the File to Fix No Cells Were Found Excel Blanks Error
Another effective solution that you can try is moving the Excel file to the new workbook. Follow the below steps to get your task done:
- Open the corrupted Excel file > right-click on the sheet tab > Select All Sheets.
- Now right-click the Excel sheet tab > click Move or Copy.
- Select New workbook > Choose Create a copy box > Click OK.
Also Read: Microsoft Excel Can’t Insert New Cells (11 Quick Methods)
Method 5- Use Microsoft’s Built-In Open and Repair Feature
As already mentioned in the causes section, corruption in the workbook can also result in the occurrence of an Excel error message no cells were found. In such a situation, you can use Microsoft’s built-in Open and Repair utility to repair the corrupt Excel file.
Follow the below steps to learn how to use this tool:
- Open Excel >> Go to the File> Open.
- Next, click on the Browse to select the corrupted workbook.
- In the Open dialog box, select the corrupted document.
- After this, click on an arrow next to an “Open” button >> click Open and Repair
- If you want to recover as much of the spreadsheet data as possible, click Repair.
In case, Repair isn’t able to repair and recover your worksheet data, click on the Extract Data button to extract values & formulas from a workbook.
Recommended: Utilize the Excel Repair Tool
If your Excel file is severely corrupted and the Open & Repair tool fails to repair it, it is recommended to use the Excel Repair Tool. It is the professional recommended utility that helps you repair and recover corrupted, damaged, and inaccessible data from the Excel workbook.
This unique tool is capable of restoring entire data including the charts, worksheet properties cell comments, and other data without doing any modification. It is easy to use and supports all Excel versions.
Steps to Use MS Excel Repair Software:
User Questions and Answers
Can This Error Message Lead to Permanent Data Loss?
No, this error message doesn't lead to data loss. But improper handling of the file during troubleshooting might cause severe data loss. So, make sure to keep a backup file before solving the error.
What If I Encounter the Error in A Shared Spreadsheet?
If the same error occurs in a shared spreadsheet, communicate with other users and ensure everyone follows the troubleshooting steps collaboratively.
How Do You Fill Blank Cells in Excel with Go To Special?
By implementing the method #1 mentioned in this blog, you can fill blank cells in Excel with Go To special command.
What Is the Alternative to ISBLANK in Excel?
The if( Value = Blank() is the alternative to ISBLANK in Excel to test specifically for a blank cell.
Also Read: Fix Excel Cannot Shift Nonblank Cells Off The Worksheet Error
Bottom Line
In conclusion, the Excel data validation no cells were found error might be a temporary roadblock, but if equipped with the right knowledge & troubleshooting methods, you can overcome it easily. Also, take good care of your Excel worksheet data by making a habit of maintaining a proper backup.
Happy spreadsheeting!
Priyanka Sahu
Priyanka is a content marketing expert. She writes tech blogs and has expertise in MS Office, Excel, and other tech subjects. Her distinctive art of presenting tech information in the easy-to-understand language is very impressive. When not writing, she loves unplanned travels.