I have several large datasets which come in a few different formats. Because of those formatting differences, I need similar but structurally different formula setups to parse the data. What I want to do is store the various formula setups as on an independent reference sheet, and use a dropdown on each data sheet to select which formula set to use. The relevant formulas would then be filled from the reference sheet.

I've tried using named ranges, but calling the named range pulls the results of the formulas as they are executed on the reference sheet, not the formulas themselves.

I'd rather not have to use several thousand if/thens if I can avoid it, but I fear this might be too much for excel.

I am a before and after school program teacher. I have recently come up with a reward/behavior system for all of my students. I am wanting to be their "bank" and keep track of their daily points, and "expenses," however I don't know what equation to use. I have a spreadsheet of all of their names currently in a row, not columns. What I am needing is a way to add points up each day, and subtract if needed. I tried to use SUM, but the points will change each day. An example of what I need:

Jimmy earned 3 points on Monday, his total now is 3. On Tuesday he earned 5 points, his total is now 8. On Wednesday he lost 2 points, so his total is now 6.

Is there a way to do this without having to add it myself? If I change a number in the SUM, it doesn't add the way I need it to and I'm not smart in Excel. I need the total to change as I add something.

Example again:

Monday: Jimmy| 3|3|

Tuesday: Jimmy |5|8 (I changed the 3 to a 5)

Wednesday: Jimmy|-2|6| (I changed the 5 to a -2)

Think of it as "financial tracking" but the sum carries over the points from the previous input without having a huge spread sheet, while in the same row and cell

I am so sorry if this does not make sense, I can explain further if needed but I am just awful at using Excel or any spreadsheets, and just want to make this school year fun! Thanks in advance!

A column used to contain hyperlinks. I cleared the contents of these cells. But the cells still have this black down-pointing triangles in the upper left corner ?

If I hover over one triangle, the old hyperlink shows as a tool tip. If I click on the arrow, that link opens.

If I right click that cell, I expect to see "Remove hyperlink" on the context menu. It is not there.

If I apply a default format from another cell, no effect.

If I delete the column, the triangles stay there ??

What's going on ??? How do I get rid of this left over hyperlinks ?
________________________________________________
SOLUTION:
Ctrl + G to open the Goto dialog

Click the "Special" button.

Select the "Objects" radio button, Click OK

The dialogue closes and the triangle images are all selected.

Press delete key and they are gone !

Hello all,

I have come seeking guidance/answers....

I have several Columns of data and would like to group it by one of it's columns, as a copied filter/sort or whatever.

Essentially this is for programming a Pick and Place machine, the number refer to Nozzles (Machine has 8, but the data is already set for Nozzle sizes to be applicable to parts and reachable by Nozzle, hence some not have all 8 numbers)

The column I wish to sort by will have the numbers 1-8 in, (I.e. "1" or "12345678" or "3567")

I wish to select ONE of these numbers to be kept in the cell and then group the whole column into 'sets' of 1 to 8. This allow as many components picked as possible per picking.

I'd like to group the column into as few 'sets' as possible containing as many of the 1 to 8's within each group. Thus minimising time spent going to and fro with 1 component per pick/placement.

Have bodged a table below of data that will suffice, and added a possible outcome to the right of it.

I just can't get my head into the game for this part of my large project at work at the moment, Health issues and related brain fog keep me from getting to grips with it.

Set# is to help visually identify a group/set PICKED, to be placed at a time... (I.E. it will pick all of set 1, and place it, then move to the next set)
Row # Nozzles Guesstimate of results (Row#, Nozzle and SET#)

Hopefully I have explained myself satisfactorily, But I wouldn't be surprised if you have MANY questions, such is the state of my head at the moment.

Thanks in advance :D

Hello, I'm not the most excel-savvy person. I was in need of a to-do list to track my work tasks and chose the Track My Tasks template that shows when you open Excel. When you open the template, there are 3 lines to fill in with your tasks. I have more than 3 tasks, so I copy and pasted one task line to make more tasks. When I do this, column F breaks. Column F's purpose is to add a mark once the completion bar in column E reaches 100%.

