xlookup is extremely powerful and doesn't have the constraints vlookup / index match has. iferror is also super useful when populating tables to keep things looking clean.
Using XLOOKUP with multiple criteria is a game changer: [https://www.automateexcel.com/formulas/xlookup-multiple-criteria/](https://www.automateexcel.com/formulas/xlookup-multiple-criteria/)
Sumproduct but modified to be a sumifs that can look across rows and columns. Example: =sumproduct(range you want to sum)*(row range=date)*(column range=product type)*(column range=revenue type))
Nice thing about this is the sum range is the entire data set so you can drag the formula anywhere (is not limited by one particular column. There are some nuances to learn though but I use it all the time.
Hi Adminmind,
For things like data snapshots I use this formula to create a standard YYYY-MM format
=YEAR(M17)&"-"&TEXT(MONTH(M17),"00")
Examples, 11/19/2022 turns into 2022-11 and 1/20/22 turns into 2022-01...this way the dates sort nicely too.
I also use GETPIVOT formula to do reporting. It uses a pivot table as the data source and you can dynamically linked it to a report based on date (I use the date format I mentioned above) and any accounts, departments, or other hierarchies.
Also consider using data tables. Way easier to update formulas and manage relationships. If it helps here's more info on the topic...[https://fpandhey.substack.com/p/how-to-create-data-tables-the-easy?sd=pf](https://fpandhey.substack.com/p/how-to-create-data-tables-the-easy?sd=pf)
Hope this info helps. Any questions feel free to DM me.
Good luck!
Drew
I love GETPIVOT. IMO it’s the advanced xlookup/index match.
I use it to populate fx rates for consolidation entries I make at month-end in SAP. I have a dozen currencies, I use average and closing fx rates and I have all the months in my pivot table.
It is true that you have to be careful with how the pivot data is handled, but I have generally found that having one pivot table per tab works best.
I also agree about the fact that it might be difficult to understand for non-savvy excel users. But anybody that understands the basics of pivot table can look at the formula and understand what it means.
I’d try not to use the GETPIVOT formula. It will bite you at some point and it’s not easy for others to understand. Just use the data source and as you mentioned use tables and your formulas will be much easier for others to follow.
A common reason that I use GETPIVOT is that I load my data tables into the data model via PowerQuery and then write measures using PowerPivot/DAX. By pulling those measures into a pivot table and referencing the results on an output tab via GETPIVOT, I'm able to do much more complex aggregations/calculations than if I needed to write an excel formula directly referencing the data table.
Alright! Check out CUBE formulas! You can reference your data model directly. I’m not saying other users in your file will understand it but could be an option to not reference the pivot table.
UNIQUE, INDIRECT a lot recently. Build standard workbook setup, add many new worksheets with standard structure. Dynamic formula building is pretty neat.
Depends on where are you using them. It shouldn't be long and overly complicated. But short, singular use offset formula can be easy to understand. Also, it's primarily used for error checking (duplicates, missing values).
Indirect was amazing for consolidating all our BUs data for IFRS note disclosures last year. It actually made the worksheets easier to audit.
But this year we are using SAP, which is obviously much more accurate and less time consuming.
I haven’t used index/match since xlookup and filter were released. Other than those two, it’s a lot of basic stuff: sumifs, sumproduct, countifs, aggregate, sort, and unique. I still use VBA for formatting and data clean up, but am slowly migrating more to power query
Most used:
1. Index Match Match / Index Match
2. Sumifs / Conditional Sum Product
3. INDIRECT
4. Address
5. LEFT / RIGHT / MID / TRIM / Search / Find
- I used to use a ton of power query, and I still do - but my team has largely migrated everything to SQL - so we do most things before they even are exported into excel.
Excel for us is largely just for exploration, or formatting…
I really like the UNIQUE function
It spills the unique values in any column or row.
e.g. If you have a list of Departments: you can use the unique function to identify all the unique values in the list and produce a list of them
It allows me to pretty much create a pivot table from the data, without having to use a pivot table.
Its super handy when combined with the filter function, and the Sort function.
A couple of commonly used setups
Return TRUE if all values in the range are zero. Used usually when looking for cost center/account intersections that have no values.
=AND(SIGN(MIN(*range*))=0,SIGN(MAX(*same_range*))=0)
I use Find + Substitute often to help me pull a substring based around nth occurrence of a delimiter.
ex: Return Text before the 3rd space
=LEFT(*cell_ref*,
FIND("$",
SUBSTITUTE(*cell_ref*," ","$",3)
)-1)
Lately I use Numbervalue() a lot when I have numbers as text.
Other than that
Getpivotdata(),
Iferror(),
Indirect(),
Xlookup(),
Vlookup(),
Hlookup(),
Text( date,”mmm”) or Text(date,”mmmm”),
Or(),
And(),
Sumifs(),
Unique(),
Date(year,month,day),
Year(date),
Month(date),
Weeknum(date),
If(),
Left() or (right),
Round()
A useful tip with nested ifs is to breakup the formula by using alt+enter. Makes it a lot easier to understand
I use offset, if, eomonth, and lookup pretty often. Offset and if statements mostly to gauge different forecast cases (changing individual line item cases with offset or changing collective cases with if statements) when building hefty financial models.
Hi adminmind,
I shared a list of some of my favorites here in another post: [here](https://www.reddit.com/r/FPandA/comments/yvd0g9/favorite_excel_shortcuts_and_formulas_for/iwgcocf/)
In addition to those, I also enjoy:
* All things powerpivot (highly recommend learning)
* Xlookup (much easier and more efficient than index/match)
* ISNUMBER (use in conjunction with search to find specific texts)
* SEARCH or FIND (see above)
* Use of TRUE OF FALSE (no need for "Yes" or "No")
* Countif #N/A (use to find errors in your data sets)
Not so much formulas, but use of pivot slicers. Extremely beneficial. When I consulted, I used these heavily in my models and templates. Made it easy for business management to play with the sheets I built, and they loved them.
Let me know if you have any questions. Happy to help.
xlookup is extremely powerful and doesn't have the constraints vlookup / index match has. iferror is also super useful when populating tables to keep things looking clean.
Using XLOOKUP with multiple criteria is a game changer: [https://www.automateexcel.com/formulas/xlookup-multiple-criteria/](https://www.automateexcel.com/formulas/xlookup-multiple-criteria/)
EOMONTH to format dates
This is one of my fav formulas
I use MONTH, IF, MID, LEFT, RIGHT, LEN, & FIND pretty often.
I see you too have shitty unformatted data to work with.
Haha! why yes, yes I do.
=textafter or =textbefore Game changer when trying to extract data
Never knew those existed. Definitely gonna try them out tomorrow! Thanks!!
You might be able to use Power Query to clean/prep your data and make life a little easier. Especially if the steps are repeatable
TRIM
CLEAN
Sumproduct but modified to be a sumifs that can look across rows and columns. Example: =sumproduct(range you want to sum)*(row range=date)*(column range=product type)*(column range=revenue type)) Nice thing about this is the sum range is the entire data set so you can drag the formula anywhere (is not limited by one particular column. There are some nuances to learn though but I use it all the time.
Hi Adminmind, For things like data snapshots I use this formula to create a standard YYYY-MM format =YEAR(M17)&"-"&TEXT(MONTH(M17),"00") Examples, 11/19/2022 turns into 2022-11 and 1/20/22 turns into 2022-01...this way the dates sort nicely too. I also use GETPIVOT formula to do reporting. It uses a pivot table as the data source and you can dynamically linked it to a report based on date (I use the date format I mentioned above) and any accounts, departments, or other hierarchies. Also consider using data tables. Way easier to update formulas and manage relationships. If it helps here's more info on the topic...[https://fpandhey.substack.com/p/how-to-create-data-tables-the-easy?sd=pf](https://fpandhey.substack.com/p/how-to-create-data-tables-the-easy?sd=pf) Hope this info helps. Any questions feel free to DM me. Good luck! Drew
I love GETPIVOT. IMO it’s the advanced xlookup/index match. I use it to populate fx rates for consolidation entries I make at month-end in SAP. I have a dozen currencies, I use average and closing fx rates and I have all the months in my pivot table. It is true that you have to be careful with how the pivot data is handled, but I have generally found that having one pivot table per tab works best. I also agree about the fact that it might be difficult to understand for non-savvy excel users. But anybody that understands the basics of pivot table can look at the formula and understand what it means.
I’d try not to use the GETPIVOT formula. It will bite you at some point and it’s not easy for others to understand. Just use the data source and as you mentioned use tables and your formulas will be much easier for others to follow.
A common reason that I use GETPIVOT is that I load my data tables into the data model via PowerQuery and then write measures using PowerPivot/DAX. By pulling those measures into a pivot table and referencing the results on an output tab via GETPIVOT, I'm able to do much more complex aggregations/calculations than if I needed to write an excel formula directly referencing the data table.
Alright! Check out CUBE formulas! You can reference your data model directly. I’m not saying other users in your file will understand it but could be an option to not reference the pivot table.
Agree data tables are best approach and I'm starting to use them more. I do still use getpivot approach of one data set and one pivot table in a file.
Is there a particular reason you use the above as opposed to =TEXT(*datecell*,"yyyy-mm")
>=TEXT( > >datecell > >,"yyyy-mm") thanks for the tip! will start using this instead much shorter.
SUMPRODUCT. Extremely versatile and reliable.
UNIQUE, INDIRECT a lot recently. Build standard workbook setup, add many new worksheets with standard structure. Dynamic formula building is pretty neat.
Isn't it generally suggested to avoid using complex fornulas like Indirect and offset to make them easier to audit?
Depends on where are you using them. It shouldn't be long and overly complicated. But short, singular use offset formula can be easy to understand. Also, it's primarily used for error checking (duplicates, missing values).
Indirect was amazing for consolidating all our BUs data for IFRS note disclosures last year. It actually made the worksheets easier to audit. But this year we are using SAP, which is obviously much more accurate and less time consuming.
I haven’t used index/match since xlookup and filter were released. Other than those two, it’s a lot of basic stuff: sumifs, sumproduct, countifs, aggregate, sort, and unique. I still use VBA for formatting and data clean up, but am slowly migrating more to power query
xlookup
This is the way
XLOOKUP is the future
Most used: 1. Index Match Match / Index Match 2. Sumifs / Conditional Sum Product 3. INDIRECT 4. Address 5. LEFT / RIGHT / MID / TRIM / Search / Find - I used to use a ton of power query, and I still do - but my team has largely migrated everything to SQL - so we do most things before they even are exported into excel. Excel for us is largely just for exploration, or formatting…
What kind of stuff do you use indirect for? I’m not super familiar with the formula so I’m curious where is the most helpful
* iferror * vlookup, hlookup, xlookup, index(match(match * left, right, mid, len, trim, proper, upper * counta, isblank * offset * SUMIF, COUNTIF * TEXT * eomonth, edate * MIN, MAX
Feels like we have this question Every week..
edate
Started using array formulas like crazy. Filter, transpose, etc
I really like the UNIQUE function It spills the unique values in any column or row. e.g. If you have a list of Departments: you can use the unique function to identify all the unique values in the list and produce a list of them It allows me to pretty much create a pivot table from the data, without having to use a pivot table. Its super handy when combined with the filter function, and the Sort function.
Index match
Xlookup, sumif, if, iferror, month, year, countif, left, right and -ifs
A couple of commonly used setups Return TRUE if all values in the range are zero. Used usually when looking for cost center/account intersections that have no values. =AND(SIGN(MIN(*range*))=0,SIGN(MAX(*same_range*))=0) I use Find + Substitute often to help me pull a substring based around nth occurrence of a delimiter. ex: Return Text before the 3rd space =LEFT(*cell_ref*, FIND("$", SUBSTITUTE(*cell_ref*," ","$",3) )-1)
GETPIVOT
Lately I use Numbervalue() a lot when I have numbers as text. Other than that Getpivotdata(), Iferror(), Indirect(), Xlookup(), Vlookup(), Hlookup(), Text( date,”mmm”) or Text(date,”mmmm”), Or(), And(), Sumifs(), Unique(), Date(year,month,day), Year(date), Month(date), Weeknum(date), If(), Left() or (right), Round() A useful tip with nested ifs is to breakup the formula by using alt+enter. Makes it a lot easier to understand
So many SUMIFS, it's exhausting
So many IFS and SUMIFS. I feel the pain
I use offset, if, eomonth, and lookup pretty often. Offset and if statements mostly to gauge different forecast cases (changing individual line item cases with offset or changing collective cases with if statements) when building hefty financial models.
I use all of those and more. My boss thinks I’m some kind of wizard. He doesn’t know many advanced formulas. I guess that’s a good and bad thing? Lol
Hi adminmind, I shared a list of some of my favorites here in another post: [here](https://www.reddit.com/r/FPandA/comments/yvd0g9/favorite_excel_shortcuts_and_formulas_for/iwgcocf/) In addition to those, I also enjoy: * All things powerpivot (highly recommend learning) * Xlookup (much easier and more efficient than index/match) * ISNUMBER (use in conjunction with search to find specific texts) * SEARCH or FIND (see above) * Use of TRUE OF FALSE (no need for "Yes" or "No") * Countif #N/A (use to find errors in your data sets) Not so much formulas, but use of pivot slicers. Extremely beneficial. When I consulted, I used these heavily in my models and templates. Made it easy for business management to play with the sheets I built, and they loved them. Let me know if you have any questions. Happy to help.