Importing, Consolidating and Maximizing the Value of Excel Data

[#1]

See how to open Excel files and use the Excel Import Wizard.

The Excel Import Wizard is built into JMP and will help guide you through pulling in data that could be anywhere in an Excel workbook, within one or more Excel worksheets.

We need to get data into the right structure for analysis in JMP, and the Excel Wizard will help us accomplish this. JMP has a structured row/column approach to data, where columns represent a single construct, variable or attribute. JMP uses a row to represent a single unit or observation, so that all the data across the row in all the rest of the columns reflect only that one unit or observation.

Use the File -> Open command to open an Excel worksheet or workbook in JMP. By default, the Excel file should be opened in the Excel Import Wizard.

• Select the desired Worksheet and view Data Preview to see what will happen using current Worksheet Settings.
• After import, view Modeling Types for each variable/column on Column Panel and number of rows to make sure import brought in all your rows.

Example: Merged Cells and Multiple Column Headers

• See in Data Preview how JMP handles Merged Cells by default, then select NEXT to view and/or change those default Merged Cell settings.
  • In this example it used Replicate data in spanned rows.

Example: Two Tables in One Excel Worksheet

• There are two tables, process data and associated spec limits, but only one column header.
• Though the column headers are below one of the tables, the Worksheet Settings are flexible, allowing the data above to be paired with headers below. Use the NEXT button and select where the data end to pull in the table just right – providing one table for process data and one for associated spec limits.
• Bonus:  See how to load spec limits to data table as Column Properties meta data.

Example: Multiple Tables Across Multiple Worksheets

• 5 different Excel tables have the same format and are basically the same content, just spread across different Excel Worksheets.
• Check Use for all Worksheets check box.
• Select worksheets from the Worksheets Window.
• Check the box to concatenate Worksheets and let JMP match columns.
• Optionally, choose Add Column that includes Worksheet Name.
• See how JMP might change Data Type if it finds an inconsistency of data type in Excel, and how to find and handle the inconsistent data.

Example: Handling Formulas

• JMP imports data, not the formulas, and Excel allows different formulas in various cells. That inconsistency makes analysis difficult and potentially costly, so we clean/recreate the correct formulas in JMP.
• In this example, we use Stack to format the imported data to give a table that will take advantage of a single formula column that accurately reflects the nature of the relationships and provides the calculated data so it can be analyzed in JMP.

Tips 

• Restore Default Settings if you want to get rid of past work importing Excel files using the Import Wizard.

Additionally, here is a short video on automating Excel importing and making a report:

Excel and Automation 

This is part of the Get Going with JMP series.

Resources:

• Documentation on importing Excel data