Agreed Bill, I'd definitely use a custom format for the "Discount" wording. However, I probably wouldn't have the drop-down on the face of the invoice itself. I'd probably have the entire invoice cells protected, and the worksheet protected, with it simply updating when I select the percentage from a drop down. The reason I say that, is because it is likely that there could be several inputs to the invoice, aside from just discount. Perhaps they would be in a nicely designed input form on the left, with the invoice itself on the right, recalculating as the inputs are changed. If you leave just the input cells unprotected, and set protection to select unlocked cells only, then you can tab between the inputs for faster entry. This stems from my complete lack of trust of the general user - formed over years of shattering disappointment - and my experience that they invariably start deleting rows, re-formatting cells and just generally making a mess of things, which would ruin the beautifully presented invoice. I always try and separate the input (customer, discounts etc) from the output (invoice) as if you were using an actual accounting system. Not that I have control issues, of course.

Tnx👍

You write that long ass description but dont put the code in? Im lazy lol

Hi ! I have created a drop drown list in excel ( like 10%, 20%, 30%) So when I wanted to apply the discount from the drop-down list on the invoice. It shows( like 10%, 20%, 30%) but I want it on the invoice that shows ( Discount 10%, Discount 20%, Discount 30%) i’ll be appreciated if somebody can tell me the formula

I have something ODD with my version of excel and check boxes. (Version 2502 Build 18516.200000) insert check boxes in A1:A10, then in B1 =Countif(A1:A10,True), and as i check the boxes, the countif will not update. Now if i download a file from you, or Mark, or Lea, or Mike, and open their workbook, it will work perfectly. But if i add a sheet and follow along with their video and make my own in their workbook, it will not work. i have contacted MSO, but no reply --- any ideas?

Life saver! Thank you.

Very useful Thanks

I was wondering, I have created a map for sales from different states, however my data shows different cites have sales. Is there a way to have the state on the map show the sum of sales.

I'm wondering how this would work with text, rather than numbers?

Who wrote the song! Is it available as a single?

Returning to this video after years of struggling to collab 15+ workers work output excel data, every day, using just vlookup. Now I look back with google sheets opened in my chrome tab and chuckle. Things have progressed a lot

Easy solution is to downgrade your office subscription to not have copilot and pay a bit less.

I have a full family license for 365 and on the Insider Beta edition. Even though I have a new computer that has a Copilot key and Copilot key, I didn't get Copilot in Excel until 01/16/2025. It was added without a change in Excel's version which was Microsoft 365 MSO (Version 2502 Build 16.0.18502.20000) 64-bit. Every time I opened a workbook, I'd immediately right click on the icon and hide it. Then, around 01/25/2025 I was updated to Microsoft 365 MSO (Version 2502 Build 16.0.18516.20000) 64-bit, and now that icon never appears! I do NOT have any code in my Personal workbook doing it! That said, I keep almost all of my Excel files in Dropbox. After a recent Windows update, OneDrive took over my Desktop which was my default file and download location and had to change everything over to OneDrive. The reason I bring this up is because Copilot will only work on files saved to OneDrive! If anyone knows any work around, PLEASE let me know! ALSO, as I mentioned my laptop has a Copilot key located to the right of the Right Alt key where the Right Mouse key used to be. I've been able to disable it using PowerToys which determined the key was F22, but have been unable to find out what they key is (presumably an F key higher than 12). If you have PowerToys and that key, it would be appreciated if you could let me know. Thanks!

Copilot crashed and burned! Thanks Bill et al

"A tiny macro, now you are gone for good!" 🎶 Awesome!

Many thanks!

The problem in cell C13 (At 2:25) can be solved thus: =CONCAT(REGEXEXTRACT(B13,"\d*",1)) BTW, this formula: =CONCAT(REGEXEXTRACT(B13,"\d+",1)) ("+" instead of "*") will yield the same result

Thank you for your informative videos!

I turned off Copilot by going into Options & unchecking in Copilot section. I have no use for Copilot at all!

