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 community, my first post, please be gentle!

I have a colleague at work who always - always - removes filters on a shared spreadsheet when he’s finished with using it. Not clear them, totally remove them. So the next person must always re-apply them. He’s done it for years and I’ve never asked why, as he’s not very approachable. In my years of working in offices and with other people I’ve never met anyone so dedicated to removing filters from every spreadsheet they lay their hands on. It has baffled me for years but it’s got more intense recently as we both use more of the same documents.

Am I missing something here? Is there some logical reason for this behaviour that I am missing? Open to any answer, just trying to understand if I’m the idiot before querying with them directly, as it’s beginning to drive me insane.

Thanks!

At the end of each month, I download our company's transactions and dump them into a backup folder. I have an excel file for each month going back 14 years. Same format, same structure excel sheet every month.

I'm looking for a solution to combine all of this data into one massive database. However, seeing that each file contains over 4000+ rows, combined, that excel file can easily contain over 700,000 rows of data making it impossible to use. Maybe something that I can recall a certain year or buyer instead of recalling all data at once.

Any suggestions or ideas here would be appreciated!

Hi everyone,

I'm currently working with a pretty large Excel file that's around 2GB in size. Whenever I try to perform simple tasks like sorting a list by A-Z, Excel either crashes or becomes unresponsive for a long time before I have to force close it.

I am running a Ryzen 5 2600 with a 1660 ti GPU and 16gb RAM. Does anyone have any tips or tricks to handle large Excel files more efficiently?

Edit: I was sent this file by a business that I work with. It doesn’t contain many formulas and is just a massive table containing product ID numbers and names of products and links to corresponding products.

I'm trying to learn Excel for a job interview and want to know if you can do VLOOKUP backwards, I.E you have the value of something but want to find what it is associated with. So the example I'm currently working with is with video games and the amount of copies they sold each quarter, if I wanted to look for the game that sold closest to 1300 copies, how would I do that if the games are on the left side of the table and my copies sold are on the right side of the table? Thank you 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)))

It would make my life a lot simpler if people could leave formulas as is in their sheets so I don't have to do the math every time. If people paste everything as values, is there a way to find out how the values were calculated?

Thanks

I understand that I can format the cell with 0000 to get it to display as such, but I need to be able to use =LEFT(xx,2) to get 09.

In case I am way off the mark in my method, I might as well explain what I am doing. This data is imported from a website that is keeping track of information by time. What I need the sheet to do is add up how many times something occurs every hour which can be done with a basic COUNTIF formula and counting how many times it finds 09. But since excel doesn't want to keep the value of 0900, it thinks =LEFT(XX,2) = 90 rather than 09.

Hello again everyone. Reposting from last week after having tried some of your solutions to no avail. I am trying to calculate a median value from a subset of information. On Excel 2019 version 16.

The guide I followed suggested this format, but it returns a VALUE! error:

=MEDIAN(IF(L2:L200, “Same”, Y2:Y200))

Also tried your suggestion:

=MEDIAN(IF(L2:L200=“Same”, Y2:Y200))

Which outputs 0, but there are no 0s in the subset of data.

L column is checking for subset "Same", and Y column contains the number values I want the median for. Is it possible that the issue is because the values in Y column are the result of a formula?

Thanks in advance to all you wizards!

Access or excel?

I'm familiar with excel (and Google sheets) so I generally use those for spreadsheets and data entry and lists and all sorts of things like that. I happened to stumble into an Access file and saw the hkme toolbar looks very similar to the "data" tab in excel, so I'm under the impression it's a similar tool, perhaps even specialized in what I use excel for.

Half the time is personal use for video game stuff and the other half it's documents and sheets for the small business I work for.

Is it worth it to learn Access and convert relevant files over to Access? Is it much different to learn? Is it easier or harder to write a guide to using it compared to excel?

I can answer whatever I can to help clarify what I'm doing as needed.

----EDIT----- The conclusion: continue with excel.

I'm familiar with it, i am capable of writing instructions for future users, and apparently rather than full support for Access it seems to be more accurate to say its just being sustained.

