Fill cells right-to-left or bottom-to-top with Google Sheets formulas

Formulas like split(...) fill cells left-to-right in Google sheets. Using transpose(...) I can fill top-to-bottom. Is it possible to fill cells "the other way round", i.e. right-to-left or bottom-to-top?

Here is how it looks like in Google Sheets with formulas

or just with the result of the calculation.

What I am looking for is a formula in the selected cell (content "a"), which fills upwards.

The reason behind this is the following. I have cell with a query

=query(importrange(named_range, "A:E"), "select Col1, count(Col2) group by Col3 pivot Col4, Col5")

This returns a table with the pivot labels Col4(year) and Col5(YES/NO) separated by a comma along the row:

I wanted to put a formula in the row above (the red cells) that separates the pivot labels in the cells just above, independent of how many pivot levels there are.

Array formulas in cells spilling rightwards and/or downwards is part of the fundamental 'grammar' of spreadsheets, and it's not possible to override this behaviour (with the one proviso that formulas in spreadsheets in a right-to-left language will spill leftwards and downwards for obvious reasons).

Can you give some more details on what you are trying to achieve?

EDIT based on additional details:
So what you can do here is to use LET to put your QUERY within a named variable, use CHOOSEROWS to extract the header from that name, prettify the header in the manner you're looking to do with TRANSPOSE/SPLIT/TRANSPOSE, then finally use an array literal to stick the pretty headers on top of the original QUERY:

=arrayformula(let(
queryresult,*YOUR QUERY*,
queryheaderrow,chooserows(queryresult,1),
prettyheader,transpose(split(transpose(queryheaderrow),",")),
{prettyheader;queryresult}))

This will work for any depth of pivot, but the order of the rows in the pretty header is based on the order you've specified in the QUERY. So to get YES/NO above 2022/2023 as you've specified in your example you might need to try pivot Col5,Col4 within the select string rather than pivot Col4,Col5 as you have done.

You can use:

=LET(ζ,TOCOL(SPLIT(A1,",")),SORT(ζ,SEQUENCE(ROWS(ζ)),))