![Computergaga](/img/default-banner.jpg)
- Видео 689
- Просмотров 44 041 474
Computergaga
Великобритания
Добавлен 26 сен 2010
Welcome to my channel where you can expect videos on Excel tips and formulas to help you in your day to day work.
Videos on using the latest Excel features, formulas, advanced Excel tips, VBA and Power BI.
If you have a question, please ask. I try to reply to as many comments as I can. My apologies if I do not respond. It is very difficult to respond to everybody.
Who am I?
My name is Alan, I'm a Microsoft MVP and I have been training people how to get the most from Excel for over 20 years. I am the author of two Excel books "Advanced Excel Success" and "Advanced Excel Formulas".
I love working with data and helping people improve their Excel skills and save time.
I live in a town called Ipswich in the UK and I'm a father to two wonderful kids.
Videos on using the latest Excel features, formulas, advanced Excel tips, VBA and Power BI.
If you have a question, please ask. I try to reply to as many comments as I can. My apologies if I do not respond. It is very difficult to respond to everybody.
Who am I?
My name is Alan, I'm a Microsoft MVP and I have been training people how to get the most from Excel for over 20 years. I am the author of two Excel books "Advanced Excel Success" and "Advanced Excel Formulas".
I love working with data and helping people improve their Excel skills and save time.
I live in a town called Ipswich in the UK and I'm a father to two wonderful kids.
Get Excel to Write Formulas FOR YOU 🔥 (Formula by Example in Excel)
Get Excel to write formulas for you from given examples.
🔔 SUBSCRIBE if you’d like more tips and tutorials like this.
📢 Please leave me a COMMENT. I read them all!
🎁 SHARE this video and spread the Excel love. Or if you’re short of time, please click the 👍
Excel Formula by Example can write formulas from given examples. That's right! Give Excel the answer and Excel will write your formula.
This feature is currently in Excel Online only.
Connect with us!
LinkedIn ► www.linkedin.com/in/alanmurray-computergaga/
Instagram ► computergaga1
Twitter ► computergaga1
TikTok ► www.tiktok.com/@computergaga
#excel365 #excel #exceltips
🔔 SUBSCRIBE if you’d like more tips and tutorials like this.
📢 Please leave me a COMMENT. I read them all!
🎁 SHARE this video and spread the Excel love. Or if you’re short of time, please click the 👍
Excel Formula by Example can write formulas from given examples. That's right! Give Excel the answer and Excel will write your formula.
This feature is currently in Excel Online only.
Connect with us!
LinkedIn ► www.linkedin.com/in/alanmurray-computergaga/
Instagram ► computergaga1
Twitter ► computergaga1
TikTok ► www.tiktok.com/@computergaga
#excel365 #excel #exceltips
Просмотров: 941
Видео
NEW Import Data from Web into Excel 🌐 (TWO Examples)
Просмотров 2,4 тыс.21 день назад
Get data from the web into Excel using the new web connector. This new feature of Excel utilise AI to interpret structures on a web page. In this video, we see two examples of importing unstructured data. The data on the web page is not in a table, but Excel offers suggested table structures for us to import from. And if that is not enough, a web from examples option to tell Excel what we need....
Change Number Format Button Styles in Excel 🔥 (DID You Know This?)
Просмотров 1,3 тыс.Месяц назад
Change the number format button styles in Excel to work the way that YOU need them to. 🔔 SUBSCRIBE if you’d like more tips and tutorials like this. The Accounting, Comma, and Percentage number format buttons on the Home tab can be modified to apply the format style that you need. This can then be saved to a template. This technique saves tons of frustration and time when formatting spreadsheets...
Create Charts with ChatGPT-4o from Excel Data 😮 (AMAZING!)
Просмотров 1,2 тыс.Месяц назад
Create charts with ChatGPT-4o from Excel data. This is awesome! In this video, we will upload an Excel file to ChatGPT-4o and ask it to create a specific chart. The Excel file contains 11 columns and more than 2000 rows, but ChatGPT delivers in seconds. In the video we then edit chart titles, add average lines, edit colours, remove chart elements and ultimately insert to chart to a PowerPoint s...
Copilot in Excel Tutorial - Create Formula Columns EASILY
Просмотров 2 тыс.Месяц назад
Copilot in Excel Tutorial - Create Formula Columns EASILY
REDUCE Function in Excel (Extract Uppercase Words ONLY 🚀)
Просмотров 1,5 тыс.Месяц назад
REDUCE Function in Excel (Extract Uppercase Words ONLY 🚀)
Power Query UI Magic 🪄 (NO CODE Data Transformations)
Просмотров 1,2 тыс.2 месяца назад
Power Query UI Magic 🪄 (NO CODE Data Transformations)
XLOOKUP Case Sensitive in Excel | Match Text Exactly
Просмотров 1,1 тыс.3 месяца назад
XLOOKUP Case Sensitive in Excel | Match Text Exactly
LAMBDA Function in Excel (CUSTOM Excel Formulas 😮)
Просмотров 2,5 тыс.3 месяца назад
LAMBDA Function in Excel (CUSTOM Excel Formulas 😮)
Visual Calculations in Power BI ⚡AMAZING (DAX is Easier than EVER)
Просмотров 1,1 тыс.3 месяца назад
Visual Calculations in Power BI ⚡AMAZING (DAX is Easier than EVER)
Reference Sheets in Excel VBA by Name (the BEST WAY)
Просмотров 1,1 тыс.4 месяца назад
Reference Sheets in Excel VBA by Name (the BEST WAY)
Sum Cells with Formula ONLY in Excel 🚀 (Excel Formula Trick)
Просмотров 2,4 тыс.5 месяцев назад
Sum Cells with Formula ONLY in Excel 🚀 (Excel Formula Trick)
Highlight Dates DUE Within 3 Months of Expiry Date 🕛 | Excel Tutorial
Просмотров 3,1 тыс.5 месяцев назад
Highlight Dates DUE Within 3 Months of Expiry Date 🕛 | Excel Tutorial
Clean Bad Data with ChatGPT for Excel 🤖 AMAZING!!
Просмотров 2,4 тыс.5 месяцев назад
Clean Bad Data with ChatGPT for Excel AMAZING!!
Frequency Function in Excel (Frequency Distribution Table Made Easy 🔥)
Просмотров 1,9 тыс.6 месяцев назад
Frequency Function in Excel (Frequency Distribution Table Made Easy 🔥)
Extract from PDF to Excel with Copilot 😲 (So EASY)
Просмотров 9 тыс.6 месяцев назад
Extract from PDF to Excel with Copilot 😲 (So EASY)
Dynamic SUM Formula with XLOOKUP (feat TAKE and DROP)
Просмотров 3,2 тыс.8 месяцев назад
Dynamic SUM Formula with XLOOKUP (feat TAKE and DROP)
Clear ALL Filters Button in Power BI (ONE Click to Remove Filters)
Просмотров 7 тыс.8 месяцев назад
Clear ALL Filters Button in Power BI (ONE Click to Remove Filters)
Magical Ctrl + Enter Excel Shortcut 😮 (THREE Examples)
Просмотров 2,7 тыс.9 месяцев назад
Magical Ctrl Enter Excel Shortcut 😮 (THREE Examples)
Find Matches in Two Lists (Extract Matches AUTOMATICALLY) 🔍🪄
Просмотров 2,8 тыс.9 месяцев назад
Find Matches in Two Lists (Extract Matches AUTOMATICALLY) 🔍🪄
Add Leading Zeroes in Power BI (Text and Number Values)
Просмотров 5 тыс.9 месяцев назад
Add Leading Zeroes in Power BI (Text and Number Values)
TWO COLUMN Lookup in Excel with the XLOOKUP Function
Просмотров 10 тыс.10 месяцев назад
TWO COLUMN Lookup in Excel with the XLOOKUP Function
Case Sensitive XLOOKUP Formula in Excel
Просмотров 1,4 тыс.10 месяцев назад
Case Sensitive XLOOKUP Formula in Excel
Working Days Between Two Dates in Power BI (NETWORKDAYS 🗓️)
Просмотров 7 тыс.11 месяцев назад
Working Days Between Two Dates in Power BI (NETWORKDAYS 🗓️)
LOOKUP Nth Match in Excel (Includes BONUS Content to SUM to Nth Match)
Просмотров 2,2 тыс.Год назад
LOOKUP Nth Match in Excel (Includes BONUS Content to SUM to Nth Match)
Excel BYROW and BYCOL Functions (Two Examples)
Просмотров 4,3 тыс.Год назад
Excel BYROW and BYCOL Functions (Two Examples)
Combine Files with DIFFERENT Headers in Power Query | TWO Examples
Просмотров 13 тыс.Год назад
Combine Files with DIFFERENT Headers in Power Query | TWO Examples
Excel TEXTBEFORE and TEXTAFTER Functions (SEVEN Examples)
Просмотров 3,9 тыс.Год назад
Excel TEXTBEFORE and TEXTAFTER Functions (SEVEN Examples)
DYNAMIC Images in Power BI (in Tables, Tooltips & Slicers)
Просмотров 8 тыс.Год назад
DYNAMIC Images in Power BI (in Tables, Tooltips & Slicers)
Filter Based on Cell Values in Power Query | DYNAMIC Power Query Filters
Просмотров 12 тыс.Год назад
Filter Based on Cell Values in Power Query | DYNAMIC Power Query Filters
Hello help me plesa
Thank you so much, I tried and it worked!
Bro thank god, idk why it was so hard to find an answer to this question. I thought it was Shift, because I thought that was what I used to use in every other program, but apparently it is Alt in Excel. Thank you!
I don't have the Form object available to insert. I can only insert a Module or Class Module
thanks..
Thanks very so much
I really need to sort out something. I need to highlight a complete row, based on the LATEST or most recent date that appears in a Column. In order to highlight the latest date cell corresponding the column it is ok, and I use the formula MAX that works flawlessly; but when I try to highlight the complete Row the problems appear and I can't find a solution. Can you give me any help ?
is there a way for this to work on multiple different sheets? because it only worked on sheet 1, and i need it to work on multiple different sheets on the same file at different times
Thanks for this video, do we have an option to select multiple countries?
Thank you !
Thank you Dear For Your Vedeo
Which is the extension working in the background?
Awesome
Hi, this is extremely helpful. I need a dynamic sum and criteria range, so that when I copy the formula over, the sum range and criteria range will change. Is there a way to do that in this example? It would reference a cell for the A:A and B:B.
Cool tutorial! Thank you. Bombastic ! I have learned how to make a function out of a procedure!
Nice British accent)
hello I made a product catalog on Google sheet then I brought this catalog into WordPress I was wondering if it would be possible thanks to the combination of Google Sheet APIs, WordPress and the use of n8n software during the sequence that we are going to see and how many times would it be possible for all the keywords present in the description column of each of the products to be automatically replaced by good keywords? and the same with the images
❤👍
This was so helpful.
SAVED MY LIFE THANKS
If you wanna count the start of the week from Sunday, you can use: (date - starting date -3)/7+1
Excellent! Thanks!
🫡
Another solution instead of List.Generate that can simply be used as a Custom Column: = Table.AddColumn(#"Replaced Value", "Custom", each List.Distinct(List.Transform({Number.From([Start date])..Number.From([End date])}, each Date.StartOfMonth(Date.From(_)))))
what if you want the music to continue playing after that slide?
Power query is the best tool ever for cleaning data , easy to learn it and very powerful and with knowledge of m code , you can do anything
Oh yes!!!
That’s nice , will check this out , it’s somehow similar to column by example in power query
Yes, sure is Chahine. Thanks!
The second scenario was a great eyeopener. thanks for sharing.
You're very welcome, Samuel.
I've gotta give this a try.
Oh yes!!! It's good. It can't do lookups like Copilot, but it can do calculated columns great.
@@Computergaga It wrote this formula for me. It strips off the letters at the end of a string, where the string starts with a varying number of numbers and ends with a varying number of letters. Pretty impressive. =RIGHT([@ID], LEN([@ID]) - (INDEX(FILTER(ROW(INDIRECT("1:" & LEN([@ID]))), (65 <= CODE(MID([@ID], ROW(INDIRECT("1:" & LEN([@ID]))), 1))) * (CODE(MID([@ID], ROW(INDIRECT("1:" & LEN([@ID]))), 1)) <= 90)), 1) - 1))
Just looking at that, scares me
What if I want to find the max value for a customer id and not the last value
You want the MAXIFS function
Thanks, very interesting, is this a bit like Flash Fill? Paul
Yes, except that it writes a formula while Flash Fill is a one-time only feature. Also Flash Fill has a button while Formula by Example is currently executed automatically as you type. Would be great to have a button form it.
Could you please reshare the file? Unfortunately the link does not work
Sorry about this. The link goes to Wyn's SharePoint site and seems like it has been removed. I don't have a copy of the files. I've removed the link from the description.
51:25 Well, I;m not sure whether it would work. The column names are hardcoded so for example when there's '2021 AC' it wouldn't be included edit: I think instead of 'Exppand' we can use something like this: = Table.Combine(#"Invoked Custom Function"[fnFiltersColumns])
This tutorial is so clear and easy to understand. I have some problem, the table result only show 10 data per row (because the website default is show 10 row, but we can change to show all) how can we fetch all data?
Awesome Sir
Thank you very much ☺️
Excellent and it looks so simple! If I might suggest, could you preface the announcement of the videos in your email to advise the version/s of Excel to which the tutorial relates and functions (eg, 2021 v 365). Someone else mentioned "data scraping" ~ what if the website requires login and password (eg, for a bank statement or share/stock portfolio)?
Thank you! And thank you for the feedback. For the login and password, a window should appear asking for authentication when you connect to the site for the first time.
@@Computergaga Yes, thanks Alan. But can the login process be completed programmatically?
I seem to have failed to subscribe somewhere along the line. I would have loved to attend this. For me any formula is a potential Lambda that has failed to dress properly, ready to be presented to the world. Without a named Lambda to wrap it, there is no 'statement of intent' to tell the user what it attempts to achieve. Any Lambda function is 'just' programming and complexity versus over-simplicity is basically a balance to be struck with every formula and function. The Microsoft Excel development team must face this dilemma every time they release a new function. And then I bitch about the shortcomings
I know how much you love LAMBDA functions Peter ☺ You can check if you're subscribed. YT do not seem to use the Subscriber facility so much anymore. Joining the meetup group is more reliable.
That’s really cool, didn’t know you could do that
Oh yes!!!! Thanks, Qasim.
Need an introduction to LAMBDA first. Check out this video - ruclips.net/video/bq82SKt5fC4/видео.html
Wow! That's great and didn't know about it at all. The transform by example is super. Thanks a lot!
My pleasure, Erica.
Great presentation, Marco. Thanks for the shoutout. 😎
Thank Marco! Very entertaining and thought provoking presentation.
I shall finish watching this tomorrow, I think I've guessed the method, as I've done similar in in PQ with List.accumulate.
Hurray! Alan is the new king of the United Kingdom. Thanks to LAMBDA!😊
LAMBDA can do anything 🤴🏼
@@Computergaga Absolutely! LAMBDA is game changer.
This is exactly what I needed. Thank you for this!
No worries!
Sometime I get a '0' when i use this in the option. Can you please explain why this happens?
A 0 sounds like it is return a value form a blank cell. I would check the range used in the formula.
Hocam emeğinize sağlık Excelde uzman değilim Excel 2016 kullanıyorum 2016 versiyona göre formül ile yapabilimisiniz
You're welcome! In Excel 2016, you're best looking at a Power Query approach. I'm not sure on a formula for that version.
Ruined by the music. How do I turn it off or do I need to unsubscribe?
Thanks for the feedback. I'm producing long form and short form content and sometimes in short form I'll opt for music over voice. This is mainly as YT limit it to 60 seconds and I cannot narrate at that speed so opt for a longer detailed version. You can tune off sound in YT, that is no problem. There is a mute option as you watch it.
@@ComputergagaSo humble and polite answer given by sir...such a good soul you are
Didn't quite get the step where you insert che checkboxes. I added both ActiveX and Form checkbox control to the ribbon, but I don't get either to paste any checkboxes into the cells I've selected. How does that work?
These new check boxes are in the beta version of Excel 365. They should be GA soon. You can accomplish the sound with the checkbox form control. They are just more awkward and you will need to link each one to a cell.
Thanks Alan very clear explanation, I just subscribed to your channel. One more question please, how about if want to count the duplicate in the whole sheet? lets assume, we have 3 companies and I want to know the employees registered in more than one company using their Identity Number?
You're welcome! Thanks. You could use the COUNTIFS function for this. If the ID number was in column A, you could use =COUNTIFS($A$2:$A$50,A2). This will count their occurrences. This is a brief example that can be adapted to needs. I have a video on this channel somewhere.