Merge cells optically, but not functionally.
Merging cells has the drawback, that it cuts the functionality of the excel file.
So why not implementing a functionality, that merges cells ONLY OPTICALLY, so that the cells still keep their value and thus the full functionality, but that excel recognizes, when cells in rows that are directly located under the each other, contain the same values and thus just merges the cells optically, but does not merge the values, so that the user sees the merged cells on his monitor, and is able to print the table with merged cells, but still can e.g. sort the rows in the table because each cell still functionally contains its value.
I'd like to have a VERTICAL "center across selection" alignment option.
Sorry, didn't word the middle part as clearly as I'd've liked. The original material would never be merged away, never cease to exist or be moved or... anything. It would all stay right where it was to begin with.
It would simply display as one big, merged-looking cell.
@Chris: I believe he is looking for something up/down, not left/right. The part about rows directly under each other seems key.
So what it looks like he'd like is, if A1, A2, A3, A4, A5, A6, and A7 all had the same value, the seven individual cells would NOT show as seven individual cells, but rather it would be like you merged the whole range, A1:A7, and set the formatting to center up and down, for sure, and maybe left and right (but up and down, for sure) as if it were one single merged cell even though all the other values would still be present, editable, sortable on, and useable in formulas elsewhere (because merging like that now would wipe A2:A7 out and you'd have to be a heroic formula writer to deal with a column of, say, a thousand rows doing three here, nine there, seven in this spot, and so on.
So merging across the selection is definitely NOT what he needs. It would be more like my thought below about allowing the merge across selection to be applied up/down as well as the current left/right.
There is a way to merge them optically, but not where you can pull the same number in each cell that it covers. I apologize if this is an obvious answer, but if you select the cells you want to "merge" optically, under the home tab go to Alignment and click on the bottom right arrow for more options, under "Horizontal:" click the drop down box and select "Center Across Section". This will center this optically.
If the centering (display only) ACTED like present merging in that ONLY the value in the cell in the "upper left corner" of the range would then be centered across the range, hiding all contents in the other cells in the range, this would be different than the linked request in one way.
To explain via example:
Cells A1, B1, C1 have 3, 4, 6 as their respective contents. Merging them wipes out completely the contents of B1 and C1. Gone. This could cover their display by showing only A1's content of 3 with B1's and C1's contents hidden, but still accessible in a variety of ways, not the least being functions that need their information.
So the "3" from A1 is displayed overtop all three cells, but all three contents are still present.
Another way in which this could differ from the linked request is that the linked request ONLY ONLY ONLY goes for centering across the selection. To be sure, the high percentage of times one would wish to use any of these features, it would be to accomplish precisely that so, yay.
However, what if you wish to have the value right "indented"? That is, displayed at the right of the three cells, with the other two not displayed at all? That is not an option pictured by the linked request because it is very much aimed at not having to merge in order to center across cells. Again, very high percentage. But with billions of spreadsheets...
And further, THIS could operate on a 2-D range, say A1:C4, while the current center across selection option will take that range and make each ROW in it do it, but will not make that a single block displaying the "upper left corner's" contents centered up/down, left/right.
(I know that is an argument for NOT doing away with merging, in the context of the link's request, but putting this request's idea into play and either doing this, or improving the link's requested features would make it applicable.)
I DO basically hate merged cells, especially into exported results like PDF to Excel conversions, but centering across a selection alone and only if the selection has no contents other than the "upper left corner" cell's contents is too restrictive for any improvement made since these are once in a lifetime things.
It could be of interest as well if accessible to the Conditional Formatting engine, which by the way is all about not affecting underlying formats, only changing how they are displayed. Makes CF sourced changes missed by VBA for many macro writers checking formatting... Anyhow, if, for example, CF could center the highest value, say, of three next-to-each-other cells over their display space (so, selecting the highest value of the three and showing it only, centered, at the right, whatever, of the cell), that would be a straightforward, not workaround-y way of achieving that desire.
Finally, I get all kinds of cr*p from customers and vendors alike in which their precious display concepts and font sizes and so on make, I guess, pretty documents, but I have needs and rights too. And just care about what is shown in my usage, not all their garbage. I have the original on the hard drive for that. But if I edit the stupid spreadsheets they send, they've used merging and right indenting and blah-blah-blah to make it look pretty to them and I have to do all the de-merging and moving of cells and clearing of columns for deletion, before I can make it suitable. If they had this, a LOT of that would go away.
So yeah, it's different, though obviously built on the same unhappiness with Merge. It's actually broader and more fully useable, if implemented by Excel according to its spirit.
I might mention that it might, or might not, have implications with the new "Spill" functionality that is supposed to begin infusing great tranches of formulas.
Looks very similar to me.
No, sorry, but I do not see any connection between this suggestion and the one from your link.
which already has 300 votes at the time of pasting?