Fully Automating Excel Report Font Size Adjustment Under Severe Constraints

---

---

1. Introduction

Background: Typical "Excel Report" Workflow in Business Systems

The business system used at my workplace includes a report generation feature. The specifications of this feature are as follows:

• First, when a staff member clicks the "Issue" button, a template file for the specified Excel report is copied to a working folder.
• Next, data registered in the business system is poured into that Excel file.
• Note that this Excel report consists of a hidden sheet for pouring data from the business system and a printing sheet that extracts and formats the poured data using formulas like VLOOKUP. When data is written from the business system, a well-formatted report is created.
• Once the staff member confirms the generated report and clicks the "Register" button, the business system converts this Excel report into a PDF and registers it in another system.

Challenge: Automating Font Size Adjustment to Fit Long Text

However, since the reports included in the document can be very long, staff members must visually check the Excel report after the data is written every time it is issued. If the report text overflows the frame, they must manually adjust the font size before clicking the "Register" button. This task wastes about 1 to 2 hours per day. Therefore, I wanted to automate this and create a system where simply clicking the "Issue" button automatically adjusts the font size so that the report fits within the frame and generates the PDF.

I once consulted the vendor to see if they could specify the font size along with writing the data from the business system to the Excel report. However, their response was that for reliability reasons, they did not want to use Microsoft.Office.Interop.Excel in the business system, and they wouldn't accept such a request even for millions of yen. I certainly thought that made sense.

Constraints: No Macros allowed (.xlsx) / No System Modifications

While the template files for the Excel reports used in the business system can be revised, the following restrictions apply:

• The Excel report must be an Excel file with the .xlsx extension, meaning macros cannot be used.
• Writing from the business system to the Excel report is limited to writing values to a hidden data-entry sheet; it cannot specify fonts for areas on the display sheet.
• Since it is a vendor-made system, I cannot access the PDF generation logic called from within it.

It seemed hopeless, but... we made it happen!!

Normally, you would think it's impossible to automatically change the font size to just the right amount when you can't specify the font for the report area on the printing sheet, can't run macros in the Excel report, and can only pour data into a data-entry sheet. However, by combining various techniques, we were able to achieve it! I will now explain how this was accomplished.

2. Overall System Architecture

First, I will illustrate the overall configuration of the currently operating system.

• Step 1: Export data from the business system to a CSV file.
• Step 2: Read that CSV file with a Review Excel (with macros) to calculate the optimal font size.
• Step 3: Register the data back into the business system via CSV.
• Step 4: The business system pours the values into the Excel report template.
• Step 5: The appearance automatically switches due to a "mechanism" embedded in the Excel report.

Pre-processing (Environment with macros):

Report Issuance (Environment without macros):

Microsoft Excel is a highly sophisticated application, offering features such as:

• Use of formulas in cells
• Advanced built-in functions for string processing, mathematical operations, and arrays
• Automatic word wrap within cells
• Automatic row height adjustment based on the number of lines of text displayed in a cell
• Merging cells vertically and horizontally
• Shape drawing, including text boxes
• Use of formulas within text boxes
• Programming via VBA

On the other hand, these features have limitations, for example:

• Available formulas vary by version
• Merging cells disables automatic height adjustment
• Row height is limited to a maximum of 409.5pt
• Text boxes using formulas have a limit of 255 characters

Therefore, it is necessary to utilize these abundant features while bypassing the various restrictions they possess.

3. Technique 1: Calculation (Aggressive Simulation via VBA): Identifying the "Maximum Font Size" to Break Through Excel's Barriers

When determining the font size on the business system side, the biggest hurdle is "reproducing Excel's rendering results." Specifically, we need to accurately identify the "absolute maximum size that fits within the frame" while clearing the following constraints:

