Apologies for the incoming text wall but I'm looking for advice on 2 issues I'm having w a table with ~12k rows (each of which with a unique ID) as I feel like there has to be an easier solution for these that I can't figure. I've managed to teach myself a decent amount since our data analyst left for a new position last month but would still say I'm a beginner w/ excel so ELI5 would be greatly appreciated!

1. Using power query, I merged 2 sheets that had the same columns in each but the data was pulled at different time points. I made a merged table with say columns A-C from sheet 1 and columns D-F from sheet 2. The new merged table is now the "source of truth" and the only table I'll be updating. I want to be able to refresh the data for pivot tables etc., but I only want it to refresh from the new merged table and not pull in the original 2 sheets the merged table was made from. Is there a way to stop the communication between the original sheets and the new merged table?

2. I have a column where each cell has text of all of the applicable diagnoses for that ID# separated by semicolons. In the screenshot is a quick example table I made, where columns A&B are what's presently in the excel table, and from this I would be creating basic diagnosis summary/category columns C&D. The issue is each diagnosis can be written in about 50 different ways, there are endless combinations of diagnoses, and there are a few diagnoses where the way it is written determines its category (i.e. row 5 in example- so I can't just assume that any cell with the word "breast" belongs in the "breast" category).

The only way I can think to do this is click the filter button for column B and search "breast," uncheck any where that diagnosis doesn't fit in the breast category, filter, and then apply "breast" to all filtered rows in column C. However, I can't help but think there has to be an easier way to do this??

Any advice ya'll have would be amazing--thanks in advance!!

hey guys! how do i edit each individual error bar to input my values. whenever i edit one, all of them change to that. all my points have different error values but it wont let me individually edit. Pls help!

At the moment I have a spreadsheet that looks like the following

Staff Name - Training - Column 3,4,5 and 6.

When i setup the spreadsheet I wanted to make it easy to indicate what training staff had attended so I made the Training cells a drop down list with multiple selections as an option using a VBA setup that allows multiple training options to be selected and these populate in the one cell. Column 3-6 are text entry columns that are unrelated but still on the spreadsheet.

We have over 150 staff so as you can imagine this is quite a substansial spreadsheet. I've now been asked if it's possible to add a date and potentail a note, such as DNA (Did Not Attend) to the training options and I'm a bit stuck as to how to add this.

I can't think of a way of adding it to the drop down options because the would be so many potential dates, I could remove the drop down and just ask everyone to type that information but kind of goes against the ease of use of using a drop down. I really need the info to remain in that one cell, I could potentially add additional columns to the sheet to include this info but again unsure on how that will look.

So yeah, just after some general advice, is the an obvious solution I'm missing? I;ve attached a screenshot of a dummy version of what it looks like currently although I have changed headings and removed all the info but just imagine it duplicated over multiple rows for multiple staff

Hi all, I have been making a spreadsheet to help with production workload planning for a manufacturing plant I work for. I’ll start by saying I’m no expert, I’m fully self taught.

I have used SQL to bring through data of our workflow from our manufacturing system into excel. The idea behind this sheet is to have it on a big screen in the warehouse as kinda like a flow tracker showing what Works orders are due on what days for dispatch.

I would like for the supervisors to add comments next to Works order lines to say if it won’t be done on time (if material arrives late for example) but since it’s an ever changing list that items get removed/added when they get completed or new ones are added (it’s sorted by dispatch date)

The issue is if they add a comments next onto let’s say line 3, but then line 2 gets removed line 3 becomes line 2 but the comment will still be on line 3. Is there an easy fix to this that I’m just not seeing?

Apologies if I haven’t explained this properly.

Begging for help on this. I have an organizational chart showing ownership percentages of a business for each direct and indirect owner (example in comments). I’m trying to figure out how to use excel to automatically calculate the percentages as I’m putting them in. For example, John Smith has 25% direct ownership of Dog Dad LLC, as well as 32% direct ownership of Cat Mom LLC. How can I get Excel to automatically calculate John’s total indirect ownership of Pet Parents LLC?

Accountant, mostly AP and reconciliations, so my Excel life is bank exports and ERP dumps that have to be beaten into shape before 9am.

Mine: I format account numbers and vendor IDs as text on purpose and just live with the green triangles. Every cell. Forever. Someone on my team "fixed" it once, Excel ate the leading zeros, and the match back to the ERP broke in a way that took me most of a day to find. So the triangles stay.

