r/excel

I have a spreadsheet that has a list of interest accrued, daily, over 4 years. Something like:

INTEREST

21/10/25 $5.10

22/10/25 $2.67

23/10/25 $7.89

I am trying to use sumifs and edate together to sum all the figures for a given month starting from the 10th of the month, so that I can drag the formula through to apply across the 4 years. But I’ve twisted myself into a mental pretzel and can’t seem to make it work.

I would like the end result to be one figure per month, displayed next to the 9th of the month. 

So for 9th May 2026, the figure would be the sum of all interest from 10th April 2026 to 9th May 2026. 

Does anyone know how to untangle this one? Help would be very appreciated.

Additional info:

• Excel Version (Microsoft Office LTSC Professional Plus 2024)
• Excel Environment (desktop, Windows)
• Excel Language (English)
• Your Knowledge Level (Beginner to Intermediate)

Syntax:

The dates are entered as DD/MM/YY (which seems to update automatically in the body of the cell to DD/MM/YYYY, then displays when the cell isn’t clicked as DD-MM-YYYY).

Where I‘m getting stuck:

- I‘m working on the premise that I’d need to make a set of formulas that:

(a) extract the date within the month; and

(b) then specify that if it’s exactly the 9th, then sumif from the 10th of the prior month up to and including that day.

- I can’t seem to isolate how the sumif function can do that.

Hi, I've never used the excel macro, I have 400 files that have the same column structure, but each one contains a different number of rows. I want to automate the same calculations/formulas across all spreadsheets for multiple columns inside the spreadsheet, for example adding new columns with formulas and automatically filling them down to the last row containing data in each file. What would be the best way to do this in batch? Would VBA/macros in Excel be the best option, or is there a better alternative? If possible, I’d appreciate an example of a script to apply formulas to every file inside a folder in batch.

Hi, I got a spreadsheet named 01 on OneDrive.

I created a checkbox in column G, and I would like to add a timestamp in column K when the checkbox is checked. When the timestamp is removed when the box is unticked.

I tried a few examples from Google search and YouTube, but they didn't work.
I also tried to make it in Excel, then uploaded it back to OneDrive, but the function didn't work.
Any easy solution? Thank you

Basically, yesterday my boss order me to do a excel that automatize some of our database. At this moment, I have the database like in picture one, my idea is create a panel, where after I select the packing couple, its automatclly fill the text box with the tests that are aplicable to (image 2).

I need to deliver this until tomorrow.

Someone knows how I can do this? Its hard?

I already tried with GPT and Gemini, but without sucess

PS: English its my second languase, so excuse any mistake.

The database are like that

This is how I wanna to it be

https://preview.redd.it/1w9f4cynvg2h1.png?width=3733&format=png&auto=webp&s=3e6dfc126f3c4e610493da48e55db11ecc7546cc

I'm not that well versed in excel outside of knowing the immediate basics and i'm looking for a clever(erer) way to set up an economy sheet for my band that keeps track of travel costs, income and personal expenses as well as some degree of merch tracking (we'll do individual items by hand, i'll just need the overall costs and income in the sheet to balance the sheet).

I'm basically looking for some inspiration or tips on how to properly set it up where it keeps track of the above mentioned things.

What i've put in the example sheet is personal expenses (would love some thoughts on how to include individual contributions as well), travel costs, gig/merch income and merch costs in a way that can help us get a better idea of where we are economically.

I've tried a couple of templates i've found online, but none of them seem to do what i need.

Any tips are greatly appreciated!

A co worker sent me a file to do some work on over teams and SharePoint. The dates which can be interpreted as US dates were automatically converted. And the datedif function won't work unless they're in US format?

This is stuff I was doing years ago with zero frustration and it's an absolute pain now. Is this a configuration issue my employer has made or is Excel a basket case since office365?

I have a sheet which has some data from the future already prefilled, but not everything, so some inputs are 0 for now. This leads to div0 on a bunch of fields calculations further down.

Ordinarily IFERROR would be fine, but I want to be able to see #N/A, #REF and so on still. Unfortunately there's no IFDIV0 like there is for IFNA.

Is there an elegant way to do this?

I've got a workbook with multiple sheets.

Is there a way to automatically export specific sheets on a schedule?

Thanks, any help is appreciated

Been using Excel for data work for a few years now and VLOOKUP was just muscle memory at this point. Last month I finally switched over to XLOOKUP on a project where I was pulling values from multiple sheets and man, it's a different experience.

No more locking in a column number that breaks the second someone inserts a column. XLOOKUP lets you reference the return range directly, it searches both left and right by default, and handling not-found errors is built right into the function instead of wrapping everything in IFERROR.

If you're still on VLOOKUP out of habit, try swapping it on your next task. The syntax clicks fast and you'll wonder why you held on so long.

Me: This will be a quick 5-minute Excel task.

Excel: Interesting.

5 minutes later:

• Why is the formula returning #N/A?
• Who merged these cells?
• Why are dates stored as text?
• Why does one value have a trailing space?
• Why is there a hidden column Z?
• Who named this file Final_v2_Actual_Final_UseThisOne.xlsx?
• Why does the pivot table say 0 when my eyes say 47,382?

2 hours later:

• Learned three new formulas
• Questioned my career choices
• Muted my entire family
• Created a masterpiece no one will ever understand
• Saved the file as Final_v2_Actual_Final_UseThisOne_REALLYFINAL.xlsx

Boss: Looks great. Can you make the header blue?

Hey everyone,

Just wanted to share a formula setup that completely changes how you track sequences, especially if you’re tired of messy old-school helper columns.

