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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


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:)

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

Office 365 Training for IT Pros

Learn how to provision Office 365 tenants, synchronize your on-premise Active Directory, and implement Single Sign-On.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

737 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