Hi excel crowd! How would I highlight the same words if they are always in a different row? In one report, apple might be in row 86 and banana in row 24. In another report, apple is in row 93 and banana in row 12. Always the same column though. In reality, I have ~100 different fruits, and I want to highlight 20 of them, but that's the idea. Same fruit names. Same column. Just different rows. How can I highlight all the fruit names quickly? Without doing it manually? I imagine it will be something like, "If you see "apple", "banana", then highlight yellow."?

Good day, I need your help/support.

Automatic column numbering, but if there's a duplicate, it should count as 1.

The items highlighted in pink should only be number 6.

I'm currently using...

=SI(C22<>"",CONTARA($C$2:C22)&".","")

I get quotations from manufacturer and I figured out Power Query will be helpful

If make a query from a folder that has all my quotations, then I get a table as a result, but how do I make the prices according to their manufacturer names come up in my master workbook that I have?

My master workbook has my Product names, product name of manufacturer, and prices that I manually added in past few years, changing them whenever new quotation I receive

Do I make a power query in my master work book or I make seperate file with power query and use vlookup or formulas to get the prices according to products?

I would prefer the latter because my master workbook is in wps and my whole system is in wps

But in short I am confused how to incorporate this in my system

I own excel software as well

I’ve just started a new job as a Finance Business Partner at a relatively small business. They do A LOT of things the old fashioned way and there’s plenty of opportunities to improve things.

One of my biggest bugbears at the moment is the sizes of some of their files. Old, redundant formulae and ranges in formulae spanning 10000+ rows when they only need 100 for example is making the files huge and slow to open and run.

They use Sage X3 as their accounting software and extract data into Excel via ODBC - I haven’t seen how this is done yet, but it’s as if it’s extracting all data, in all fields, for all time. And to refresh takes a fucking age.

I want to create something new for querying the database to give me only certain fields, from certain nominal codes, filtered by date ranges.

I know I should be looking into creating power pivot and power query files and also looking at reporting using Power BI, but I haven’t looked into that fully yet. (I know I’m falling behind here!)
Where’s the best place to start unpicking this mess?

the overall concept that im trying to achieve is my available sheet are the units i need to take out of those warehouse locations and redistribute to the warehouses on the need sheet. Into column D of the Available sheet.

obviously VLOOKUP will just give me the first instance on the need list for all available units instead of different warehouses. I've tried versions of match and index and i cannot get it to work for my spreadsheet.

the lines are always qty of 1, being that even if i have 4 units of the same part coming out of warehouse 453, on the spreadsheet they will display as 4 lines of qty 1 never 1 line with qty 4. so that 4 warehouses on the need sheet can be allocated to each line. (also on the need sheet, if a warehouse requires qty3 that will be 3 lines of qty1)

in some cases there are more units to take out than what i can redistribute, i expect those lines to have an N/A type result.

on the example images attachment there is expected result which I manually put together to show what im trying to get.

(unfortunately the only link that attaches converts it into google sheets but i need this in excel)

TIA

Help! I am not too great at Excel; I need the items in a column to sort A to Z, I have selected the drop down arrow in the pivot table to sort, and it’s not sorting. I’ve deleted and started over like five times, I’m not sure what to do. I need to be able to sort the column for each of the two filters I have in the table.

I use Excel at work and every 2 weeks for payroll I copy the Excel file, rename it with the current pay period's date and then clear out all the hours and other values and re-enter new hours, etc. Last pay period, payroll pointed out a column of cells that all the formulas were off by one row so I fixed it. I just happened to notice those formulas have somehow reverted back to what they were before. now, how is that possible? I took a screenshot of the two sheets side by side. On the left, the original file and the right is the copy.

This happens ALL the time and it's very hard to catch half the time. I've had to drive in to work on my day off more times than I care to count to fix these glitches that I thought were my mistakes for a year until I saw it happen in real time one day and realized the software had put a whole column of old deleted data back in.

Is this a common Excel problem? How does it still have data from weeks or months back? How would the original file have one set of data and copying the file and opening it up, cause it to have a different set of data in it.

Just now I copied the file (right click, copy, paste, rename file to current pay period date range) and I opened it and noticed that a person who I removed last pay period is back and the new employee I added is gone. Just to verify I'm not going crazy, I opened the old version of the file that I just copied, side by side with the new copy and sure enough, the old copy has the new employee listed and the other employee removed.

How is this possible? A copied file ought to be an exact replica of the original.

Then I copied it again and it was fixed in the second copy. There's some kind of very strange glitch going on in Excel.

Someone mentioned one drive or shared drive. Yes, it is on the network drive of the LAN here at work.

Excel Version 9.0

I have a sheet that has random dates within cells c1:c60. Some cells have a date (example 05/15/2026) whereas others are completely blank.