• Poor compatibility between merged cells and AutoFit: Excel VBA's AutoFit (automatic row height adjustment) does not function correctly for report fields where multiple rows and columns are merged on the report side.
• Single row height limit (409.5pt): Very long text can exceed the height limit of a single cell/row (approx. 14.4 cm), making accurate measurement impossible.
• Discrepancy between screen display and printing: The line break positions in the Excel screen display and the PDF output for the print image differ slightly. Therefore, judging something as "just barely fitting" on the screen carries the risk of it overflowing when printed.

Solution: Use a separate macro-enabled Excel and employ various techniques

To solve these problems, I devised and implemented the following methods:

• Use a macro-enabled Excel separate from the report: Add a font size determination feature to the Excel file used during the review stage before issuing the report, which already has various support functions implemented via formulas and macros.
• No merged cells: Use non-merged cells for determining font size (so AutoFit functions correctly).
• Split cells by paragraph: Instead of putting the entire text into a single cell, use many vertically arranged cells and pour the text divided into paragraphs separated by line breaks (to bypass the 409.5pt row height limit).
  • Even in Excel 2019 or earlier, you can use the FILTERXML function to split text into paragraphs across cells using formulas without VBA.
  • For example, to split the text in cell A1 by paragraph and place them in cells B1, B2, B3, etc., the formula for cell B1 would be as follows. (If you autofill this to B2 and B3, the ROW(A1) part changes to ROW(A2) and ROW(A3), returning indices 2 and 3 respectively.)

=IFERROR(FILTERXML("<root><item>" & SUBSTITUTE($A$1, CHAR(10), "</item><item>") & "</item></root>", "//item[" & ROW(A1) & "]"), "")

String splitting using FILTERXML only works in the Windows version of Excel (not available on Mac). This system assumes a Windows operational environment.

• Ensuring a safety margin: Considering the possibility that the line break positions on the screen and during printing may shift slightly, add one character to the end of each paragraph to ensure that if the fit is borderline, it definitely increases by one line.
  • For example, in the previous formula, we replaced line breaks with "</item><item>", but instead, we replace them with "+</item><item>".

=IFERROR(FILTERXML("<root><item>" & SUBSTITUTE($A$1, CHAR(10), "+</item><item>") & "</item></root>", "//item[" & ROW(A1) & "]"), "")

• Addressing width differences with and without column merging: In Excel reports with a "graph paper" style layout, there is horizontal merging. However, the number of characters that fit in a line differs between merged and unmerged columns even if the total width is the same. Therefore, while displaying various texts in the cells, adjust the width of the cells used for font size determination so that the number of characters per line is always the same.
• Reducing size until the height fits: With text in the cells like this, write a VBA process that performs AutoFit on each row, calculates the total height of the cells containing text, and if it exceeds the report frame height, decreases the font size by 1pt, re-performs AutoFit, and recalculates the total height until it fits within the frame.
• Registering the calculated size in the business system: Write a VBA process to output the determined font size as a CSV file so it can be read by the business system.

4. Technique 2: Display (Defensive Layout via .xlsx): Achieving "Automatic Font Size Switching" in Macro-free .xlsx

Even if the optimal font size (e.g., 14pt, 9pt) is identified in the calculation phase, the business system cannot change the font size of the Excel report, nor can the Excel report use macro-enabled .xlsm files. The business system simply pours values into a hidden data-entry sheet. We needed to change the size of the characters displayed within the report frame to the specified value using purely standard Excel features.

Solution: Multi-layered "Linked Picture" Structure to Break Through Hopeless Constraints

Initially, we tried using "Text Boxes" that can link to cell values, but we were blocked by an old Excel specification limit of only 255 characters. Because of this, we constructed a layout structure centered around "Linked Pictures" through the following steps. The reason why using "Linked Pictures" removes the limit is because "Excel treats that area not as a 'text string' but as 'rendered image data (metafile)'."

Why are text boxes not suitable?
When linking a cell to an Excel text box (by specifying =A1 etc. in the formula bar), there is a very old specification limit where only the first 255 characters can be displayed. Since this is fatal for the "long report" requirement, we overcome this barrier with "Linked Pictures."

