OfficeNewb.com
OfficeNewb.com
  • 85
  • 262 428

Video

Python While Loops
Views 16Year ago
Python If Statements
Views 24Year ago
Installing Python
Views 49Year ago
Downloading Python
Views 46Year ago
Art of Animation
Views 823 years ago

Comments

  • Gurutej Kaushik
    Gurutej Kaushik 12 hours ago

    Hey Kyle, great video. A powerful formula indeed. Could you please help me out in changing the weeks from 1 to 4, I tried using this formula in the name manager. =OFFSET('DYNAMIC SUM w CHART'!$B$2,0,'DYNAMIC SUM w CHART'!$C$2:$F$2,0,4) but it change.

  • Sanjeev Kumar
    Sanjeev Kumar 3 days ago

    Sir please make a full course from beginner to advance on power query like your other courses which is available on udemy

  • Sanjeev Kumar
    Sanjeev Kumar 3 days ago

    Hello sir do you recognize me ? I'm sanjeev and I'm also your student at udemy. Your method is very simple and easy to understanding keep it up sir

  • Kelsey Rock
    Kelsey Rock 7 days ago

    How do I use the FN4 shortcut in a mac laptop where I don't have function keys?

  • Siddharth Bhusari
    Siddharth Bhusari 9 days ago

    Wonderful, Thank you so much sir.

  • Laura Johnson
    Laura Johnson 9 days ago

    This was amazing! So simple yet so helpful. I wish I still taught Excel so I could incorporate all the great information you teach. Thank you for sharing!

  • Santhosh Ganesan
    Santhosh Ganesan 11 days ago

    im using office 2019 but i dont have xlookup, how to do this...?

    • OfficeNewb.com
      OfficeNewb.com 10 days ago

      The function is slowly being rolled out, current to Office Insiders and then to the general Excel users.

  • Belle Bristol
    Belle Bristol 11 days ago

    Thanks for the update and preview of what is to come.

  • William Billard
    William Billard 11 days ago

    A very useful, practical, and informative video. Thanks, Kyle!! As an Excel master teacher and explainer, you are one of the very best around!

  • Boubekeur Zeffane
    Boubekeur Zeffane 11 days ago

    Thank you for sharing, you can use subtotal in data tab to sum the totals at each change in the order

    • OfficeNewb.com
      OfficeNewb.com 11 days ago

      The Subtotal tool is great. But it doesn't work with the FORMAT AS TABLE command and disrupts further functionality of the list.

  • Ijeoma Afulukwe
    Ijeoma Afulukwe 11 days ago

    Great overview of XLOOKUP. I'm looking forward to try this out.

  • RickK Evil
    RickK Evil 11 days ago

    You might want to mention that doing it this way, the data table needs to be sorted so all your order numbers ar together

    • claude77573
      claude77573 11 days ago

      No, the data table does not need to be sorted by order number. If you sort by product, the last rows will still total by order number. It may be more logical to sort by order number, as the formula is computing running totals by that variable, but I found the running totals by order number will still be correct no matter how you sort the table.

    • OfficeNewb.com
      OfficeNewb.com 11 days ago

      Great catch! The sort order on the ORDER ID column is what greats the groups for the Running Total.

  • Pawan Patil
    Pawan Patil 11 days ago

    nicely explain very easy

  • ib032011
    ib032011 12 days ago

    Hi Kyle, I am keen to take up one of your course,can I know your Udemy course Microsoft Excel - Excel from Beginner to Advanced(www.udemy.com/microsoft-excel-2013-from-beginner-to-advanced-and-beyond/?couponCode=) , does it work for Office 365? Your course videos were recorded in 2013,will you update the course videos frequently ?

    • OfficeNewb.com
      OfficeNewb.com 12 days ago

      When I originally recorded the course it was with Excel 2013, but I am going through and updating the course with the latest version of Excel. The course will work with Office 365, currently Excel 2019. Below is a coupon code, good for a little while and for anyone that might be interested in a complete Microsoft Excel training course: www.udemy.com/microsoft-excel-2013-from-beginner-to-advanced-and-beyond/?couponCode=YT-0010

  • Rebecca Sanchez
    Rebecca Sanchez 12 days ago

    Excellent and clear explanation. Loved the comparison with VLOOKUP to understand the difference and enhanced abilities. Thanks!

  • Larmine Goba
    Larmine Goba 12 days ago

    Wow! What a wonderful function. Thanks for the clear explanation. Kyle, I love your teaching style. Can't stop going over those courses I did with you. Especially the Index and Match course I did some time ago. Thanks for the updates. Please keep posted. Some of us believe in you and your ability to teach content.

  • Sumeet Sahani
    Sumeet Sahani 12 days ago

    =LOOKUP(lookup_value,lookup_vector,result_vector) This function can be used to return the value left of the lookup value. The only difference is that this function lacks last two parameters of =XLOOKUP() office 2016

  • Ge We
    Ge We 13 days ago

    why are you in the video? Added value? 😣

  • Sudhagar83 Perumal
    Sudhagar83 Perumal 13 days ago

    Well explained.... Many thanks.

  • Siddharth Bhusari
    Siddharth Bhusari 13 days ago

    👍🏻👍🏻👍🏻👍🏻👍🏻 thank you so much sir!

  • Tyler Schwartz
    Tyler Schwartz 13 days ago

    I love the little noise you make at 6:55

    • OfficeNewb.com
      OfficeNewb.com 13 days ago

      That's funny, not sure how I made that noise. :)

  • aun manoi
    aun manoi 14 days ago

    Thank you very much I brought you course in Udemy but not start yet Ha ha ha .

    • OfficeNewb.com
      OfficeNewb.com 13 days ago

      No problem. You've got lifetime access to the course and all the content.

  • José Antônio Morato de Carvalho

    VLOOKUP with CHOOSE => Left search alternative: =VLOOKUP($B$4,CHOOSE({1,2},'Customer Info'!$C$2:$C$92,'Customer Info'!$A$2:$A$92),2,0)

  • Mohan Arole
    Mohan Arole 14 days ago

    Great tool, exicited to work with Xlookup...not sure how long it's gonig to take ...

    • OfficeNewb.com
      OfficeNewb.com 14 days ago

      Microsoft is slowly rolling it out, currently to Office Insiders.

  • don town
    don town 14 days ago

    Been using Excel since 1993(Ver.5) & never knew about this 'hidden' feature. Thanks!

    • OfficeNewb.com
      OfficeNewb.com 14 days ago

      It’s definitely a hidden feature. Glad you like it.

  • Jene Coy
    Jene Coy 14 days ago

    Thanks Kyle, Good explanation. When doing the search, instead of choosing 2-92 in the column, just click the entire column and it will save time. Later if you update the table, the formula still works for the added material.

    • OfficeNewb.com
      OfficeNewb.com 14 days ago

      Great tip. Formatting the list as a table would help with a dynamic range as well.

  • Aniekan Charles Ekong

    Thank you for the update and detailed explanation

  • scgrigsby
    scgrigsby 15 days ago

    From MS: Note: August 28, 2019: XLOOKUP is currently a beta feature, and only available to a portion of Office Insiders at this time. We'll continue to optimize it over the next several months. When XLOOKUP is ready, we'll release it to all Office Insiders, and Office 365 subscribers.

  • vermabalraj
    vermabalraj 15 days ago

    Hi, Kyle I highly appreciate the function you introduced Microsoft has in pipeline. Its' really great forward step and it will certainly make life easier. I have a question regarding to dynamically search a retrieving column so that excel can automatically search the column for that we need to add some other match function. Correct me if I am wrong.

  • Robert Sparkman
    Robert Sparkman 15 days ago

    Can't believe how long it took Microsoft to get this done...

  • Wayne Brown
    Wayne Brown 15 days ago

    If this site is for "newbies", could you please start an Access playlist from the get-go, instead of an advanced level?

    • OfficeNewb.com
      OfficeNewb.com 15 days ago

      Thank you for the suggestion. I’ll look to add some basic Access videos.

  • Seema Sharma
    Seema Sharma 15 days ago

    Thanks for the updates. Good explanation. You are the best Kyle👍🏻

  • WoodRodent
    WoodRodent 15 days ago

    Great explanation! What's up with the 1.34 billion comments RU-clip?

  • John Dunn
    John Dunn 15 days ago

    Mr. Pew’s enthusiasm for XLOOKUP has infected my excitement and curiosity, not only for XLOOKUP but also for VLOOKUP. Thank you for this insight.

    • OfficeNewb.com
      OfficeNewb.com 15 days ago

      If you haven't worked with VLOOKUP before, I highly recommend becoming familiar with it.

  • Cool Krish
    Cool Krish 15 days ago

    Unable to download sample file

    • OfficeNewb.com
      OfficeNewb.com 15 days ago

      The exercise file is located in the link at the bottom of the blog post in the video description.

  • rudolphg76
    rudolphg76 15 days ago

    So how is this different from index,match? I've stopped using vlookup a long time and have considered it obsolete. I've used index-match instead.

    • OfficeNewb.com
      OfficeNewb.com 15 days ago

      The XLOOKUP behaves similar to the INDEX/MATCH pair, without the need of nesting functions. It also gives you additional SEARCH_MODE options with the ability to search for the last result within the list by searching last to first.

  • Aditya Priyaranjan
    Aditya Priyaranjan 15 days ago

    THANKS FOR THE UPDATE! :D

  • Paramveer Sachdeva
    Paramveer Sachdeva 15 days ago

    I love and appreciate the video you shared sir, however i would like to add in, I'm also using office 365 business premium. Sadly, Google sheets is generations ahead and the features that I have been using in google sheets, like IMPORTRANGE, in G suite or google sheets (free features), i have been using them since over 3 years approx, are being added, that too with much less efficiency, NOW. these features have been existing in a database builder called FILEMAKER, which is from apple, and these are just stripped down versions of filemaker. Im getting very disappointed in excel, which I have been using since decades and I have been sending regular feedbacks to the company too, so maybe they listen. Ive also migrated to Google sheets, easier to collaborate and free features which are generations ahead of microsoft. im also a student of yours on udemy and I love your tutorials, which i rate as very good in quality.

    • Kristoffer Krause
      Kristoffer Krause 15 days ago

      Google sheeta has its place in the world, but I do not believe that world to be inside any truely secure data environment. Given the security issues that the end user ultimately opens due to week passwords, lack of multifactor authentication, or permanent sharing links... google docs just doesnt seem as safe as the excel on premiae documents that can be fully locked down both at a file level as well as an environment level for any security minded corporation. I will agree with you that there are functionality missing from Excel that google docs has, but the same can be said in the opposite direction. If you dig deep enough there is always fault to be found, but does that make any of the products you mentioned better or worse overall? I say its more of a preference based on factors of security, data limitations, environmental connectors, and accessibility. While I too use google docs, my use is limited to only personal items such as online gaming trackers to collaborate with my peers. Personally at work I utilize more SQL driven datasets with Excel being the down and dirty work horse for gathering expected results while I polish my queries. Remember you need the right tool for the job...

  • Kristoffer Krause
    Kristoffer Krause 15 days ago

    This seems like an ok update if still using vlookups. An Index/Match long solved this problem, and can utilize multiple lookup variables and ranges when used as an array formula. Xlookup while a long needed fix to the vlookups functional limitations, it still does not look to meet heavier lookup needs. However, the xmatch that is also coming soon looks to be an improvement for match especially with wildcard functionality built in...

    • brianxyz
      brianxyz 15 days ago

      @Kristoffer Krause If you look at the support documentation you'll see that these new functions are very powerful and do take advantage of dynamic arrays and the new calculation engine. support.office.com/en-us/article/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929?ui=en-US&rs=en-US&ad=US support.office.com/en-us/article/xmatch-function-d966da31-7a6b-4a13-a1c6-5a33ed6a0312?ui=en-US&rs=en-US&ad=US

    • Kristoffer Krause
      Kristoffer Krause 15 days ago

      @OfficeNewb.com is there an array formula version that would allow for multiple search criteria and match array combinations? If so then this would be a more exciting update than I give it credit. Real world data has more need for multi criteria searches these days, and using a concatenation to create a single column of data as well as its match criteria is archaic. Would love to see a built in formula with the same functionality of an index/match built in with it being optimized internally for excel, perhaps we could call it a zlookup since it would be the last lookup we would ever need...

    • OfficeNewb.com
      OfficeNewb.com 15 days ago

      Great insight. The XLOOKUP can also search horizontally, replacing the HLOOKUP, as well as search top to bottom or bottom to top. It also has improvements for wildcard searches.

  • Beau Dure
    Beau Dure 15 days ago

    Simply excellent. How did you define a column (without the header) so quickly?

    • OfficeNewb.com
      OfficeNewb.com 15 days ago

      I use the CONTROL + SHIFT + {ARROW KEY} to make the selections.

  • Anicet IRIE
    Anicet IRIE 15 days ago

    Thanks a lot!

  • Vinicius Naegele
    Vinicius Naegele 15 days ago

    Thanks for the update!

  • Derek Fingleson
    Derek Fingleson 15 days ago

    Very Cool !

  • Franquil Rodriguez Alonzo

    Excellent!

  • Talha Talha
    Talha Talha 22 days ago

    powerful tool ,, good explanation

  • OSMAN IDDRISU
    OSMAN IDDRISU 23 days ago

    Excellent, thanks

  • WoodRodent
    WoodRodent 24 days ago

    Great explanation for someone new to Power Query! Thank you.

    • OfficeNewb.com
      OfficeNewb.com 23 days ago

      You’re welcome! I plan on doing some more Power Query videos.

  • German Duterte
    German Duterte 24 days ago

    Sir what if the website needs to input username and password ?

  • Evan Dawson
    Evan Dawson 24 days ago

    Hey Kyle, How are you? Yet another Awesome Video from you!!!!

    • OfficeNewb.com
      OfficeNewb.com 24 days ago

      Glad to hear you’ve enjoyed the new video. Power Query is awesome!

  • Zéphyrus
    Zéphyrus 25 days ago

    These are great! Thanks for the laugh :)

  • coolag74
    coolag74 25 days ago

    The best explanation of this function...

  • Kim Hao Teong
    Kim Hao Teong Month ago

    It says "Reference isn't valid". Can anyone please help?

  • Mayank Gupta
    Mayank Gupta Month ago

    I have a data with standard columns in 4 workbooks. I have applied multiple pivots in one file and want to copy/move all those pivots in other workbooks as well. I tried the same, but when I copied the pivot to other workbook, then source data is reflecting of the initial work book. is there any means by which the copied pivot gets the source range from the new/current workbook ( I select complete sheet as source data).

  • John Paul Francisco

    Awesome tutorail.. Thank you. What if the website have to enter password and username to input first?

  • Ravi Kumar
    Ravi Kumar Month ago

    Great Brother you go really deep.,...exactly how to play with multiple field list without using power query..because what i thought before launching 2010 people use to get all there work done in excel 2007..how do they do that..

  • John G
    John G Month ago

    You nailed it! You really have an inventive mind. This is learning by doing at its best . It is written that "A student is not better than the teacher, but the student who has been fully trained will be like the teacher"(Bible Luke 6:40). Thanks for the best stuff on earth. And, now I fill like I can code like my teacher--like you. "Great teachers are hard to find." How many more tricks you got? What's the best macro vba book for beginners that you might recommend me to buy?

  • Tricia Donovan
    Tricia Donovan 2 months ago

    Thank you so much for your video. Can you clarify one thing? I am trying to combine multiple tabs that each have varied amount or rows, that will fluctuate each time it is being used. I am trying to have the individual tabs as a data dumb for someone else and need one tab to show all the data. However, both your and several other video show one label and different months values. I am trying to do tabs with checks that have been issued from different companies. Each tab showing CK#, CK Date, CK Amt, and Payee Name. I just need a master file without having to copy and paste to create it but cant seem to get that to happen in Excel. Can you advise?

  • Yusef Cream
    Yusef Cream 2 months ago

    You teach Excel exceptionally well. I'm in one of your class

  • Sandeep Kothari
    Sandeep Kothari 2 months ago

    Kyl, you are simply great!

  • John Jacob JinglehimerSchmidt

    Remember I when I was trying to do this like 9 years ago. Couldn't find anybody that had some clear tutorials. Talk about struggling to understand what I was doing. Now ..... I can find a plethora of vba tutorials out there. Where where ya'll around 2000? Nice vid.

  • Diego Vega
    Diego Vega 3 months ago

    excelent tutorial!

  • Rodrigo Lisboa Nascimento

    Many thanks. Great tutorial.

  • Rodrigo Lisboa Nascimento

    Many thanks. Great tutorial.

  • Rodrigo Lisboa Nascimento

    I love it so much! Thanks!!!

  • Hestia A
    Hestia A 3 months ago

    Hello, I love your explanation. My problem is that the data I need is on a second spreadsheet. I have it arranged so that the two criteria that I need to compare are in the first two columns (employee ids and dates)of each spreadsheet. The data I need to extract is in the second spreadsheet. It needs to go in a column on the first spreadsheet. Can the CHOOSE function allow me to do this and if so how?

  • Up4Excel
    Up4Excel 3 months ago

    Excellent tip on how to access that old pivot table wizard. Anyone know what other forgotten Excel features might still be accessible like that?

  • Sagar Shrivastava
    Sagar Shrivastava 3 months ago

    Sir, can you pls tell how to extract the href value of the next td, i wrote this code .Range("F" & i).Value = htmlELe.Children(5).href . But it is giving error. Can you pls tell me the correct line of code.

  • mangobrother
    mangobrother 3 months ago

    Great tutorial. You sir are a great teacher!

  • Mohamed Elnagar
    Mohamed Elnagar 4 months ago

    Kyle, you are amazing instructor, i would like to appreciate your ease of explanation and easy delivery of information just like watching a movie which i am enjoying ... Thanks a lot

  • miles cruikshanks
    miles cruikshanks 4 months ago

    i have a page with a 6 columns by many rows table containing uniform sized pictures with text (ex. Bob Corp) and a price (ex. $2,000,000) in each picture. Any way to scrape this text and number out of the pictures?

  • Charlotte Rinderknecht

    I was getting so frustrated with other explanations because they didn't answer the question of more than two tables, particularly for Office 365. THANK YOU!

  • Yogesh Sali
    Yogesh Sali 5 months ago

    It's very amazing and useful nested function

  • njofang franck
    njofang franck 5 months ago

    Hey what if I wanted to click an (A) tag hyperlink inside a table of hyperlinks. Many thanks. Great tutorial. Perfect for the second phase of my education. 😎😎👌👌

  • Robert Sparkman
    Robert Sparkman 5 months ago

    I am subscribing because of this video!!

  • Sana Noor
    Sana Noor 5 months ago

    Could you please make a video on extracting reviews and rating for multiple keywords to search on a website using vba automation

  • Sana Noor
    Sana Noor 5 months ago

    Wow that's great video. Thank you

  • M. Wasim
    M. Wasim 6 months ago

    drive.google.com/file/d/1KE7v6Q5iIj1Qt1Ya7KNitqz_GbZBj-wl/view This is the demo image for question

  • M. Wasim
    M. Wasim 6 months ago

    I have excel sheet with two data ..one is above which will always remain same ...and second where i have write data daily base ...i want that when when i write only shop name in bottom data the contact and addreas will automatically write in reapective cells ,columns and rows ...please guide how j can achieve this ...i am attaching a excel sheet to the mail as well...please guide me ..thanks

  • Azik D.
    Azik D. 6 months ago

    you're a good teacher. give us more of web scraping. can barely find any decent courses.

  • Mauro E
    Mauro E 6 months ago

    Sorry but in my Excel 2016 I cannot have that Wizard as result of Alt, D, P , can you help me please?thank you

  • L VR
    L VR 6 months ago

    Terrible

  • BoardgameBaker
    BoardgameBaker 6 months ago

    Most videos on this subject start from a way too advanced level. This was a proper introduction which gave me a solid platform to stand on while exploring the Get & Transform function. Great job!

  • Missing No
    Missing No 6 months ago

    what if the site does not have Tables but divs ?

  • Muhammed Asif Khurshid

    Thanks and regard. Very clear.

  • Derick
    Derick 7 months ago

    This doesn't work for Excel Office 365. I tried adding "PivotTable and PivotChart Wizard" to the quick access toolbar and ribbon and still nothing. I'm guessing they want you to use Power Query instead. My workbook will be used on different versions of Excel, Excel 2007 onward, and I want to use this method for 12 worksheets. I currently have one master worksheet where any data entered on any one of the 12 worksheets is automatically copied to the master, using VBA, for my pivot table to work. The search is on to rectify this issue. If/when I find I'll update this comment.

    • Derick
      Derick 7 months ago

      @OfficeNewb.com Yes, I'm using the latest desktop version of Excel 365. Weird. I found another way to do this but each table scope has to be set to workbook and not worksheet. When I create a pivot table for one of the twelve sheets then switch from "Active" to "All" in the pivot table field configuration window, I see all tables scoped to workbook. My issue is that I cannot have the 12 of them scoped to workbook since they're 12 of the same, a copy, January through December with all the same table name. If you'd like I can send you a screen recording.

    • OfficeNewb.com
      OfficeNewb.com 7 months ago

      Hello Derick - Weird... I am currently using the latest release of Excel, through Office 365, and the command is still working. Are you working with the Desktop version of Excel?

  • Modhumita mondal
    Modhumita mondal 8 months ago

    Nice info

  • Nachu Sambasivam
    Nachu Sambasivam 8 months ago

    This is a very helpful and simple way to explain. Thanks. How would i use it to move rows and columns like in a regular pivot as in consolidated it does not allow to choose our own

  • OfficeNewb.com
    OfficeNewb.com 8 months ago

    UPDATE: When using the CHOOSE function, make sure you finish the formula by pressing CONTROL + SHIFT + ENTER. The {1,2} is an array. In order to complete the formula as an Array you need to press CONTROL + SHIFT + ENTER

  • William Davis
    William Davis 8 months ago

    More Hockey Stats! ;)

  • Yılmaz DALKIRAN
    Yılmaz DALKIRAN 8 months ago

    First!

  • S Gain
    S Gain 8 months ago

    Thank you for the tutorial and Happy New Year :)

  • Md. Islam
    Md. Islam 8 months ago

    Thanks a lot for the video ,learned something very useful. I had one question regarding charts with let's say double headers, like the one in this link? en.wikipedia.org/wiki/List_of_countries_by_Human_Development_Index

  • Saul Espinoza
    Saul Espinoza 8 months ago

    This is amasing, there is no even the need to set absolute reference on the ranges!

  • GSSS BIBIPUR School Patiala

    Dear Mr Kyle Pew, SUBJECT : Excel Query to search required record from large excel file in few clicks. I m ATTACHING EXCEL FILE For reference Sir i have one query ..i am Amandeep Singh and working as a school clerk. I am basic to intermediate level excel user. I am asked by many Students to find their name and complete record in an large excel file . I followed traditional way of Control +F shortcut Key which is time consuming . Right now I am finding the record of candidate with numerous click of keyboard I just want to know is there any well defined awesome excel query method like using Forms or using function or formula available to fetch the record of required candidate from large excel file. For example, I m fantasizaing about an excel form in which I have to select the named range of large excel fileand then only thing left is i have to fill the name of candidate,Fathers name of candidate and date of birth in a form or formula to fetch the record of required candidate I am lookin for. ..i m confused how to fetch the record of required candidate of my institute in few clicks. If any wayout is there pls share with me. I m looking forward to your response whats your email address where i have to share the excelfile

  • Adil Najmuddin
    Adil Najmuddin 8 months ago

    Very cool! Thanks for sharing. Is there a way to loop through all of the columns in the table as well?

  • Yılmaz DALKIRAN
    Yılmaz DALKIRAN 8 months ago

    Thanks Mr. Pew! Greetings from Türkiye =)

  • ewillden
    ewillden 9 months ago

    Great video Mr. Pew! We here at American Airlines started using Udemy this year. Awesome stuff! Thx! ;-)

  • Cristian Florescu
    Cristian Florescu 9 months ago

    Sir, please record your videos a way more louder ! I barely hear it on the max volume ;)

    • Arrigo Lupori
      Arrigo Lupori 9 months ago

      Same here, works perfectly. Quality video. I think you might have a faulty computer. Sorry!

    • Cristian Florescu
      Cristian Florescu 9 months ago

      @Jani Huttunen I already did, before commenting

    • Jani Huttunen
      Jani Huttunen 9 months ago

      Sorry, no offense, but you should check your either computer settings or youtube audio volume settings, as the volume quality is perfect.

  • DR.PINAKI SENGUPTA,MD
    DR.PINAKI SENGUPTA,MD 9 months ago

    Can you please add a link to download the practice file

  • Ronnie Myrick
    Ronnie Myrick 9 months ago

    Any way to make the line extend to the edge? Its driving me crazy.