Set default text format

Posted on 2013-01-27
Last Modified: 2013-02-09
Hi Experts,
Is there a way to set the default format of few cells of worksheet to Text ot Number or Date? So that whenever it should stay there permanent and I would not have to chage it at all?

Thank you in Advance.
Question by:RadhaKrishnaKiJaya
  • 4
  • 2
  • 2
  • +1
LVL 21

Expert Comment

by:Tapan Pattanaik
ID: 38825675
Hi RadhaKrishnaKiJaya,

Please  check these links.

Setting Up Excel Default Formatting:

Expert Comment

ID: 38825677
Are you referring to in a saved worksheet that you utilize or overall in excel when you make a new workbook those cells are always that format?

If you're just talking about working within a specific worksheet right click the cell, go to format cells, and select the format you'd like it to default to under Categories:) if that's not what you meant just let me know, take care!

Author Comment

ID: 38827382
Thank you LifeN-Ti and tapanpattanaik for your help.

tapanpattanaik, your links are very informative.  Thank you again.

LifeN-Ti, I am trying to make changes on a existing worksheet (saved worksheet).  Yes, we can make changes by right click --> Format Cells etc.  Here user can modify it.  Here what I am looking for is to make the cell permanent.  Even if user tries to modify, it should not work.  In other words, to make the chages permanent.

Can we code it in ThisWorkBook (hit Alt + F11 key, you would see it), so that it should maintain the default format permanently?  Please let me know.  Thank you.

Assisted Solution

LifeN-Ti earned 250 total points
ID: 38828597
Hi RadhaKrishnaKiJaya,
No problem! And ahhhh I see!

You want to protect the workbook and lock the cell so it can't be modified aside from changing the value inside (they can't change the format/delete it/etc only change the value). You can do that by going under Format Cells, then going to the "Protection" tab and selecting "Locked" - once you do that you then have to protect the workbook (review tab) as it mentions how under the locked option, once you protect the workbook and the cell is locked it cannot be modified with exception to the value of the cell. Is that what you meant? So they can't change the category even if they wanted to? If I misunderstood I apologize, just let me know:)
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.


Author Comment

ID: 38828866
Thank you LifeN-Ti, this is really helpful.  I tried your instructions which protects the whole worksheet.  Infact, I don't want to protect the worksheet altogether as the users need to update it frequently.  Instead, I just want protects the format of the cells.  For example, A1 can accept only numbers, nothing else.  A2 can accepts only Dates, nothing else.  Here, user can enter data, but not change the data type.  I know we can achieve it using simple VBA coding inside ThisWorkBook.  But I don't know how.  Please let me know if you have any idea.  Thank you.
LVL 48

Accepted Solution

PortletPaul earned 250 total points
ID: 38849483
For example, A1 can accept only numbers, nothing else.  A2 can accepts only Dates, nothing else.  Here, user can enter data, but not change the data type.  

I believe you need to define this input area as a "table", which will enable you to maintain a consistent format per column.

See Quick Start: Create an Excel Table (Excel 2010)

or: Setup Database in Excel (youtube) note: the presenter makes a data entry error when trying to use automatic calculations from the table - this is actually a good thing to know :-)

For data validation refer to this (although this is Excel 2002)
or a more recent video (Excel 2010)
LVL 48

Expert Comment

ID: 38866961
Hi, are your worksheet formatting and data validation needs solved?

Author Comment

ID: 38871954
Hi PortletPaul, Thank you for the post.  They are very helpful.  I did some temporary fix manually.  Here I was looking for some kind of global macro, which should do it automatically.  For example, the worksheet can read ThisWorkBook file upon activating the macro (hit Alt + F11 key).  Thank you.

Author Closing Comment

ID: 38871958
Thank you guys for your help.

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now