How to fix missing data validation arrows in Excel workbook

When entering data into a workbook, very often you will come across a situation when you have to enter the same data into cells. In such situations, data validation comes really handy. Instead of typing things again and again, you can create drop-down lists in the cells. This way you just need to select the item from the list and move on to the next cell. In a nutshell, if you add data validation drop-down lists to your Excel spreadsheet, you can easily enter the data. Though, at times, when you open an Excel workbook, you may find the data validation arrows missing. The reasons could range from objects being hidden to workbook corruption. While most of the reasons can be dealt by changing settings, but in case of a corrupt Excel sheet, you would be required to look out for efficient excel repair solutions.

Consider a scenario, wherein, you add data validation lists to your spreadsheet (which contains critical information about your company's clients), when you open the same workbook again to enter more data, you find the data validation arrows missing.

Cause

The above problem could surface in the following conditions:

• You might not have clicked the cell with data validation dropdown arrow (considering you worksheet contains many cells with data validation)
• If your worksheet contains other shapes like logos, text boxes and you have enabled a setting to hide them. Since they are objects too, the dropdown arrows will also remain hidden.
• You could have turned off the default option for a dropdown list
• Your worksheet may be corrupted

Resolution

Try the following steps in order to fetch the data:

• Make it easy to recognize the cells that have data validation. You can color them or add a cell comment
• Make the objects visible again
• Turn on the default setting for a data validation list to show the arrow
• In order to fix corrupt Excel file, you can observe the following Repair excel file steps:

1.Try copying the data to a new workbook, the data validation arrows may reappear
2.Or try to repair Excel file as you open it. ( Use Open and Repair feature)
3.If you are not able to get the desired results, opt for an excel repair software. Such third-party tools extract information from a corrupt Excel file by employing improved scanning algorithms.

Solutions for 'Excel found unreadable content in Book_Name'

A pivot table is the most efficient feature of Microsoft Excel 2007. It helps you summarize a table's data by its different fields. You can create the table by defining which fields to view and how the data should be displayed. Based on your selection, Excel organizes the data and enables you to get a different view of the data. Though at times, you may encounter various errors while trying to open an Excel workbook with pivot tables. A pivot table using key performance indicators (KPIs) or a corrupt workbook could be the reasons among others behind error messages. In order to access the stored data in a workbook, you should adopt corrective measures or use a backup. If nothing seems working, you can go for an advanced excel repair utility.

Consider a scenario, wherein, you try and open a Microsoft Excel 2007 workbook and receive the following error:

“Excel found unreadable content in Book_Name. Do you want to recover the contents of this workbook.”

When you click 'Yes', you come across the following Excel recovery report:

“Removed Records: PivotTable report from /xl/pivotCache/pivotCacheDefinition1.xml part (PivotTable cache)”

And, if you open the same Excel workbook in the Microsoft SharePoint Web Part report page, you may receive the following error message:

“The workbook that you selected cannot be opened. The workbook may be in an unsupported file format, or it may be corrupt. Would you like to try and open this file in Excel?”

Cause

The above error could have surfaced due to corruption (a cause itself stated in the above error message). But, there could be other reasons which are listed below:

• If the Pivot Table uses key performance indicators (KPIs)
• The KPIs are created in the Analysis Services Business Intelligence Development Studio
• On or more of the KPIs have an expression in the Current Time Member property

Resolution

If one or more KPIs have an expression in the Current Time Member property, then you can combat the problem by removing the expressions from all Current Time Member properties.

In case, you still encounter an error, it suggests that your Excel file is corrupt. To fix corruption, you need to seek help from an repair excel file software. Such third-party tools apply improved scanning algorithms to extract data from corrupt Excel workbooks in an effortless way. You can easily download an Excel repair utility from the Internet.

Repair corrupt Excel files by converting them to SYLK format

A Microsoft Excel workbook can get corrupt in various instances including application malfunction, unexpected system shutdown, third-party plug-ins, operating system corruption, human errors and others. You can repair Excel file by converting it into another format i.e. Symbolic Link Format (SYLK) format. Converting a corrupt Excel file in this format makes sense as it helps filtering out the corrupted elements. SYLK is a Microsoft file format which is typically used to exchange data between applications specifically spreadsheets. A SYLK file has a .slk suffix.

If your MS Excel file, which is either created with MS Excel 2002 or MS Excel 2003, is corrupt and you can open it, then you must follow the below-mentioned steps to filter the file:

• Open the damaged or corrupt Excel file, go to File and click Save As
• In the Save As type list, click SYLK, and then save the file (If your workbook contains more than one spreadsheet, using this step you can only save the active sheet using the SYLK format. In order to save multiple sheets , you can save each sheet as a separate SYLK.)
• You may encounter a message saying that the selected file type does not support workbooks that contain multiple sheets, click OK to save only the active sheet.
• And, if you come across a message saying that your workbook contains features which are not compatible with the SYLK format, click Yes
• Now click Close, if you see a message which says save the changes you made, click Yes
• Go to the File menu and click Open
• Select the .slk file that you saved, and then Open it
• Go to File menu and then click Save as
• In the Save as type list, click Excel workbook and then Save

To repair Excel file, this method is recommended and used very often. If you still fail to repair damaged excel file or it is severely damaged that you cannot open it, there are plethora of Excel repair utilities which perform repairing operation efficiently.