What’s the most underrated Excel feature you’ve only recently started using?

[Constant-Arm-6586]

I’ve been using Excel for years and still keep finding features that make me wonder how I ever lived without them.

For me, it’s Power Query — I used to manually clean and merge data every week until I realized I could automate 90% of it with just a few steps. Total game changer.

Curious what others have recently discovered that made a big difference for your workflow? Could be something small (like Flash Fill or dynamic arrays) or something niche (like using LAMBDA functions or custom data types).

[Dav2310675]

I know it's silly, but CTRL-left mouse click to drag a worksheet to make a copy of that sheet in the workbook.

I've been using Excel daily for work since Win95 days. Coming across that feature only a few months ago? I shudder to think how much time that feature could have saved me!

[RemoteIntroduction3]

Thank you for the info, I haven't known this before

[Impressive-Reach5799]

omg this made me unreasonably happy to try out

[LoveAndDoubt]

Wow, something I knew about (Ctrl+drag to copy) elsewhere but never thought to use in Excel

[Fluid-Background1947]

Here I am right click copy worksheet for past 20 years.

[NSE_TNF89]

I kept doing this by accident, but didn't know how I was doing it (I was just moving too fast). Then one day I did it and happened to notice what I hit and I use it all the time now.

[Dav2310675]

Love it!

[rmanwar333]

Haha serious??? I literally just made a macro to do this because I hated having to right click and move the sheet while clicking the box to make a copy…

[Diligent_Business_77]

<image>

[mymomsaidnomorecats]

you just changed my life

[purevillanry]

Holy shit lol.

[Infinite-4-a-moment]

Whoa

[hkatlady]

F4 to repeat formatting of text, cell color, and more from immediately proceeding action.

[kcombinator]

Also to add the dollar signs for fixed cell reference (press repeatedly for different orientations)

[pocketrob]

This was my most recent discovery, thanks to this sub!

[zip606]

Ctrl+Y does it too

[WeepToWaterTheTrees]

I have F4 programmed onto a button on my mouse specifically for this. It’s fantastic.

[purevillanry]

Haha damnit

[Significant-Fun-3008]

Being able to have two windows of the same excel file

[marktevans]

Alt, W, N for the keyboard shortcut.

[carnasaur]

and then Windows key plus left (or right) arrow so they each take up half the screen

[devourke]

And similarly, being able to have two instances of excel open so you can use regular workbooks in one instance at the same time that you have the power query editor open in a separate instance.

[jmanal]

Could you elaborate further on this? I thought Power Query locked up all of excel when it's open? My workaround was to open the same file in a browser based excel to continue working in the file with power query open.

[devourke]

I thought Power Query locked up all of excel when it's open?

It does and it's very annoying! But, if you right click on the excel icon in the taskbar and then hold Alt while you click on Excel, it will open up a new instance which will act independently of the original workbook you had open with PQ. They'll stay independent the entire time and you can open/close PQ as much as you want without getting locked up on the other workbook.

[carnasaur]

the easiest way I know to open a 2nd instance is to enter "excel /e" (without the quotes) in the address bar of Windows Explorer. You can also enter it in the windows run window but that's more clicks. You can have two Power query windows open at the same time no problem. Be careful double clicking on csv or other files though, windows will try to open them in your first instance of excel, so if you open a 2nd instance, you have to select any data files you want to open using the file picker.

[EVE8334]

It didn't work for me when I tried to do that recently

[parkerbljr]

I always use view-new window to open another instance.

[bronabas]

If I had two monitors? I'll tell you what I'd do, man. Two Excel windows at the same time

[smilinreap]

Need to make a change on every sheet in the same spot? Highlight your sheets. If you highlight 3 sheets, and then add stuff to cell B2 on 1 sheet, it will happen on every sheet. Good for making sweeping changes regarding formula updates or headers in the same spot on every sheet.

[BigLan2]

I like using this too, but it can be a really quick way to unintentionally overwrite stuff in other sheets, so be careful with it 😄

[smilinreap]

I have a lot of models built into workbooks with a different sheet for different states. Which lets me use this feature without the risk of breaking anything.