We all know UNIQUE is great for telling you what is distinct in a dataset, but it doesn’t tell you when something showed up for the first time chronologically. If you have a long list of transactions, logs, or customer check-ins and you want to flag the exact moment a value debuts - row by row, in order - you need a running unique index.

A lot of examples online tell you to use a basic SCAN and COUNTIF combo with a custom range. Except there is a huge catch: Excel doesn't actually accept that raw variable as a valid range index inside COUNTIF, and if your range has blank rows at the bottom, the counter gets totally confused and starts indexing the empty spaces.

Here is the clean, bulletproof version that handles expanding ranges and ignores blank rows perfectly:

=LET(
    rng, A2:A100,
    SCAN(0, SEQUENCE(ROWS(rng)), LAMBDA(acc, idx,
        LET(
            current_val, INDEX(rng, idx),
            sub_range, TAKE(rng, idx),
            IF(current_val="", "", IF(COUNTIF(sub_range, current_val)=1, acc+1, acc))
        )
    ))
)

Instead of feeding the raw text data directly into SCAN, this setup feeds it a row index using SEQUENCE and ROWS. Inside the LAMBDA, TAKE creates a dynamic, expanding slice of your data that grows row by row, looking from the very top down to where the formula is currently working. COUNTIF checks that specific slice. If it’s the first time the value pops up, the counter ticks up. If it’s a duplicate, it holds flat.

The real lifesaver here is the blank check at the end. If your data stops at row 50 but your range goes to 100, it leaves the remaining rows completely blank instead of filling them with ghost numbers.

This comes in handy for a ton of real-world scenarios. In a CRM system, you can use it to pinpoint the exact transaction row where a customer ID converted for the first time. If you do log analysis, it lets you flag the exact timestamp a specific error code made its first appearance. It is also great for inventory management when you need to mark the precise row a new SKU debuted in a seasonal order list.

The best part is that it lives in a single cell, it is fully dynamic, and the index updates automatically as you add rows. Just a heads-up: since it uses COUNTIF on an expanding range inside SCAN, it can get heavy if you throw 50k rows at it, but for standard datasets it works like a charm.

SCAN has been around since 2022 but it feels like barely anyone touches it. Definitely worth adding to your toolkit if you want to level up your spreadsheet game.

Note; content tested and verified on Excel 365.

I’m curious how people here feel about AI tools for Excel.

I’ve tried using LLMs for spreadsheet work, and it helps with things like explaining formulas, cleaning up small parts, or figuring out how to approach a task.

But the workflow still feels pretty off. I copy something from Excel, paste it into ChatGPT, get an answer, move it back into the workbook.

What are you using? Excel Copilot, ChatGPT, Claude, add-ins, something else?
And what do you still prefer to do manually?

Right now, I'm in the process of trying to gain an in-depth understanding of MS Excel, as well as how to use it in various cases - whether trying to collect data, building dashboards, and interpreting data results. Now the question is, should I try mastering Excel first before moving towards Power BI, or is it inconsequential?

Also, how exactly is Power BI needed when MS Excel in itself is capable of housing dashboards and charts?

Hi folks, I'm trying to find the last date for a customer in an array which rows has blank values.

I'm currently trying to use an Index function as I think this will help with the gaps.

For example, if we supply a customer which is listed in row 4 and row 12 with a blank anywhere in between row 4 and row 12, I want to return a date value that is at the intersection of row 12 and column 2 if that makes sense.

I've got this big table and customers repeat periodically throughout and I'm trying to find the date value the last time the customer appears.

I'm trying to work out using an Index function because my array has rows where there is no value.

Below, is an example of how the table looks. If i was looking for the last time Customer 4 appears, I'd like for it to return the Supply Date value of 21/5/2026.

Customer Name Supply Date

Customer 1 18/05/2026

Customer 2 18/05/2026

Customer 3 18/05/2026

Customer 4 18/05/2026

Customer 5 18/05/2026

Customer 6 18/05/2026

Customer 7 18/05/2026

• Blank line -

Customer 8 21/05/2026

Customer 9 21/05/2026

Customer 10 21/05/2026

Customer 4 21/05/2026

Customer 11 21/05/2026

Customer 11 21/05/2026

Customer 11 21/05/2026

Customer 12 21/05/2026

Hopefully that is clear as mud.

Well, I rely heavily on Excel for my current work but it's in Windows. I'm using MacOS at home, and I want to get better at Excel in my free time but there are some annoying differences which make the process hard.

Mostly, it's dates differences. When I type 18-2 and click enter in an Excel file on Windows, it gives me the date (dd.mm.yy) 18.02.2026. When I do the same on MacOS, it gives me ...well, it gives me nothing.

Have you noticed other differences which are inconvenient for you?

Hi there,

Trying to get cells to change to orange if they have an expiration date within the next 30 days or red if they are already expired. I have tried every formula I can find but I need the formula to take the year into account, since many of our supplies don't expire until the next year. If I need to use a helped column I can do that as well, but if possible to do it without one that would be even better.

One of our more active clients at work provides a data file in xlsx format multiple times a day, which I import into my model in order to process a bunch of calculations. The data file itself is quite small, usually up to 20 lines at most. However, it's formatted quite terribly and depending on who sends it can extra columns for no reason. It also has extra empty rows with data in just one cell in the middle sometimes. I have resigned myself to just pasting it in each time. The other issue is that this data forms part of a rolling total on my end, so power query would wipe the previous data if I tried to import it normally.

I'm decently handy with both power query and VBA, but I have never been able to figure out a good way to deal with poorly formatted data. Any tips?

Hi, I wish to know of a way I can seperate a lot of columns filled with data so that there is a blank column in between the columns with data. But not individually; all at once.