- Wednesday
Handling Different Column Structures in Excel
- Neale Blackwood
- Dynamic Arrays, Formulas, Data
- 0 comments
One thing you learn in Excel is that there are so many ways to display the same data. When working with Actual / Budget / Variance one structure can be a challenge to work with. See how the FILTER function can simplify it.
Sign up to hear about free Excel training.
I won't share your email with anyone.
1 min read
The FILTER function typically filters rows, but it can also filter columns.
The image below has a challenging structure.
If you need to extract values from this layout there are a few options, but the simplest is the FILTER function.
In the image below there is a FILTER function in cell P3 that extracts all the Actuals.
Changing cell P2 then allows extraction of the Budget – image below.
The formula in cell P3 is.
=FILTER(B3:L5,B2:L2=P2)Because the criteria range is across a row it filters out the columns.
If wanted to total the rows you can tweak the formula as below.
The amended formula is.
=BYROW(FILTER(B3:L5,B2:L2=P2),SUM)And if want both.
The revised formula is.
=LET(amt,FILTER(B3:L5,B2:L2=P2),HSTACK(amt,BYROW(amt,SUM)))As you can see FILTER offers solutions to different column layouts in Excel.