Hi Mynda! I have a large amount of sales data that I need to break out by salesperson in separate excel files. What is the easiest way to automate this? I can break into separate tabs but would like to have a process that automatically separates them into new spreadsheets. Thank you.
More of an annoyance. For me it's when I CTRL+T, and that ugly default formatting is applied to the table. Then I have to go to table formatting to change it (usually to blank).
I frequently get data in Excel which has a column for date time stamp. I need to do a pivot table using the datetime. Problem is Excel never understands by default the value in that column is a datetime. I have to always split the date and time separately (using string functions), then use the "Text to Column" function to tell Excel what format the date is in. Then add back the time to the date. Is there an easier way to do this?
I learn how to do something better every time I watch your lessons and then I wish my job required more excel data analysis. You are one of the top Excel experts worldwide. Thanks for sharing!
Glad to have the channel back. Thanks for these great shortcuts and tips Mynda. In my work place I usually extract some flight data into excel and for a long time I have not been using Power Query until recently. Its amazing how it can clean up data with just a few clicks, however am stuck on how to filter data between times before midnight and times after midnight. Example: (Between 16:45 to 03:50). I don't get any data while filtering the times using Power Query filter option😐. Thanks for any assistance on how to get this right.
Thanks for your support! Regarding your Power Query question, you're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Hi Leila Another great presentation, I really enjoy seeing your approach to the different topics, even though I know the usage already. The topics in this video are very well chosen, and easy to go to, I will allow myself to share it with my colleagues.
As always, beautifully explained For me I take table names one step further. I give tables a tbl prefix, constants a cst prefix, named ranges an nr prefix and use camel case Cleaning data from coworkers takes way longer than it should because they don’t do things like this
Making multipe charts from power pivots but requiring different fields and calculations which takes me time to leatn how to create measures to include in pivot charts
Great videos ... Thanks for uploading videos related to Power BI, is it possible to use Power BI instead of Microsoft Acess, or you will recommend videos of yours in which you briefly explain Power BI in terms of Microsoft Acess working .
Power BI and Access serve different purposes. Access is a database, Power BI is a reporting tool that can report on data in databases, among other sources like Excel, CSV files, web pages etc.
Hi Mynda! It is the most annoying repetitive task to colour variance columns with conditional formatting in a pivot table. I do use "apply rule" to embed per column. I however have approximately 40 pivot tables with four variance columns each. Recently tried custom formatting which is slightly faster once you created the first one, but that somehow clashes with the formatting in the data model and throws an error message on the file.
I hear you! It’s be great if the formatting could be applied to the whole table in one go but still be unique for each column. Can’t see them developing this though, so might be worth creating a macro to automate this repetitive task 🤔
Keeping or maintaining format on my bezutiful charts every timemy poer pivots refresh, i tried ticking boxes to maintain charts but does not work, especially if it is a combo charts with secondafy axis,or if im wirking with negative values and need it to reflect as positive values on the charts
Try removing all filters before setting your formatting. That said, if the filters result in negative values only being present some of the time, I have a feeling it won't retain these settings.
I could not find the excel spreadsheet that had the hyperlink to all of your courses. Could you please provide a link to the spreadsheet please? Really enjoyed this video, very informative!
Interesting feedback 🙏 @BCOzSportsGamer, create another (dimension) table with a unique list of the Roster names. Link both tables to this new dimension table and when building your PivotTables, use the names from the dimension table. HTH.
When I need multiple reports which require same tables but different relationships I have to create multiple files of different reports using same tablesbut different relationships
#10 tip is what I need - But doesn't seem to work. I set Classic Pivot table checkbox checked but it still creates new tables as the excel default. What am I missing?
I'm not sure what you're referring to as the "Classic Pivot table checkbox" because in the default PivotTable layout dialog box there's no check box as such. You select it from the 'Report Layout' drop down > 'Show in Tabular Form'.
Hi, May I ask u one question? What is the best laptop fit with Microsoft excel due to my daily report contains storage around 50MB with formula working?
I don’t think your laptop is the issue. You need to look at how the data is structured. This seems like something that should be done in PowerQuery rather than excel formulas.
An Excel task that takes longer than it should: Converting a Word table to Excel. Issue #1: Hard returns in Word table cells. When converted, many of the cells in a row will be split or merged. Issue #2: Too much text. Excel cells can contain up to 32,767 characters but only 1,024 characters can be displayed or printed. I blame neither Excel nor Word. Training is provided in both apps, but people often do what's fast and easy for them at the moment, instead of what's useful long term for the project. Another task: Printing someone else's spreadsheet. We're looking for a utility that generates for all worksheets in a workbook, a report of all used cells that contain content, versus cells that contain formatting only. Most of the workbooks we receive from clients are not formatted for printing; it's usually assumed they will be read on screen. In the practice of law, this is often not the case. Excel files may need to be submitted as part of discovery or submitted to the judge.
Converting non-conforming dates to proper Excel dates. Nothing seems to seamlessly deal with dates that are imported with the days and months flipped. I've tried Power Query but it doesn't seem to get it even if I lie about what 'region' date format I want...
You can change the date format using “change type with locale” as explained here: Power Query Change Type Using Locale th-cam.com/video/QKgS3hrrmvw/w-d-xo.html
What´s one Excel task that “always” takes too much time It's not so much a task, but more a thing that happens from time to time when I import data from an Access DB into Power Query, that I get a message that the Database or the query in Access has already been taken up exclusively by another user - even though I know for sure that I'm the only user. It can cause a bit of a challenge with updating!
My no1 pinch point in Excel is the trainwreck of open/save windows, the recent list & irritating nagging to use OneDrive. Not strictly an Excel problem but 365, SharePoint etc. The OG of this is the use of Desktop as both a container of 'everything', while ALSO actually being a folder under User. Microsoft keep adding Win10 features like Quick Access... like a sticking plaster. Now the multiple cloud servers I use each have a mirror folder on the HDD, adding even more confusion. Add random sync errors... mostly Microsoft, rarely Google. Had an Excel error I might have mentioned before where the auto sync of the two files corrupted every single formula - silently replaced them with values. Never found out why of course. Watching formulae disappear in front of your eyes, not a good feeling.
Not sure your approached, but if you create the charts, then it would be easier to change the source data that feeds the charts, than point the charts to new source data. You can use Power Query to do this. Otherwise, VBA to edit the chart references.
Maybe your audience is not anyone who really knows, or is interested in doing something *beyond* "use a built in template". So, here, you could avoid that step and have something that is your own, create the project with the default thing, and just save *that* as a template. But nah, nope, don't do that. Make sure you do things in a nice coroporate way...
I'm not really surprised that you, @@MyOnlineTrainingHub don't "follow what I'm saying." Maybe I'm failing to understand that "the most basic stuff that shouldn't really be that hard for people to understand," is really, "fantastic content that people really need." I mean, I'm not surprised, because it's clear you're one of the "experts" out there, who are presenting expertise which is a 2 second Google search away... If that. And often *bad* advice, or bad assessment of stuff. It just really irks me that people like yourself are presenting yourself as some sort of IT expert, when you don't really seem to be presenting more than anything that is right basic.
❓What's one Excel task that always seems to take longer than it should?
FREE 239 Excel Shortcuts for Windows & Mac: bit.ly/3uydH1k
Hi Mynda! I have a large amount of sales data that I need to break out by salesperson in separate excel files. What is the easiest way to automate this? I can break into separate tabs but would like to have a process that automatically separates them into new spreadsheets. Thank you.
Maintaining conditional formatting when inserting or deleting rows in a table.
Is there a keyboard shortcut to remove the decimal places from a number (convert it to a whole number)?
More of an annoyance. For me it's when I CTRL+T, and that ugly default formatting is applied to the table. Then I have to go to table formatting to change it (usually to blank).
I frequently get data in Excel which has a column for date time stamp. I need to do a pivot table using the datetime. Problem is Excel never understands by default the value in that column is a datetime. I have to always split the date and time separately (using string functions), then use the "Text to Column" function to tell Excel what format the date is in. Then add back the time to the date. Is there an easier way to do this?
I learn how to do something better every time I watch your lessons and then I wish my job required more excel data analysis. You are one of the top Excel experts worldwide. Thanks for sharing!
Wow, thanks so much! Great that you're still learning. You never know when these tips might come in handy 😉
Glad to have your channel back Mynda!
I'm glad to have it back too 😅 Happy to share Excel videos with the community again!
Amazing tour........ Amazing possibilities. Amazing Training Hub! Thank you.
Thank you so much 🙏😉
These productivity tips are just what I needed! I love how you break down each problem. Can’t wait to try out those shortcuts!
Glad to hear you found some useful tips! 😊
Wow... I am already using all the 10 tips u have shown..... I can now declare myself as the KING of excel
Productivity king 😁
Great presentation, Mynda! Glad you are back online.
I'm happy to be back! Thank you very much 😊
Wow! I'm always amazed at Mynda's teachings.
Thank you so much, Bob!
Excellent tutorial. As always.
Glad you liked it!
Three good tips I didn't know. Thanks Mynda.
Which are those three tips? 😃
Glad to have the channel back.
Thanks for these great shortcuts and tips Mynda. In my work place I usually extract some flight data into excel and for a long time I have not been using Power Query until recently.
Its amazing how it can clean up data with just a few clicks, however am stuck on how to filter data between times before midnight and times after midnight. Example: (Between 16:45 to 03:50). I don't get any data while filtering the times using Power Query filter option😐.
Thanks for any assistance on how to get this right.
Thanks for your support! Regarding your Power Query question, you're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Control +Shift+V is one of my favorite new keyboard shortcuts that I found out about this year.
Great to hear!
Hi Leila
Another great presentation, I really enjoy seeing your approach to the different topics, even though I know the usage already.
The topics in this video are very well chosen, and easy to go to, I will allow myself to share it with my colleagues.
@ivanbork4175 Leila?? 😂
Thanks a tonne Mynda for the shortcut keys.. super useful .
Should I make a video featuring the most used shortcuts? 😃
Life-changing!!! Thank youuuuu
Wow! Wonderful to hear 🙏
Glad your channel is back. I thought i was going crazy. Thanks to your videos i can pretend to be an excel expert on my CV
Thanks for your support 🙏😁
Teacher . Thank you very much.
You're welcome!
Thank you! I am going to go through your workbook in a systematic way. I know bits here and there, but there is lots more I don't know
great to hear!
What a great great tips!!!
Glad you think so! 🙏😊
There's always something, thanks 👍😎✊
You got that right, Steven!
You always seem to teach me something new 😎
Glad it's worth your time to watch 😁
@@MyOnlineTrainingHub your videos ALWAYS are! 😀
Thanks for sharing!!
My pleasure and thank you for watching!
Excellent Mynda!
Thank you very much!
Thanks 🙏❤ Ma'am
Thanks for watching! Which one is your favorite tip from the video? 😃
@@MyOnlineTrainingHub 5.
6, 9
Great tips!
I'm happy you liked them! Which one is your favorite? 😊
@@MyOnlineTrainingHub the multiple excel files one was highly relatable
Thank you Mynda.
Thank you for watching!
Thanks Mynda
Thank you for watching!
Thank you very much. Your follower from Iraq ❤❤
Greetings to Iraq from Australia! Thank you for watching and for the support 😊
As always, beautifully explained
For me I take table names one step further. I give tables a tbl prefix, constants a cst prefix, named ranges an nr prefix and use camel case
Cleaning data from coworkers takes way longer than it should because they don’t do things like this
Great tips! Thanks for sharing 😊
As usual great video , for templates I’ll check them out , don’t know why I underestimate them and never tried them 🤦♂️
Surprisingly, many people don't know they exist. The built-in templates can save a lot of time 😃
Making multipe charts from power pivots but requiring different fields and calculations which takes me time to leatn how to create measures to include in pivot charts
DAX measures are certainly a skill that you have to build over time. Much like Excel formulas.
Thanks
Thank you!
Creating time intelligence dax measures or measures such as % of a subcategories or % of subtotals
Good to know 🙏
Great, what software did you use to edit your videos? I love the transitions
Thanks! Premiere Pro.
Great videos ... Thanks for uploading videos related to Power BI, is it possible to use Power BI instead of Microsoft Acess, or you will recommend videos of yours in which you briefly explain Power BI in terms of Microsoft Acess working .
Power BI and Access serve different purposes. Access is a database, Power BI is a reporting tool that can report on data in databases, among other sources like Excel, CSV files, web pages etc.
Hi Mynda! It is the most annoying repetitive task to colour variance columns with conditional formatting in a pivot table. I do use "apply rule" to embed per column. I however have approximately 40 pivot tables with four variance columns each. Recently tried custom formatting which is slightly faster once you created the first one, but that somehow clashes with the formatting in the data model and throws an error message on the file.
I hear you! It’s be great if the formatting could be applied to the whole table in one go but still be unique for each column. Can’t see them developing this though, so might be worth creating a macro to automate this repetitive task 🤔
Your Excel Periodic Table is a genious as the Table of Dimitry Mendéléev, indeed.
Thank you!
Keeping or maintaining format on my bezutiful charts every timemy poer pivots refresh, i tried ticking boxes to maintain charts but does not work, especially if it is a combo charts with secondafy axis,or if im wirking with negative values and need it to reflect as positive values on the charts
Try removing all filters before setting your formatting. That said, if the filters result in negative values only being present some of the time, I have a feeling it won't retain these settings.
I could not find the excel spreadsheet that had the hyperlink to all of your courses. Could you please provide a link to the spreadsheet please? Really enjoyed this video, very informative!
Never mind I found it in the Excel spreadsheet that you provided, I'm sorry!
Glad you found it, Mark. Please reach out via email if you have any questions as I won't see follow up replies to this thread.
Can you do a quick QnA reminding people the importance of cyber security considering your channel was recently compromised? Love all your content ❤
Working on it!
For me, it's always creating relationships between tables. I am yet to understand it, honestly.
Same. I have two data tables, each with Roster names, I want one Roster set to show the data from both, can’t seem to relate it. But I am new to this.
Interesting feedback 🙏
@BCOzSportsGamer, create another (dimension) table with a unique list of the Roster names. Link both tables to this new dimension table and when building your PivotTables, use the names from the dimension table. HTH.
When I need multiple reports which require same tables but different relationships I have to create multiple files of different reports using same tablesbut different relationships
Dear Mynda,
Is it possible to make the file used in the video available for download? 🤗
Sure, you can download the workbook here: www.myonlinetraininghub.com/10-excel-productivity-tips-for-work
@@MyOnlineTrainingHub Thank you for the treatment. 🤗
I clicked for the thumbnail...WOW, you look great
Hope you learned some Excel tips too.
@@MyOnlineTrainingHub yes ma'am
Is it just me or my display resolution is in HD but your video is a bit off? Thank you for your content, helps me to advance in Excel and BI.
I suspect the video is not playing in HD. Check the cog icon in the bottom right of the video.
#10 tip is what I need - But doesn't seem to work. I set Classic Pivot table checkbox checked but it still creates new tables as the excel default. What am I missing?
I'm not sure what you're referring to as the "Classic Pivot table checkbox" because in the default PivotTable layout dialog box there's no check box as such. You select it from the 'Report Layout' drop down > 'Show in Tabular Form'.
Hi,
May I ask u one question? What is the best laptop fit with Microsoft excel due to my daily report contains storage around 50MB with formula working?
I don’t think your laptop is the issue. You need to look at how the data is structured. This seems like something that should be done in PowerQuery rather than excel formulas.
What @Ch715A said.
@@Ch715A thanks for ur recommendation.
for me it's finding and replacing formulas based on new sheet names that I need to sum up
Great to hear 🙏
Is there a way to combine multiple tabs in one tab? (All colums are the same)
Yes, please see this video: th-cam.com/video/YOC-pEIuHpA/w-d-xo.html
An Excel task that takes longer than it should: Converting a Word table to Excel.
Issue #1: Hard returns in Word table cells. When converted, many of the cells in a row will be split or merged.
Issue #2: Too much text. Excel cells can contain up to 32,767 characters but only 1,024 characters can be displayed or printed.
I blame neither Excel nor Word. Training is provided in both apps, but people often do what's fast and easy for them at the moment, instead of what's useful long term for the project.
Another task: Printing someone else's spreadsheet. We're looking for a utility that generates for all worksheets in a workbook, a report of all used cells that contain content, versus cells that contain formatting only. Most of the workbooks we receive from clients are not formatted for printing; it's usually assumed they will be read on screen. In the practice of law, this is often not the case. Excel files may need to be submitted as part of discovery or submitted to the judge.
Interesting to hear these scenarios. I don't see a lot of use cases for Excel for lawyers. Thanks for sharing.
Converting non-conforming dates to proper Excel dates. Nothing seems to seamlessly deal with dates that are imported with the days and months flipped. I've tried Power Query but it doesn't seem to get it even if I lie about what 'region' date format I want...
You can change the date format using “change type with locale” as explained here: Power Query Change Type Using Locale
th-cam.com/video/QKgS3hrrmvw/w-d-xo.html
Please provide practice file for videos please
You can download it here: www.myonlinetraininghub.com/10-excel-productivity-tips-for-work
Something I always crash on is dates and date formats / regional settings.
Why, if not a secret?
Not able to download the short keys
Always redirected to the top of the page
What´s one Excel task that “always” takes too much time
It's not so much a task, but more a thing that happens from time to time when I import data from an Access DB into Power Query, that I get a message that the Database or the query in Access has already been taken up exclusively by another user - even though I know for sure that I'm the only user.
It can cause a bit of a challenge with updating!
Sounds like a SharePoint file checkout issue maybe?
@@MyOnlineTrainingHub Thank you for the advice 🙂
table of. Contents
Inserting a simple check box into a cell. Never seem to be able to find it.
I recommend you to watch this video of mine on this topic: th-cam.com/video/MB9DWvHXQLk/w-d-xo.html You can find the solution to your problem there 🙏
Just reminded of another pet peeve, how cell notes go haywire every time you set a filter...
Oh yes! Nightmare 🤦♀️
My Excel doesn't have the "Analyse Data" feature
It's available with 365.
I am basic
Teaching is good but teaching is faster
My no1 pinch point in Excel is the trainwreck of open/save windows, the recent list & irritating nagging to use OneDrive.
Not strictly an Excel problem but 365, SharePoint etc. The OG of this is the use of Desktop as both a container of 'everything', while ALSO actually being a folder under User.
Microsoft keep adding Win10 features like Quick Access... like a sticking plaster.
Now the multiple cloud servers I use each have a mirror folder on the HDD, adding even more confusion. Add random sync errors... mostly Microsoft, rarely Google.
Had an Excel error I might have mentioned before where the auto sync of the two files corrupted every single formula - silently replaced them with values. Never found out why of course. Watching formulae disappear in front of your eyes, not a good feeling.
I hear you, especially on the open/save backstage area. I use F12 to 'Save As' and open the original File save dialog box.
@@MyOnlineTrainingHub Now THAT is a Top Tip!
making 60+ charts from a single template, and having to change the data source for each chart.
Not sure your approached, but if you create the charts, then it would be easier to change the source data that feeds the charts, than point the charts to new source data. You can use Power Query to do this. Otherwise, VBA to edit the chart references.
Hail to Ctrl + Shit + V! :)
Maybe your audience is not anyone who really knows, or is interested in doing something *beyond* "use a built in template". So, here, you could avoid that step and have something that is your own, create the project with the default thing, and just save *that* as a template. But nah, nope, don't do that. Make sure you do things in a nice coroporate way...
Thanks for watching. I'm not following what you're saying, though.
I'm not really surprised that you, @@MyOnlineTrainingHub don't "follow what I'm saying." Maybe I'm failing to understand that "the most basic stuff that shouldn't really be that hard for people to understand," is really, "fantastic content that people really need."
I mean, I'm not surprised, because it's clear you're one of the "experts" out there, who are presenting expertise which is a 2 second Google search away... If that. And often *bad* advice, or bad assessment of stuff.
It just really irks me that people like yourself are presenting yourself as some sort of IT expert, when you don't really seem to be presenting more than anything that is right basic.