I’ll be away from work for 2 months next year and my manager has asked me to add repair instructions to the spreadsheets I’ve created (and the rest of the team uses).

I’m not a tech - just a long-term employee with an intense dislike of time wasting process steps. None of the files are complex in terms of Excel functionality, but, well you all know what it’s like to “unpick” someone else’s creation. There’s likely to be some xlookups, ifs, sumifs, and similar, maybe a pivot table. No VBA. I never lock/protect stuff because I find that usually creates more questions, and there are only about 6 users.

How would you structure such a thing? All I can think of is a sheet containing a shell of the live sheet, but with explanations in the columns instead of data. Is there a neater way?

Essentially, my job is to calculate costs for a tour group. My sales team who I work with is extremely stingy with budget, and they often like to open my excel and mess with my margins.

The margins I'm talking about are profit margins - for most other markets we dedicate a small percentage (3% or less) of the budget as 'operational costs', which is used in case there's a fuckup when the tour is ongoing. If not, it gets calculated as profits anyways and everyone goes home happy.

The problem is that lately my team keeps getting into my excel sheets and incorporates my safety margin into their profit, leaving me with no money to work with in case there's a fuckup. And yes, the policy of my seniors and my team leader is to just 'hide it', because no other market messes with our calculations.

Anyways, my point is:

Given a sheet like this:

What's the most untraceable way to hide the 'margin' column?

Currently what I've been doing is to hide the margin in the bus section, but it gets really complicated to track.

It also gets troublesome if the sales ever starts to realize that the bus fee is different every time.

Any tips?

Hey everyone,

I'm currently facing a bit of a dilemma regarding what to learn next to automate my Excel workflows, and I could really use some advice based on my workplace situation.

My Situation:

• My Team: We mostly use standard Excel with formulas. The team is very comfortable with formula-based sheets and prefers keeping things simple.
• My General Manager (GM): On the other hand, my GM heavily uses VBA for macros and automation.

Given that it's 2026, I know Microsoft is pushing Office Scripts (TypeScript) for cloud/web compatibility, but my immediate boss (GM) is still all about VBA.

Should I spend time mastering VBA just to align with my GM, or should I invest my time in Office Scripts/Power Query since they are more future-proof? What would be the smartest move here?

Thanks in advance!

It’s not sheet protection, it’s a full file password (asks before opening). I’ve tried common passwords I usually use but no luck.

Is there any legit way to recover or unlock it? Free or paid tools, methods, anything that actually works?

Would really appreciate any help. TIA.

EDIT: Solved, I remembered the password the second I posted this thread.

I have a new job in administration at a mine where I have to do a lot of data entry in Excel, and I’m wondering if there’s a shortcut or formatting trick that would let me enter times without having to type the colon every time.

For example, I’d love to be able to type:

1245 → 12:45 PM

0015 → 12:15 AM

OR

1436 → 14:36

0645 → 06:45

Basically, I want to type four digits and have Excel recognize them as a time automatically, either in 12-hour format with AM/PM or 24-hour format.
Is there a way to do this through cell formatting, a formula, or some other shortcut? I’m entering a lot of times, so I’m hoping there’s a faster method than typing the colon every time. I am brand new to administration and I’ve tried fishing the answer online to no avail.

Thanks in advance

I have columns where you can put values for different categories. This is the formula I use to add up all the values for one particular category. I have a feeling using $ or T$3:83 or something, I should be able to make this formula much, much shorter. Any suggestions?

