Are You Making This Excel Mistake? Fix It in Minutes!
ฝัง
- เผยแพร่เมื่อ 1 มิ.ย. 2024
- The reason why you spend too much time on writing Excel formulas (quick fixes).
👩🏫 Learn more about Excel Tables with my course: bit.ly/eformulas24course
⬇️ Download the example file here and follow along: bit.ly/eformulas24file
Struggling with Excel formulas can turn simple tasks into time-consuming challenges, leaving you frustrated and behind on your work. The problem isn't the formulas though; it's how we put them together. Fortunately, there's a treasure trove of formula productivity secrets hidden within Excel waiting to be unlocked. In this video, I'll guide you through 5 techniques you can use to maximize writing formulas efficiently, turning potential struggles into streamlined success.
LEARN MORE
===========
📰 EXCEL NEWSLETTER - join 450K+ subscribers here: www.myonlinetraininghub.com/e...
🎯 FOLLOW me on LinkedIn: / myndatreacy
💬 EXCEL QUESTIONS: Get help on our Excel Forum: www.myonlinetraininghub.com/e...
⏲ TIMESTAMPS
==============
0:00 Why you spend so much time in Excel
0:28 What you should not do
0:52 Fix №1
1:18 Fix №2
3:55 Fix №3
5:43 Fix №4
8:32 Fix №5
#Excel #ExcelFormulas #ExcelFunctions - วิทยาศาสตร์และเทคโนโลยี
![[TH] VCT Masters Shanghai Playoffs DAY 3 // FNC vs FUT | EDG vs PRX](http://i.ytimg.com/vi/pw2T5v0ogDI/mqdefault.jpg)
❓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 🙏
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. :-)
Always use Tables! Nice video as usual!
Thank you!
This the best lecture simplify the way of using formulas . Thank you
Glad it was helpful!
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...
My excel skills are improving because of your wonderful videos. Thank you Mynda!
Wonderful to hear! Keep up the hard work 💪
Excellent as always Mynda!
Thanks so much, Chris! 🥰
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!
Great information! Mynda's presentation makes it very easy to understand and follow along.
Glad you think so! 🙏
Very helpful ❤ Thank you 😊 Mynda
You are so welcome! 🤗
very cool tips, thanks
Thanks so much!
Good morning. Very Good! 👍👍Thank you!
Thanks for watching!
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 😊
Please make video on pure book keeeping/ Financial Accounting.
In Excel or just accounting fundamentals like t-accounts etc.?
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.
Amazing one 🎉
Thank you! 😊
Great Gob. keep it up.
Thank you, I will 😊
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: th-cam.com/video/ZCQAweoAdOw/w-d-xo.html
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! 😊
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 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!
Pivot table is also good.
Yes! LOVE PivotTables. 😁
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: th-cam.com/video/CNlw1-Vh4cE/w-d-xo.html
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. 😁
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: th-cam.com/video/YOC-pEIuHpA/w-d-xo.html
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"))
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.
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 😊
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.
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.
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 ( th-cam.com/video/YXJCcrkLTco/w-d-xo.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 😉
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 😉
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 🙏