Loading
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:

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

 

Leave your own comment:

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

Comments for this tip:

Kirstin Endacott    23 Mar 2016, 20:16
Only thing I've found to work is Gnumeric. A free basic spreadsheet program I found on cnet. It opens the old .wk4 files and allowed me to save as the excel file.
Willy Vanhaelen    01 Mar 2016, 10:27
@Steve LaPha

Are you sure it are Lotus 123 files because the file extension for those is ".wks" not ".123". You can try to change the extension to wks.

I know that Excel 97 had a converter to open them but Excel 2007 can't open them anymore.

IBM Symphony is a Windows program and uses only the name of Lotus' Symphony but has further nothing to do with this Old DOS program.
Steve LaPha    29 Feb 2016, 12:08
I have this issue. I have some files that are in the Lotus 1-2-3 "*.123" format. I have not been unable to open them with Office 2010. I downloaded Open Office and that would not open my files. I also tried downloading a copy of Lotus Symphony and that would not open them. I tried a couple of converters on the web but they did not either. Does anyone have a solution that works with these types of files?
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.

Regards,

L.
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

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

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.