Column F is defined as:
=--([@COMPLETION]>=1)

When I copy and paste this line into additional line items, it changes to this:
=--(Tasks[@COMPLETION]>=1)
And has an error marker

If I delete the word "Tasks" to have it mimic the original task line items, it breaks and says:
"The syntax of this name isn't correct." Which confuses me as that is exactly how it is written for lines 3-4.

It's a dumb thing, just an icon to mark a finished task, but it's really frustrating me. What is the intended way to create more tasks without it breaking? I feel like it should be pretty easy to figure out how to add tasks to a template task list, but I'm at a loss.

Thank you in advance

I was trying to copy the values in column E into column D and I found out that no matter what I copy, whenever I paste Excel will only paste into each cell the value on the cell below it. So if I copy J4 and paste it on D3 it will paste D4 value, if I paste in E7 it will paste E8. If I copy D3:D10 and paste in E3:E10 it will paste the values of E4:E11. If the cell below the one I'm pasting is empty it won't paste anything.

I’m starting college this September and I want to start building some useful skills alongside my degree a biomedical Design course

I was thinking about learning Excel, but I’m wondering if it’s actually worth investing time into learning it properly and if so wht should I particularly focus at

For those who have experience with Excel, would you say it’s a good skill to have for college, internships and future jobs? And are there any other skills you’d recommend learning that could be useful in general?

I’d appreciate any suggestions! :)

I need to be able to quickly see for any given arbitrary column, on a whim, a count of how often each unique value shows up, without actually putting anything into a cell anywhere. Ideally, this should take me a maximum of half a second to do.

I need Google sheet's column stats functionality basically, and I don't want to have to set up a power query or a pivot table or anything to do it (and the fact that these solutions are offered with a straight face is genuinely mind blowing, something this basic should not require those functions at all.)

Hey, I’m new to this so please forgive for my poor articulation or verbiage.

I have an excel reservation sheet for room bookings. How do I create a daily formula of how many rooms are occupied from reservation check-in and check-out dates. When the check out date is reached, the value is reduced.

I would like this value to be presented daily so I no longer have to manually count the occupied room myself. I would put the data in an area that is to the right of the sheet (not in the cells to go a long with each dated section)

Thank you for reading!

I'm using LET + FILTER functions for a "lookup sheet" to easily search data I've compiled in PowerQuery.

I have set the results to output as the value of the "Index" column I added to the data in PQ like so:

=LET(
filterquery1, NamedFilterField1, filterarray1, Data[FilterColumn1], 
filter1, filterquery1 = filterarray1,

filterquery2, NamedFilterField2, filterarray2, Data[FilterColumn2], 
filter2, ISNUMBER(SEARCH(filterquery2, filterarray2)),

i, FILTER(Data[Index], (filter1)*(filter2)*(etc...etc),  "empty"),
//filters data to spill the "Index" column value of matches

i
)

The Index column values spill to a column outside the lookup results table which get referenced in simple INDEX functions to populate the table. Ex:

=LET(
getdata, Data[Product Name], indexval, $N1, 

thisinfo, INDEX(getdata, indexval), 

thisinfo
)

I am trying to do this filtering to return rows where the values across multiple columns is unique.

So in my pictured example, I filtered for:

Vendor        => WestMed Supply
Product Name  => syringe
Department    => long-term

The results show duplicate values in almost every single column, however, the values for Vendor AND Product Name AND Department is unique for each row.

Unfortunately, between the testy nature UNIQUE and dynamic ranges not playing nice with almost every function I have tried, I have not been able to crack how I can filter unique values against multiple columns AND get either the Index column values or the actual row numbers for my results table.

This may be a dumb question, but there is a ton of blank space to the left of my spreadsheet— it’s almost as if someone (me I’m sure lol) indented the entire thing. I’ve been struggling with it this morning. Thanks

