Jtdylktuy

Basically There are two settings which I want to change from default behaviour of Excel.

1. Changing Default Column width

In Excel, Currently default column width is set to "8.43" for every new instance of Excel.

I need to change this setting and I want to change it once and it should be applied to all new Excels afterwards.

2. To stop excel to convert large numbers into exponent number

If it is possible to set a general rule in excel which suggests all excel instance not to convert large numbers into exponent value.

update - Here I would like excel to accept data as it is entered by user (and not format at all)

For first requirement I have created a template Book.xltx where I have set default Width to 20 and save this template into

C:Users\AppDataRoamingMicrosoftExcelXLSTART

path. But this doesn't seem to help.

UPDATE

I Found the solution for the first question here

I was able to resolve this by disabling the Start screen in Excel.

I had the exact same problem. You all previous posters didn't clarify
whether your Sheet.xltx file was working - mine was working fine. I'd
open a new workbook & it would be the dumb default, but if I added a
sheet it would be my lovely default sheet.

The problem was the same whether I picked "blank workbook" from the
start screen or just hit ctrl-N at any time.

However, by going to File->Options

Then General->Start up options-> uncheck "Show the Start screen when
this application starts"

And now it's using my lovely template.

But this is not working when I open CSV files in excel how to enable it for CSVs as well.

To change Default Column Width, and Stop excel to convert large numbers into Exponential number on a WORKBOOK, I would like to suggest this VBA (Macro) code.

Private Sub Workbook_Open()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

    With ws



        .Cells.ColumnWidth = 20

        .Cells.NumberFormat = "0"

    End With

Next ws



End Sub

How it works:

• Press ALT+F11 to open VB editor window.


• On Top Left find Project Explorer, Click on
  ThisWorkbook.


• 
  Copy & Paste this code.


• To see the effects Close and Open the
  Workbook.

N.B.

• Every time you insert New Sheet, just Close & Open the Workbook to get the effect.

Edited:

You may use this code on Columns/Range of your choice for specific Formats.

Sub formatColumns()



     Columns(1).NumberFormat = "@"

     Columns(2).NumberFormat = "General"

     Columns(3).NumberFormat = "0"

     Columns(4).NumberFormat = "dd/mm/yyyy"

     Columns(5).ColumnWidth = 35

     Columns(6).RowHeight = 20



   End Sub



Or



Sheet1.Range("A2:E500").NumberFormat ="DD/MM/YYYY"







Sheet1.Range("A:E").NumberFormat = "dd/MM/yyyy"