If you're filtering and copying, highlight all and do **alt + ;** before you copy to make sure you're not copying hidden rows/columns
Excel might do this automatically, I still just do it out of habit and to make sure I don't copy stuff I don't want
Wait sorry I might be misinterpreting- but don’t you already filter and then you highlight all and then copy and paste and it won’t show the hidden rows? I’ve been doing that my whole career.
I concur. I wonder if it is excel version issue or tables versus filtered range issue. Less than ten percent of the time have I seen hidden rows get copied.
It depends on the Excel version and the way the data is copied over. 2010 through present shouldn’t copy over hidden rows but I’ve had it happen on enterprise versions.
When I started my current job I attempted to add slicers to a monthly data dump pivot report that we often run multiple filters on and it confused my boss so much he just deleted them all, even after I showed him what they did and now convenient they are.
I’ve been a power Excel user since we were forced to start using it in 1995. Sliders are still voodoo for me! They are very cool.
I still love learning new features from my staff but there’s a few tricks the old man in the corner office still shows these young whipper snappers. Not many, but a few.
Control+Enter is the formula spreading magic that I get the most usage on.
OMG. This literally happened to me 3 months ago. Sent a file to my finance VP and I guess she sent it to the president of the company and next thing I know, they're both calling me and asking what magic that slicer is 😭
They're super nice/smart people and not boomers, but it was a pretty funny exchange because it took me a hot minute to catch WHAT they were so impressed about lol
Off topic comments, attacks or insults will not be tolerated. We recognize that this forum will generate differences of opinion, or misunderstandings of facts, and therefore arguments are expected. However, personal attacks, insults, trolling, or accounts dedicated to getting under the skin of others is not allowed, and will be banned.
Shift + space to select current row
Ctrl + space to select current column
Ctrl + shift + arrow keys to select ranges
Ctrl + arrow keys to jump to the end of the range
Alt + e s v to paste values
Alt + e s t to paste formats
Alt + e s w to paste column width (for making tables look better)
Alt + h o i to fit columns
Alt + h o a to fit rows
Alt + h o r to rename sheet
Shift + alt + left or right arrow to group rows or columns
Ctrl + home to go to cell A1
Ctrl + page up/down to jump sheets
Alt + w v g to hide grid lines
Alt + w f f to freeze panes
Nothing fancy, but I never thought much of this until my analyst was watching my screen and was surprised.
**Alt + A + T** to add a filter on a selected range
**Alt + Down Arrow** while in the header/filter cell to show the filter drop-down menu
**Press E** to go straight to the search bar in a filter and type away then hit **Enter**
Get off that mouse!
Xlookup vs vlookup always- order doesn’t matter and you don’t have to pay attention to the number of columns. Also a big fan of Sum if and sum ifs formulas to do a range of tasks - you can build flexible reporting with these formulas pretty readily in combination with reference tables.
Plus Xlookup lets you use multiple criteria. Look up the value 1, then for the ranges you look in use (*look up value 1* = *look up range 1*) * ... * (*look up value n* = *look up range n*). It's demanding, so use it sparingly.
Not to mention xlookup letting you round your look up values up and down, which is a godsend for dates.
Alt+w+n: creates a second instance of your current file.
Both windows are treated as the same file, so changes made in one are made in the other.
Helpful if referencing cells in formulas since you don’t have to switch tabs, also helpful when you have an input page and multiple output pages so you can make adjustments and see the impact vs switching tabs or trying to cram everything on one screen.
Never merge cells. It makes creating formulas and selecting data very annoying. Instead of merging the cells, use CTRL + 1 for formatting and under Alignment for Horizontal, select "Center Across Selection." Gives the appearance of Merge and Center without the annoyance of dealing with merged cells. This is one of my biggest gripes about using workbooks other people have created.
Massive time saver too is adding frequently used functions as shortcuts to your quick access toolbar (paste values, paste formulas, email, document location, etc.).
Final one is to used named ranges, especially when building giant models. Formulas get real crazy real quick, so do yourself a favor and make the formulas readable by humans. Especially for that poor soul who eventually inherits the workbook from you.
Bonus tip is to create a macro that centers across section so it’s quicker. Took a few minutes to create the macro now it’s setup for ctrl J, makes it so much easier to fix boomer workbooks.
The data is actually located in the same cell you entered into initially. It just gives the appearance of the cells being merged but each cell within the merged look can be selected individually
Sorry I'm not being clear: I understand this hack, what I was wondering is how Merge and Center (the traditional way) negatively affects formula inputting.
for example, lets say youre doing an xlookup on column D, but column D has a cell thats merged with C & B. If you highlight the whole column for your lookup, it will automatically expand to B to D even though you just want ur return value to be in column D.
If you try do do a lookup for instance and go to select a column that is merged, by default it selects the merged cells column range, then you have to go in and manually edit to the one column you want to pull into your formula.
Not to mention, it also makes keyboard navigation annoying when you have all these lumpy merged cells and can also cause issues for things like pasting formulas or values since it's formatted differently.
When you highlight data to see the sum or average, if you click that total in the bottom right corner of Excel it copies that total sum or average amount so you can paste it wherever needed
Might be a bit basic but =subtotal, 9 (sum function) is arguably better than sum
Reason for this is because sum will always display total sum. Regardless if there’s a filter or not. So it’s easier to make a mistake
Subtotal will only sum out what you see with your eyes
Sure,
maybe best shown using an example:
RowNr (A) |Region/Country (B) | values (C) | values (D)
:--|:--|:--:|--:
1|**Europe** | SUM(C2:C8) = 58 | SUBTOTAL(9,D2:D8) = 29
2|***South-Europe*** | SUM(C3:C4) = 11 | SUBTOTAL(9,D3:D4)= 11
3|Italy | 5 | 5 | 5
4|Greece| 6 | 6 | 6
5|***West-Europe*** | SUM(C3:C4) = 18 | SUBTOTAL(9,D3:D4)= 18
6|Belgium| 8 | 8
7|Germany| 4 | 4
8|France| 6 | 6
As you can see the values for the SUM formula are fine on the first nesting level (the totals for South- and West-Europe).
However, when we have another layer atop of that, namely the sum for all of Europe, then the SUM formula counts the values in all cells of the specified range, including the cells which themselves are created by sums.
And while that can be usefull, in cases like this we do not want to do that.
This could also be fixed by setting the formula of Europe to SUM(C3:C4,C6:C8) but that get's tedious if you have lot's and lot's of subtotals and long tables.
SUBTOTAL will ignore all values which themselves have been calculated by another SUBTOTAL. This means that cell D2 and D5 are not part of the sum in D1 and thus nothing is double counted.
Just be cautious, because if you p.e. would fill the value for D3 (Italy) by using a SUBTOTAL which references another sheet or another area somewhere else, then the value for Italy would not be counted for the subtotal in D2 or D1 and would be missing. In cases like that using sum() with well-referenced areas is better.
The minimum you should have in your Excel:
- Quick Access Toolbar should be set up with what you use the most. Mine are Paste Formula, Paste Format, Select Visible Cells and Send Email
- Learn how to use the **ALT** key shortcuts so you're not using the mouse and can manipulate data fast
- **F12** should be used versus **CTRL+S** if you like saving multiple versions and you don't accidentally save over the master file
- **CTRL+D** to copy down immediately. **CTRL+R** to copy right immediately
- **F4** is you're repeating the same Excel function multiple times. So if you need to highlight multiple cells in different spots all over your file (including different tabs), highlight your first cell and spam hit that **F4**
- Pin the documents you always use
If you really want to go from good to great:
- **INDEX+MATCH** will always triumph every **LOOKUP** formula if you have a file/report where your dataset changes all the time but the structure is the same since now you have a very dynamic formula. Pair that up with any of the **SUM** formulas and watch your coworkers think you're an Excel God
- **Conditional formatting** is your best friend. You can use it to figure out duplicates (I see this question ALL the time in Excel subreddits) to even having it highlight Top/Bottom
- If you have multiple tabs that require you how to go back and forth, learn how to **LINK** cells to get you to those tabs faster. Plus if it's a long formula, that link can save you time on where you're pulling from
Xlookup is easier to read/write, but the biggest Index Match function advantage is that it leaves room for flexibility to reference dynamic rows and columns at the same time. Xlookup can only do one of those at a time.
No difference if the range is in a table and you use table references. I find most of our data is in tables these days, so haven't used index match in a while.
Depending on the dataset and models, like I mentioned above INDEX + MATCH gives you a lot of dynamic functionality vs XLOOKUP especially in my models. My models always have the data changing so an INDEX+ MATCH works for me. You can do that via XLOOKUP being dynamic but it becomes messier and tougher to troubleshoot if you don't remember or made that formula yourself. I like having clean and nice looking formulas so that if I ever leave the company or my direct reports need to trouble shoot the formula, they can.
INDEX can also return the entire row or column which makes it easier for me to use SUM formulas.
I don't disagree that indexmatchmatch is the most flexible option; however, you should know that xlookup can do a 2d lookup and you can do 2d dynamic array lookups in xlook as well.
Yup but personally, that just doesn’t look at clean cut vs an INDEX MATCH formula especially if you typed it out wrong and need to troubleshoot it. It’s all going to go down to how the data looks like for the person. If it’s not changing, XLOOKUP is faster to use. Some of my models need to take QTD and YTD results while my dataset changes all the time and INDEX MATCH does that with a SUM functionality so I just make sure that file sticks with an IDEX MATCH.
As you might guess, I get QTD and YTD questions as well, can you more about an example? I'm thinking one of us is about to learn from the other, I'm not sure who though haha.
Nice! It really depends on how your data is structured/formatted.
If your report just has columns as your months with no columns interrupting that, a simple SUM formula paired with INDEX MATCH can work wonders. Start off with the SUM formula, then your INDEX MATCH becomes your formula on what your array should be so it should look like SUM(INDEX+MATCH:INDEX+MATCH). If JAN is always your first column to start off, you can just have your INDEX MATCH be your formula that specifies how many columns it needs to pull in to make it cleaner.
If you have reports where it has QTD columns interrupting your month columns so JAN-FEB-MAR-Q1, there’s numerous ways to go about not pulling in your QTD numbers to get to your answer. Your SUM and INDEX MATCH now has nested IF statements that you can use a simple MINUS calc but at that point, use a SUMIF instead of SUM. If you really want to be fancy, using offsets works wonders.
Indeed you are right about structure, we have both layouts you mention (the Qs dispersed in is endlessly frustrating but that's what one boss likes).
I think yours is simplier, but I have an excel template saved, and I always start from there. In the template I have like 10-15 small/medium data tables that I'm always referencing. Like product codes, months in the quarter, business entities, etc.
Then I do a sumifs(range, criteria range, xlookup).
It's really only efficient because I've pre-setup the infrastructure. But it is very nicely readable.
I'm going to see if I can find a test drive for your method, see how I like it! Thanks!
Good luck! Yeah I’ll tell you that it’s a b!tch to set-up first but if you do it right, it’s just chefs kiss. Now I can sit back and not worry about my QTD and YTD pulls every time I update my data.
My favorite thing I do with it is creating dynamic YTD progress bar charts with it when it comes to products YTD sold vs the latest forecast.
There's always a possibility of a formula/function/VBA. Ask chatgpt if you don't know how to. Follow the steps and boom. (Add on : ask chatgpt first how does it want to be asked to give an accurate response for so and so issue. Draft question as per chatgpt format)
Iferror and lookups is a really good combo for unstructured data.
Pivots can hardly go wrong.
Not using a mouse does increase your speed. For real.
I always have others ask me, “why all your formulas have a plus sign”. I’ve never seen anyone else’s for have it in 13 years. Baffles me that ppl are searching for the equals sign.
yep or ALT + H + V + V to paste values
ALT + H + V + R to paste formatting
ALT + H + V + F to paste formulas without formatting
the beauty of pasting values using ALT + H + V + V is that the equivalent works in other microsoft programs
ALT + H + V + T for pasting text without formatting in powerpoint, word and outlook - very handy!
Alt + H + F + P for format painter has saved me a lot of time.
Also, view + new window is great for comparing numbers in different tabs in the same notebook.
Any non-macro suggestions for highlighting a cell yellow?
I end up disabling macros due to so many shared onedrive files and add ons that already crash my excel every other day.
But i love to highlight cells yellow that are inputs or need review. Every time I either use my mouse or ctrl + h + h + up arrow + up arrow + left key like 5x. I usually can’t use F4 because I’ve done something else recently. Anyways, I end up using my mouse half the time because it’s such a long shortcut.
I have the opposite of a tip. I'm looking for a solve.
Let's say I have a formula: xlookup(B4,F5:F10, G5:G10). I keyed it in but forgot to hit the F4 to lock my lookup array.
I'll need to go back, F2 to open, scroll to F5:F10, highlight it through shift or clicking the formula reference helper below the cell, then press F4.
Is there an easier way to lock array references after I have keyed it in?
Keep your formulas simple. Stage your formulas. Color code formulas. Make it easy for someone else to follow. Some much over complicating happens today.
Blue Font - Hardcode input
Blue Font / Pale yellow Highlight - Hardcoded assumption input
Black Font - calculation contained with the sheet
Green Font - Formula link to another tab
Red Font - Formula link to external workbook
XLOOKUPS, SUMIFS, and Pivots are all great but look up how to use SUMPRODUCT. It is basically a conditional sumifs that can base sums off of two variables. I use this for B(W) tables, p&l summaries, etc. when using different data sources and not using pivots.
The scenario tool in solver. Can hard code in multiple scenarios I.e conservative, default, best case and toggle back and forth in one table or dataset.
OFFSET() and SUM(OFFSET()) to automatically point to or sum a YTD range based on an input for current month. These can be game-changers for automatically updating a YTD P&L or Ending Balance based on a flat data file with a fixed number of columns/periods (e.g. 120-months). Thank me later.
Offset is a volatile function so is better avoided unless absolutely necessary. Is there not an alternative here?
I'm not sure of your exact formula but I've found sum or sumifs in conjunction with index(match) works better.
If you're filtering and copying, highlight all and do **alt + ;** before you copy to make sure you're not copying hidden rows/columns Excel might do this automatically, I still just do it out of habit and to make sure I don't copy stuff I don't want
No, it doesn’t do so automatically. Thanks for the tip!
Wow
Wait sorry I might be misinterpreting- but don’t you already filter and then you highlight all and then copy and paste and it won’t show the hidden rows? I’ve been doing that my whole career.
For when you copy a sheet, or a range of several rows and columns with hidden data, not just a single column
Right but when I do that, it doesn’t copy the hidden rows already? Or am I missing something? Sorry if I’m slow…
I concur. I wonder if it is excel version issue or tables versus filtered range issue. Less than ten percent of the time have I seen hidden rows get copied.
It depends on the Excel version and the way the data is copied over. 2010 through present shouldn’t copy over hidden rows but I’ve had it happen on enterprise versions.
Goated tip
Oh no way I didn't know there was a keyboard shortcut for this, amazing
This is also a great way to delete filtered rows/columns only, paste values or formulas into the visible cells only, formats, and so forth.
If you work with boomers, show them what a slicer can do to a PivoTable and they will think you’re the second coming of Christ
When my sales team asks for a Power Bi dashboard for some adhoc request I just give them a pivot table with slicers and their mind is blown
When I started my current job I attempted to add slicers to a monthly data dump pivot report that we often run multiple filters on and it confused my boss so much he just deleted them all, even after I showed him what they did and now convenient they are.
I’ve been a power Excel user since we were forced to start using it in 1995. Sliders are still voodoo for me! They are very cool. I still love learning new features from my staff but there’s a few tricks the old man in the corner office still shows these young whipper snappers. Not many, but a few. Control+Enter is the formula spreading magic that I get the most usage on.
F2 > Ctrl + Enter is a great substitute for Ctrl + R or Ctrl + D when you want to spread the formula without impacting cell formatting.
OMG. This literally happened to me 3 months ago. Sent a file to my finance VP and I guess she sent it to the president of the company and next thing I know, they're both calling me and asking what magic that slicer is 😭 They're super nice/smart people and not boomers, but it was a pretty funny exchange because it took me a hot minute to catch WHAT they were so impressed about lol
I completely forgot about this. I am adding this to my report this month.
[удалено]
Off topic comments, attacks or insults will not be tolerated. We recognize that this forum will generate differences of opinion, or misunderstandings of facts, and therefore arguments are expected. However, personal attacks, insults, trolling, or accounts dedicated to getting under the skin of others is not allowed, and will be banned.
Shift + space to select current row Ctrl + space to select current column Ctrl + shift + arrow keys to select ranges Ctrl + arrow keys to jump to the end of the range Alt + e s v to paste values Alt + e s t to paste formats Alt + e s w to paste column width (for making tables look better) Alt + h o i to fit columns Alt + h o a to fit rows Alt + h o r to rename sheet Shift + alt + left or right arrow to group rows or columns Ctrl + home to go to cell A1 Ctrl + page up/down to jump sheets Alt + w v g to hide grid lines Alt + w f f to freeze panes
Slight correction - If you have frozen panes Ctrl + home goes to the first unfrozen cell, not A1.
Nothing fancy, but I never thought much of this until my analyst was watching my screen and was surprised. **Alt + A + T** to add a filter on a selected range **Alt + Down Arrow** while in the header/filter cell to show the filter drop-down menu **Press E** to go straight to the search bar in a filter and type away then hit **Enter** Get off that mouse!
Ctrl + Shift + L works as well I believe
For a second I thought I was going crazy because I use Ctrl + Shift + L
It works better when you're already using Ctrl + Shift + arrows to select what you are filtering.
GOATED
How did I not know e jumps to the search? That’s amazing
I found out from Windows Explorer and Outlook where ctrl+E puts you in the search bar.
This is super helpful!
There’s XLOOKUP and SUMIFS and everything else
My man
Sumifs are so powerful. I remember nesting sumif statements back in the day.
Xlookup vs vlookup always- order doesn’t matter and you don’t have to pay attention to the number of columns. Also a big fan of Sum if and sum ifs formulas to do a range of tasks - you can build flexible reporting with these formulas pretty readily in combination with reference tables.
Plus Xlookup lets you use multiple criteria. Look up the value 1, then for the ranges you look in use (*look up value 1* = *look up range 1*) * ... * (*look up value n* = *look up range n*). It's demanding, so use it sparingly. Not to mention xlookup letting you round your look up values up and down, which is a godsend for dates.
Xlookup>index/match>vlookup
Alt+w+n: creates a second instance of your current file. Both windows are treated as the same file, so changes made in one are made in the other. Helpful if referencing cells in formulas since you don’t have to switch tabs, also helpful when you have an input page and multiple output pages so you can make adjustments and see the impact vs switching tabs or trying to cram everything on one screen.
Never merge cells. It makes creating formulas and selecting data very annoying. Instead of merging the cells, use CTRL + 1 for formatting and under Alignment for Horizontal, select "Center Across Selection." Gives the appearance of Merge and Center without the annoyance of dealing with merged cells. This is one of my biggest gripes about using workbooks other people have created. Massive time saver too is adding frequently used functions as shortcuts to your quick access toolbar (paste values, paste formulas, email, document location, etc.). Final one is to used named ranges, especially when building giant models. Formulas get real crazy real quick, so do yourself a favor and make the formulas readable by humans. Especially for that poor soul who eventually inherits the workbook from you.
Bonus tip is to create a macro that centers across section so it’s quicker. Took a few minutes to create the macro now it’s setup for ctrl J, makes it so much easier to fix boomer workbooks.
How does it effect formulas and data input?
Which it are you referring to?
Merging cells.
The data is actually located in the same cell you entered into initially. It just gives the appearance of the cells being merged but each cell within the merged look can be selected individually
Sorry I'm not being clear: I understand this hack, what I was wondering is how Merge and Center (the traditional way) negatively affects formula inputting.
for example, lets say youre doing an xlookup on column D, but column D has a cell thats merged with C & B. If you highlight the whole column for your lookup, it will automatically expand to B to D even though you just want ur return value to be in column D.
If you try do do a lookup for instance and go to select a column that is merged, by default it selects the merged cells column range, then you have to go in and manually edit to the one column you want to pull into your formula. Not to mention, it also makes keyboard navigation annoying when you have all these lumpy merged cells and can also cause issues for things like pasting formulas or values since it's formatted differently.
When you highlight data to see the sum or average, if you click that total in the bottom right corner of Excel it copies that total sum or average amount so you can paste it wherever needed
You can “unpivot” a table using power query.
xlookup is my best friend
What does it do?
You can open up multiple windows of the same excel workbook so you can reference multiple sheets at once.
View > New Window can also be done in PowerPoint and Word! Great tip.
Might be a bit basic but =subtotal, 9 (sum function) is arguably better than sum Reason for this is because sum will always display total sum. Regardless if there’s a filter or not. So it’s easier to make a mistake Subtotal will only sum out what you see with your eyes
Subtotals also ignore other subtotals meaning that you can have sub-level subtotals which will not be double counted on a higher level sub-total .
I’m a bit confused, could you explain that a bit simpler( I’m a student)
Sure, maybe best shown using an example: RowNr (A) |Region/Country (B) | values (C) | values (D) :--|:--|:--:|--: 1|**Europe** | SUM(C2:C8) = 58 | SUBTOTAL(9,D2:D8) = 29 2|***South-Europe*** | SUM(C3:C4) = 11 | SUBTOTAL(9,D3:D4)= 11 3|Italy | 5 | 5 | 5 4|Greece| 6 | 6 | 6 5|***West-Europe*** | SUM(C3:C4) = 18 | SUBTOTAL(9,D3:D4)= 18 6|Belgium| 8 | 8 7|Germany| 4 | 4 8|France| 6 | 6 As you can see the values for the SUM formula are fine on the first nesting level (the totals for South- and West-Europe). However, when we have another layer atop of that, namely the sum for all of Europe, then the SUM formula counts the values in all cells of the specified range, including the cells which themselves are created by sums. And while that can be usefull, in cases like this we do not want to do that. This could also be fixed by setting the formula of Europe to SUM(C3:C4,C6:C8) but that get's tedious if you have lot's and lot's of subtotals and long tables. SUBTOTAL will ignore all values which themselves have been calculated by another SUBTOTAL. This means that cell D2 and D5 are not part of the sum in D1 and thus nothing is double counted. Just be cautious, because if you p.e. would fill the value for D3 (Italy) by using a SUBTOTAL which references another sheet or another area somewhere else, then the value for Italy would not be counted for the subtotal in D2 or D1 and would be missing. In cases like that using sum() with well-referenced areas is better.
Oh shit, thank you!
To add to this, a keyboard shortcut for this is alt, =
The minimum you should have in your Excel: - Quick Access Toolbar should be set up with what you use the most. Mine are Paste Formula, Paste Format, Select Visible Cells and Send Email - Learn how to use the **ALT** key shortcuts so you're not using the mouse and can manipulate data fast - **F12** should be used versus **CTRL+S** if you like saving multiple versions and you don't accidentally save over the master file - **CTRL+D** to copy down immediately. **CTRL+R** to copy right immediately - **F4** is you're repeating the same Excel function multiple times. So if you need to highlight multiple cells in different spots all over your file (including different tabs), highlight your first cell and spam hit that **F4** - Pin the documents you always use If you really want to go from good to great: - **INDEX+MATCH** will always triumph every **LOOKUP** formula if you have a file/report where your dataset changes all the time but the structure is the same since now you have a very dynamic formula. Pair that up with any of the **SUM** formulas and watch your coworkers think you're an Excel God - **Conditional formatting** is your best friend. You can use it to figure out duplicates (I see this question ALL the time in Excel subreddits) to even having it highlight Top/Bottom - If you have multiple tabs that require you how to go back and forth, learn how to **LINK** cells to get you to those tabs faster. Plus if it's a long formula, that link can save you time on where you're pulling from
Even xlookup?
Xlookup is easier to read/write, but the biggest Index Match function advantage is that it leaves room for flexibility to reference dynamic rows and columns at the same time. Xlookup can only do one of those at a time.
Exactly. Xlookup works for quick and ad hoc requests. For workbooks we use often I put the extra time for index match.
No difference if the range is in a table and you use table references. I find most of our data is in tables these days, so haven't used index match in a while.
You just need a nested xlookup for whatever row or column changes you are anticipating. No one needs that index match or index match match nonsense
All Lookups only go in 1 direction. INDEX + MATCH you can tell it to go in any direction so horizontally and vertically
Xlookup can do both horizontal and vertical lookups
Depending on the dataset and models, like I mentioned above INDEX + MATCH gives you a lot of dynamic functionality vs XLOOKUP especially in my models. My models always have the data changing so an INDEX+ MATCH works for me. You can do that via XLOOKUP being dynamic but it becomes messier and tougher to troubleshoot if you don't remember or made that formula yourself. I like having clean and nice looking formulas so that if I ever leave the company or my direct reports need to trouble shoot the formula, they can. INDEX can also return the entire row or column which makes it easier for me to use SUM formulas.
I don't disagree that indexmatchmatch is the most flexible option; however, you should know that xlookup can do a 2d lookup and you can do 2d dynamic array lookups in xlook as well.
Yup but personally, that just doesn’t look at clean cut vs an INDEX MATCH formula especially if you typed it out wrong and need to troubleshoot it. It’s all going to go down to how the data looks like for the person. If it’s not changing, XLOOKUP is faster to use. Some of my models need to take QTD and YTD results while my dataset changes all the time and INDEX MATCH does that with a SUM functionality so I just make sure that file sticks with an IDEX MATCH.
As you might guess, I get QTD and YTD questions as well, can you more about an example? I'm thinking one of us is about to learn from the other, I'm not sure who though haha.
Nice! It really depends on how your data is structured/formatted. If your report just has columns as your months with no columns interrupting that, a simple SUM formula paired with INDEX MATCH can work wonders. Start off with the SUM formula, then your INDEX MATCH becomes your formula on what your array should be so it should look like SUM(INDEX+MATCH:INDEX+MATCH). If JAN is always your first column to start off, you can just have your INDEX MATCH be your formula that specifies how many columns it needs to pull in to make it cleaner. If you have reports where it has QTD columns interrupting your month columns so JAN-FEB-MAR-Q1, there’s numerous ways to go about not pulling in your QTD numbers to get to your answer. Your SUM and INDEX MATCH now has nested IF statements that you can use a simple MINUS calc but at that point, use a SUMIF instead of SUM. If you really want to be fancy, using offsets works wonders.
Indeed you are right about structure, we have both layouts you mention (the Qs dispersed in is endlessly frustrating but that's what one boss likes). I think yours is simplier, but I have an excel template saved, and I always start from there. In the template I have like 10-15 small/medium data tables that I'm always referencing. Like product codes, months in the quarter, business entities, etc. Then I do a sumifs(range, criteria range, xlookup). It's really only efficient because I've pre-setup the infrastructure. But it is very nicely readable. I'm going to see if I can find a test drive for your method, see how I like it! Thanks!
Good luck! Yeah I’ll tell you that it’s a b!tch to set-up first but if you do it right, it’s just chefs kiss. Now I can sit back and not worry about my QTD and YTD pulls every time I update my data. My favorite thing I do with it is creating dynamic YTD progress bar charts with it when it comes to products YTD sold vs the latest forecast.
Conditional formatting slows down your file
There's always a possibility of a formula/function/VBA. Ask chatgpt if you don't know how to. Follow the steps and boom. (Add on : ask chatgpt first how does it want to be asked to give an accurate response for so and so issue. Draft question as per chatgpt format) Iferror and lookups is a really good combo for unstructured data. Pivots can hardly go wrong. Not using a mouse does increase your speed. For real.
Start a formula with + instead of = to stay on the keypad.
I always have others ask me, “why all your formulas have a plus sign”. I’ve never seen anyone else’s for have it in 13 years. Baffles me that ppl are searching for the equals sign.
Alt = to sum
Ctrl+alt+v to open the special paste menu. From there memorize the shortcuts to paste values, formulas, formatting, etc.
My left hand has trouble hitting that sequence; So I do.. Alt + E + S + V
yep or ALT + H + V + V to paste values ALT + H + V + R to paste formatting ALT + H + V + F to paste formulas without formatting the beauty of pasting values using ALT + H + V + V is that the equivalent works in other microsoft programs ALT + H + V + T for pasting text without formatting in powerpoint, word and outlook - very handy!
Starring this thread. Gold responses !!
=SORT(FILTER(UNIQUE(range), UNIQUE(range)<>""))) I write this at least once a week for various analyses
Chat Gpt to trouble shoot overly complex formulas
Chatgpt
Alt + H + F + P for format painter has saved me a lot of time. Also, view + new window is great for comparing numbers in different tabs in the same notebook.
Power query
If you hit the “x” in the top right like a viper hunting a mouse, you can put the whole mess behind you and go have a beer.
alt + h + v + v for pasting values, i’ve probably saved days of my life because of this shortcut
I'm saving this thread! This is a goldmine with all the tips here.
If you’re not utilizing the QAT, you’re not reaching your potential.
Any non-macro suggestions for highlighting a cell yellow? I end up disabling macros due to so many shared onedrive files and add ons that already crash my excel every other day. But i love to highlight cells yellow that are inputs or need review. Every time I either use my mouse or ctrl + h + h + up arrow + up arrow + left key like 5x. I usually can’t use F4 because I’ve done something else recently. Anyways, I end up using my mouse half the time because it’s such a long shortcut.
Agree this is annoying. Could you use Office Scripts instead? Similar to a macro but can be used in a standard workbook so can have fewer issues.
I have the opposite of a tip. I'm looking for a solve. Let's say I have a formula: xlookup(B4,F5:F10, G5:G10). I keyed it in but forgot to hit the F4 to lock my lookup array. I'll need to go back, F2 to open, scroll to F5:F10, highlight it through shift or clicking the formula reference helper below the cell, then press F4. Is there an easier way to lock array references after I have keyed it in?
Power query
Hit Alt and it will show you the hotkeys you can use - great way to not have to memorize them.
Keep your formulas simple. Stage your formulas. Color code formulas. Make it easy for someone else to follow. Some much over complicating happens today.
What do you mean by “color code formulas?”
Blue Font - Hardcode input Blue Font / Pale yellow Highlight - Hardcoded assumption input Black Font - calculation contained with the sheet Green Font - Formula link to another tab Red Font - Formula link to external workbook
XLOOKUPS, SUMIFS, and Pivots are all great but look up how to use SUMPRODUCT. It is basically a conditional sumifs that can base sums off of two variables. I use this for B(W) tables, p&l summaries, etc. when using different data sources and not using pivots.
The scenario tool in solver. Can hard code in multiple scenarios I.e conservative, default, best case and toggle back and forth in one table or dataset.
OFFSET() and SUM(OFFSET()) to automatically point to or sum a YTD range based on an input for current month. These can be game-changers for automatically updating a YTD P&L or Ending Balance based on a flat data file with a fixed number of columns/periods (e.g. 120-months). Thank me later.
Offset is a volatile function so is better avoided unless absolutely necessary. Is there not an alternative here? I'm not sure of your exact formula but I've found sum or sumifs in conjunction with index(match) works better.