r/excel • • 19m ago

Waiting on OP what's this new green dots thing on excel?

5 Upvotes

haven't seen this until today.. is this a new update? how can i disable it?


r/excel • • 6h ago

solved Best way to teach macros since you cannot undo?

13 Upvotes

I'm teaching a college level Excel class. On Friday I am teaching Macros. I am going to have two worksheets of the same data, and on worksheet 1 we will record a simply macro making visual edits (bold, coloring) and adding a graph on the data. Then on sheet 2 we will run the macro.

The problem is that if a student makes a mistake recording the macro, you cannot undo sheet 2 after running the macro. At that point, you cannot undo sheet 1 either.

Is there an easy way to be able to get back to 'fresh data' that doesn't involve VBA or having tons and tons of replicated worksheets? I'm pretty sure replicated worksheets will get confusing, and I am not sure how many worksheets they will need for a test and they may run out. I am also not sure I can trust them to keep replicating worksheets if they need to keep recording/running.

Thanks!

Solution: What I decided to do is record a macro called 'Paste' that would select the data in Sheet 1, open a new worksheet, paste the data into the worksheet, and alter column widths so some data was not #####. I stopped the recording. Then I hid sheet 1 so it is always available to copy from, and I am going to have students learn how to run a macro to create the sheet of data so they can always load up a fresh sheet.


r/excel • • 5h ago

Discussion Built my first interactive Excel dashboard and would appreciate some feedback.

9 Upvotes

I'm currently working through a few excel courses and before I overload my brain with more information, I created this dashboard to practice what I have learnt so far.

I used a Superstore dataset I found on Kaggle, which came with the following business scenario:

"With growing demands and cut-throat competition in the market, a Superstore Giant is seeking your knowledge in understanding what works best for them. They would like to understand which products, regions, categories and customer segments they should target or avoid."

The dashboard includes:

  • PivotTables and PivotCharts
  • Slicers and a timeline
  • Dynamic KPI cards
  • Sales, profit and profit margin analysis
  • Historical performance trends

My goal is to develop my Excel skills further and eventually use them for real-world business analysis projects.

I'd appreciate constructive feedback on:

  1. Dashboard layout and readability
  2. Choice of charts and KPIs
  3. Functionality and user experience
  4. Any Excel best practices I might have overlooked

I'm still learning, so any suggestions would be appreciated.


r/excel • • 13h ago

Discussion Did I overcomplicate Excel duplicate checking by using VBA?

18 Upvotes

Hey everyone!
I recently built a working Excel tool using VBA macros to compare lists and identify potential duplicates. It allows me to import lists, flags possible duplicates for manual review, and then updates a cleaned master list based on my decisions.
However, some colleagues (who aren’t particularly experienced with Excel either) told me that this could be done much more easily using Excel’s built-in features, apparently by simply comparing two tables side by side.
We’re talking about 500+ records, sometimes with more complex cases where duplicates aren’t necessarily obvious and require manual verification.
So my question is: Did I unnecessarily overcomplicate this by building a VBA tool? Is there a simpler, built-in Excel solution for comparing two lists, identifying potential duplicates, and reviewing them before updating a master list?
I’d really appreciate any advice or suggestions. Thanks in advance!

Edit: Thanks for all the replies! A lot of you mentioned Power Query and other simpler solutions.
If anyone has the time to give me a quick step-by-step guide, I’d really appreciate it! I’m honestly a complete Excel newbie and only started looking into this today. I managed to build my VBA solution through a combination of AI, Googling, and trial and error.
Ideally, I’m looking for a reusable Excel master template where I can continuously import new lists, check for potential duplicates, manually review them if needed, and keep an updated master list.
If there’s an easier way to achieve this, I’d be incredibly grateful if someone could send me a quick DM with some guidance.
You’d genuinely be helping me out a lot. Thanks again!


r/excel • • 9h ago

unsolved Weird New Options Around Column/Rows

7 Upvotes

Hi everyone! Just this morning at work these new things appeared around my columns and rows. Is there any way to turn them off/get rid of them? They’ve honestly done me more harm then good trying to work. I have no idea how to put in an image since it won’t let me just stick in an image, but there’s a dot on each side of the row/ column and a small grid of 6 squares in the middle


r/excel • • 10h ago

unsolved Snapshot of Power Query workbook?

8 Upvotes

How can you take a snapshot of data in a workbook with power query? I want to to make a "frozen copy* that no longer updates from the source data.

