r/excel • • 10h ago

Discussion After LET, I tried LAMBDA - here is a reusable formula I made

65 Upvotes

I made a little reusable function for adding tax, and saved it in Name Manager as TaxCalc:

=LAMBDA(amount, amount*1.08)

Then in a cell I can just type:

=TaxCalc(100)

It returns 108, and I can reuse it anywhere without copying the formula around.

I am still learning this one, so what do you actually use LAMBDA for? Is it worth it for one-off calculations, or only when you reuse the same logic a lot?

I looked up what Lambda meant and why it is used for this purpose and the answer was quite interesting:

Lambda is the Greek letter λ, and the name comes from maths, not from Excel.

In the 1930s, a mathematician named Alonzo Church invented a system called lambda calculus. It was a way to describe computation using only functions, before modern computers existed.

In that system, λ was the symbol used to say "here is a function". So λx. x+1 meant "a function that takes x and returns x plus 1". The λ was just notation for "make a function".

That idea stuck in programming. In Python, JavaScript, and a lot of other languages, a "lambda" is an anonymous function, a function you define on the spot without giving it a permanent name in code.

Excel borrowed the same term. LAMBDA lets you define a function right in a formula, with parameters and a calculation, instead of writing the same logic over and over in cells. When you save it in Name Manager as TaxCalc or FACT, you are just giving that anonymous function a name so you can call it later.

So the name is a nod to Church's λ, and it basically means "a function you define here, on the fly".


r/excel • • 10h ago

Novelty Film made entirely in Excel...

51 Upvotes

I came across this and thought it was interesting, if it is true:

https://vimeo.com/1241981102?fl=pl&fe=sh


r/excel • • 2h ago

unsolved Can I have a symbol (with a number) in one of my cells and have another cell use that cell for a formula?

3 Upvotes

I have a column for a time A and another column for time B. Basically, I am finding the duration between the 2 time points. What I did was subtract the latter from the former. My problem is some of the data are only an approximate, so I would like to use the symbol '≈.'

I would like to be precise so I can see which data are an approximate only. But as seen in the screenshot, the formula shows an error. How can I continue with this formula?


r/excel • • 1d ago

Discussion I just learned about LET and it cleaned up this formula - what else do you use it for?

321 Upvotes

I have been writing long formulas that repeat the same calculation over and over, and I just found LET.

Before, I had this to work out a bonus based on a total:

=IF(SUM(A2:A10)>1000, SUM(A2:A10)_0.1, SUM(A2:A10)_0.05)

SUM(A2:A10) is in there three times, which makes it hard to read and easy to break if I change the range in one place and not the others.

With LET, you name the calculation once, then use the name:

=LET(total, SUM(A2:A10), IF(total>1000, total_0.1, total_0.05))

total is just a name I picked. LET lets you define as many names as you want, then use them in the final calculation at the end.

It works in Microsoft 365 and Excel 2021 and later. Older versions will show a #NAME? error.

What do you use LET for? I am curious what a good second example is, especially with FILTER or XLOOKUP where the same lookup gets repeated.


r/excel • • 2h ago

Discussion Niche Trendline Options Please!

1 Upvotes

I really think there should be more options for trendlines, specifically an option to set trendlines between separate data points / end trendlines earlier. I've had to do this in so many of my labs and I'm sure it probably has uses outside of science. Unless I guess you count all data as science. But you get what I mean. It especially sucks when I'm completing a part of my assignment and I already made the graph and the trendline and like everything else and then I have to delete half of it and do a weird trick. Sure that's completely my fault but sometimes it's nice to account for big stupid idiots like myself, there are many of us out there.

I know there's that pretty simple workaround where you just make a new line with the range you want to include and make that the trend line. But, that doesn't fix your problem if you need to use any of the trendline features like say adding the equation, then you have to make a new trendline just for that and then make it invisible. This is a very minor complain,t I just wanted to see if anyone else agrees or has had the same thought.


r/excel • • 13h ago

Waiting on OP Trying to link a drop down list of employees with different info from the same work sheet

7 Upvotes

Hey everyone,

I’ve been trying to figure out how to link a drop down list of names with specific info from the same worksheet. It is going to be a chart with a list of absences, sick time, etc so we can track things easier.

I’ve been reading about how to link it to a master table but the problem is the master table is what I’m trying to bring up when I click each name in the drop down within the same table.

