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

[@MyOnlineTrainingHub]

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

[@AlbertoCohen-b6d]

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]

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.

[@MyOnlineTrainingHub]

Awesome to hear! Thanks for watching.

[@chrisbanks7830]

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!!

[@MyOnlineTrainingHub]

Awesome to hear!

[@okiemutemarshal-umukoro3159]

Please can I email you. Its important. Thank you.

[@SantoshKumarPakki-f5d]

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

[@MyOnlineTrainingHub]

My pleasure 😊

[@sentralorigin]

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

[@MyOnlineTrainingHub]

🥰

[@stevenlagoe7808]

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

[@MyOnlineTrainingHub]

Thanks so much for your support!

[@francisaranha]

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.

[@MyOnlineTrainingHub]

Wonderful to hear 🙏😊

[@AsifDawood]

Great video. Thanks for uploading

[@MyOnlineTrainingHub]

I'm glad you found it helpful!

[@chrism9037]

Thanks Mynda, this is great (as usual) !

[@MyOnlineTrainingHub]

Thanks for watching, Chris!

[@ExcelWithChris]

I learn something new EVERY time!! Thanks.

[@MyOnlineTrainingHub]

So pleased to hear that, Chris!

[@jasongins]

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]

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

[@HandelMcHandel]

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

[@MyOnlineTrainingHub]

My pleasure!

[@michaeldavenport9268]

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

[@MyOnlineTrainingHub]

Awesome to hear!

[@diannedrechsler4792]

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]

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.

[@merrillmchenry1450]

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

[@MyOnlineTrainingHub]

Glad it was helpful!

[@adelgonalves7839]

Excellent... As always. Thanks Mynda

[@MyOnlineTrainingHub]

Glad you enjoyed it!

[@Adam_K_W]

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!

[@MyOnlineTrainingHub]

Glad it was helpful!

[@SvetlanaMaltseva-n6s]

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

[@MyOnlineTrainingHub]

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

[@JonBushell]

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

[@MyOnlineTrainingHub]

Great to hear!

[@colacoja]

An excellent tutorial. Thanks

[@MyOnlineTrainingHub]

Much appreciated!

[@mraza99]

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

[@MyOnlineTrainingHub]

Awesome, thank you!

[@dholcombpa]

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]

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

[@yengyeng4287]

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]

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.

[@ernesttsape]

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]

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]

@@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.

[@profemanuperez]

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]

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.

[@eng.ahmedwaznah2261]

Great tricks👏 I'm grateful

[@MyOnlineTrainingHub]

Glad to hear that 🙏

[@welbeckjotoo9857]

Really nice trick, thank you!!!😘

[@MyOnlineTrainingHub]

Glad it was helpful!

[@captainteach007]

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

[@MyOnlineTrainingHub]

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

[@captainteach007]

@@MyOnlineTrainingHub Thank you very much

[@muhammadasad729]

Simply brilliant.

[@MyOnlineTrainingHub]

Thanks so much!

[@murrayh-c7937]

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

[@MyOnlineTrainingHub]

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

[@patrick.schommer]

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]

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

[@ral_1]

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

[@MyOnlineTrainingHub]

Thanks so much!

[@BaSYaVSkI]

Great video , much appreciated.

[@MyOnlineTrainingHub]

Thanks for watching 😊

[@MBJazzful]

Excellent! Thank you

[@MyOnlineTrainingHub]

Glad it was helpful!

[@ademccarthy]

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]

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

[@sandrawhite1366]

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

[@johnpomfret605]

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]

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: kzbin.info/www/bejne/gmWlpoiwmMh_ptE

[@johnpomfret605]

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

[@Burdisso1982]

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

[@MyOnlineTrainingHub]

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

[@FrankGoesJr]

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

[@ademccarthy]

Great video Mynda,

[@MyOnlineTrainingHub]

Thank you!

[@charlesHAPA]

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]

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

[@manibhai9432]

Well explained🥰

[@MyOnlineTrainingHub]

Thank you 🙂

[@chidinduuwaeziozi7360]

Amazing!

[@MyOnlineTrainingHub]

I'm glad you liked it!

[@robertcooper3759]

Awesome Mynda...

[@MyOnlineTrainingHub]

Cheers, Robert!

[@rcommins]

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

[@rcommins]

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

[@khristino-n4q]

Yes, how did you do that?

[@rcommins]

@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]

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

[@rcommins]

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

[@mikeerana2168]

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

[@MyOnlineTrainingHub]

Love that!

[@garywilhelm2299]

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]

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

[@ennykraft]

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]

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]

@@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.

[@excxmoody]

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]

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

[@simonemason2281]

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]

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

[@keylanoslokj1806]

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

[@MyOnlineTrainingHub]

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.

[@johndurran614]

Excellent

[@MyOnlineTrainingHub]

Thank you!

[@DickvanderVelde]

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

[@MyOnlineTrainingHub]

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

[@NjaanAdima]

How did you make the navigation area?

[@MyOnlineTrainingHub]

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

[@NjaanAdima]

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

[@hhattingh]

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]

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

[@yahde1414]

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]

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

[@yahde1414]

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

[@Load2DeliverLLC]

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

[@MyOnlineTrainingHub]

No, sorry.

[@clydonleonor3968]

Hi. Can you use filter instead of offset?

[@jonathannorth531]

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

[@MyOnlineTrainingHub]

@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.

[@JoseAntonioMorato]

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. 🤗

[@MyOnlineTrainingHub]

Hence why I used MATCH 😜

[@benosborne413]

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]

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

[@PedramPourmand]

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

[@MyOnlineTrainingHub]

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.

[@altafmehrab]

Great

[@MyOnlineTrainingHub]

Glad you liked it!

[@christopherwilliamson8228]

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]

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]

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.

[@txreal2]

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

[@MyOnlineTrainingHub]

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

[@salimashraf]

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

[@MyOnlineTrainingHub]

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]

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

[@ayaanbretmitchell9830]

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