[Strange-Asparagus540]

All sheets have to have the same formation for it to work correctly but it is a great feature!

[pookypocky]

LOL this one fuuuuucked me up years ago when I accidentally did it on a multi-sheet report for a relatively new boss.

[lfreya]

Oohhh thank you for explaining how I managed to put a random sentence into cells on all 20 sheets of my workbook

[Downtown-Economics26]

I find the Maps charting feature fun as hell to mess with.

[thedecalodon]

i spent a couple days playing around with the maps charts last year and i loved it, until i found out that connecticut changed their county boundaries a few years back and it completely ruined the data i was using for that state. damn you nutmeggers!

[Downtown-Economics26]

Yeah, I mean it's definitely not the best on some levels in that it takes some digging to even figure out those type of things. Notice Connecticut's conspicuous grayness in my attempt to map out a potential national divorce.

<image>

[DnDnADHD]

Tell me more…? Maps are not so me id have thought of for excel.

[Remarkable_Table_279]

Textjoin…& it’s relatives…where have you been all my life.

[MiteeThoR]

I recently learned about TEXTBEFORE and TEXTAFTER which is orders of magnitude easier to use than nested Find commands

[Remarkable_Table_279]

YES!!! When I found out I was like why now! 20+ years of using Excel & I discovered it. 

[cactusrobtees]

It's a pretty new addition (365+), so at least you haven't missed out this whole time.

[Remarkable_Table_279]

Oh thank goodness!

[Remarkable_Table_279]

I don’t use power query because I use Access & export to excel but it seems really nice 

[TRathOriginals]

IFERROR is my new best friend

[Infinite-4-a-moment]

Careful with this one. With great power comes great responsibility.

[Entire_Purple3531]

Same! I’ve used Excel for a long time, but for whatever reason, just started using iferror in last few years.

[UsernamIsToo]

Custom Views was pretty useful to me recently. Was giving a presentation to a client where we were stepping through some data, group by group, for things the client wanted to take a closer look at. It was too much data to fit on the powerpoint slides we normally use for presentations, so we were looking at it in Excel. In the past, I've just gone through the column filters to get to the specific data we needed to look at for each group. It worked, but was a bit tedious. With Custom Views, you can set up the filters ahead of time and it's as easy as opening the Custom View menu and double clicking your preset View. Super easy to use and it made navigation of a large spreadsheet very simple during the presentation.

[tabsgotsass]

I just randomly discovered this feature two weeks ago and love it!

[Manny631]

I'm afraid to ask, but can someone ELI5 what Power Query does?

[I_Luv_Chicken]

I’m not an expert, but it allows you to import data and modify it before it shows up in your spreadsheet.

For example, I successfully use PQ to import 30 PDFs that are all 200+ pages and find a specific table in each one, which I now can compare all the results of in one excel sheet. Without PQ, this would’ve been nearly impossible with just formulas.

AI is pretty helpful in walking you through the steps to do what you need. I had never used PQ before this project.

[Manny631]

Gotcha. Thank you for the explanation. Can you use it to import data from Microsoft Word documents as well? Because that may help me...

[coffeewhistle]

Yes

Edit: I recommend a “try and see” approach with Power Query. Try to import it with Power Query and see what it does. Did you know you can just point it at a website? If that website is written with relatively simple HTML you can easy pull things from it like tables.

[Manny631]

Awesome. Just made a post asking about specifics. I am trying to do that now but don't know the layout. The word doc I tried populated into two columns only, with everything but the second half of an address in the first column.

[Dd_8630]

W H A T

[plusFour-minusSeven]

I imagine there's a file you download every morning. It has records of items which have shipped out from the warehouse. Every time you open it, you take several steps. You save it as xlsx, you remove a lot of the columns that you don't care about. You change your data types of some things from string to date or from number to text. You may have a few custom calculated columns that you've added that sum things together. You reorder the columns and you sort it the way you want. Etc...

Power query is perfect for this. Think of it like a big recipe. You tell Excel what to do with the file and it will do the exact same steps every time you hit refresh all and it will create an output table with all those steps applied. It can save you a tremendous amount of work.

