Tip of the week # 491
11 August 2016

How to remove limitations of what is displayed in a cell (Microsoft Excel) *

The other day somebody asked me how they could 'unlock' the restriction of about 1,000 characters that can be written in an Excel cell.

Well, in Excel version 2007 if a cell contained more than 1,024 characters, or if it contained a formula that returned a result of more than 1,024 characters, only the first 1,024 characters were displayed in the cell. Furthermore, additional characters weren't printed and did not appear in print preview either.

So if you created your workbook using Excel version 2007 you might still have the restriction described above, even if you are now using version 2010, 2013 or 2016. You can obviously increase the row height and column width of the worksheet, but if you have upgraded all you have to do is save the existing file as a newer version.

Here's how:

  1. Open the document, as normal.
  2. Press F12 (or File, Save As if you prefer).
  3. Select the first option labelled Excel Workbook (*xlsx) from the Save as type drop-down list.
  4. Press ENTER or click on Save.

You now have a copy of the document, so you might want to rename or delete the old one. Or, if you are using a content management system, I would strongly recommend saving it as a new version - on top of the old one.

If you are keen to find out more about Excel specifications and limits click here for version 2010 or here for version 2013 and 2016.

With thanks to Bernice for today's tip inspiration.

* Unless stated otherwise, these tips were written for Microsoft Office 2010.