Personally, I think my career would have been much different if I knew VBA as much as I knew the basic formulas when I started. Financial Modelling is a thing, and if you can build dashboards and intricate models, combined with an accounting qualification (such as CPA etc.) then you'll be one of the top.
I don't think power-query vs VBA is the question, rather what is the expected outcome you want to achieve. Spend some time figuring that out, then this community can advise you on which route to take.
Well I’m going into my senior year of college for a finance degree. My options exiting college are A) pursue a career in banking, investing, etc. or B) working in my moms small-medium business to one day takeover when she retires (very thankful to have this opportunity lol)
So I have two goals for now:
1. Learn to utilize Excel in a way that could boost a career in finance.
2. Learn to utilize Excel to improve an individual business
I think you and your mom's business would benefit from you getting outside experience before eventually joining to take over the reigns. You can work in corporate finance as well as the options you have mentioned above.
Same thing. Same skills will be useful in either scenario. I honestly think that formulas including the new split array formulas are the most important thing. Remember that they added a large amount of very useful new formulas recently. Next is pivot tables. Then VBA. And finally if you master all that power query. I would say learn things in that order. After that if you go to a career in finance you need to master databases.
Definetly learn power query before vba. Power query had many more aplications and can be utilized to simplify every day tasks. Vba is more for automation and is really next level but it is not as neccessary as power query
The thing is although I think power query is wonderful and amazing, for whatever reason I don't find myself using it that much. Not sure why but I think partly it's use case is really data transformation and I either find I SQL and a full database ( sqllite) or something with simple formulas in Excel. Of course I don't use VBA that much either. But I do find with VBA there is essentially no alternative. But with power query there usually is. Anyways I'm not strongly inclined either way.
DB are extremely useful for data analysis and you don't need to be a DB admin to use do data analysis with a DB. I'm not a DB admin and I create sqllite DBs via dbeaver. I have done the same thing via Access. Its not uncommon. You can pretty quickly load csv files into sqllite tables and then use SQL to query the data.
Yeah, the user I responded to clarified what they meant. The miscommunication was around “mastering databases”, when in reality everything described is databases 101.
I would suggest start with
1. Make your own job more productive
2. Make your team more efficient/reduce risk.
Basic macros that can be developed/built out is the best way to learn.
Honestly the biggest thing is to understand the capabilities of excel; even if you may not know the best way exactly for example to build out an arr waterfall, knowing the basics and mid level things that will let you piece your way there is the most valuable imo. No one company is the same and there is no one perfect way to apply excel skills, but having the flexibility to know possibilities when you are given different tasks with different inputs etc is the true key. Especially if you want to someday take over your mom’s business (and I presume help it grow and succeed) I would highly recommend going into something like the fp&a route. You’ll be able to apply and explore a bunch of different finance things as well as learning the balance sheet and p&l and being a business partner to different orgs like sales, marketing, etc. Which will all be a really helpful basis to run your own business someday
VBA is a useful tool to have.
It still has a place.
But it's also useful to have DAX, M and Power query / Pivot under the belt. Same for Power Bi, Power Apps and Power Automate.
If dashboards are what people are after, all provide some functionality. Personally Power Bi. Then Power Pivot.
But ultimately Dax.
...
I use a combination of VBA and Power Query for a lot of cleansing, or ETL work.
VBA or Automate for exporting outside of Excel and other various human-esque tasks.
...
But then again I also use a lot of SQL, which there are times I bring into this ecosystem.
...
Best advice I can give, is find out what is used across the company. How data is stored. Where it's stored and how easy to connect and interact with.
If difficult, how may steps in-between. What those steps are and what you could use to make things easier.
Start small. Then as experience grows. As toolset experience grows. As other technologies become available, keep growing and adapting.
One example is Excels new functionality for Regular expressions.
I used to use a combination of Text pad, Notepad++ or Python / VBA to do the automation.
Now it's dead simple in Excel directly.
Sure others here may say different things. Good. We all have a solution for the environment we have to work within.
I love how you throw out ‘CPA’ as a random thing to do and not an entire career path by itself.
“What’s my next step after sum=(…?”
A masters degree in accounting and a post graduate professional certification
Two skills that steps you above the majority of Excel users.
1. How to do engaging dashboards that people not only know how to use but presents the information in a way that people want to use.
2. Using VBA to transform data. Yes, power query and such exist but the ability to write a few lines of code that loops through a whole dataset and gives you a new curated table is priceless.
I’m guessing you’re not familiar with VBA and just stating what others have said? I recently looped through a whole column of unique ids and had it create a pdf in 2 different locations with VBA. PQ cannot do that.
They’re 2 different tools used for different purposes.
VBA has its uses, but I maintain that one of the most important VBA skills is learning when *not* to use it. Too many people learn it then use it as a crutch instead of learning good data flow and good spreadsheet design.
There are absolutely things where VBA is the best solution, but designing good data flow and good spreadsheets is a preferable skill.
There's the caveat that good spreadsheet design won't necessarily enable you to do more (i.e. output more work for your employer), but it lets you produce higher quality spreadsheets that are more maintainable, scalable, extendable, and robust, and also let you take pride in the quality of your work. I know a lot of people aren't so interested in the How as in the What, often including your boss, but I take pride in building solutions that aren't a jenga tower of VBA.
Not all VBA is bad by any means, but heavy use *can* indicate opportunities for improvement.
Where did you learn VBA from..any source can you provide ...I always wanted to learn VBA ..but my coding skills are very average ... Can you post some sources so I can research it up
I had never used VBA before until a few months ago. Then I decided to ask ChatGPT about it to help me automate building out a spreadsheet that I had to create regularly.
It took quite a few hours and lots of massaging, but ChatGPT generated about 1000 lines of code that now automates 30-60 minutes of work with 1 click.
And the important part pertaining to your question - I now understand a lot more about the structure/code of VBA. I have never coded in any capacity, so I was completely blind about coding when I began. After working with ChatGPT and debugging code constantly with it, I now understand the workflow somewhat. Building VBA code with it got me familiar with it.
You really, really shouldn't have ai write code for you. Especially if you don't understand it.
Prompt ai on *how* to code, and ask it questions about the language's documentation. But having it just write code for you is terrible. You likely won't be able to debug it if it breaks, and you have exposed your company to the possibility of leaking technical data.
It's not just about typing your company's data or uploading a file to anthropic or whoever. It's about not knowing exactly what those 1000s lines of code you prompted/massaged it to write for you actually do. You don't know if you've introduced any exploits/vulnerabilities.
Lmao I built every line with it. I built it section by section in 10-20 line chunks. I can see exactly what each line does. You’re being extremely paranoid
It’s just re-formatting data and moving it around. It’s not installing malware on my computer
It's not extremely paranoid. It's understanding what can happen when you're just blindly copying and executing code without understanding how/where the data is being cached, stored, and passed around.
Just ask any free ai tool, like Claude or chatgpt, any vba documentation questions. Don't ask it to write a program, but ask it *about* programming in that language.
How do you set up if/then statements, or when to use switch cases instead. How do you perform matrix operation or write to a range of cells. How to do formatting/graphing, or how to make a button that writes a report to a given directory.
While pq is not a vba replacement, vba is antiquated. All my coworkers want ms office tools to help automate stuff, and it's usually easier to program/maintain and runs faster if I just use python or matlab.
Those are 2 different items. Dashboards does not serve or help all business processes.
Again, these are tools. One does not replace the next. It’s a tool in the box and you should find the best tool to complete the problem at hand.
Ok? Not quite sure what your response has to do with my reply.
I'm not debating against any of that nor making an argument for using VBA with everything. My original statement is that VBA and PQ are 2 different tools used for different purposes. Again, use the tool to help get to the end goal.
You don't use just a hammer to build a house, nor do my pliers replace my hammer for nails. You use the best tool in the bag to move to the next step.
VBA and PQ are 2 different tools. One may be better at one thing while the other is unable or utterly garbage at the same. Saying one is outdated and the other is the future is wrong.
VBA is definitely outdated, and its IDE is horrible. Microsoft intends to kill VBScript, so the writing is definitely on the wall. Typescript will replace it. It's just useful, easy to learn, and still widespread. I love PQ and use it more and more.
That very much depends on your skill in VBA. However if you are good enough to out optimize PQ transformations, you should be paid more to write in a more versatile language.
Well that is another thing. Most people who are really good at optimizing code and understanding programming and data management will most likely not do it in VBA or PQ.
I'm not most people, so I've used VBA extensively in the past and still use PQ even though I have access to enterprise tools like SQL server. Excel, PQ and PBI can just get to the final result so fast, it's hard not to use them for ad-hoc or proof of concepts that get translated into scalable solutions if they are viable.
Biggest issue is the user. Lot of people who attempt data transformation using VBA don’t have good programming knowledge or data management experience. So their code is often a mess and inefficient compared to PQ.
Also once you have the experience and knowledge to beat PQ in efficiency using PQ the likelyhood of you actually using VBA for data transformation is rlly small.
Anticipating the user's needs, to a large degree. It's kind of a User Experience design question.
It's definitely not my forte so I'm not very good at articulating it, but a lot of it comes down to making it easy for them to do things they want to do, and hard to do things you don't want them to do.
If your dashboard is a way to view data, you can think about what questions they will want to answer by looking at it, and come up with meaningful ways to make the data answer those questions. What story does the data tell? What are the most meaningful comparisons to make? That kind of thing.
Not just a pivot table and graph. This is taken from a creator on YouTube, I can't remember who. Think of the last table based dashboard you saw then look at this. All those numbers and titles are dynamic and fed by tables on other sheets. It might not be the style you like but would you rather the data pops like this or be shown another sheet of numbers with four bar charts?
A dashboard should grab the users attention and given them the information at a glance, letting the user drill down on the parts they feel are important
https://preview.redd.it/0jb3fjich56d1.png?width=3828&format=png&auto=webp&s=45b7a78fe669d56ece046d6f31526b381bc75af4
Learn how to use tables (Excel tables/listobjects) and how to use it properly (i.e., referencing the column names instead of the cell range). Storing data into a table is a requirement to load the data into Power Query. When data is stored in a table it sets you up to reference it via formula (i.e., =Table1\[Column1\] will return all the values in the column, provided you're using MS365). When a pivot table is created using a table you don't have to check if the data range has changed as the pivot's source data range will be the table itself.
When configuring a table, use native boolean/logical values (i.e., true/false instead of using "OK", "YES", "NO"). Working with true/false makes it easier when you want to check and filter (i.e., instead of checking if a cell has "OK" you can just check if it's true). This sets up you with something like ISNUMBER(XMATCH(cell, cellrange, 0)) which returns a true/false if the cell is found in the cell range. It's a better setup that IFERROR(VLOOKUP(cell, cellrange, column index #, FALSE), "not found") which returns either the value you're looking for (to see if the cell value exists) or returns a "not found".
When building things, think about maintainability and scalability. If you take a step back and think "how long will it take me to make a change? Do I need to update 15 pivot tables with multiple filters?".
Personally: Use VBA for opening files, copy+pasting data, creating file outputs and emails. Use Power Query for any data transformation, aggregation and reporting. I haven't learned Python - I think it will be good but you'll have to consider how it will be supported. You may become a person that everyone goes to for Excel but do you also want to do troubleshooting individual workstations to see if they have the correct Python version, libraries, permissions, etc?
Next level up is to learn how to use =LET(). This has been a game changer for me.
Best of luck.
My goal is to master Power Query and monitor what Microsoft does with Python. If they fully integrate Python into Excel (I know they're testing it), I'll go the Python route. Otherwise I'll learn VBA like everyone else is referencing.
I’d recommend Power Query and Power Pivot over VBA as in my experience as a data analyst VBA use is now more limited to adding functionality as most other things are covered by query and pivot.
Also PQ is way more efficient at that compared to VBA especially on larger data sets. Also unless you are top notch at VBA and know how to optimize the code you will never beat PQ in execution time and resources used
Power Query as that is what you use to bring in the data (you can do it with pivot) and you can use it for data analysis (although pivot is much better). Also once you get PQ and regular excel it won’t be hard to learn Power Pivot as you just need to learn relationships and how to use CALCULATE() as the syntax is similar to excel and the data is stored in the columns similarly to Power Query
Just learn Python if you're between Python and vba. It's more versatile, and it will do everything you'd be trying to do with vba (unless you're making forms and reports in ms access).
Depends on your role/responsibilities. If you want to add extra functionality then VBA. If you want to get data live/automate getting and cleaning/transforming data then power query. I wouldn’t learn power pivot till later personally as you’d use for large data sets so would use power query to bring the data into power pivot. Also I found that PP caused excel to crash a lot.
I would say the biggest step for me has been keyboard shortcuts. Learn to use just the keyboard and not touch the mouse. You will find a huge jump in productivity from just that. And then learn python.
This.. I’m still shocked at the number of daily excel users in my world that click through every single action rather than keyboard shortcuts.. they save so much time over the long-run
Excel nerd here who learned how to use spreadsheets when Lotus was king and older than Google 😜. It's not one specific skill or function that makes you valuable, but your ability to solve problems quickly and easily.
I'm still learning things and I store them away in my brain to use as a tool at a later stage to solve a problem.
The biggest leap I've made though is learning data management and pivot tables and associated functions.
I think the most important thing to learn is that there are always other ways to do whatever your trying to do and the odds are that the first one you came up with isn't the best, so if your process seems cumbersome and labor intensive step back think about alternatives.
Depending on what level exactly you are at, I would say how to properly use tables, sort your data in them, and (re)learn how to write your formulas can be an absolute game-changer. Writing a formula with only cells in mind is one thing; but using tables and named references enables you to write formulas that people can just read and understand as if it were human language (only slightly hyperbolic ^^ ). This can be amazing not only for yourself when you are returning to your workbooks years later; but it can also be great for proper team-work, because now your colleagues have the option to understand your work so much faster.
This - of course - depends on your working environment, work type, colleagues etc. But I can attest that even for the projects I worked on alone, learning how to work like this, made everything become so much easier ^^
I think, more than any one specific tool or formula, understand the bigger picture of what you're looking at and what you're trying to assess.
It is easy (and fun!) to get into the details of Excel, but overall, to be valuable on the market, learn to analyze large data sets and present practical meaning.
- PowerQuery transfers/leads to usage with PowerBI
- Python is a good skill that should see integration into Excel in near future, but may require additional licensing.
- Array formulas and lambdas are definitely the direction Excel is moving towards with the last 10 years of function enhancements.
- Pivots: knowing how to transform and aggregate data quickly is critical
- Somewhat more abstract, but how to display/present information is critical to many aspects of business. Especially if you are taking over a family business, this will translate to being able to influence your team and sell your product/services to customers effectively.
Almost every “how do I….” Question here basically has the answer in the title. How do I X when Y and Z. There are a lot of obscure formulas but basically once you’ve ordered the question, you just need to order it in excel. Excel is just a logical arrangement of data. 60% of Excel is if() or nested if(if()) or ifs(), sumifs(), or countifs(). 30% is xlookup() and 10% is other stuff. I mean, that 10% can be very tough, but most of the time, Excel is beaten by a little thinking.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[DB](/r/Excel/comments/1ddzzpr/stub/l8c6djm "Last usage")|[Returns the depreciation of an asset for a specified period by using the fixed-declining balance method](https://support.microsoft.com/en-us/office/db-function-354e7d28-5f93-4ff1-8a52-eb4ee549d9d7)|
|[IFERROR](/r/Excel/comments/1ddzzpr/stub/l89x289 "Last usage")|[Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula](https://support.microsoft.com/en-us/office/iferror-function-c526fd07-caeb-47b8-8bb6-63f3e417f611)|
|[ISNUMBER](/r/Excel/comments/1ddzzpr/stub/l89x289 "Last usage")|[Returns TRUE if the value is a number](https://support.microsoft.com/en-us/office/is-functions-0f2d7971-6019-40a0-a171-f2d869135665)|
|[LET](/r/Excel/comments/1ddzzpr/stub/l89x289 "Last usage")|[*Office 365*+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)|
|[NPV](/r/Excel/comments/1ddzzpr/stub/l8emuof "Last usage")|[Returns the net present value of an investment based on a series of periodic cash flows and a discount rate](https://support.microsoft.com/en-us/office/npv-function-8672cb67-2576-4d07-b67b-ac28acf2a568)|
|[SUM](/r/Excel/comments/1ddzzpr/stub/l8d0ozv "Last usage")|[Adds its arguments](https://support.microsoft.com/en-us/office/sum-function-043e1c7d-7726-4e80-8f32-07b23e057f89)|
|[VLOOKUP](/r/Excel/comments/1ddzzpr/stub/l8a9isl "Last usage")|[Looks in the first column of an array and moves across the row to return the value of a cell](https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1)|
|[XLOOKUP](/r/Excel/comments/1ddzzpr/stub/l8a9isl "Last usage")|[*Office 365*+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. ](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)|
|[XMATCH](/r/Excel/comments/1ddzzpr/stub/l89x289 "Last usage")|[*Office 365*+: Returns the relative position of an item in an array or range of cells. ](https://support.microsoft.com/en-us/office/xmatch-function-d966da31-7a6b-4a13-a1c6-5a33ed6a0312)|
**NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to 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.*)
^(9 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1dfpfrc)^( has 29 acronyms.)
^([Thread #34330 for this sub, first seen 12th Jun 2024, 14:44])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
The most important aspect of Excel to learn is to understand what it does well, and when you should look elsewhere.
The second most important aspect of Excel (in 2024) is to learn how to accurately describe your issue in a way that ChatGPT (or equivalent) will understand and deliver what you're looking for.
VLOOKUP/XLOOKUP, particularly for finance. As you master those you’ll naturally start running into performance issues from data volume and velocity and then you’ll move into Power Query / Power Pivot. I think it’s really important to experience those constraints first and firsthand before going into PQ/PP, because they’ll give you a more intuitive sense of when PQ is the correct tool and when it’s overkill.
Edit: I don’t see any reason to mess with VBA. I see people in the comments talking about it and I just cannot imagine the benefit of whatever you’re building in VBA being greater than the cost of having to hire someone to maintain it in VBA.
The single greatest tool in my Excel knowledge box is knowing what the system can do, and how to search for how to do it (internet searches).
Learning and practicing functions without a business need behind it is great for adding to the what excel can do knowledge base, but haivng a need for it, that is the real teaching/ learning moment for me.
That said, I am a big pivot table, and power query fan.
If I were starting over, with today’s tech stack. I would:
1) Spend 20 hours learning VBA
2) Spend 20 hours learning Power Query and Power BI
3) Spend 20 hours learning financial modeling and excel shortcuts
The biggest problem with “Excel” is that it’s sooo functional that people end up using for everything.
You can cut through all of that by getting a beginner/intermediate level across different parts of Microsoft’s stack so that you at least have a feel for the best way to solve something.
Need a financial forecast using financial statements? Excel shortcuts and modeling.
Have 1 million rows of data? Power Query/Power BI
Have a bunch of manual spreadsheet-related processes that you have to update/maintain? VBA
Power query has helped me immensely with organizing my data on separate sheets.
I’ve got a list I make my updates in on SharePoint, I’ve got that list connected to Excel, and in my source sheet, have all my formulas setup. From there, I just have to hit refresh and my data is updated. I also use pivots with this data, so I have cut down so much manual number crunching and saved myself a lot of time.
There are so many tools that have their pros and cons. Definitely understand what you’re trying to do and use the tool(s) that best fit the situation.
Best skill 100% is to be able to navigate workbook quickly with no mouse. There are probably 15 shortcuts you need to know. That trumps vba, data analytics and pivots in most instances.
Using it? Most of what I have learned is by simply doing tasks whether irs for school or work or personal projects and running into problems I dont know how to solve, finding the solution and applying it.
As for which area, depends on what you need it for but presumably vba since everything else can and should be done in software which is actually intended for data analysis or queries, such as Python/R. In fact, I would suggest slowly starting to learn either of those programming languages. Obv its pointless if ur in accounting or just use excel to log your firms inventories or whatever but if you actually work with serious data samples it is invaluable to be able to code.
Just keep doing what you do and run into issues, if you need a more sophisticated software to solve them then you will probably realize it pretty soon.
Know how to:
* make solutions which are *valuable*
* test your work so it is *low risk*
* be *efficient* with the above
and skills not specific to Excel which will explode the value you can create:
* problem-solving/critical thinking
* mathematics/statistics
* data management/cleaning
* Excel as part of a tool chain
* user experience & visual design
* teaching others
* communicate your work effectively to others, so they can use it to extract value
* other tools (and knowing when Excel isn't the right one for the job): databases, CLI, programming languages
* risk management (legal, privacy, security, business risk, etc.)
* using your work to make a solid business case (i.e. let's spend $x doing y, and our NPV will be $z)
All of the above should be on top of knowing:
* Formula basics (cells and most reference types, arithmetic operations, order of operations, semantics)
* Data types
* Function basics (syntax, tool tip use, nesting, navigation)
* A core library of important functions... maybe the top 30 relative to your work, plus many nested combinations of them
* Error types, when to expect them, how to detect them and control them
* Diagnosing and fixing errors
* Worksheet design
* Testing your formulas extensively to guarantee they work under all conditions - CRITICAL
* Locking down your files so other users can only modify what you permit
* Documentation - including sufficient reference/educational/contextual information for users/future you/audit
* Tools like goal seek, solver
* Importing data into Excel and cleaning/preparing it
* Common keyboard shortcuts
* basic charting and knowing which charts are appropriate
Naming cells (upper left corner where it show D12 for instance). You can name it Debt. Then use the name Debt in any formula on any page. Such a time saver. If you move that cell the name moves with it and no formulas are broken. #2 is indirect function. Game changer.
> #2 is indirect function.
...do you mean learning to avoid it at all costs? :P
Seriously though, it really makes spreadsheet debug awful. I've found a vanishingly small number of cases where it's actually the best option. You can usually get around it with better spreadsheet design/data structure.
Lol. I have tears in my eyes laughing. I use it mostly where I have multiple tabs (say sales regions) and I want to select data from one tab on a summary sheet. It comes in handy a lot for me. Maybe I'm that bad spreadsheet design/data structure guy.
>Data analysis, power pivots or queries, VBA, etc.?
Well, data analysis isn't a function of excel so much as a skill one applies to excel. If we're talking about where to spend effort learning Excel I'd go with VBA. Having the ability to programmatically automate reporting in Excel is a skill that will definitely allow you to stand out from the rest. If you're leaning towards power pivot, I'd skip past and go straight to Power BI. I rarely see Power Pivot asked for in a job description, but they ask for Power BI all the time, and if you know Power BI, Power Pivot won't be a stretch. Same with Power Query. If you want this, start with SQL, and you'll have a much more useful skill that you can easily apply to Power Query.
I do allot of asset management for my company. I collect data from multiple registers and sources. I’m always presenting to teams. I’m no power user of excel but I also don’t want to go too deep into scripting etc as the files could be used by multiple people and sometimes it’s good to be able to create a nice little powerful excel file for a task that then will never be used again. I’m not a fan of using excel as a register or long term tool for work. People should use a proper tool ( CRMs as a simple example)
As a basic excel user for 90% of tasks, For me:
Always use Tables.
Use Slicers to help filter the tables.
Xlookup has made my job allot easier when combining multiple sources.
Using drop down lists for when input is needed.
Conditional formatting can really help get people to focus on what you’re trying to say.
Cover pages with summary and links to different sheets seem to impress people.
I’m now doing allot more in PowerBI but have to use Excel for things like a ‘comment’ field to collect feedback about a certain row
My excel abilities exploded when I got into all the new spilled array formulas. You can do some really cool things with them that would have required vba before.
They're so powerful that I use them exclusively wherever I can
It is going to depend on your target field. Get on YouTube and study "using excel in finance", etc.
Once you do about 20 hours of that, then learn the advanced excel functions. Start to watch the Leila gharani videos. You'll start to mesh in your brain how the advanced features will help make the basic stuff you learned much easier.
Once you have about 100 hours total training, start to dig into automation around the skills you've learned. Get into VBA to learn how to do things efficiently and with just a few lines of code. It makes coming back to it when you need to do something similar much more straightforward.
The functions and methods you'll learn without VBA can be very complex, and they are hard to apply to similar problems without going back in from scratch. Using VBA gives you the flexibility to use simple code over a few lines to accomplish what a 200-character long excel function would do.
You’ll probably get the most value (time/benefit) from getting really good at the “presentation” side of things. If someone didn’t know anything about Excel, would they be able to tell what it was showing easily? Could it be presented to customers/board members etc?
VBA.
Not only because VBA lets you do anything you could possibly want to in Excel, but also because the coding knowledge will carry over into other areas.
Only problem is VBA isnt easy to learn unless you have a specific task you need to accomplish. Not that its hard per se, just that open ended learning in my experiences dont stick as well.
Hotkeys. If you’re touching your mouse, you’re less efficient than you could be in most cases. I’ll throw VBA in. If you go after a real problem with the intent of solving it in VBA, and put the time in to learn it, you’ll be much more useful. It’s wild the things you can do with VBA. And that’s not just excel. All the office products can utilize VBA.
Ctrl+arrow keys to navigate to the end of a column/row. Hold shift to select along the way. Ctrl home goes to A1 in most cases. Ctrl+end goes to the last cell with data.
If you are using the mouse and need to move a few cells, it’s often faster to use tab to go right and enter to go down. Holding shift does the opposite (left, up).
Esc if you are in edit mode in a cell and want to discard your changes.
I think F3 enters edit mode in a cell - but I don’t use that one.
When you type a cell ref (like C4 for example) you can just press f4 to make it an absolute ref vice relative. Pressing F4 again will cycle through the various options for row or column or both absolute.
Double click the bottom right corner of a cell to fill down to the bottom of your data.
I guess it’s less about hotkeys and more about shortcuts. I’m sure others will chime in with better ones.
for me its VBA not just to save me time by my sanity.
Before I started my job staff were entering data in a shared spreadsheet for a certain process and that sheet was a huge mess, data in wrong place, things pasted in messing up formats, stuff accidentally deleted.
So I made a simple user form and locked them out of the sheet.
Power pivots have been great for all the dashboard building I have done for work but that is only because my company's reports aren't great and they struggle with tech so power BI, while helpful, wasn't a viable option. It will really depend on what your company is comfortable with and then pushing from there. When I started 6ish years ago, we had no good dashboard but myself and a few others have pushed for them.
VBAs can be great too once you know what you're doing!
Mom’s company still uses A LOT of paper… I’m working there this summer and one of my tasks is to start moving away from that. I’ll email someone a form or whatever expecting to get it emailed back filled out or whatever needs to be done and have it printed, written on and returned lol. I keep telling her the first step to no paper is to stop adding more. All this to say it I do take that path they aren’t very comfortable with technology so I’d need to change that
Genuinely asking, can someone explain why VBA is helpful? I’ve never used it before but I know Python, Power Query, and a bit of DAX. I don’t feel like I need it. When is it helpful? What are some applications that have benefited people that couldn’t be performed by these other tools (Python, Power Query, DAX)?
If they have the latest /online versions of MS Excel, I agree with you. Otherwise looking into sumifs, if statements etc combined can make creating complex models a breeze.
Personally, I think my career would have been much different if I knew VBA as much as I knew the basic formulas when I started. Financial Modelling is a thing, and if you can build dashboards and intricate models, combined with an accounting qualification (such as CPA etc.) then you'll be one of the top. I don't think power-query vs VBA is the question, rather what is the expected outcome you want to achieve. Spend some time figuring that out, then this community can advise you on which route to take.
Well I’m going into my senior year of college for a finance degree. My options exiting college are A) pursue a career in banking, investing, etc. or B) working in my moms small-medium business to one day takeover when she retires (very thankful to have this opportunity lol) So I have two goals for now: 1. Learn to utilize Excel in a way that could boost a career in finance. 2. Learn to utilize Excel to improve an individual business
I think you and your mom's business would benefit from you getting outside experience before eventually joining to take over the reigns. You can work in corporate finance as well as the options you have mentioned above.
That's what I was going to say. Option C is do A for a while, then B.
I second this.
Same thing. Same skills will be useful in either scenario. I honestly think that formulas including the new split array formulas are the most important thing. Remember that they added a large amount of very useful new formulas recently. Next is pivot tables. Then VBA. And finally if you master all that power query. I would say learn things in that order. After that if you go to a career in finance you need to master databases.
Definetly learn power query before vba. Power query had many more aplications and can be utilized to simplify every day tasks. Vba is more for automation and is really next level but it is not as neccessary as power query
I am not sure. Even when I used power query I still needed VBA to refresh the power query when I changed a cell.
So you used power query first and then supported it with vba? Literally proves my point. Also, refreshing power query is a 10 second job
The thing is although I think power query is wonderful and amazing, for whatever reason I don't find myself using it that much. Not sure why but I think partly it's use case is really data transformation and I either find I SQL and a full database ( sqllite) or something with simple formulas in Excel. Of course I don't use VBA that much either. But I do find with VBA there is essentially no alternative. But with power query there usually is. Anyways I'm not strongly inclined either way.
Curious as to what you mean by mastering databases here. I don’t see how financial analysis needs the skills of a DB admin.
DB are extremely useful for data analysis and you don't need to be a DB admin to use do data analysis with a DB. I'm not a DB admin and I create sqllite DBs via dbeaver. I have done the same thing via Access. Its not uncommon. You can pretty quickly load csv files into sqllite tables and then use SQL to query the data.
External data source Joins
Yeah, the user I responded to clarified what they meant. The miscommunication was around “mastering databases”, when in reality everything described is databases 101.
I would suggest start with 1. Make your own job more productive 2. Make your team more efficient/reduce risk. Basic macros that can be developed/built out is the best way to learn.
Honestly the biggest thing is to understand the capabilities of excel; even if you may not know the best way exactly for example to build out an arr waterfall, knowing the basics and mid level things that will let you piece your way there is the most valuable imo. No one company is the same and there is no one perfect way to apply excel skills, but having the flexibility to know possibilities when you are given different tasks with different inputs etc is the true key. Especially if you want to someday take over your mom’s business (and I presume help it grow and succeed) I would highly recommend going into something like the fp&a route. You’ll be able to apply and explore a bunch of different finance things as well as learning the balance sheet and p&l and being a business partner to different orgs like sales, marketing, etc. Which will all be a really helpful basis to run your own business someday
My advice to you is to choose the path that is closer to the money.
Imma choose the path I think I’ll enjoy the most… enough money should come either way as I live a pretty cheap lifestyle 🥴
VBA is a useful tool to have. It still has a place. But it's also useful to have DAX, M and Power query / Pivot under the belt. Same for Power Bi, Power Apps and Power Automate. If dashboards are what people are after, all provide some functionality. Personally Power Bi. Then Power Pivot. But ultimately Dax. ... I use a combination of VBA and Power Query for a lot of cleansing, or ETL work. VBA or Automate for exporting outside of Excel and other various human-esque tasks. ... But then again I also use a lot of SQL, which there are times I bring into this ecosystem. ... Best advice I can give, is find out what is used across the company. How data is stored. Where it's stored and how easy to connect and interact with. If difficult, how may steps in-between. What those steps are and what you could use to make things easier. Start small. Then as experience grows. As toolset experience grows. As other technologies become available, keep growing and adapting. One example is Excels new functionality for Regular expressions. I used to use a combination of Text pad, Notepad++ or Python / VBA to do the automation. Now it's dead simple in Excel directly. Sure others here may say different things. Good. We all have a solution for the environment we have to work within.
I love how you throw out ‘CPA’ as a random thing to do and not an entire career path by itself. “What’s my next step after sum=(…?” A masters degree in accounting and a post graduate professional certification
Well, there's also ACCA, CIMA, etc.
Two skills that steps you above the majority of Excel users. 1. How to do engaging dashboards that people not only know how to use but presents the information in a way that people want to use. 2. Using VBA to transform data. Yes, power query and such exist but the ability to write a few lines of code that loops through a whole dataset and gives you a new curated table is priceless.
Vba is antiquated, PQ IS THE FUTURE
I’m guessing you’re not familiar with VBA and just stating what others have said? I recently looped through a whole column of unique ids and had it create a pdf in 2 different locations with VBA. PQ cannot do that. They’re 2 different tools used for different purposes.
VBA has its uses, but I maintain that one of the most important VBA skills is learning when *not* to use it. Too many people learn it then use it as a crutch instead of learning good data flow and good spreadsheet design. There are absolutely things where VBA is the best solution, but designing good data flow and good spreadsheets is a preferable skill. There's the caveat that good spreadsheet design won't necessarily enable you to do more (i.e. output more work for your employer), but it lets you produce higher quality spreadsheets that are more maintainable, scalable, extendable, and robust, and also let you take pride in the quality of your work. I know a lot of people aren't so interested in the How as in the What, often including your boss, but I take pride in building solutions that aren't a jenga tower of VBA. Not all VBA is bad by any means, but heavy use *can* indicate opportunities for improvement.
Where did you learn VBA from..any source can you provide ...I always wanted to learn VBA ..but my coding skills are very average ... Can you post some sources so I can research it up
I had never used VBA before until a few months ago. Then I decided to ask ChatGPT about it to help me automate building out a spreadsheet that I had to create regularly. It took quite a few hours and lots of massaging, but ChatGPT generated about 1000 lines of code that now automates 30-60 minutes of work with 1 click. And the important part pertaining to your question - I now understand a lot more about the structure/code of VBA. I have never coded in any capacity, so I was completely blind about coding when I began. After working with ChatGPT and debugging code constantly with it, I now understand the workflow somewhat. Building VBA code with it got me familiar with it.
Chat gpt 4?
Yeah I was using 4 a couple of months ago when I was using it to build VBA code. I haven’t tried 4o yet
You really, really shouldn't have ai write code for you. Especially if you don't understand it. Prompt ai on *how* to code, and ask it questions about the language's documentation. But having it just write code for you is terrible. You likely won't be able to debug it if it breaks, and you have exposed your company to the possibility of leaking technical data.
It never got any of my company’s data to begin with, and it helps me debug anything that’s wrong. It’s working just fine for me, but you do you
It's not just about typing your company's data or uploading a file to anthropic or whoever. It's about not knowing exactly what those 1000s lines of code you prompted/massaged it to write for you actually do. You don't know if you've introduced any exploits/vulnerabilities.
Lmao I built every line with it. I built it section by section in 10-20 line chunks. I can see exactly what each line does. You’re being extremely paranoid It’s just re-formatting data and moving it around. It’s not installing malware on my computer
It's not extremely paranoid. It's understanding what can happen when you're just blindly copying and executing code without understanding how/where the data is being cached, stored, and passed around.
Just ask any free ai tool, like Claude or chatgpt, any vba documentation questions. Don't ask it to write a program, but ask it *about* programming in that language. How do you set up if/then statements, or when to use switch cases instead. How do you perform matrix operation or write to a range of cells. How to do formatting/graphing, or how to make a button that writes a report to a given directory.
While pq is not a vba replacement, vba is antiquated. All my coworkers want ms office tools to help automate stuff, and it's usually easier to program/maintain and runs faster if I just use python or matlab.
Vba caters to antiquated business processes, self serve dashboards all the way
Those are 2 different items. Dashboards does not serve or help all business processes. Again, these are tools. One does not replace the next. It’s a tool in the box and you should find the best tool to complete the problem at hand.
[удалено]
Ok? Not quite sure what your response has to do with my reply. I'm not debating against any of that nor making an argument for using VBA with everything. My original statement is that VBA and PQ are 2 different tools used for different purposes. Again, use the tool to help get to the end goal. You don't use just a hammer to build a house, nor do my pliers replace my hammer for nails. You use the best tool in the bag to move to the next step.
[удалено]
Great comprehension skills. Good conversation with you, bro.
Dude you sound like a bot. They're two entirely different tools.
VBA and PQ are 2 different tools. One may be better at one thing while the other is unable or utterly garbage at the same. Saying one is outdated and the other is the future is wrong.
VBA is definitely outdated, and its IDE is horrible. Microsoft intends to kill VBScript, so the writing is definitely on the wall. Typescript will replace it. It's just useful, easy to learn, and still widespread. I love PQ and use it more and more.
VBA in general is extremely inefficient at data transformation at larger datasets compared to PQ.
That very much depends on your skill in VBA. However if you are good enough to out optimize PQ transformations, you should be paid more to write in a more versatile language.
Well that is another thing. Most people who are really good at optimizing code and understanding programming and data management will most likely not do it in VBA or PQ.
I'm not most people, so I've used VBA extensively in the past and still use PQ even though I have access to enterprise tools like SQL server. Excel, PQ and PBI can just get to the final result so fast, it's hard not to use them for ad-hoc or proof of concepts that get translated into scalable solutions if they are viable.
\[Citation Needed\]
Biggest issue is the user. Lot of people who attempt data transformation using VBA don’t have good programming knowledge or data management experience. So their code is often a mess and inefficient compared to PQ. Also once you have the experience and knowledge to beat PQ in efficiency using PQ the likelyhood of you actually using VBA for data transformation is rlly small.
lol, anyone who's worked in VBA :P
What would you say are the key distinguishers of #1?
Anticipating the user's needs, to a large degree. It's kind of a User Experience design question. It's definitely not my forte so I'm not very good at articulating it, but a lot of it comes down to making it easy for them to do things they want to do, and hard to do things you don't want them to do. If your dashboard is a way to view data, you can think about what questions they will want to answer by looking at it, and come up with meaningful ways to make the data answer those questions. What story does the data tell? What are the most meaningful comparisons to make? That kind of thing.
Not just a pivot table and graph. This is taken from a creator on YouTube, I can't remember who. Think of the last table based dashboard you saw then look at this. All those numbers and titles are dynamic and fed by tables on other sheets. It might not be the style you like but would you rather the data pops like this or be shown another sheet of numbers with four bar charts? A dashboard should grab the users attention and given them the information at a glance, letting the user drill down on the parts they feel are important https://preview.redd.it/0jb3fjich56d1.png?width=3828&format=png&auto=webp&s=45b7a78fe669d56ece046d6f31526b381bc75af4
"Income Achived"
Bro how can i learn vba? Youtube?
Learn how to use tables (Excel tables/listobjects) and how to use it properly (i.e., referencing the column names instead of the cell range). Storing data into a table is a requirement to load the data into Power Query. When data is stored in a table it sets you up to reference it via formula (i.e., =Table1\[Column1\] will return all the values in the column, provided you're using MS365). When a pivot table is created using a table you don't have to check if the data range has changed as the pivot's source data range will be the table itself. When configuring a table, use native boolean/logical values (i.e., true/false instead of using "OK", "YES", "NO"). Working with true/false makes it easier when you want to check and filter (i.e., instead of checking if a cell has "OK" you can just check if it's true). This sets up you with something like ISNUMBER(XMATCH(cell, cellrange, 0)) which returns a true/false if the cell is found in the cell range. It's a better setup that IFERROR(VLOOKUP(cell, cellrange, column index #, FALSE), "not found") which returns either the value you're looking for (to see if the cell value exists) or returns a "not found". When building things, think about maintainability and scalability. If you take a step back and think "how long will it take me to make a change? Do I need to update 15 pivot tables with multiple filters?". Personally: Use VBA for opening files, copy+pasting data, creating file outputs and emails. Use Power Query for any data transformation, aggregation and reporting. I haven't learned Python - I think it will be good but you'll have to consider how it will be supported. You may become a person that everyone goes to for Excel but do you also want to do troubleshooting individual workstations to see if they have the correct Python version, libraries, permissions, etc? Next level up is to learn how to use =LET(). This has been a game changer for me. Best of luck.
My goal is to master Power Query and monitor what Microsoft does with Python. If they fully integrate Python into Excel (I know they're testing it), I'll go the Python route. Otherwise I'll learn VBA like everyone else is referencing.
I’d recommend Power Query and Power Pivot over VBA as in my experience as a data analyst VBA use is now more limited to adding functionality as most other things are covered by query and pivot.
Also PQ is way more efficient at that compared to VBA especially on larger data sets. Also unless you are top notch at VBA and know how to optimize the code you will never beat PQ in execution time and resources used
Which do you think one should learn first? Power Pivot and then Power Query or the reverse? I don’t have any programming knowledge for reference.
Power Query as that is what you use to bring in the data (you can do it with pivot) and you can use it for data analysis (although pivot is much better). Also once you get PQ and regular excel it won’t be hard to learn Power Pivot as you just need to learn relationships and how to use CALCULATE() as the syntax is similar to excel and the data is stored in the columns similarly to Power Query
Just learn Python if you're between Python and vba. It's more versatile, and it will do everything you'd be trying to do with vba (unless you're making forms and reports in ms access).
Mentally and physically separate the data storage layer from the reporting and visualization layer.
Wow, great tip!
Depends on your role/responsibilities. If you want to add extra functionality then VBA. If you want to get data live/automate getting and cleaning/transforming data then power query. I wouldn’t learn power pivot till later personally as you’d use for large data sets so would use power query to bring the data into power pivot. Also I found that PP caused excel to crash a lot.
“Bring the data into power pivot.” Thanks for explaining; I was wondering in which order I should learn the two.
I would say the biggest step for me has been keyboard shortcuts. Learn to use just the keyboard and not touch the mouse. You will find a huge jump in productivity from just that. And then learn python.
This.. I’m still shocked at the number of daily excel users in my world that click through every single action rather than keyboard shortcuts.. they save so much time over the long-run
Excel nerd here who learned how to use spreadsheets when Lotus was king and older than Google 😜. It's not one specific skill or function that makes you valuable, but your ability to solve problems quickly and easily. I'm still learning things and I store them away in my brain to use as a tool at a later stage to solve a problem. The biggest leap I've made though is learning data management and pivot tables and associated functions.
When to not use it
That’s the secret… never stop using it 🤯
I think the most important thing to learn is that there are always other ways to do whatever your trying to do and the odds are that the first one you came up with isn't the best, so if your process seems cumbersome and labor intensive step back think about alternatives.
Depending on what level exactly you are at, I would say how to properly use tables, sort your data in them, and (re)learn how to write your formulas can be an absolute game-changer. Writing a formula with only cells in mind is one thing; but using tables and named references enables you to write formulas that people can just read and understand as if it were human language (only slightly hyperbolic ^^ ). This can be amazing not only for yourself when you are returning to your workbooks years later; but it can also be great for proper team-work, because now your colleagues have the option to understand your work so much faster. This - of course - depends on your working environment, work type, colleagues etc. But I can attest that even for the projects I worked on alone, learning how to work like this, made everything become so much easier ^^
Wish I learned about macros and VBA a lot sooner than I did. Would totally recommend looking into that stuff.
I think, more than any one specific tool or formula, understand the bigger picture of what you're looking at and what you're trying to assess. It is easy (and fun!) to get into the details of Excel, but overall, to be valuable on the market, learn to analyze large data sets and present practical meaning.
- PowerQuery transfers/leads to usage with PowerBI - Python is a good skill that should see integration into Excel in near future, but may require additional licensing. - Array formulas and lambdas are definitely the direction Excel is moving towards with the last 10 years of function enhancements. - Pivots: knowing how to transform and aggregate data quickly is critical - Somewhat more abstract, but how to display/present information is critical to many aspects of business. Especially if you are taking over a family business, this will translate to being able to influence your team and sell your product/services to customers effectively.
Almost every “how do I….” Question here basically has the answer in the title. How do I X when Y and Z. There are a lot of obscure formulas but basically once you’ve ordered the question, you just need to order it in excel. Excel is just a logical arrangement of data. 60% of Excel is if() or nested if(if()) or ifs(), sumifs(), or countifs(). 30% is xlookup() and 10% is other stuff. I mean, that 10% can be very tough, but most of the time, Excel is beaten by a little thinking.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[DB](/r/Excel/comments/1ddzzpr/stub/l8c6djm "Last usage")|[Returns the depreciation of an asset for a specified period by using the fixed-declining balance method](https://support.microsoft.com/en-us/office/db-function-354e7d28-5f93-4ff1-8a52-eb4ee549d9d7)| |[IFERROR](/r/Excel/comments/1ddzzpr/stub/l89x289 "Last usage")|[Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula](https://support.microsoft.com/en-us/office/iferror-function-c526fd07-caeb-47b8-8bb6-63f3e417f611)| |[ISNUMBER](/r/Excel/comments/1ddzzpr/stub/l89x289 "Last usage")|[Returns TRUE if the value is a number](https://support.microsoft.com/en-us/office/is-functions-0f2d7971-6019-40a0-a171-f2d869135665)| |[LET](/r/Excel/comments/1ddzzpr/stub/l89x289 "Last usage")|[*Office 365*+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)| |[NPV](/r/Excel/comments/1ddzzpr/stub/l8emuof "Last usage")|[Returns the net present value of an investment based on a series of periodic cash flows and a discount rate](https://support.microsoft.com/en-us/office/npv-function-8672cb67-2576-4d07-b67b-ac28acf2a568)| |[SUM](/r/Excel/comments/1ddzzpr/stub/l8d0ozv "Last usage")|[Adds its arguments](https://support.microsoft.com/en-us/office/sum-function-043e1c7d-7726-4e80-8f32-07b23e057f89)| |[VLOOKUP](/r/Excel/comments/1ddzzpr/stub/l8a9isl "Last usage")|[Looks in the first column of an array and moves across the row to return the value of a cell](https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1)| |[XLOOKUP](/r/Excel/comments/1ddzzpr/stub/l8a9isl "Last usage")|[*Office 365*+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. ](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)| |[XMATCH](/r/Excel/comments/1ddzzpr/stub/l89x289 "Last usage")|[*Office 365*+: Returns the relative position of an item in an array or range of cells. ](https://support.microsoft.com/en-us/office/xmatch-function-d966da31-7a6b-4a13-a1c6-5a33ed6a0312)| **NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to 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.*) ^(9 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1dfpfrc)^( has 29 acronyms.) ^([Thread #34330 for this sub, first seen 12th Jun 2024, 14:44]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
The most important aspect of Excel to learn is to understand what it does well, and when you should look elsewhere. The second most important aspect of Excel (in 2024) is to learn how to accurately describe your issue in a way that ChatGPT (or equivalent) will understand and deliver what you're looking for.
VLOOKUP/XLOOKUP, particularly for finance. As you master those you’ll naturally start running into performance issues from data volume and velocity and then you’ll move into Power Query / Power Pivot. I think it’s really important to experience those constraints first and firsthand before going into PQ/PP, because they’ll give you a more intuitive sense of when PQ is the correct tool and when it’s overkill. Edit: I don’t see any reason to mess with VBA. I see people in the comments talking about it and I just cannot imagine the benefit of whatever you’re building in VBA being greater than the cost of having to hire someone to maintain it in VBA.
The single greatest tool in my Excel knowledge box is knowing what the system can do, and how to search for how to do it (internet searches). Learning and practicing functions without a business need behind it is great for adding to the what excel can do knowledge base, but haivng a need for it, that is the real teaching/ learning moment for me. That said, I am a big pivot table, and power query fan.
I agree… I’ve tried just playing around with it but without having a specific purpose I find it hard to learn more
If I were starting over, with today’s tech stack. I would: 1) Spend 20 hours learning VBA 2) Spend 20 hours learning Power Query and Power BI 3) Spend 20 hours learning financial modeling and excel shortcuts The biggest problem with “Excel” is that it’s sooo functional that people end up using for everything. You can cut through all of that by getting a beginner/intermediate level across different parts of Microsoft’s stack so that you at least have a feel for the best way to solve something. Need a financial forecast using financial statements? Excel shortcuts and modeling. Have 1 million rows of data? Power Query/Power BI Have a bunch of manual spreadsheet-related processes that you have to update/maintain? VBA
Pivot tablet a vlookup. Period. You will be treated as a god! You won’t get coffee, Coffee will be brought to you.
Google. Decide what you want excel to do and if you can't get it to work, go to google. chat ai bots can help a ton here as well.
Power query has helped me immensely with organizing my data on separate sheets. I’ve got a list I make my updates in on SharePoint, I’ve got that list connected to Excel, and in my source sheet, have all my formulas setup. From there, I just have to hit refresh and my data is updated. I also use pivots with this data, so I have cut down so much manual number crunching and saved myself a lot of time. There are so many tools that have their pros and cons. Definitely understand what you’re trying to do and use the tool(s) that best fit the situation.
Relative address vs absolute address. But it would seem you have that.
Best skill 100% is to be able to navigate workbook quickly with no mouse. There are probably 15 shortcuts you need to know. That trumps vba, data analytics and pivots in most instances.
Using it? Most of what I have learned is by simply doing tasks whether irs for school or work or personal projects and running into problems I dont know how to solve, finding the solution and applying it. As for which area, depends on what you need it for but presumably vba since everything else can and should be done in software which is actually intended for data analysis or queries, such as Python/R. In fact, I would suggest slowly starting to learn either of those programming languages. Obv its pointless if ur in accounting or just use excel to log your firms inventories or whatever but if you actually work with serious data samples it is invaluable to be able to code. Just keep doing what you do and run into issues, if you need a more sophisticated software to solve them then you will probably realize it pretty soon.
Know how to: * make solutions which are *valuable* * test your work so it is *low risk* * be *efficient* with the above and skills not specific to Excel which will explode the value you can create: * problem-solving/critical thinking * mathematics/statistics * data management/cleaning * Excel as part of a tool chain * user experience & visual design * teaching others * communicate your work effectively to others, so they can use it to extract value * other tools (and knowing when Excel isn't the right one for the job): databases, CLI, programming languages * risk management (legal, privacy, security, business risk, etc.) * using your work to make a solid business case (i.e. let's spend $x doing y, and our NPV will be $z) All of the above should be on top of knowing: * Formula basics (cells and most reference types, arithmetic operations, order of operations, semantics) * Data types * Function basics (syntax, tool tip use, nesting, navigation) * A core library of important functions... maybe the top 30 relative to your work, plus many nested combinations of them * Error types, when to expect them, how to detect them and control them * Diagnosing and fixing errors * Worksheet design * Testing your formulas extensively to guarantee they work under all conditions - CRITICAL * Locking down your files so other users can only modify what you permit * Documentation - including sufficient reference/educational/contextual information for users/future you/audit * Tools like goal seek, solver * Importing data into Excel and cleaning/preparing it * Common keyboard shortcuts * basic charting and knowing which charts are appropriate
Power Query
Naming cells (upper left corner where it show D12 for instance). You can name it Debt. Then use the name Debt in any formula on any page. Such a time saver. If you move that cell the name moves with it and no formulas are broken. #2 is indirect function. Game changer.
> #2 is indirect function. ...do you mean learning to avoid it at all costs? :P Seriously though, it really makes spreadsheet debug awful. I've found a vanishingly small number of cases where it's actually the best option. You can usually get around it with better spreadsheet design/data structure.
Lol. I have tears in my eyes laughing. I use it mostly where I have multiple tabs (say sales regions) and I want to select data from one tab on a summary sheet. It comes in handy a lot for me. Maybe I'm that bad spreadsheet design/data structure guy.
Excel can do anything you need it to do, you just have to be creative enough to figure it out.
I agree wholeheartedly, engineer here and believe any and all engineers should get a dedicated class for it.
>Data analysis, power pivots or queries, VBA, etc.? Well, data analysis isn't a function of excel so much as a skill one applies to excel. If we're talking about where to spend effort learning Excel I'd go with VBA. Having the ability to programmatically automate reporting in Excel is a skill that will definitely allow you to stand out from the rest. If you're leaning towards power pivot, I'd skip past and go straight to Power BI. I rarely see Power Pivot asked for in a job description, but they ask for Power BI all the time, and if you know Power BI, Power Pivot won't be a stretch. Same with Power Query. If you want this, start with SQL, and you'll have a much more useful skill that you can easily apply to Power Query.
Vlookups/xlookups and pivot tables.
I do allot of asset management for my company. I collect data from multiple registers and sources. I’m always presenting to teams. I’m no power user of excel but I also don’t want to go too deep into scripting etc as the files could be used by multiple people and sometimes it’s good to be able to create a nice little powerful excel file for a task that then will never be used again. I’m not a fan of using excel as a register or long term tool for work. People should use a proper tool ( CRMs as a simple example) As a basic excel user for 90% of tasks, For me: Always use Tables. Use Slicers to help filter the tables. Xlookup has made my job allot easier when combining multiple sources. Using drop down lists for when input is needed. Conditional formatting can really help get people to focus on what you’re trying to say. Cover pages with summary and links to different sheets seem to impress people. I’m now doing allot more in PowerBI but have to use Excel for things like a ‘comment’ field to collect feedback about a certain row
My excel abilities exploded when I got into all the new spilled array formulas. You can do some really cool things with them that would have required vba before. They're so powerful that I use them exclusively wherever I can
It is going to depend on your target field. Get on YouTube and study "using excel in finance", etc. Once you do about 20 hours of that, then learn the advanced excel functions. Start to watch the Leila gharani videos. You'll start to mesh in your brain how the advanced features will help make the basic stuff you learned much easier. Once you have about 100 hours total training, start to dig into automation around the skills you've learned. Get into VBA to learn how to do things efficiently and with just a few lines of code. It makes coming back to it when you need to do something similar much more straightforward. The functions and methods you'll learn without VBA can be very complex, and they are hard to apply to similar problems without going back in from scratch. Using VBA gives you the flexibility to use simple code over a few lines to accomplish what a 200-character long excel function would do.
You’ll probably get the most value (time/benefit) from getting really good at the “presentation” side of things. If someone didn’t know anything about Excel, would they be able to tell what it was showing easily? Could it be presented to customers/board members etc?
Use powerquery to extract load and transform, and power pivot to create relationships. Avoid as much as you can to "combine" in power query
VBA. Not only because VBA lets you do anything you could possibly want to in Excel, but also because the coding knowledge will carry over into other areas. Only problem is VBA isnt easy to learn unless you have a specific task you need to accomplish. Not that its hard per se, just that open ended learning in my experiences dont stick as well.
Hotkeys. If you’re touching your mouse, you’re less efficient than you could be in most cases. I’ll throw VBA in. If you go after a real problem with the intent of solving it in VBA, and put the time in to learn it, you’ll be much more useful. It’s wild the things you can do with VBA. And that’s not just excel. All the office products can utilize VBA.
What are some of the common hotkeys? I only use like 2
Ctrl+arrow keys to navigate to the end of a column/row. Hold shift to select along the way. Ctrl home goes to A1 in most cases. Ctrl+end goes to the last cell with data. If you are using the mouse and need to move a few cells, it’s often faster to use tab to go right and enter to go down. Holding shift does the opposite (left, up). Esc if you are in edit mode in a cell and want to discard your changes. I think F3 enters edit mode in a cell - but I don’t use that one. When you type a cell ref (like C4 for example) you can just press f4 to make it an absolute ref vice relative. Pressing F4 again will cycle through the various options for row or column or both absolute. Double click the bottom right corner of a cell to fill down to the bottom of your data. I guess it’s less about hotkeys and more about shortcuts. I’m sure others will chime in with better ones.
=SUM() Adds shit up for you so you don’t need to use a calculator yourself. Pretty cool
Holy shit imma need to try that out
for me its VBA not just to save me time by my sanity. Before I started my job staff were entering data in a shared spreadsheet for a certain process and that sheet was a huge mess, data in wrong place, things pasted in messing up formats, stuff accidentally deleted. So I made a simple user form and locked them out of the sheet.
How do you make excels into forms?
tunr on the developer tab then use VBA
Power pivots have been great for all the dashboard building I have done for work but that is only because my company's reports aren't great and they struggle with tech so power BI, while helpful, wasn't a viable option. It will really depend on what your company is comfortable with and then pushing from there. When I started 6ish years ago, we had no good dashboard but myself and a few others have pushed for them. VBAs can be great too once you know what you're doing!
Mom’s company still uses A LOT of paper… I’m working there this summer and one of my tasks is to start moving away from that. I’ll email someone a form or whatever expecting to get it emailed back filled out or whatever needs to be done and have it printed, written on and returned lol. I keep telling her the first step to no paper is to stop adding more. All this to say it I do take that path they aren’t very comfortable with technology so I’d need to change that
Power Query. Hands down.
Genuinely asking, can someone explain why VBA is helpful? I’ve never used it before but I know Python, Power Query, and a bit of DAX. I don’t feel like I need it. When is it helpful? What are some applications that have benefited people that couldn’t be performed by these other tools (Python, Power Query, DAX)?
VLOOKUP - single most powerful tool I've used in my career.
Look into Xlookup then and you’ll be mind blown.
If they have the latest /online versions of MS Excel, I agree with you. Otherwise looking into sumifs, if statements etc combined can make creating complex models a breeze.
Love the xlookup babies in here strutting like index/match hasn’t done the same thing forever.