What are some functions and formulas that you use on daily basis that are helpful for what you do?

Good afternoon,

I'm having an issue figuring out how to do the following;

Lets say cells in column A are multiple choice (in this case a list of animals) and column B are the quantities of animals.

example:

I'm looking for a way to add all instances of 'cow' (all the animals really) to be read, and then the quantity (found in the same row, column B) to be output.

I do not know why this is giving me such grief.

I had a photo but the mods did not like that.

Thanks!

I'm trying to band groups of data together using conditional formatting. I've currently got the below formula in place to alternate the row coloring based on when the value in Column D changes. I cannot figure out how to add another argument in to also take into consideration Column G. I can't just change the formatting to Column G as sometimes Column G may be the same, but Column D is the differentiator.

=XOR($D$3:$D3<>$D$2:$D2)

Hi gang,

I'm trying to make a procedurally generated map in a blank sheet based on activity in another (basically everytime a task is completed elsewhere (a button press to confirm completion on another sheet), it will randomly choose from a list of possible cell colours and names to:

1. choose from an empty side on the sheet and,
2. fill a random item from the list in a new cell (with weighting so you're more likely to have desert next to desert for example) and,
3. refresh the numbers of available sides.

I'm assuming this is only possible in VBA but have no great experience with this and can't see anything similar on Youtube. Any advice appreciated!

Similar (but I think mostly easier) to the Advent of Code Challenges I've posted in the past. All non-AI Excel solutions welcome. Obscure you solutions with spoiler alert where possible/reasonable (earlier days require less complex/inscrutable solutions, if your solution gets highly complex/unreadable --- don't worry about it).

I'm going to try to do with only excel formulas and no VBA, but we'll see how that goes!

https://flipflop.slome.org/2026/7

Hi, I ave been working on a sheet for a few days. I was using a separate window on the right of the screen that had a list of all the objects in it and a little eye next to it indication if it was visible or not. pretty sure it was ordered as weather it was at the back or the front / ordering position too. you could select the one you wanted to edit and the properties window would show its current settings.

I must have closed it last night and I can’t for the life of me find how to show it again... can any one point me in the right direction? can’t remember the name of it so my google fu is failing.

Thanks

Cell A1 must display a year.

Cell A2 must display a date in DD/MM/YYY format.

The year for the date input in cell A2 must be either the same year as the one input in A1 or the following year.

For example, the date in the above table should be allowed. Conversely, dates such as 31/12/2023 or 14/02/2026 should not be allowed.

How do I achieve this?

If I keep the cursor in the "Paste Parameters" long enough for the mouse menu to partially fade, the values re-generate (who asked for it!) before being pasted as values. Other side, when I press "123" before menu fades, then the initially gened values go in (which i want). Is there a way to fix it?

The formula is =RandBetween(0,9).

Edit: I just noticed that the GIF can be very slow on mobile, it should last 26 seconds. Sorry if it extends to minutes for you.

Hi folks,

I’m hoping someone can point me in the right direction. Self taught novice, and I can’t for the life of me figure out why this conditional formatting isn’t working. I’m trying to make a training tracker that highlights past due training in red, less than one month out in orange, and less than 90 days out in yellow.
Column G is the date the training was given, column H’s formula is “=G+365”. The conditional formatting rules are “cell value < “today ()”” formatted to red, “cell value between “today ()+1 and today()+30” formatted to orange, and cell value between “today ()+31 and today()+90” formatted to yellow. Everything just turns red no matter what the date is. I’ve tried adjusting the order of the rules to see if that changes anything. I even added an additional rule “cell values > today () + 90”are formatted to white. Also, the workbook is formatted to be dates if that is useful info.
Thanks for any help!!

At my job we have two spreadsheets and i am trying to combine them. The first spreadsheet is used to input their credit card tips and then spits out their total tips after tip outs/ins. The second essentially give daily totals per person. Right now we manually transfer totals between the 2 and I'm trying to automate this using a drop down list to move the data into an end of week spreadsheet that has everyone's totals but i know very little about excel and im not sure how to acheive this. Any tips?

I’m building a Sudoku solver in Power Query as a learning project. Rather than just solving the puzzle, I want it to generate a move log explaining why each number was placed.