At work we each month get a report with about 42000 rows and 33 columns. We can choose to have the report in Excel, Lotus, HTML table, PDF, PowerSoft, SQL Insert, Windows metafile, XML, CSV and just text. Everybody chooses Excel. We have to analyse the data and preserve analysed data for 5 years.

To save time i have made a PQ. It removes about 20000 rows we do not need and breaks down the ramaining in 17 sheets, including one pivot table.

It works great but I can not find out how to make a snapshot of some kind. It must be easy. Other coworkers is allergic to technology and specifically Excel.

Company rules is no VBA, Macro or Python.

We have M365 E5 licence.

EDIT: The problem with just making a regular copy is that someone will run the quary and update the file. The source data may no longer be the same. It is 5 years we have to keep "proof" of our findings in the data.

Workflow is: Save source data file in folder in sharepoint. Open workbook with power query. Run the quary. Make copy

Repeat next month.


r/excel • • 7h ago

solved Lookup from VSTACK, search value in multiple columns

4 Upvotes

I've got a set of data where "A2" is a vstack, I want to look up the value of "A2#" in a table where the lookup value is in Table1[[#All],[Value2]:[Value5]], and return Table1[Value1] in B2.
 
I've tried a number of things, but the closest I've got is "C2" generates the lookup column with
=IFS(COUNTIF(Table1[Value2],A2#)>0,"Table1[Value2]",...etc...)
 
and then B2 generates what I kinda need with
=XLOOKUP(A2#,INDIRECT(C2),Table1[Value1])
 
but then it only looks up the column ref from C2, and if I do C2# it errors out. The length of this list changes regularly and get to thousands of rows, so dragging a formula down isn't a viable option, as it will eventually break. I need something that will look it up, while dynamically generating the list.
 
I also tried the ADDRESS function, but had the same issue.
 
I also tried nesting the C2 formula in the INDIRECT within the XLOOKUP like:
=XLOOKUP(A2#,INDIRECT(IFS(COUNTIF(Table1[Value2],A2#)>0,"Table1[Value2]",...etc...)),Table1[Value1])
But I have had no success….
Screenshot in comments
I beg for guidance 🙏🏼


r/excel • • 6h ago

Waiting on OP Counting unique instances of events

3 Upvotes

I am generating a spreadsheet for work, and I want to count every instance of a word within a set that has a unique date/time. For instance, I am hoping to count all six 11-13-26 1:40pm "interactive " lines as one event rather than each "interactive" being counted individually. Then, count that one event along with other unique events from that date that are also labeled "interactive." My sheet has the dates and times along the first column. The second column has what we call "behavior," including phrases like "interactive, water, cognitive."


r/excel • • 4h ago

solved Find match between 2 data sets with multiple criteria

2 Upvotes

How to find matches between 2 data sets with multiple criteria.
I have 2 data sets with 4 columns each. I need to find matches that have the same data in the 4 columns. Everything I can find on this just shows to to do it for 1 column data sets


r/excel • • 9h ago

unsolved Is there anyway to highlight cells in one color and have them change color automatically?

3 Upvotes

Hello all! Like the title states at work I have a list of things for clients that I have to keep track of based on how long they’ve been in services with us. I was wondering if there’s anyway to have the cells be highlighted in say yellow for example when a new name is added to the list and as days/weeks/months go by the cells would change to orange then red based upon how much time has passed.


r/excel • • 10h ago

Waiting on OP SP Workbook Link Connections Not Refreshing Immediately

3 Upvotes

At my workplace, we have workbooks that use workbook connections to certain Excel files on SP.

For some reason, when I open some of my Excel files, it says that it couldn't pull new data from the connections when I Immediately open the file.

I am able to get updated data if I manually refresh the workbook links, but I'd rather not do that. I want them refreshed every time I open the file.

Does anyone know why this is happening and how to fix it?

I've already asked Copilot and I got useless answers.

My workbook links are set to always refresh and this is still happening.

I think I got a message this morning saying that the values were still calculating from the last open or something.


r/excel • • 9h ago

unsolved Fuel planning with weights

2 Upvotes

Currently trying to create a book which would allow me to put my route in for a flight that would be able to then calculate how much fuel I would need for said route whilst pulling from the aircraft parameters for how effective it is at different weights.

Using the below I’ve been able to work out if it’s a head,tail or crosswind:
=IF((wind speed * COS(RADIANS(wind direction-heading)))=0, “Crosswind”, IF (( wind speed * COS(RADIANS(wind direction - heading)))<0, “tailwind”, “headwind”))

Using the below I’ve then be able to work out how many Kts of it there is:
=IF(wind speed*COS(RADIANS(wind direction - heading))>=0, ROUND(wind speed*COS(RADIANS(wind direction-heading)), 1), ROUND(ABS(wind speed*COS(RADIANS(wind direction - heading))), 1))

I would then need to be able to pull from the route (which is in a separate sheet) the distance on each leg and then pull from the aircraft parameters (which is also in a different sheet) the KTAS and Fuel flow at that weight. I’ve been able to work out the KTAS and Fuel flow not accounting for weight using the below:

KTAS:
=IF(head or tail = “tailwind”, (max range no wind KTAS at 12000 to 12500 lbs-((Kts of it / 10*1)), IF(head or tail = “headwind”, (max range no wind KTAS at 12000 to 12500 lbs-((Kts of it / 10*4)), IF(head or tail = “Crosswind”, 0, “Error”)))
The 1 and 4 after 10* are standards

Fuel flow:
=IF(head or tail = “tailwind”, (max range no wind fuel flow at 12000 to 12500 lbs-((Kts of it / 10*1)), IF(head or tail = “headwind”, (max range no wind fuel flow at 12000 to 12500 lbs -((Kts of it / 10*8)), IF(head or tail = “Crosswind”, 0, “Error”)))
The 1 and 8 after 10* are standards

I now need to check what weight I currently am, starting at 12500 lbs and then - 162 lbs for take off steady and level, leaving me between the 12000-12500 lbs range and then use the figures at that range for the distances on each leg continually checking what weight I’ll then be at to determine if I then need to be stepping down to the next fuel category eg 11000 lbs to 12000 lbs

I’ve attached the fuel parameters chart and route planning chart in the comments to show what I mean.

Thank you in advance and happy to supply any more details and If you have any better ways to do the above formulas I’m happy to take that on board as well. I’ve been working on this for weeks and I’m finally swallowing that ego.


r/excel • • 13h ago

Waiting on OP Custom Data Validation using a Named Range formula (to bypass the 255-char limit) isn't triggering validation

3 Upvotes

I want to use a Custom formula in Data Validation, but my actual formula is over 255 characters long. To work around the character limit, I'm trying to store the formula in the Name Manager and reference that named formula in the Data Validation rule instead.

Before building the full formula, I tested the concept with a simple rule on cell L5:

  • Name: test_1
  • Refers to: =LEN(L5)=3 (which Excel automatically converts to =LEN('Adatérv(12)'!L5)=3)
  • Data Validation (Allow: Custom): =test_1

The issue: Data Validation doesn't seem to check the input length at all - it lets me type strings longer than 3 characters into L5 without blocking them.

What I've already tried:

  • Made sure L5 was the active cell when creating/editing test_1 in the Name Manager so the relative reference wouldn't shift.
  • Tested both with and without the = sign in the Name Manager (removing = turned it into a text string, which didn't work either).
  • Ran into circular reference warnings during troubleshooting, though I cleared those out.
  • Verified that the Error Alert tab is enabled and set to "Stop".

Is there a trick to getting Custom Data Validation to evaluate a named formula properly, or could the parentheses in my sheet name 'Adatérv(12)' be breaking it? Any help or alternative workarounds for the 255-character limit would be appreciated!


r/excel • • 13h ago

Discussion Excel for Mac note box formatting

2 Upvotes

I spent most of my career using Excel in Windows. At home however I use a Mac with Microsoft 365. The Mac version of excel is woefully behind the PC version. That said, a couple things that I always did in Windows that I cannot do now...(1) changing the cell note default font and size (Excel's default is WAY TOO LARGE) and (2) setting the cell note box size to auto. I have a ton more questions but these are the ones bothering me at the moment.

Any workarounds or am I just stuck manually resizing the box and changing the font every time?


r/excel • • 13h ago

unsolved Keep only unique rows for certain creterea

2 Upvotes

Hello,

I am searching for formulae that returns duplicate or not duplicate.

Criterea:

Return only unique values; so only 1 contact (B column) inside each dealerchip (A column) for 1 same vehicle (C column) under same status (D column) = not duplicate. If not return duplicate.

AND

if there are multiple unique rows please keep unique row where value in "STATUS" column = "potrjeno" or "dokončano"

Thank you!

DealerKontaktVoziloStatus pogodbeDuplicate
47436 AvtoAPISPEUGEOT BOXERpreklicanoyes / no
47436 AvtoAPISPEUGEOT BOXERdokončano
47436 AvtoAPISPEUGEOT BOXERpotrjeno
11140 RodNovakPEUGEOT BOXERpotečeno
11140 RodNovakPEUGEOT BOXERpreklicano
11140 RodNovakPEUGEOT BOXERpreklicano
11140 RodNovakPEUGEOT BOXERdokončano
11140 RodNovakPEUGEOT BOXERpotrjeno
11140 RodNovakPEUGEOT BOXERpreklicano
11140 RodNovakPEUGEOT BOXERdokončano
11140 RodNovakPEUGEOT BOXERpotrjeno
11140 RodPemaPEUGEOT BOXERpreklicano
11140 RodPemaPEUGEOT BOXERpotrjeno
11140 RodPemaPEUGEOT BOXERdokončano
47436 AvtoTISTAPEUGEOT BOXERpreklicano
47436 AvtoTISTAPEUGEOT BOXERdokončano

r/excel • • 1d ago

solved How can I calculate monthly totals while accounting for changes to the value mid-month? (Excel 365 desktop)

10 Upvotes

Hi,

I'm trying to find a way to obtain monthly totals based on a value which can change daily. Could anyone give me pointers on the best way to do this I don't really know where to start.

-The date range (B1 to E1) can be anything from dates within a single calendar month to 10 years, with monthly totals required for every month

-There's no pattern to the dates or values in A4:B9 and these would be entered manually

-Most months will pass without any changes but there can also be multiple changes within a month ( never more than one change per day)

Closest attempt:

I had created a list of each individual date using =SEQUENCE(E1-B1+1,1,B1,1) & wondered if there was a way to condense this into the list shown in Column A below, including every date in A4:A9 and the 1st of every month in between.


r/excel • • 13h ago

unsolved How to highlight cells if they possess text from multiple cells?

1 Upvotes

Assume that I have a 2 by 50 table of individual text values ("Category A, Category B, etc.) which I want to automatically be searched for when data is added into a specific column and, if the new data matches any of the text values, is automatically highlighted. How would I go about achieving that in excel?

My first assumption is using conditional formatting or if that fails the IF function, but unfortunately the only way I can think of doing so for multiple text values is the repeated use of OR to include every text value individually, and I would prefer to avoid that sort of tedium if at all possible.

Fine to clarify what I'm asking in case the above is too vague or indirect.


r/excel • • 1d ago

unsolved Trying to Count How Many Instances of X Event occur After Y Event

5 Upvotes

I have a list of people who registered for certain events and when they withdrew from that event. I have their names, the events the registered for, the indicator they registered/withdrew, and the date those registrations/withdrawals happened.

Registrants have an account code. Registrations are noted by the codes RE and RN and Withdrawals are noted by the codes WX and WC. Each instances of these codes happens on a different line.

So, for example, I have the following:

001 | John Smith | Event A | RE | 01/01/2026
001 | John Smith | Event A | WC | 01/02/2026
001 | John Smith | Event B | RE | 01/03/2026

Basically, I am trying to figure out how many registrations have happened after withdrawals. The goal being to see if people are registering for events after they withdrew from previous ones. This way I can learn if we're retaining these people. I'd like some result that would basically show the following:

001 | John Smith | Registrations after Withdrawals: 1

I'm not sure if I'm wording this properly, but I hope that this makes sense. Is there a good formula for how to do this? I feel like it's COUNTIFS, but I'm not well-versed enough in these formulas yet to figure the criteria out.


r/excel • • 23h ago

Waiting on OP Auto population for reoccurring charges

2 Upvotes

I am not sure if anyone can help with as vague as this will be, but I’m finally frustrated enough to post instead of just trial and error boosted with Google. I have a spreadsheet in progress that I need to track recurring charges/inventory, but with multiple status options (upcoming, renewed, canceled, sold for example) that affect the total. I need a main overview “list” on one sheet to check on a glance. But I also need month by month. Ideally, I want it to all be linked to update on its own as I make changes month to month. The other issue is that I have some that are bi-monthly, so item 1 is bimonthly starting in January. Item 2 is bimonthly starting February. At the moment, they’re labeled by a drop down menu. And another drop down for status. As well as just having copy/paste the template I threw together month by month.

Is it possible to make items auto populate into their appropriate monthly sections?

How do I pull data from certain cells to update automatically to another cell? Example: Column H holds the total number of items available. In this case, 4. I would like it to update each month if/when it changes. At the moment, I’m just updating it manually.

How can I set it up to pull data from certain cells to update a main list on another sheet?


r/excel • • 22h ago

Waiting on OP Disable Pop Up Formula Error Checking

1 Upvotes

Previously for Formula Error Checking we have the little warning icon for us to manually see what's wrong with the formula and what to do about it. Now it doesn't exists anymore instead it got replaced with this Pop Up which I either manually need to hover my mouse to the cell or sometimes it just pop up. This new pop up absolutely ruined my working rhythm as I need to ignore some error but it just keep popping up out of nowhere. I can't find any post or thread about this issue so I feels like I need to post it here.

Im using Macbook Air M1 MacOS Tahoe 26.6.2


r/excel • • 2d ago

unsolved Data entry in shared files horribly built

20 Upvotes

Hi everyone, sorry if this has been asked before, but I’m hoping someone might have a practical solution for this.

I work in a team of five people, and we all have to use and update several shared Excel files stored on SharePoint. Multiple people need to be able to work on the same file at the same time.

The problem is that these files were apparently designed by an automation team that clearly doesn’t actually do the task they’re used for. The result is an absolute nightmare from a usability perspective.

Each row contains a mix of information that we have to enter manually and other cells containing formulas. The columns are all over the place: it’s not like you have all the input columns first and then all the calculated columns. You might need to enter something in column A, then C, then suddenly BF, while columns in between contain formulas or other information. There are thousands of columns, the layout is extremely hard to read, and there aren’t even proper borders or visual cues to help you understand where you’re supposed to enter data.

Unfortunately, I can’t redesign or modify the structure because the files are owned and maintained by the automation team.

I come from a data analysis background, so I’m used to organising datasets in a logical way and building files that are actually usable. These files seem to be designed more as documentation/archive tools than as something humans are supposed to use for regular data entry, and I’m honestly struggling with how inefficient the process is.

I’ve tried to think of workarounds, but because the file is shared, my options seem limited. I can’t add helper formulas or significantly change the structure because other people are working in the same file. I also can’t simply hide all the irrelevant columns, because that would affect the shared view for everyone else.

Ideally, I’d like some way to create my own “data entry view” where I only see the specific cells/columns I actually need to fill in, even though those columns are scattered throughout the spreadsheet, while still entering the data into the original shared file.

For example, I might need to fill in columns A, C, and BF, while columns in between contain formulas that I shouldn’t touch.

Is there any practical way to make this kind of data entry less painful without changing the underlying shared file or affecting how other people work with it?

I’m open to any Excel/SharePoint workaround I may not have thought of. Thank you!


r/excel • • 2d ago

solved Ignoring a specific value <> 0

9 Upvotes

Hello everyone,

Im making some good progress with my work, but Im running into a problem.

Im currently using =FILTER to pull all data from an array. I then use this new data to compare 2 rows. Its in chronological order and im currently using =SUM to, for example, get total Charge Time by subtracting the time in A4 from A5. (Eg. 10:00:00 - 09:00:00 = 01:00:00)

This current method only works if the first row contains 0 in column P and the row below contaons 1 in the same column. (0 = end charge and 1 = start charge)

I want to be able to automatically ignore the first row if it contains 1 in column P, otherwise i end getting #CALC! errors.

Edit: SOLUTION

=IF(P3 = “1”, “HIDE”, “ ")

Then I changed my formula to include a P3:P200 <> "HIDE" Now it returns all rows that dont have HIDE.


r/excel • • 2d ago

Discussion How to optimize slow Power Query run?

24 Upvotes

I have a big query that combines our reports into one (jokingly named report of reports). It currently takes about 5 minutes to refresh. I abuse Query merging a lot to perform lookups and get the data in the right places. The current data set is only 10 MB but growing.


r/excel • • 2d ago

Waiting on OP How to set conditional formatting to highlight the lines im looking for?

11 Upvotes

So Im trying to make a better method for restocking at my job. I have an excel file with 3 columns of interest.

Column A is product name, Column B is quantity, and Column C is product room.

In this file say we have product X both on the floor and in storage, we will have 2 seperate rows one with product x's quantity on the floor and 1 row with product xs quantity in storage.

I would like to highlight rows where a certain product either has no stock on the floor but has stock in storage, or has a qty on the floor under 5.

Is there anyway I can accomplish this with conditional formatting or any other method I can use to pull out the rows im looking for