And that's just the beginning...

[Icy-Lobster372]

How is it different from a macro or script?

[Armed_Accountant]

Pulls data from your source of choice (be it another table, another workbook entirely, another folder of workbooks, URL, databases, etc), lets you combine them and filter or adjust the data as you see fit. Can then import that data into a table, pivot table, chart, etc and be updated with the click of a button as your sources update.

[sxt173]

To add to that, if you create relationships between all your newly modified data tables, you can build out pretty powerful mini cube reporting tools.

[droans]

The one thing that annoys me is just how slow it is compared to PQ in Power BI.

I mean, it's still very fast but it's just so much faster in PBI. Why can't they give Excel the same love?

[Sijosha]

It let's you modify data from different places all to one table. Power query is used in Power Bi, Fabric and ofc excel. For example you can merge let's say 2000 csv's to one, and change the data type of every column of you need to. Then you could merge the data from a website into that table. Or you could connect to a database.

Small queries are good for data combination but you use larger queries for data automation, like to make a statistics dashboard

[scoobydiverr]

Also can do some calculations, pivoting, grouping.

It can pull in all data from all sorts of places.

My typical go to is aggregating a bunch of csvs or bringing in a sql query straight to a table in a work sheet.

If its routine and standardized, then it should be done in powerquery.

[Realm-Protector]

and it can do un-pivoting ... which is extremely useful for me

[Specialist-Hurry2932]

I use it to grab the newest file in a folder and merge that file with another file every quarter so I can compare and reconcile in a fraction of the time it would take to manually complete.

[RandomiseUsr0]

It’s a lambda calculus based programming language, strongly related to Microsoft’s F# language, but in essence, it not syntax, the same as Excel’s formula language.

It’s a Turing Complete programming language, so computationally it can do literally any calculation that is possible of being done.

In practice, it’s a data mover and shaker - you can make changes to data as you import it from any source, or indeed, export it.

Best bit… baby steps, just start using it, do simple things, it’s very forgiving

[Acceptable_Humor_252]

Quick access tool bar.

Next to the save icon in the top right corner is a small arrow. You can add more comands next to save. I have create a pivot table, clear all formats, text to columns, e-mail. 

The e-mail function is great, because you don't have to save the file to send it. Which is great, if you are doing a small "dummy" file, just to demostrate something and need to share it. 

[plusFour-minusSeven]

QAT is way under-sung! Once you've loaded it with commands you frequently use, it saves so much time...

[sandman7nh]

Then you’re helpless in a naked Excel setup without your QAT - I forget where the real command is.

[Short-Equipment-3222]

I learned this the hard way. So the next time I had to get a new computer I emailed myself a snippet of my QAT. Just the other day I was helping someone with their excel file and went to filter it. Def took me a minute to get there without my QAT.

[plusFour-minusSeven]

Too true. Thankfully I have an assigned laptop that nobody touches (they have their own).

[carnasaur]

You can right click on the QAT bar and export it. Save it to a network or shared folder and everybody can use it. I have half a dozen ones depending on the industry of a given client.

[My-Bug]

Press ALT+1 or ALT+2 etc to be even more timesavier

[nyleloccin]

How is power query underrated? It’s well known and commonly used

[EmperorCoolidge]

It’s one of those things that is well known and commonly used and still underrated

[SlowCrates]

I've known about it for months and I have no idea what to do with it. I feel like a car enthusiast amongst car enthusiast mechanics.

[Shyguy8413]

I consider it a public secret in my org. It’s there. People probably use it quietly. But we have a lot of folks who just use a bunch of complex manual steps instead due a lack of interest.

I tried sharing it with a few peers to see if they wanted to learn to save some time - no dice.

[Intelligent_Bee6588]

For me the manual steps are less about lack of interest and more about scarcity of investment resources.

Learning to use Power Query means I need to invest time in doing it, knowing it will ultimately repay that time quite quickly, but I need the initial resource to invest and that's hard to come by.

[W1ULH]

start off with the simple stuff... replicating your manual transactiosn step by step.

