1

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.

  1. 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\\AppData\Roaming\Microsoft\Excel\XLSTART

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.

0

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"
  • what will it do, when excel is having data like "11.8" ? – Amit Mar 1 at 10:12
  • @Amit,, it's a general solution, you asked how to stop Excel to convert Exponential conversion and I've suggested. Now you are thinking differently, next you say how about DATE. First you decide what is your real need!! – Rajesh S Mar 1 at 10:16
  • hello Rajesh, I have updated my question. "Here I would like excel to accept data as it is entered by user (and not format at all)" Because preventing large data to be converted into exponent value, I cannot afford 11.8 to be converted into 12 . – Amit Mar 1 at 10:18
  • @Amit,, this works in general for entire Sheet,, If You want handle both Exponentials and Numbers use this .Cells.NumberFormat = "0#.0" – Rajesh S Mar 1 at 10:20
  • I'm really sorry but that too will not fit into ""Here I would like excel to accept data as it is entered by user (and not format at all)" .. "0#.0" will affect dates. – Amit Mar 1 at 10:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.