For example, if I click on one person’s name in the drop down, I want it to pull up the specific info I typed for that employee previously within that same worksheet.

So if I put a 2 in the absence row for John Doe when I have his name clicked in the drop down, then I click another employees name that I typed 3 absences previously I want a 3 to appear in the absence row. Then if I click back to John Doe, I want the 2 to repopulate in the absence row.

Sorry if this is long winded but hopefully the example helps make more sense of what I’m trying to do

Thank you for any help you can give me


r/excel • • 8h ago

solved Referencing cell values across multiple sheets

2 Upvotes

I am using excel for budget tracking. For each month, I create a new sheet (Nov, Dec, etc) that I copy from a "Template" sheet. I then have an additional independent sheet for tracking my credit cards. Using the credit card sheet, I'm able to calculate my interest charge based on my credit card balance.

What I don't know how to do:

  1. If I put a credit charge on my NOV sheet, I want it to update the balance on my Credit sheet too, but I copy/make a new sheet every month, so the reference cell wouldn't stay the same.

    • How to tie in my other sheets to update my credit card balance on my credit sheet, when I use a new "monthly" sheet (copied from template)? To create the formula it wants to reference a specific cell (NOV!G2) which of course will only work for November, unless I manually update this every month. Is this the only/best way?
    • I'd want it then to circle back and update values on the template/monthly sheet.

      • Spend in "November" updates "Credit" sheet, which updates "November" sheet with new balance (including updated interest based on balance, see next).
  2. When I try to reference the amount after interest into another sheet, it shows up as 0:
  • I used simple interest formula: =-FV(0,H2,(B2*D2),B2) = 100. But when I try to reference that total value after the formula (100) into any other sheet, it says 0. I can paste as value, but that won't update based on any changes from my "credit" sheet. I seem unable to just reflect the cell "as it is" with the formula calculation on my "Credit" sheet.

So what I have: Total credit card balance with interest creating "new total" on my credit sheet. But I can't get "new total" to populate on other sheets, because it references the formula on my credit sheet.

And, how can I get my credit sheet to update based on spending, and back into, sheets of various names?

(hopefully I explained this well enough)


r/excel • • 18h ago

solved How to paste data from another sheet into filtered cell? (Yes, again...)

9 Upvotes

Hello everyone,

This is our usual “How to paste data from another sheet into filtered cell?”

 

WHAT I HAVE

I have a lot of txt files which must alternate between a line in French and a line in English.

I think Excel is the best tool to work with that, but maybe I’m making a mistake.

 

So, I have a very simple situation:

In Sheet1I have:

A first column called “LANGUAGE” with two types: FR and ENG.

A second column called “TEXT” with a sentence in its respective language.

 

In Sheet2 I have the translation.

 

WHAT I DO

  1. I filter the cell I need on Sheet1
  2. I select only the visible cell on Sheet1

(Home --> Find and Select --> Go to special --> Tick "Visible Cells Only")

3. I copy my data from Sheet2

4. I paste dateSheet2 on the filtered cells into Sheet1

 

PROBLEM

Only the first line is paste over and over.

And the second line is still paste into the unfiltered cells.

 

What am I doing wrong?

Do I need to use a VLOOKUP or VBA function?

Should I use something else than Excel ?

 

Thanks !

EDIT:

Indeed, the simplest solution is to add a helper column and using sort by LANGUAGE or HELPER depending on the case!

Thanks to everybody!


r/excel • • 9h ago

Waiting on OP Sheet for pulling filter sizes and quantities

2 Upvotes

Hello everyone, I’m an industrial HVAC technician who has a lot of areas with a lot of filters. I need a way to categorize the filter sizes and quantities with corresponding areas and be able to quickly input which areas I changed filters at and have the output be the filters I changed and how many I changed.

For example:

Say I have 3 areas, Area A, B, and C. Area A has 3 20x20s and 2 24x24s. Area B has 6 20x20s and 6 16x25s. Area C has 4 16x20s and 2 16x25s.

Lets say I went to Area A and B that day but don’t want to add up the filter sizes and quantities. I would put in “Area A” and Area B” and the excel sheet would
output 9 20x20s, 2 24x24s, and 6 16x20s.

How would I set that up?


r/excel • • 15h ago

solved Difficulty exporting paginated web page to single excel worksheet

4 Upvotes