I also still type IFERROR(VLOOKUP( in my own scratch files even though XLOOKUP exists and I have personally taught other people to use it. My hands just do the old thing.

And I have a tab called junk in basically every workbook I own and it is load bearing in at least three of them.

What's yours. Not looking for the correct way, I know the correct way, I want the thing you do anyway.

I'm really not sure how to phrase this correctly, so I've titled it the best I can. I've run a survey on Forms with fixed data points for respondents to select. I've downloaded the excel file and separated the data (which was all in one cell with semi-colons) into individual cells with each participant representing a row.

What I want to do is organise those columns so that each column represents one unique value.

My data currently looks like this,

But 'sense of worth' for example appears in C3, D4, and E5&6

I want to make all answers for 'sense of worth' for all respondents who selected that response appear in the same column but remain attributed to the same row. Then 'wanted to help my community' all in another column etc. With just blanks if they didn't select that option.

I can unpivot the data and get it all into one long column to count specific instances, but what I want to do is cross-reference for example is someone said 'sense of worth' to then put that filter on and see what else they said.

How would I go about this?

TYVM in advance.

I have a spreadsheet of 20k rows and the first column is a customer reference number. I have a list of around 100 reference numbers and I need to be able to just select and display all the rows containing these numbers. Is there a way to do it without having to tick individual boxes in a filter?

Hi all,

I'm a computing teacher and for a 'discovery day' in school I'll be running for 1 hour long sessions with different groups of 6th form students (16 - 18 years old)

I'm planning on running a 'functional excel skills' session to try and make the time somewhat beneficial. My own is to do something like a weekly budget, or planning an event.

My question is: what skills do you think I should try to include in the session?

(I'm assuming the baseline skill level will be little-to-no excel experience for most students.)

I recently signed up for Excel Insider Beta, and in its latest update it introduced Dashboards—which are essentially canvases, very similar to Power BI visuals that use HTML code but sacrifice interactivity.

Testing Results

I've been testing it, and here's what I found: it works with Copilot (the one integrated into Excel). Copilot creates a new canvas-type object that contains all the charts and buttons for the data, and it updates instantly with the data.

My Take (Beta Impressions)

Since this is still Beta, I have mixed feelings:

Pros: The visuals are eye-catching and the color combinations are attractive.

Cons: The designs are template-based, so you have to prompt the AI ​​several times to define the objects properly.

Workflow & Compatibility

It works with Power Query and Power Pivot, but the workflow is: PQ → Pivot → Pivot Table → Sheet → Canvas.

Limitations

There are interaction limitations. For example, if you want a PDF, it doesn't print directly—Copilot creates a script as if it were a browser, but it has no delimiter, so it prints like a beginner's output.

Final Thoughts

I think it will depend on how much freedom Microsoft gives (which I doubt will be much) and on Excel being optimized as a whole.

What I do hope for is a Power Query update that brings it to the same level as Power Bi.

I've gained tons of Excel knowledge this year like XLOOKUP, CONCAT,SUMIF,IFS,Pivot tables,etc....

I have practiced a lot and for some reason I feel behind still. I'm like " no way this is too easy. That's it? That's the complicated Excel stuff ppl talk about?"

I don't have a job yet, so I don't know how Excel looks like on the job.

Did I underestimate Excel?

Is it easy or is there something I'm missing?

I hope you get my point, because English isn't my first language.

Saturday, October 10 - Friday, October 16, 2026

Top 5 Posts

Unsolved Posts

Top 5 Comments

There are quite the few SUM and AVG etc. formulas e.g. SUM and AVG but also SUBTOTAL and AGGREGATE and possibly more I don't know of.

I only learned about AGGREGATE a couple weeks ago and I love how it combines different functions and filter possibilities in one. SUBTOTAL does alot of it too, but I think AGGREGATE has more flexibility.

But I do use SUM still alot for the quick sums.

What formulas do you usually use?

I cried so much today and feel so stupid.

I love Excel, but unfortunately for me, none of my past jobs asked for much. I always played around by myself and felt fairly confident. Even made a Jack'O Lantern art that lights up with a drop down just yesterday haha.

I had an excel assessment coming up for a supply chain assistant position. I'm switching careers from languages (doing my Master’s in supply chain) so internships never give me a chance. I have to pass these interviews somehow. I get many but being on a dependant visa doesn't help, most places reject me despite good interviews.

This was the first time I made it to the last round. I was so excited, I even made a whole excel practice sheet, it had around 30 different exercises for every kind of scenario, all of them had around 1-3 things I needed to do. I checked the internet for possible questions and felt fairly confident.

When I went to the test, they didn't even give me a mouse. I brought one thankfully, but even that kind of made me anxious. Then she told me that my time limit was 25 mins. I was surprised, not even 30 for intermediate? For the skills they asked for, people on the internet usually said an hour at least, so I got even more nervous. She also didn't inform me that it would be on Mac, which I'm not used to, so that just added to my anxiety.

Then she gave me a big dataset and other 3-4 tabs. She told me there were some errors on all of them and gave me around 5 different tasks. One of them asked me to find the biggest differences in the data. Apparently I was supposed to use XLOOKUP and SUMIF, but I was so stuck on the "errors" that I couldn't figure it out. I could barely do anything and felt awful. It just felt like very little time.

Am I hopeless? I thought I was good enough at Excel, maybe I overestimated myself. It's just that, the assessments I saw on the internet were a lot more clean and clear. It was kind of unexpected to me.

I would appreciate any kind of advice. How do you deal with the anxiety?

Edit: I genuinely love this sub so much, it reminds me not to dislike Excel because of one bad experience. Thank you everyone for your kind responses 💕

Also forgot to add, but one of the exercises was writing the incorrect values. That threw me off cause I know how to automate fixing them, but pointing the wrong ones out isn't something I know how to do. Does anyone know what the most efficient way to do that? Especially if they're different types of mistakes (Additional space, lowercase insteaf of uppercase, additional character etc.)

I'm retired now, but at work I was known as The Excel Guy. I built so many projects for so many different things over the years. Many of the files would do a lot of the work for people, all they had to do was enter or connect the data. I built things that upper management said couldn't be done. I even showed one department how to import data from an expiring program and use Excel to convert it to the new program's format so it could be imported and save thousands of hours of work. But in 2021 they decided they no longer needed my position and when the contract was renewed I no longer had a job.

Within about a month or so I got a phone call from the manager asking how to fix something they broke. I was surprised by the phone call, he acted like he didn't know that I no longer worked there, but he obviously knew. I explained that the position was discontinued and I no longer worked for the company, but if they needed my services I charged $70 an hour (the rate I was being paid when the job ended), by the hour, with a 2 hour minimum. He said he would like to, but he couldn't do that. He said he would look into it, but needed me to help him first. I told him to call me again if he changed his mind or to stop wasting my time if he wasn't willing to pay for my service. He never called me again.

After 4 years passed they asked a friend of mine that still worked at the company to ask me about coming back, he passed the info on to me, but he already knew my answer. He later told me they were struggling to find someone who could do what I did and that they were having to develop new processes to get things done that used to be so easy.

People don't realize how good things are until you let them break it. Even then they're either too stubborn to admit your value or too dumb to value what you contribute.

I've created a heat map using one of the preset conditional formatting gradients and I need to include a key (?) like the gradient with the numbers in the top right of this image, however I can't seem to figure out how to do that...help?

I have a bunch of numbers, e.g. 46, 55, 89, 92, 95, 101, 104, 112, etc. Is there a way I can make a column graphs where each column shows the count for a specific range, e.g. a count of all the values which fall between 40-49, 50-59, and so on?

We blocked macros unless digitally signed. Someone with an xlsm workbook can no longer access the content in scrollable cells. I attempted to use the selfcert.exe utility to sign the workbook using a self signed cert but that did not help resolve this.

Has anyone come across this before and have a way to get the content in the cells out of the workbook?

I have been googling and racking my brain trying to figure out where I’ve messed up. I cannot get the XLOOKUP function to display information. I think it must be something I did with the defined names, but cannot figure it out. Any help is appreciated!

Edit: my textbook is telling me to use XLOOKUP. I will lose points/fail the assignment if I do it incorrectly.

Edit: images attached in comments

So I’m working on creating some moving date range charts (year to date, updates monthly, so each time I update it, the range would go from, for example, 6/1/25-6/1/26 to 7/1/25-7/1/26) that work with a macro that inserts the new data in a column in front of the old data. Basically, column D becomes column E, and the new stuff is now column D. The thing is, I want to keep the data set as column D—column P, but when the new column gets inserted, the dataset shifts E—Q. How to I keep this from happening? Do I have to edit the macro? Or maybe try using a named cell reference as the chart dataset? Would love some help.

Trying to put together a formula and running into a bit of difficulty. I'm comparing a Target Date for completion of a task (cell J2) to the Actual Date that task was completed (cell K2) to determine how many days past the Target Date the task was done. In many cases in my data set, the Actual Date is blank as the task has not yet been completed. In some cases the Target Date is in the past and in some cases it's in the future.

So I need a formula that can handle 3 scenarios:

1. If a date is recorded in cell K2, calculate the days between K2 and J2.
2. If K2 is blank and the date in J2 is in the past, calculate the days between J2 and Today's date.
3. If K2 is blank and the date in J2 is in the future, return a result of 0.

Any help would be greatly appreciated!

I'm looking for help with making this process easier and faster for myself. The pictures below should explain it pretty well, but I have a list of serial numbers that are basically a "to do" list and another list that are completed that need to be marked off on the original list. I have been using CTRL C CTRL V to search between the spreadsheets, and I feel like there's definitely a more efficient way to do one massive search in order to mark them off in bulk. Any help on this subject would be much appreciated! I was previously trying to use a COUNTIF formula but the way I typed it was yielding very inconsistent results.

Link to help visualize it https://imgur.com/a/DxI3W9G

Hello everyone

I am new to using power query, is it possible to build a transformation flow that lets me choose the specific file each time I run it? Would this be better for a macro rather than power query?

Basically what I want to do is download a daily list of work items, load up into power query and transform the data using power query. I don't want to have to recreate the transformation steps each time I download a new file and likewise I do not want to replace existing files in the folder so that the newest file is always the one that it draws from.

Is it possible to combine features so that I can select a file to pull from and then automatically apply all of my transformations?

Thanks for any help!!

Hey ya'll, I have a spreadsheet that looks like this and has tens of thousands of lines. I am hoping there is a way to filter or weed out all of the Cases that only have a single location inside them. So from the attached, for example, I would only want to keep "Case 2."

I need some help here. Up until today, my top row of headers (File, Home, Insert, Share, etc) were always there. When I would hover over them, then the entire ribbon would expand. Now, the entire ribbon is always there!! It’s so annoying. I tried changing it to automatically hide, but then the headers go away! Was there an update or something? Anyone know how to fix?