Clever Clogs tip # 735
2 December 2023

Change automatic data conversion to suit your way of working (Excel 365) *

Here we are, last tip of the year. Which, since 2015, only meant one thing... a list of your top 10 tips. But as I changed the weekly tips to monthly tips this tradition isn't really appropriate anymore. So here's a "normal" tip about a new option in Microsoft 365. (I've added a link to the top tips of the last 8 years at the bottom of this email; enjoy!)

So where was I... new option in Excel 365.

Back in July 2019 I told you about one of my pet peeves in Excel being that you cannot add a zero in front of a number. No, 01223214177 should not be turned into 1223214177.

OK, you can put a space after the area code. Or you can put an apostrophe before the "leading zero".

But a new setting was recently added to Microsoft 365 that you might want to change if you often work with or import, say, telephone numbers.

Here's how:

  1. On the File tab, click Options.
  2. Type the letter D (or click Data).
  3. Under Automatic Data Conversion untick Remove leading zeros and convert to a number.

    Depending on your day-to-day use you might want to disable all data conversion or pick and choose the ones that are appropriate for you. Those of you who often open .csv files might want to play with the various settings.

  4. Press ENTER or click OK.

Next time you type, paste or import a file with a number starting with a zero into Excel you might spot three things... an apostrophe is added in front of the number and a tiny green triangle appears in the top left hand corner telling you about the fact that the number is now stored as text. Which is why it is left aligned, rather than right aligned.

Hope this was a helpful tip to end 2023. I'll continue to post a shortcut of the week on my home page and will stay active on LinkedIn.

Enjoy the festive season. And remember: Escape isn't just a button on your keyboard!

Related tips

  1. How to put a zero in front of a number - tip_543.php
  2. Display your numbers with leading zeros - tip_193.html

Top tips of 2015 - 2022

  1. Best of 2022 - top 12 tips - tip_723.php
  2. Best of 2021 - top 11 tips - tip_683.php
  3. Best of 2020 - top 10 tips - tip_634.php
  4. Best of 2019 - top 10 tips - tip_591.php
  5. Best of 2018 - top 10 tips - tip_559.php
  6. Best of 2017 - top 10 tips - tip_529.php
  7. Best of 2016 - top 10 tips - tip_502.php
  8. Best of 2015 - top 10 tips - tip_470.php

Last month's CleverClogsTipTime on LinkedIn (newest post first)

  1. Four sleeps to go
  2. What's on your post-it this week?
  3. AutoCorrect or AutoIncorrect
  4. Training doesn't have to be long or torturous to be effective!
  5. Find and delete all data but keep Formulas, Conditional Formatting and/or Data Validation (super useful at the end of a tax year!)
  6. Do you use the Navigation Pane in Word?
Until next year!


* Unless stated otherwise this tip is written for Microsoft 365 desktop apps and Windows 10 users, but might also be useful in Office 2010, 2013, 2016 and 2019.