For the figures in this example, we used the Team Results.xlsx file located in the JMP Samples/Import Data folder. The file has the following characteristics:
Team Results.xlsx Worksheet
Example Initial Data Preview
4.
Type 3 for Column headers start on row
5.
Type 4 for Data starts on row.
6.
Select Ungrouped Team Results in the Worksheets pane.
7.
Deselect Use for all worksheets.
Selecting the Column Header Row
See Individual Worksheet Settings for details about all options.
8.
Click Next to configure other import settings.
9.
For Data ends with row, type 9.
10.
For Data ends with column, type 5.
Specifying the Last Column shows your changes.
Specifying the Last Column
See Additional Individual Worksheet Settings for details about all options.
11.
Click Import to convert the worksheet as you specified (Final Data Table).
Final Data Table
Adds a new Source Table column that lists the worksheet name for each imported table. This option is available after you select the preceding concatenate option.
If you deselect Replicate data in spanned rows, JMP unspans the cells and copies the cell contents into the topmost cell. The remaining unspanned cells are left empty.
Divides subcategories into separate columns in a worksheet with hierarchical headings. You must also select Treat multiple column header lines as hierarchies. The main category is imported as the Label column.
Repeats the header text in each cell for rows that are spanned in the worksheet. For example, the State column heading shown in Replicated Headers in a Microsoft Excel File was in a merged cell in the worksheet. On the left, the heading is replicated. Deselect the option to avoid repeating the heading as shown on the right.
Replicated Headers in a Microsoft Excel File
Tips: 
To speed up the data preview in large worksheets, deselect Update settings on any change on the first wizard window. Modify the settings and then click Update now to refresh the data preview.
To view all rows in the Data Preview pane, select Show all rows. The preview might be slightly delayed depending on the size of the spreadsheet.
In an Excel worksheet, multiple header rows can have an implied hierarchy; the second header row contains data that are categories of the first header row. The Original Data in Excel and Final Data in JMP shows an example. In the worksheet at the top, the seasons “Winter” and “Spring” are in spanned cells above the months within those seasons. In the JMP data table, you want the seasons in one column and their corresponding months in another column.
The Original Data in Excel and Final Data in JMP
1.
In JMP, select File > Open.
2.
In the Open Data File window, select Texas Precipitation.xlsx, located in the JMP Samples/Import Data folder, and then click Open.
Excel Import Wizard Preview
Excel Import Wizard Preview shows the default settings for this worksheet:
3.
Under Preview Pane Refresh, make sure that Update settings on any change is selected.
4.
Next to Number of rows with column headers, click the up arrow once.
Notice that Data starts on row automatically updates to 3.
Updated Settings on Page One shows the updated settings.
Updated Settings on Page One
5.
Click Next.
6.
Select Treat multiple column header lines as hierarchies.
7.
Next to Data ends with row, type 6 and press Enter.
Updated Settings on Page Two shows the updated settings.
Updated Settings on Page Two
Notice that Replicate data in spanned rows is selected by default. JMP unmerges data that were merged in the worksheet and copies the cell contents as separate categories. “TX” was originally in a merged cell. In JMP, it will be copied into separate cells.
8.
Click Import.
The Final Data

Help created on 9/19/2017