Engine Works

Under the hood of Alteryx: tips, tricks and how-tos.
22 - Nova
22 - Nova

by Kiran Manjunatha and Bhumika Agarwal (@BA02)

Once upon a time, before you read this awesome article:


Stakeholder: “Hey! Can you merge those cells like we do in our Excel workbooks? Also, providing a common header would be a good idea too.”


Alteryx developer: “Naah, I don’t think so. Not possible using Alteryx.”




Source: GIPHY 


What if we say, “Yes, it is possible! And here is how.” (Given you are a “jugaad” developer.)


With the intention of not making this guide very long and boring at the beginning itself, here’s some good news! It will be a three part series. Woohooo! Here is the outline of the series:


1. How to Create an Alteryx Report with Merged Rows (you are here)




2. How to Create an Alteryx Report with Merged Columns




3. How to Create an Alteryx Report with Both Merged Rows and Merged Columns




Due to our lack of capability to generate cool datasets, we shall be using a subset of publicly available superstore sample data.


Let’s get to it!


How to Create an Alteryx Report with Merged Row Cells


Prepare the data in the desired format with all the required columns you wish to see in your output. Refer to the example below.




Now let's start by converting the data into the desired report first.


The concept here is that your merged cells row will become the parent row, and all the other rows in other columns will become the rows in the child table.


Step 1: Create child tables for each parent merge row cell


Insert a Basic Table tool and configure it like below:





Additionally, configure the tool to add the borders, background column, alignment, etc. [Optional]


Step 2: Create the parent table with nested child tables


Insert a Basic Table tool and configure it like below:




Additionally, configure the tool to add the borders, background column, alignment, etc. [Optional]


This specific step (step 2) will create row merged cells.


Step 3: Now, let’s add the column headings!


In a Text Input tool, input headings as a row like below:




Insert a Basic Table tool and configure it as:




Now use the union tool to add the headings on top of the data tables. Make sure to configure the output order accordingly.




Step 4 [Excel]: Phew! Output


Add a Render tool and configure it to write it to Excel.




Excel report output:




Other Formats


“We don’t want it in Excel. Any other formats which Alteryx can render to?”


For output formats other than Excel, we need to add an extra step to get the borders to align. Add a Layout tool and configure it as below:




After the Layout tool, add a Render tool and configure it to the required file format. This table format after the Layout tool can also be used in the body section of an Email tool!


Optional Step [Other Formats]


You might have observed that in formats other than Excel, you can see the extra line like the one below the Technology row.




You can make the extra lines less visible by using white borders and light grey background for the table cells like below. This will give a more elegant look. Nice!




We hope this was helpful. Feel free to reply to this post and hit us up with your queries if you are facing any issues or need any clarifications. We shall be happy to help.


Cheers and Happy Analyzing :-)


Source: GIPHY


Stay tuned for the next part of this blog! We hope you’re as excited as we are for this blog series!


Read Part 2 Here.


9 - Comet

I was excited when I saw this blog series, thinking there was finally a new tool and/or setting in an existing tool to configure these types of table output options.  The step-by-step instructions here are really helpful, but I would love Alteryx to develop a much easier way to output data in these types of merge cell formats with settings inside a single table tool.  Something like an additional section in the table tool to, select the fields you'd like to [merge rows/merge columns] and number of rows/columns to merge [ numeric value ]. I'm sure it's way more complicated than that, but just a feature request. 


Alteryx Community Team
Alteryx Community Team

@collin_pace You can give a like to this idea that someone submitted for Designer to try to resurface it :)  https://community.alteryx.com/t5/Alteryx-Designer-Ideas/Multiple-row-headers/idi-p/29317


9 - Comet

Thanks @MeganBowers for the link.  Funny enough, after clicking it, I found that I already liked this idea at some point in the past.  Happy to see you bring more attention to it!  Maybe it gains some traction.

5 - Atom

this is very helpful. thank you especially for the easy-to-follow steps.

9 - Comet

This is some cool + easy stuff !👍 Thank you @atcodedog05  and @BA02  ...

5 - Atom

I was able to follow it and do it on my own! Woohooo!! Thanks @atcodedog05  & @BA02 

5 - Atom

This is great stuff. :) 

22 - Nova
22 - Nova

Thank you 🙂 @archelaus @AsmiDesai @Deepali77 @Joyce-Swapna 


Happy to hear that you folks found it helpful and easy to follow 😀



20 - Arcturus

Nice work @atcodedog05 !!!!!

13 - Pulsar
13 - Pulsar

Hello @atcodedog05  I don't know why I only see this series today. Very cool and thank you!

This is SO helpful. Thank you very much!!

6 - Meteoroid

Need of time, Thank you

6 - Meteoroid

wow this is very helpful. The only issue I run into after rendering to excel, is when I filter on a merged column, it will only pull up one row. In the sample dataset, in excel if I filter on Segment to Consumer, only one row of data will appear. Is there a way to get the other categories aligned to Consumer (Office Supplies and Technology) to appear?




11 - Bolide

Never too later to see such a terrific post!

17 - Castor
17 - Castor

This is a very useful post & series Kiran & Bhumika ( @atcodedog05 @BA02 ) - thank you for this.

As you say - it's a very common request that we see frequently from our users - and this is a simple way to achieve it.