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.
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:
Comments for this tip:
Bob Hopkins 04 Jan 2017, 07:51
If you still have a working version of Lotus 123 you can do a "save as" to produce an Excel file in either .XLW or .XLS format. You can then open these in Excel, then save them again in the latest Excel format.
The conversion is not 100% perfect but it gives you a flying start.
There can be quite a number of issues to address. Just one, for example, is that in a Lotus table the column offsets start at zero whereas in Excel they start at 1. You just have to work through all the issues you encounter until the Excel version works in an acceptable way.
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
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
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?