Include Top

Data Manipulation in Excel Using SigmaXL

Introduction to Basic Data Manipulation

Open Customer Data.xlsx (to access, click SigmaXL > Help > Sample Data or Start > Programs > SigmaXL > Sample Data). This data is in stacked column format. This format is highly recommended for use with SigmaXL. Note that all pertinent information is provided in each record (row). Also note that only one row is used for column headings (labels) and there are no blank rows or columns. Each column contains a consistent format of either numeric, text, or date. This is also the data format used by other major statistical software packages.

Sample Data

Note that Loyalty, Overall Satisfaction, Responsive to Calls, Ease of Communications, and Staff Knowledge were obtained from surveys. A Likert scale of 1 to 5 was used, with 1 being very dissatisfied, and 5 very satisfied. Survey results were averaged to obtain non-integer results.


Category Subset

  1. Click SigmaXL > Data Manipulation > Category Subset.
  2. If you are working with a portion of a dataset, specify the appropriate range, otherwise check Use Entire Data Table.

  3. Category Subset
  4. Click Next.
  5. Select Customer Type, 1, >> as shown:

    Category Subset Selection
  6. Click OK.

    A new subset worksheet is created containing only Customer Type 1.
    Note: We could have chosen more than one Customer Type and had the option to create a subset which included or excluded these Customer Types.

Random Subset

  1. Click Sheet 1 Tab of Customer Data.xlsx.
  2. Click SigmaXL > Data Manipulation > Random Subset.
  3. Ensure that the entire data table is selected. If not, check Use Entire Data Table. Click Next.
  4. Enter Number of Rows in Random Subset as 30. The default Sort Data selection is Original Order.

  5. Random Subset
  6. Click OK. A new worksheet is created that contains a random subset of 30 rows. This feature is useful for data collection to ensure a random sample, e.g., given a list of transaction numbers select a random sample of 30 transactions.

Numerical Subset
  1. Click Sheet 1 Tab of Customer Data.xlsx.
  2. Click SigmaXL > Data Manipulation > Numerical Subset.
  3. Ensure that entire data table is selected. If not, check Use Entire Data Table. Click Next.
  4. Select Overall Satisfaction, >=, Enter Value as 4.

  5. Numerical Subset
  6. Click OK.
    A new subset worksheet is created containing only those rows with Overall Satisfaction >= 4.

Date Subset
  1. Click Sheet 1 Tab of Customer Data.xlsx.
  2. Click SigmaXL > Data Manipulation > Date Subset.
  3. Ensure that entire data table is selected. If not, check Use Entire Data Table. Click Next.
  4. Select Order Date, select 1/9/2016, click Start Date, select 1/12/2016, click End Date.

  5. Date Subset
  6. Click OK. A new subset worksheet is created containing only those rows with Order Date between 1/9/2016 to 1/12/2016.

Define, Measure, Analyze, Improve, Control

Lean Six Sigma Software Excel Add-in

Simulate, Optimize,
Realize

Lean Six Sigma Software Excel Add-in

Web Demos

Our CTO and Co-Founder, John Noguera, regularly hosts free Web Demos featuring SigmaXL and DiscoverSim
Click here to view some now!

Contact Us

Phone: 1.888.SigmaXL (744.6295)

Support: Support@SigmaXL.com

Sales: Sales@SigmaXL.com

Information: Information@SigmaXL.com