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.
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.
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.
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.
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.
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!
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.
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!
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?
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.
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
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.
@@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.
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?
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?
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.
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
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
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.
@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.
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.
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!
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.
@@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.
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.
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.
@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.
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.
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?
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.
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?
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
❓What's the Excel task that makes you want to pull your hair out?
Learn Excel with my courses: bit.ly/selfup24courses
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.
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.
Awesome to hear! Thanks for watching.
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!!
Awesome to hear!
Please can I email you. Its important.
Thank you.
Thanks for letting us know new concepts..each and every time😊
My pleasure 😊
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.
Oh, yeah. I forgot about that because I usually load directly to a PivotTable and keep my file small. Thanks for the reminder.
i wish i forgot how to use Excel so i could learn it all over again with you
🥰
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.
Wonderful to hear 🙏😊
So clear and easy to follow, as always. 👍👍
Thanks so much for your support!
Thanks Mynda, this is great (as usual) !
Thanks for watching, Chris!
Great video. Thanks for uploading
I'm glad you found it helpful!
An excellent tutorial. Thanks
Much appreciated!
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.
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.
I had forgotten about Treemaps - thanks for reminding me - great video!
My pleasure!
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!
Glad it was helpful!
Excellent... As always. Thanks Mynda
Glad you enjoyed it!
Outstanding! These are going to make me look like a Rockstar! Thank you for this
Awesome to hear!
Simply brilliant.
Thanks so much!
Brilliant again! Great job knowing to use the offset formula to do this was/is great! Kudos.
Glad it was helpful!
Your channel is really great. Thanks. Re looking at all my spreadsheets now.
Great to hear!
I learn something new EVERY time!! Thanks.
So pleased to hear that, Chris!
Excellent! Thank you
Glad it was helpful!
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.
Great use for the dynamic named ranges. Glad it will be helpful!
Great tricks👏 I'm grateful
Glad to hear that 🙏
Nice tips, thank you! How do you create the category descriptions with the cute little icons in them?
They're emojis. You can insert them with the Windows key + ;
@@MyOnlineTrainingHub Thank you very much
Hi, great tutorial. Can you please share how to add navigation pane. It looks like very useful tool. Thank you
You can turn the navigation pane on in the View tab of the Ribbon. It's available in 365.
Amazing!
I'm glad you liked it!
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!
Awesome to hear, Patrick! Yes, when you use 1 or -1, MATCH is assuming the list is sorted.
Really nice trick, thank you!!!😘
Glad it was helpful!
Awesome video, and thanks for teaching us these tips. Helpful stuff. I will definitely look at your paid training content.
Awesome, thank you!
Really great tutorial. Can you tell me how you added icons to your slicer please?
I used emojis in the cells. You can insert them pressing the Windows key + ;
Great video Mynda,
Thank you!
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?
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.
Great video , much appreciated.
Thanks for watching 😊
Well explained🥰
Thank you 🙂
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
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.
@@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.
Awesome Mynda...
Cheers, Robert!
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?
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.
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?
Those are emojis in the sheet tabs. You can insert them using the Windows key + ;
@@MyOnlineTrainingHub Didn't now this either, great tip
WoW! that's great! How do you have the icon next to each spreadsheet name at the bottom?
Glad you liked it! The icons are emojis in the Sheet tab names. You can insert them with the Windows key + ;
@mynda, you are amazing, Its very informative video. Thank you so much!!
Thanks so much!
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.
Yep, this is a common problem with users who don't understand OFFSET and inadvertently add data inside the range being counted by OFFSET.
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
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
@@MyOnlineTrainingHub Thank you very much Mynda. Much appreciated. I will investigate further.
Excellent
Thank you!
How did you make the navigation area?
It's the navigation pane on the View tab of the ribbon in Microsoft 365.
@@MyOnlineTrainingHub Thank you, madam. How did you make it permanent on the left side?
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.
You can post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
How do you add the neat little icons into your Category column visible at 1:30? Thanks!
I figured it out - thank you for all of your great videos!
Yes, how did you do that?
@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.
I actually used emojis in the sheet tab names. Press the Windows key + ;
@@MyOnlineTrainingHub I noticed that on your Navigation panel on the left of your screen - with colors!
What font are you using for the icons in the categories section?
They're emojis. You can insert them with the Windows key + ;
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.
I've been using Excel for 20+ years, but I'm still learning! Keep chipping away and you'll get there.
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
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
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!
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.
@@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.
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.
Please post your question and sample Excel file/screenshots on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Which lookup function is faster in microseconds and occupying machine resources?
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.
Great
Glad you liked it!
Hello, do you have a Sheets version video for the IPad 2022 or higher??
No, sorry.
Hi. Can you use filter instead of offset?
or instead of adding 'growing space' in the counta - could you use a unique against the source data and counta that?
@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.
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.
The icons are emojis in the sheet tab names. Press the Windows key + ; to insert them.
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?
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
I didn't follow how the index-match formula worked in tip #5. Can you explain how it works?
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.
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?
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?
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.
80 percent of my excel knowledge is from this channel.... :D
Love that!
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. 🤗
Hence why I used MATCH 😜
#4
Fixed Category > Salary Sub-Category (-4,000) does not show up in tree map?
Thanks
It doesn't make sense to show income and expenses in the treemap. They are opposites.
Hi, I can't see Navigation option in View menu. can anyone help how to fix it?
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.
@@MyOnlineTrainingHub thanks for your response, I am using 2021
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
Keep practicing and things will fall into place. It's an accumulation of knowledge.
@@MyOnlineTrainingHub I'll follow along with the practice sheets you provided
Wow this is basic beginner stuff. I have no hope at this life stuff. Wow