Preparing a Worksheet

Overview

Frequently, you use worksheets that are already created for you. However, sometimes you must enter or import data into a Minitab worksheet before you start an analysis.

You can enter data in a Minitab worksheet in the following ways:
  • Type the data directly into the worksheet.
  • Copy and paste the data from other applications.
  • Import the data from Microsoft Excel files or text files.

After your data are in Minitab, you might need to edit cells or reorganize columns and rows prepare the data for analysis. Some common manipulations are stacking, subsetting, specifying column names, and editing data values.

In this chapter, you import data into Minitab from different sources. You also learn how ShippingData.MTW was prepared for analysis.

Get data from different sources

For the initial analyses in Getting Started with Minitab 18, the worksheet ShippingData.MTW, which contains data from three shipping centers, is already set up. However, the three shipping centers originally stored the shipping data in the following ways:
  • The Eastern shipping center stored data in a Minitab worksheet.
  • The Central shipping center stored data in a Microsoft Excel file.
  • The Western shipping center stored data in a text file.

To analyze all of the shipping data, open each file in Minitab, then stack the files into one worksheet.

Open a worksheet

Start with the data from the Eastern shipping center.

  1. Open the sample data, Eastern.MTW.

Open data from an Excel spreadsheet

The Central shipping center data are in an Excel spreadsheet. You can open Excel files in Minitab.

  1. Save the sample data, Central.xlsx.
  2. Choose File > Open.
  3. Browse to the folder where you saved the data set.
  4. Double-click Central.xlsx.
  5. Click OK.

Open data from a text file (*.txt)

The Western shipping center data was in a text file. Open the text file.

  1. Save the sample data set, Western.txt.
  2. Choose File > Open.
  3. Browse to the folder where you saved the data set.
  4. Double-click Western.txt.
  5. Click OK.

Combine the data into one worksheet

Notice that the worksheets for the shipping centers have the same column names. To make the data easier to analyze, you need to combine the data into one worksheet by stacking columns that have the same names. You can move data by copying and pasting or by using commands on the Data menu.

  1. Choose Data > Stack Worksheets.
  2. From Stack option, select Stack worksheets in a new worksheet.
  3. Use the arrow buttons to move the three worksheets from Available worksheets to Worksheets to stack.
  4. In New worksheet name, enter MyShippingData
  5. Click OK.

Move and rename a column

The column Source contains the labels that identify data from the shipping centers. Move the Source column to C1, and rename the column Center.

  1. Click in the Source column, then choose Editor > Move Columns.
  2. Under Move Selected Columns, select Before column C1.
  3. Click OK.
  4. Click in the column name cell Source, type Center, and press Enter.

Prepare the worksheet for analysis

The data are now in a single worksheet, but you still need to manipulate the data in the following ways:
  • Recode data
  • Add a new column
  • Create a column of calculated values
Tip

For a complete list of data manipulations available in Minitab, go to Help and How-To Overview and click "Manipulate Data in Worksheets, Columns, and Rows" in the navigation menu on the left. Then, click "How-To."

Recode the data

The labels in the Center column do not adequately identify which center the data are from. Recode the labels to remove the file extension.

  1. Choose Data > Recode > To Text.
  2. In Recode values in the following columns, enter Center.
  3. In Method, select Recode individual values.
  4. Under Recoded value, replace Eastern.MTW with Eastern.
  5. Under Recoded value, replace Western.txt with Western.
  6. From Storage location for the recoded columns select In the original columns.
  7. Click OK.

The labels in the Center column are now Eastern, Central, and Western.

Calculate difference values

Before you save your new worksheet and perform analyses, you need to calculate the number of days that elapsed between order dates and delivery dates. You can use Minitab’s Calculator to assign a formula to a column to calculate these values. If you change or add data, the calculated values are automatically updated.

Insert a column

Insert a column between Arrival and Status.

  1. Click any cell in C4 to make that column active.
  2. Right-click, then choose Insert Columns.
  3. Click in the name cell of C4. Type Days, then press Enter.

Assign a formula to a column

Use Minitab’s Calculator to perform basic arithmetic or mathematical functions. Minitab stores the results in a column or a constant. You can assign a formula to a column so that the calculated values update automatically if the data change.

Calculate the delivery time and store the values in the Days column.

  1. Choose Calc > Calculator.
  2. In Store result in variable, enter Days.
  3. In Expression, enter Arrival - Order.
  4. Select Assign as a formula.
  5. Click OK.
Note

You can also add a formula to a column by selecting the column and choosing Editor > Formulas > Assign Formula to Column.

Tip

For more information on formulas in columns, go to Calculator functions. For more information on Minitab’s Calculator and the available operations and functions, go to Overview for Calculator.

Examine the worksheet

The Days column contains the calculated values that represent delivery time. These values are expressed in number of days. When you assign a formula to a column, a status indicator appears in the upper right corner of the column heading on the worksheet. This indicator specifies whether the formula is properly defined and whether the data need to be updated by re-calculating the values. A green check mark indicates the data are up-to-date.

Tip

Hold the pointer over the status indicator to view the formula assigned to the column. Double-click the status indicator to edit the formula.

Update the worksheet

Suppose you learn that the arrival date for a shipment in the Central shipping region is incorrect. If you correct the date in the worksheet, Minitab automatically updates the Days column.

Update the arrival date in row 127.

  1. In the Arrival column, double-click row 127 to put it into edit mode. Change 3/7/2013 9:17 to 3/8/2013 9:17.
  2. Press Enter.
Minitab automatically updates the value in the Days column from 2.98125 to 3.98125.
Original worksheet
Updated worksheet
Note

If you prefer to update formulas manually, then choose Editor > Formulas > Calculate All Formulas Automatically to deselect this option. If values in the worksheet change and cause the formula in a column to be out of date, the status indicator for that column changes to a yellow triangle. Choose Editor > Formulas > Calculate All Formulas Now to update all formulas in the project.

Save the worksheet

Save all your work in a Minitab worksheet.

  1. Click in the worksheet, then choose File > Save Worksheet As.
  2. Browse to the folder that you want to save your files in.
  3. In File name, enter MyShippingData.
  4. From Save as type, select Minitab.
  5. Click Save.

In the next chapter

The shipping center data from several sources are in Minitab and are set up properly for analysis. In the next chapter, you adjust Minitab default settings to make future analyses easier.

By using this site you agree to the use of cookies for analytics and personalized content.  Read our policy