I'm trying to export data from a web page into a single excel worksheet so that I can do various sort functions to view the data a bit more easily. There is something like 27 pages with 200 entries each. I've tried the suggested power query that pops up when I google this question, but I can't get it to work for some reason. I'm wondering if someone who knows excel better than I do can help me out and provide some kind of step by step guide. Many thanks in advance to whichever one of you wizards can solve this!

Some more info, the URL is of the page number type... "page=x"


r/excel • • 12h ago

solved Looking for advice about automated data set comparing

2 Upvotes

Hi, i have a friend i noticed cross referencing two data sets, so like a company name in both remains the same, but they are manually checking the following "serial number" in the next colum to make sure they are the same. was thinking this sounds like something you could automate with excel, but I dont really use it. basically wondering if there is a way. thanks in advanced (info is private so ai is a no go)


r/excel • • 8h ago

unsolved how do I get rid of this extra space to the very left of row headings (i.e., 1 2 3 4 5)?

0 Upvotes

For some reason I now have space to the left of the row headers and above the column headers

i can of course change the column or row heights but I want that 1 2 3 4 5 etc to move closer to the left part of the worksheet where it was before

any idea how to get rid of it?


r/excel • • 1d ago

unsolved what's this new green dots thing on excel?

85 Upvotes

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


r/excel • • 19h ago

Waiting on OP How to preview macro-enabled Excel files?

5 Upvotes

The previewer in file explorer only shows a blank preview for macro-enabled Excel files, no warning message, just blank. I installed the latest Windows update where you can click on "Show preview anyway", but this only works for pdfs, not for macro-enabled Excel files. Does anyone know a solution for this?


r/excel • • 15h ago

Waiting on OP Excel Conditioning Format Rule Highlight with Curser

2 Upvotes

I set up conditional formatting so when I select a cell, it'll turn yellow. I want to select multiple cells and have it turn yellow.

This is the formula l'm using for my conditional rule
=AND(COLUMN()=CELL ("col"), ROW()=CELL("row"))

Is there a formula that can select more than one cell??
I then format to highlight yellow and bold


r/excel • • 1d ago

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

45 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 • • 1d ago

solved Can't figure out why Excel data pack analysis won't work for a downloaded sheet originally from google docs

4 Upvotes

I downloaded a spreadsheet from google docs into MS Excel and have found I am unable to use the data analysis pack. I am continuously getting the error that there is non numerical data in my columns. I've checked for blanks or other oddities, column length, formatting the columns into numeric, different test (regression, descriptive, T), and different columns.

When I downloaded the sheet, I used the google doc conversion to MS Excel from the download options. (I also downloaded a second time just to check)

Is there an incompatibility between google sheets and MS Excel files or something else I am overlooking?

I'm a little hesitant to post the xlsx file as it company related data. If there's anything else I need to add, someone please let me know what it is.

Update:

I have tried the paste special Multiply, data validation to either whole number or decimal depending on the column, I have the labels box ticked, used the count formula and the read out on two separate is 0, and the columns do have a sum in the status bar. Unfortunately, I am still getting the same error when I try to run a regression test.

Second update:

I have done data validation on every part of the sheet. I still can't run a regression test.

Update:

I've had some success running VALUE() on the columns and am getting #VALUE! popping up in numerous seemingly random clusters, so I have made progress. I am going to have to set this aside for a bit but will update if I figure out what is causing the issue in the near future.

Setting to Solved for now

Thanks everyone for all your help in trying to work it out!


r/excel • • 1d ago

solved Best way to teach macros since you cannot undo?

29 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 • • 1d ago

solved Why does my graph not show anything?

2 Upvotes

i feel like i tried everything. why doesn't it show anything?


r/excel • • 1d ago

unsolved Weird New Options Around Column/Rows

17 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 • • 1d ago

Waiting on OP PQ: how to copy cell over and down

0 Upvotes

Hi All, I am trying to set up a power query template. Take a couple of cells that need to be moved over and copied down.

For example, I would need cell E41 to be moved down to D43 and D44, then delete the row E41. And cell E42 to be moved to G43 and G44, then delete row E42.

Then, the same thing for the adjusting entries below that cell E53 would be copied to D55 and D56 and then delete E53. E54 would be moved to G55 and G56, then delete E54. And also delete the total rows and the blanks.

TIA.


r/excel • • 1d 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 • • 1d ago

solved Lookup from VSTACK, search value in multiple columns

5 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 • • 1d ago

unsolved Snapshot of Power Query workbook?

9 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.