Protect Excel Files - Keep Your Reports Safe

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

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

  • @swiftg6176
    @swiftg6176 3 года назад +2

    Mynda, the fact that you've had this channel since 2010, and still you take time to "like" all 55 comments says a lot about you as a person. For this reason alone you have a new subscriber, fan,
    newsletter
    expertise

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  3 года назад

      So nice to have you as a subscriber :-)

    • @swiftg6176
      @swiftg6176 3 года назад

      @@MyOnlineTrainingHub Mynda, I'm not sure what happened to the rest of my comment. But I believe it said I visited your website (myonlinetraininghub.com) and subscribed to your weekly newsletter. And thank you for sharing your expertise. Sorry about that.

  • @ashasukumaran6
    @ashasukumaran6 3 года назад

    I use protect sheets in most of my files but when ever I watch one of your videos I learn something new. This video did not disappoint either. Thanks for covering these little topics that we all think we know. Kudos to you.

  • @jessiesantos2868
    @jessiesantos2868 3 года назад +2

    Another awesome tutorial by Mynda, you're absolutely an excel genius. Everyone who's watching should take advantage of this because not all instructor are rendering their service free of charge. Kudos!

  • @mohamedbillow5364
    @mohamedbillow5364 3 года назад

    Thank you so much for your videos. I like your approach, pace and expounding more on the topic.All your videos were helpful.Imagine watching one of your video every night before going to bed.Thank you a 1000 times.

  • @rajanghadi5635
    @rajanghadi5635 3 года назад +1

    Very nice. As always with proper pace so that even a beginner can understand.

  • @swimfast8076
    @swimfast8076 2 года назад

    Thanks for all the examples and explanations. Watching your videos has helped me to become proficient at excel dashboards. Your videos are clear, precise, informative and more importantly one can use your teaching right away. Congratulations!

  • @wayneedmondson1065
    @wayneedmondson1065 3 года назад

    Thanks Mynda. Glad you mentioned that Worksheet and Workbook protection available from the ribbon buttons is easy to bypass (it is). Also, just learned that you can now unhide more than one worksheet at a time directly from the Unhide dialog. Been waiting for that for years. Thanks for sharing :)) Thumbs up!!

  • @martinargimon730
    @martinargimon730 3 года назад

    HI Mynda. Many Thanks for your quick tutorials , they are always very helpful and u are a superb teacher.. Thanks again ., all the best ! Martin (south africa)

  • @loupasternak
    @loupasternak 3 года назад

    Glad you reminded people that the password protection is not robust.

  • @spilledgraphics
    @spilledgraphics 3 года назад

    Best video about protecting an awesome piece of work (the dashboard!). One and only tiny suggestion: hide columns from W to the last one (right) and row 36 to the last one (down), and that´s it! This "hiding columns step" I always do, to frame the area on the worksheet where the dashboard or report ...."lives & breathes..." 😝🤙 lastly good points on minute 2:00 and 2:45 for the steps in protecting the visual objects !

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  3 года назад

      Thanks so much! Yes, I agree to hide the unused rows and columns. I was leaving those tips for a separate tutorial on presenting your dashboard ;-)

  • @009hjs
    @009hjs Год назад

    Thanks for the clear explanation 👍

  • @aguerojg
    @aguerojg 3 года назад

    Great video and very useful Mynda, thank you very much for sharing these insights!

  • @John-ol1cs
    @John-ol1cs Год назад

    Great video! Very informative.

  • @anthonygikuri
    @anthonygikuri 3 года назад

    Thank you. You rescued me.

  • @danielventura8447
    @danielventura8447 3 года назад

    Thank you so much for your videos! It has been great help! I always learn something new or the usages of a specific tool I´d heard before.

  • @ashifp
    @ashifp Год назад

    Very useful video.
    Thank you.

  • @buttercakemonster3171
    @buttercakemonster3171 3 года назад

    Thank you so much. Well explained & easy to understand.

  • @jonk3361
    @jonk3361 3 года назад

    I have been looking for something like this for a long time. Thank you so much🙏🏼🙏🏼🙏🏼

  • @AccessAnalytic
    @AccessAnalytic 3 года назад

    Well explained Mynda - Subscribed!

  • @MlguelM
    @MlguelM 3 года назад

    Excelente Mynda! Muchas gracias!

  • @komanguy
    @komanguy Год назад

    Great tip! Thanks a lot!

  • @jeremy_317
    @jeremy_317 7 месяцев назад +1

    Great video! Is there any backend benefit/enhanced security by preventing any editing for the entire workbook upon initial entry (file -> passwords -> prevent editing = making it read-only) vs protecting/"locking" all sheets/ workbook from within the file itself (Review Tab -> Protect Workbook/Protect Sheet)?
    Other than differences in read-only versus locking from a frontend accessibility standpoint (still can unhide arrays in read-only for example), is it more advantageous to use both forms/"layering" of security, or will the locking-features from within a file be enough?

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

      Read only can be bypassed easily by saving a copy of the file and removing the 'read only' setting, so I'd say there's no real advantage.

    • @jeremy_317
      @jeremy_317 7 месяцев назад

      Ok thank you!

  • @aviman2010
    @aviman2010 2 года назад

    Hi Mynda. Another Gold Standard tutorial. I have a workbook with a couple of slicers and a timeline but, when I try to disable resizing and moving, the tick box is greyed out.

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  2 года назад

      Thanks, Tony! I don't have that issue with timelines in my files. Maybe try inserting it again.

  • @mattschoular8844
    @mattschoular8844 3 года назад

    Thanks Mynda. Keep up the great content

  • @JV44HeinzBar
    @JV44HeinzBar 3 года назад

    Nice tips. I'll be using this in the future.

  • @filipwinski4219
    @filipwinski4219 3 года назад

    As always, valuable and useful content !!! One of the problems I haven't found a solution anywhere is how to secure the "official" Excel table (ctrt + T) so that the user can edit the rows (add new, change or delete) but he/she is not allowed to change the table structure (headers, formats, adding or removing columns etc.).

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  3 года назад

      Thanks, Filip. The Table issue is a known limitation. Unfortunately, there's no fix for it at the moment, but we complain regularly to the Excel team at Microsoft, so hopefully one day we'll see a solution.

    • @filipwinski4219
      @filipwinski4219 3 года назад

      ​@@MyOnlineTrainingHub Thanks for the reply Mynda. I also hope MS will fix it soon.

  • @darrylmorgan
    @darrylmorgan 3 года назад

    Hi Mynda!Great Tips,Unfortunately I Have To Use These Techniques In My Office...Thank You :)

  • @NanasNumbers
    @NanasNumbers 6 месяцев назад

    Thanks for all you do. I have a question. I know there is a way to make an Excel so that if someone sends it to another person, it will stop working. I have Excels that I sell & customers have to enter in current data. I understand how to unlock certain cells [so customers can enter current info] & lock others [so customers cannot edit or copy formulas], then protect the sheet. I also add copyright info in locked cells so that no one can resell it. But there is nothing stopping them from looking up a RUclips video on how to remove that protectection. But I know others in my same niche who create Excels & somehow they protect that Excel from being emailed to anyone else. This is exactly what I need so that no one steals my work & resells it. It's been a huge problem. I come out with a new Excel product and after a month of sales, everyone has it. The customers in my community are very close & share information, so after one person buys it, they just send it to all of their friends. I know there is a way to do this, but all the people selling Excels in this niche are very close mouthed and DON'T share knowledge.

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

      I'm not aware of a way you can prevent people from unlocking an Excel file that you have given them access to i.e. if they can open the file then they can unlock it AFAIK. You might be best to reach out to the person whose file you've seen that has this functionality, assuming you've tried to unlock it an were unsuccessful. I did a video on how to remove password protection on Excel files here: ruclips.net/video/24rISp-naqI/видео.html

  • @AccountingandQuickbooks
    @AccountingandQuickbooks 3 года назад

    Very useful thanks

  • @faddtayo
    @faddtayo 3 года назад

    thanks alot for this video....

  • @chrism9037
    @chrism9037 3 года назад

    Thanks Mynda!

  • @ufuksakar6080
    @ufuksakar6080 3 года назад

    You are very successful 👏👏
    Great video thanks for turkey

  • @buddybenmir0811
    @buddybenmir0811 3 года назад

    You are life saver! I learned a lot from your videos. Quick question, can you also lock the way your excel looks like when you send it as attachment? Like for example, when you hide the ribbons, formula bar, headings? And even some of the changes you made in advance option when not showing the horizontal and vertical bars and sheets? Thanks in advance! 😉

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  3 года назад +1

      Glad it was helpful, Mark! Some settings are retained at workbook level, like the hiding of the scroll bars and sheet tabs, but the ribbon is on a per user basis i.e. not something you can retain at the workbook level.

    • @buddybenmir0811
      @buddybenmir0811 3 года назад

      @@MyOnlineTrainingHub thanks for your response. I will take note of that. The things I am learning from you is helping me a lot in my work. Keep up the good work. More power to your growing channel! Cheers!

  • @chrysnotreally
    @chrysnotreally Год назад

    Thank you!

  • @JawadJawad-qm5dw
    @JawadJawad-qm5dw 3 года назад

    Thanks very much my teacher!

  • @danielosorio217
    @danielosorio217 6 месяцев назад

    What could be better methods to add security features that can't be broken for protecting a workbook and sheet-level other than using the standard 'Protect Workbook' and 'Protect Sheet"? I was thinking along the lines of using VBA macros or other methods. Thanks for any suggestions!

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

      Simply put, Excel cannot be secured. VBA or no VBA, you can remove passwords as shown here: ruclips.net/video/24rISp-naqI/видео.html
      If you need to secure your reports, then Power BI is the solution: ruclips.net/video/gOs7EC-FebE/видео.html

  • @ricos1497
    @ricos1497 3 года назад

    I normally just make my workbooks read only, or an xlst, because I get fed up of unprotecting every time I need to make a change! I usually use a plus (changing to a minus on selection) icon to replicate the pivot chart +/- buttons using a macro. I find the standard ones a bit ugly and also leave the chart open to being moved, resized etc by a clumsy user.

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  3 года назад

      Read Only is another great way, Rico! Thanks for sharing. Keep in mind that Read Only won't prevent your users form seeing your formulas and workings, or moving your charts around, so if that's important, then worksheet/workbook protection is the way to go.

  • @markmediate6092
    @markmediate6092 3 года назад

    Fantastic! Thank you!

  • @JaniceCook-jx8pw
    @JaniceCook-jx8pw Год назад

    Thanks for this. How can I protect the sheet but still allow data groups to be expanded and collapsed?

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  Год назад

      Hi Janice, at the Protect Sheet dialog box check the box for 'Format rows'.

  • @apcorreia2005
    @apcorreia2005 2 года назад

    Thk U so much for your incredible work! I'm a great fan! Can you post the link of the video wich contains the dashboard construction please? I'm trying to do something like you (seems) to have: a combobox filtering all the dashboard objets. I can not find it. Once again, congrats for your work!

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  2 года назад +1

      This dashboard is shown in this video and the link is in the video description: ruclips.net/video/rsx43g7TBBs/видео.html

    • @apcorreia2005
      @apcorreia2005 2 года назад

      @@MyOnlineTrainingHub Thk U for your quick answer. Is it the same dashboard? I couldn´t find the combobox in the upper left corner. sorry.

  • @shoppersdream
    @shoppersdream 7 месяцев назад

    Very Nice, thanks! I won't be able to change the Data Source for my Pivot Table if my Sheet is Protected. Right?

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

      Yes, correct. You can test it to be sure though 😉

    • @shoppersdream
      @shoppersdream 7 месяцев назад

      @@MyOnlineTrainingHub Got it, thanks!

  • @vaishalikrishna5894
    @vaishalikrishna5894 2 года назад

    I would like to know how to create different view in a same dashboard. One for manager view and one for employee view. When employees click the excel specific sheets should not be viewed. When managers click it all sheets should be viewed

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  2 года назад

      Better to use Power BI for this type of data segmentation. There's no proper way to do this securely in Excel. You can learn Power BI here: ruclips.net/video/Of2ML6TjkAI/видео.html

  • @lucalenzi9922
    @lucalenzi9922 2 года назад

    Another great video Mynda. Unfortunately, it seems not work in my case, where the pivot tables don't refresh. In my file, I have pivot tables created from PowerPivot with tables loaded into data model from power query linked to external files. Can you confirm that I must use another strategy to protect the file but allow the updating of pivot tables?

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  2 года назад

      Hi Luca, looks like that's a limitation. You can embed your reports in a web page instead: ruclips.net/video/uvA-U9FKgPw/видео.html

  • @SuperDarekR
    @SuperDarekR 3 года назад

    Thank you so much

  • @emad28871
    @emad28871 3 года назад

    Despite that, the right-click option is still active for the slicers even the worksheet is protected, so that unwanted changes can be made. How to disable the slicers right-click menu ?

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  3 года назад

      As I said in my reply to your duplicate question on my other video. You can't do anything about it.

  • @imran16285
    @imran16285 3 года назад

    Thank you very much :)

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

    I am Kwesi from Accra Ghana.
    I have created tables in excel sheet but any time I protect the sheet I can't grow my tables when I want to insert additional data. Can you assist me to overcome this challenge.

  • @aviman2010
    @aviman2010 2 года назад

    Sorry, previous comment should have referred to the timeline only. Slicers were fine

  • @yulinliu850
    @yulinliu850 3 года назад

    Thanks!

  • @rahulkalingeri1206
    @rahulkalingeri1206 3 года назад

    Hey Mynda, Thanks for another very useful video :-)
    I have a question. Is there an option to disable resizing and moving of charts similar to slicers?
    I have drill down buttons on my chart and I want the users to interact with the charts. So, I would want to keep the charts unlocked but I would want to lock it for movement or resizing similar to slicers. Is this possible?

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  3 года назад +2

      Thanks, Rahul. You cannot lock a PivotChart if you want the user to be able to click the expand/collapse buttons on the charts.

  • @annadraganova
    @annadraganova Год назад

    hiya is there an option, where i can leave all sheets visible to others, but have all sheets protected from editing ? Like the protect sheet option, but for 20 sheets on one go ?

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  Год назад

      You can, but you have to set the protection one sheet at a time, unless you write some VBA code to automate it.

  • @emiliaalejandrakuschnir5199
    @emiliaalejandrakuschnir5199 2 года назад

    How can I group/ungroup columns in a protected sheet?

  • @shashikantbehera07
    @shashikantbehera07 Год назад

    Thank you for the video.. but the chart on top right is still movable.. I want my user to interact with the + & - signs but not drag and move it.. that's not happening.. tried the don't move option in formatting but doesn't work.. Do you have any other ways of doing it ?
    Thanks in advance!

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  Год назад +1

      Unfortunately, if you want to be able to click the +/- buttons on the Pivot Chart then it will also be movable.

    • @shashikantbehera07
      @shashikantbehera07 Год назад

      @@MyOnlineTrainingHub Right. Wish Excel has a alternative to that.. Anyway, thank you for the response!

  • @UghUgh1234
    @UghUgh1234 Год назад

    would love to know how to protect 50 sheets at once.

  • @martinargimon730
    @martinargimon730 3 года назад

    Hi mynda. Just a quick one. Do u share your reports with your clients via ‘Onedrive’?, and if so , I understand that U can either give them the right to view only or to edit. There is very little tutorials on hiw to share all our reports and sheets ‘on line’ and i would think that the ‘essence’ of the ‘on line’ version is to get more people to use this feature. I use it all the time , but tutorials in general do not regard this topic as essential. The whole world is shifting into ‘on line’ sharing and yet we see NOT much on ‘on line’ interface. Am i right or fantazising ?

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  3 года назад +1

      Great idea, Martin. I'll add online sharing to my to-do list.

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

    Is there any way to make it so that the user can use the +/- drill down buttons on the pivot chart, without it being so that they select the chart, the pull handles appear, and they're able to move the chart around if they're not careful? I'm desperately trying to figure out how to do this.. 😭

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

      Unfortunately, no.

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

      @@MyOnlineTrainingHub tragic... thank you for replying though! Spent a couple hours this morning trying to work with Chat GPT on a VBA solution. It offered one, but I can't get it to work for some reason. Super annoying. Trying to build a dashboard, and hate that there isn't a cleaner way for users to interact with these charts without it going into this sort of "editing" mode.

  • @hrithikzgirl83
    @hrithikzgirl83 2 года назад

    This is great! However, my users are still able to right click on the slicers and make changes to the settings.
    I want users to be able to use the slicers without being able to right click on slicers. Does anyone know how to do that?

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  2 года назад

      Not possible, I'm afraid.

    • @hrithikzgirl83
      @hrithikzgirl83 2 года назад

      @@MyOnlineTrainingHub Oh that's unfortunate. Thanks for the reply and your amazing work!

  • @deepakrajmano950
    @deepakrajmano950 3 года назад

    I want user to allow using grouping and ungrouping. How to enable group and ungroup in a protected sheet without doing unprotect and password at all. I tried everything i knew please help

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  3 года назад

      Don't think you can as there's no option to allow user to hide/unhide rows and columns. You would have to write a VBA solution.

  • @teoxengineer
    @teoxengineer 3 года назад

    Mynda Hi,
    Problem is that we are able to use VBA for unprotecting worksheets or workbook. For example, if we locked a worksheet and send it our colleagues and then if anybody knows VBA editor, he/she can make it unprotect.
    So,
    is there any technique to protect sheets from users even if who knows VBA ?
    Thank you

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  3 года назад +1

      No. Excel wasn't designed to keep confidential data secure. The protection is designed to make it difficult for people to break your file, so the security isn't that robust.

    • @teoxengineer
      @teoxengineer 3 года назад

      @@MyOnlineTrainingHub If we locked VBA a security password, how does Excel act regarding data security? I tried it first time yesterday and achieved full protection for sheets&workbook if anybody knows VB aditor. However, I want to be sure whether it is safe way or not

    • @mattknap6528
      @mattknap6528 2 года назад

      @@teoxengineer if I understood it corectly you have managed to protect Excel worksheet & workbook using VBA - can you tel me how did you do it (for instance, using Macro with userform and password)?

  • @mynights85
    @mynights85 3 года назад

    i cannot access the protection tab while using the online office 365, like when im on a chromebook, help !?

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  3 года назад

      Hi Kelly, worksheet protection can only be set in the desktop client. Once you open the file in Excel Online, the protection settings are respected.

  • @rafiullah-zz1lf
    @rafiullah-zz1lf 3 года назад

    The exel tends to round off a big number to exponents and once it is saved even if someone try to convert it to number it will show zeros. What is the work around

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  3 года назад +1

      Not sure what you define as a big number, but please see this tutorial on custom number formatting which might help: www.myonlinetraininghub.com/excel-custom-number-format-guide If you're still stuck, please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

    • @rafiullah-zz1lf
      @rafiullah-zz1lf 3 года назад

      @@MyOnlineTrainingHub numberrs like 13 digits or more in a cell are converted to exponents ...

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  3 года назад

      You can just convert them back to Number to show the actual digits.

  • @sumonmajumdar762
    @sumonmajumdar762 2 года назад

    I want to disable resizing and moving the timeline but I could not because it does not allow me to select under the position menu. Please help. Thanks

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  2 года назад

      Timelines have limited functionality, unfortunately.

    • @sumonmajumdar762
      @sumonmajumdar762 2 года назад

      @@MyOnlineTrainingHub Thanks for your prompt cooperation

  • @khalidabdullah939
    @khalidabdullah939 2 года назад

    Im trying to lock certain rows and coloums but everytime I lock them, the whole shee gets locked. Somebody, help me. 😭

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  2 года назад

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

  • @wndmier90
    @wndmier90 3 года назад

    Hola!!!

  • @Giovanni-vx8xl
    @Giovanni-vx8xl 3 года назад

    Thank you :)