Getting around common pitfalls working with Excel data in JMP
Feb 17, 2016 5:23 AM
For whom it is good for:
If you are a new user trying to make use of your Excel data in JMP, this will probably prevent you from falling in traps others have too. Maybe even more important this could act as a reference for experienced users who are going to help colleagues doing that task and asking questions about why the data does not look like they are expecting it to be.
Excel sheets are still a common source of data. For analytical purpose JMP users know better tools, but how to make good use of JMP’s functionalities if the data is in a shape that is not easy to import. How to handle different structures, multiple sheets in one table, different date formats, localization challenges and similar?
In this eposter I will show some hopefully useful ways to make your life easier. However there is no one golden rule which fits all. Although JMP is capable of working with Excel Files both on Windows and Mac OS, this poster mainly presents the Windows side. I refer to JMP 12, at the same time many recommendations are true also for JMP 11 or previous. The Import Wizard for Excel files came with JMP 11.
Starting with the structure of a data set it is important to understand the differences between JMP and Excel. In Excel you can enter all kinds of data into one cell, no matter of the structure of the rest of the data. In JMP a cell is related to its column, and therefore to one specific variable. In addition one spreadsheet can have multiple worksheets, in JMP you would have several or one combined table.
You can imagine that Excel files in a trivial structure are best to import. However not always you have variable names in the first row and the data beyond. You will not be able to directly read in or drag and drop the data into JMP.
Some structures in Excel files are good to gain overview of the data in a table. However those structure is often very cumbersome for the purpose of statistical analysis. Another challenge could be the type of data, e.g. dates could be an issue – and that’s no matter what software you are using. Localization and others can make it difficult. Learn how you could overcome some of these.
I'm happy to answer your questions either at the discovery Meeting, via email or via the comments section below. Looking Forward to seeing you in Amsterdam.