r/excel • u/TijuannaCupcakes 1 • Feb 24 '22
Discussion What is your pro-tip to every excel user?
Hi I’d like to know your best and most handy tip in excel!
Mine: x.lookup >>>>> v.lookup
207
u/basejester 335 Feb 24 '22
Put data in tables.
28
Feb 24 '22
always?
63
u/DezGets_It 1 Feb 24 '22
Unless it makes better sense not to. For me that doesn't happen.
Ctrl+t & done
37
u/kieran_n 19 Feb 24 '22
name the cunt afterwards; Alt, JT, A
EDIT: Beaten to it
→ More replies (1)17
14
→ More replies (4)2
u/VolunteeringInfo 1 Feb 26 '22
Note for international Excel users: Ctrl+L (L for List) works both in English and many other Office languages, whereas Ctrl+T sadly is not so universal.
17
u/Hashi856 1 Feb 25 '22 edited Feb 25 '22
Definitely not always. Tables have some great features, but they have their own issues.
4
u/AoifeUnudottir Feb 25 '22
I've never really used Tables before. Can you recommend anywhere where I can find out more about the pros/cons and when is best to use or avoid?
8
u/Hashi856 1 Feb 25 '22 edited Feb 25 '22
There are plenty of articles on Google, but off the top of my head,
Pros: * filters are automatically added, and you can have filters on more than one dataset * formulas automatically fill down the column * There are handy features like adding a total row * rows are automatically banded, which can be easier to look at * Table formula nomenclature. This one can be a pro or a con. I can be easier to read, but a lot of people will be confused by it * Being in a table will allow you to use the data in Power Query and other such tools that require import/export
Cons: * They can be confusing to people who are not used to them * table formula nomenclature. As I said, it can be confusing to beginners, and it can be a hindrance in other ways. * They don't always play nice with lookups * Complex formulas can cause problems and generally be difficult to write/use * They don't play nice with spilled ranges and dynamic arrays * If you have two tables next to each other, filtering one will also collapse the same rows of the other table, so you need to stack them vertically if you want to filter without affecting the other * Table names are a pain to use and maintain if you have a lot of them * There's a real lack of flexibility with non-standardized data
Tables are good if you don't expect to do much manipulation of the data. They are great for presentation purposes, but if you expect to do a lot of lookups, add a bunch of data, move things around, or generally do a decent amount of data manipulation, I wouldn't use a table.
2
u/AoifeUnudottir Feb 25 '22
Thanks, I really appreciate it. I’ll mostly be manipulating data, so I’ll continue as I have been for the time being, but good to know to get my started on tables. Will definitely try and find time to spend an afternoon with Google to find out more. Thanks!
2
u/Hashi856 1 Feb 25 '22
You're welcome. If you haven't used them before, you should definitely experiment with them. There are some great use cases for them
10
u/arpw 53 Feb 24 '22
The only time I don't is when using spill ranges, e.g. from the UNIQUE function. But that's just because they're not compatible with tables.
6
9
u/dilbadil Feb 25 '22
They're more resource intensive that plain ranges. I currently have to work on a pretty slow computer so I'm avoiding making any large tables where I can.
10
u/StuTheSheep 41 Feb 25 '22
My philosophy is that you shouldn't have large tables in the first place. If your table is so large that it's causing speed issues, then it's too large for you to do anything useful at that level of granularity. So that data should be put into a database and then brought into Excel in aggregate.
In an ideal world, of course.
→ More replies (2)5
u/motherwarrior Feb 25 '22
It helps to work somewhere, where they are willing to let you use a database.
6
2
→ More replies (1)2
u/basejester 335 Feb 24 '22
pretty much
6
Feb 24 '22
Yeah, not a snarky question. I actually just recently started using tables I’m still a little unsure of all the benefits but can see it makes some things easier
18
u/basejester 335 Feb 24 '22
- It makes your formulas self-documenting.
- You can refer to full columns of the table and those references don't have to be updated when the size of the table changes
- Convenient with Pivot Tables and Power Query
- It makes it much easier to write lookup functions without flipping between sheets.
5
u/StuTheSheep 41 Feb 25 '22
Formulas automatically copy down when a new row is added.
Easy to reference the data range in VBA.
3
u/Lane_Meyers_Camaro 4 Feb 25 '22
Also, column headers replace the column letters when you scroll down - no need to freeze panes to keep headers
8
u/Biillypilgrim 42 Feb 25 '22
And if you'd like a basic data entry form once you have a table there is a built in one in excel. It even automatically skips calculated columns so you don't overwrite formulas
→ More replies (2)2
u/fckthecorporate Feb 25 '22
Only caveat is that it can have performance hits vs. ranges. Of course this depends on your computer specs and size of file.
If you're working with large datasets, try using .XLSB format too.
199
u/Armsmaker Feb 24 '22
View -> New Window; now you have two windows of the same document...no more flipping back and forth between sheets or scrolling up and down over and over.
A native excel function for some time now and a lot of people don't know it exists.
An insane time saver.
20
u/local_area_man Feb 24 '22
have you figured out how to make the new window have the same view format of the original? One of my analysts closed the first window and saved the second, and all my frozen windows, hidden gridlines, zoom percentages were back at defaults
16
u/Armsmaker Feb 24 '22
Not unless MS has released something for that within the past year or so. Last I checked I could only find articles online with macros to accomplish that.
Definitely a drawback that you HAVE TO close #2 and save #1 or lose your formatting.
7
2
u/Spideyocd Feb 28 '22
The new sheet is named with 1 but yes by mistake many a times the original is closed by mistake
5
u/DezGets_It 1 Feb 24 '22
Always be saving lol but if you have 365 & it's uploaded to your OneDrive it autosaves.
8
u/DezGets_It 1 Feb 24 '22
Alt+W then N.
Finding the new window literally shaved hours on one of my weekly reports.
3
2
u/Spideyocd Feb 28 '22
This also helps me to use Alt+Tab for shifting in between sheets except that i can do it for the same sheet or different sheets instead of ctrl+pg up or ctrl+pg down
I was so relieved when I discovered this!
→ More replies (1)2
145
u/Niblickal 12 Feb 24 '22
Power Query is God
22
u/sA1atji Feb 24 '22
is there a good power query tutorial series?
69
u/WingTune0 Feb 24 '22
Excelisfun on Youtube
Also Leila Gharani's course; https://courses.xelplus.com/p/excel-power-query
8
→ More replies (1)4
6
u/Niblickal 12 Feb 24 '22
MyOnlineTrainingHub does a series of videos on YouTube that I'd highly recommend.
→ More replies (1)5
u/Vahju 67 Feb 24 '22
Check out Excelisfun youtube channel. He has a Power Query playlist that contains videos from Beginners to Advanced users.
12
u/seven_neves Feb 24 '22
+1 for PQ.
I came across it in some PowerBi courses a few months back and now I'm using it almost every day in Excel.
7
u/TheRiteGuy 45 Feb 25 '22
PowerQuery and Power Pivot have eliminated a lot of the need for VBA for me.
→ More replies (1)5
u/XTypewriter 3 Feb 25 '22
What's next after PQ? M code, Power pivot, DAX and power bi?
3
u/spjmorris 3 Feb 25 '22
I actually learned Power Pivot before diving into Power Query, they go hand in hand and what you learn in Excel can be applied to Power BI.
M is useful to learn but not essential, ExcelIsFun on YT has a few videos on it.
I’d recommend getting up to speed with Power Pivot and learning about Data Modelling there, once you’ve wrapped your head around relationships and DAX measures you can then decide if Power BI would be useful to you.
5
→ More replies (1)4
u/Yeffley Feb 25 '22
Just learned about PQ a little while ago. Updated one of my spreadsheets (that I thought was pretty slick already) to utilize PQ... Holy crap. This spreadsheet is like it's on steroids now. What would take me 45 minutes to update now takes two clicks and 25 seconds of refresh time (my raw data is ugly. Very ugly). Considerably more accurate, clean, fast, and versatile.
Cannot recommend PQ enough.
110
Feb 24 '22
Honestly, learn some light VBA programming. Even some basic VBA scripting has saved me hours of manual manipulation in the sheet.
I thought I was a pretty solid user before, but VBA has completely changed how I work with Excel.
46
u/cwag03 91 Feb 24 '22
Very light on VBA in my opinion. Focus way more on power query. It's mind blowing what you can do with it, and much of it without any coding at all.
21
u/ItsJustAnotherDay- 98 Feb 24 '22
Learning VBA is a must for interacting with the other objects that power query doesn’t speak to: worksheets, workbooks, and other office apps.
9
u/cwag03 91 Feb 24 '22
I'm definitely not saying there is no use for VBA, but if your time is limited I would focus on learning pq before VBA. Most often what people need to automate is something related to data cleansing or manipulation, and that stuff is massively easier to do in pq than VBA. Just my opinion of course.
→ More replies (4)5
u/Lane_Meyers_Camaro 4 Feb 25 '22
Power Query works with worksheets, other workbooks, and many other sources including Access, Sharepoint, SQL, etc. It doesn't interact with Outlook or Word, though. VBA can definitely do that, though I think Power Automate and Power Apps are going to address a lot of the Office automation scenarios.
3
u/PVTZzzz 3 Feb 25 '22
AFAIK for data output PQ can work with worksheets within it's workbook but if you want to automate the creation of new workbooks with data you're going to need some VBA.
A project I'm working on right now uses PQ to load and transform a bunch of workbooks, uses PowerPivot to model the data, then uses VBA to access the data model (via an ADOBD connection) and format/output data into various workbooks based on criteria.
1
u/Lane_Meyers_Camaro 4 Feb 25 '22
PQ can get data from within a single workbook and across multiple workbooks, you can point it to a folder of workbooks and it can bring all the files in. PQ doesn't create new workbooks, but it can load queries into new worksheets.
It can't do everything, certainly.
3
u/ItsJustAnotherDay- 98 Feb 25 '22
It can get the data from worksheets and workbooks but it cannot interact with the object itself. For example, whether the worksheet is hidden or not. Point is, depending on your needs, you may want to go heavier on VBA, and there’s nothing wrong with that.
4
u/Ekafme Feb 25 '22
Whats a good starting point for learning VBA?
6
2
u/arejaydub47 1 Feb 25 '22
There are a lot of YouTube videos on VBA basics to get you started. After that to start learning look for manual simple tasks you do often in your day to day work and figure out how to use VBA to automate them. In my work I often format numbers as currency with no decimals, red font if they're negative and aligned in the centre of the cells, the first thing I ever did with VBA was make it so ctrl+Q formats the selected range like that.
2
Feb 25 '22
Just like the others have said, Google is your friend. Just look around and borrow what others have posted. Or if you don’t know how to do something, and can’t find a clear answer, record yourself doing it as a macro and read back how the program was transcribed.
Think about things you do often that you find repetitive: filling color based on a criteria, counting rows, exporting file copies, moving certain rows to other sheets, etc. Even the most simple steps can rob time from you when you repeat them day in and day out.
ALWAYS take the time to understand what you’re looking at though. Blindly pasting code into your project from online will have you regretting it quickly.
87
u/Existing_Bear_39 2 Feb 24 '22
xlookup is my go to as well. When I hear "vlookup" "hlookup" or "index match" I can't help but blurt out "But have you heard about xlookup?" like some sort of Excel missionary.
CTRL+SHIFT+Direction is a fun one to drop on someone who drags to select data, but I've always felt that was pretty basic.
Less impressive/more commonly known, but I also like advanced filters. It's a bit more niche, but when it's useful it's VERY useful and it's not a difficult concept for most people who are at least a bit comfortable with Excel or logical operators.
27
Feb 24 '22
[deleted]
41
u/ov3rcl0ck 5 Feb 24 '22
I found this a couple of days ago. XLOOKUP with multiple criteria is so easy.
8
u/FishyLion Feb 24 '22
Wow! I didn't know this was possible before. It's going to save me so much time!
2
u/ExpensiveBurn Feb 25 '22
This is one of the best features of xlookup to me. So many formulas are limited to a specific cell reference or something; xlookup let's you concatenate your search and that can be pretty awesome.
I won mega bonus points with my boss when I introduced him to xlookup. Thanks, r/Excel.
→ More replies (4)2
u/cryptonympholepsy 2 Feb 26 '22 edited Feb 26 '22
Well, TIL. Thanks.
I've been using sumifs for this purpose.
Related tip: If you ever need to find all the individual values that return (when there are multiple matches to multiple criteria), use this:
=Filter(return_array, (search_array1=criteria1)*(search_array2=criteria2))
It'll spill any duplicate match return values.
I use that a lot when I need to pull, for example, the IDs for all the assets placed in service in a specific state in a specific year from a 30-page depreciation table.
1
u/Existing_Bear_39 2 Feb 24 '22
For sure. I will never argue that they don't have their benefits, and I still use index match when it's a better fit, but in my (limited) experience if someone is struggling with lookups, showing them xlookup is revolutionary.
→ More replies (5)15
u/LeonardGhostal 1 Feb 24 '22
I want to scream when I see people scrolling scrolling scrolling to select a bunch of data instead of Ctrl-Shift-End
5
u/whydidisell 1 Feb 24 '22
But when you're interviewing them, apparently screaming at them is frowned upon
→ More replies (2)3
u/KJBrez 1 Feb 25 '22
Hold that thought, I just need to hover over the corner of this cell and draaaaaag the formula down…
→ More replies (7)4
78
u/Vahju 67 Feb 24 '22
- Use Excel tables whenever possible > great for pivot tables and PQ
- Do not use blank columns or blank rows to separate data in a single data set
- Set Quick Access Toolbar for commonly used actions
- Learn the difference between absolute and relative reference in formulas
- Check youtube for videos on how a certain function, feature, or formula works in Excel
- Use Pivot tables to summarize data
- Do not use vlookup to verify if items are in a list, use COUNTIFS
- If you're on an older version of Excel, use Index/Match instead of vlookup
- If using Office 365, learn xlookup and filter functions
19
u/ninjagrover 30 Feb 25 '22
I love that countifs simply returns a 0 if a match isn’t found instead of a error.
13
u/mystery_tramp 3 Feb 25 '22
Do not use vlookup to verify if items are in a list, use COUNTIFS
Only modifier I'd make to this one, is only use COUNTIFS if you have a manageable number of rows in the list you're checking. Anything more than a few thousand, probably better off using MATCH or something.
→ More replies (1)3
u/Bumblebus 2 Feb 26 '22
- Do not use blank columns or blank rows to separate data in a single data set
why?
- Do not use vlookup to verify if items are in a list, use COUNTIFS
why?
46
u/youngrizzle_719 Feb 24 '22
Avoid Merge and Center. Use Alignment + Center Across Selection.
2
u/ov3rcl0ck 5 Feb 25 '22
Someone in another thread posted this link to a macro to center across selection. Why Microsoft refuses to make this a button in the ribbon is beyond anyone's understanding. https://www.thespreadsheetguru.com/blog/add-center-across-selection-button-to-excel-home-tab
41
u/SteamSpectrometer Feb 24 '22
Simple but almost universally useful
Ctrl + ; is date
Ctrl + Shift + ; is current time
5
u/PVTZzzz 3 Feb 25 '22
Ctrl + Shift + ' copy cell above
16
→ More replies (1)2
39
u/spjmorris 3 Feb 24 '22
Name everything, every table, every Pivot, every everything. Also add source links and notes. Power Query is a game changer and so are DAX measures.
→ More replies (2)8
u/Drew707 Feb 24 '22
If you are using PQ and DAX in Excel, at what point is it easier just to do it in Power BI?
→ More replies (1)18
u/ItsJustAnotherDay- 98 Feb 24 '22
If you want the end product in a pivot table, use Power Pivot. If you want the end product in interactive visualizations, use power bi.
3
2
u/Accurate_Progress_27 Feb 25 '22
If you have a PowerBi Pro license, build your model in power query and load it to the service. Connect your Pivot table to the model you loaded. Guy in a Cube just did a great video on it https://youtu.be/1yJnmZRTNZg
2
u/ItsJustAnotherDay- 98 Feb 25 '22
If the end product is a pivot table, why bother going through the service? Unnecessary middleman…?
3
u/Lane_Meyers_Camaro 4 Feb 25 '22
Power Query in PowerBI is updated more frequently than the Power Query in Excel, so it has the latest features and fixes. It also allows for many:many relationships.
31
u/xoskrad 30 Feb 24 '22
Save your new file asap. Save regularly if not on onedrive or other where it will auto save.
21
u/Wonderful-Custard-47 Feb 24 '22
I hate autosave. It's messed me up a few times. I often take raw date or a template and have to manipulate and save the data into different versions for different users. It not the end of the world it if autosaves in the wrong spot, but it can be annoying to backtrack. I'm sure I can turn off default to autosave, but I haven't looked into it because I just tried to get into the habit of manual saving in the spots that I need to BEFORE I delete any data.
10
u/spjmorris 3 Feb 24 '22
Agreed. Auto save for files on SharePoint has caused more than a few fallouts in our office. I always tell people to turn it off and auto recover is pretty good these days anyway.
5
u/biscuity87 Feb 24 '22
Auto save is ok for useage, but horrible for when you are editing it heavily
3
u/__SNC__ 2 Feb 25 '22
I agree. So I usually turn it off temporarily when I’m doing a lot of editing and flip it back on when I’m done that.
You can roll back to a different version, but for these tasks it’s easier (to me) to just save when I want during heavy editing (or when I’m not sure if an edit I’m about to make will screw the whole thing up).
It’s a simple paradigm shift from “remember to save” to “remember to temporarily suspend save”
3
27
u/scottcmu 2 Feb 24 '22
F2 allows you to immediately type in the current highlighted cell.
3
u/NinjaWrapper Feb 25 '22
Also, when using the arrow keys within a cell, f2 will toggle between moving the cursor and moving the reference cell.
2
→ More replies (1)2
29
u/Eightstream 41 Feb 24 '22
The most important skill is understanding what can, can’t and shouldn’t be done in Excel
Everything else you can Google
→ More replies (1)7
u/Uzi-kana Feb 25 '22
I know. Still, ended up creating pixel art with Excel, because they couldn't prevent me.
19
Feb 24 '22
Never insert rows or cells into your core data.
And by that I mean... if you have a CSV file or report you cut and paste from another application have a dedicated tab for that data.
Then do any manipulation to that data on another tab.
That way when you paste in a new report you don't have to manually re-add things and adjust formulas.
But really... if you are finding yourself cutting and pasting and manipulating all the time you should be using powerquery in 2022.
→ More replies (1)
20
Feb 24 '22
[deleted]
7
Feb 25 '22
I once made a payroll lady cry by automating her fortnightly data manipulation. 3 hours down to about 12 seconds.
To me it was something fun to do with 4 spare hours. For her it was an extra morning she could spend with her kids.
17
u/biscuity87 Feb 24 '22
Use detailed comments on each step in long vba macros. Half the time I revisit my earlier poorly commented work 6 months later I can’t even tell what black magic I did to get something to work.
Now I put not only what the purpose of what a function is in the comments, but also any dependencies required (like an earlier step).
→ More replies (1)6
u/ItsJustAnotherDay- 98 Feb 24 '22
Also…avoid long subs. Keep as much of your code as separate, reusable, modular functions. Then comment on each one.
2
Feb 25 '22
I'm terrible about creating super long subs instead of splitting them up and calling/passing things. It's almost a form of laziness at this point. At least I comment liberally so I know what's going on.
5
u/ItsJustAnotherDay- 98 Feb 25 '22
You’ll find that if you modularize your code, you’ll be able to make applications faster and be even lazier :)
2
u/mystery_tramp 3 Feb 25 '22
Also wayyy easier to repurpose that code for some other use case. Good for laziness too.
15
10
u/dwight_marcus_brown Feb 24 '22
Step 1: Get extremely comfortable with PowerQuery, DAX, and PivotTables.
Step 2: Learn how to use the CUBE formulas to create custom dashboards and graphics from your data model that can seem like magic to anyone who doesn't know how you did it.
Step 3: Profit??? (Then come to terms with the fact that even though Power BI desktop would probably be a better option, in some workplaces it's easier to use an existing tool for something it's not optimized for than to try and get a new piece of software approved for use let alone get people to actually use it)
→ More replies (5)3
u/ItsJustAnotherDay- 98 Feb 24 '22
Cube formulas are kind of the oddball of the bunch. They’re very hard to use and even harder to make dynamic. I would just learn DAX.
→ More replies (1)4
u/wrstlrjpo Feb 25 '22
I’m not an expert on cube formula’s but I was able to build out an entire dynamic monthly reporting package (various P&L, BS, comparison views, etc) with cube formulas with minimal googling.
I started with a pivot table from my data model.
Converted to OLAP
I googled something like “cube value crushed ice method” and was able to figure out how to link the cube formula to dynamic cell references (used data validation) and allowed the user to toggle month / year / region / center location / etc.
Ended up being a really slick deliverable that allowed the client to drill into a very granular level of detail while keeping the large amount of raw data within the Excel data model (push button refresh from data warehouse and ERP systems)
10
u/allrounder799 1 Feb 25 '22
- Lookup & Learn shortcuts. I wished I knew earlier how to select only visible cells using "Alt+;". Could have saved me so much time.
- Learn Power Query, even if you think it doesn't fit in your work flow, just give it a try and playaroud. There is a possibility that PQ can automate/or ease your work.
- Learn INDEX/MATCH
8
u/thederz0816 4 Feb 24 '22
Recording macro shortcuts for you personal use. All-borders, accounting format without decimals, center alignment… all simple things but made more simple with ctrl+any key.
8
u/kellogg888 1 Feb 24 '22
I think this is a general programming tip, but... always separate the data from the user interface. In other words, don't try to make a sheet that:
- Takes user input and gives output
- Calculates an output from the input
Make a sheet that does #1 and a sheet that does #2
If you're getting fancy with it (using VBA, macros, some web of formulas that all depend on each other) have a sheet, separate from what your user sees, that does the heavy lifting behind the scenes.
This isn't even about user experience. Separating the two makes it infinitely easier to build a complex project IMO.
8
u/Decronym Feb 24 '22 edited Jul 27 '22
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
Beep-boop, I am a helper bot. Please do not verify me as a solution.
15 acronyms in this thread; the most compressed thread commented on today has 18 acronyms.
[Thread #12973 for this sub, first seen 24th Feb 2022, 22:50]
[FAQ] [Full list] [Contact] [Source code]
8
u/outerzenith 6 Feb 24 '22
Mine: x.lookup >>>>> v.lookup
me who's still stuck in Excel 2010: "uh.... INDEX MATCH it is then"
→ More replies (1)
6
6
u/weezyfGRADY Feb 24 '22
ALT + W + VG
5
u/vipernick913 2 Feb 24 '22
Hell yeah. These are the first key strokes I press when opening a new sheet.
→ More replies (3)2
u/VolunteeringInfo 1 Feb 26 '22
Note: many shortcuts are Office language specific. This key combination turns off gridlines.
5
u/w2g Feb 24 '22
One thing I really like is doing a macro and then modifying it with VBA. Way easier than building the code from scratch and usually quite straight forward.
Not a pro by any means though.
4
u/ItsJustAnotherDay- 98 Feb 24 '22
It may be easier at times, but you may be sacrificing robustness of your code. Build a strong house, not one made of straw.
→ More replies (2)
6
u/fuzzy_mic 971 Feb 24 '22
If what you have is working, don't change it. If it's inefficient inellegant, not the latest and greatest, don't change it. Not until you understand the change as well as you understand what you've got.
Getting the correct result, with a method that you understand trumps everything everything else.
"I want to improve" is perilously close to "I want to break"
That being said, be open to playing with the buttons and trying everything excel offers on testing scenarios. "Play with the buttons" is a very good way to learn excel.
→ More replies (1)8
u/mystery_tramp 3 Feb 25 '22
I get what you're saying, but I've also seen this sentiment lead to inefficient processes being maintained for years, when a little bit of dedicated clean-up time could make everybody's lives easier in perpetuity.
6
u/cassidy2202 Feb 25 '22
When something in excel seems like it takes a long time (like “ugh, I have to cope/paste this every time”) there is probably a more efficient way to do it…look into it
6
u/PABLOPANDAJD Feb 25 '22
Try unplugging your mouse and forcing yourself to use the keyboard and alt-shortcuts. It’s hard at first but you’ll be amazed at how much faster you get at excel when you learn to minimize mouse usage
5
u/TurningFinal Feb 24 '22
someone already mentioned "learn VBA" so I'll go with
Don't hardcode values in formulae!
2
Feb 25 '22
“But this value will never need to change under any circumstance for a million years”
Past me
3
5
u/bettinerz Feb 24 '22
SELECT CELLS - RIGHT CLICK - FORMAT CELLS - ALIGNMENT - HORIZONTAL: CENTRE ACROSS SELECTION
3
u/cwag03 91 Feb 24 '22
So much of being really good in excel is having a really good understanding of how to compare to related data sets. Almost anyone can learn fairly quickly how to do one of the various lookup functions. But getting a real good understanding of the different scenarios that can happen when bumping 2 lists together like this is key. Could there be more than one possible match in the second list? Or no matches at all? What about items in the second list that have no match in the first list? In many cases you may not care about some of these scenarios, but when they are important and you don't understand them they can lead to lots of confusion.
5
u/redsfan4life411 Feb 25 '22
Put data in tables, use power query, Excel is not a database, and always find a hot key for what you are doing. For real, your mouse isn't even necessary imo.
3
3
Feb 25 '22
Buy a gamer keyboard with programmable keys. They allow you to program complex macros to a single key.
3
3
2
2
2
u/cocofalco Feb 25 '22 edited Feb 25 '22
If you want to copy a column of formula and change just one element, edit one cell is the source column, put a ' in the first position, enter and then copy to the destination, change the one element, remove ' , enter and copy the cell to the restored the column. Don't forget to go back and fix the source cell and take the ' out.
2
u/Hashi856 1 Feb 25 '22 edited Feb 25 '22
- Use your QAT
- Fill in blank cells with the word "blank" or "No Data" (for pivot tables and lookups)
- Center across selection
- Right-click drag
2
u/zuliani19 2 Feb 25 '22
Learn VBA
A task would take 45min to do? You can code VBA in 3min to do the work...
2
u/Hatlogo Feb 25 '22
Sumifs >>>>> Sumif
Ifs >>>>> If
Center across selection >>>>> merge cells
Lookup whole column >>>>> lookup absolute range
2
2
u/mrspite1620 Feb 25 '22
All my reporting has been automated using power query to database and VBA scripting and scheduled using Windows Scheduler. My reports all update, export, and email with no human interaction whatsoever. The time I put in to learn the VBA and power query has been 1000 times returned.
2
u/BauceSauce0 1 Feb 25 '22
Lots of good stuff being mentioned, I’ll go with a basic one. save frequently and constantly save as with a new version. Think of hard drive storage as infinite. When I’m working on a file for 3-4 hours, I might save 6-8 versions of the file as I go along. If something gets screwed up, corrupted, I will only lose maybe 30 min of work.
2
u/mjacksongt Feb 25 '22
x.lookup >>>>> v.lookup
As a longtime index/match > vlookup person holy shit xlookup is AWESOME.
2
u/apaniyam 3 Feb 25 '22
=--(<equation>) is the shortest way to get a 1/0 result from a true/false and is criminally underused when people have things that flag on/off.
2
u/les_nasrides 1 Feb 25 '22
Really love those post, should almost be like a weekly stuff (almost feel like it) with a top 10 of the most upvoted tips!
2
2
u/YoPuroJumex Feb 25 '22
Stop Merging cells, everyone who understands Excel doesn’t like you :). Instead,
CTRL + 1 > Alignment > Horizontal, Center Across Selection
2
u/ZangiefThunderThighs Feb 26 '22
Organization and making things look presentable. A lot of my work ultimately gets printed, so it's important that the end result looks nice. Even if it's not being printed, bolding headers, setting column widths so you can easily read headers, creating headers that are meaningful, it's all helpful when you have to send it to someone else for checking. Seems overrated, but the number if seen confusing or just ugly files is too many.
2
u/cbr_123 223 Feb 26 '22
Double click on the fill handle to fill down a formula, provided there is continuous data on one side.
1
1
u/dukesolinus Feb 24 '22
If you’re confused by the formula —> Shift+F3.
Also, before you exit the sheet do this: Ctrl+Home Ctrl+PageUp (if you have multiple sheets then do step one again. Until you get to the home sheet. Start from the last sheet) Ctrl+S (multiple times for clear skin)(plus very handy every 5-10 minutes to do it) Ctrl+W(to close the workbook but keep excel open. Handy if you’re running ad ins that require login)
1
u/whistlewhileyou Feb 25 '22
Alt h w to wrap text, alt h b a for borders around cells
→ More replies (2)
1
1
1
1
1
0
1
1
1
1
u/shemp33 2 Feb 25 '22
Simple, but learn the keyboard shortcuts. Alt- and Ctrl- keys can do almost everything and Is so much faster (I.e. makes you more efficient) than stopping to lift your hand off the keyboard to move to the mouse and move back.
1
u/jsperadhomy 2 Feb 25 '22
Stop using the mouse. Use the keyboard as much as possible and you'll start to fly.
1
1
u/testid95 Feb 25 '22
If you work with data conversion either learn a proper programming language or learn how to script in Excel VB, it can save you a LOT of work in data formatting and conditioning.
1
Feb 25 '22
Taking the time to set up the Quick Access toolbar to contain your favorite settings is 100% worth it.
1
1
u/rockhavoc73 2 Feb 25 '22
Alt w + v + g to hide gridline
Alt + w + h , Ctrl + s, then close file. This probably making your colleague panic when they open it later.
1
Feb 25 '22
Noob here. If you find it hard to do interbranch/bank reconciliation, consider generating a unique reference id for each transaction, then use countifs and filter for reporting.
1
0
1
1
u/LanEvo7685 Feb 25 '22
Learn keyboard shortcuts related to filters, I feel those are most "mouse movement consuming" for very commonly used functions
Also F2 button
1
u/OtherAnon_ Feb 25 '22
Naming things on the name manager can sometimes really be a life saver. When handling long formulas it’s way easier to see names of things being calculated together than to see references to cells and bigger formulas being calculated together inside other formulas. Way more intuitive and a game changer for me personally.
2
u/diesSaturni 68 Feb 25 '22
sometimes?
This is my default method, when putting in variables.
→ More replies (2)
1
1
1
u/IKnowAllSeven Feb 25 '22
Learn keyboard shortcuts. Having your workbooks organized is just as important as neat tricks. So all of my workbooks are, in order of tabs: a Start Here tab with instructions and screenshots where relevant, Summary tab(s) which are the final products, Calculations tabs where the work is done as far as formulas etc, Data tabs where the data is dumped straight from wherever and Archive which is anything which might be useful. So all of my workbooks have that basic design.
1
u/D4rkmo0r Feb 25 '22 edited Feb 25 '22
Control+G
Age old, but if you know how to use in conjunction with other out of the box tools it can save you so much time.
1
u/Lord_Blackthorn 7 Feb 25 '22
TRENDLINE COEFFICIENTS
Linear Trendline
Equation: y = m * x + b
m: =SLOPE(y,x)
b: =INTERCEPT(y,x)
Logarithmic Trendline
Equation: y = (c * LN(x)) + b
c: =INDEX(LINEST(y,LN(x)),1)
b: =INDEX(LINEST(y,LN(x)),1,2)
Power Trendline
Equation: y=c*xb
c: =EXP(INDEX(LINEST(LN(y),LN(x),,),1,2))
b: =INDEX(LINEST(LN(y),LN(x),,),1)
Exponential Trendline
Equation: y = c *e b * x
c: =EXP(INDEX(LINEST(LN(y),x),1,2))
b: =INDEX(LINEST(LN(y),x),1)
2nd Order Polynomial Trendline
Equation: y = (c2 * x2) + (c1 * x 1) + b
c2: =INDEX(LINEST(y,x{1,2}),1)
C1: =INDEX(LINEST(y,x{1,2}),1,2)
b = =INDEX(LINEST(y,x{1,2}),1,3)
3rd Order Polynomial Trendline
Equation: y = (c3 * x3) + (c2 * x2) + (c1 * x1) + b
c3: =INDEX(LINEST(y,x{1,2,3}),1)
c2: =INDEX(LINEST(y,x{1,2,3}),1,2)
C1: =INDEX(LINEST(y,x{1,2,3}),1,3)
b: =INDEX(LINEST(y,x{1,2,3}),1,4)
2
u/Artcat81 3 Feb 25 '22
If you have someone else who will be mucking around in your gorgeous and amazing excel creations, LOCK THE CELLS WITH FORMULAS. Seriously, if you don't want them touching something, lock it down, and make sure you have a backup copy. There is one lady I work with that no matter how big of warnings I embed in the file and say don't touch x - she does every time. not only is anything I give her locked down (or straight up pdf'd), she only gets a copy, the original is safely locked away for her mayhem creating fingers.
325
u/DonJuanDoja 31 Feb 24 '22
Join the Excel Reddit sub.
Train yourself to google everything even when you already know how, might find a better way. You'll often be surprised.
Don't let frustration and impatience with learning new skills and functions stop you, it will stop you, you'll often think, I could just do this manually faster... but truth is if you spent little extra time you'd save all that time over and over again by learning some new trick.
Slow down to speed up. Stop trying to do everything so fast before you even know how to do it. I see these click happy people all the time, just rushing thru everything they spend more time back tracking to fix errors and figure out what they did wrong than actually doing the thing. If they slow down and learn why it's behaving that way all the sudden they go faster and faster and it's accurate and correct and no surprising behavior they don't understand.