1. "Font Size-Specific Switching" in Non-Printing Areas

The cell area where the main report text is displayed will switch based on the "Recommended Font Size" value received from the business system. These "backstage cell areas" are placed in a non-printing region.

• Structure of backstage cells: Prepare as many cells (columns) as the number of font sizes you want to display in the report (e.g., for 14pt, 13pt... 8pt).
• Switching formulas: Set a formula in each cell. (Assuming G1 contains the font size and H1 contains the text)
  • P1 (for 14pt): =IF($G$1=14,$H$1,"")
  • P11 (for 13pt): =IF($G$1=13,$H$1,"")
  • P71 (for 8pt): =IF($G$1=8,$H$1,"")
• Font size settings: Set the font size of each backstage cell to its respective size (14pt, 13pt... 8pt).

2. Complete Avoidance of the 409.5pt Limit Using "Vertically Merged Cells" Backstage

Overcoming another hurdle: If the report is very long and the height of a backstage cell exceeds 409.5pt, the text will still be cut off.
Solution: Merge each backstage cell for each font size (e.g., P1) vertically across multiple rows (e.g., P1 to P10) to create one large cell area. You can also merge horizontally at the same time (e.g., P1:T10).
This dramatically expands the total height of the text display area, completely avoiding the single-row height limit.
It is important to strictly match the "column width" of this vertically merged cell range to the width of the report frame.

3. "Multiple Linked Pictures" on the Frontstage Cell Area

Automatic appearance switching: In the text field of the report sheet's print area, place the "vertically merged backstage cell ranges" created in step 2 as "Linked Pictures," layering them for each available font size.
Effect: Text enters only the cell matching the font size provided by the business system (e.g., if $B1=11, text appears in C1), and the appearance of that cell is displayed as a linked picture. Since linked pictures of cells without text have a transparent background, they exist as empty boards, resulting in the appearance of the font size having switched.

Example: Procedure for linking the range P1:T10 as a picture over the range A1:E10:

If you prepare seven font sizes (e.g., 14pt, 13pt... 8pt), you create seven backstage cell areas like P11:T20, P21:T30... P71:T80, set their respective font sizes, and enable "Wrap Text."
The reason for performing the paste operation via the ribbon instead of right-clicking the target area is that right-clicking after the first time will select the already pasted picture, making the "Linked Picture" menu option disappear. To select the target area, instead of clicking it directly, select an adjacent cell first, then move the selection to the target area using the arrow keys. Then, perform the "Linked Picture" operation from the ribbon. It is recommended to set the Zoom to 100% in the View ribbon during this task.

4. Solving the Border Issue during PDF Conversion: Uncheck View > Gridlines

During the PoC (Proof of Concept) process, we encountered an issue where thin gray borders appeared on the PDF when using Linked Pictures. This was resolved by unchecking "Gridlines" in the "View" tab of the sheet containing the backstage cell areas, which removed the border information from the rendered pictures.

This architecture of "Calculation (VBA) → CSV Integration → Display (Linked Picture)" is the "ultimate secret technique" for achieving automated reports compatible with long text under hopeless constraints.

5. Operational Ingenuity: A Hack to Avoid "Forcing" Data Input

The task of calculating the optimal font size and registering it in the business system is essential for the report issuer, but it is not a required task for the reviewer. Consequently, it is easily overlooked if left as is.

Therefore, I added a feature to the macro-enabled Excel used for reviews that automatically generates "review records"—a benefit for the reviewers themselves. By bundling the optimal font size with these review records into the CSV output, we ensured that the optimal font size is registered in the business system without being forgotten.

6. Conclusion

By introducing this method, we achieved a reduction of one to two hours of work per day and the elimination of human errors.
While many business systems are difficult to replace or modify, simply combining the standard features of Excel—a mature technology—can sometimes provide much more flexibility than the latest systems built with significant investment.
I believe there are many business processes that rely on Excel reports, and I hope the techniques summarized in this article will be useful in such situations.