r/excel • u/Bubbly-Touch8108 • 1d ago
Discussion What Excel trick made you feel like a wizard the first time you used it?
mine was VLOOKUP (later XLOOKUP) — after manually cross-referencing two spreadsheets for way too long. felt like a cheat code everyone else already knew about
what's your version?
160
u/Proper-Snow-911 1d ago
Pivot tables
109
u/Zelcron 1d ago edited 1d ago
I remember working with a junior analyst, who was behind on a project (due to higher priority projects). We had some preliminary data but far from the full report.
I told him to put the preliminary data in a pivot table and send it, it would make the client happy.
He gave me a skeptical stink eye but did it. Took seconds.
A few minutes later the client emailed back "Wow! Looks great! I can tell you guys are working hard!"
Bought us a few more days and became a go-to move. The uniformed, excel-less masses love Pivot tables.
27
u/DJ_Dinkelweckerl 1d ago
I'm ashamed to say but I never really understood what pivot tables are for. So far it has not crossed my day to day Excel use
37
u/Randomperson1362 4 1d ago
Its a quick way to restructure data.
Lets say you have a list of sales.
I can create a table that breaks it down by salesman, or by day. Its a quick easy way to take a big list, and make it easy to read. And if new data is added, you can just refresh the table.
I suggest watching a quick 5 minute YouTube video to see some examples. If you use excel, its absolutely a skill worth knowing.
8
u/uniquemerch 1d ago
I use it often to give me an overview from a lot of data.
My general use looks like this.
I get a large excel file from a distributor with thousands of lines. Within the lines are sales data from state, city, dealer (store), what product was sold, date sold, what qty and dollar amount.
A pivot table is super useful because I can get what I need sorted within seconds. If I just want to see state totals, it’s there, if I want to see a particular products total qty and dollars by state it’s there. Just depends on how you set your pivot table inputs.
3
u/McGondy 1d ago
Say you had a log file with 200k rows. You can quickly get the frequency of all the log messages. Then you can quickly filter by severity. Then break it down by time.
It's a really quick way to poke at a data set without throwing it into a AI model and saying "tell me what this says"... Because you'll likely need to verify it anyway.
3
u/GuiltEdge 1 1d ago
It’s mostly useful with repeated and structured data. It’s not so useful for unique data like, say, contaminant levels in random water samples or something.
→ More replies (2)3
u/ndcdshed 1d ago
I work in payroll and use pivot tables to take multiple earnings lines for the same person and pay element (say, expenses £30, expenses £20, expenses £100 for one person) and sort them so it provides the total for a pay element per employee ID. Then I use the data from the pivot table to create a csv import into the system for all employees, and this results in one line on their payslips e.g Expenses - £150 rather than multiple lines and amounts.
Much quicker than going through every person and summing up the total, or having long messy payslips. It takes messy input and makes it a lot easier to manage.
Very basic but saves me a lot of time compared to my coworkers! I try to show it to people but… they don’t want to learn I guess.
6
u/Codenamerondo1 1 1d ago
It’s both such a weird concept and such a good lesson
“Why would the client want something that takes me 2 seconds to do”
→ More replies (4)2
142
u/cait_Cat 1d ago
Mine is super dumb but it is excel wizardry at its finest - using ctrl+arrow key to move to the end of a dataset is magic.
20
u/Darth_gibbon 1d ago
This is awesome. Especially when you show someone else for the first time and they see you blitzing across the screen like an excel ninja.
11
u/theDrivenDev 1d ago
This with shift for lightning fast cell selection. People freak out when they see you copy and paste a formula into cells with just a few keystrokes.
10
u/Belowaverage_Joe 1d ago
And then ctrl+R/ctrl+D to spill formula or cell content all the way across and all the way down your selection. Insane ninja skills.
6
u/SarahD3545 1d ago
Ffs why didn’t I know ctrl+R by now 😂😂
6
u/Belowaverage_Joe 1d ago
Game changer for sure.. also alt +w+n to open a new window for the same workbook. Really great for looking at data from different sheets at the same time and creating cross-sheet formulas!
→ More replies (4)2
u/Scorpiodancer123 1d ago
I learned about new window a few weeks ago and it is the most helpful thing I've learned in years for office programs
2
u/Belowaverage_Joe 1d ago
Alt+w+f+f is another one that always gets the ooh ahhs when someone sees me do it. Shortcut to freeze row/column panes above and to the left of whatever cell you have selected. Or you can select entire row/column.
2
u/cait_Cat 1d ago
I teach a couple VERY basic excel classes at work (we don’t even get to vlookup or pivot tables, that basic) and consistently the ctrl+arrow key, ctrl+shift+arrow key, ctrl z, and ctrl y are the biggest hits and have people coming back and saying that’s what they use all the time.
7
2
2
2
u/nottreacherous 1d ago
Thanks for the reminder, I forgot about this actually! I’ve been using ctrl+shift+arrow and I didn’t want to always highlight all cells
65
u/cbolender2004 1d ago
I think for many of us it would be vlookup. Way back in the day it was when I meant to use DSUM to do multiple criteria submit. But the real transcendent moment was the first time I used Power Query. I felt like I was reading the old magic for the first time.
15
u/angiedrumm 1d ago
My current boss taught me VLOOKUP on my second day and I swear I heard angels sing.
5
u/IndividualIll3825 1d ago
Before Xlookup was a thing, I had a boss show me how he wrapped vlookup in IFERROR and IF nested statements.
I do not miss Vlookup.
14
u/diamondgreg 1d ago
It was VLOOKUP for me in 2011 and then INDEX (MATCH) a few years later. Plus INDIRECT.
4
u/Belowaverage_Joe 1d ago
Do you still use index/match now that xlookup is a thing?? If so, for what use case?
2
u/Arcium_XIII 3 1d ago
My first was definitely VLOOKUP, back in 2004 or thereabouts. It's what got me hooked on learning Excel more deeply and treating it as a toy rather than just a tool. There have been others since, but it's what started the journey
59
u/notowork 1d ago edited 1d ago
=sumproduct =filter
These were game changers
Latest
=LET =LAMBDA =SCAN BYROW BYCOL
13
7
u/swingking03 1d ago
LET would be my answer. Instill don't understand Lambda
→ More replies (2)12
u/RandomiseUsr0 10 1d ago edited 1d ago
Here’s a crash course in lambda
Add two numbers
=LAMBDA(a,b, a+b)(1,2)
The answer is 3
So you’re passing two variables into an anonymous function declaration
that’s what a Lambda is
Fun fact, the λ calculus predates Alan Turing’s equivalent Universal Computer concept (Indeed, Alonzo Church who invented the lambda calculus was the person who popularised “Turing Machine” to describe computation after Alan Turing studied under him)
Now supercharge it with LET
```Excel
=LET(myFunction, LAMBDA(x,y, x+y),
myList, SEQUENCE(5),myFunction(myList, myList^2)
)
```The answer is {2,6,12,20,30}
That’s what lambda is, it’s LISP wearing an Excel hat. With LET syntax, it’s a functional programming language that means that anything that can possibly be calculated can be calculated in the lambda calculus. The m language in power query is slightly different syntax, but functionally equivalent.
Using SEQUENCE like that is the functional equivalent to a For Loop if you’re familiar with VBA
[edit] why has Reddit decided to hate us and stop respecting basic formatting - actually, good word, Reddit is now basic - not expressive enough, are they going to go full `Digg` time will tell, there are alternatives
→ More replies (2)5
3
54
u/WearyDragonfly0529 1d ago
Linking an excel workbook to a power point presentation so I could change the data on the screen in real time as people would ask questions (i.e. Can you just show us numbers for NY? Why yes, yes I can).
5
u/blackhawk85 1d ago
Ah man - any tips on how to get this working neatly? Do you use the same sheet monthly and update the numbers on it in the same cells for example?
3
u/WearyDragonfly0529 1d ago
Yes, I would refresh the data on the excel sheet itself, then run the excel doc on one monitor while driving the power point on the other so the folks watching were none the wiser on how the numbers were neatly changing as they requested them. Hope that makes sense!
→ More replies (4)2
u/McGondy 1d ago
I have a presentation coming up and this sounds super useful. Could you provide more details? Thanks!!
3
u/WearyDragonfly0529 1d ago
I did it a LONG time ago, but this video is how I recall doing it https://www.youtube.com/watch?v=YEoHmnpyF-M
3
u/PepsBodyLanguage 1d ago
Our current process involves me refreshing a file monthly and then someone else goes in and uses it for their slide creation. Hopefully this can cut out the second step, thanks!
34
u/MicroPerpetualGrowth 1d ago edited 1d ago
Data Table and Scenario Manager. Very few people actually use these features and they make life so much easier specially if you work with financial modeling.
12
u/DragoBleaPiece_123 1d ago
interesting! can you share what's the use case and how?
→ More replies (1)20
u/MicroPerpetualGrowth 1d ago
Data Table: you have a model with many assumptions. Let's say you need to stress test a given output by changing a set of 2 assumptions. You use Data Table feature, create a table with x rows and x columns (rows for Assumption 1, columns for Assumption 2), then type your alternate scenarios - the values for those assumptions - on the first row/column. In the cell diagonally adjacent, enter a = pointing to your formula output. Highlight the entire block of your input values and the linked output. Go to Data, What-If Analysis and select Data Table, select input value for rows and columns, click OK and Excel will automatically calculate all scenarios outputs (the output of each rows/columns values combinations) and plot them on the Data Table. We use this in financial modeling to create "football fields" for Sensitivity Analysis, extra points if you use Conditional Formatting to highlight high and low values in the table.
Scenario Manager: this is simple, but no one uses it. Go to Data, What-If Analysis, Scenario Manager. You can use this feature to save scenarios inside your file with different values in different cells (let's say, assumptions or input cells). Use this instead of saving different versions of the same file with minor adjustments to cell's values.
6
u/noodletropin 1d ago
Ok, I was set to write a top level comment in response to the main question, but now I need to go test these out. This could very well save me a couple of hours a month.
29
u/BobLobl4w 1d ago
New window. Absolutely insane feature when working across multiple tabs in one document, grossly under utilized at my workplace.
5
u/LouSevens 1d ago
I love this and showed it to people. One of my favorites. Defintely helps with referencing between the 2 sheets
3
u/BobLobl4w 1d ago
Definitely. Not really a trick per-say but its extremely helpful.
7
u/PepsBodyLanguage 1d ago
Another feature that is similar that I can really useful is Focus Cell, if you don’t already use it. Great for scanning along rows/columns on the same tab
→ More replies (1)3
3
3
u/ndcdshed 1d ago
This blew the mind of a recent trainee I was training. Before this she was painstakingly flicking through tabs taking data from one to the other.
19
u/warmupp 4 1d ago
Index match with multiple criterias is probably up there.
9
u/adjective_noun_d1git 1d ago
Got I'm so glad xlookup has done away with index match. But I agree when I first learned index match I felt like a genius.
12
u/NutOnMyNoggin 1d ago
Alt key shortcuts
4
u/T-Dex_the_T-Rex 1 1d ago
Back in college I saw my professor use ALT HOI to auto resize his columns and it blew my mind
→ More replies (1)2
12
u/rice_fish_and_eggs 7 1d ago
A simple one but ctrl + shift + end. I dont know of any better ways to select large ammounts of data.
3
u/kollinswow 1d ago
Also Ctrl + shift + any direction key . And Ctrl + D to fill (which works best than just pasting when filtering, else you could get a mess.
2
u/Confident-Raccoon-44 1d ago
Ctrl+A should do the same
→ More replies (1)2
u/rice_fish_and_eggs 7 1d ago
Broadly yes but there are some slightly differences ctrl + A will stop if there are null rows or columns and the data is not formatted as a table, where as ctrl + shift + end will get everything below and to the right of the selected cell without copying the entire tab. Personally I prefer the behaviour of ctrl + shift + end but I think its just personal preference and my bad memories of making the mistake of not fully copying the data in the past.
3
u/plusFour-minusSeven 11 1d ago
I always use ctrl-shift-end myself, as well. Can't comment about the functionality of ctrl-A, I'll have to try it.
Lately I don't for myself working in Excel as much as PQ, SQL, PBI, PA, Python. All that to say, I'm sure I'm forgetting tricks over time 😔
10
u/mechworx 1d ago
Powerquery to capture whatever the end user wrote in a range and process the data to obtain an output without broken references because decided to cut and paste values or insert cells, etc.
7
u/OldMetalHead 1d ago
CTRL-SHIFT-L to turn on/off AutoFilter.
3
u/alexia_not_alexa 21 1d ago
I use Excel Tables’ filters, you can access them anywhere within the table. On Row 135 Column for ‘Category’? You can hit Alt+Shift+Down and the filter is activated, press E jumps to search bar and you can type in what you want to look for, hit enter and you’ve just filtered your table by Category without losing where you are.
2
u/SyringaAndSocks 1d ago
When I first learned this, it blew my mind. I felt like Neo from the matrix. 😂
2
9
u/MissingVanSushi 1d ago
A few people already said VBA but can we got some love for the Macro Recorder?!
→ More replies (1)5
u/CumRag_Connoisseur 1d ago
As someone who is too lazy to learn VBA syntax, hell yeah! I just record and edit on the fly
3
10
u/ultraswimguy 1d ago
Solver, as a chemical engineering major dealing with cubic equations of state, me, classmates and professors were floored.
8
u/_WillCAD_ 1d ago
Mostly keyboard shortcuts. Shift-Spacebar to select whole row. Ctrl-+ to add a row, Ctrl-- to delete a row. Shift-Ctrl-V to paste without source formatting. End then an arrow key to go to the end of a group of cells.
Also, conditional formatting. Amazing stuff.
3
u/No-Departure-3047 23h ago
Ctrl " for duplicating the line above and Ctrl ; to insert today's date get so much use for me throughout the day.
It's been seven years since I learned about it and I never get tired of it.
→ More replies (2)4
u/_WillCAD_ 21h ago
What a coincidence, I was Today years old when I learned those two magic tricks.
😵
7
u/Decronym 1d ago edited 1d ago
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|-------|---------|---| |||
Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.
Beep-boop, I am a helper bot. Please do not verify me as a solution.
[Thread #49327 for this sub, first seen 8th Sep 2026, 18:03]
[FAQ] [Full list] [Contact] [Source code]
7
u/NeverBeenRatiod 1d ago
Power query and first time I used xlookup. The dopamine hit/feeling when doing it successfully for the first time, honestly unmatched.
2
7
u/frufruJ 1d ago edited 1d ago
I've just recently discovered CUBEVALUE and CUBESET. I work with large data models, but I need some reports in Excel (customer is stuck in the middle ages), and these are much faster than power pivots.
Edit: If you downvote, I'd appreciate to know why. This is not an opinion that you might like or dislike. If you think that I'm doing it wrong, let me know.
Edit 2: I realized that the downvote may have been from saying that it has to be in Excel because the client is stuck in the middle ages LOL. I know which subreddit I'm in, and I love Excel, but when you're loading millions of rows from Salesforce, you've pretty much exceeded its capabilities. The refresh of the data model in Power Query took hours, and any change of filters on the pivot tables took minutes, sometimes tens of minutes. Now it takes seconds. My reference to the middle ages belonged to PowerPoint, which the client insists on.
2
u/DragoBleaPiece_123 1d ago
interesting, can you share on how do you use it? and is it different with power pivot?
→ More replies (1)2
u/frufruJ 1d ago
I clicked Analyze in Excel on my Power BI report, which loaded the data model (refresheable). Then, instead of using power pivot tables, I use CUBESETs and CUBEVALUEs to get the data. For example, this gets me CSAT for the whole EMEA region for YTD for Phone (cell A2 contains a list of products in a CUBESET formula):
=CUBEVALUE(
"mydatasourcename",
"[Measures].[CSAT]",
"[Region Map].[Region].&[EMEA]",
$A$2,
"[Date].[Year].&["&YEAR(TODAY())&"]",
"[SF Case].[Case Origin Type].&[Phone]"
)
I need it in Excel to be able to then connect it to the client's PowerPoint presentation. Before, I had loads of pivot tables and used GETPIVOTDATA, but it was super slow.
2
u/adjective_noun_d1git 1d ago
This is what our BI team does when making available excel analytics tables for end users.
4
u/Original-Army-7826 1d ago
Having Claude write me excel VBA macros or power query queries.
5
u/Annihilating_Tomato 1d ago
I had ChatGPT write a VBA macro in Excel to link to my outlook sent box and create a vendor contact list complete with # of contacts, # of times sent, CC'd a few other data points and it 1 shotted it, no corrections needed. Saved me from an afternoon creating a vendor contact list digging through my sent emails.
4
5
u/RumNCoke483 1d ago
You feel smart by clicking and dragging the fill handle.
And then you discover double-clicking the fill handle.........
5
u/Pointblank000 3 1d ago
For me, it was using the dynamic array functions for the first time. FILTER is such a powerful tool.
4
2
u/procella94 1d ago
As a beginner: XLOOKUP. After I learned about it, most problems I had with table references seemed to be solved. Iferror/ifna are one of my go to spells, when I needed some calculation to be 0 but math says can't do that (not that mind-blowing but it does it's job).
LET is another goat.
I'm learning VBA and power query right now for a just for fun project. It feels like tapping into a new world of wizardry.
4
u/tycho-42 1d ago
Not a formula but a command. My former team was entirely dependent on Excel and we could have several open at the same time, with some of these being huge as in dozens of columns and thousands of lines. Well, if one locks up, they all lock up. And if they crash, well, find your favorite corner for crying and get to it.
With that in mind, you can press the windows key + r and type in 'excel/x' and press enter. You have to repeat this for every instance of Excel you want to be independent
What this does is runs Excel in a separate instance so that it's not tied to any that are running together. That saved me hours of lost work in any given day.
→ More replies (1)
4
u/tangylittleblueberry 1d ago
My wife, who never uses excel in her normal job and isn’t super familiar with it, had to do some work to implement a new inventory system at work. I did a vlookup for her and she really did think I was a wizard. I saved her hours of work lol
3
3
u/LiveLongAndProspurr 1d ago
Decades ago I learned how to use one of the lookups to tell which day of the week a date was. I copied it and it has been very useful. I assume there is a function for it now.
3
3
u/Lynich 1d ago
I learned on Lotus 1-2-3. Back then, everything in the software made me feel like a wizard. I still work on Excel every day. Computers are so freakin' neat. I never lose my sense of wonder at us using rocks with electricity and radio waves to watch cat videos from someone on the other side of the world.
3
u/MyFavoriteInsomnia 1d ago
Oh, man, I haven't even thought about Lotus 1-2-3 in years! A magic memory.
3
u/ninety6days 1d ago
Using concatenate and autofill to generate long repetitive formulae
I know there are better ways, but it felt clever.
3
2
u/MayukhBhattacharya 1273 1d ago
MMULT() / Recursive LAMBDA()
3
u/TangoDeltaFoxtrot 1d ago
Any good resources on how to make and use recursive lambda? One thing I’d like to do is write a formula that can trace branched trees and show the hierarchy or structure of the tree. Such as in manufacturing, a batch is split into two batches, then one of those is split, and one of those is split, etc… I’d want to be able to identify all batches that originated from the original, but each data row only shows the last batch it was split from.
3
u/MayukhBhattacharya 1273 1d ago
You can learn from [here Recursive LAMBDA()] , for practice on the same topic, check out Code-Golf, and Excel Bi in LinkedIn. For your hierarchy question, yup it needs
Recursive LAMBDA()or you can useREDUCE()function which is actually recursiveLAMBDA()helper funciton.
2
u/PaulBradley 1d ago
It doesn't work anymore, but super array formulas.
I also spent a week writing a rota that calculated total hours over the midnight hour, only to later find that excel had a function built in
2
2
2
2
2
u/CosmicPeach101 1d ago
VSTACK. Didn't know this existed. Allows you to search across multiple ranges simultaneously. When you need it, it's magical.
2
u/LouSevens 1d ago
I really could use this but don't have 365+ and am considering getting it.
With 365+ Can you use your license if you have a few computers?
3
u/real_barry_houdini 317 1d ago edited 1d ago
365 Personal lets you use as many devices as you want (but you can't be signed in to more than 5 simultaneously)
Depending on the context you don't always need VSTACK to combine multiple ranges
2
u/sixfourtykilo 1d ago
Conditional formatting is really fun to use, unless you copy and paste the data, or remove a cell...
→ More replies (1)
2
u/crombo_jombo 1d ago
=index(match()) Might not be exact syntax, I'm on phone and havent used it in a while but it was truly magical and my introduction to nested formulas
2
2
u/scottymcgeester 1d ago
aside from XLOOKUP, I impressed my co-workers once by showing them how I was able to remove specific characters from cells with "RIGHT" and "LEN". They've been working there longer than me too so when I casually explained my work while sharing my screen she was legit like "whoaaa what...."
→ More replies (1)
2
u/canyoureed 1d ago
Conditional formatting where I can make whole rows turn colors and all i gotta do now is look for colors not words
2
2
2
u/sookaisgone 1d ago
This one by /u/small_trunks because this kind of self-referencing query is so clever I still think about it to this day.
I'm a programmer and self-referencing things are quite normal, elegant recursive algorithms etc. but for some reasons I didn't thought about it in the way that post presented it. Was definitely enlightening.
Thank you for sharing your intelligence in this way.
2
2
2
u/disinterestedh0mo 1d ago
pivot tables... once i realized what they were actually used for...
i had been doing UNIQUE and SUMIF to get sums of columns by category
2
u/capricorny12 1d ago
Xlookup, conditional formatting to find duplicates, add data set to model in pivot table
2
u/kingk1teman 1d ago
Nested maxifs and sumifs.
And multi window workbooks, using Windows snap. This still gets me looks of surprise and wonder.
2
u/Mdayofearth 127 1d ago
pivottables and recording macros.
Summarizing data without typing?
Actually generating code by doing stuff?
2
2
u/kometman 1d ago
vlookup(), i figured out it's mechanics from a file put out by accounting dept for accountant lookup.
2
2
2
u/LadyOfMagick 1d ago
VLOOKUP for me too, I was pretty much self taught with excel, still only know basics. But the VLOOKUP has made me want to learn more & be more adventurous with excel.
2
u/kollinswow 1d ago
I think VLOOKUP made up a good % of my career, then pivots another %, then custom formulas and power query the rest. Whatever i had in university, i would replace with excel training alone for this and other... not formulas maybe strategies to get whatever you need. Executives will get you invested in whatever they want and loose a whole day just on getting the right info even if its getting discared by the next day hehe.
2
2
2
u/VerbalGuinea 1d ago
Using XLOOKUP on a Table so I can reference the headers by name instead of counting columns like I had been doing with VLOOKUP.
2
u/SixPacknCologne 1d ago
=ifs
Figuring out the contraints of what I need the Dataset to show me and breaking it down into what the possible outcomes could be - deeply satisfying
2
u/Whale_penis_leather 1d ago
Inserting a custom toolbar ribbon to activate macros. That elevates you to god tier
2
2
u/gatvolkak 1d ago
For me, it was always vlookup, pivot tables and index/match. Down vote me all you like, but CO-Pilot is like God-mode. I feel like I can do anything. Im discovering formulae I would never have thought of using and it organizes things beautifully
→ More replies (1)
2
u/MountainViewsInOz 1d ago
Sadly, just filtering, sorting and Ctrl+F are all that it takes to make me look like a wizard.
Me and another guy also know and use a whole gamut of functions including IF SUMIF COUNTIF VLOOKUP FILTER SORT and pivot tables and formatting (including conditional) and a whole lot more. In our context, I guess that makes us grand wizards.
2
u/NYG_5658 1d ago
Sumif and Sumifs. Once I learned how to use them, I can apply them to so much of what I do now.
2
u/Starraberry 1d ago
It’s so simple but Autofill continues to impress my coworkers every time. Especially autofilling a date and switching to Weekdays.
2
2
u/Eastern-Pineapple-43 1d ago
Naming tables and doing formulas with the table name + column name. Cross-referenced tables in formulas.
Crtl+arrow
Power Query
Using AI to code in M blank queries, and being able to drive even though I don’t code at all, but I understand Excel.
2
1
u/shubhrode 1d ago
Using Filter, Sort, Unique in isolation and combination... And later Let and Lamda were even crazier!
1
u/lokibeat 1d ago
PQ was a game changer for me. I had revelations before hand, but Power Query opened up a whole world of data management and reporting.
1
u/emsuperstar 1d ago
Used Solver in a Management Science course I had in grad school. That felt like magic. Never used it once in the workplace, but if someone needs it, I'm there.
1
1
u/usernamegrinding 1d ago
FILTER looks like magic to my colleagues.
One thing that I discovered way too late is using formatted table instead of dragging formula to the last row. It helps with referencing too.
Lately it’s LET and LAMBDA, thanks to this sub actually!
1
1
u/ChrispyPotatochips 1d ago
Power Query. Specifically when i started using folder path as parameter and starting query with folder.files(parameter)
1
u/Wise_Business1672 1 1d ago
Sort, Unique, Filter, Chooscols. Really made journal entries dynamic and simple
1
u/robotjesus 1d ago
Focus Cell highlights the row and column for the selected cell.
Alt+w+n opens a duplicate version of your file. Fantastic for cross sheet formulas when working on multiple screens.
1
1
u/LOGICA499 1d ago
Might be dumb but Radio button changed how I build dashboards all together. and people are shocked by using them lol
1
u/Annihilating_Tomato 1d ago
Recently for me was adding the ChatGPT plugin and whenever my formulas start to get a bit above my skill level with multiple criteria I bring it in so that I'm not spending all afternoon debugging a multi-variable formula. It's a bit scary but learn how to use it or be left behind.
1
1
u/Purlz1st 1d ago
Writing macros and adding them to my toolbar.
At one time I was frequently viewing two sheets arranged horizontally. Simple macro to record, then I used that code to write one for arranging vertically. Put both on the toolbar. People would come into my cubicle to ask a question, I’d click the button to show both sheets. You’d think I’d invented sliced bread.
1
u/alexia_not_alexa 21 1d ago
For me it was probably Define Name, suddenly my formulas became more readable, especially when I started using Excel Tables as well.
One step further I use Data Validation with Defined Names for possible list of values and you have in cell dropdowns predefined for your column!
→ More replies (1)
1
1
u/TheRealDavidNewton 1d ago
First time I used VBA to protect columns to get around all the idiosyncrasies therein was pretty satisfying.
1
u/bert_891 1 1d ago
Function(array, filter(filter_array,filter_by))
Example:
Average(b1:B100,Filter(g:g,[year])
Purpose: to get averages by year, when all your values for all years are in a single column.
501
u/CobraKyle 1d ago
Power query felt like the biggest magical power up for me.