❓What Excel formula trick has saved you the most time and frustration in your projects? Learn more about Excel Tables with my course: bit.ly/eformulas24course
Using tables has definitely helped me. Now when I get files from coworkers that don’t have tables, I do a little bit of work to get everything in order before continuing
While I generally don't have to enter data into tables, when I do, I switch the Enter Key direction to 'Right' instead of 'Down'. Because that is kind of a bother to chase down in the options menu, I record a Macro for each direction and add to the QAT.
@@brianspiller9075 that’s a brilliant idea. I’m doing that as soon as I get to work tomorrow For smaller data sets the data entry form (buried in in ribbon customization, called Form) also works. It pulls in all the fields of the table and allows the user to tab into each one If your data has many fields or a lot of columns with data validation I don’t recommend this
@@brianspiller9075 that’s amazing. I’m doing that How do you record the action of changing the direction or for that matter any option? The recorder seems to be for the workbook only
Great video!! When criteria arrays are perpendicular we can use a single cell formula: =SUMIFS(D2:D64,A2:A64,F2:F6,C2:C64,G1:I1) plus we can use PIVOTBY ,GROUPBY these days
I go straight to Excel tables and dynamic arrays and don’t even bother with cel references anymore. Who doesn’t like a good broadcasted report: just a single formula and fully dynamic… just great! :-)
As usual a very practical and useful video Mynda! Thanks for these tips. Your casual attire and changed video background is also adding a cool refreshing touch! Keep rolling! 🙂
The use of tables was one of the first things I learned in Excel and I always try to use them when possible. Not only are they useful in Excel, but they are useful if you use external tools that connect to Excel files such as Power Platform.
@@MyOnlineTrainingHub I wonder if there’s someone who is absolutely amazing at demystifying Excel that could do a video showing those functions in action… wink wink, nudge nudge 😊
table is fun and neat until you are dealing with unstructured data, back to absolute reference we go. Talking about named reference, I wonder if excel will add the feature to automatically update the name reference range if it is referencing a dynamic array.
You can use the spill operator in the Name to have it automatically update e.g. Name =B4#. As for unstructured data, IMO unstructured data should be structured before analyzing. See this video: ruclips.net/video/CNlw1-Vh4cE/видео.html
I love your videos but in this last one you used table structured references then copied them across the columns. I thought these structured references weren't absolute and would change values when copied across the columns. Do you do something so this didn't happen? I have had to use the format table[[column]:[column]] syntax to prevent this.
Copying and pasting formulas containing structured references results in different behaviour than copying using left click and drag 😉left click and drag results in relative structured references.
@cgrablew, You got it absolutely right, I was going to make the very same comment/observation. I guess it just worked well (absolute reference) because she had previously named those ranges from the previous demo on named ranges.
I use all of the functions you presented to a greater or lesser extent, but the real combo is the combination of unique, sort filter, vstack and let . Nevertheless, when using tables, xlookup is a great feature.
I’ve been using excel for decades. I mostly use it for infrequent cleanup of exports and imports into other systems. And some reporting. I’ve been watching your videos and kicking myself for not nothing to learn some of these tricks or better ways to do things. The # for example. That’s going to save me some time. I’ve written code to solve problems that some of your videos are showing me was unnecessary. Doh lol. Never too old to learn though. Thanks for making these tutorials, I know how much time and effort goes in to even a simple short video like this. One thing I get tripped up on with named values and ranges is you can’t make them relative when filling the formula to other columns or rows. (Or can you?)
Better to discover these tips late than never 😉 regarding relative filtered ranges, I presume you mean having the named range ignore the rows that are filtered out. The answer is no, but you can use the FILTER function: ruclips.net/video/ZCQAweoAdOw/видео.html
Use a table, and Example 2 needs 3 formulas only. And of course it's adaptable to Example 1 using COUNTIFS: cell E2: = unique( table[dept] ) cell F1: = transpose( unique( table[year] ) ) cell F2: = sumifs( table[salary], table[year], F$2#, table[dept], $E3# )
16 comments, so someone already mentioned my favorite, Pivot Tables! The downside for the refresh is to auto-update on opening. However, two points for formula routes. 1. When entering formulas for a range, enter as an array. Select the range and enter the formula accomadating for fixed/unfixed cell referencing. Enter with CTRL+Enter. 2. To create Named Ranges more easily, use that feature found in the Formulas Ribbon, Defined Names section, Create from Selection. Selecting the entire columns in the example is okay because Excel will only look at the used range, CTRL+End reveals "last" cell used.
Got to love PivotTables. They are my favourite Excel tool after Power Query. I prefer not to enter formulas as arrays, but it's handy if you want to prevent people accidentally deleting/breaking your formulas. 😁
Great question. B4:D7 is a relative reference, meaning if you copy a formula with this reference the column and row references will update relative to where you copied it to, whereas $B$4:$D$7 is an absolute reference and will remain referencing those cells irrespective of where you copy it. I cover this and more fundamentals in next week's video, so keep your eye out for it.
Hoping you can help me. I have a multi sheet workbook that uses data from a main ledger sheet to a receipts sheet and a status report sheet, for a workplace Sunshine Fund. Is there a formula, shortcut, or way to build the receipt book and status report so that it isn't effected or corrupted if I have to add rows (New Employees/Members) to the main ledger? Thank you for any assistance you can offer!
I need different value for Uppercase and Lowercase. Can you help me please. I used =if(A1="a",5,if(A1="A",10,"not a or A")) but it not working properly.
I've been using Excel for more than 20 years, but that doesn't mean it would take that long. It all depends on how much you practice. These shortcuts also work on a Mac, yes 😊
No. Spilled arrays cannot be formatted in a Table. Better to use Power Query to consolidate the data if you want to use Tables: ruclips.net/video/YOC-pEIuHpA/видео.html
Dear Mynda, Sorry, but you only used the intended efficiency in the "Spilled Arrays" worksheet, because in all the others, the same system could have been used instead of copying and pasting, or dragging, and without worrying about using fixed or relative references: References 1 =COUNTIF(A2:A22, E2:E6) References 1 =SUMIFS(D2:D64, A2:A64, F2:F6, C2:C64, G1:I1) Named Ranges =SUMIFS(Salary, Dept, F2:F6, Year, G1:I1) Excel Tables =SUMIFS(Table1[Salary], Table1[Dept.], F2:F6, Table1[Year], G1:I1) 🤗
True, Jose! But I have to keep in mind that not everyone has dynamic arrays. I had an email today from someone with Excel 2019 complaining about that exact issue. I encouraged her to ask her employer to upgrade to 365 😉
question if I may RE table and references. In the past when I have created table references for formulas that stretch across rows and columns, I have have to make my table reference absolute. IE ( ruclips.net/video/YXJCcrkLTco/видео.html ) I would have had to write the formula as =SUMIFS(Salaries[[Salary]:[Salary]],Salaries[[Dept.]:[Dept.]],$F6........ect) is there a setting you have that allows the reference to be absolute like yours? It would save me formula wiring time believe me!
The behaviour is different depending on whether you copy and paste a formula or left click and drag. With the former you get absolute references and don't have to use the double square bracket technique 😉
Me, with every new Mynda video. "I guess I can spare ten minutes." refill teacup play "Yeah, yeah, anyone who's used Excel for five minutes knows all tha...ooooooh. That was cool!" pause scroll back play pause open Excel play with new cool thing for 20 minutes finish video "What'd she say that other video was called? Oh, good, here's the link." click play repeat above n times "How is it lunchtime already?"
❓What Excel formula trick has saved you the most time and frustration in your projects?
Learn more about Excel Tables with my course: bit.ly/eformulas24course
Using tables has definitely helped me. Now when I get files from coworkers that don’t have tables, I do a little bit of work to get everything in order before continuing
Wonderful to hear, Patrick!
While I generally don't have to enter data into tables, when I do, I switch the Enter Key direction to 'Right' instead of 'Down'. Because that is kind of a bother to chase down in the options menu, I record a Macro for each direction and add to the QAT.
@@brianspiller9075 that’s a brilliant idea. I’m doing that as soon as I get to work tomorrow
For smaller data sets the data entry form (buried in in ribbon customization, called Form) also works. It pulls in all the fields of the table and allows the user to tab into each one
If your data has many fields or a lot of columns with data validation I don’t recommend this
@@brianspiller9075 that’s amazing. I’m doing that
How do you record the action of changing the direction or for that matter any option? The recorder seems to be for the workbook only
Couldn’t you just hit tab? Or is your preference more so because you’re entering mostly numbers using the number pad?
Great video!!
When criteria arrays are perpendicular we can use a single cell formula:
=SUMIFS(D2:D64,A2:A64,F2:F6,C2:C64,G1:I1)
plus we can use PIVOTBY ,GROUPBY these days
Doh, forgot to demo that one too. Thanks for sharing 🙏
My excel skills are improving because of your wonderful videos. Thank you Mynda!
Wonderful to hear! Keep up the hard work 💪
Name ranger is really helpful, easy for other people to read, and the table function makes it even better!
Glad you like these options 👍
Thanks to your video on the LET function I use it all the time as an alternative to named ranges.
I go straight to Excel tables and dynamic arrays and don’t even bother with cel references anymore.
Who doesn’t like a good broadcasted report: just a single formula and fully dynamic… just great! :-)
Love that you're embracing the modern tools, Geert 😊
@@MyOnlineTrainingHub …since they can out in the Beta channel, TBH. :-)
As usual a very practical and useful video Mynda! Thanks for these tips. Your casual attire and changed video background is also adding a cool refreshing touch! Keep rolling! 🙂
Thanks for your support, Vijay!
Excellent as always Mynda!
Thanks so much, Chris! 🥰
This the best lecture simplify the way of using formulas . Thank you
Glad it was helpful!
Great information! Mynda's presentation makes it very easy to understand and follow along.
Glad you think so! 🙏
The use of tables was one of the first things I learned in Excel and I always try to use them when possible. Not only are they useful in Excel, but they are useful if you use external tools that connect to Excel files such as Power Platform.
Wonderful to hear. You are one of the few 😊
LET and MAP are underrated functions to simplify complex formulas
Hopefully, they'll become more commonplace 🤞
@@MyOnlineTrainingHub I wonder if there’s someone who is absolutely amazing at demystifying Excel that could do a video showing those functions in action… wink wink, nudge nudge 😊
😂I've already done a video on LET. MAP will come along eventually...
Wow, fantastic, I'm very tankful.
Glad you like it!
Most I've learned from trial and error, like this video i always wish i found/had it earlier. It would have saved me hours of time
Great video 🔥😎
🙏Trial and error is a memorable way to learn, but like you say, it's slow.
table is fun and neat
until you are dealing with unstructured data, back to absolute reference we go.
Talking about named reference, I wonder if excel will add the feature to automatically update the name reference range if it is referencing a dynamic array.
You can use the spill operator in the Name to have it automatically update e.g. Name =B4#. As for unstructured data, IMO unstructured data should be structured before analyzing. See this video: ruclips.net/video/CNlw1-Vh4cE/видео.html
Good morning. Very Good! 👍👍Thank you!
Thanks for watching!
Also I would add Alt+Enter to break down the formula text into separate lines :)
Yes, especially for long formulas. Thanks for mentioning it.
I love your videos but in this last one you used table structured references then copied them across the columns. I thought these structured references weren't absolute and would change values when copied across the columns. Do you do something so this didn't happen? I have had to use the format table[[column]:[column]] syntax to prevent this.
Copying and pasting formulas containing structured references results in different behaviour than copying using left click and drag 😉left click and drag results in relative structured references.
@cgrablew, You got it absolutely right, I was going to make the very same comment/observation. I guess it just worked well (absolute reference) because she had previously named those ranges from the previous demo on named ranges.
Very helpful ❤ Thank you 😊 Mynda
You are so welcome! 🤗
I use all of the functions you presented to a greater or lesser extent, but the real combo is the combination of unique, sort filter, vstack and let . Nevertheless, when using tables, xlookup is a great feature.
Great to hear 👍
I’ve been using excel for decades. I mostly use it for infrequent cleanup of exports and imports into other systems. And some reporting.
I’ve been watching your videos and kicking myself for not nothing to learn some of these tricks or better ways to do things. The # for example. That’s going to save me some time. I’ve written code to solve problems that some of your videos are showing me was unnecessary. Doh lol.
Never too old to learn though. Thanks for making these tutorials, I know how much time and effort goes in to even a simple short video like this.
One thing I get tripped up on with named values and ranges is you can’t make them relative when filling the formula to other columns or rows. (Or can you?)
Better to discover these tips late than never 😉 regarding relative filtered ranges, I presume you mean having the named range ignore the rows that are filtered out. The answer is no, but you can use the FILTER function: ruclips.net/video/ZCQAweoAdOw/видео.html
I like the named range and the # sign. I think I will use it in the future, so I will follow your lesson about that
Great to hear!
Please make video on pure book keeeping/ Financial Accounting.
In Excel or just accounting fundamentals like t-accounts etc.?
Use a table, and Example 2 needs 3 formulas only. And of course it's adaptable to Example 1 using COUNTIFS:
cell E2: = unique( table[dept] )
cell F1: = transpose( unique( table[year] ) )
cell F2: = sumifs( table[salary], table[year], F$2#, table[dept], $E3# )
Yes 🙏 dynamic arrays are the best! Thanks for sharing.
16 comments, so someone already mentioned my favorite, Pivot Tables! The downside for the refresh is to auto-update on opening.
However, two points for formula routes.
1. When entering formulas for a range, enter as an array. Select the range and enter the formula accomadating for fixed/unfixed cell referencing. Enter with CTRL+Enter.
2. To create Named Ranges more easily, use that feature found in the Formulas Ribbon, Defined Names section, Create from Selection. Selecting the entire columns in the example is okay because Excel will only look at the used range, CTRL+End reveals "last" cell used.
Got to love PivotTables. They are my favourite Excel tool after Power Query.
I prefer not to enter formulas as arrays, but it's handy if you want to prevent people accidentally deleting/breaking your formulas. 😁
Always use Tables! Nice video as usual!
Thank you!
damn, this is so cool! It literally blew my mind! As a casual Excel user I knew almost none of these, thanks!
Wonderful to hear! 😊
Pivot table is also good.
Yes! LOVE PivotTables. 😁
very cool tips, thanks
Thanks so much!
im new to excel, let say the range is B4:D7, what the difference with $B$4:$D$7?
Great question. B4:D7 is a relative reference, meaning if you copy a formula with this reference the column and row references will update relative to where you copied it to, whereas $B$4:$D$7 is an absolute reference and will remain referencing those cells irrespective of where you copy it. I cover this and more fundamentals in next week's video, so keep your eye out for it.
Hoping you can help me. I have a multi sheet workbook that uses data from a main ledger sheet to a receipts sheet and a status report sheet, for a workplace Sunshine Fund. Is there a formula, shortcut, or way to build the receipt book and status report so that it isn't effected or corrupted if I have to add rows (New Employees/Members) to the main ledger? Thank you for any assistance you can offer!
Yes, use a PivotTable to extract the data from your main ledger to your receipts and status report sheets.
When in a group you don't know who is the Excel Master, YOU are the Excel Master 😎
Thanks so much!
Amazing one 🎉
Thank you! 😊
I need different value for Uppercase and Lowercase. Can you help me please.
I used =if(A1="a",5,if(A1="A",10,"not a or A"))
but it not working properly.
Try this: =IF(CODE(A1)=CODE("a"),5,IF(A1="A",10,"not a or A"))
How long did it take you to this good on Excel? And will shortcuts work on the file on a Mac?
I've been using Excel for more than 20 years, but that doesn't mean it would take that long. It all depends on how much you practice. These shortcuts also work on a Mac, yes 😊
Great Gob. keep it up.
Thank you, I will 😊
Is there any exercices to practice?
⬇️ Download the example file here: bit.ly/eformulas24file
Can u take data from multiple tabs via vstack and then convert vstack data to a table?
No. Spilled arrays cannot be formatted in a Table. Better to use Power Query to consolidate the data if you want to use Tables: ruclips.net/video/YOC-pEIuHpA/видео.html
Dear Mynda,
Sorry, but you only used the intended efficiency in the "Spilled Arrays" worksheet, because in all the others, the same system could have been used instead of copying and pasting, or dragging, and without worrying about using fixed or relative references:
References 1
=COUNTIF(A2:A22, E2:E6)
References 1
=SUMIFS(D2:D64, A2:A64, F2:F6, C2:C64, G1:I1)
Named Ranges
=SUMIFS(Salary, Dept, F2:F6, Year, G1:I1)
Excel Tables
=SUMIFS(Table1[Salary], Table1[Dept.], F2:F6, Table1[Year], G1:I1) 🤗
True, Jose! But I have to keep in mind that not everyone has dynamic arrays. I had an email today from someone with Excel 2019 complaining about that exact issue. I encouraged her to ask her employer to upgrade to 365 😉
question if I may RE table and references. In the past when I have created table references for formulas that stretch across rows and columns, I have have to make my table reference absolute. IE ( ruclips.net/video/YXJCcrkLTco/видео.html ) I would have had to write the formula as =SUMIFS(Salaries[[Salary]:[Salary]],Salaries[[Dept.]:[Dept.]],$F6........ect)
is there a setting you have that allows the reference to be absolute like yours? It would save me formula wiring time believe me!
The behaviour is different depending on whether you copy and paste a formula or left click and drag. With the former you get absolute references and don't have to use the double square bracket technique 😉
Me, with every new Mynda video.
"I guess I can spare ten minutes."
refill teacup
play
"Yeah, yeah, anyone who's used Excel for five minutes knows all tha...ooooooh. That was cool!"
pause
scroll back
play
pause
open Excel
play with new cool thing for 20 minutes
finish video
"What'd she say that other video was called? Oh, good, here's the link."
click
play
repeat above n times
"How is it lunchtime already?"
😂thanks so much for tuning in even though you know 99% of what I cover. I'm just glad I can make it worth your time to watch 🙏
Apart from Excel-Worms, also learners of English Language can exploit your videos.
Glad it's doubly useful 😁