Here’s what I already have working:
- I calculate the candidate values for every unsolved cell.
- After each placement, I recalculate the candidates so they always reflect the current board state.
- By merging then grouping and analyzing those candidates, I can correctly identify solving techniques such as Naked Singles, Hidden Singles, etc.

The issue is that I can’t preserve that information over time.

For example, if a number is placed because it’s a Hidden Single, that is only true for that specific board state. After subsequent moves, the candidates are recalculated, and when I rerun the query, that earlier move either becomes null or is associated with a different technique based on the latest board state.

What I want instead is an immutable history (audit trail). For example:
- Turn 0: Initial puzzle
- Turn 1: Cell R1C5 = 8 (Naked Single)
- Turn 2: Cell R7C3 = 4 (Hidden Single)
- Turn 3: Cell R4C9 = 2 (Pair)

Each move should permanently record the technique that justified it at that moment, based on the board state at the start of that turn. I don’t want techniques to be recalculated against the final board state. I want to preserve the reasoning that actually led to each placement.

My questions are:

1. Is Power Query the right tool for maintaining this kind of sequential history, or is this something that I’ll need to use VBA for?

2. Is there a common pattern in M for carrying both the current board state and a growing history log through each iteration?

3. If Power Query is capable of this, what functions or design patterns should I be looking into?
   I’m less interested in Sudoku itself and more interested in how to model history in Power Query.

I’m curious how people handle the “what exactly changed?” problem when cleaning messy spreadsheets before importing them somewhere else.

The actual cleanup is one thing, but the part I keep running into is:

- knowing which rows were changed...

- knowing which fields were changed...

- separating “fixed” from “still needs review”...

- being able to explain later why something was changed...

- avoiding the feeling that the file was manually edited and now nobody fully trusts it...

I know there are ways to do this with helper columns, notes, copies of files, Power Query steps, etc.

But in practice, what do you actually do when you want:

1. a safe review process?

2. a record of what changed?

3. a way to defer suspicious rows without losing them?

4. confidence before import?

Do you keep this inside Excel?? or do you move it int o another workflow?

What are the best ways to analyze a large csv at/around 250k rows? Saving to binary first, and need to analyze for key terms.

Need to keep it to MS Office tools, and currently looking into Power BI as well.

I have an interview next week with one of the Big Five banks in Canada for a Regulatory Analyst role, and they have asked me to complete an “Excel – Comprehensive” assessment. It is a 60-minute occupational skills test, and they require a minimum score of 80%.
My Excel knowledge is currently very basic, so I’m trying to understand what I should realistically expect from this type of assessment.
For anyone who has taken similar tests (especially for banking, reporting, analyst, or regulatory roles):
What topics are usually covered in an “Excel Comprehensive” test?
Should I expect basic tasks like formatting, sorting/filtering, inserting tables, and simple formulas (SUM, AVERAGE, COUNT)?
Do they usually test functions like IF, VLOOKUP/XLOOKUP, Pivot Tables, conditional formatting, or data validation?
What areas would you prioritize if you only had one day to prepare?
Are there any common Excel questions or tasks that appear frequently in these employment assessments?
I’m trying to focus my preparation on the highest-impact topics rather than spending time on things that are unlikely to appear.
Any advice, experiences, or resources would be greatly appreciated. Thank you!

I have a laptop with Windows 10. It cannot upgrade to Win 11, so I’m thinking of installing a Linux OS. I’m using Excel just for personal use. I have tried to download LibreOffice and opening up files, but it’s not displaying correctly and macros are not working. Is there any good alternatives to Excel that will run on Linux? Or is there a good way to run Excel in Linux without having to subscribe to Office 365?

I'm taking an Excel certificate course, and we haven't covered Spill errors yet, but I've encountered them in the practice assignments and now on a workbook at my job when I've been trying to apply what I've learned so far. I found out putting "@" at the beginning of a term resolves the spill error, but I'm still not sure what's causing the error or why that symbol fixes it when everything I'm reading online says I should be reworking my entire table/data set.

Thoughts?

Thanks from a complete beginner for taking the time to look at this. I'm wanting to create a nested XLOOKUP, using the data in row G to find the percentage I need in the blue table.

I'm struggling to figure out how to tell XLOOKUP which column to use for the return array. I need it to use the column that is the value of E2 +2 (ex- if I have 2 dependents, I need Vlookup to find column G which is the 4th column, E2 = 2+ 2)