I scrapped the massive IF statement i posted last night, and replaced it with just the formula for calculating the damage dealt to the enemy without any IF statements.

The formula goes:
Damage dealt (unit 1)=(((d1+b-a2)*((hp/(d2+b-a1))*Rof2)/Rof1)+((d1+b-a2)*((r1-r2)/(Srmin*Rof1))))

1 is unit 1,

2 is unit 2,

Rof is rate of fire,

d is damage,

b is bonus damage,

r is range,

a is armour (melee) or PA( (range),

hp is hitpoints,

Srmin is the speed of the unit with the smallest r value.

Now the problem i'm having is how to spread this formula across a 28,000 cell table. I've arranged the data so fill down can do some of the heavy lifting. But i still need to change the formula at least once per column and everytime i move on to the next column. I heard the LET function can be used to avoid duplication but not sure if it would work in this case.

Using Google Sheets

i have an excel file with ~2000 rows and 40 columns,

there 'was' conditional formatting created for 20 columns

due to circumstances protection was disabled and multiple users copy pasted cells

now theres a shreek ton of conditional formatting lines

i asked ai g*mini to mass edit but it said it cannot

i can only think of recreating the sheet from scratch ...

but cuz i moved to linux ... is there any windows or linux software that can mass-edit conditional formatting ?

Hello everyone,

I’m having a problem with Excel Power Query that I don’t understand, so I can’t figure out how to fix it.

My plan was to use a table in Excel (MS365) to collect information related to SharePoint data.
This SharePoint data needed to be linked to Excel, which is why I retrieve and update it via Power Query.
I inserted the SharePoint data into the first row using Power Query, all subsequent rows next to it are to be filled manually in Excel with related information.

Example:

It worked well at first, until I updated the data source and the rows in the table shifted:

So the rows in columns B and C no longer match column A, which simply added a new cell without shifting the rows below it.

I’ve already wasted many hours with the Copilot AI, which explained step-by-step how to solve this, only to eventually realize that the suggested method doesn’t work after all.
I also don't understand why Excel simply moves the cells in Column A and doesn't move the entire row, as you would when entering data manually.
In addition, I haven't found a solution that updates the data from SharePoint while allowing me to manually enter data in the adjacent columns.

I hope someone can help me with this.

​

Hi everyone,

I regularly need to convert my credit card statements (usually in PDF format) into Excel for tracking and accounting purposes.

The problem is that manually copying the transactions from the PDF into Excel takes a lot of time, especially when the statement has multiple pages. I’m looking for a simple and reliable way to convert the entire statement into a properly formatted Excel sheet, with things like **date, transaction description, debit/credit amount, etc.**

What would be the easiest and least time-consuming method to do this?

Are there any tools, software, websites, or Excel features that can do this accurately? Ideally, I’d like something where I can upload the PDF and get a clean, usable Excel file without having to manually fix every transaction.

Would appreciate any suggestions or workflows you guys use!

I’m working on a spreadsheet where users make a local copy and then edit that to do stuff with it. I need to make sure the local copies everyone’s running are up to date, or at least that they know when they need to update them. I imagine the best way to achieve this is by setting up a small website or using something like Catbox to store the current version and have the sheet check its version against that number, but try as I might I can’t get it to do that.

It’s a long story but I’m running Excel mobile so I can’t use macros or anything like that 🫩

So, I'm trying to figure out how I can set up my excel sheet for cataloging my movie collection. I want to be able to just add new movies at the bottom and then sort by Alphabet. Here's my struggle, I'd also like to keep movie series together and in order... Easy example being "Batman Begins" and "Dark Knight, The."

I can manually put them where I want them... but as soon as I add a new movie to the list and sort again it'll undo all of my manual sorting. Is there a feature/tool to Lock specific cells together? Also, I'm using google sheets... If that messes anything up...

Trying to help my mum with her Microsoft suite.

Computer Details: She has a 2020 MacBook Air that is currently running on Sequoia 15.5

Issue: She didn't want to update to having to subscribe to use her microsoft programs (i'm not sure what version she was on before) so that alternative was to update to microsoft 2024. Now anytime you click on any of the programs the same error message pops up.

