5 Excel Tricks for Self-Updating Spreadsheets (Files Included)

Поделиться
HTML-код
  • Опубликовано: 24 дек 2024

Комментарии • 148

  • @MyOnlineTrainingHub
    @MyOnlineTrainingHub  4 месяца назад +5

    ❓What's the Excel task that makes you want to pull your hair out?
    Learn Excel with my courses: bit.ly/selfup24courses

    • @AlbertoCohen-b6d
      @AlbertoCohen-b6d 4 месяца назад

      I really love your channel and your newsletter.
      One thing that really bothers me is keeping pivot tables formatting consistent (I once learned to use templates but I keep messing up...).
      Another thing is visual positioning when I'm working with large tables; I'm now using a VBA to color row/column intersections and another for autoadjusting row/column sizes as I type in.

  • @brighttriangle
    @brighttriangle 15 дней назад +1

    As always, we appreciated your valuable tips and were particularly pleased to discover the TreeMap chart type, which was new to us. We were also impressed by the innovative application of the MATCH() function at 18:53. Thank you for generously sharing your expertise with the Excel community.

  • @chrisbanks7830
    @chrisbanks7830 4 месяца назад +11

    It's rare that a tips video has examples on things I do every day for each example, but this is one. Really nice tips for sure, thank you!!

  • @SantoshKumarPakki-f5d
    @SantoshKumarPakki-f5d 4 месяца назад +4

    Thanks for letting us know new concepts..each and every time😊

  • @jasongins
    @jasongins 4 месяца назад +4

    Hi! I recommend disabling the Background Refresh in the query. This will prevent the pivot table from updating until the query is finished, and you will no longer need to hit refresh twice. If you right click on the query, you will find it among the options.

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  4 месяца назад +2

      Oh, yeah. I forgot about that because I usually load directly to a PivotTable and keep my file small. Thanks for the reminder.

  • @sentralorigin
    @sentralorigin 4 месяца назад +10

    i wish i forgot how to use Excel so i could learn it all over again with you

  • @francisaranha
    @francisaranha 4 месяца назад

    I absolutely love your videos! Thank you for making life easier. I already knew many of these things,but you have shown me a new way to use them.

  • @stevenlagoe7808
    @stevenlagoe7808 4 месяца назад +2

    So clear and easy to follow, as always. 👍👍

  • @chrism9037
    @chrism9037 4 месяца назад +2

    Thanks Mynda, this is great (as usual) !

  • @AsifDawood
    @AsifDawood Месяц назад +1

    Great video. Thanks for uploading

  • @colacoja
    @colacoja 4 месяца назад +1

    An excellent tutorial. Thanks

  • @diannedrechsler4792
    @diannedrechsler4792 29 дней назад

    This is a really useful video. Particularly using OFFSET formulas as a named range. I like the way you could use that as a dynamic named range in the drop down list too but I have a shorter way of doing that. In the list source box, enter =INDIRECT("table_name[table_column_name]"). No need to create a named range then.

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  29 дней назад

      INDIRECT is fine if you only have one drop down list, but I wouldn't use it if you have a column of drop-down lists. In fact, you don't even need it if you're referencing a table. You can simply define a name for the table column and then use that defined name in the drop down list, thus avoiding the volatile INDIRECT function.

  • @HandelMcHandel
    @HandelMcHandel 4 месяца назад +1

    I had forgotten about Treemaps - thanks for reminding me - great video!

  • @Adam_K_W
    @Adam_K_W 3 месяца назад

    Thanks for this one! I've not used Tree Maps in the past but I can imagine several scenarios in my various files and reports where they will be very handy!

  • @adelgonalves7839
    @adelgonalves7839 4 месяца назад

    Excellent... As always. Thanks Mynda

  • @michaeldavenport9268
    @michaeldavenport9268 3 месяца назад

    Outstanding! These are going to make me look like a Rockstar! Thank you for this

  • @muhammadasad729
    @muhammadasad729 4 месяца назад +1

    Simply brilliant.

  • @merrillmchenry1450
    @merrillmchenry1450 3 месяца назад

    Brilliant again! Great job knowing to use the offset formula to do this was/is great! Kudos.

  • @JonBushell
    @JonBushell 4 месяца назад

    Your channel is really great. Thanks. Re looking at all my spreadsheets now.

  • @ExcelWithChris
    @ExcelWithChris 4 месяца назад +7

    I learn something new EVERY time!! Thanks.

  • @MBJazzful
    @MBJazzful 4 месяца назад

    Excellent! Thank you

  • @dholcombpa
    @dholcombpa 4 месяца назад

    Nice. Thanks for this. I'm hoping the ideas in section 4 will help me with dynamic data in pie charts and the desire to remove blank (or 0% values) from the resulting chart. Can't wait to dig into this.

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  4 месяца назад

      Great use for the dynamic named ranges. Glad it will be helpful!

  • @eng.ahmedwaznah2261
    @eng.ahmedwaznah2261 4 месяца назад

    Great tricks👏 I'm grateful

  • @captainteach007
    @captainteach007 4 месяца назад

    Nice tips, thank you! How do you create the category descriptions with the cute little icons in them?

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  4 месяца назад +3

      They're emojis. You can insert them with the Windows key + ;

    • @captainteach007
      @captainteach007 3 месяца назад

      @@MyOnlineTrainingHub Thank you very much

  • @SvetlanaMaltseva-n6s
    @SvetlanaMaltseva-n6s 28 дней назад

    Hi, great tutorial. Can you please share how to add navigation pane. It looks like very useful tool. Thank you

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  28 дней назад

      You can turn the navigation pane on in the View tab of the Ribbon. It's available in 365.

  • @chidinduuwaeziozi7360
    @chidinduuwaeziozi7360 Месяц назад

    Amazing!

  • @patrick.schommer
    @patrick.schommer 3 месяца назад

    The formula to find the last actual value threw me. When choosing the final parameter (1 - Less Than), the tool tip indicates it will find the largest value that is less than or equal to lookup_value. That statement makes it seem like it will find the largest value in column D. The formula works even if the Sep value is less than the largest value.
    I put the MATCH part of the formula into Copilot for some help, and it replied MATCH assumes a sorted list when using "1 = Less Than" as the final parameter. This inherent assumption finally made the formula make sense to me.
    For you:
    a) Do you agree with Copilot on the assumed sorted list assumption for MATCH? This is a very clever trick for finding the latest entry in a list. I like clever.
    b) Your videos are excellent and right at or just above my level of Excel understanding. Perfect to improving my skills!

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  3 месяца назад

      Awesome to hear, Patrick! Yes, when you use 1 or -1, MATCH is assuming the list is sorted.

  • @welbeckjotoo9857
    @welbeckjotoo9857 4 месяца назад

    Really nice trick, thank you!!!😘

  • @mraza99
    @mraza99 4 месяца назад

    Awesome video, and thanks for teaching us these tips. Helpful stuff. I will definitely look at your paid training content.

  • @murrayh-c7937
    @murrayh-c7937 4 месяца назад

    Really great tutorial. Can you tell me how you added icons to your slicer please?

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  4 месяца назад

      I used emojis in the cells. You can insert them pressing the Windows key + ;

  • @ademccarthy
    @ademccarthy 4 месяца назад

    Great video Mynda,

  • @yengyeng4287
    @yengyeng4287 4 месяца назад

    Hi @Mynda, the video is amazing. thanks for the tips and tricks! Just 1 question, wonder what is the reason for using 1e10 in the match function in the Dynamic Text Labels section?

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  4 месяца назад

      Glad you liked it! To find the last value in a column you need to get MATCH to search for something it will never find e.g. a very big number like 1e10. It will get to the end of the values and then because I used 1 in the last argument, it will force MATCH to simply return the row number for the last value it finds.

  • @BaSYaVSkI
    @BaSYaVSkI 4 месяца назад

    Great video , much appreciated.

  • @manibhai9432
    @manibhai9432 4 месяца назад

    Well explained🥰

  • @ernesttsape
    @ernesttsape 4 месяца назад

    Thank you so much for sharing this. This is so valuable. May be just one question: What's the difference if I just create a pivotchart from the figure instead of using the OFFSET function? Will this make any difference as the pivotchart will be simpler, faster and will also be dynamic? Thanks in advance for your answer

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  4 месяца назад +1

      If the chart you want to use can connect to a PivotTable, then absolutely do that. The Treemap chart I used does not work with PivotTables, hence the OFFSET technique.

    • @ernesttsape
      @ernesttsape 4 месяца назад

      @@MyOnlineTrainingHub ​​⁠thanks a lot for your answer!
      Understood! That's exactly what I was thinking because I tried to use the Treemap with the pivot table (tried to connect both) but it didn't work.

  • @robertcooper3759
    @robertcooper3759 4 месяца назад

    Awesome Mynda...

  • @profemanuperez
    @profemanuperez 4 месяца назад

    Thank you for sharing your great knowledge in Excel. This video is very interesting, as usual. Please, let me ask you something about the last part of it. When you talk about using INDEX(first column of the table, MATCH...) wouldn't be easier to use COUNTA(third column) in the second argument of INDEX instead of the MATCH function?

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  4 месяца назад +1

      Yes, you can use COUNTA as long as you know every cell in the range will have a value. If one cell is blank, the wrong row will be returned.

  • @ademccarthy
    @ademccarthy 4 месяца назад

    Hey Mynda, I notice you have some fab icons within your cells (e.g. categories) and alongside your sheet tab names - where did you get them from and how do you insert them?

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  4 месяца назад +1

      Those are emojis in the sheet tabs. You can insert them using the Windows key + ;

    • @sandrawhite1366
      @sandrawhite1366 4 месяца назад

      @@MyOnlineTrainingHub Didn't now this either, great tip

  • @Burdisso1982
    @Burdisso1982 4 месяца назад

    WoW! that's great! How do you have the icon next to each spreadsheet name at the bottom?

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  4 месяца назад

      Glad you liked it! The icons are emojis in the Sheet tab names. You can insert them with the Windows key + ;

  • @ral_1
    @ral_1 4 месяца назад

    @mynda, you are amazing, Its very informative video. Thank you so much!!

  • @garywilhelm2299
    @garywilhelm2299 3 месяца назад

    In the Pivot Table treemap discussion, I see that your use of COUNTA in the 4th argument of the OFFSET formula can create an issue / problem when a grand total is included in the Pivot Table. And since many (perhaps most) pivot tables will have a grand total at the bottom, this could be a problem. The problem with having a Grand Total in the pivot table is that COUNTA, with its oversized range, will now include the Grand Total line so that the Grand Total shows up in the tree map. Bar charts and line charts that work natively with Pivot Tables work properly with or without a grand total. Of course, you could adapt the OFFSET formula by subtracting 1 in the 4th argument. But then what if you change your mind and remove totals from your pivot table? You have to be careful with this - in addition to being careful not to enter anything in the blank cells in the oversized axis range under the pivot table.

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  3 месяца назад

      Yep, this is a common problem with users who don't understand OFFSET and inadvertently add data inside the range being counted by OFFSET.

  • @johnpomfret605
    @johnpomfret605 4 месяца назад

    Great info and video Mynda. I have a question.If I am loading financial data into a "transactions" sheet as you use above, the data will fill the first 5 columns. Should I add the Sub-category, Category and Category Type as part of the data transformation or can I load the data in and somehow then add the three columns which allow me to select the Sub-category and then use the Vlookup to fill in the other 2 columns? I hope that makes sense. John

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  4 месяца назад

      It depends on whether you're loading the data to Power Pivot, in which case keep the lookup values in a separate dimension table and create a relationship between the tables so you can summarise by these categories. If you're loading the data to a single table, I would use Power Query to do the lookup by merging the tables as this is more efficient than inserting a load of lookup formulas. More on Power Query here: ruclips.net/video/L4BuUzccLpo/видео.html

    • @johnpomfret605
      @johnpomfret605 4 месяца назад

      @@MyOnlineTrainingHub Thank you very much Mynda. Much appreciated. I will investigate further.

  • @johndurran614
    @johndurran614 4 месяца назад

    Excellent

  • @NjaanAdima
    @NjaanAdima 2 месяца назад +1

    How did you make the navigation area?

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  2 месяца назад

      It's the navigation pane on the View tab of the ribbon in Microsoft 365.

    • @NjaanAdima
      @NjaanAdima Месяц назад

      @@MyOnlineTrainingHub Thank you, madam. How did you make it permanent on the left side?

  • @charlesHAPA
    @charlesHAPA 4 месяца назад

    Hello 👋, I have two sheets with different headers to create relationships and then a pivot table. How may I share the workbook for your assistance? Thank you for your good work.

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  4 месяца назад

      You can post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

  • @rcommins
    @rcommins 4 месяца назад

    How do you add the neat little icons into your Category column visible at 1:30? Thanks!

    • @rcommins
      @rcommins 4 месяца назад

      I figured it out - thank you for all of your great videos!

    • @khristino-n4q
      @khristino-n4q 4 месяца назад

      Yes, how did you do that?

    • @rcommins
      @rcommins 4 месяца назад

      @khristino-n4q there is a wingdings-like font that has the icons she used. It starts with Seq . . . and has emoticons in it. I'm away from my computer, but will post it in a couple hours.

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  4 месяца назад +1

      I actually used emojis in the sheet tab names. Press the Windows key + ;

    • @rcommins
      @rcommins 4 месяца назад

      @@MyOnlineTrainingHub I noticed that on your Navigation panel on the left of your screen - with colors!

  • @DickvanderVelde
    @DickvanderVelde 4 месяца назад

    What font are you using for the icons in the categories section?

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  4 месяца назад

      They're emojis. You can insert them with the Windows key + ;

  • @excxmoody
    @excxmoody 3 месяца назад

    How long have you worked with Excel to get this level of confidence? Learning each day little bits at a time not to feel overwhelmed...gaining more understanding and seeing the program from the looks of it can do quite a bit. So far more than what I originally thought, the all commands area...lengthy.

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  3 месяца назад

      I've been using Excel for 20+ years, but I'm still learning! Keep chipping away and you'll get there.

  • @FrankGoesJr
    @FrankGoesJr 7 дней назад

    Dear all, I'm a great admiror of your work but where exactly can I find the included files that we can use ? Thanks for letting me know! Frank

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  7 дней назад

      Thank you! The download link is in the video description. Here it is again: ⬇️ Download the example file here and follow along: bit.ly/selfup24file

  • @ennykraft
    @ennykraft 4 месяца назад

    Why did you create a named range for the subcategories? Since they're in a table they already have a name. I alway select the column I want to populate my drop down list.
    And why didn't you change the data in the treemap to the range of the Pivot table once it had been created? I learned this super handy trick from one of your videos where you did it for a map chart and have used it loads ever since.
    But what I really want to know is how you managed to get the icons into the categories. Is there a video where you describe how to do it? Thank you!

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  4 месяца назад

      If the Table containing the subcategories is on a different sheet to your drop-down lists, they won't pick up new items added to the table. That only works when the subcategories are on the same sheet as the drop-down list. Try it and you will see what I mean.
      Because not all charts will ignore empty cells like a treemap, so I wanted to demonstrate a technique you can use if you're using a scatter chart etc.
      The icons are emojis. Press the Windows key + ; to insert them.

    • @ennykraft
      @ennykraft 3 месяца назад

      @@MyOnlineTrainingHub thank you for the info. I have actually never tried putting my table someplace else. Regarding the icons, I opened the file on my Mac at home and was really excited when I saw detailed and colorful icons. Microsoft could do some improvement in regarding their icons.

  • @simonemason2281
    @simonemason2281 2 месяца назад

    Do you have any videos where you're merging two tables where some of the columns had data manually entered in the past but later updates mean the data is now on coming via power query. Is there a good way to combine these? I'm currently bringing the new data in with lookup formulas for three columns (out of 12 columns) where the first half of the the column is manually entered data and the second half is via a lookup. There have been more updates and there is another power query which will mean 4 more columns I'm going to need to do lookups for. I think now is the time to get this streamlined.

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  2 месяца назад +1

      Please post your question and sample Excel file/screenshots on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

  • @keylanoslokj1806
    @keylanoslokj1806 4 месяца назад

    Which lookup function is faster in microseconds and occupying machine resources?

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  4 месяца назад

      They should all be fairly equal now that the calc improvements have been implemented. If you're able to sort your data and use an approximate match, then that will be noticeably faster.

  • @altafmehrab
    @altafmehrab 3 месяца назад

    Great

  • @Load2DeliverLLC
    @Load2DeliverLLC 4 месяца назад

    Hello, do you have a Sheets version video for the IPad 2022 or higher??

  • @clydonleonor3968
    @clydonleonor3968 4 месяца назад

    Hi. Can you use filter instead of offset?

    • @jonathannorth531
      @jonathannorth531 4 месяца назад

      or instead of adding 'growing space' in the counta - could you use a unique against the source data and counta that?

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  4 месяца назад

      @clydonleonor3968 Yes, you can use any function that returns a range, including XLOOKUP, INDEX & MATCH etc. You can't use FILTER because it returns an array.
      @jonathannorth531 you could I suppose, but adding UNIQUE will be an extra step. The risk with COUNTA is that blanks will throw out the count, so you have to be sure there will never be intentional blanks in the count range.

  • @hhattingh
    @hhattingh 4 месяца назад

    Stupid question, but how was the little images created on the category section next to the text? I did not see icons used in example on Excel Icon tab.

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  4 месяца назад

      The icons are emojis in the sheet tab names. Press the Windows key + ; to insert them.

  • @benosborne413
    @benosborne413 3 месяца назад

    Hi, I have a data sheet that I want to calculate. 1 = Contractual, 2 = Actual, 3 = Variance. In the variance I want to indicate a positive or negative value. If the actual are less that the contractual, then it must be minus or indicated in red. If the actual are more than the contractual, then it must indicate a plus. How would I do that?

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  3 месяца назад

      Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

  • @PedramPourmand
    @PedramPourmand 4 месяца назад

    I didn't follow how the index-match formula worked in tip #5. Can you explain how it works?

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  4 месяца назад +1

      We use INDEX to return the running total value for the current month. We use MATCH to find what row the current month is on by getting it to look up a large value that it will never find. When it gets to the end of the values in the column it's looking up, the 1 in the third argument for MATCH tells it to simply return the last non-empty cell.

  • @christopherwilliamson8228
    @christopherwilliamson8228 4 месяца назад

    When I try to do this, only three of the four files in the folder are being used in the combine and transform section? Any idea why, please?

  • @JonBushell
    @JonBushell 4 месяца назад

    Help. I did as you suggested and made tables in all my sheets but now I realise I can’t add rows to the top. I build it recently at top and down. And now I can’t un table it either. Do I need to scrap and start again?

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  4 месяца назад

      Your tables don't have to be in the first row of a sheet. You can select the first row and insert rows above a table.

  • @mikeerana2168
    @mikeerana2168 4 месяца назад +1

    80 percent of my excel knowledge is from this channel.... :D

  • @JoseAntonioMorato
    @JoseAntonioMorato 4 месяца назад

    Dear Mynda,
    The formula "=MATCH(1E+100,Table1[Current $k],1)" does not work if the new function "=XMATCH(1E+100,Table1[Current $k],1)" is used. 🤗

  • @txreal2
    @txreal2 4 месяца назад

    #4
    Fixed Category > Salary Sub-Category (-4,000) does not show up in tree map?
    Thanks

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  4 месяца назад

      It doesn't make sense to show income and expenses in the treemap. They are opposites.

  • @salimashraf
    @salimashraf 4 месяца назад

    Hi, I can't see Navigation option in View menu. can anyone help how to fix it?

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  4 месяца назад +1

      It's available in Excel for Microsoft 365 and possibly 2021. Not sure about 2019. So I suspect you have an earlier version of Excel.

    • @salimashraf
      @salimashraf 4 месяца назад

      @@MyOnlineTrainingHub thanks for your response, I am using 2021

  • @yahde1414
    @yahde1414 4 месяца назад

    I need help lol why is it so hard grasping these concepts lol. You are doing such a good job explaining it. I just don't know how to apply it to my job lol

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  4 месяца назад +1

      Keep practicing and things will fall into place. It's an accumulation of knowledge.

    • @yahde1414
      @yahde1414 4 месяца назад

      @@MyOnlineTrainingHub I'll follow along with the practice sheets you provided

  • @ayaanbretmitchell9830
    @ayaanbretmitchell9830 23 дня назад

    Wow this is basic beginner stuff. I have no hope at this life stuff. Wow