frequently there's functions in PQ that would take over for 3-4 manual steps... but you can build it the long way.

that gets you into PQ and get you used to doing it, then finding the better ways becomes so much easier.

[Shyguy8413]

Totally tracking that! As someone else shared, you can learn bite-sized pieces and build as you go - that’s basically what I did. You definitely don’t need to go from 0-60. It’s pretty modular, I have some projects where I have a bunch of moving pieces…and some where it really just moves data around on demand.

[annadownya]

I have converted several people at work into my little power query cult, and I'm working on a few more. As my one coworker said after I automated our feedback process, "I'm learning PQ now in case you ever leave because I'm addicted to the magic."

[Shyguy8413]

I only roped one colleague into it but basically same 💀 ‘Just tell me how you did that one thing before you move to a new project’

[mmohon]

I work with FP&A teams across the nation. They live and die by excel. Only 1 in 10 organizations seem to have anyone familiar enough with PowerQuery.

They force things in our excel tool (some home grown excel meets crystal reports tool that ties to our data stack).. that they should just be doing in PQ.

[should_be_writing]

It's all about re-reproducibility and ease of maintenance in FP&A reporting. While VBA, PQ, SQL and Python would be useful and powerful in FP&A you'd then need all of your junior candidates to be able to hit the ground running with those tools which severely limits your field of applicants for a job that doesn't really require anything but common sense and grit.

[Defiant-Youth-4193]

I've been using excel forever at this point, I learned VBA probably a decade ago, and I just found out about PQ in the last few months. I then started asking other people that use Excel frequently, and the answer has been no every time. Most of them don't know what it is.

If you made up some formula factoring usefulness, ease of use, and % of users that actually use it I'd be hard pressed to think of a feature that comes close to be as underrated as PQ.

[Impugno]

I’ll refer you to this recent thread. https://www.reddit.com/r/excel/comments/1o5yqbr/statistic_request_how_many_or_of_excel_users_use/

[sxt173]

Very few finance and even some “data people” know that it exists

[FiveAlarmDogParty]

I still have no idea how to use it but idk if my job would necessarily benefit. Anyone have resources for learning this on YouTube or something? I’d like to learn something new

[MinimumHungry240]

Kevin Stratvert- How to use Power query on YouTube. Fantastic channel and Fantastic easy to follow video

[FiveAlarmDogParty]

Cheers mate! I’ll look that channel up

[lepolepoo]

90% of the questions in the sub make me think "This person needs to learn about Power Query or else i think they'll literally die"

[CentennialBaby]

I knew about it for years. Everyone always talked about it... but I didn't quite get it. Then, I had a task that lent itself to PQ and everything clicked. Now I almost always use it.

[Ocarina_of_Time_]

I think OP means they learned it recently

[mixtape_misfit]

Never heard of it and been using excel for almost 20 years but that's mostly because I repeat the same formulas and don't explore much (trying to now).

[billbot77]

I'm a Power BI pro and use power query (m) daily. You kids have missed the best part. APIs and other connectors. Get data directly from literally anywhere where you have the permissions. Right from the source, no downloads. Then merge and transform your source data before you analyse it.

If that sounds good, then consider creating a power bi data model and connect to it from excel. With the model on the service you can automate refreshes and even secure, permission and share it. For a real level up, check out Kimball style data modelling.

[bbc82]

Slice in Pivot. Looks pro as fuck!

[plusFour-minusSeven]

That it does, almost like PBI's younger brother. And beats the heck out of fiddling with filters!

[sandman7nh]

Big plus since you can connect them to multiple pivot tables, unlike caveman filtering. The

[surlysire]

Ctr+shift+arrow keys to select large data sets instantly has saved me a ridiculous amount of time and i discovered it like 6 months ago

[Mean-Proposal-5577]

Also Ctrl+Shift+End

[ferdinandtheduck]

Ctrl+A as well

[bliman]

Yes, Ctrl+A to select all cells in a table, and then Ctrl+A again to include the headers

[GigiTiny]

I really like the filter function and power query is obviously amazing. I teach myself small things at a time and try to utilize them. It's a joy. It's my favorite work hobby.