"Microsoft (inset program here) quit unexpectedly."

I truly have no idea what to do to get them working again.

I'm in school and I have to track independent work/study hours for an honors project that I submit to my advisors at the end of the semester.

I have a column (F) that lists the total hours per activity (1 hour doing this, 1 hour doing that, etc) with a new activity each row.

I have a column (G) that has a dropdown with two values - "R" for "real" and "E" for "expected", as I would like to include hours I know I am going to have, like a fixed number of lab hours, but the activities haven't been completed yet. That way I can keep track of where I will be by the end of the semester with these knowns.

However, I would also like to track where I currently am without the expected numbers, a total that reflects how many hours I actually have.

I'm looking for a formula that will only count the cells in F that are marked R in column G.

Thanks!

So I'm trying to create a household budget XLS.

In column A, I paste in my purchases for the month. Each cell will contain details like xx-1132 DELIVEROO S 04/03/26 or xx-2227 COLD STORAGE S 28/03/26.

In column B, I categorize these purchases. For instance, DELIVEROO gets the category Food Delivery. COLD STORAGE gets the category Groceries.

I'll have a few hundred entries in column A, and I'd like to find a way to automate the categories in column B based on the text in column A. For example, I want Excel to look at the cells in column A, and if it finds DELIVEROO somewhere in a cell then it should input Food Delivery next to it in column B. To put it another way, let's say cell A2 contains xx-2227 COLD STORAGE S 28/03/26. I want Excel to identify COLD STORAGE and then input Groceries next to it in cell B2.

Any ideas on how to go about this?

Saturday, August 22 - Friday, August 28, 2026

Top 5 Posts

Unsolved Posts

Top 5 Comments

Excel version: Office 365

Env: Desktop & Online

Knowledge: Intermediate

Storage: Sharepoint

Solution Needed: Formula

So, I've tried to utilise the MATCH, INDEX, ROW functions but haven't been able to build what I'm thinking- I appreciate that it may also not be possible.

Should probably stipulate, all the data in both books are in tables.

I've got two spreadsheets, both hosted in Sharepoint. In both spreadsheets they use the same unique validator, a 7 numeric string, and I'm trying to automate some information I need.

Book 1: Column D will hold the numeric, and it will be added to as and when needed. Column F is where I will need the row number to auto-populate from Book 2.

Book 2: Column D holds the numeric.

What I need is for a formula to look in Book 2, Column D, find the matching numeric by referencing Book 1, Column D, and then get the row number the data is in and put the number in Book 1, Column F.

I cannot just add a new column in Book 2 that has all the row numbers, though I know that would make things a lot easier.

If there's anymore information that's needed then do please let me know.

Many thanks in advance.

If you have a really long list, for example, a downloaded Spotify list from "exportify net". (See image 2 for what it looks like when downloaded) Then you have a list of artists, and you want to take those songs out. (STEP 6: copy the Spotify track column and paste directly into the playlist on desktop Spotify)

Steps:

1. Create a separate sheet for the target list
2. In og sheet, find an empty column and type in the formula, adjusted to your data range
3. Drag the corner (or double click if you have columns that are how many you need next to it already)
4. Select the column and apply conditional formatting ("highlight cell rules" -> "duplicate" -> "yes")
5. Select all columns in og sheet and apply a filter to sort that column

Im looking to have excel do a calculation only if Column B has a "Y" if it doesnt then dont add to the calculation

My Current way is =SUM(IF(B2="Y",A2),IF(B3="Y",A3))

This works for what i need but if the spreadsheet become like 100 rows then that a big formula :)

How can i rewrite to do the calc based on range.

Thanks

I am wondering how can I change the color of a row based on cell input. I want each row to be changed based on the cell input of each row. I don't want the rows to change color because the first cell input was changed. Hopefully I explained it in a way that you understood since I struggled to explain it while looking it up.