Excel Custom Cell "Merge" or "Split"

Question

I am trying to recreate the following table in excel:

But I am struggling to split the cells into three parts.. any solutions?

Answer 1

But I am struggling to split the cells into three parts.. any solutions?

Start with 12 columns for categories (14 columns in total). Then, ignoring the first 2 columns:

1. In rows 1, 5 and 6 merge sets of 3 cells into 1 cell - leaves 4 cells.

2. In rows 2 and 3 merge set of 4 cells into 1 cell - leaves 3 cells.

3. In row 4 merge sets of 6 cells into 1 cell - leaves 2 cells.

Answer 2

You can use just six (6) columns for the Categories region (so 8 total with the leftmost two columns).

For descriptive purposes, I'll pretend the 4-cell rows are "3" units wide. First column is then 3 units wide, second is 1 unit wide, third is 2 units, fourth is 2 units, fifth is 1 unit, sixth is 3 units.

Then make your cells out of those columns by combining them in appropriate groups from the above: 2 cells are 12/2 = 6 units wide (3+1+2), 3 cells are 12/3 = 4 units wide (3+1, 2+2, 1+3), and 4 cells are 12/4 = 3 units wide (3, 1+2, 2+1, 3).

Remember though that Excel can be funny-ish with heights and widths if not in Page Layout view. That's for starters. But... a column 8 wide and a column 6 wide are often not precisely as wide as a single column 14 wide. Actually, that varies by version. You have to shoot for the obviously correct, then adjust wee amounts here and there. If not in PL view, remember that changing fonts has an effect on this. Subtle ones, like the actual widths Excel will save regardless of what you typed. (Sigh...) Oh, and then your printer driver...

Otherwise, Mr. Postill is showing you the only way to do it wholly within Excel.

Practice it, and think about it theoretically, if you are tasked to recreate a lot of forms, especially govenment forms. Banks and governments like to think they are getting their originals, and annoyingly often refuse forms they realize you created.

By the way, rows can have this done to them as well.

In my experience, I have found you do NOT want to merge any cells until you have all the row heights and column widths right. You can test that kind of thing with standard cell bordering that simulates the merged cells, and with appropriate font text for seeing if lines of text line up. Print a sheet, place it over the original, hold them up to a light and see if things fit exactly enough over each other.

If building your own, but needing this, it's easier because you don't have to do the lining up.

Then do your merging and populate your cells with text and formulas.

"Wholly within Excel"... You can build the table outside of Excel, in Word for example, which is a program with MUCH more flexibility in creating things like this. You can even populate the Word table from Excel, and plunk it into Excel (make a suitable empty region to plunk n into if you will have live Excel material on the same page) so it's dynamically linked in Word to the underlying data that populates it and then dynamically linked into Excel in what seems like some "uncle-daddy" situation, but hey...