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.

Working with Lotus 1-2-3 Spreadsheets

by Allen Wyatt
(last updated April 2, 2013)

8

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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Printing Multiple Pages On a Piece of Paper

If you want to save paper on a printout, you might consider printing multiple pages on a single piece of paper. This can be ...

Discover More

Adding Drop-Shadows to Paragraphs

Drop shadows are a style of paragraph border used to enhance the visual impact of a paragraph. They are also a great way to ...

Discover More

Printing via Macro without Messages

When you are printing a document, it is not unusual to see messages (dialog boxes) periodically. When you want the printing ...

Discover More

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!

More ExcelTips (ribbon)

Enlarging the Formula Bar

The Formula bar is used to display the formula that appears in a cell. You may want to modify how the Formula bar is ...

Discover More

Controlling How Excel Interprets Percentages

When entering data in a worksheet, Excel tries to figure out how your entry can best be shown on the screen. When it comes to ...

Discover More

Getting Rid of Numbered Columns

Excel normally refers to columns as A, B, C, etc. It also has a referencing format that allows columns to be referred to by ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 9 + 4?

2017-01-04 07:51:35

Bob Hopkins

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.


2016-03-23 20:16:06

Kirstin Endacott

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.


2016-03-01 10:27:02

Willy Vanhaelen

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


2016-02-29 12:08:23

Steve LaPha

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?


2015-04-18 19:56:03

Nikhil

Excel won't open all kinds of excel files


2014-10-29 02:25:43

tigerflying

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


2014-02-02 19:35:56

Laurent Masse-Navette

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.


2013-06-25 07:45:23

Wilbart

Excel 2013 won't open a Lotus file.
This tip didn't make any difference?
Am I missing something here?


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.