188.8.131.52 Working with Excel
Origin provides flexible ways to interact with Excel. You can either import Excel data into an Origin workbook, or open an Excel book inside Origin. If you require full access to all of Origin's graphing and analysis features, you will probably want to import your Excel data files into Origin. If it is important to maintain a separate Excel workbook file—perhaps, so that other colleagues who do not work with Origin have access to that file—you will probably want to open your Excel data files directly. We give a brief introduction to working with Excel in this tutorial.
What you will learn
This tutorial will show you how to:
- Copy-Paste data from Excel with full precision.
- Import an Excel file into an Origin workbook.
- Open an Excel file in Origin.
- Save an Excel file with path relative to the Origin Project file.
Copy-pasting Data from Excel
It may be desirable at times to simply copy+paste data from Excel to Origin instead of importing or opening Excel. These steps show that such a copy-paste operation can bring in data with full precision.
- Launch Excel and Origin separately.
- Open the file <Origin Installation Folder>\Samples\Graphing\ExcelData.XLS in Excel.
- Select columns B through L. Right click, bring up the Format Cells dialog, and set the number of decimal places to 2. Now Excel will show fewer decimal places.
- Click on the top left cell in the Excel sheet to select the entire sheet, right-click and select Copy, or use the keyboard shortcut CTRL+C to copy.
- In a new book in Origin, place the cursor in row 1 of column 1 and press CTRL+V or right click and Paste.
- Note that the numbers come into Origin with full precision, not the number of displayed digits in Excel. As shown in the example below, the number circled value is displayed with 2 decimal places in Excel (0.35) but imported with all three decimal places into Origin (0.348).
- In Origin, right click on the row 1 header and select Set as Comment to make this row an Origin column comment.
- Right click on row 1 again and select Set as Long Name. This makes rows 1 and 2 in Excel the worksheet header in Origin:
- You can now double click column 1, set it as Date, and plot the data.
In Step 5, you can right click to choose Paste Link or press Ctrl+Alt+V instead of choosing Paste or pressing Ctrl+V. As a result, you will create a DDE link between the Excel data and Origin workbook. By this way, the linked data in Origin workbook will change when the values in Excel are changed. You will learn more in this blog.
Importing Excel File
Origin supports importing Excel files directly into Origin workbooks. Multiple sheets are supported and controls are available for setting specific rows in the Excel sheet to be brought into an Origin worksheet as header information such as Long Name or Comments. If you want to perform analysis or data manipulation operations on your Excel data, we recommend importing your data into Origin.
- With a new book active in Origin, use the menu Data: Import from File: Excel (XLS, XLSX, XLSM)....
- Select the file <Origin Program Folder>\samples\graphing\Excel Data.xls and make sure the Show Options Dialog is checked.
- In the dialog that comes up, leave the Use Excel COM Component to Import check box checked.
- Set Index of Rows for Comments From to 1
- Set Index of Rows for Long Name drop-down to 2 and click OK to Import.
- Click and select the Comments cell under column 1, hold the CTRL key and drag the bottom right point of the selected cell to stretch across all columns with data, thus copying the same comments to all columns.
- Press F4 to bring up the format dialog, switch to the Format tab, change the Apply To drop-down to Comments and set Dynamic Merge to Horizontal. Click OK.
This merges the comments cells and shows the title in the center of all the data columns as shown below:
Open Excel File in Origin
At times it may be desirable to keep the data in an external XLS file while opening the file inside Origin as an Excel window and then working with the data. When you open Excel (.XLS or .XLSX) files as Excel workbooks in Origin, an OLE instance of Microsoft Excel is launched. You can plot directly using Excel workbook data, but many analysis features as well as 3D plotting are inaccessible.
- Select File: Open Excel... and select the file \Samples\Graphing\Excel Data.xls.
- A new Excel window opens inside the Origin workspace. When this window is active, the Origin main menu has different entries, some of which are specific to Excel. The Excel toolbars are also available.
- Highlight the Excel data range A3:A26 and right click. Select Format Cells to make sure that the data is Date format.
- Now select the Plot menu in Origin and select the Multi-Y: Double-Y plot type.
- Select A3:A26 in the Excel sheet and click X in the Select Data for Plotting dialog to assign the X data.
- Select B3:C26 in the Excel sheet and click Y in the Select Data for Plotting dialog to assign the Y data. Select Multiple Layers from the Plot Into drop-down.
- Click Plot to create a double-y plot as shown below:
- By default, Origin displays the tick labels for time on the X axis in MM/DD/YYYY format. Double-click the X axis to open the Axis dialog. Select the Horizontal icon in Scale tab and change the scale from 1/1/1970 to 1/1/2000.
Select the Bottom icon in the Tick Labels tab and change the Display to year.
Click OK to apply the settings and close the dialog. The graph should be resemble to the following image:
After opening Excel in Origin, switching to another Origin window, causes a toolbar spacer to become visible where the Excel menu used to be. To remove it, right click and select Hide Toolbar Spacer or Hide Toolbar Spacer Always.
Setting External Excel File Path Relative to OPJ Path
The Origin Project can contain an Excel window which is linked to an external Excel file. In such a case it may be beneficial to save the Excel file in the same folder as the Origin project, or a subfolder under the Origin project folder. This will make the two files more portable, as seen in the following steps:
- Close Excel if it is running.
- Perform the steps under the Open Excel File in Origin section above, and (optionally) create a plot.
- Save the OPJ to a folder location such as "C:\My Files\My Project.opj".
- Right-click on the Excel window and select Save Workbook As. Save it in a (new) subfolder under the OPJ save location, such as "C:\My Files\Data\My Data.xls".
- Right click again on the Excel window title and select Properties. Check the box that says Relative to current project (opj) path. Note that the Excel file path in the box below changes to a relative path of "Data\My Data.xls".
- Save the OPJ again. Now you can copy the entire subfolder structure, starting from where the OPJ is saved, and put it on an external memory device (i.e., a memory stick or similar), or zip the entire folder structure. When taken to another computer and opened, Origin will look relative to the OPJ path to find the Excel file.
If your Excel file is in a different location and you want to save it to the same path as the OPJ, you can open the Excel file in Origin, then right click the title, select Properties and click the Switch to OPJ path upon Saving button. On saving the OPJ, the Excel file will be copied from its current location to the same path where OPJ is saved.