Stack or Wrap Columns

I was asked how to prepare a table with 50 columns for printing. The specific request was to break each record in the data table into multiple printed rows with a blank row in between each record. Basically, we want the columns to wrap when printed. In this post, I’ll show how to define groups of columns that can be stacked for printing purposes with Power Query.

Objective

Before we get too far, let’s visualize the situation. We have a job cost table with a ton of columns, like this:

We can’t effectively print this because of the number of columns. If we fit them to print on a page, the font size is too small. So, we need to somehow get Excel to wrap or stack groups of columns. That way, each job cost row becomes multiple printed rows with a blank row between each job. More like this:

As this is Excel, there are multiple options, including formulas. In this post, I’ll demonstrate one option that uses Power Query. This option has the benefit that it will dynamically adapt to new data source rows when refreshed.

Video

Overview

We’ll walk through the following steps

  1. Pick the columns for each report row
  2. Combine them
  3. Format the report table

Let’s get to it!

Note: this tutorial uses Power Query … you can quickly determine if your version of Excel includes Power Query by clicking the Data tab, and looking for the “Get & Transform Data” group of commands.

Detailed Steps

Time needed: 15 minutes.


Overview: we will create a new query for each report row that defines its columns. Then, we will append the queries. Finally, we apply some formatting to the results table.

  1. Pick the columns for each report row

    We will perform this step for each report row.

    Our illustration creates four report rows for each job:
    Report row 1: Job ID
    Report row 2: Owner, Org, Type, SysCode, PM … Bill Type
    Report row 3: Cur Hrs, Cur Cost, YTD Hrs … PctBud
    Report row 4: blank row

    Since our report contains 4 rows per job, we will perform these steps 4 times.

    A. In Excel, select the data table
    B. Data > Get Data > From Table/Range
    C. Select the columns to appear in this row (eg, Job ID)
    D. Home > Remove Columns > Remove Other Columns
    E. Transform > Use Headers as First Row
    F. Add Column > Index Column > From 1
    G. Add Column > Custom Column > “Row” =1 (2, 3, 4, …)



    H. Name the query Row1, Row2, …
    I. Close & Load To … Connection only query

    Upon completion, we’ll have one new query for each report row. Now it is time to combine them.

  2. Combine them

    To combine the individual row queries in Excel:

    A. Data > Get Data > Combine Queries > Append
    B. Three or more tables > Add them in order:



    C. Sort by Index column
    D. Sort by Row column
    E. Remove the Index and Row columns
    F. Close & Load To … Table

    The results table appears in Excel, and now we are ready to format it.

  3. Format the report table

    Use the Table Tools tab to format as desired.



    Here are the steps I used to create the report shown above:
    A. Table tab > uncheck Header Row
    B. Applied Bold to first three report rows
    C. Table tab > uncheck Banded Rows
    D. Table tab > check Banded Columns
    E. Table tab > Table Styles

Once this solution is in place, we can just right-click and Refresh to pull in any new jobs from the data source table.

If you have any alternatives you prefer, or have any improvements to this approach, please share by posting a comment below … thanks!

Sample File:

Posted in ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Stay Connected

If you'd like to be notified when I write a new Excel article, enter your name and email and click SUBSCRIBE. You can unsubscribe anytime, and I will never sell your email address.

Want to learn Excel?

Our Campus Pass includes access to our entire Undergrad and Masters catalog. Gamification ensures it is the most fun you can have learning Excel :)

2 Comments

  1. Daniel Lamarche on July 16, 2020 at 11:11 pm

    And you were kind enough to supply the sample file for this project!!! I’d like to be the first one to thank you for that.
    I am passionate with PQ and I’m attempting to throw in a quick example after every Excel Level 3 that I give.

    Looking forward to follow your “Applied Steps” 😉

    Daniel

    • Jeff Lenning on July 17, 2020 at 8:33 am

      Hi Daniel … thanks for your kind note, and I’m glad the sample file will help!
      Thanks
      Jeff

Leave a Comment