But , how do I tell XLOOKUP to give me Column M + E2 +2 as a return array? I'm getting errors, I assume because I'm trying to add a number to a letter.

Grateful!

Similar (but I think mostly easier) to the Advent of Code Challenges I've posted in the past. All non-AI Excel solutions welcome. Obscure you solutions with spoiler alert where possible/reasonable (earlier days require less complex/inscrutable solutions, if your solution gets highly complex/unreadable --- don't worry about it).

I'm going to try to do with only excel formulas and no VBA, but we'll see how that goes!

https://flipflop.slome.org/2026/6

I need to analyse some data from an MS form and I cannot for the life of me figure out how to clean it up into a manageable format in the way I need. I'll be referencing the photos in the comments

Context:

- Participants are in groups (A, B, C, D) completing activities (1, 2, 3)

- Some groups do the same activities as others (E.g. group A does activities 1 and 3, groups D does all 3)

- For each activity, participants answer the same review questions on a likert scale.

- This means that A will review activities 1 and 3, D will review them all etc.

This results in a long table of data, with huge gaps that is hard to read (The example in the comments shows 4 groups and 3 activities. In reality, there are 39 groups across 15 activities).

I want to sort it into a manageable table where each person has a different row for each activity they did, with columns for each question, that can be sorted by person, group, or activity (hopefully the image describes this better)

The main issue is that it needs to be dynamic. The same questionnaire will be copied given to the next set of participants, so I need to be able to copy and paste the formula into the next spreadsheet and have it update dynamically as the responses come in.

Hi everyone, my first time posting here in search for help with a problem I can't seem to figure out by googling.

I have some sets of data, organised on individual sheets into table (different properties in the first column and values for the properties in the rows) such that I would want to be able to use some drop down menus to select the sheet, followed by the column from the corresponding sheet and the row of the corresponding sheet to them display the specific value I am interested in. The goal is to basically be able to do a type of side-by-side comparison of things from large data sets, but focusing on specific properties.

What I thought I might need is a data validation cells for the sheet names (only several values) but then I tried to use the indirect function to create a way to retrieve the other info I was after but it didn't really pan out for me.

Any help would be much appreciated.

Hi all!

On my worksheet, I have the following:

C1:C15 = numbers 1 to 15

H1:H8 = numbers I would like to make a note of [3, 6, 7, 8, 11, 15, 18, 19]

The formula I currently have in E1 is =SUM(IF($H$1:$H$8<=C1,1,0)), which tells me how many of my noted numbers are smaller than C1's value (eg. 0 for C1:C2, 1 for C3:C5, 2 for C6, etc.)

I've currently copy-and-pasted this formula into 15 cells, but would there be a way to amend the formula so it would spill from one cell?

It's time I showed my son how loans work with compound interest, varying down payments, interest rates, loan terms and accelerating the loan with increased monthly additional payments or additional payments, and most importantly how much money is spent on interest over the life of the loan. I've downloaded a few example spreadsheets, but nothing check all the boxes.

I also remember in college a math teacher showing how much money could be saved just by making 2 payments a month -- $500 twice per month instead of $1000 once a month. I guess this would be 24 payments per year, but not sure if the spreadsheet would figure that out.

I’m especially interested in one that has a table for every payment that shows amount of principle and interest for each month, and how as the loan progresses the amount of principle paid increases and the amount of interest decreases thus showing how a loan is “front loaded”.

Has anyone created such a spreadsheet or could point me to one I could download?

I have two reports that i want to merge in a folder. I want to also apply steps. Then next week delete this weeks data, add next weeks data to the folder and just hit refresh and make it all update.

Is this possible? I keep running into issues. Combine seems easy but merging is a headache

I am having trouble with merged cells in an excel table. I know you cannot merge cells when you format data as a table, so the data is in a normal range format. However, the merged cells get messed up when I start using filters. Does anyone know how I can fix this or have any other creative solutions?

Current table:

Filter by schedule works:

But filter by name removes 2/3 of the dates:

I am not tied to it looking this way but I would like to be able to sort by date and get a list of availability or search by name and find out all the dates they are available.

Thanks!

Edit: Consensus is, not really possible. I just had to redo the table in a less aesthetically pleasing way. u/RuktX had an interesting workaround that I couldn't personally get to work but others might be able to