list data validation function disabled

KAR FUNG 121 Reputation points
2026-09-27T14:46:12.6666667+00:00

Hi Sir,
Originally, some of the bold blue text is with function of list data validation, got data for selection.

However, this function suddenly disable for sheets. and all pictures in the sheet are missing.

Kindly advise, how to restore the spreadsheet. The file as per follow link.

Microsoft 365 and Office | Excel | Other | Other
0 comments
Answer accepted by question author
Rory Archibald 19,220 Reputation points Volunteer Moderator
2026-09-30T11:02:45.46+00:00

It sounds like you have the display of objects turned off. Check under File - Options in the Advanced section:

User's image

Make sure it is set to 'All' and not to 'Nothing'.

Was this answer helpful?

2 people found this answer helpful.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest

  1. AI answer

    2026-09-27T14:46:26.94+00:00

    Check these Excel conditions first, because each can make Data Validation appear unavailable or stop working:

    1. Finish cell edit mode.
      • If a cell is currently being edited, the Data Validation command is not available.
      • Press Enter or Esc, then try again.
    2. Check whether the worksheet or workbook is protected or shared.
      • Data validation settings cannot be changed if the workbook is shared or protected.
      • Unprotect or unshare the workbook first.
    3. Verify that data is being typed directly.
      • Data validation messages only appear when users type directly into a cell.
      • If data is copied or filled, validation messages do not appear.
      • To reduce this, go to File > Options > Advanced > Editing options and clear Enable fill handle and cell drag-and-drop, then protect the worksheet.
    4. Make sure calculation is automatic.
      • Go to Formulas > Calculation Options > Automatic.
      • Manual recalculation can prevent validation from working correctly.
    5. Check formulas used by validated cells.
      • If formulas return errors such as #REF! or #DIV/0!, Excel ignores the data validation until the error is fixed.
      • Also confirm any referenced cells still point to valid values.
    6. If the drop-down source depends on another sheet, confirm compatibility.
      • In Excel 2010 and later, data validation can refer to values on other worksheets.
      • In earlier versions of Excel, that setup is not supported.
    7. If the workbook was inherited and validation cannot be changed:
      • It can be modified or removed unless the worksheet is protected.
      • If the protection password is unknown, contact the previous owner.
      • Another supported workaround is to copy the data to another worksheet and then remove the data validation.

    For the missing pictures, no supported recovery steps are provided in the available material.

    References:

    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.