Prism tip - Power pasting from Excel to Prism
It is common to use Excel to organize and manipulate data and then copy selected values from Excel and paste into Prism. We think this is a great way to work -- keep all your data organized in Excel and then graph and analyze what you want in Prism.
Prism has tools to make this both easy and powerful. If you use Prism for Windows, you can use the Microsoft's object-linking-and-embedding (OLE) to embed an Excel spreadsheet right in your Prism file, or you can maintain a link from your Prism data table to your Excel sheet so that when you update your data in Excel, the changes will propagate to your Prism graphs and analyses.
If you use Prism for Mac, you can't take advantage of OLE, but there are still lots of ways you can manipulate your Excel data when pasting it into Prism. With both Prism Windows and Prism Mac, you can transpose rows to columns, rearrange data generated by an instrument, filter your data, etc.
This page explains the many choices you have.
Paste Special (Windows and Mac)
When you choose Paste Special, Prism pops us the same dialog used to Import data. Some of its capabilities include:
- Set defaults for either linking to Excel sheets or embedding them.
- Define the meaning of commas. Are commas used to denote thousands? Or are they used as a decimal separator (common in many countries? In one case "10,000" means ten thousand and in the other it means ten (point zero, zero, zero).
- Tell Prism how to handle spaces in the data files.
- Import a row of data, skip a specified number of rows, then import another row.
- Rearrange the data as you import. For example, place three (any number) of values side by side, then go down to the next column.
- Define a special value (perhaps 999) to denote missing values, and show these values as missing (blank) in Prism.
- Rename the Prism data table to match the Excel file name.
- Tell Prism whether or not to import your column title from Excel.
- Specify where in the Prism data table you want the pasted or imported data to go.
- Transpose columns to rows or row to columns.
Paste Transpose (Windows and Mac)
When you paste transpose, the data block is rotated 90 degrees. Each row from Excel becomes a column in Prism, and each column in Prism becomes a row in Prism. This can help when you want to organize the data in Excel one way, but Prism requires the opposite arrangement. With Prism Windows you can also choose to either embed your Excel sheet into your Prism file or maintain a link so that when you change your Excel data your Prism files will show the changes.
Paste Embed (Windows only)
When you choose to Paste Embed, a copy of the Excel file will be embedded into your Prism file. On your Prism data table, the values pasted into Prism will be surrounded by a border, and you won't be able to edit those values directly in Prism. (unless you unlink).
Instead, double-clicking will open the embedded Excel file. You can edit this workbook in Prism (it will be your entire original workbook, even if it has several worksheets tabs). Then close Excel and the updated values appear in your Prism data table.
Everything is organized in one file. You can use Excel to organize your data and do preliminary calculations, and then paste selected values into Prism. When you edit the data in Excel, you can instantly update the analyses and graphs in Prism.
Paste Link (Windows only
When you choose to Paste Link, Prism will paste the values into the data table and also create a link back to your Excel file. The link has two functions. It lets you trace (and document) the source of the data so you stay organized. It also is a live link. If you edit or replace the data within Excel, Prism will update the analyses and graphs.
Note that the link is via the file name. If you move the Excel file, or rename it, the link will be gone. If you are working with a new Excel file that has no name, you won't be able to link (save the file first).
Pros and cons of embedding vs. linking
An advantage of embedding is that everything will be in one file. You don’t need to worry about keeping track of multiple files. You don't need to save the spreadsheet file separately (except as a backup). The disadvantage of embedding is that your Prism files will be larger.
The advantage of linking is that your Prism files stay small, and you won't end up with several files with the same data (which can get confusing if you need to edit some values). The disadvantage of linking is that the link only persists so long as the linked file keeps its location and name. It is easy to lose track of that link.
A disadvantage of both linking and embedding is that you can't simply edit the pasted values in Prism. You need to double-click to return to Excel. But it is easy to unlink the Prism data block.
If in doubt, use Paste Link. If you plan to share files with Mac users, simply paste the data and don't bother with linking or embedding.
Why won't Paste Embed or Paste Link work on Macs?
We wish we could make it work, but Apple and Microsoft simply don't provide the infrastructure we would need to make embedding or linking work on Macs. If you open a file with linked or embedded tables on a Mac, you'll see the data as plain editable data in Prism Mac.
What exactly is pasted? Round-off issues.
When you copy a block of values from Excel to the clipboard, you paste exactly what you see on screen. If you told Excel to only show one digit after the decimal point, that is what is copied to the clipboard. So Prism only can paste the value with one digit after the decimal. Before copying data from Excel, use the Format Cells command to show plenty of digits so round-off issues are not a problem within Prism. Prism 5.03 and 5.0c offer an option on the Paste Special dialog to paste as many digits as possible, even if Excel rounded to fewer digits.