ExcelRibbon.Tips.Net ExcelTips (Ribbon Interface)

Working with Lotus 1-2-3 Spreadsheets

Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Working with Lotus 1-2-3 Spreadsheets.

You probably already know that Excel will easily open a wide variety of spreadsheets stored in other formats. One that it will open is spreadsheets originally created in Lotus 1-2-3. If you have some old 1-2-3 files and you want to make sure that they are treated, in Excel, according to Lotus 1-2-3 "rules," there are a couple of settings you need to pay attention to.

Start by displaying the Excel Options dialog box. (In Excel 2007 click the Office button and then click Excel Options. In Excel 2010 and Excel2013 display the File tab of the ribbon and then click Options.) Scroll all the way down to the bottom of the options and you should see a section named Lotus Compatibility Settings. (See Figure 1.)

Figure 1. The advanced Excel Options.

Notice that there are two options in this section; these are the two you want to work with.

  • Transition Formula Evaluation. When checked, this option causes Excel to open and evaluate 1-2-3 files without losing or changing information. Based on the formulas in the 1-2-3 file, if this option isn't checked, then you might lose some data or some formulas won't calculate the same in Excel as they would have in Lotus 1-2-3. Specifically, with this option selected Excel evaluates text strings as 0 (zero), Boolean expressions as 0 or 1, and database criteria according to the rules used in Lotus 1-2-3.
  • Transition Formula Entry. If this check box is selected, then formulas entered in Lotus 1-2-3 version 2.2 syntax are converted to Excel syntax. This option should not be selected if you don't plan on saving the spreadsheet in Excel format.

Make your settings, as desired, then close the Options dialog box. Open the Lotus 1-2-3 spreadsheet and Excel should process the file according to the settings you made.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12498) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Working with Lotus 1-2-3 Spreadsheets.

Related Tips:

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!


Leave your own comment:

  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*What is 4+5 (To prevent automated submissions and spam.)
          Commenting Terms

Comments for this tip:

Nikhil    18 Apr 2015, 19:56
Excel won't open all kinds of excel files
tigerflying    29 Oct 2014, 02:25
didn't work for me either, using Excel 2007

I am now pursuing downloading OpenOffice and using the spreadsheet there to open the old Lotus files, then save in a modern Excel format
Laurent Masse-Navette    02 Feb 2014, 19:35
Hi Allen,

I have been surfing the internet pretty much the entire day and I have grown convinced that your claim that WK4 old Lotus format files can be imported into Excel 2013 just isn't true. Even though the menus you mention are there, there is no way to unlock the lotus formats in the list of formats that this version of Excel can read. If I try to bypass this and just read it as an unknown format file, it just reads garbage. This link explicitly lists the format as unavailable: http://technet.microsoft.com/en-us//library/dd797428.aspx

I have read somewhere that the translation software has been removed at some point because they discovered that lotus formats had security flaws. It has been so since office 2003. See the link: http://office.microsoft.com/en-us/office-2003-resource-kit/office-2003-service-pack-3-administrative-template-adm-files-opa-files-and-explain-text-updates-HA010238019.aspx?CTT=1

If you insist that these formats are indeed supported, I would ask you to try importing a WK4 file in Excel 2013 and let us know the exact sequence of steps you've followed.


Wilbart    25 Jun 2013, 07:45
Excel 2013 won't open a Lotus file.
This tip didn't make any difference?
Am I missing something here?

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us


Advertise with Us

Our Privacy Policy

Our Sites


Beauty and Style




DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)



Home Improvement

Money and Finances


Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives


Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.