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
- Pick the columns for each report row
- Combine them
- 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.
- 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. - 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. - 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:
Excel is not what it used to be.
You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.
Want to learn Excel?
Our training programs start at $29 and will help you learn Excel quickly.
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
Hi Daniel … thanks for your kind note, and I’m glad the sample file will help!
Thanks
Jeff