Brilliant- this is so timely for me- I have a power pivot that wouldn’t lock the sort- I had a sort field exactly like you had but I was using the unsorted fact table rather than the sorted dimension table in the pivot- thanks a lot 👍
Thank you! There could be many reasons the data model is slow to refresh and it's probably less to do with the data model and more to do with the source of your data and how you're connecting to it, assuming there is Power Query in between. I'd be looking to see if it's the query that's slow. This post has some tips for speeding up slow queries: www.myonlinetraininghub.com/excel-forum/power-query/any-way-to-speed-up-really-slow-refresh-times-in-power-query
@@MyOnlineTrainingHub It's your training file excel_slicer_sorting.xlsx on tab Data Model Examples. When I change the sort order and then refresh, recalc is 5 seconds. Is there a setting I need to change? If this feature is for internal analysis, this is not an issue. But if used for a dashboard, the end-user will think a 5 second refresh is the model blowing up. (Side note...keep up the amazing output...I'm a major fan!)
Hi Mydna. Great video. I've followed the instructions but unfortunately "Use Custom Lists When Sorting" is not visible. Do you know what would be causing this issue?
You've probably loaded the data to the data model upon creating the PivotTable. In which case, Custom List sorting is not available. Instead, use the Data Model solution shown in the last example.
Is there a way to move all the highlighted values (data selected) to the top of a slicer? Besides increasing the number of column to 5 , which takes up space. Scrolling down a list of 100 choices is taking time. Thanks.
The only way to do this is if there are no values for the other items at that filter level as you can go into the slicer settings and check the option to “hide items with no values”.
Ok I got a great idea for a video. I have the same problem in excel and word. everything is in English. the menu, ribbon tabs options etc. but for some reason all the template titles are in Spanish.
Is there any way to get data of a separate file into a new excel file? I know about get data, but I want (without getting data manually) when clicking a button in Excel (produced by macro), a window appears and ask me to add the demanded file. Then the data loads automatically in the new excel file.
Dear Mynda, what series of your training courses do you recommend for someone who has some familiarity with simple Excel formulae (like VLOOKUP, INDEX/MATCH, etc.) and wants to be proficient in Excel charts/dashboards, Power Pivot & Power Query, and Power BI? I believe I have already asked this before (somewhere) but I just cannot find your reply, sorry. I am conversant with MS Access/VBA and SQL and I want to make (slight) a career change. Which version of MS Office do recommend? For learning/training purposes, is Office 2021 sufficient or should I go for Microsoft 365? Many thanks and regards.
Hello and thanks for your interest in my courses. From what you describe, my Excel Dashboards + Power Query + Power Pivot + Power BI course bundle is ideal: www.myonlinetraininghub.com/power-bi-course With your Access and SQL skills, you'll pick Power Pivot up easily. You can use Office 2021, but if you're going to purchase a license, I would definitely recommend getting 365 as it's always up to date. If you have further questions, please reach out via email: website at MyOnlineTrainingHub.com as I won't see follow up questions on this thread due to the volume of comments I get.
Q: Unrelated question (but I heard Kirsty had the same question :) ! Is there a way to highlight cells in the same column that are duplicates of 1 or more of the previous contiguous cells? For example a column should contain ONLY alternating 1s and 0s: I would like to highlight/identify those cells in the same column that contain a 1 or 0 that following a previous 1 or 0 respectively.
Conditional formatting of cell(s) based on a formula. Since the column should contain only alternating 1's and 0's adjacent cells should not be equal. Formula for a value in A2 might look something like: =OR(A2 = A1, A2 = A3). This formula will highlight both duplicate cells. To highlight only the second cell, use only the first test from the OR: =A2 = A1
You shouldn't need to sort the month if it's based on a proper date. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Few weeks back I requested you to help me selecting months as per INDIAN FISCAL YEAR (Apr-Mar) this is a great help
Thank you
You are most welcome 😉
Brilliant- this is so timely for me- I have a power pivot that wouldn’t lock the sort- I had a sort field exactly like you had but I was using the unsorted fact table rather than the sorted dimension table in the pivot- thanks a lot 👍
So glad it helped 😊
Imho you run one of the smartest Excel channels on YT. This video was just splendid and fun. Thx Mynda.
Wow, thank you for your kind words 🥰
Well done! It's always interesting to see it from a different angle.
Glad you enjoyed it 😊🙏
minute 6:21 is gold! wow! thank you Mynda!
So pleased you liked it 😁🙏
I got a good chuckle out of this video; not that i haven't enjoyed any of your previous ones.
😁thanks for watching. Glad you enjoyed it!
Waw! My brain is going to explode with you Mynda 😊, it's amazing how easily you do
😁 thank you, Ricardo! Glad you enjoyed it.
Very clever tips, thanks Mynda!
Cheers, Chris 🙏😊
You are definitely going to be canonised by the high priest of Excel as the "Patron saint of Excel"... you just EXCELL all the time !
😁thank you!
Very good 👍 Will move from Custom List to PP for sorting.
Awesome to hear 😊
I am just loving your creativity, and now story lines
😁😍
😎
😁thank you! Not sure YT likes them as much.
@@MyOnlineTrainingHub well i ❤️ them, it makes learning fun
I build a lot of dashboards and I love your videos! Question: why does the data model take up to 5 seconds to refresh?
Thank you! There could be many reasons the data model is slow to refresh and it's probably less to do with the data model and more to do with the source of your data and how you're connecting to it, assuming there is Power Query in between. I'd be looking to see if it's the query that's slow. This post has some tips for speeding up slow queries: www.myonlinetraininghub.com/excel-forum/power-query/any-way-to-speed-up-really-slow-refresh-times-in-power-query
@@MyOnlineTrainingHub It's your training file excel_slicer_sorting.xlsx on tab Data Model Examples. When I change the sort order and then refresh, recalc is 5 seconds. Is there a setting I need to change? If this feature is for internal analysis, this is not an issue. But if used for a dashboard, the end-user will think a 5 second refresh is the model blowing up. (Side note...keep up the amazing output...I'm a major fan!)
Hmm, I don't have this performance issue. Not sure why it would be.
Brilliant indeed!... Thanks for sharing... loved the new video format too (fun to watch)
Awesome, thank you! 😁
Awesome, Thank you 👍
Thank you too!
Super helpful video! On a side note, I love Kirsty... 😂
😁Glad it was helpful!
I never knew the slicer setting did not travel. Thank you!
The slicer setting travels, but the custom lists don't.
ok, so this was good as usual, but CTL-R???? I had no idea about this feature... SO useful! Thanks for the unintended bonus!
So pleased there was a bonus tip for you 😁
Excellent as always. Thank Mynda👍 🌹
Thanks so much 😊
Hi Mydna. Great video. I've followed the instructions but unfortunately "Use Custom Lists When Sorting" is not visible. Do you know what would be causing this issue?
You've probably loaded the data to the data model upon creating the PivotTable. In which case, Custom List sorting is not available. Instead, use the Data Model solution shown in the last example.
Amazing video Mynda! Combining fun and serious learning! :) Arnaud
Glad you enjoyed it! 🙏😊
I love this solution. Thank you!
Glad you can use it!
Great Video.
Thanks
Glad you liked it!
Is there a way to move all the highlighted values (data selected) to the top of a slicer? Besides increasing the number of column to 5 , which takes up space.
Scrolling down a list of 100 choices is taking time. Thanks.
The only way to do this is if there are no values for the other items at that filter level as you can go into the slicer settings and check the option to “hide items with no values”.
Thank you Mynda 🙏❤
Pleasure 😊
Amazing video Mynda
Thanks so much!
Ok I got a great idea for a video. I have the same problem in excel and word. everything is in English. the menu, ribbon tabs options etc. but for some reason all the template titles are in Spanish.
That is odd 🤔 I’d send a frown via the help tab in Excel. This goes to the Excel team at Microsoft and they can help you.
@@MyOnlineTrainingHub I sent emails and complained in the feedback but no reply.
Is there anything better than one Mynda? Yes, two Myndas... I mean a Mynda and a Kirsty! 😉
😁 you're too kind! 🙏
Is there any way to get data of a separate file into a new excel file?
I know about get data, but I want (without getting data manually) when clicking a button in Excel (produced by macro), a window appears and ask me to add the demanded file. Then the data loads automatically in the new excel file.
You can do this with Power Query: ruclips.net/video/L4BuUzccLpo/видео.html
Dear Mynda, what series of your training courses do you recommend for someone who has some familiarity with simple Excel formulae (like VLOOKUP, INDEX/MATCH, etc.) and wants to be proficient in Excel charts/dashboards, Power Pivot & Power Query, and Power BI? I believe I have already asked this before (somewhere) but I just cannot find your reply, sorry.
I am conversant with MS Access/VBA and SQL and I want to make (slight) a career change.
Which version of MS Office do recommend? For learning/training purposes, is Office 2021 sufficient or should I go for Microsoft 365?
Many thanks and regards.
Hello and thanks for your interest in my courses. From what you describe, my Excel Dashboards + Power Query + Power Pivot + Power BI course bundle is ideal: www.myonlinetraininghub.com/power-bi-course
With your Access and SQL skills, you'll pick Power Pivot up easily. You can use Office 2021, but if you're going to purchase a license, I would definitely recommend getting 365 as it's always up to date.
If you have further questions, please reach out via email: website at MyOnlineTrainingHub.com as I won't see follow up questions on this thread due to the volume of comments I get.
@@MyOnlineTrainingHub that's awesome, thank you.
How do I register so I can purchase the courses?
Q: Unrelated question (but I heard Kirsty had the same question :) ! Is there a way to highlight cells in the same column that are duplicates of 1 or more of the previous contiguous cells? For example a column should contain ONLY alternating 1s and 0s: I would like to highlight/identify those cells in the same column that contain a 1 or 0 that following a previous 1 or 0 respectively.
Conditional formatting of cell(s) based on a formula.
Since the column should contain only alternating 1's and 0's adjacent cells should not be equal. Formula for a value in A2 might look something like: =OR(A2 = A1, A2 = A3). This formula will highlight both duplicate cells. To highlight only the second cell, use only the first test from the OR: =A2 = A1
@Thelvalen great question. I see @johnpalmer3848 has answered it. Thanks, John!
@@johnpalmer3848 Works like a charm. Thanks much!
Cannot sort month in the slicer on connected pivot table
You shouldn't need to sort the month if it's based on a proper date. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Is this something we can do with MS365 for Mac?
Yes, except for the last option with the data model.
💚
🙏
Do you have Kirsty’s number? ❤
😂
Uau ...
🙏😊
👍😎✊
🙏😁
Did you get that Kristy🥱? 😂
😁thanks for watching!