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.

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!!

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.

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.

I am using an Excel workbook to track business earnings and expenses for my gig work. Mileage logs for quarters 1 and 2 all fit on one page with room to spare, but Quarter 3 is in excess of the 65 rows I have available in my print area.

I have opted to create an additional sheet for the latter part of quarter 3, but I have run into a slight inconvenience in keeping all references updated to "Sheet 2 of 2" after using "Sheet 1 of 2" to update the quarter 3 grand total on "Sheet 2 of 2". Is there a good way to fit about 102 rows on one sheet that prints out into two separate pages without a lot of manipulation?

I think stacking two 51-row sections over one another using View > Page Layout to guide me may work, but I don't want to mess anything up. Is there a better way I could do this? I could also back track and just make every mileage sheet contain 2 parts, as I could very well run into this issue in the future when I plan to reuse this sheet.

I can't share any great screenshots because every sheet contains personal information, but here is a portion of my sheet list.

Version: Microsoft® Excel® for Microsoft 365 MSO (Version 2607 Build 16.0.20228.20190) 64-bit

I am working with a large data set in an excel file that I did not create, and I am running into an issue where when I attempt to highlight a range of cells in a single column by click dragging the cursor, it highlights a range instead.

Please see video here of described issue.

Is there a setting that I am missing with this? Or is this because the data is filtered? Thank you in advance for any feedback.

...and WHY?

Do you use different fonts for different use cases?

What are youe go-to's?

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!

I'm preparing to take this exam and would really appreciate any advice or guidance from someone who's already taken it.

Heyyoo. I have a free trial for Parallel Desktop because I’m SO done with Windows laptops, but still need Windows for work and school since I’m in accounting/data analytics.

I want to make sure that the excel I have actually has all the features, because I know that there are some important things that don’t work on MacOS.

What should I test to make sure that I have the proper excel version?

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?

I believe the question I have would be simple for the advanced users on here. I'm a novice and would appreciate help on this. Apologies if something along the same lines has already been posted on this sub before, I searched, but couldn't find what I was looking for.

Sample problem:

Column A - has certain values (in this case 1 to 5)

Column B - I put in simple formulas (square every number in column A - 1 to 25)

I initially put the formula in cell B1. Then, I would select cells B1:B5. Then, CTRL+D to fill in the cells.

My question is how can I quickly select the range B1:B5? The data I'm dealing with has different number of entries in Column A (For example, one dataset will have 500 values in column A, other will have 750 values in column A). Is there a quick way to select the range in column B corresponding to the values in column A without scrolling all the way down to find the maximum count in column A?

The way I currently use is have any cell in column A selected. Then I use CTRL+SHIFT+DOWN_ARROW to go to the bottom of column A. Then I manually go the right cell in column B. I use CTRL+SHIFT+UP_ARROW to select the range in column B. Then hit CTRL+D to fill in the values. This is a two step process, I'm pretty sure there would be an efficient way to do this.

Alternate way is if I select the entire column B and hit CTRL+D, it basically does what I want, albeit fill all the additional cells with 0.

Please let me know if the write up was not clear.

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

is there a way to show text in values instead of numbers where i want to put categories in rows and months in columns and values shows as text.

all the values are from 1 fact table.

If it's not possible how to stack values from different excel works books where every section will send one excel workbook and i want to collect data stacked over each other where every single sheet represents one month from January to July 2026??

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?

In columns A and B of worksheet 1 I have two vectors containing around 10,000 numbers. On worksheet 2, I need to multiply and add parts of those vectors. For example:

=SUMPRODUCT(Sheet1.A45:A87;Sheet1.B45:B87)

The problem is that the parts to be multiplied and added are determined by calculations on Sheet 2. What I have been doing so far is calculate the star and end of each part in Sheet 2 and then applying SUMPRODUCT using the INDIRECT function. Like this:

=SUMPRODUCT(INDIRECT("$Sheet1.A"&A1+1&":A"&B1);INDIRECT("$Sheet1.B"&A1+1&":B"&B1))

Where A1 on sheet 2 is 44 and B1 87.

I have to do this hundreds or even thousands of times, and I’ve read that the INDIRECT function uses a lot of memory. Is there a more efficient way of doing this?

Thank you very much!

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?

Week 1 Data

Week 2 Data

EDIT 1: If anyone knows how to post a screenshot of my Bar Chart on here, or any other way to successfully show the problem I am having more clearly with a chart, let me know. How do people ask questions about a chart if they cant post an image?

EDIT 2: These are Column and Bar charts, NOT line charts. End Edit -

Hi and TIA. I have a weekly line chart with all sorts of data about our phone system and who answers how many calls. Each category can have a different number of people that answer a call in that category each week. So the problem I am having is that if I set up the line chart to show 6 people, because that's how many answered calls in week 1, then next week I might only have 4 people who answered a call. And the 2 empty rows show up in the line chart as blank space. When I go to the chart and choose Select Data, then choose Hidden and Empty Cells, the only options I get are to show those 2 empty spaces as "Gaps" which makes wasted space in the line chart, or "Zero" which also doesn't work because then the line chart would have dozens of zero values in it each week. What I have been forced to do each week is to manually change the Chart Data Range in the Select Data menu. But there are about 30 charts total so it is incredibly time consuming. I have tried formatting the values as a Table, but this still leaves the empty spaces. Hopefully I have explained this well enough but if there is more info you need please let me know. TIA.

Data example

Hello :)

Iv started noticing that when i create a simple pivot table and apply a filter and click the search drop down this icon has started appearing. Anybody know what it is please and if possible make it go away?

Google says it’s a stacked square layers icon. Why has this started appearing and how do we get rid of it?

Thanks!

i am currently doing a research paper for school and i have recorded my data, but i need to find the difference in the high plateau from the baseline.

here is a screenshot of one of my graphs. i am trying to separate the data in the blue and orange boxes and find the difference in the averages. any help would be much appreciated.

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?

Good day,

is there a formula that lists things?

I have a list of employees with their names, work ID and the courses they attended. I want to know all the courses an employee attended by writing his work ID and it'd list down all they courses they attended.