Home » Questions » Computers [ Ask a new question ]

Predefine the format which can be entered in an excel cell

Predefine the format which can be entered in an excel cell

Okey, this is a little hard to explain. I need to restrict entering the data in an excel cell, to a certain format, in this case to a time format which looks like, for example: 1:05 (h:mm).

Asked by: Guest | Views: 62
Total answers/comments: 1
Guest [Entry]

"This is actually pretty easy to do.

Right-click on the cell you want to restrict the format in
Choose ""Format Cells""
In the ""Numbers"" tab (the one that it opens on), you will see a list of categories
For your example, choose ""Time""

On the right, you can choose different formats in which to have the time displayed
If you can't find a format that fits your need, choose ""Custom"" from the Categories list
Scroll down in the new box, and towards the bottom are some more time formats to choose from. There are some tips here on how to use the custom formatting.

To force users to only be able to enter matching text, I think you can use form controls. To access them, you need to enable the Developer tab in the ribbon. You can do this by clicking the Orb, ""Excel Options"" at the bottom of the menu, and then tick the third checkbox down, ""Show Developer tab in the Ribbon"".

Once the Developer tab is in the ribbon (to the right of the view tab), you will be able to insert Form and ActiveX controls in the spreadsheet. You have a LOT more control over these, but there's also more work involved. I've only ever used them in Word, where I'm able to limit text to only being in an allowable format.

Microsoft has a KB on How to use the form controls on a worksheet in Excel."