Set default zoom setting

Using an Addin

Creating an Excel addin (.xlam) with the following code:

Public RunWhen As Double
Private Sub Workbook_Open()
MsgBox "The Zoom will be changed to 150%."
cRunIntervalSeconds = 5 ' Update value every five seconds
cRunWhat = "ThisWorkbook.TheSub"  ' the name of the procedure to run
    RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
        Schedule:=True
End Sub

Sub TheSub()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
   ws.Select
   ActiveWindow.Zoom = 150
Next ws
MsgBox "Zoom has been changed."
End Sub

Create a blank new workbook.

  1. Press Opt+F11 (or Tools > Macro > Visual Basic Editor) to activate the Visual Basic Editor.

  2. In the Project Explorer pane on the left, double-click ThisWorkbook under Microsoft Excel Objects.

    Paste the code into Thisworkbook section in VBE:

    VBA zoom

    Save the workbook as .xlam file, e.g., SetZoom.xlam.

  3. Enable the macro setting in Excel.

    Add this Excel addin file by: File > Add-Ins > Go… > Browse… > locate the .xlam file and then double click on it to add.

Next time after you open an existing workbook the code will run to change the Zoom value to 150%.

Using a template

  1. On your Mac create a new folder called “xlstart”. You can create this folder anywhere, but I prefer doing it within the Documents folder.
  2. Launch Excel
  3. Go to Preferences > General > At startup
  4. For open all files in set the xlstart folder you created at Step 1 as the path
  5. Exit preferences
  6. Open a new workbook and customise your Zoom setting to 150%.
  7. While you’re at it, you can also customise a whole heap of other attributes if you like
  8. Save the workbook as an Excel template, making sure you use the name “workbook”, and make sure it’s saved in the xlstart folder you created at Step 1. (Note: when saving the file, if Excel has placed the .xltx extension at the end of the file name, then manually remove the .xltx extension before saving)
  9. Quit Excel
  10. Launch Excel

Reference: https://apple.stackexchange.com/a/276021

Save a workbook as a template

  1. Open the workbook that you want to save as a template.

  2. On the File menu, select Save as Template.

  3. In the Save As box, type the name that you want to use for the new template.

  4. (Optional) In the Where box, choose a location where the template will be saved.

  5. Next to File Format, select Excel Template (.xltx), or, if your workbook contains macros, select Excel Macro-Enabled Template (.xltm).

  6. Select Save.

    Unless you select a different location, the template is saved in /Users/*username*/Library/Group Containers/UBF8T346G9.Office/User Content/Templates.

Use your template to create a new workbook

To start a new workbook based on a template, on the File menu, select New from Template, and then select the template you want to use.

Set delimiter when reading csv

Using different delimiters/separators than your default one.

  • if the Decimal Separator is a period (.) then the CSV separator will be a comma. ← 🇺🇸
  • if the Decimal Separator is a comma (,) then the CSV separator will be a semicolon. ← 🇪🇺

Text to Columns

The trick is that you have to create a new workbook and then import the CSV file rather than open directly. [Not ideal ❌]

In the CSV import dialog you can select things like the delimiter, encoding, etc..

A pain for this approach: you need to save as a separate file after the import.

A workaround to avoid creating a duplicate: ✅

  1. Select the 1st column

    Usually everything is loaded to the 1st column as delimiters were not loaded correctly.

  2. Then go to your Data tab and start the Text to Columns process, follow it all the way to the end, and make sure that you uncheck everything but the delimiter you want to be default.

    This step changes the delimiter to your system default one. You original data file will be overwritten.

  3. Save the change. Next time Excel will load your csv file correctly.

Source: https://stackoverflow.com/a/16465120/10108921

Indicate separator directly in CSV file

[This method is versatile as it can accommodate any delimiter, but NOT recommend as it complicates the dataset import for other software ❌]

For Excel to be able to read a CSV file with a given field separator, you can specify the separator directly in that file. For this, open your file in any text editor, say Notepad, and insert the below string as the first row:

  • To separate values with comma: sep=,
  • To separate values with semicolon: sep=;
  • To separate values with a pipe: sep=|

In a similar fashion, you can use any other character for the delimiter - just type the character after the equality sign.

Once the delimiter is defined, you can open your text file in Excel like you normally would.

For example, to correctly open a semicolon delimited CSV in Excel, we explicitly indicate that the field separator is a semicolon:

Excel read csv

❗️A side effect of this is when you read the csv using other apps, such as R or Numbers, the first raw will need to be skipped.

References:

https://answers.microsoft.com/en-us/msoffice/forum/all/how-do-i-set-default-zoom-in-excel-2019/334f9386-758c-4747-9390-69c611d05197

https://learn.microsoft.com/en-us/archive/msdn-technet-forums/a1f4d7b0-2f68-4f05-a673-717467141c5e

https://learn.microsoft.com/en-us/answers/questions/715252/how-to-make-excel-to-open-to-a-pre-defined-zoom-le