Latest update and restart comp. took floating icon off even with Copilot kept enabled.

Hey Bill, I think the code you'd want to use here is this: Application.CommandBars("Copilot Menu").Controls(Application.CommandBars("Copilot Menu").Controls.Count).Execute. I just tested this in a French version of O365 (v2411) and there were only 12 controls. So assuming that the number of items will change, and the "hide" will always be last, I think this should work.

Did you reach out to someone on the Excel team? I know you are connected

what a fast solution accompanied by an amazing song. Thanks MrExcel.

Jeepers! I made a comment and you wrote a song. What a world! Next up, try opening a new document in Word and see what punkrock Clippy invades your document.

Power query. Data type date. Add column name of day. Insert pivot table and deploy slicers.

I like how you frame the questions. If I am a hiring manager, I would ask this question set because I want candidates to think I want to know the right answer or the best options for solving the specific Excel functionality questions. What I actually want to know is how they take in and process the problem and how they approach and articulate the solution. A technically correct answer might be barrier to entry for consideration, but much more important is the way they understand, clarify, and communicate. And the exact set of nuances I will be looking for will depend on what the position needs most.

I think that if the hiring people are clever, they will be happy with any method as long as it provides the right answer.

I agree with the way you narrowed down the day of the week and class, but I would've probably used TEXTSPLIT to separate the date and time.

I always learn something new in your videos!

I think you passed the test. Regardless of what they were looking for lol They were definitely looking for that slicer but I think though

Bill, ur vlogs r always good.

I think it's a result-driven question by the personnel manager. So whatever way the applicant does it, the manager will accept. A better interview question would be "HOW DO I GET RID OF THAT FLOATING COPILOT LOGO ATTACHED TO THE CURSOR!?!?!"

IMO you can't do this just within the pivot table because of the weekdays. I simply would ask whether they want extra columns in the table or use either Power Query (or Power Pivot if I wanted to show off). In Power Query getting weekdays is easy in case anyone is wondering. The weird things managers want will never cease to amaze me. My absolute"favorite" was the guy who had a list of customers from around 20 different countries. He wanted a formula which would generate an address field that would be formatted according to the customs of that particular country - even though post offices are perfectly capable of translating foreign addresses. I just LOVE doing work that's completely unnecessary - NOT.

Great video !! Formula alternative: (dt=date clm, cl=class clm,tt=tot clm) =PIVOTBY(INT(dt),cl,tt,SUM,,,,,,LET(t,WEEKDAY(dt),((t=2)+(t=4))*(cl=1)))

Thanks so much for the assistance. Saved me a lot of time. Could you speak slower and slow down a little please? I had to watch it multiple times to catch everything,

Absolute best tutorial on calculating cummulative sum... brilliant. I mean the first option.

Thanks 🙏! Fantastic!!!

Thanks 👍🏾

I used the followiung =SORT(UNIQUE(VSTACK(B2:B15;C2:C15;D2:D15;E2:E15;F2:F15;G2:G15))). Took me 30 seconds... But, besides... watching your video made me realise that I thought I know Excel, and now I know I still have a long way to go...

just amazing

It didn’t work

perfect, Tnx

The last time I had to do something similar (different process) I ran into a character limit (URL limited to 255 chars I think). Do you know off the top of your head if this has that same limit? If I find time to confirm first, I'll try to update this. Also going to use this space to mention to fellow viewers that if you've reading this because you've watched this vid and just set up a personal.xlsb for the first time, you'll want to make sure you're periodically backing that up if you end up relying on it. It's easy to forget and probably not grabbed by your typical cloud backup.

Very good. Suppose I want to insert a new column between COGS and Profile - I have to recalculate the subtotals. *BUT* doing that loses my formatting! Grrr! Can I avoid this?

I keep getting a spill error and can't figure our what I'm doing wrong. Can you advise? The details are from another worksheet

I absolutely fkn dispise excel.

Thank you

Is not there any VBA code to perform F2 > Enter on bulk cell range?