Set default text format

Posted on 2013-01-27
Medium Priority
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.
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.


Assisted Solution

LifeN-Ti earned 1000 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 49

Accepted Solution

PortletPaul earned 1000 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 http://office.microsoft.com/en-au/excel-help/overview-and-samples-of-data-validation-HA001034657.aspx (although this is Excel 2002)
or a more recent video (Excel 2010)
LVL 49

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

771 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