Need help with Excel and Power BI? You've come to the right place!
I'm Mike and this channel is where I post and share short tutorials that will help to save you time, boost your productivity and improve your Excel and Power BI skills. Whether you're a novice, an everyday user or a seasoned Excel/Power BI pro, I hope you like what you find here - and if not, why not make a suggestion for a topic.
My background...30+ years in IT training, working with and for a large number of global and UK-based companies and organisations across a diverse range of sectors.
Like what you see? Want more Excel? - Subscribe to the Channel - Check out my website: theexceltrainer.co.uk - Connect with me on LinkedIn www.linkedin.com/in/thomasmik
BTW...this is my new channel. My old channel is still active and there's plenty more Excel tutorials there: ru-vid.com
I was specifically looking for a solution to use on Tables. Fortunately I discovered a section of the "View" tab called "Sheet View" where I was able to do exactly the same thing without converting my table to a range!
Hello and thank you for this information. I oversee a large department... I use M365 excel web version, which I share with my team through Microsoft Teams... I have a large document with a tab for each employee (47), the data in those tabs is displayed in a dashboard tab for the director to see the departments standing... I need to share the tabs with each individual employee along with the dashboard, but I need to prevent them to see any other employees information... #1. Would I be able to do this with the web version? #2. would I be able to accomplish any level of protection in the M365 excel web version? If not, #3. would I be able to set up the excel workbook through the desktop version, secure it (hidden sheets, password protection) and then upload to MS Teams to share with the team and allow every employee to see their tab while updating the data every month? Thank you...
@AbigailVillafane-y5p The only way to achieve what you want to do (hide the sheets and have them made visible only if the password is known) is using VBA, Excel's built in programming language. You'd have to write the VBA code using the Desktop version of Excel. The problem is that the web version does not support VBA (writing or running) which means if the file was opened in the web version the VBA code would not run. Even if you decided "OK to use this particular file we must use the Desktop version of Excel" this is not 100% foolproof. Anyone with the appropriate knowledge (and it's find-able online) would be able to crack the code and view the sheets the should not be viewing. Maybe look for a different solution, either not Excel-based or if you do want an Excel based solution consider having a separate file for each employee and combining the data into the dashboard from those files. If you want to discuss further drop me a message via theexceltrainer.co.uk/contact/
Hi Mike, great presentation once again! Newbie question. On the sheets with Language cells, when I have the geography pull down and select official language, the cells fill with not only the "language" of the country but then has the word "language" immediately after. So for instance, for Belgium it has in the language cell Dutch language. How would I convert those to just the language? So it just had "Dutch" not "Dutch language". Thanks.
@SteveKrieg-us1pf Hi Steve. Glad you found this useful. There's a couple of ways to do this. Simplest way is to go to a blank cell, say H2 (this assumes you have generated the languages in column C like my demo file) and enter this formula: =LEFT(C2,LEN(C2) - 9). Then copy it down. What this does is extracts the first X characters from C2, X being the total number of characters in C2 minus 9. Why 9? Because that's how many characters are in the word language plus the space before it Once you have done that, select all the entries in Col H and Copy . Then click on C2 and use Paste > Values to paste the results of the formula over the top of the original formulas in col C
@@MikeThomas67 I'm sorry Mike, I'm not getting it or it's not working properly. I can get it to work in C2, but not in the other country language names in C3-C26. Steve
@SteveKrieg-us1pf You're using my demo file? I only tested with C2. When you copy down are you seeing #SPILL errors in col C? If so this is because some of the countries have more than one language. I've actually worked out a better way to do it. The formula is more complicated but it means you don't have to use an extra column and then copy > paste values In C2 enter the following: =SUBSTITUTE(IF(COUNTA(A2.[Official language]) > 1,TEXTJOIN(",",,A2.[Official language]),A2.[Official language]),"language","") What this does is counts the numbers of text values returned by the Official Language property and if it's more than 1 it combines them into a single cell entry separated by commas. It then substitutes the word language with nothing If you're still stuck reach out to me via theexceltrainer.co.uk/contact/
You can attach a Slicer to a Table or a Pivot Table. For this solution you do need a pivot table however there may be an alternative way to do it (which I havent looked at) that doesn't need the pivot table
Thank you Mike🎉🎉 you helped me a lot to fix this problem since other videos are all suggested to use wizard which is no doubt disappeared in excel for Mac nowadays. THANK YOU again for your so many professional tutorial videos ❤❤🎉🎉
Thank you, very helpful. Quick question; is it possible to have the cell value show "All" when all values in the slicer are selected ? could we work around this with a formula?
@ayoolaolamiekan1488 This is what I came up with... =IF(LEN(Pivots!N2) - LEN(SUBSTITUTE(Pivots!N2,",",""))=COUNTA(UNIQUE(Orders[State]))-1,"ALL",Pivots!N2) LEN(Pivots!N2) - LEN(SUBSTITUTE(Pivots!N2,",","")) counts how many commas there are in the string in N2 (generated by the TEXTJOIN function COUNTA(UNIQUE(Orders[State]))-1 will only work in Excel 365 - it counts the number of unique items in the State column of the Orders table and then subtracts 1 So if the number of commas is 1l fewer than the number of states, show ALL (we subtract 1 because there will be 1 less comma than states)
I gave a like and subscribed, unfortunately I still have my issue. I am new to Macros and I work with Windows in work and use Cells.EntireColumn.AutoFit but I couldn't find that in your file. Am I doing something wrong here?
@MrRobbieHolland Hi Robbie. This video was created to solve a specific problem in Excel on the Mac. There are 2 types of macros (on Windows and Mac)...macros that you run yourself and macros that run automatically. The macros that run automatically (known as event macros) do so in response to an "event" such as opening a file, printing, saving, moving from one sheet to another (there are more events than that - I have just listed a few). In Excel for Mac when you try and create an event macro you get an error. What I was listing in my file was simply all the macro codes needed to create the start and end of event macros. All macros start with Sub Event_Name and end with End Sub and that is all I was providing - the frame work if you like for each macro. So no you weren't doing anything wrong.
Bu eklenti excel kurdum. Ama URL yazdıpımda çalışmıyor. Neden olduğununu çözemedim. Bir ayar yapmak gerekiyor mu? Yada bu eklentinin güncel haline nereden ulaşabilirim? Size hata kodu atsam bana yardımcı olabilirmisiniz? Şimdiden teşekkürler
Is this a one time use mail merging method? I'm trying to save a word doc template that contains the mail merge linkage for future use too. I tried other methods but I wasn't able to save it for future use. My goal is to update the source excel file periodically and reprint the mail merge doc with new data and updates
Just tried using this method and it worked for one website but not another. For the failed URL, I receive this following error message: Cannot locate the Internet server or proxy server. For what it's worth the webpage shows the data in a table that is filterable by many characteristics. Any idea on how to get past this error to get the underlying data? Thanks.
Thank you so much for this tutorial. Is there a way to set the sheets in such a way, that an external user can see the different sheets at the bottom of the workbook, but when they want to click on the sheet, they cannot view it without inserting a password? Meaning, does the sheets have to be hidden and password protected OR can it just be password protected to restrict viewing....just so that I don't have to change the settings each time and unhide only certain sheets for each user that I have to send the document to. Hope this makes sense what I am asking.....
@mardeleenmuller8177 Yes it is possible to have all the sheets displayed and when someone clicks on a sheet's tab it pops up a password request. The only way I know to do this is using VBA - Excel's built in programming language
Great tips! I admit that I get impatient with the custom format choices too. I end up guessing. lOVE the trick of copying the pivot tables to use that custom format in another file. Thank you!
I did not find this helpful for my purposes, but THANK YOU for being so transparent with your not wasting time. This was BRILLIANT, and I appreciate you!!
Thanks, Mike. Often when we looking for tricks like this it means it is rush hour, I always feel uneasy whenever I watch other videos and someone starts giving me stories "about how their great grand father fought in First World War, then next they say sponsor of this videos is, next they say subscribe!..like a bunch of unnecessary information"
Hi Mike, Im struggling with the table design example at 3:37 on my Mac Excel with the downloaded spreadsheet. When I click on the table, the caret at the bottom doesn't show up and the table design menu doesn't auto show. I am using Version 16.87 (24071426) of Excel. Appreciate your videos, they really help a noob like me. Thanks, Steve K
@skrieg1436 Hi Steve. I messed up! I uploaded a version of the file where that data wasn't in a Table. You're the first person to mention it to me. Please try again. I've uploaded the correct version this time
Hi Mike ,nice to meet you , I think that this video is very useful and invaluable, pls help me out to use in full Power Toys and your video in my future work pls tell how to enable Hex field in fill in the colour tab in Excel , since I don-t have this in my Microsoft Excel