The scale of the information generally seems to not be large enough to warrant proper DBMS at this stage and the business won't be expanding quite that for my purposes within my expected time here. What we do have that's under my influence is small enough and simple enough to even be easily transferred to a new system manually if that needs to happen.

Thanks for all your responses.

I've actually been trying to find a way to do this for years! But today I stumbled upon the thread "Formula for Ignoring Certain Words when Sorting a Table" and it seems to have the solution.

The fix was in a post where a person said:

The only way to do that is to add a helper column with the MID function in my formula as the formula for that helper column and then sort by the helper column. =MID([@title],1+(LEFT([@title],2)="A ")*2+(LEFT([@title],3)="An ")*3+(LEFT([@title],4)="The ")*4,99) or a bit shorter =LET(z,[@title],MID(z,1+(LEFT(z,2)="A ")*2+(LEFT(z,3)="An ")*3+(LEFT(z,4)="The ")*4,99) where [@title] is the cell in that row in the title column.

I was trying to replace @ title with @ Movie List but that obviously isn't right because I keep getting errors like The first argument of LET must be a valid name.

https://preview.redd.it/48zfmrpk1xhd1.png?width=646&format=png&auto=webp&s=86285a5cdd830485d5cee595b59a2bd81bfbf4af

Any specific function about excel that made your life lot easier and you wish you had known it earlier.

In the marine sector, seafarers are required to log their sea service in Months and Days. A trivial spreadsheet surely, given a vessel Joining Date and Signing-Off Date? You'd be right, but the-powers-that-be stipulate rules. The rules for UK seafarers (because these apply to my situation) are:

• A month is defined as a calendar month, or 30 days if made up of periods less than 1 month.
• One month is calculated from the date you joined the vessel to the preceding day of the following month irrespective of the number of days served.
• Odd days should be added together and reckoned at 30 days per month.
• No day may be counted twice.

So, if you join a vessel on the 15th of one month and serve on board until the 14th of the following month, that is calculated as 1 month sea service.

Eg: From 3rd Jan to 5th March calculates as 2 months and 3 days sea service.

Eg: From 19th Jan to 9th April is 2 months and 22 days sea service.

I’ve been trying to come up with a way to elegantly Excel this, but so far I’ve been unsuccessful. Websites exist that calculate this for you (opaquely), but I like to manage all my own data whenever possible. Here is one such site: https://onboardtime.com/

Ideally, I’d like my spreadsheet to give a 'Months & Days' total for the trip, and have a separate running total of 'Months & Days'. Any suggestions? Thousands of seafarers will be eternally grateful!

Excel version: anything modern, *.xlsx

Edit: Here is the exact text from the official paperwork: 'LENGTH OF VOYAGE: this must be given in calendar months and days, e.g. from 3 January to 5 March = 2 months and 3 days. Odd days should be added together and reckoned at 30 days to the month.' Available here. (.pdf file)

Edit: Specifically, the Excel I'm using is LTSC Professional Plus 2021

Edit: Kinda solved, (courtesy of /u/ExistingBathroom9742 with assist from /u/PaulieThePolarBear) but the solution doesn't work for all data:

=LET(Sday,B11,Eday,C11,SeaMonths,((YEAR(Eday)-YEAR(Sday))*12)+IF(DAY(Eday)<DAY(Sday)-1,-1,0)+(MONTH(Eday)-MONTH(Sday))+IF(DAY(Eday)-DAY(Sday)>29,1,0),CompDate,EDATE(Sday,SeaMonths)-1,SeaDays,Eday-CompDate,CHOOSE({1,2},SeaMonths,SeaDays))

Currently I'm using a macro to paste as values and assigned Ctrl+Shift+V to trigger it. But the downside is that I cannot undo anything once I use the macro. So any better approach to this problem? Or is there a way to enable undo after using a macro?

I put 26 to the 4th power in Excel as 26**4 and got

260000

And the formula disappeared

But when I put in 26^4, I got the right answer of

456976

and the formula showed up as 26^4

What is happening?

https://docs.google.com/spreadsheets/d/1E9khdBS1VxRKD7yx_Js3y359JJG3vQsRZWm51WW6xT4/edit?usp=sharing

I'm trying to make a sheet that shows the highscore for "linecounts" of each month. I'm only concerned with the numbers from the 6PM yellow tinted rows.

The "Highscore" sheet right now has a formula that works in sheets, but doesn't work when I try and bring it into excel:

=LET(t,FILTER(July!D3:H,July!C3:C=0.75),f,BYROW(t,LAMBDA(s,IFERROR(MATCH(MAX(t),s,0)))),{FILTER(TOCOL(July!B3:B,1),f)+MAX(f)-1,MAX(t)})

Hello all,

Every month we get these training reports that we have to filter with spec courses and then average about 15 columns worth of % on each row. Thing is, every 2 columns has a cell with info in parenthesis (ex. (45/100). It takes forever to ctrl+click each cell with % to include in average formula. I’ve played with average if but have not been able to succeed in entering conditions for desirable results.

This is a critical report but would hate to keep spending a lot of time on it.

Ive made a mockup of the table and would so appreciate guidance!

I’m having issues with formulas. Row A1 is formatted as text and has a 21 digit number in it. When I go to row 2 and use SUM to try and make cell A2 = A1 + 1 the cell just shows the formula instead of the new number. Any idea how to make this add up?

• Show formulas is turned off
• Entire column is formatted to text since they are large numbers.
• I’ve tried both SUM and ADD functions and neither work. It just shows the formula instead of the 21 digit number.

I have a fairly large spreadsheet with associated macros. It normally occupies about 300MB in memory, about 9MB on disk. After Excel's recent update to the latest current channel (Version 2408 (Build 17928.20114)), the spreadsheet immediately runs up to 4GB+ in the initial calculation process after opening and Excel crashes. Repair of the spreadsheet finds no errors. I reverted to Version 2407 (Build 17830.20166) and the spreadsheet has no issues. Curious if anyone else is experiencing a similar issue with the latest build.

Update: Testing in progress, but issue appears resolved with Version 2409 (Build 18025.20030).

https://preview.redd.it/yqy6ts9hqwjd1.png?width=153&format=png&auto=webp&s=6e61f0824d5c962ef08568a003f1e4a0746e407d

I have messing around to no avail. Was hoping someone knew of a quick way to do this.

New to Excel!

I am entering in data and was curious if there was an easier way to navigate through cells on my keyboard. For example: I enter data in A1, B1, C1, D1 and want to jump right down to A2 with a hotkey (if it exists) but can't seem to find the answer I am looking for. Anyone have professional advice rather than navigating with my arrow keys to A2?

I want a formula that picks not a static amount of entries i.e. Top 5 etc but the Top N entries that make up 80% of the Total and sums all other entries into one "Others" entry so i can create a doughnutchart from that list. Doesnt matter if its done with excel formulas, power querry or DAX in a Data Model whatever is the easiest and fastest for medium sized datasets (about 12 categories with each around 500-1000 entries).

I have dollar amount and currency in one column. But want to split the dollar amount and currency type into 2 columns but do not have a delimiter and the width are different sizes.

This is an example of my data

10.00000USD

150.0000USD

2500.00521AUD

And want the following in 2 separate columns

10.00000 USD

150.00000 USD

2500.00521 AUD

Only way I managed to do it is to use the U as a delimiter.

That returns 10.0000 in one column and SD in another column. I then replace SD with USD.
Is there an easier way to do this?

Per title. I tried to search on the internet but the results don't work. Is anyone able to provide a formula that converts american date value to uk date value?

Eg for 3rd of January 2024

Convert 1/3/2024 to 3/1/2024

Thanks!

EDIT: the actual value in the cell needs to change, not just the formatting. It is because the date value is going to be copied into a .csv file for importing into a database. Changing formatting does nothing for .csv files.

Hi,

After googling around I found a sequence formula that lists all "Mondays" between 02 dates.

Formula - =SEQUENCE(ROUNDUP((K10-J10)/7,0),1,J10+7-WEEKDAY(J10,12),7)

Here, start date is cell J10 and end date is cell K10.

My setup in excel will be as below -

setup

Weekday cell will change as per requirement.
Is there a way to include the weekday cell into the sequence formula

Your help will be GREATLY APPRECIATED.