11.1.1.1 Working with Excel

Summary

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.

  1. Launch Excel and Origin separately.
  2. Open the file <Origin Installation Folder>\Samples\Graphing\ExcelData.XLS in Excel.
  3. 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.
    Tutorials81 Working with Excel 001.png
  4. 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.
    Tutorials81 Working with Excel 002.png
  5. In a new book in Origin, place the cursor in row 1 of column 1 and press CTRL+V or right click and Paste.
    Tutorials81 Working with Excel 003.png
  6. 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).
    Tutorials81 Working with Excel 004.png
  7. In Origin, right click on the row 1 header and select Set as Comment to make this row an Origin column comment.
    Tutorials81 Working with Excel 005.png
  8. 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:
    Tutorials81 Working with Excel 018.png
  9. You can now double click column 1, set it as Date, and plot the data.
    Tutorials81 Working with Excel 006.png

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.

  1. With a new book active in Origin, use the menu Data: Import from File: Excel (XLS, XLSX, XLSM)....
  2. Select the file <Origin Program Folder>\samples\graphing\Excel Data.xls and make sure the Show Options Dialog is checked.
    Tutorials81 Working with Excel 019.png
  3. In the dialog that comes up, leave the Use Excel COM Component to Import check box checked.
  4. Set Index of Rows for Comments From to 1
  5. Set Index of Rows for Long Name drop-down to 2 and click OK to Import.
    Tutorials81 Working with Excel 007.png
  6. 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.
    Tutorials81 Working with Excel 008.png
  7. 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.
    Tutorials81 Working with Excel 009.png
    This merges the comments cells and shows the title in the center of all the data columns as shown below:
    Tutorials81 Working with Excel 010.png

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.

  1. Select File: Open Excel... and select the file \Samples\Graphing\Excel Data.xls.
  2. 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.
    Tutorials81 Working with Excel 011.png
  3. Highlight the Excel data range A3:A26 and right click. Select Format Cells to make sure that the data is Date format.
    Tutorials81 Working with Excel 020.png
  4. Now select the Plot menu in Origin and select the Multi-Y: Double-Y plot type.
  5. Select A3:A26 in the Excel sheet and click X in the Select Data for Plotting dialog to assign the X data.
    Tutorials81 Working with Excel 013.png
  6. 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.
    Tutorials81 Working with Excel 014.png
  7. Click Plot to create a double-y plot as shown below:
    Tutorials81 Working with Excel 015.png
  8. 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.
    Tutorials81 Working with Excel 021.png
    Select the Bottom icon in the Tick Labels tab and change the Display to year.
    Tutorials81 Working with Excel 022.png
    Click OK to apply the settings and close the dialog. The graph should be resemble to the following image:
    Tutorials81 Working with Excel 023.png

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:

  1. Close Excel if it is running.
  2. Perform the steps under the Open Excel File in Origin section above, and (optionally) create a plot.
  3. Save the OPJ to a folder location such as "C:\My Files\My Project.opj".
  4. 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".
    Tutorials81 Working with Excel 016.png
  5. 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".
    Tutorials81 Working with Excel 017.png
  6. 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.