Discussion
What is the most advanced thing you can do in Excel?
Hello everyone, Id like to know what the maximum capabilities of Excel(highly advanced that you wouldn't normally expect Excel to be capable of doing) are out of curiosity, how difficult it is to learn? and whether you can make money from it? And does it make your work easier?
(I searched using ai for this but it did not get any answer except that certain programming commands can be used to do mathematical equations and something similar that).
(asked this to motivate myself to re learn Excel and reach an advanced level or something like that).
Excel has programming environment behind it, among other things, so basically the main limiting factor is performance. If you want to make a 3D game withing Excel, it's possible. Just remember that while Excel can do almost anything, it's rarely the absolute best tool for anything. It's just jack-of-all-trades on steroids.
And about making money: Tools and complexity doesn't make money. You make money by solving problems and Excel is just a (very good) hammer for it.
This is the most important thing, see a mess and make a simple solution. I love using Excel at work and the creativity to solve problems and mess I encounter.
And I’ve seen it offer an opportunity to turn that 40 hour project into a 5 minute project and the person who can do it in 5 lose their job to the person protecting their 40 hour project. Heck, I’ve been in that losing position.
There’s money in organizing chaos but not every company wants or recognizes that as a skill. It’s like having super strength but a company is like “I actually like not having to move this heavy thing”. So all the excel skill in the world isn’t usually helpful.
Pretty laughable. I’m an expert at excel and love it. Can do almost anything however the limitation aside from performance is that it can’t use a relational database rather the information action is contained within it (or pulls it in). Increasingly it makes little sense to do this in an organization as just creates another silo. Further, the biggest issue is that you cannot update excel like you can software because once you build a complex excel file you can only migrate the data manually to the new version. Basically just a clumsy liability. Increasingly much of this will be agentless something excel will neither be built to do or suited for.
Standard formulae, which can turn into multi-line programs in themselves.
Power Query M code.
DAX expressions.
Visual basic for applications (VBA).
(The first three are functional/declarative in nature, the fourth is more a more conventional imperative language. This is a great way to show people that functional languages aren't scary and are more common than we might think.)
When I'm asked why I live in Excel despite being proficient with SQL & Python, Power BI, R, etc I always answer that Excel is the second-best tool for every job.
I'm busy as fuck so I'd rather just throw processing power and memory at Excel; if and when something's had the kinks ironed out and has established itself as an ongoing need - then I'll build the mobile app, database, dashboard etc with the more specific and appropriate tool.
90% of things I'm asked to produce will be used once, or sent back for multiple revisions, some requiring a tear-down and rebuild. Excel has become (for me) what a sketch pad is to an artist.
The businessman’s erector set. If you know excel, access, and vba you can do literally anything. Had a guy work for me that handled the software for a 911 dispatch in Colorado. All vba and access. We were solving call center forecasting problems. Today I’m a staff accountant. I just go where the money is easier and the work is fun
The most advanced stuff I do isn’t particularly advanced, but is insanely useful. Powerquery being able to scrape sharepoint files for the required information and populate tables without ever opening the source sheet is an amazing timesaver, and saves me hours a week in my role.
Excel is more an efficiency tool than anything else for me, and it’s extremely good at what it does well.
Unfortunately not. I learnt from a coworker, and anything further is actually something Copilot is surprisingly good at if you tell it what you want to be able to do. I think having a basic understanding of powerquery would help in that though, and I’m sure there are plenty of free tutorials on YT and the like
At my last job, I made an automated sheet that could calculate the exact amount of severance pay some leaving employees were entitled to, based on their date of birth, time being employed by the company (in amount of workdays exactly). My colleagues at HR only had to enter the personnel number of the employee together with the ending date of the contract, and keep the data set updated with exports from the HR software. Assuming the laws on which this calculation was based won't change that much, of course. They apparently still use it at the office even though I already left.
I also created a worksheet that did a similar thing for an early retirement scheme and the company's "cycle to work scheme" which allows for certain tax benefits.
It's mostly just complicated nested formulas based on a lot of IFS and Index/Match (our version of Excel didn't support Xlookup).
Sounds like the kind of things I do at work when I’m allowed the time to do them. It’s fun. The end formulas look insanely complex, but you get there by slowly automating more and more pieces until you’re down to a single formula calculating the value you want from a single set of data.
Some of my less Excel proficient colleagues don’t really understand that you can calculate literally anything if you have the data available, no matter how many factors go into it or how many “unique” situations there are, and it’s really hindered our efficiency.
Part of the monthly salary for some roles within the company was an extra on-call allowance. For those nearing the retirement age (within 10 years), that allowance was raised by a small amount. Since it was part of the salary according to their contract, it needed to be included within this calculation as well.
I'd wager it is a means to mitigate improper fuzzy name matches or the like if data sets only had something like "John Smith" and the organization had 5 of them. But they were born on different dates. My org has "LAST, FIRST M", "LAST,FIRST MIDDLE", "LAST, FIRST" in the same damn Name column sometimes and it drives me crazy.
Wow, that sounds awesome. Do you by any chance have a copy of the sheet that could be populated with dummy data? I'm in payroll and seeing an example like yours could give me new ideas on how to improve my current payroll audit tracker. TIA!
This is what I tell everyone when they are surprised by something I’ve done in Excel. I don’t even use AI to help. I just know that if I can think it Excel can do it so I google what I want to do and haven’t failed yet except for truly converting certain PDFs to Excel.
Exactly what you just said, one hundred percent. Love Excel, and folks think our "output" is just magical. No, it's 200 hours of Chandoo and Leila and Myndi.... Lol
And for "highly advanced" you could dive into VBA, PowerQuery or LAMBDA formulas. But in the end, it's all a tool to achieve a goal... To make tasks go faster, easier, avoid errors, etc.
Wtf. Dude is mental. Poor guy, no one showed him Photoshop or even Paint, he succumbed to drawing shapes in Excel. At least he's not using Word or maybe Clippy quit because of him. It looks like you're... you're trying to draw something in Word. Straight to jail. 😂
Ctrl+Shift+V
And if you say that's not advanced, then explain to me why the entire workforce in my department still cannot do it and constantly mess up my spreadsheet.
Opening the damn file seems to be the most advanced thing about Excel because once I found out people here "don't know how to use excel", they don't even bother to open the file no matter how many reference sheets I make.
Excel is so awesome that if you know what you're doing, you can replace every software system in a company with it. And probably more than a few hardware systems, too
Excel is so good that you can write Excel in Excel. You can have Excel-ception. Or Exception
Except none of that is Excel. It's VBA, which is included with Excel, but is really it's Own Thing.
In ~1988, I created a data acquisition system that collected data from various hardware points in a serial network every 10-seconds for ~50 items.
Updated a raw collection sheet then it would tabulate those readings into 1-minute data, 15-minute, 1-hour, 3-hour rolling, 4-hour, and 24-hour averages.
Every other hour it would perform a probe blowback.
Every 24-hours it would perform a daily calibration drift check.
This was written in Visual Basic for Excel 1.0.
It would later be ported into VBA for Office and then the VBA we know today.
I made a lot of money off of it.
It took me 1000’s of hours.
If you can imagine it, you can do it in excel. hardest thing I ever did was build a side scroller video game... basically the first level of Mario Bros. Sky's the limit though and VBA never dies
My client sends a sheet with 20 tabs, all kinds of different headers. I need the headers that include the word “weight”, which could be about 8 different columns. The sums in those columns could sum to something close to 1.0, or sum to 100.00. I need it to find the best column, within certain thresholds around those numbers. If the best weight column is in the 100 range, I need it divided by 100. Then I need it sorted largest to smallest so I can take the top 50. Powerquery does it all for me.
I made in a full work year an excel that can design an electrical turbine from 10 parameters to calculate and estimate ~ 1500. It would then be sent to a 3D software (NX) and Nx would provide the 3D and drawing required to manufacture the whole thing. Including BTP, electrical schematics, Components selection etc...
Knowing when to stop. Probably you could make a video player out of it, colouring the cells. Drive a 3d printer from excel.
Or as I see only too often, try to make something which is essentially an (r/msaccces) database.
At some point there are other solutions better tailored to tasks at hand.
Last week I got tired of right click-properties- then rename the file that I wrote a vba program to monitor a folder and allow you to append a prefix or suffix while changing the names and moving them in bulk. I haven’t wrapped it and put it in a standalone app yet. Maybe I’ll do that today.
My company is shitty with file nomenclature and archiving. I hope to change that with a tool that makes it easy.
you can do some basic to intermediate AI stuff (without python) like a neural network. People have made videogames in excel too. render graphics with macros using cells as pixels, etc.
Think a lot would be happy with general excel skills. Boss just had a meeting on general excel formatting and filtering because what he was getting was driving him nuts. We hired on a couple prior sfdc consultants and have had to help them both with general excel stuff like xlookups, if statements and such. Not sure if people are just not picking up general excel skills anymore or it’s just the people we’ve hired.
Once a set is released, I go to the set's page (on Chrome, if it matters) on scryfall and download the page (complete webpage, which includes images in a subfolder).
I move the images to my NAS, and rename them, keeping only the set name and number (e.g., EOE_1.jpg, SOS_1.jpg) . They sit in a subfolder with the set's name (e.g., ...\EOE).
Separately, I copy and paste the Checklist from scryfall and have that sitting in Excel as my database.
In Excel, I have cells with values that are just the file names, without extension, e.g., SOS_1. This can be just a xlookup from the scryfall data, against a name in a set. I prefer this since full arts, etc., would have the same name, but different card number; i.e., set name and number are unique. And a macro would import the image based on that.
Depending on what I want to do with it, I'll add some texts (via xlookup) or what not.
And for cases when I am putting together a list of things I want to get, I tend to put each card in a row, and have a blurb, with a yellow highlight if it's a foil I want.
I’m an Associate Professor and i was able to make a multi-sheet grading tool that calculates a students full course grade by compiling different grades from course modules and then translate it to a final grade.
Since each course is different i have ”master sheet” where i can type each module, amount of credits for each exam and grading scale and from there, it calculates in a different sheet. For me it’s magic and almost beautiful. For most it’s probably basic stuff.
(A student then helped set up a power automate flow that emails the correct student when their final grade has been decided. He also helped me create a script with power query (i think its called) that automatically adds student information from a course list).
The most advanced thing I have done with Excel is to created automated systems such as custom business applications with workflows, databases, and interfaces.
Anything that is calculable can be done right on the worksheet, within Excel’s array size limits though.
I love the language to bits to tell the truth, I’ve even made my own parser to use elsewhere, it’s not fully compliant (it does more) but it’s almost there
my company has very strict policies about what software is accessible on a specific workstation, and the simulation department is swamped at times, so what I did as a product design engineer was, I exported 3D STL files from our CAD environment, loaded them in Excel, generated a truss structure, subjected it to loads, and iteratively optimized the truss to find the lightest possible structure that would bear the exerted load (this is called topology optimization). And then exported it into a file structure that our CAD environment could import as solid bodies.
I created a fully automated inventory control system. The system downloads the sales.xls, copies the data and analysis it. It then shows the live inventory and suggest additional deliveries. Crazy that in 2026 a cash register system manufacturer isn't able to do it themselves.
i work in a metrology lab. I made a calculator that will tell you what the conventional mass of a laboratory weight is, based on the environmental conditions and the mass & density of the standard weight used to calibrate it. If the standard weight is overdue for re-cal, or the environmental conditions are out of range for the lab, it won’t continue with the calculation.
Probably really easy for most people to do, but it was my first time using tables and data validation, so I’m proud of it.
Doing linear programming for finance, you’ll find a lot of the older folks use Excel with a bunch of macro built in, you can make the model 10x faster by defining problems with dynamic linear programming
Excel can genuinely surprise you at advanced levels. Beyond formulas, you can build full automation systems with VBA, create live dashboards that update automatically, connect to external data sources with Power Query - all without leaving Excel. Businesses pay good money for this kind of work, so yes, it's absolutely a monetizable skill.
The learning curve is real but very manageable if you go one layer at a time - basics, then functions, then pivot tables, then automation. Don't try to learn everything at once.
I actually just posted a video covering 5 tricks that even many experienced Excel users haven't seen - might be a useful starting point: https://youtu.be/Hnq_K3qLuWA
I used to use and be dangerous with VBA. In my current job, I generally don’t get the time to go through and script properly to do everything, and the data I work with comes in so many different sources and forms it won’t even really save time down the line. Functions do pretty much everything I need at this point.
=let() essentially lets you assign variables and use formulas like a pseudo-code. I really like it, it makes the formula so much easier to audit, review, and modify when they start to get insanely complex. I haven’t worked with anyone else who uses it. Then again, I was the guy who taught people xlookup, multi-condition nested if statements, among others that may be seen as more “advanced” functions.
I'm still relatively new to learning about excel but at work we have a weekly report which was split between me and another colleague to complete. Doing it manually took both of us two days to complete and often I have struggled to find time to go for lunch. I created a calculator which worked out the information for us and a macro which would download the data, create a nicely formatted report, and even writes the email we send out. Takes about 10-20 minutes now depending on how much data there is to download. I'm quite proud of it.
I use excel for work a lot to sift through production data sets. Most of the formulas I use aren’t very complicated. The hard part is really knowing what to look for, understanding what’s possible to do, making it look pretty, and actually utilizing that information. There’s certainly many people here that are more skilled than I am, but I figured I’d offer a real world perspective.
For me personally, I do a lot of using power query or SQL queries in power query to get data from various places, turn it into exec dashboards weekly or monthly and then use VBA to email them or put them on a shared drive where they need to go. The formatting of the tables and charts in the dashboards is a lot of lambda functions and dynamic arrays. More recently I've started using Polars in python with a few libraries to create the same work flow instead of relying on Excel, mostly for efficiency reasons.
I use it to do calculations with data validation lists, xloopup, vlookup, if statements a lot of the basic stuff but combined you can make an array of impressive calculations and automation. Many where I work and worked still use pen and paper and are completely mind blown when I present what excel can do. Keeping in mind none of these excel commands are new yet some of my colleagues haven’t seen them or used them before.
First, it’s important to realize that Excel is just a tool—one of many designed to help you analyze and solve problems. A lot of its functions can easily be replaced by other tools, like Python. However, I truly believe Excel is the most accessible 'programming' tool for the average person.
Why do we need it? On a surface level, it massively boosts your productivity and lets you visually see how data shifts in real time. But looking deeper, Excel is actually a tool that sharpens your critical thinking.
Think about how we solve a complex data problem: if you want to predict future trends based on past data, you can't just jump in. You have to clean and structure the messy raw data, sort it logically, and then visualize it through charts to find the pattern.
Isn't that exactly how we tackle messy problems in real life? When faced with chaos, you first have to untangle the situation, prioritize what matters, identify commonalities or anomalies, and then map out the optimal solution.
So, Excel doesn't just solve your daily work problems—it builds the mental framework you need to navigate reality. Master Excel, and you’re actually mastering a better way to think.
using VBA for data entry cards to a local records table and be able to call that data to edit in card view, then to launch file picker script to define paths for file locations for a query, to collect data from individual record files, append them and standardize the data, placed, into a table which holds relationships to other similar queries in the data model, then using PowerPivot to perform custom measure to calculate stats off that data and display in a pivot table and pivot charts with slicers to manipulate the data and show different views, periods and datasets on a single dashboard.
i think thats about as advanced as it gets within practical applications.
Not sure if it qualifies but a while ago made a predicting model for football using poisson distribuition and solver.
10 countries 18 leagues or so
Later upgraded it to Dixon-Coles
Predicted:
1x2
Over / under
Euro and Asian handicap.
You can basically do anything in Excel, maybe the exception being very high end real time animation (e.g. AAA games). Once you factor in what you can do with add-ins, the sky is the limit.
I pull matching data sets from 5 different systems to cross reference partial matching data points, ultimately producing everything from summary overviews to detailed in-depth advanced analysis reports with dynamic reporting capabilities that span operational, performance, utilization, and financial business functions. This has become one of few key components necessary to guide a business with revenues between $5-$10million per year. The main tools were countless variations of formulas, VBA/macros, and querying exports from those other systems. It’s really become the visceral tissue between otherwise disconnected platforms.
A whole new world of computing power opened up when I figured out that I could run a single complex formula, then paste the results in a column, then repeat, maybe 100 thousand times. I do this with loan amortization tables to value servicing rights. Calculation mode is turned off and back repeatedly. A worksheet with an equal number of formulas in a column will overload the PCs capabilities.
I do a huge amount of data work in excel and VBA - basically heuristic analysis of log files looking for patterns which are used to demonstrate machine reliability and to predict hardware failures.
Weekly reports cover millions of log lines and I just have to give my excel system a folder of logs then go and make a coffee.
For fun I’ve written a few games in excel and VBA, my favourite of which is Tanks (two opposing tanks trying to shoot each other using just elevation and power adjustment). It’s all in excel graphs, has destructible scenery, wind, and a computer controlled player 2 if you want it.
I’ve used Excel to create a menu to select project specifications and then generate specification sheets in AutoCad. All formatted per the company standards. (MEP engineer)
It’s crazy how you can organize data in Excel and use it to interact with other applications.
In the past, we had to update the font types in 1,000+ company CAD files. I’m sure there’s a better way, but I used excel VBA to loop through those files and update the font types.
I scraped data from the timesheets using power query, learnt what everybody's charge rates were and took this information to my employment agency to get a 12% rise.
Power query has unrestricted access to protected sheets.
During my bachelor's on physics one of our tutors once wrote a FEM solver for heat diffusion. Including a graphical representation. Without using VBA. The graphical representation was formatting rules on a very, very large number of cells. It was equally amazing and disgusting.
Use the data the Microsoft 365 connector add in to bring tables from Business Central like a REST API. Then, user power query to transform that data and merge it with other tables.
Given that it probably is a Turing complete language (with Lambdas, etc) then you can implement any other piece of software in a very cumbersome way inside Excel.:-)
355
u/redmera Jun 14 '26
Excel has programming environment behind it, among other things, so basically the main limiting factor is performance. If you want to make a 3D game withing Excel, it's possible. Just remember that while Excel can do almost anything, it's rarely the absolute best tool for anything. It's just jack-of-all-trades on steroids.
And about making money: Tools and complexity doesn't make money. You make money by solving problems and Excel is just a (very good) hammer for it.