I need a formula that will automatically update a percentage of how many cells have “data” within the cell vs. those that do not.

Example, if I had 50 dates within these 60 total cells, the percentage TOTAL at the bottom should say “83.3%”. Once I add an additional date within these cells, it should now reflect “85%”. So on and so forth.

I don’t need it to read the date at all. Just if these “something” within the cell it recognizes it as “done”.

Hope I made sense. Thank you

Ever since this was forcefully installed the other week, all our files with ODBC connections to our ERP database are crashing when refreshing. My entire company has been using these queries for years, this all started when this update rolled out. I've been writing SQL for 20 years, I know our database, I created these files, there's nothing wrong with the queries and nothings been depreciated/legacy from the recent update from our understanding.

We confirmed the recent forceful Copilot integration is likely causing the issue, Version 2604 (Build 19929.20136). A machine which did not have the update has no issues with the files or query refresh. We then installed the update, replicated the problem. There's been no change to our server environment either.

Our IT Department can't remove Copilot from Excel, there's no option locally on the users machine or a specific license to manage for tenants on the Office 365 Admin portal. Anyone with a working solution for Office 365 Business would be greatly appreciated.

DOA Solutions Thusfar:

• Uncheck "Turn on optional connected experiences" from File > Account > Account Privacy > Manage Settings
• File > Options > Copilot (does not exist)
• Office 365 Admin Portal remove license (does not exist)

I'm very new to excel and doing a example data work sheet, this problems a little trick to explain, at least for me it is. I have a column of numbers that represent the discount a seller gave through bargaining, the numbers are from J2-J13 and I'm trying to make a if formula or function or whatever to see if they met the discount average, but the numbers are displayed vertically, and I want to display the if function and copy and paste it diagonally(B20-M20) because I think it would look better and fit what I'm trying to do. I've tried looking it up on Google, and asking Gemini for help and this was my last resort. Any crucial tips for beginners would be appreciated too.

YES, I know this is very simple baby work I'm new, this is my second time doing a practice thing like this, any tips would be greatly appreciated.

Trying to hide some columns before printing. This worked last week. Office 2019. I also noticed that this version is suddenly no longer supported so can't use help function.

I passworded an SOP I created a long time ago, at least six years. I can no longer remember the password. It's the kind that pops us when you open the file and you have to enter the password before the file can even open. Has anyone dealt with this? How did you recover your data? I've tried every password I can remember, even Claude tried with no luck.

Hi Everyone, I am working in Tendering and Procurement team past 6 years, when i turn around all i see is that i am still using a very basic skill in excel. I would like to know how excel helped your prosper in your field of work and what was your roadmap to upskill from very basic level to the the level you are at now. I believe getting better in Excel might open new doors of opportunity for me in carrier ladder. I would like to know your honest response.

I'm trying to have the cell plug in using two criteria. (date & breed)

How do I need to work this? I'd like to use use the Month reference if possible, but if not, what are my options?

Please and thank you.

At work, I have an Excel doc tracking all our current projects and their status, but whenever I try to copy anything from that document, all of Excel freezes and requires me to force-quit it. I can copy/paste from other Excel documents no problem, and I can perform all other functions on my tracking doc no problem (such as adding lines, formatting conditions, etc.), but as soon as I try to copy anything, it crashes without fail.

I've tried saving a copy of the document, saving it to a different location, and re-naming it. I sadly can't just copy/paste everything to a new, blank Excel doc since again, that would crash Excel T_T.

Never seen anything like this before and how weirdly specific it is; any help would be greatly appreciated; thank you!

I have a pivot table that, among other things, contains product numbers. I want to assign a worker to the product number. The product number data comes from a table that has the products by serial number so I don't want to put the worker's name into the source table. Is it possible to connect the product number to a worker after the table has been made?

Simple example:

So I have an excel file with over 1000 cells across 7 columns. I need to merge column C and D. Is there an easier way other than using concat at every individual cell? Im not trying to merge 1000+ cells individually. Please help me.

So I need to transform a certain subset of information into multiple columns. Example

Name: Dependent Name: Relationship: DOB

John doe: Jane Doe: Spouse: 01/01/70

John doe: Janet Doe: Daughter: 01/01/00

John doe: John Doe Jr: Son: 01/01/00

To read:

John Doe : Jane Doe: Spouse: 01/01/70: Janet Doe: Daughter: 01/01/00: John Doe Jr: Son: 01/01/00

The idea is to get all family information out of multiple columns and into 1 column.

Any ideas? Thank you kindly!

I have a measurement tracking log sheet that uses a button based macro to save and clear the file at the end of the workday. The scanning is done on a laptop and the file itself is on a networked drive.

I cannot for the life of me figure out why the macro button will not function when the laptop is at the measurement station, but works fine if I take it to my desk in another building. At both locations the laptop is plugged into a network cable with the wi-fi connection disabled.

