r/excel

...and WHY?

Do you use different fonts for different use cases?

What are youe go-to's?

So this is a doozy. I have about 60 word files on a SharePoint site. Each of the word files is a table, similarly formatted, with similar columns. My goal is to get the data within the columns into an excel file.

The tables are formatted in such a way that when you paste into excel, sometimes it is pasted into multiple rows when it’s not immediately clear that would be the case in the word file.

Ultimately the last column is something like languages and lists all the different languages related to that row.
What I want to happen is split that out into excel so each language is on its own row.

Is there any better way than manually going through all the files, pasting, taking the overflow into the next rows down into the cell above (essentially unmerging the cells), and pulling out each language into its own row?

I’ve tried a =TRIM( and a =TRANSPOSE(TEXTJOIN( but it is still cumbersome and doesn’t work all the time. Plus, ideally could assign a chunk of files out to other people and they would not have the same proficiency with formulas as me.

Any help or advice is appreciated! Thanks!!

I've been investigating why some Excel workbooks become painfully slow even when they don't contain that much actual data.

A few things I've found surprisingly easy to overlook:

• Whole-column references like A:A can make formulas process far more cells than the workbook actually needs.
• Volatile functions such as OFFSET and INDIRECT can trigger additional recalculation.
• A workbook can have a huge used range even when the visible data occupies only a tiny portion of it.
• Broken references can be hiding in places you don't immediately notice.
• External links, add-ins, and macros can introduce another layer of slowdown.

The phantom used-range problem surprised me the most. A sheet can look like it contains only a couple of cells while Excel's tracked range extends thousands of rows and columns beyond the actual content.

I'm curious what other people have encountered:

What's the strangest or least obvious thing you've found that was making an Excel workbook slow?

I'm especially interested in cases where the workbook looked completely normal but had some hidden performance problem.

I'm trying to turn a google sheet in to an excel sheet and one of the functions the sheet uses is stumping me.

=sort(unique(trim(tocol(iferror(split(Log!J33:J,",")),1))))

This is the function that is used in the sheet to sort a column where each cell contains multiple things separated by a comma. It places each item separated by a comma into it's own cell and sorts them alphabetically. It ignores any spaces there might be and simply grabs the text between the commas.

I've tried simply looking it up to see if someone else had asked in the past but I would not know how to explain exactly what I'm trying to do. I barely use excel and seem to have 0 ability to understand how more complex functions like this even work.

I'm using office 2024

I am currently trying to create a formula that will take the names underneath a specific column in Sheet 1 and produce results in another tab of how many times that job appeared and match it to its corresponding name on Sheet 2. Does anyone know how I could do this? Thank you.

I play a lot of Magic: The Gathering. I have a spreadsheet that tracks when I play each of my decks and the outcome of the game. The spreadsheet has a section for each deck, and then a column of dates for when I played those decks.

I'm trying to find a way to take those columns of dates and generate a list of, for example, the top-ten most recent decks I played. I don't know how to get Excel to do that, because it would need to look at all of the date columns and find the top-ten most recent dates and the deck they correspond to. I don't even know how to begin to do that.

Any thoughts or suggestions?

Thanks.

Hi there,

I got tasked with scheduling a meeting for 10+ people and it's hard to track everyone's schedule. Is there any easy template in excel where you can track both dates and times for the individual attendees? When i did a search, i found a lot of templates for date scheduling which seems easy enough but the times of everyone's schedule have to overlap for a quorum. Any help is appreciated. Thanks!

As of 14 September, the COPILOT function will no longer be available in Excel. See https://support.microsoft.com/en-us/excel/functions/copilot-function Note that the Copilot feature remains - it is the worksheet function that will stop working.

This is a very uncommon move by Microsoft, given their history of maintaining backwards compatibility.

Have you used the COPILOT function? If so, then what for? Thoughts?

Until yesterday, there was an online catalog of all Excel functions at:

https://support.microsoft.com/en-us/excel/excel-functions-alphabetical

Today, visiting that page yields a 404 (Not found) error.

What gives?

I have a spreadsheet for my budget that calculates the bills I need to pay each week and I can't figure out how to set up an equation to automatically pull the amount from a list of bills based on what day they are due. Here is how I have it set up. In column "A" I have the date of every Friday (payday) set up as mm/dd/yyyy. The date column covers the entire year. Then in column "B" I have the total of the bills to be paid that week (this is where I want the equation). I have a few other columns that pull from column B but those aren't necessary for this explanation. Over to the right in starting in column "N" I have the day of the month that the bill is due, (e.g. 1st, 5th, 23rd) listed as a single number (1,5,23), in column "O" I have the amount of the bill and in the next column it says what the bill is. How do I set up an equation to pull the amount of the bill if the day falls between Friday of the current week and the day before the following Friday? I have tried using sumif but I can't get it to work.

Ive used the error fix button often due to a few excel files I get each week being saved as text instead of a number. For some reason, the button changed to AI where it takes around 10-15 seconds to fix something that used to take 2. Is there a way to remove this feature and get the old error button back? Other people in the office still have the original button and im not sure why I dont anymore. Or is there a way to change the cells manually, because it wouldn't let me do that either.

Hi,
I work with a ... 'technologically challenged' group that would like a very simple data collection form where you just input someone's name , age, address etc and it will put it into a table or database. I would just use google forms but they require it to be offline, and with excel I could do it simply with some cell input linking to a table on another tab but ideally I would like to give it a bit more of a UI , maybe with macros, so they have a 'submit' button and it's harder to mess up input. Due to GDPR it would also need to be secure somehow too.

They had someone build them a database years ago using microsoft access but it's kind of outdated now and janky and we would all love to get them off that system.

Is there a way to do this in excel? Because there's no way they could handle a SQL database, but they basically only need a single table for the data because it's just for checking if someone has contacted them previously.
TIA!

What could cause a freshly downloaded Excel from App Store not let me edit anything? I create fresh workbooks but no matter what I type it doesn't write. The files are not locked or read-only. I never experienced this before - albeit I only used Excel on windows.

I am trying to have Microsoft Power Automate to refresh a report that exists in Salesforce. That report is linked to an excel file via a powerquery.

I have created a script on OfficeScripts that have workbook.refreshAllDataConnections(). It does update a pivot table, so I can tell that the script is runnint

The table that PQ puts the data in is set to not do Background Refresh and to refresh when opening the file.

 

What is the best way to have PA to refresh Excel’s PQ?

Table properties on

https://preview.redd.it/73bhc3gob7kh1.png?width=461&format=png&auto=webp&s=d9b9c3fe3e4f39fd0daeca18781a42204453a704

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:

Items	Cost
Bus	15
Hotel	30
Margin	5
Total	50

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.

Items	Cost
Bus	20
Hotel	30
Total	50

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

Any tips?

I always try to make sure I select A1 when closing spreadsheets. This is especially true if it's a document being submitted to someone else, or a client.

And when I open a spreadsheet on A1, I get this weird "Virtual Fist Bump" feeling.

Anyone else?

Please help me. I have 2 columns with names from 2 different data sources and I’m trying to reconcile them.

Ones “first last” and the other is “Last, First”

The problem is that the data is not uniform and I have no control over it. There are a bunch of duplicates, misspellings, etc. One column might say “M&S” and the other “Projected M&S”

How do I use xlookup/indexmatch to reconcile these? I tried making a concatenation key but again the data is not uniform.

Is there another function or workaround I’m not thinking about?

This may be a simple answer, but I'm trying to make a contact sheet that only displays the contacts based on the city selected in a drop-down menu. I have 13 cities listed, and 61 contacts between them, each name would need to be in its own cell. Am I overthinking this?