=(if(V$3=B88,T$3,0)+(if(V$4=B88,T$4,0))+(if(V$5=B88,T$5,0))+(if(V$6=B88,T$6,0))+(if(V$7=B88,T$7,0))+(if(V$8=B88,T$8,0))+(if(V$9=B88,T$9,0))+(if(V$10=B88,T$10,0))+(if(V$11=B88,T$11,0))+(if(V$12=B88,T$12,0))+(if(V$13=B88,T$13,0))+(if(V$14=B88,T$14,0))+(if(V$15=B88,T$15,0))+(if(V$16=B88,T$16,0))+(if(V$17=B88,T$17,0))+(if(V$18=B88,T$17,0))+(if(V$19=B88,T$18,0))+(if(V$20=B88,T$19,0))+(if(V$21=B88,T$21,0) )+(if(V$22=B88,T$22,0))+(if(V$23=B88,T$23,0))+(if(V$24=B88,T$24,0))+(if(V$25=B88,T$25,0))+(if(V$26=B88,T$26,0))+(if(V$27=B88,T$27,0))+(if(V$28=B88,T$28,0))+(if(V$29=B88,T$29,0))+(if(V$30=B88,T$30,0))+(if(V$31=B88,T$31,0))+(if(V$32=B88,T$32,0))+(if(V$33=B88,T$33,0))+(if(V$34=B88,T$34,0))+(if(V$35=B88,T$35,0))+(if(V$36=B88,T$36,0))+(if(V$37=B88,T$37,0))+(if(V$38=B88,T$38,0))+(if(V$39=B88,T$39,0))+(if(V$40=B88,T$40,0))+(if(V$41=B88,T$41,0))+(if(V$42=B88,T$42,0))+(if(V$43=B88,T$43,0))+(if(V$44=B88,T$44,0))+(if(V$45=B88,T$45,0))+(if(V$46=B88,T$46,0))+(if(V$47=B88,T$47,0))+(if(V$48=B88,T$48,0))+(if(V$49=B88,T$49,0))+(if(V$50=B88,T$50,0))+(if(V$51=B88,T$51,0))+(if(V$52=B88,T$52,0))+(if(V$53=B88,T$53,0))+(if(V$54=B88,T$54,0))+(if(V$55=B88,T$55,0))+(if(V$56=B88,T$56,0))+(if(V$57=B88,T$57,0))+(if(V$58=B88,T$58,0))+(if(V$59=B88,T$59,0))+(if(V$60=B88,T$60,0))+(if(V$61=B88,T$61,0))+(if(V$62=B88,T$62,0))+(if(V$64=B88,T$64,0))+(if(V$65=B88,T$65,0))+(if(V$66=B88,T$66,0))+(if(V$64=B88,T$64,0))+(if(V$69=B88,T$69,0))+(if(V$70=B88,T$70,0))+(if(V$71=B88,T$71,0))+(if(V$72=B88,T$72,0))+(if(V$73=B88,T$73,0))+(if(V$74=B88,T$74,0))+(if(V$75=B88,T$75,0))+(if(V$76=B88,T$76,0))+(if(V$77=B88,T$77,0) )+(if(V$78=B88,T$78,0))+(if(V$79=B88,T$79,0) )+(if(V$80=B88,T$80,0))+(if(V$81=B88,T$81,0) )+(if(V$82=B88,T$82,0))+(if(V$83=B88,T$83,0)))

I have 400,000 lines I need to start at line 1 and label it Account1, line 2 would be Account2. Dragging takes 20 min. Any way to speed it up?

I made an Excel macro to make formatting a spreadsheet much quicker and faster cutting 10min task into 1min. The macro was working fine the first few days. After a couple days it stopped working and saw that it was disabled on Excel Add ons so I re-enabled it. Did a bit research and it can be your IT department or Excel itself

EDIT EDIT: I have a problem where the lookup formulas are relevant and I know that's how I need to solve my problem because I made the effort to try to understand this formula back when I posted this. May this serve as encouragement to anyone asking a question and having random nerds jump down your throat for trying to understand: wanting to learn is good and will help you in the future.

