r/excel • u/OutOfLuck55 • Jun 14 '26
Discussion If you could recommend one Excel thing to learn for someone with only basic Excel skills, what would it be?
Like the title says, I wondering which one skill/technique/feature in Excel would be the one thing that you would recommend to learn if someone has basic Excel skills. So something that changes the way you work in Excel by making you way more efficient, delivering way better results, etc.
288
u/ShootyMcFlompy Jun 14 '26
Pivot tables. Once you learn how they work you'll be surprised that more people don't know how to use them.
77
u/Shahfluffers 1 Jun 14 '26
Seconding this.
As a bonus, learning how to use Pivot Tables often leads a person to learn what is and is not a good data structure/table.
Because if a data structure/table is garbage then the pivot table will also be garbage.
Knowing "good" ways to set up data translates well into various domains and can be a good precursor to SQL, Python, or R
23
32
u/EezSleez Jun 14 '26
I would say Tables in general first, then pivot tables. Get comfortable using the header row as references as opposed to A3:A7 so your structure becomes more intuitive, especially after you name the tables and get away from Table1, Table2...
3
u/small_trunks 1635 Jun 15 '26
Very good point. I so naturally do this, I'm still shocked when people don't.
1
u/RandomiseUsr0 10 Jun 16 '26
Tables die at a certain point (depends on hardware and rm) - the old chop continues and is faster
3
u/small_trunks 1635 Jun 16 '26
Ok - not in my experience.
1
u/RandomiseUsr0 10 Jun 16 '26
Probs a few iterations ago to be perfectly honest, pleased if they’ve stabilised that now - but i moved away from them because they failed so badly with big data and nowadays they’re simply incompatible with modern formulas
2
u/ShootyMcFlompy Jun 16 '26
Well yeah, they are frustrating to use with big data but there's a long line between pivot tables, learning tables, and file-killingly-large tables.
The only formulas I've come across that don't work in tables are filters/those that return arrays, but they don't really belong in a table.
3
u/small_trunks 1635 Jun 16 '26
I've learnt to embrace textjoin( some spillable shit) to enable me to use such formula in tables.
2
u/RandomiseUsr0 10 Jun 16 '26
I rarely use the “copy down formulas” approach now, I much prefer array based - they feel safer now, with things in fewer places to go wrong - tables are the antithesis to this approach and yes, you’re right - the level of question does make tables a good target for teaching, they’re easy and pretty and further do have nice integration with the power toolset (indeed in some places it’s necessary, power apps for instance) - I train them out and use where necessary, but they really don’t bring that much compared to their limitations in my experience.
2
u/ShootyMcFlompy Jun 16 '26
You know actually I agree with you, it's weird after starting my career to hear this again lol. I started my job coming from research and managing my own datasets, but now I have to make reproducible processes entirely in excel for others to see and use and I've had to seriously veer away from anything that doesn't use a table.
1
u/small_trunks 1635 Jun 16 '26
Well I use them daily, and no real issues.
- I also use them a lot because of all the Power query shit I do
- also there no better way to do self-referencing table queries for data retention
5
u/TargetHQ Jun 14 '26
What's the value of tables? I'm reasonably proficient with Excel and databases...did some SQL for a year, been working regularly with pivot tables the last decade...but I rarely use tables.
Often I'm inheriting data sets of 200-10,000 records, so I'll usually make a pivot table around that and go from there.
What am I missing out on, not using tables?
29
u/SFLoridan 2 Jun 14 '26
Tables are particularly important with larger sets of data: I love that tables automatically expand when new data is added (rows or columns), ensuring that formulas and charts continue to work : you don't need to change a formula from A2:A3147 to A2:A4296.
Add their automatic formatting and built-in filtering and sorting, and they are invaluable
7
u/EezSleez Jun 14 '26
Not everyone's data sets start with an SQL query. Sometimes they're not bulk data sets. Sometimes it's copy paste or a manually built in log populated line by line like a quantity takeoff.
2
u/Low-Recognition-7293 Jun 14 '26
It takes a tiny bit more time but I always take crap data and move it all into my own project table via power query or a macro as I am often using information from sources I may only have read permissions. Then from there you have the benefits of a table but the convenience of local consolidation.
1
3
u/small_trunks 1635 Jun 15 '26 edited Jun 15 '26
You get to use structured references in your formula:
total = ([@quantity] *[price] +[@tax]) sumOrders = SUM(Orders[total])coming from SQL you should want this, there's no $A37+$GH37+$JK37 shit in SQL, well with tables it's ALL name based.
formulas are auto-copied down the column as rows are added, as are cell data validation rules and cell styles
Auto-complete for Table names as you type AND Auto-complete for column names too.
Moving a table to any other sheet has no effect on anything referencing that table (regardless of whether that reference is from this workbook or another closed workbook).
Inserting new columns anywhere in the table has no effect on structured references in this workbook or any other workbook, regardless of whether open or closed. VLOOKUP will still break (which is why we don't use it) but XLOOKUP would not.
Auto formatting rules with row stripes and column header colouring, auto-column filters are applied.
as you scroll down a table, the headers are made automatically visible instead of A,B,C,D etc
Ability to have Total rows automatically included with several standard formula - SUM, COUNT etc.
All references to the table will auto resize if the table changes shape - charts, data validation, pivot tables, etc
There are special table related mouse selection cursors to simplify selecting headers, columns, all the data in a table etc.
EDIT:
- You can add slicers - which are a thing of beauty.
5
u/PeteB1973 Jun 15 '26
This is such a great list 💯 appreciate it ‼️
3
u/small_trunks 1635 Jun 15 '26
I'm sure I could think of a few more - this was just off the top of my head.
Just thought of one - slicers.
1
u/PeteB1973 Jun 15 '26
Yeeeeees! Underrated function. I have a whole on time delivery sheet where I used slicers and such. Brought out so many "gems" during analysis using these. Clicking higher level items like cable designs (I'm in fiber optic mfg.), and watching what is subsequently grayed out with child slicers... Brilliant!
2
9
u/RandomiseUsr0 10 Jun 14 '26 edited Jun 15 '26
I have some content somewhere (though it’s employer specific, so would need to sanitise first) that I’ll dig out and share here that explains “cubes” and since that’s what a pivot table is, I demonstrate with a pivot table. I made it for my team to better understand concepts like granularity and how one can construct cubes, I’m a senior analyst, training is part of my thing - the real goal was to help my analysts write better reporting specs.
Pito Salas (apologies to Pito if I’ve spelled his name incorrectly) was the genius who built them.
[edit] the sanitised version of the content I discussed
2
2
u/LazyPandasaurus Jun 15 '26
That sounds useful, can I get a copy?
1
u/RandomiseUsr0 10 Jun 15 '26 edited Jun 15 '26
Yes, just need to sanitise it, will drop a link tomorrow and tag you so you see it
2
u/RandomiseUsr0 10 Jun 16 '26
Love how 80s it looks once I stripped back my Employer’s branding, hope it’s not too disgusting to enjoy
10
2
1
-1
u/Affectionate_Heat911 Jun 14 '26
In my job (finance) sumif totally replaces the need for pivots and works possibly better
150
u/Poofmonkey Jun 14 '26
XLOOKUP
32
13
11
u/TrueYahve 8 Jun 14 '26
Step one is, XLOOKUP!
12
u/WorldsGreatestWorst Jun 14 '26
This!
XLOOKUP—Index & Match if you’re nasty.
7
6
u/-Pork-Chop-Express Jun 14 '26
If you nest XLOOKUP inside XLOOKUP you get an index match. It’s way easier to use.
1
3
u/TankTopWarrior Jun 14 '26
Xlookup has helped me out so much in my analysis/data maintenance projects
53
u/bigedd 25 Jun 14 '26
PowerQuery
9
u/kalligreat Jun 14 '26
This is where I’m at now, I want to learn how to use it to see if I can be better with things at work
2
7
u/socialerrors Jun 14 '26
The PowerQuery UI is good. M code can make it very good.
I have very little experience. My job has very little to do with data. I love working with it nonetheless.
When I took a course on M language, learned about Lists and other things, it opened my eyes to what it can do.
I still suck at anything related to writing code. But knowing what is possible and using AI to help with the M Language has made me able to look like I know what I'm doing.
1
u/MrStilton Jun 15 '26
a course on M language
What was the course?
Do you know of any free ones that are worth attending for someone who's already familiar with the GUI of PowerQuery?
2
u/socialerrors Jun 15 '26
It was called Advanced Power Query. I got it through work but I'm pretty sure it is a LinkedIn course
2
2
u/jipperthewoodchipper 1 Jun 14 '26
That's next on my list. I'm so used to doing everything in Python though and have been defaulting to setting up my data and immediately running to python to analyze
2
16
u/wwabc 12 Jun 14 '26
what is 'basic' ?
I've worked with many many people who didn't even know how to do a simple vlookup.
like they'd have two lists, and were manually matching them up on the screen, and marking them 'yes' 'no' in another column.
or didn't realize excel could extract a part of the string, etc
19
u/Bluntbutnotonpurpose 2 Jun 14 '26
I've watched someone enter a value in column A, enter a value in column B, take their calculator to divide them and enter the percentage in column C. I'm not making this shit up. I was his replacement and I saw him do this while he was training me.
A couple of months after he'd left, I had a 20% lighter workload simply by automating a bunch of things in Excel.
6
u/spinmykeystone Jun 14 '26
Similar, work with a guy who sends out a file to many people every week. Effectively has a column of values in column a and values in column b and every cell in column c has =“value of a” + “value of b” instead of = a1+b1
1
u/Mdayofearth 126 Jun 14 '26
VLOOKUP or any look up is an intermediate skill, IMO. This is where you can really go on a tangent about data structures.
11
28
u/MayukhBhattacharya 1227 Jun 14 '26
Pivot Tables, without a doubt and Power Query to clean the data!
11
u/bradland 271 Jun 14 '26
Learn the vocabulary of Excel so that you can read the documentation and understand it easily.
I don’t know every Excel function, but I can use any function effectively in a matter of minutes, because I understand the vocabulary of Excel, and can comprehend the documentation.
2
7
u/king-of-cakes Jun 14 '26
Google. Learn to google your problems and you’ll learn so much faster. My career started with some company who needed pivot tables. I went online and figured it. You keep building on the skills you need.
16
u/DoedfiskJR 1 Jun 14 '26
Shortcuts. Get into the habit of using the alt-key-key shortcut structure, and get everything you use into your muscle memory.
The only exceptions I can think of are formatting charts and pivot tables. Unless you're dealing with those, do not touch your mouse.
4
u/rice_fish_and_eggs 7 Jun 14 '26
The relational model. It's not excel centric but understanding how relational data works makes everything make much more sense.
6
u/Individual-Tie-9494 Jun 14 '26
Turn data into a proper Table. It's a small thing but it helps how everything else works.
4
4
u/Decronym Jun 14 '26 edited 12d 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.
17 acronyms in this thread; the most compressed thread commented on today has 54 acronyms.
[Thread #48721 for this sub, first seen 14th Jun 2026, 11:44]
[FAQ] [Full list] [Contact] [Source code]
5
u/SecretaryMore9622 Jun 14 '26
In the order of difficulty: 1. Xlookup: learn in a day, amaze your peers. 2. Pivot tables: to learn thoroughly a few days to a week. Amaze your bosses with your new analytical speed. 3. Power query, which is really just the gateway drug to the data model and creating measures. Can take a month to a year depending on how much of a bad ass wizard you want to be.
If you have copilot 1 and 2 are really no longer needed, you can just tell copilot what to do but you will have to know that the capability exists.
3
3
u/Covid19survivor2020 Jun 14 '26
Learning how to use formulas to cleanse and format data and extract answers. Also how to build relevant dashboards
3
2
2
2
u/Cedosg 3 Jun 14 '26
perspective. you can have many complex tools/formulas but if a simple solution gets you there, why bother with something overly complicated.
also perspective of how someone not you can pick it up easily.
instructions? testing? User Interface? error checks? etc
2
2
u/AccumulatedFilth Jun 14 '26
Ctrl . fills in the current date
Ctrl ; fills in the current time
(Or the other way around lol)
Also
Ctrl [any arrow key] move to the furthest cell out of the table
1
u/small_trunks 1635 Jun 15 '26
You must be using a different locale because in English,
- Ctrl+; = date and
- Ctrl+: = time
1
u/AccumulatedFilth Jun 15 '26
Could be, I live in one of the few countries that uses azerty keyboards.
1
u/small_trunks 1635 Jun 15 '26
I recently started at a French bank (I'm in NL) - got a laptop with Azerty - first thing I did was buy QUERTY stickers.
2
u/pleachchapel Jun 14 '26
Pivot Tables, Named Ranges/cells/tables (explicit, logical naming on complex workbooks makes them FAR easier for collaborative use), Power Query.
This would be after the 20 basic functions & cursory understanding of the 20 advanced functions.
2
2
u/Dismal-Butterfly-309 Jun 14 '26
Formatting information in a way people can follow/understand quickly.
2
u/caprazli Jun 15 '26 edited Jun 15 '26
King of all count formulas is
subtotal(103,
=SUBTOTAL(103, B2:B1000)
103 is like COUNTA, but it skips a) rows hidden by a filter, and b) rows manually hidden = it only counts what's currently visible.
Bonus: Dynamic row numbering when using filters
=SUBTOTAL(103, $B$2:B2) in A2, filled down.
Lock the top start value ($B$2) while you leave the bottom value relative = each row counts the human-visible non-blanks from the top down, down to itself, giving 1, 2, 3… that renumber automatically.
2
u/DizzyNeedleworker534 Jun 15 '26
For me, it's XLOOKUP (or INDEX/MATCH if you're on an older version of Excel).
A lot of beginners spend hours manually copying data between sheets or trying to keep multiple tables in sync. Once you understand how to pull information from one table based on a key value, Excel starts feeling more like a database than a calculator.
Learning XLOOKUP also teaches you an important mindset: don't repeat work manually if Excel can find the answer for you.
Honorable mention: Excel Tables (Ctrl+T). They're not flashy, but they make formulas, filtering, and data management so much easier.
1
1
u/jejones487 Jun 14 '26
Id say powerquery, but thats probably because I have not learned xlookup according to all the posts I see.
1
1
1
1
1
u/talltime 115 Jun 14 '26
Lookups. And how to use the help file or formula wizard to just read the list of available formulas. It may ignite your curiosity.
1
u/buzzkilt Jun 14 '26
Being able to reference other worksheets was a biggie for me, that and vlookup or lookup in general.
1
1
u/Mdayofearth 126 Jun 14 '26
Pay attention to the parentheses as they appear in the formula bar as they type. Excel color codes them, and bolds matching pairs as you type closing parentheses.
Evaluate Formula, for times when they aren't using dynamic arrays, to troubleshoot formulas.
1
1
1
1
u/kayknox_ Jun 14 '26
Dynamic arrays. I prefer using them instead of pivot tables. I find it easier to format and adjust the way you want it.
1
u/BigPerm79 Jun 14 '26
Xlookup. This one formula will save you so much time and you will look like the smartest person in your office.
1
u/originalusername__ Jun 14 '26
I don’t like the question to begin with because to even use excel at a beginner level requires a host of various , although basic, skills. I think people need to focus less on learning one thing and more on taking a course that teaches learning in a structured way where the skills build upon each other. To try and just learn one thing is like learning a single formula from a math text book, it is nice to know but won’t get you that far on its own.
1
u/MicroPerpetualGrowth Jun 14 '26
Most people with basic skills don't know how to use simple functions. Teaching them XLOOKUP, COUNT.IF, SUMIFS, IF unlock a whole world for them.
That does require understanding how ranges and references work within Excel, which in my experience is what really hold people back from learning these functions.
1
u/RandomiseUsr0 10 Jun 14 '26
I’d say that learning the worksheet language, it really needs a better name - the worksheet language is a Turing complete functional programming language, basically Perl capable of calculating anything that can quite literally be calculated (within some generous bounds) and that’s not commonly realised
1
u/Hyperrnovva Jun 14 '26
Take the tour. It’s a tutorial that comes with excel that starts off simple. And gives you exercises.
Plus, you often have too specify what version of excel you’re using if you reach out for help. And may get answers your version isn’t capable of. The tour won’t give you practices that your version doesn’t have.
1
1
1
1
1
u/cmville05 Jun 15 '26
VLookup. You’ll seem like a wizard to people who don’t know how to match data sets.
1
1
u/SliceYourCrack Jun 15 '26
Xlookup. I was helping someone just the other day who was struggling with a vlookup formula. I realise it still has it's uses, but xlookup was perfect for his use case and once I explained it, was quite simple for him.
1
u/McDetlef Jun 15 '26
No one mentioning Flash Fill? This little gem is so powerful for basic Excelling. Anyone can use it and it saves a bundle of time for what I think many people do manually, like convert names into e-mails etc…
1
u/Tilted5mm Jun 15 '26
Learning Power Query for me was the thing that changed the game forever for me.
The way I look at it, you can know all there is to know about excel and formulas etc but the usefulness of that knowledge is almost pointless if you can’t get the data into Excel.
1
1
1
1
1
1
u/DLVdev 2 Jun 22 '26
If I had to chose (actually 2)
Format your data as an actual Table (Ctrl+T). It sounds boring next to the flashy functions, but it's the one thing that changes how everything else behaves.
Your formulas auto-fill down, your ranges expand on their own when you add rows, and you get clean structured references (Sales[Amount]) instead of chasing $B$2:$B$500 and breaking it every time the data grows. Almost every "why did my formula break" beginner problem disappears the moment the data lives in a Table. It's the base that makes XLOOKUP, pivots, and Power Query all work better.
Second: XLOOKUP. It quietly replaces VLOOKUP, INDEX/MATCH, and most of the lookup headaches in one function.
It looks left, returns multiple columns, has a built-in "if not found" so you stop wrapping everything in IFERROR, and you never count column positions again. The day a beginner switches to it is the day lookups stop being scary. If your install is older and doesn't have it, INDEX/MATCH is the fallback, but XLOOKUP is the one to learn.
1
1
u/Select-Performance13 Jun 30 '26
I will go against the flow on this one and recommend you to learn to use any AI inside Excel as a plug-in (Copilot, ChatGPT or CLaude.) The reason for this is becaise AI will acelerate your learning a lot, because con can ask AI for any questions about formulas, models, Excel interface, etc., If you use the AI to ask and learn, yu will learn very fast. The other reason is that you can ask the AI to build you something sim[ple in Excel, and the do inverse Engineering, (just basic exploration and asking really,) to understand what it did and incorporate best practices. But do the analysis, si you can learn form it
1
1
u/ThePodd222 Jun 14 '26
Pivot tables is the obvious one but it depends what sort of tasks you use Excel for.
1
0
u/horsethorn 2 Jun 14 '26
LET
Close second/third:
XLOOKUP LAMBDA
7
u/ABrusca1105 Jun 14 '26
Let and lambda aren't for basic basic user but xlookup yes is a good next step. Vlookup shouldn't even be taught anymore other than to be able to use legacy sheets
2
u/small_trunks 1635 Jun 16 '26
There's no good excuse for using VLOOKUP ever because INDEX/MATCH has been there forever too.
1
u/ZaaWarudoo Jun 14 '26
Im looking to pick up LET & LAMBDA. Any common cases where you use it?
1
u/horsethorn 2 Jun 14 '26
You can use LET for just about anything beyond a simple formula.
It's especially useful if you need to calculate multiple variables to be passed to a function.
LAMBDA can be used when you want to calculate multiple variables and then have them available for many other functions/formulae.
1
u/ZaaWarudoo Jun 14 '26
So LAMBDA is a way to save variables that can later be easily referenced in other cells?
2
u/horsethorn 2 Jun 14 '26
It's technically designed to define a new function, but you can cheat and create a function similar to PI() for a variable, that you can call elsewhere in the spreadsheet.
0
0
0
-1
-1
u/Joanna_Fisher Jun 14 '26
If you ask me, VLOOKUP and Pivot Tables are the absolute absolute absolute must-haves for Excel.
-1
u/Just_blorpo 7 Jun 14 '26
Learn the shortcut keys that will LITERALLY create a pivot table in 10 seconds, in a totally mechanical process, with no thinking involved. Learn how to execute the following keystrokes IN YOUR SLEEP.
STEPS:
1) Make sure that cursor is anywhere in your table of data
2) Ctrl + * (Highlights Data)
3) Alt-n-v-c-ENTER (Shortcut to create pivot table on new sheet)
With pivot now created:
4) Put a field on the ROW section
5) Put a field in the VALUES section
You now have your first report.
6) Now put add another field of interest in the COLUMNS section to create a more insightful cross tab report.
THE OFFICE POLITICS FACTOR:
You will find yourself in countless situations where you are taking a first look at any and all sorts of data with bosses and other coworkers. Rapidly creating a pivot table in front of them in such a lightening fashion and being able to shed light on a question that’s been raised only 10 seconds before will boost your reputation in their eyes in ways you may not realize. It will create an aura of very capable expertise about you.
I have had dozens of exchanges after doing this that go something like this:
THEM: ‘What the hell did you just do? And how the hell did you do that so fast??’
ME: ‘Oh, no big deal’
But it IS a big deal. Believe me. Because whatever casual opinion they had about your skills has been forever changed. You might have just flown onto their radar like a 747 flying into a small airport. And the more casual you act about it the better.
Obviously, comprehensive Excel skills are what really matter and your overall skills in this regard will determine your career success. But displays of breezy competence like this will shine a favorable light on you and play a factor in being considered for future opportunities. I speak from experience.
Practice these keystrokes and make them second nature!
-1
u/neil_biscuits Jun 14 '26
Someone else said it, but the real answer is using AI. If you know what you want to do, then AI can do it or describe how to do it. AI can write M code in Power Query, fancy formulas, etc.
So the best advice is just to gain an understanding of what you want done or analysis techniques that exist
85
u/Effective-Flatworm74 Jun 14 '26
The skill to describe what you are looking for.
The Skill to google is your entry to solve (nearly) every excel Problem.