If you have worked with Business documents or with ER Excels, you have probably faced the issue of having merged cells that don’t vertically grow dinamically to be adjusted to the content, even with the Wrap text. Did you know that there’s an OOB solution for it? In this post and also in below video, we are going to see it in detail, hence you can avoid making the errors I did when I discovered it (thanks to my friend Matt Fey for sharing it to me the first time) and started to implement it in all my ER reports. check it out!
The problem:
Merged cells in excel don’t work fine with the Wrap text functionality:
Therefore, when we generate an Excel report from Electronic Reporting or we work with Business Documents if the data to show is too big, the information will be truncated. We can always change the row height in the template, but it is not a valid solution, since we would be establishing a fixed height, the real solution is to have the height to grow dinamically to adapt to the data, that can be achieved using the OOB Auto-fit row height
The solution: Auto-fit row height
My friend Matt Few told me that in the form Electronic Reporting Parameters, we can find in the Runtime tab the parameter Autofit row height:
Disclaimer! If you turn the parameter on, regression test all the excel reports to make sure you didn’t mess any formatting up.
This parameter will allow us to have dinamically adjusted heights in the row Excels, but we have to also match other conditions in the template to make it work. For merged cells:
- Parameter set to yes.
- Wrap Text: The wrap text property in the excel template needs to be set to yes.
- In the excel template rows, we need to make sure to have the cell size set to AutoFit Row Height.
By default the rows in excel have the AutoFit row Height enabled, but when you manually change the height of it, then it swaps to a fixed sized mode. To easily ensure it is correct, you can go to Home tab in excel and in the Cells section, click on format and set the AutoFit Row Height:
It will automatically adjust the size and from then on, that row is “flexible” to grow if needed.
Here you are 3 examples, the 3 of them have been generated with the ER parameter set to yes, but only the first one is working fine, since in the 2nd one we didn’t set the Wrap text, and the 3rd one had a fixed height:
Final considerations
This functionality works pretty good with different fonts, sizes, line breaks… but you might have problems with the needed height calculated wrongly when including indents, upper case texts, some date formats, etc.
Hence, avoid indenting, and you should test carefully the excel generations. In addition, if you locate any potential issue, you can always add an extra space using a hacky solution I found (not the most elegant), that is adding an extra thin row, and merging just the cell in question, like this:
And the result is:
Of course this makes sense when you plan to convert the Excel to PDF.
Conclusion
Microsoft has provided a solution to deal with flexible cells based on data in our Electronic reporting powered Excel files, but you have to meet the 3 conditions listed and explained above for having it to work properly, as well as making sure you know how it behaves and the limitations of the functionality. Thanks for reading and I hope this was as useful as it was for me when I knew the feature for the first time (thanks to Matt Fey again!).
Fernitudela.dev 