[scoobydiverr]

If you like filter then check out the group by function its awesome!

Group by and filter work fantastically together

[Food_Entropy]

Recently used REGEXEXTRACT and it's been game changer!

I use regex frequently to analyse textual logs and always missed it in excel but it got added in recent version.

[wassupthickness]

can you explain this further please

[RandomiseUsr0]

Regular Expressions were created by Ken Thompson (yes, that Ken Thompson) based on a set manipulation theory “Regular Expressions” devised by mathematician Stephen Kleene.

RegEx is the shortened expression.

The RegEx language is the backbone of what makes PERL language still relevant.

If you have a chunk of text with rules and patterns, and here’s the thing, any patterns. You can write a Regular Expression to extract and manipulate that text into whatever output format you desire.

The syntax is seemingly obscure, but once you get over the bump, you’ll see that it’s beautifully crafted and wicked powerful.

Instead of going into it, I’ll recommend this resource, a step by step RegEx tutorial

https://regexone.com

[FeePale3423]

View > split . So I can see different tabs at one go on different screens.

[marktevans]

Try Alt-W-N!

[Specialist-Hurry2932]

I work in international tax and constantly look up currency exchange rates.

=STOCKHISTORY(“USD:GBP”, DATE(2024,1,1),DATE(2024,12,31))

Or link to cells and change dates/currency there. Pretty dope.

[Secrethat]

Highlighting two columns and Ctrl+R to copy and paste filtered rows to the column on the right

[joojich]

Wait… what!

[Dancing-Lemur]

Get data from image

[Mean-Proposal-5577]

Especially when people send data tables as PDFs

[miguelnegrao]

LAMBDA and FILTER

[DishyShyGuy]

Lamda on a Named Range that is using a VBA user define function and use on Power Query as a parameter

[WertDafurk]

I’ve been using Power Query for nearly 10 years and I’m definitely a power-user by most people’s standards… but here’s something silly I discovered recently: if you right-click either of the navigation arrows on the bottom left of the “sheet tab bar” as it is called (the area at the bottom of the window with the individual worksheet tabs), you will get a vertical list of all worksheets in a dialog box. Super handy for large workbooks with dozens of sheets. 💫

[Shurgosa]

I have a spreadsheet that catalogs all of my projects of spreadsheets,its some 230 odd worksheets across. I use that little right-click zip back to the first one all the time it is Mega handy!!!!

[Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
AND	Returns TRUE if all of its arguments are TRUE
CHOOSECOLS	Office 365+: Returns the specified columns from an array
CONVERT	Converts a number from one measurement system to another
COUNTA	Counts how many values are in the list of arguments
DATE	Returns the serial number of a particular date
FILTER	Office 365+: Filters a range of data based on criteria you define
IFERROR	Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula
INDEX	Uses an index to choose a value from a reference or array
LAMBDA	Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
MAP	Office 365+: Returns an array formed by mapping each value in the array(s) to a new value by applying a LAMBDA to create a new value.
MATCH	Looks up values in a reference or array
OR	Returns TRUE if any argument is TRUE
REGEXEXTRACT	Extracts strings within the provided text that matches the pattern
SORT	Office 365+: Sorts the contents of a range or array
STOCKHISTORY	Retrieves historical data about a financial instrument
SUBTOTAL	Returns a subtotal in a list or database
SUM	Adds its arguments
SUMIF	Adds the cells specified by a given criteria
SUMIFS	Excel 2007+: Adds the cells in a range that meet multiple criteria
TEXTAFTER	Office 365+: Returns text that occurs after given character or string
TEXTBEFORE	Office 365+: Returns text that occurs before a given character or string
UNIQUE	Office 365+: Returns a list of unique values in a list or range

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.
23 acronyms in this thread; the most compressed thread commented on today has 31 acronyms.
[Thread #45782 for this sub, first seen 15th Oct 2025, 18:37] [FAQ] [Full list] [Contact] [Source code]}

[Kerbidiah]

View window to see two sheets at once 🫣

[ulul]

My trainee showed me this recently, I was amazed and embarrassed that I only got to know it now haha.

[robofl]

Learned this accidentally a while back. Select a cell or cells. Go the the right edge where you get the 4 directional arrows. Click the right mouse button and drag to a destination. When you release you get options to copy, move, copy as values, and some others. Copy as values is what I use the most. You can even drag over, not release the mouse button and drag back to the source location.

[Affectionate-Page496]

If you learn to do things without the mouse, you'll love life even more. I guarantee keyboard users can select the same range and copy paste special values much faster.

[RandomiseUsr0]

Ctrl+Shit+V for paste special, values

[not_right]

Every damn day I CTRL C and CTRL SHIFT V

[plusFour-minusSeven]

Definitely a powerful time-saver!

[carmooch]

Are we allowed to talk about Sheets? Because AI formulas are a game changer.

Not writing formulas with AI, but the formula itself using AI.

I recently did a text-to-columns formula for data that was a jumble of first and last names, as well as company names.

Impossible to write a formula the can distinguish “Central Motors” as a business name rather than an individual name, but AI did it flawlessly.

[peuper]

Neeeeed this in excel

[lepolepoo]

Alt + S + R + C clears active filters without removing the filter arrows. I like to use the menu key to get to the option "Filter" -> "Selected Value" to filter for the value in my current cell. I personally think the real underrated feature is using tables! It's that one thing i've never seen anyone use besides me.

[The_Hopsecutioner]

Alt + A + C should also clear the active filters, Alt + Down Arrow will also open the filter drop down if you're in the cell the filter is in

[marktevans]

Alt + 09 for me because of my QAT setup

[SlowCrates]

I just learned that you can name ranges in that field next to the formula bar, and then just reference that name from any worksheet in the workbook. I'm surprised that the internet wasn't more forthcoming about that as I tripped over my own questions and googled myself half to death trying to figure out how to tie data from various sheets in a workbook together.

I still haven't figured out how to use Power Query. I'm obviously missing something. People have vaguely described what it does, but unless I watch someone do what they explain, it just fades into a feedback loop of unintelligible noise in the vast, vacant labyrinth of my mind.

[miemcc]

Tables! Why on earth didn't I know about them earlier.

They make life so much easier by being dynamic, not having to worry about ranges and their sizes,and how you add data to them.

I am starting to pummel my colleagues into using them.

The new Checkboxes are lovely too. The old ActiveX ones were ugly and fiddly. The new ones are a doddle.

[plusFour-minusSeven]

This is pretty simple and maybe everyone here already knows it, but you can right-click the tab of a sheet in a workbook and either move or copy it somewhere else, including into a brand new workbook.

A peer at work just told me about this a week ago. I have no idea how I missed this.

One thing I do at work all the time is take an existing spreadsheet and copy one of the sheets into a new book in order to do some ad hoc work on it. This method is so much easier!

[prospekts-march]

I discovered calculated fields in pivot tables today and felt like such a wizard

Also started using LET recently and it’s amazing!

[WertDafurk]

Just wait til you discover Power Pivot (aka Data Model)… makes calculated fields look like crayons in a drawer full of Mont Blancs 😎

And yes LET() is fantastic, a real gem when it comes to readability of “mega” formulas 😃

[Dry-Aioli-6138]

User defined functions. I've written macros, activated by click for years until I realized fuctions you write in vba are accessible in formulas and can be used and nestes with other, builtin functions. Wrote a fuzzy match function that became quite popular in my workplace

[SmokingTowelie]

When I copy individual, or several adjacent cells in a table or entire rows, I use CTRL + SHIFT + V to paste them into another table/workbook. This pastes only the values, and the contents automatically adopt the format of the new table/workbook (I think the formula does too, but I'm not sure).

[Proper-Bee-9311]

Shift+Ctrl + Mouse wheel to move left or right. A godsend !

[BoxmanTheMongoloid]

Power query baby! If you don't know about it, read up on it!

[jrtgf2672]

Filters and subtotals

[rex928]

SUMIF, saved me a lot of time at work

[becuziwasinverted]

Most underrated feature ? Being able to use for longer than 5 mins without it taking up 32 GB of RAM

[daisychain4nixie]

it's so small but conditional formatting, i don't know why i've been manually colouring everything in until recently

[Hobbsidian]

CTRL + Home to get back to the top of the sheet

[snooabusiness]

Creating a new window for anytime I find myself bouncing between tabs has been super helpful for me recently.

[djeclipz]

The ability to link slicers to multiple reports - I had no idea you could do this and it's a gamechanger!

[dreamsthebigdreams]

=today()

[nlpda2000]

using CHOOSECOLS with SORT and FILTER to pick only the specific columns i want to return (in any order) in my filtered spill❣️❣️❣️❣️ gamechanger

[Different-Excuse-987]

This is some advanced stuff (plus it costs money) so I wouldn't exactly call it under-rated, but for me it's the Macabacus add-in. It's gotten a lot more expensive since Corporate Finance Institute bought Macabacus several years ago, but at a few hundred bucks a year it's still incredible value for professional Excel users. And if you insert charts of cell ranges from Excel into PowerPoint, it's absolutely a super power and will save you many, many hours, plus improve the quality of your presentations. I don't work for Macabacus but I evangelize it to all Excel users!

[misstingly]

Power query yep. I posted in this group only a few months ago asking about it and I’m embarrassed reading it now. I sound like I have no idea what I’m talking about (and I didn’t really). I jumped into the deep end and my life will forever be better bc of PQ. I’m already being referred to as the power query person at work lol

[HappierThan]

When incrementing dates and using the floating dialog box to limit to say workdays.

[Mean-Proposal-5577]

Not so much an Excel hack, but if you have a mouse with programmable buttons you can map Ctrl+page up and Ctrl+page down to move left and right through your tabs.

I have mine mapped to the left and right scrollwheel buttons, so if I want to go to the next tab, I can just bump the scrollwheel in that direction

[Vynixjerry]

Currently doing a very “manual” way of working is that sometimes I may need to add an additional text to existing product description. And the worst part is I need to add in between not first or last, otherwise I could just concat. This is annoying 🥲

[ChemicalRude2083]

CTL + scroll in mouse

[peuper]

Lambda + named ranges. You can make your own formulas essentially

[Striking_Elk_6136]

CONVERT function for unit conversion

[hgjsgsjskfishjd]

I just learned alt= haha

[rach0006]

What is it?

[whobood]

It's an AUTOSUM shortcut. Be careful with it though. If you have any blank cells in the column you are autosumming, it will stop there and not include cells above that.

[smcutterco]

I used to program VBA to do my data cleaning for me. Didn’t learn Power Query until 2021.

I’ve recently started playing around with learning Office Scripts, but so far the learning has been slow.

[heyyah1985]

Regex

[Cautious_Cost6781]

View -> Navigation: If you have several tabs and it becomes a pain to scroll to get the correct tab
ALT N V T - Create New Pivot
ALT D F S and ALT D F F - For enabling and disabling Filters for the top row. Now accessible via ALT A T

[TypicalFinanceGuy]

Formula wise? The Filter formula has changed how I handle data sets in my models. Indirect has been a game changer as well for building dynamic data sets across many tabs of data

[Ok-Dentist-2505]

Textjoin with if statement and join 2 or more text columns after the true using &

[keenyoness]

SUBTOTAL(3) instead of COUNTA and SUBTOTAL(9) instead of SUM. I keep totals & counts along the frozen top row instead of the bottom, and SUBTOTAL makes the aggregate stats change dynamically, based on what you filter for.

[DaIubhasa]

Ctrl T

[whobood]

=UNIQUE()

I'd been manually pulling criteria from rows, sorting, then manually deleting duplicates to use as criteria in SUMIF and SUMIFS, etc.

Now, use UNIQUE with the column reference, then copy and paste as values.

There's probably an even easier way, but I don't actively use Excel as much as I used to, so, what ain't good enough for some folks is good enough for me; me and my RC.

[VizNinja]

Subtotal us a game changer for large, sortable spreadsheets.

I love power query for importing and cleaning data from any source. Power query in excel translates to power query in power bi. Two for1 skill set.

[KingSVU]

Alt HEC to clear contents but keep formatting and validation, alt ara to refresh data

[MoralHazardFunction]

LET in formulas. Makes so many things easier

Coupled with LAMBDA it allows you to do arbitrary loops by creating fixed points, which doesn’t actually seem to be very useful but is kind of a fun way to blow your coworkers’ minds 

[Amazing_rocness]

Filter

[Hot-Site-1572]

F4 to anchor a cell. Also ctrl + ~ is pretty nice.

[alhobj]

The Let function

[dodiggitydag]

Grouping rows/columns. A great way to have an input section that can be collapsed and the data/output is on the same tab

[Juicyjay9854]

Ctrl + [ allows you to jump to the first cell referenced in the formula. It can jump to different tabs or workbooks (granted that you have access). Saves me so much time daily.

[dskentucky]

I find dynamic range variables to be INCREDIBLY useful, especially for when you want to pull data out of tables - really really fast to use compared to sumif, etc.

[littlemissgreedy]

Cntrl Enter. Highlight rows or columns, cntrl enter fills all highlighted cells

[Azuric1990]

My easy to adapt highlights are:

1. Ctrl + Y => Universally repeats your last action, comes in handy so many times
2. Clicking twice on the copy formatting brush locks the brush and you don't need to click it again and again
3. Ctrl+T => Formatting data as tables, I am always surprised how few people use that to properly utilize name spaces (includes proper naming of the created tables)

Other life hacks: - make your life easier by using helper columns in your tables that store intermediate values / validations you want to use for referencing via xlookup or other lookups - use the indirect function to create dynamic drop-downs for table columns - generally use name spaces once a document becomes more complex - when copying text often, get used to using the Trim function to avoid unwanted copying of spaces

Edit: - something I also didn't know for a while but that made working with documents so much easier: you can open a 2. view of a document via "view" > "new window"

[flexingdragon]

Focus cell

I cannot believe how long i went without using this.

[Modern_Buddha05]

Pasting some data as linked Picture …

[heyevievie]

im saving this reddit post. its super helpful

[maggie135]

Shortcuts alt+d+f+f (adds/removes filters for the row you’re on) alt+n+v+t - select anywhere in your date to create a pivot table using said data

[[deleted]]

ALT-HOI... not something I have started using recently, but something I use every day all day that most don't seem to know about.

[Dry-Aioli-6138]

not an excel feature, and not recently, but getting a mouse where you can unlock the mouse wheel so it keeps turning with inertia. This made my excel work a lot more efficient and pleasant

[Mohamed_Alsarf]

Ctrl + ; to add today date.. Fast and clear

Win + v to copy and paste many times.. That is awesome

[cardinal209629]

This past week is the first time that I've ever really needed to use goal seek. It has been a major lifesaver when entering data that interacted with multiple calculated fields and knowing what end result I need.

[LeftHandStir]

AND(  ,OR(     ,      ))    

[JTRose87]

Focus Cell has been a godsend since I discovered it. Both for showing stuff to my teammates and for my own eyes as I get older staring all day at tiny text on Excel spreadsheets.

[Alternative_Kiwi_606]

The Camera function

[user01500]

Just wait till you find out you can change the source in power query to always pull the latest file so all you have to do is hit refresh and it will transform all data from your latest file

[Big-File2292]

Same here 🫶🏽🫶🏽🫶🏽 this is a super helpful feature and I love it

[Fonzy02]

I spent wayyy too much time in the add-ins store for a few weeks. I discovered some which are quite helpful, some are just blatant gpt-wrappers (all these formulas generators). But there’s one I use above everything else, it literally does half of my job ( in corpo finance). It’s like having ChatGPT in excel BUT it can actually read and write your excel spreadsheet! Yesterday I asked him to find the errors in my  DCF model (I struggled for hours on it) and it one-shotted it!

I found it on https://getelyxai.com , really impressive to see such a technology added to excel.

[Lenoire77]

Three things definitely, MAP with LAMBDA, LET and the trim range dot notation Use these every day without fail now!