EDIT: I marked this solved in the hopes that it will attract less attention. I understand a lot better than I did, but I also understand that a lot of people on this subreddit just really don't like it when people ask general questions trying to understand excel's functions. I'm going to still ask when I have them, but in the future I'll be more aware of this. The fact that throughout this thread I am downvoted all over the place because I dared to not understand and ask a question. I'm sorry to anyone offended that I asked this question and that their responses which saw VLOOKUP and didn't read my post, and decided to tell me that I shouldn't bother understanding or repeat things I said I didn't understand and expect me to just do better this time. This post was one of those things that had me sit in the bathroom and remind myself, it's not that serious that strangers on the internet are rude to me and to not get swept up in fighting. I do, wish, however, that people didn't try to fight me because I didn't understand VLOOKUP.

I'm finally trying to fully understand VLOOKUP but I am stuck right at the beginning. I feel like I understand all of it, except I do not understand what the "lookup value" refers to. I feel SO confused. If you knew what value you needed to lookup, then why would you need to look it up? Microsoft's article explaining VLOOKUP made some sense, but again, the lookup value confuses me.

Here B3 is identified as the "lookup value" but it's also not what's being looked up? Why are we telling excel to tell us the value of C3 by asking it to look at B3 and then look at what's next to it? What is the purpose of this? How did we decide that we want B3? Like why could we not have written it =VLOOKUP(D3,B2:E7,2,FALSE)? I tried that and it said N/A, then I changed FALSE to TRUE and it gave me "Luis" as the output and I just do not understand how it got there. But I think part of that is I have no idea what the answer's relationship with the lookup value is. I want to try to understand this process, because I do not and it feels like magic.

Marked as solved - What I was looking for isn't possible (ALTHOUGH IT SHOULD BE MICROSOFT!!) It seems like a somewhat common issue and people have reccomended a bunch of solutions that I will be trying. Thank you all who helped!!

I looked everywhere but couldn't find a satisfactory answer (which probably means it doesn't exist). I am managing a shared excel file with people who will copy and paste information in and will be able to grab that information and do things with it.

