Auto-formatting is a very helpful feature that, as the name indicates, automatically formats the data you input into spreadsheets so it looks better and is usually easier to understand. But there are times when you do not want the data to be formatted, in which case, you will have to turn off the feature. Fortunately, doing so is quite easy and you can even customize auto-formatting to only disable some of its features while still keeping the feature enabled.

Option 1: Turn off auto-formatting for a range or a cell

To prevent Excel from automatically formatting your data in a range or a cell, you need to format it as text. This will ensure that the data remains unchanged but will not turn off auto-formatting for the entire sheet.

  1. Open your Excel spreadsheet and select the cell for which you want to turn off auto-formatting. If you want, you can select multiple cells by dragging your cursor across them.
  1. Once you've selected the cells you want, right-click on the selection and then click on 'Format cells'.
  1. When the 'Format cells' dialog box opens, click on the 'Text' option under the 'Number' tab and then on the 'OK' button.
  1. Now, even if you type in special characters or a number with leading zeroes, the formatting won't change. The same goes for text you type in as a date, such as '03-24', which won't be formatted as a date.

Option 2: Turn off auto-formatting permanently

You can also turn off auto-formatting permanently in Microsoft Excel for all spreadsheets.

  1. Open Microsoft Excel and click on the 'File' menu at the top left.
  1. Then click on 'Options' on the bottom left.
  1. Next, click on 'Data' on the left in the Options dialog box.
  1. In the 'Automatic Data Conversion' section, check that the checkbox next to the 'Enable all default data conversions below when entering, pasting, or loading text into Excel' is unchecked. If it is enabled, click on it to disable it.
  1. Click on the 'OK' button to close the dialog box and now Excel will not automatically format the data you insert into a spreadsheet.
  1. Alternatively, you can keep the setting on if you do not want to completely disable auto-formatting and instead disable some of the options within.

Things to know

  • Another method by which you can prevent auto-formatting is to use an apostrophe before typing in the values in a cell. This will stop Excel from formatting the data and it will remain intact.
  • You can also apply the Visual Basic Code or VBA Code to your data in Excel to stop it from formatting your text automatically. To do this, you will need to enable the Developer tab and then insert a module into Excel, which is why it is not the ideal option for beginners.
  • Once you have disabled auto-formatting in Excel, you can enable it at any time by going into Options from the File Menu.