Showing results for 
Show  only  | Search instead for 
Did you mean: 

Practice JMP using these webinar videos and resources. We hold live Mastering JMP Zoom webinars with Q&A most Fridays at 2 pm US Eastern Time. See the list and register. Local-language live Zoom webinars occur in the UK, Western Europe and Asia. See your country site.

Choose Language Hide Translation Bar
Importing, Consolidating & Maximizing the Value of Excel Data 

Use the JMP Excel Import Wizard to import Excel data for simple and complicated Excel file structures. 



See how to:

  • Easily source the data from Excel files with data
    • Anywhere in the worksheet, visible or hidden
    • Multiple worksheets
    • Merged cells
    • Multiple row or hierarchical column headers
  • Identify your Excel structure before import to plan your approach
    • Cell-grid based – properties, formulas, etc. that may differ between any pair of cells
    • Files that contain multiple worksheets
    • Charts that “float” over worksheet or as separate tab
    • Worksheets with multiple tables or random content anywhere
    • Row 1 that contains anything (column headers, data, or nothing)
    • Tables that have multiple rows of column header names (or none)
  • Understand JMP table structure
    • JMP file contains a single table
    • Each Column
      • Includes information that can be set after import (Data Type - Character or Number- and Modeling Type -Nominal, Ordinal or Continuous
      • Column Information/Properties support interactive analysis
      • Is named at top with unique name and reflects a single attribute or measure for all rows
    • Each row is a single unit or observation, e.g., one experimental run outcome, one sample, one wafer, one patient, one event, etc.
      • Row 1 contains first data point
  • Use the JMP Excel Import Wizard
    • Manage merged cells and repeating data
    • Handle hidden, empty and nested columns
    • Handle replicate data in spanned rows
    • Handle multiple tables in one worksheet
    • Combine multiple tables
    • Handle different types of data using Format Patterns
    • Handle Excel formulas because cells with formulas will import as the static value when Excel file saved, so consider doing calculations in JMP as formulas after import
  • Save work to JMP17 Workflow Builder

Manage Importing Excel Merged Cells to JMPManage Importing Excel Merged Cells to JMP

Questions answered by Greg Flexman @GregF_JMP and Clark Ledbetter @Clark_Ledbetter at the 2024 live webinar.


Q: I've noticed that if the excel file is filtered, and we try to import it into JMP, it only brings in the rows that are shown as a result of the filter. Is there a way to bring in the entire file even if we forgot to remove the filters? (i.e. is there a setting we can change to default to always NOT supressing hidden rows?)

A: Yes, in the import wizard on the second page, i.e. after you select "NEXT" there is a setting to "suppress hidden rows". You can toggle that to bring in the entire data sheet. The wizard by default assumes if filters on are in Excel that is what you want, but you can overrride with this "suppress" setting.


Q: Is the workflow builder only for 17?

A: Correct. Workflow Builder was released in JMP 17. If your maintenance is up to date you can upgrade to latest JMP version for free.


Q: How do you concatenate two jmp files? Is the same approach used as is for excel files?

A:  Open the files then Tables>Concatenate


Q: Sometimes when I import Numeric Data it imports as Character Data, for example dates or a string that has an exponent symbol.
A: If there is any Character (Non-number) data in a column, the entire column with be imported as Character Data and you will need to find the problem data and correct it before changing the Column Info>Data Type to Numeric.


Q: How can I correct the loss of leading zeros, like in zip codes such as 06518?
A: Here is the formula for adding leading zeros to Zip Code: Right(Char(:col), 5, "0").






Recommended Articles