❓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
@Excelambda5 ай бұрын
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
@MyOnlineTrainingHub5 ай бұрын
Doh, forgot to demo that one too. Thanks for sharing 🙏
@patrickschardt77245 ай бұрын
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
@MyOnlineTrainingHub5 ай бұрын
Wonderful to hear, Patrick!
@brianspiller90755 ай бұрын
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.
@patrickschardt77245 ай бұрын
@@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
@patrickschardt77245 ай бұрын
@@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
@MonkEBoy-ud6kj4 ай бұрын
Couldn’t you just hit tab? Or is your preference more so because you’re entering mostly numbers using the number pad?
@frameworkcc20055 ай бұрын
Name ranger is really helpful, easy for other people to read, and the table function makes it even better!
@MyOnlineTrainingHub5 ай бұрын
Glad you like these options 👍
@robparker16255 ай бұрын
Thanks to your video on the LET function I use it all the time as an alternative to named ranges.
@krishna62965 ай бұрын
My excel skills are improving because of your wonderful videos. Thank you Mynda!
@MyOnlineTrainingHub5 ай бұрын
Wonderful to hear! Keep up the hard work 💪
@GeertDelmulle5 ай бұрын
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! :-)
@MyOnlineTrainingHub5 ай бұрын
Love that you're embracing the modern tools, Geert 😊
@GeertDelmulle5 ай бұрын
@@MyOnlineTrainingHub …since they can out in the Beta channel, TBH. :-)
@XegaZero5 ай бұрын
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.
@MyOnlineTrainingHub5 ай бұрын
Wonderful to hear. You are one of the few 😊
@vijayarjunwadkar5 ай бұрын
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! 🙂
@MyOnlineTrainingHub5 ай бұрын
Thanks for your support, Vijay!
@yokoyama75905 ай бұрын
Great information! Mynda's presentation makes it very easy to understand and follow along.
@MyOnlineTrainingHub5 ай бұрын
Glad you think so! 🙏
@alamiralshafay5 ай бұрын
This the best lecture simplify the way of using formulas . Thank you
@MyOnlineTrainingHub5 ай бұрын
Glad it was helpful!
@chrism90375 ай бұрын
Excellent as always Mynda!
@MyOnlineTrainingHub5 ай бұрын
Thanks so much, Chris! 🥰
@Jojo-gf5qb5 ай бұрын
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.
@MyOnlineTrainingHub5 ай бұрын
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: kzbin.info/www/bejne/eX_PqGRji81nmac
@rusektor5 ай бұрын
Also I would add Alt+Enter to break down the formula text into separate lines :)
@MyOnlineTrainingHub5 ай бұрын
Yes, especially for long formulas. Thanks for mentioning it.
@joukenienhuis68885 ай бұрын
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
@MyOnlineTrainingHub5 ай бұрын
Great to hear!
@IamTheReaper9115 ай бұрын
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 🔥😎
@MyOnlineTrainingHub5 ай бұрын
🙏Trial and error is a memorable way to learn, but like you say, it's slow.
@Michałbłaszczyk-h3c2 ай бұрын
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.
@MyOnlineTrainingHub2 ай бұрын
Great to hear 👍
@nigelpallattАй бұрын
Wow, fantastic, I'm very tankful.
@MyOnlineTrainingHubАй бұрын
Glad you like it!
@MeppyMan5 ай бұрын
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?)
@MyOnlineTrainingHub5 ай бұрын
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: kzbin.info/www/bejne/kHS0cqqbpKaXhdk
@soubhikd5 ай бұрын
LET and MAP are underrated functions to simplify complex formulas
@MyOnlineTrainingHub5 ай бұрын
Hopefully, they'll become more commonplace 🤞
@stretch2334 ай бұрын
@@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 😊
@MyOnlineTrainingHub4 ай бұрын
😂I've already done a video on LET. MAP will come along eventually...
@cgrablew5 ай бұрын
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.
@MyOnlineTrainingHub5 ай бұрын
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.
@osoriomatucurane95112 ай бұрын
@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.
@hasanmahmud48084 ай бұрын
Please make video on pure book keeeping/ Financial Accounting.
@MyOnlineTrainingHub4 ай бұрын
In Excel or just accounting fundamentals like t-accounts etc.?
@brianspiller90755 ай бұрын
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.
@MyOnlineTrainingHub5 ай бұрын
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. 😁
@lauphyon5 ай бұрын
Good morning. Very Good! 👍👍Thank you!
@MyOnlineTrainingHub5 ай бұрын
Thanks for watching!
@shanmugavadivud55 ай бұрын
Very helpful ❤ Thank you 😊 Mynda
@MyOnlineTrainingHub5 ай бұрын
You are so welcome! 🤗
@ddBenny5 ай бұрын
damn, this is so cool! It literally blew my mind! As a casual Excel user I knew almost none of these, thanks!
@MyOnlineTrainingHub5 ай бұрын
Wonderful to hear! 😊
@aldrindacion34685 ай бұрын
Pivot table is also good.
@MyOnlineTrainingHub5 ай бұрын
Yes! LOVE PivotTables. 😁
@KKB-tt4lj3 ай бұрын
Always use Tables! Nice video as usual!
@MyOnlineTrainingHub3 ай бұрын
Thank you!
@georgeburr32123 күн бұрын
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!
@MyOnlineTrainingHub3 күн бұрын
Yes, use a PivotTable to extract the data from your main ledger to your receipts and status report sheets.
@dccd6735 ай бұрын
How long did it take you to this good on Excel? And will shortcuts work on the file on a Mac?
@MyOnlineTrainingHub5 ай бұрын
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 😊
@renatorosco3255 ай бұрын
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# )
@MyOnlineTrainingHub5 ай бұрын
Yes 🙏 dynamic arrays are the best! Thanks for sharing.
@brodesignstudio5255 ай бұрын
im new to excel, let say the range is B4:D7, what the difference with $B$4:$D$7?
@MyOnlineTrainingHub5 ай бұрын
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.
@rinakaur72455 ай бұрын
Can u take data from multiple tabs via vstack and then convert vstack data to a table?
@MyOnlineTrainingHub5 ай бұрын
No. Spilled arrays cannot be formatted in a Table. Better to use Power Query to consolidate the data if you want to use Tables: kzbin.info/www/bejne/j4CmXqN7ftp7pqM
@SalimNahra2 ай бұрын
Is there any exercices to practice?
@MyOnlineTrainingHub2 ай бұрын
⬇️ Download the example file here: bit.ly/eformulas24file
@micmike5 ай бұрын
very cool tips, thanks
@MyOnlineTrainingHub5 ай бұрын
Thanks so much!
@FAO245 ай бұрын
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.
@MyOnlineTrainingHub5 ай бұрын
Try this: =IF(CODE(A1)=CODE("a"),5,IF(A1="A",10,"not a or A"))
@nazarkamal88315 ай бұрын
Amazing one 🎉
@MyOnlineTrainingHub5 ай бұрын
Thank you! 😊
@Perokoulos5 ай бұрын
When in a group you don't know who is the Excel Master, YOU are the Excel Master 😎
@MyOnlineTrainingHub5 ай бұрын
Thanks so much!
@fayyaztabussam86114 ай бұрын
Great Gob. keep it up.
@MyOnlineTrainingHub4 ай бұрын
Thank you, I will 😊
@JoseAntonioMorato5 ай бұрын
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) 🤗
@MyOnlineTrainingHub5 ай бұрын
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 😉
@gregorymulford17275 ай бұрын
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 ( kzbin.info/www/bejne/j4mtdJaooLGHmdE ) 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!
@MyOnlineTrainingHub5 ай бұрын
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 😉
@consultant_of_swing21465 ай бұрын
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?"
@MyOnlineTrainingHub5 ай бұрын
😂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 🙏
@eversut1Ай бұрын
Apart from Excel-Worms, also learners of English Language can exploit your videos.