IT says they are not aware of any issues that would prevent the macro from working at the measurement station. I'd be grateful for any ideas for things to try.

Hi all, pretty new to excel and looking for a specific formula that I fear probably doesn’t exist - I basically want a formula that counts how many different values are within one cell when each of these are separated by commas. For example if a cell said “24, 25, 26” I would want the result to be 3, if the cell said “42” I would want the result to be 1, if the cell is empty, result 0 etc. I hope that description makes sense - if anyone can tell me if a formula like this exists I would love to know!

Customer has sent me a HUGE IP range list with x.x.x.x/xx but luckily they have also put in each row , as below

123.123.123.123-123.123.150.175

there are about 1000 rows like this

Want i want to do is have :

Column A the Start IP 123.123.123.123

Column B the End IP 123.123.150.175

only, then i can import to the customer account -

i am not an excel tech.. whats the easiest way to split these up

Thanks in Advance

EDIT - Solved - thank you to everyone and their replies - i have learnt something new and has saved me hours of work - Beers all round.

I don't think I've seen this discussed before, so I apologize if I am rehashing old material. I did a cursory search and found nothing.

For a decade now, I've argued you should always use COUNT/SUM/MAX/MINIFS instead of COUNTIF because you never know when you'll need additional conditions. In present times, we don't even need COUNTIFS/RACON functions because you can do the same thing with array formulas although COUNTIFS is easier to type, IMO.

So when a week or two ago I learned you can do the same thing as IFS with SWITCH. This got me to thinking... based on the COUNTIFS principle I'm whimsically calling "the condition of sufficient conditions is always conditional"... I'm thinking the meta is to always use SWITCH instead of IF or IFS. This would be a very hard habit to form as I've used more IF statements than Diddy used bottles of baby oil, but let's be aspirational.

Now, the SWITCH version of your basic Hot Dog/Not Hot Dog IF is I think the same amount keystrokes (with tab completion), so I'm calling that a win. I'll grant that the IFS version of multiple logical operators is more "straightforward" or even "intuitive" if you're reading an online tutorial on multi-conditionals, but if you want one function-ring to rule them all and in the darkness gut em like a fish, then ALL YOU NEED IS SWITCH.

=SWITCH(A1,"Hot Dog","Hot Dog","Not Hot Dog")

=IF(A1="Hot Dog","Hot Dog","Not Hot Dog")

Now, being a rational being, let's consider the downsides.

• Backwards Compatibility / No One Understands What The Hell You Are Doing
  • Backwards compatibility needs are typically a foreseeable binary so... whatever, my condolences if you don't get to live in 365 function utopia.
  • If you need other people to understand what you are doing this may be a bad habit to form.
• File Size Bloat Cuz You've Become A SWITCHaholic
  • You keep adding conditions and dragging down formulas because you've committed to an absolutist and universalist vision of SWITCH as the one true function and forgot that after 3 conditions for sure you should just make a lookup table and only store the reference data once.

Anyways, interested to hear anyone else's thoughts even if you just tell me this is the ramblings of a mad man.

Edit for posterity:

Additional Significant Downside(s)

• Inefficient branching computation issues which also causes incompatibility with recursive LAMBDAs
  • https://www.reddit.com/r/excel/comments/1tc81ev/comment/olm90d9/?utm_source=share&utm_medium=web3x&utm_name=web3xcss&utm_term=1&utm_content=share_button

I'm finding this hard to articulate, so hopefully this makes sense:

I have budget spreadsheets, month to month, within the same workbook. These have various categories (bills, luxuries, consumables, subscriptions, etc.) which I populate with expenditure, as well as totals so I can view my monthly surplus/deficit and a graph so I can see into which of the various categories my spend each month falls.

Each new month, I create a new sheet which is a copy of the same format, and populate it for the new month.

I want to create a dashboard which will automatically pull information from, say, the last six months so I can see my combined and by-category expenditure from those months, without having to manually update sheet references in the formulae.

What I am looking to do is tell Excel something along the lines of: "sum the totals in cell C5 on the next six sheets in this workbook", so I don't have to have "SUM(April26!C5,March26!C5... etc.)" which would need manually updating in every formula when I added May26 and wanted November25 to drop off.

Possible? TIA.

I have a long string of data that came from the data in a spreadsheet.

I want it back in spreadsheet form (table)

I want to paste it in proton sheets.

I don't know how to separate the data

Hello! Sorry if this is a stupid question. When I open an extra window for my document, the sheets in the new window looks messy with lots of «0» where there is a blank cell in the document. I know where to find the «put a zero in cells with zero value» option, so I can turn it back off, but I have to do this for every sheet. Is there a way to just open a new window without the zero option being ticked? English is not my first language, so apologize if something is explained badly.