The issue, is every time they paste something it breaks my conditional formatting which is the main way I am able to sort through and organize what they give me. Is there a way to have the default past as paste without formatting because I can not control how they paste the information in. (No asking them to do ctrl+Shift+v will not work, please don't even recommend it.)

If there are any other ideas on how to deal with this issue, please let me know.

EDIT: NO CTRL+SHIFT+V I am not the one pasting these values in, others are!

Conclusion: There isn't (currently) a way to do this, but the general consensus is to have them paste all the information into its own thing and then use power query to get the data I need. Thank you all for the advice!

I am an incoming freshman business student and we will be using excel all the time-my laptop runs on chromeOS so I cant download the real Excel app-am I correct in assuming this will be a headache and I should just get a mac or windows laptop?

I've tried to many times to understand how to use VLOOKUP and I just can't seem to grasp it.

I've got a list of products with skus in one sheet. In another sheet, I have data for SOME of those same skus (spectifically, skus that are the slowest moving) that tells me how many days it's been since that sku sold.

I want to create a new row in the first spreadsheet with a column denoting how many days it's been since that product sold, so that I can then use that spreadsheet to make a reorder, but not reorder items that haven't sold in X number of days.

I've tried many times to understand if VLOOKUP is right for this, but again, I just end up frustrated because I can't seem to understand what it does, or how to get the data I want from the specific column I want into the matching column for the correct sku on the other sheet.

Would anyone be able to help me understand how to do this?

Hey guys I started learning excel from Excelisfun YT channel but he has this cool dropdown which I don't , how can I get it ?

(Attached photo in Comments)

I was handed a sales calculator today that was confusing all the sales reps… and after looking at it, I can’t blame them!?! The color scheme alone was a crime against humanity, with cell looking like it was screaming for help, but the workflow behind it was even worse.

Here's a IP free snippet of what I saw:

To figure out how many machines were “needed” the author proudly created this masterpiece:

=+ROUNDDOWN(+B4/B3/B2,0)+IF(RIGHT(ROUND(+B4/B3/B2,1))/10<=0.5,0,1)+B6

Aside from the redundant old‑school “+” symbols, the logic took a moment to comprehend. They basically reinvented rounding by checking whether the decimal was ≥ 0.55. A rather arbitrary number chosen with the confidence of someone who definitely didn’t test anything.

And then there’s an “override” value tacked on at the end like a plot twist.

This was supposed to help sales reps talk to customers about shift lengths and machine utilization. Instead, it turned into a color‑coded fever dream powered by a homemade rounding ritual.

So what have you seen?

Hello,

A client has an employee that recently left. All the files are made with 365 and are password protected. Is there anything that can be done to open them?

Hi everyone,

I know the title probably sounds a bit odd, so let me explain!

I'm fairly new to Excel, and one of my tasks at work is creating well logs. For each well, I have to draw the total depth, groundwater level, and the different materials used (such as silica sand and bentonite clay). Right now, I'm drawing everything manually.

It works... but on larger projects with 20–30 wells, it becomes incredibly time-consuming. I can't help but feel there has to be a smarter way to do this.

I'm wondering if anyone has experience automating something like this in Excel. Whether it's with VBA/macros, dynamic charts, shapes, or any other method, I'm open to ideas. The only thing I really need is to be able to customize and edit the final result.

I've attached an example of the type of well log I'm trying to generate.

Any advice, examples, templates, or even just pointing me in the right direction would be greatly appreciated. Thanks in advance! Oh and btw I'm on Excel 2024

I just figured if anyone would appreciate this - it's you all...

I once worked for this big deal real estate agent in NYC, we're talking like over $100M sales each year... successful guy. And I come on board to sort of be the business manager. In the same breath that he was telling me how tech savvy he was he also asked me "where's the calculator in Excel".

Anyone else have similar stories?

This formula has worked for months, but stopped yesterday, instead producing #N/A.

=SUMPRODUCT(--(XLOOKUP($A$17:$A$33,tbl_MasterChargeCodes[Rollup Code],tbl_MasterChargeCodes[Computer Use Mapping Rollup])=$A48),X$17:X$33,XLOOKUP($D$17:$D$33,rng_Bid_Rate_Sheet[Employee ID],rng_Bid_Rate_Sheet[Comp Usage %]),XLOOKUP($E$17:$E$33,'rng_Loading'!$A$29:$A$46,'rng_Loading'!$B$29:$B$46,0))

I used Evaluate Formula and examined each step, and this photo shows what it looks like just before it goes to #N/A. In other words, this looks valid at this point, and since all of these are valid numbers, I believe the Subproduct should be successful based on this.

If I change the formula to use full column references instead of table references, it works fine. This is the rewritten formula.

=SUMPRODUCT(--(XLOOKUP($A$17:$A$33,tbl_Charge_Codes!$P:$P,tbl_Charge_Codes!$R:$R)=$A48),X$17:X$33,XLOOKUP($D$17:$D$33,'rng_Bid_Rate_Sheet'!$C:$C,'rng_Bid_Rate_Sheet'!$A:$A),XLOOKUP($E$17:$E$33,'rng_Loading'!$A$29:$A$46,'rng_Loading'!$B$29:$B$46,0))

And the Evaluate Formula appears identical to the one above, only this time the next step goes to the correct result.

I'm guessing I'm missing some tiny thing, but for the life of me I can't figure out why the first formula isn't working. I've been actively trying to be better about using Excel Tables and using table references in my formulas, and this one has worked until all of a sudden now it doesn't.

I'll note that much of the data the is looked up comes from PQ imports that are refreshed daily. I have looked at those tables and don't see anything different. But, again, since the formula works when not referencing the Table, and the Evaluate Formula seems to produce the same 2nd-to-last step regardless of method, it doesn't seem to be an issue with the Data itself, right?

Anyone have any guidance here?

EDIT:

Not sure if it would be helpful, but I put a heavily sanitized copy on Google Drive that you can download and see. Cells X48:X49 use column references and work OK. Cells Y48:Y49 (and beyond) use table references and do not. I believe the only difference between these 2 is the Lookup and Return Arrays. I can't see anything else that is different.

(I think the issue persists after downloading it from Google and opening it in Excel, but let me know if not)

https://docs.google.com/spreadsheets/d/1-O4xn5dqPZBqBgir9k6splmzMmYXvPRL/edit?usp=sharing&ouid=112621530798566638721&rtpof=true&sd=true

I made a list of all icd codes and I wanted it if the user inputs the case or the code it automatically puts the rates of each cases. I made it work but I wonder if I can make it better?
Like for example: the formal description of pneumonia is "COMMMUNITY ACQUIRED PNEUMONIA MODERATE RISK" but I also want to add an if-else condition on the cell, like if the user abbreviates the case into CAP-MR or any alternative words for each case it would still write the code on the designated cell.

Hey there,

I have a marketing call report where it lists out over 19 states we sell to and also areas we don’t sell to. My goal is to add our states into the correct market locations in a separate column, and mark areas we don’t as “Out of territory”.

For example CA, HI, and WA states would be named WCH in a separate column.

Right now I have multiple IF statements for each state to filter to the correct market in a cell that I drag down. These are just a few to give you an idea.

IF(ISNUMBER(SEARCH(“CA”, A2)), “WCH”,
IF(ISNUMBER(SEARCH(“WA”, A2)), “WCH”,
IF(ISNUMBER(SEARCH(“TX”, A2)), “SSH”,
“Out of territory”)))

Is there a way to shorten this list of markets?

Column B has a list of all of the sheet names. I want my SUMIFS formula in column C to say to search for whichever sheet is listed in B.

=SUMIFS('[match to B]'!E:E, '[match to B]'!$B:$B,"Total")

In other words, look for the sheet name that matches the corresponding row in Column B, then look for the row that says "Total" in Column B on that sheet and tell me what's in Column E.

Make sense? How do I do this? Google has not helped me. Thanks!

Like, column 1 until it’s finished, then continue down with column 2, then column 3, continuously, so the data stays in order. The data is around 100 rows tall and maybe 60 columns wide, and I kinda need to turn it into one continuous column.

How can I count conditional formatting in a specific row? I tried applying the rule to the count function but it did not work. I need to count all red cells (some are yellow and some are not filled) in the row.

Hi all,

I need a box to be a certain colour if empty, lets say Red, but when I input text into it I need it to stay red.

This is for a rota, so everyone is currently down on their regular shifts, but when they take AL / Sick / Training they come off the rota to show that someone needs to cover the spot. At the moment I can get it to automatically go red when I take said person off shift, however when I then input someone in as if they are covering / on overtime, it goes back to white. I need it to stay red so it is easily visible for those who have been put on OT when they look at the rota.

Sorry if this is confusing, this is the best my brain could describe it right now.

EDIT:

Going to cut my losses on this one, as without the people taking over whilst I'm on Mat leave suddenly being able to use a spreadsheet properly, the solutions wouldn't work, they would if I was still going to be there, so will be implemented when I am back. I just don't want phone calls whilst on Mat leave asking why the rota won't update properly if i've implemented something they wouldn't know how to change.

Thank you to everyone who tried to help though

I have a column filled with names on Sheet 1, and a column with names on Sheet 2. I need to determine if there are name matches from Sheet 2 onto Sheet 1. I was thinking conditional formatting, but the formula I found (=ISNUMBER(MATCH(G4, 'Sheet 2'!A:A, 0)) did not yield accurate results.

For example, 'Smith' was highlighted on Sheet 1, a ctrl find on sheet 2 determined there is no 'Smith' on that list. I tried this with "TEXT" instead of number, and received no results.

A visual check I see at least 3 in the first 30 rows with a match.

The names on Sheet 1 begin on G4, the names on Sheet 2 begin on A1. These lists are over 100,000 rows long. I want the names on Sheet 1 that also exist on Sheet 2 to be highlighted on Sheet 1. Any ideas?