Need to Reorganize Excel Data?

QI Macros Makes it Easy to Stack & Unstack Data in Excel

You can find the restacking and unstacking tools in the Restacking section of the QI Macros menu:

To use the restacking and unstacking tools:

  1. Click and drag over your data to select it.
  2. Click on QI Macros menu and select the restack/unstack function you want.
  3. Answer the prompts and QI Macros will reorganize your data for you.

Stack Columns with Blank Rows

If you have multiple columns of data and would like to stack your data into a single column, use this tool. It will automatically place a blank row between your different data sets!

NOTE: Included in the January 2021 release, this functionality is utilized if an end user has multiple columns in their data set and chooses to create a single-column Variable Control Chart (e.g. XmR Chart).

Data Before

data in multiple columns

Data After

multiple-column data stacked into one column with blank row between

Stack Data by Columns

Sometimes you need to stack several columns of data into fewer columns or even into one column. QI Macros will prompt you for how many columns of data you want to end up with. In this example, we chose "1". QI Macros leaves the original data and makes a copy into the new format:

Data Before

data in columns

Prompt

number of columns prompt

Data After

data stacked into one column

Stack or Restack (e.g., from 6x6 to 3x12)

Stack or Restack takes any size table and switches the data into as many columns as you choose. You can increase or decrease the number of columns:

Data Before 6x6 Table


restack example before

Prompt


reorder table prompt

Data After 3x12 Table


restack table after

Unstack by Label

Unstack by Label takes any size table and unstacks it based on the labels in column A:

Data Before


unstack by label before

Data After


unstack by label after

 


Unstack by Multi Label

This functionality is designed to take a column of data that has multiple labels (e.g., entity and shift) and group it into columns for each label combination (e.g., entity - shift):

Data Before


unstack by multi label after

Data After


unstack by multi label after

Unstack by Subgroup

Using any number of columns, Unstack by Subgroup will use the number of samples per subgroup (in this case 4) and unstack the data. This function is generally used to go from one column to many:

Data Before


unstack by subgroup before

Prompt


unstack by subgroup prompt

Data After


unstack by subgroup after

Unstack Matrix to List

This functionality is designed to take a group of multi-column data that has multiple labels and groups them into a single-column list of data:

NOTE: Included in the April 2020 release, the Unstack Matrix is now able to process zeroes within your data set.

Data Before


unstack matrix before

Data After


unstack matrix after

 


Generate a Random Sample

This functionality is designed to take either a single or multi-column set of data and put it into random order, depending on the number of samples you would like to randomize.

To do so, simply highlight your data:

highlight-data-before-randomizing

 

Select "Random Sample" from the "Restacking" menu -
Input the "Number of Samples" you would like to randomize:

how-many-numbers-in-your-random-sample

Do you have any headers in your data set?
(NOTE: You do NOT need to include headers in your data set):

does-your-data-have-headers

You will then be provided with a randomized set of data based upon the number of samples you chose to generate.

One example of how this tool could be used, is if you have a large set of data and want to create a Pareto Chart.

Instead of creating a Pareto out of the ENTIRE data set (e.g., 500,000 rows), you could generate a random sample out of 100 of those points and THEN create your Pareto.

NOTE: This functionality is included in the October 2022 release.


Replace Zeros with Blanks

This functionality is designed to analyze your data set and remove any zeros and/or decimal zeros, replacing them with blank cells, instead. This should be used if you do not want zeros to be calculated within your data set, because Excel treats zeros as data points - not as blanks.

To use this macro, simply highlight your data:


highlight-data-before-running-replace-zeros-with-blanks

 

Select "Replace Zeros with Blanks" from the "Restacking" menu -
The zeros in your data set will be turned into blank cells:

output-after-replacing-zeros-with-blanks

NOTE: This functionality is included in the January 2023 release.


Insert Table without Formatting

When inputting a table into an Excel spreadsheet or template, Microsoft Office automatically removes all previous formatting from your spreadsheet and inputs Excel's default formatting instead.

This tool, allows the end user to insert a table into their Excel spreadsheet without their formatting changing!

To use this macro, simply highlight your table range:


insert-table-template-example

 

And select "Insert Table W/O Formatting" from the "Restacking" menu -
A table is now inserted in your spreadsheet:

table-design-output

NOTE: This functionality is included in the October 2023 release.


Isn't it Time to Upgrade Your Data Mining and Analysis Skills?

Stop Struggling to Analyze Your Data in Excel!
Start using QI Macros data mining tools and smart charts.

Download a free 30-day trial. You'll Look Like an Expert!

QI Macros Creates These Smart Charts