- Видео 96
- Просмотров 1 578 264
Data Untelligence
Австралия
Добавлен 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
ruclips.net/video/VFW50KwsMrI/видео.html
ruclips.net/video/Y4v-Q-xrjww/видео.html
SUMIF - Advanced tutorials
ruclips.net/video/qTNBw9XkoWM/видео.html
FILTER FUNCTION - Advanced tutorials
ruclips.net/video/hdAoJ9kO07s/видео.html
ruclips.net/video/Au-j0ytEqF4/видео.html
ruclips.net/video/1_OcLXaWXbo/видео.html
ruclips.net/video/SdUQsNoUMjk/видео.html
Check out down below for some more advanced tutorials on these functions.
XLOOKUP - Advanced tutorials
ruclips.net/video/VFW50KwsMrI/видео.html
ruclips.net/video/Y4v-Q-xrjww/видео.html
SUMIF - Advanced tutorials
ruclips.net/video/qTNBw9XkoWM/видео.html
FILTER FUNCTION - Advanced tutorials
ruclips.net/video/hdAoJ9kO07s/видео.html
ruclips.net/video/Au-j0ytEqF4/видео.html
ruclips.net/video/1_OcLXaWXbo/видео.html
ruclips.net/video/SdUQsNoUMjk/видео.html
Просмотров: 3 958
Видео
Excel XLOOKUP CONTAINS partial match - Using Wildcards
Просмотров 1,2 тыс.Месяц назад
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) ruclips.net/video/hdAoJ9kO07s/видео.html =FILTER(Does Not Contain) ruclips.net/video/SdUQsNoUMjk/видео.html =IF(Contains) ruclips.net/video/k0ww0aGFeoE/видео.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 ruclips.net/video/Au-j0ytE...
Extract Unique lists in Excel - Including IF conditions
Просмотров 2,5 тыс.3 месяца назад
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,3 тыс.4 месяца назад
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: ruclips.net/vide...
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: ruclips.net/video/RufENOZErg8/видео.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
Просмотров 5 тыс.6 месяцев назад
he Xlookup to perform a 2 way lookup in Excel and using to criteria to get the desired result. Other playlists: PIVOT TABLES: ruclips.net/video/RufENOZErg8/видео.html&pp=gAQBiAQB EVERYTHING about the FILTER FUNCTION: ruclips.net/video/1_OcLXaWXbo/видео.html&pp=gAQBiAQB EXTRACT first word, 2nd word, last word, numbers, letters: ruclips.net/video/WJAuBmx2fYA/видео.html&pp=gAQBiAQB 2 MINUTE Formul...
FILTER Function Excel = Top 10 with Dynamic Filtering & Sorting
Просмотров 3,4 тыс.7 месяцев назад
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: ruclips.net/video/1_OcLXaWXbo/видео.html&pp=gAQBiAQB Want to improve your skills with excel formulas. Check out my favourite functions explained in just 2 minutes: ruclips.net/video/k...
ONE 1️⃣ simple EXCEL Formula to Merge 100's of Excel Sheets
Просмотров 3 тыс.7 месяцев назад
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: ruclips.net/video/1_OcLXaWXbo/видео.html&pp=gAQBiAQB Want to improve your skills with excel formulas. Check out my favourite functions explained in just 2 minutes: ruclips.n...
EXCEL - Filter(Contains) Function. Filter your table with just part of the description
Просмотров 17 тыс.8 месяцев назад
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 ruclips.net/video/dpk_8wfoaTQ/видео.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
Просмотров 1 тыс.9 месяцев назад
1 Minute EXCEL Formula - Calculate sales growth
1 minute EXCEL Formula - How to Rank
Просмотров 1,8 тыс.9 месяцев назад
1 minute EXCEL Formula - How to Rank
1 Minute EXCEL. How to use the IF formula
Просмотров 1,4 тыс.9 месяцев назад
1 Minute EXCEL. How to use the IF formula
Conditional Formatting based on another Cell - Excel conditional formatting using Formula
Просмотров 8 тыс.10 месяцев назад
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
Просмотров 8 тыс.Год назад
Indirect Function - Use Sheet name from Cell Reference
Drop Down Lists in EXCEL are now easier than ever. Level Up your spreadsheet!
Просмотров 2 тыс.Год назад
Drop Down Lists in EXCEL are now easier than ever. Level Up your spreadsheet!
EXCEL - Get LAST word - using TEXTAFTER formula
Просмотров 12 тыс.Год назад
EXCEL - Get LAST word - using TEXTAFTER formula
FILTER FORMULA - does NOT contain - EXCEL
Просмотров 39 тыс.Год назад
FILTER FORMULA - does NOT contain - EXCEL
Excel FILTER FUNCTION across multiple sheets
Просмотров 90 тыс.Год назад
Excel FILTER FUNCTION across multiple sheets
Dynamic sorting Excel Graphs - Automatic sorting Excel Charts
Просмотров 26 тыс.Год назад
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. ruclips.net/video/AI6Bvqx5DE8/видео.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. ruclips.net/video/Au-j0ytEqF4/видео.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. ruclips.net/video/uWwAbFd2Ms8/видео.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. ruclips.net/video/EXF1ewsrGfU/видео.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? ruclips.net/video/qAIgilKXaAk/видео.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