- 96
- 1 578 264
Data Untelligence
Australia
เข้าร่วมเมื่อ 2 ส.ค. 2020
DATA UNTELLIGENCE is a transformative concept that redefines intelligence not as the accumulation of complex knowledge, but as the ability to simplify, clarify and democratise information
Learn to use data and improve your skills with helpful tips on all things Microsoft Excel 365 and other Business Intelligence systems.
Learn to use data and improve your skills with helpful tips on all things Microsoft Excel 365 and other Business Intelligence systems.
The 3 Excel Functions EVERY Data Analyst Needs to KNOW
Find out the top 3, must know Excel formula and functions that every data analyst should know.
Check out down below for some more advanced tutorials on these functions.
XLOOKUP - Advanced tutorials
th-cam.com/video/VFW50KwsMrI/w-d-xo.html
th-cam.com/video/Y4v-Q-xrjww/w-d-xo.html
SUMIF - Advanced tutorials
th-cam.com/video/qTNBw9XkoWM/w-d-xo.html
FILTER FUNCTION - Advanced tutorials
th-cam.com/video/hdAoJ9kO07s/w-d-xo.html
th-cam.com/video/Au-j0ytEqF4/w-d-xo.html
th-cam.com/video/1_OcLXaWXbo/w-d-xo.html
th-cam.com/video/SdUQsNoUMjk/w-d-xo.html
Check out down below for some more advanced tutorials on these functions.
XLOOKUP - Advanced tutorials
th-cam.com/video/VFW50KwsMrI/w-d-xo.html
th-cam.com/video/Y4v-Q-xrjww/w-d-xo.html
SUMIF - Advanced tutorials
th-cam.com/video/qTNBw9XkoWM/w-d-xo.html
FILTER FUNCTION - Advanced tutorials
th-cam.com/video/hdAoJ9kO07s/w-d-xo.html
th-cam.com/video/Au-j0ytEqF4/w-d-xo.html
th-cam.com/video/1_OcLXaWXbo/w-d-xo.html
th-cam.com/video/SdUQsNoUMjk/w-d-xo.html
มุมมอง: 3 958
วีดีโอ
Excel XLOOKUP CONTAINS partial match - Using Wildcards
มุมมอง 1.2Kหลายเดือนก่อน
Learn how to use the XLOOKUP function in Excel when searching for a partial match. It is an If text contains Xlookup function, or an XLOOKUP(Contains) function. See Below my other Excel (IF Contains) functions below: =FILTER(Contains) th-cam.com/video/hdAoJ9kO07s/w-d-xo.html =FILTER(Does Not Contain) th-cam.com/video/SdUQsNoUMjk/w-d-xo.html =IF(Contains) th-cam.com/video/k0ww0aGFeoE/w-d-xo.html...
Excel - Split text to a vertical list using my REVERSE Concatenate formula
มุมมอง 584หลายเดือนก่อน
Most people have heard of the concatenate formula in Excel that combines a number of cells in to one cell. BUT, what if you wanted to reverse this process create a list by splitting one cell. This can be done in what I call the 'Reverse Concatenate' formula. It combines functions already availabe in Excel to allow the user to split cells, using a delimiter of their choice, and creating a list. ...
⌚Everything you need to know about 🕝TIME🕒 in Excel!
มุมมอง 3312 หลายเดือนก่อน
How do you use time in excel to sum hours, or subtract different times in an excel formula? This video will show you multiple examples on how to use time in Microsoft Excel by using 'time format' as well as converting it to 'number format'. The video will show you how to use time, hours and minutes in formula in Excel so you can create calculations. There are 3 examples in this video of how to ...
Adding a CHECKBOX ✅ is now easier than ever. LEVEL UP your spreadsheets!
มุมมอง 3692 หลายเดือนก่อน
Checkbox in EXCEL. Brand new addition to Excel, the checkbox feature is now easier than ever. This Video will show you how to add checkboxes into your spreadsheets and then format cells and formulas based on these checkboxes. Brand new feature in EXCEL - Available Now! What to learn more about the FILTER function in Excel. Check out these videos: Filter Multiple Pages th-cam.com/video/Au-j0ytEq...
Extract Unique lists in Excel - Including IF conditions
มุมมอง 2.5K3 หลายเดือนก่อน
This video is going to demonstrate how to extract the unique values or lists from a table or dataset in Excel by using a formula. It is going to be a dynamic Unique function. There will also be a more advanced formula that shows you how to extract a unique list that has an additional 'if' or 'where' condition. Write in the comments if there is another formula that is causing you problems that y...
Extract Text Like a PRO Between Two Characters in Excel
มุมมอง 1.3K4 หลายเดือนก่อน
This video demonstrates how to build a textbetween function to extract text from the middle of a cell. We will show you how to use the new textbefore and textafter funtions, and then how to use them to build a textbetween funtion. You can use these functions to extract the first word, last word, or words in the middle of a cell. Check out my other playlists below: PIVOT TABLES: th-cam.com/video...
Fuzzy Lookup in Excel - Address data cleansing example
มุมมอง 7634 หลายเดือนก่อน
How to download, install and use fuzzy lookup in EXCEL to clean data. Fuzzy matching addresses from 2 different data sources. Approximate matching of data tables in EXCEL using Fuzzy Lookup. Download site: www.microsoft.com/en-au/download/details.aspx?id=15011 Check out my other playlists below: PIVOT TABLES: th-cam.com/video/RufENOZErg8/w-d-xo.html&pp=gAQBiAQB EVERYTHING about the FILTER FUNCT...
EXCEL - Sum or Count Values based on CELL COLOR
มุมมอง 9435 หลายเดือนก่อน
Learn in 2 easy steps on how to create a new formula in excel to count or sum values, based on the cell colour. Visual basic formula to add... Module 1: Function Cellcolor(rng As Range) As Long Application.Volatile Cellcolor = rng.Interior.Color End Function This Workbook: Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) Application.Calculate End Sub Check ou...
2-Way Xlookup with multiple criteria in EXCEL
มุมมอง 5K6 หลายเดือนก่อน
he Xlookup to perform a 2 way lookup in Excel and using to criteria to get the desired result. Other playlists: PIVOT TABLES: th-cam.com/video/RufENOZErg8/w-d-xo.html&pp=gAQBiAQB EVERYTHING about the FILTER FUNCTION: th-cam.com/video/1_OcLXaWXbo/w-d-xo.html&pp=gAQBiAQB EXTRACT first word, 2nd word, last word, numbers, letters: th-cam.com/video/WJAuBmx2fYA/w-d-xo.html&pp=gAQBiAQB 2 MINUTE Formul...
FILTER Function Excel = Top 10 with Dynamic Filtering & Sorting
มุมมอง 3.4K7 หลายเดือนก่อน
Use an excel formula to extract the top 10 from a list. It can be top 10, top 100, top N, top anything. It is dynamic and easy. Learn everything you need to know about EXCEL's 'Filter' function from this playlist: th-cam.com/video/1_OcLXaWXbo/w-d-xo.html&pp=gAQBiAQB Want to improve your skills with excel formulas. Check out my favourite functions explained in just 2 minutes: th-cam.com/video/k0...
ONE 1️⃣ simple EXCEL Formula to Merge 100's of Excel Sheets
มุมมอง 3K7 หลายเดือนก่อน
Use a simple EXCEL formula to combine data from multiple sheets, from multiple tabs, or multiple tables of data. Vstack Formula explained Learn everything you need to know about EXCEL's 'Filter' function from this playlist: th-cam.com/video/1_OcLXaWXbo/w-d-xo.html&pp=gAQBiAQB Want to improve your skills with excel formulas. Check out my favourite functions explained in just 2 minutes: th-cam.co...
EXCEL - Filter(Contains) Function. Filter your table with just part of the description
มุมมอง 17K8 หลายเดือนก่อน
This video will clearly demonstrate how to use the filter(contains) formula. It will allow you to search through large datasets and return values that contain text, or a value that you specify. File location: www.etsy.com/au/listing/1681297976/filtercontains-excel-formula-digital?click_key=4401d986453504c70a98d76fee8b16185eb18eff:1681297976&click_sum=2c35480e&ref=shop_home_active_1 Other Filter...
1 Minute EXCEL. How to Sort Data
มุมมอง 9278 หลายเดือนก่อน
Learn how to sort data in Excel using a Sort Formula or Sort Function. Or simply sort the data using the ribbon in excel. This is a short 1 minute Excel Tutorial showing you multiple ways on how to sort data. WORKBOOK FILE LOCATION: www.etsy.com/au/listing/1664013490/excel-basics-workbook?click_key=512b0cce3a0c546d150dce7e5cff8cbde24c355c:1664013490&click_sum=5ccf462e&ref=shop_home_active_1 EXC...
1 Minute EXCEL. Create a Bar Chart
มุมมอง 6008 หลายเดือนก่อน
Learn the basics of creating a bar chart or column chart in Microsoft Excel. Learn how to make charts and graphs in 1 minute. Beginner Bar and Column Chart Tutorial th-cam.com/video/dpk_8wfoaTQ/w-d-xo.html WORKBOOK FILE LOCATION: www.etsy.com/au/listing/1664013490/excel-basics-workbook?click_key=512b0cce3a0c546d150dce7e5cff8cbde24c355c:1664013490&click_sum=5ccf462e&ref=shop_home_active_1 EXCEL ...
1 Minute EXCEL Formula - Calculate sales growth
มุมมอง 1K9 หลายเดือนก่อน
1 Minute EXCEL Formula - Calculate sales growth
1 Minute EXCEL. How to use the IF formula
มุมมอง 1.4K9 หลายเดือนก่อน
1 Minute EXCEL. How to use the IF formula
Conditional Formatting based on another Cell - Excel conditional formatting using Formula
มุมมอง 8K10 หลายเดือนก่อน
Conditional Formatting based on another Cell - Excel conditional formatting using Formula
EXCEL Column Charts 📊 Made Easy for Beginners in 8 Minutes
มุมมอง 33211 หลายเดือนก่อน
EXCEL Column Charts 📊 Made Easy for Beginners in 8 Minutes
Indirect Function - Use Sheet name from Cell Reference
มุมมอง 8Kปีที่แล้ว
Indirect Function - Use Sheet name from Cell Reference
Drop Down Lists in EXCEL are now easier than ever. Level Up your spreadsheet!
มุมมอง 2Kปีที่แล้ว
Drop Down Lists in EXCEL are now easier than ever. Level Up your spreadsheet!
EXCEL - Get LAST word - using TEXTAFTER formula
มุมมอง 12Kปีที่แล้ว
EXCEL - Get LAST word - using TEXTAFTER formula
FILTER FORMULA - does NOT contain - EXCEL
มุมมอง 39Kปีที่แล้ว
FILTER FORMULA - does NOT contain - EXCEL
Excel FILTER FUNCTION across multiple sheets
มุมมอง 90Kปีที่แล้ว
Excel FILTER FUNCTION across multiple sheets
Dynamic sorting Excel Graphs - Automatic sorting Excel Charts
มุมมอง 26Kปีที่แล้ว
Dynamic sorting Excel Graphs - Automatic sorting Excel Charts
Thank you very much❤❤❤
No problem. This is a very useful feature. Do you think you will use it often?
Perfect 👍 please share data file for practice
Very Good. Thanks.
Nice one. Thanks for the comment.
This is amazing, just was able to do this in some work! Thanks.
Great to hear this was helpful.. Thanks for the feedback.
Visibility is not clear.
Thank you! Clear instructions and examples, very easy to follow. I believe these will really help me at work!
Thanks for the comment and feedback. I use these all the time.
Data set sir
Is there option of sorting in the graph?
What about now if I want to maintain the text in columns and delete the ones in rows to avoid duplicating… how can I do so… pls
What about if I want to do the reverse pls
Hi there, thanks for watching. I have a video that shows how to do a reverse concatenate. Check it out. th-cam.com/video/AI6Bvqx5DE8/w-d-xo.html
Very helpful. Thanks!💛
So glad to hear that the video helped. Thanks for taking the time to write a message.
Simple, In an Easy to Understand Format, and Straight to the Point! LOVE IT!!! Thank you so much!!
Wow, thanks for the awesome feedback. Happy you found this solution!
OMG! Thank you!
Ha, im happy you found my video.
I have a question, Im creating this spreadsheet for my wife for work. Two of the columns are Issue Date and Expiration Date, and I'm using the DATE formula on this video for the expiration date which is Issue Date + 120 months (=DATE(YEAR(J7),MONTH(J7)+120,DAY(J7)). The issue im having is, as soon as i put this formula on the expiration date cells, a date comes out even though I have not put any entry yet on the Issue Date.
Hi there, thanks for watching the video. Yes, excel automatically counts a value of zero, or blank, as the date 0/01/1900. So when you are dragging the formula down it is adding 120 months to that date and giving you a value. A way around this is to nest the formula in an IF formula... If the value is 'blank', then return nothing, otherwise complete the formula. This might work for you. =IF(J7="","",DATE(YEAR(J7),MONTH(J7)+120,DAY(J7))). Let me know if it works for you?
Ahh lifesaver! You saved me at my office work, thank you so much!
That's awesome to hear! I really like using pivot tables. Thanks for leaving a comment.
My latest conundrum. How to do this across multiple worksheets? I know it involves using VSTACK, but not sure how to smash them together??
Hey hey. Take a look at this video, that shows you how to filter across multiple sheets. You could use the contains formula as part of this. th-cam.com/video/Au-j0ytEqF4/w-d-xo.html
@@datauntelligence Thanks, I did look at that video, but I can't get the two to work together. This is what I tried: =FILTER(VSTACK(sheet1:Sheet2!A4:E200),(VSTACK(sheet1:Sheet2!E4:E200)<>""))*(VSTACK(sheet1:Sheet2!A4:E200,ISNUMBER(SEARCH(E1,E4:E300)),"No entries")) OR =FILTER(VSTACK(sheet1:Sheet2!A4:E200),(VSTACK(sheet1:Sheet2!E4:E200)<>""))*(A4:E200,ISNUMBER(SEARCH(E1,E4:E300)),"No entries"). But neither worked!
A formula to highlight the text searched for would also be useful! 🙂
Ah ha, yes. That would be good. You must use spreadsheets a lot?! I would do this by adding in some 'conditional formatting' based on another cell. You can format cells (shade/color) based on another cell, or a reference cell. This video shows shows you how to do it based on numbers, but you can change it to reference a cell with text. Maybe it will give you some ideas. th-cam.com/video/uWwAbFd2Ms8/w-d-xo.html
Really useful, using it to filter consultation responses!
Yeah, I agree. I use this formula all the time. Thanks for the comment, it's great to see that it has helped you out.
I want to use the same table but use one column if tax filing status is 'married' and another column if the tax fling status is 'single' then depending on the state selected and tax filing status, then it would be determined on income range.... Yikes Help!
might be easier if we jump on a zoom so i can show you
Can we combine "multiple contains" and "does not contain" in one formula
Hi, yes you certainly can. This video shows you how to combine multiple 'and' 'or' filter conditions. You can apply the same principle to filter contains and does not contain combinations. th-cam.com/video/EXF1ewsrGfU/w-d-xo.html
sort function is not available in older excel bro
Microsoft office 2010 not showed text after in formula
Hey, yes that formula is only available in newer versions of Excel. I do have another video to get the last word with older editions of Excel. Try this out. What do you think? th-cam.com/video/qAIgilKXaAk/w-d-xo.html
How does this work if your using defined names and multiple columns
Thank you so much for holding the fluff ✨
Haha, no problem. I try to keep my videos to the point. Thanks for noticing
Thank you!
No problem. Do you use charts often in Excel?
Worst explanation, not working.
Thank you So much Sir
No pronblem. Thanks for the comment. Do you use pivot tables often?
You can avoid using the indirect function by using '=MID(A2,SEQUENCE(1,LEN(A2)),1)' to extract the characters.
Thank you. 61 and still learning!
Nice one! Keep going.
thank you sir i'm spend a lot of time some another video but i got in this video again thank you very much
Hi. Glad you found the video. Pivot tables are great once you learn more about them.
Sooper
Hey, thanks for the comment
help it doesnt work when i click the grap option after highlighting it the info doesnt go to the graph
Hey, If I have column as By MCX Bill for 13-05-2024 By MCX Bill for 14-05-2024 FO BILL FOR FO BILL FOR By MCX Bill for 15-05-2024 FO BILL FOR and I want to extract either FO or MCX, can you help me with the correct formula? It keeps extracting FO from the word"FOR". :(
Great tutorial! Very well done. I was able to easily follow along and create a consolidated to-do list across multiple worksheets and then filter that list based on whether I had flagged the to-do as something I wanted to work on today. Question - Is it now possible to format this dynamically generated table?
That's great. Hopefully it came in handy. Unfortunately there isn't currently a way that automatically brings over the format from the original tables your filtering. You may need to format the destination cells before or after the combined file. If there is anyone else out there with a solution, let us know?!
Instead of grouping year and month like that, how do I display the dates in this format: Jan 2018, Feb 2018 and so on?
If we want to find people who is over 50 old ?
Hi there. You can go to about 5:30 in the video. It shows you how to add 2 conditions. In your case, just include 1 condition where age>50.
Hi first of all thank you , i want to ask how can i used calculated item like this growth formula in grand total row also , this formula does not go down to grand total row in the last
Total clickbait...video is 2 mins 15 seconds...😊
Haha, You got me!
Is there a version of this for Sheets in Chrome?
Exactly what I was looking for
Nice. I'm glad you found my video.
What to do when suppose if condition is true than we want to multiply some value and if false than want to multiply by other digits
Good question. In this case, replace "mints" with the true outcome you want... for example A2*15. Again for the result of false, place the formula in the space instead of "".
For bonus points, calculate the total or average hours worked from A2 to Sunday.
does it work on Google Sheets? it had result on "ERROR, FILTER has mismatched range sizes. Expected row count: 998. column count: 1. Actual row count: 1, column count: 1."
very good, thank you
No problem. Thanks for the comment. How often do you use excel?
@@datauntelligence very often, but I have an older version which does not support "unique".
Ah yes, Excel seems to update often. It's hard to keep up with what's new.
SUPPER HELPFULL
Great, thanks for the comment.
Let me guess...you've been trying python in excel
Yeah, python in excel is a good addition. Have you tried it? What do you think?
@@datauntelligence I’ve only tried the basic things at the moment. It’s slightly different from what I’m used to
Genius!!
Ha, thanks for the comment.
I watch a lot of youtube videos for excel issues. Yours was one of best ones! Thank you!
Thanks for the top comment. Great to hear the videos have helped you out!
Why did you have to use the CHOOSECOLS Function? Is it not possible to have FILTER(B1:C149,D1:D149=""Mouse")
I love mixing and matching formula to find a solution I need. Great advice though to make the function even shorter.
Hello I have roster I need a formula I have this formula =count if(i8:i49,"E*") I did the removing E for EL but the first for E works but when I the and EL it doesn't pick up the shifts I put as EL I don't know how to adjust the formula to pick up all the shifts as EL but when use AND for awaking night the for waking nights it works? Is it because there E in both formula and it doesn't recognize the EL. It recognize it as E not EL by so doing giving me wrong data
Hello, my work has a lot of compter generated data, that data is mostly divided in parts with inverted commas ("), how can i use them as delimiters? is there a way to use inverted comma itself as a delimiter
Hi there, this is a weird excel quirk, but if you want to use " as the delimiter, you need to enter " four times. So for example, if you wanted use the textafter function for text1"text2 in cell (a1) you would use =textafter(a1,"""") the result would be text2. Don't forget to like and subscribe 😀
@@datauntelligence really appreciate the help Thanks