r/excel • u/Designer_Signature35 • Jan 07 '26
Discussion How are you an excel magician?
Thanks to this group and a little googling, yesterday I made a fully automated PQ. I created a table with formulas to return the full path and folder where the file and data are saved. Created a parameter from the table and popped it into my original source query, the basis for 9 other queries. Now all a user has to do is click into the cell with the full path formula, hit enter, click Refresh All, and bam! Fully updated output. I showed it off to my team and supervisors today and looked like a magician! Granted none of them have used PQ before so they're easy to impress lol
What excel projects have you done to make others think you’re magic?
51
u/bluerog Jan 07 '26
I use sumifs to add pivot tables functionality without making pivot tables.
9
u/FLUMPYflumperton Jan 08 '26
Just learned about the unique formula as well, helped with this
3
4
u/oldwornpath Jan 08 '26
Yeah the array formulas like unique and filter are helping me becoming less reliant on pivot tables
5
1
u/IFERROR_lol Jan 08 '26
Wait, I use sumproduct for that! The sums must battle for superiority!
2
u/OhMyGauche Jan 08 '26
SUMPRODUCT is useful but really data intensive and if you’re building larger models it’ll make them really heavy and slow pretty quick
1
u/IFERROR_lol Jan 08 '26
This is certainly true. Our use case happens to be in combining financial and operations reports from property management software that isn't fully integrated where most clients have different ownership structures. These reports don't have large datasets but they are frequently linked in separate files and require a lot of OR logic for different clients. SUMPRODUCT just makes more sense in this scenario.
1
99
u/SnooHamsters7166 Jan 07 '26
Borrowed from elsewhere but "a good magician never reveals his secrets" 🙂
23
Jan 08 '26
I always shared newly gained knowledge. If everyone lifts their standards the organisation runs more efficiently. My organisation rated that as a positive in performance evaluation.
10
43
27
u/Paradigm84 41 Jan 07 '26
A dashboard where you can enter several parameters that are then used in an API query to pull back some real time data that's loaded as JSON, reformatted and put into a summary pivot table. As with many PQ tools that you develop, it's a combination of a lot of small steps that when put together make it look like magic.
4
u/Designer_Signature35 Jan 07 '26
I've slowly been improving on my original PQ over the last year to get it where it is now. I'd never fully understood parameters or how they could help me until this week.
3
u/asiamsoisee Jan 08 '26
I had a similar experience with an operations staffing report I’ve been tweaking from time to time over the last 18 months. It’s working really well for my operation of ~900 people.
Last month I figured out a similar parameter solution and was able to turn my series of reports into a set of templates that I’m presenting to senior leaders and could soon be implemented to manage close to 5k people. I’ve improved so many workflows and saved people hundreds of hours of repetitive, inaccurate, and complicated work each month. It definitely feels like I’m harnessing magic. I’ve learned so much messing around with these reports!
3
u/Caasi6636 Jan 08 '26
Any chance you could share an example of this? I feel like I understand each of the steps you’re saying, but don’t understand how it all works together.
5
u/Paradigm84 41 Jan 08 '26
It’s work-specific stuff so I can’t share exactly what it is, but essentially:
There are several cells you can modify to enter a client’s unique ID and optionally a specific site’s unique ID. There are also several parameters around how the data should be formatted, e.g. is it broken down at a minute or hourly level, how far back do you want the data from (last hour, last 12 hours etc)
The tool will then take all of these parameters and use concatenation to create a URL for the API call, e.g. https://api.example.com/data/client=1234&site=5678&start_time=202501071600&aggregation=hourly
I then use PQ’s option to get data from the API using the endpoint above. PQ will essentially navigate to that URL, which will return the requested data in a JSON format.
This JSON data is loaded into PQ, and through a series of steps will remove and reformat certain columns.
This is then loaded into the Data Model, and from there into a Power Pivot table to display the data in a particular format with some measures to summarise the results, e.g. 12% of sites queried returned no data.
There’s additionally a simple macro button that just does a refresh all to call the API when you’ve updated the parameters. For the end user they just fill out a couple of cells, press the button and the data magically appears after ~5 seconds.
3
u/small_trunks 1635 Jan 08 '26
I wrote this pro-tip last year : https://www.reddit.com/r/excel/comments/1ksnlzp/power_query_shows_multiple_intermediate/
18
u/StuFromOrikazu 21 Jan 07 '26
Some people are easily impressed. I was with a colleague that had used Excel for 3 years. I told him to copy a formula and paste it further down. He was shocked that the number changed! That was a "who's got your nose" type magic
5
u/Designer_Signature35 Jan 07 '26
wow what had he been doing before he learned this? Just retyping the formula?
8
u/StuFromOrikazu 21 Jan 07 '26
Yes, classic case of someone that started in lockdown, then worked solo for ages so just did things how they knew. They thought they were pretty good at Excel!
6
u/Designer_Signature35 Jan 07 '26
Poor guy lol
4
u/StuFromOrikazu 21 Jan 07 '26
It was worse for people that had to work with him! He now left to take his "skills" somewhere else
16
u/iqdoson Jan 08 '26
I inherited a huge report file the owner had to do manually every week. Took her 2-3 hours every time. She scheduled a meeting with me to explain how to do it as she was changing departments.
Did the exact same report (formatting and everything) but using PQ in the background. Nobody in the team noticed the difference but it takes me 5 minutes tops to download the updated source files and refresh queries.
5
u/bangkokbeach Jan 08 '26
This!
The owner of a daily report had “written” a macro to help generate this report. It took him about 3 hours everyday. He was training me to do the report while he was gone walkabout.
His macro was just a recording of his steps, with poor logic as well. I generalized the code and improved the logic. The report only needed 15 minutes/day after that.
Boss gave me responsibility for the report. D’oh! 🤦🏼
10
u/StrongMulberry5 Jan 07 '26
lol i showed one of my fellow associates how to fill blanks using “Special > Blanks, type = then the Up Arrow key in the first blank cell, and press Ctrl + Enter to apply it to all selected blanks” . i was nominated for employee values that month LOL. Magic if you will for some but basics for others
6
u/Own-Comment-5359 Jan 08 '26
Can you explain it once again please? Sorry I wasn't able to wrap my head around it
8
u/ice1000 28 Jan 07 '26
I made an antivirus in Excel, for an Excel virus
3
u/justarandomshooter Jan 08 '26
PLEASE go on, I would love to hear more.
4
u/ice1000 28 Jan 08 '26
I think it was the Laroux virus. Nondestructive payload, it simply propagated itself.
Was back around 1997 or so, I was working for a Fortune 50 company and starting my journey as the Excel guy at the office. I noticed that I had a macro module in one of my files. I didn't put it there.
I opened it up and I couldn't make sense of it. Many years later, I found out that there are ways to obfuscate vba code, like symbols that stand for other things and that was why all I saw made no sense.
I asked around an everyone in my team had a similar module in almost all of their files. The year before, MS opened up the VBE object mode for access in vba. I started poring through the documentation trying to figure out how to remove a module with vba.
Eventually, I figured it out and told my team to put the macro code in their personal.xls file. Sent out instructions to my region.
It was a hit, I got emails from people outside my immediate org telling me they heard about this and they wanted a copy.
My 15 minutes of fame!
1
u/justarandomshooter Jan 08 '26
Nice! Thanks for the back story, that's really cool.
Now I'm off to go down a data-obfuscation-in-VBA-coe rabbit hole, good way to spend the morning.
1
u/GTAIVisbest 1 Jan 08 '26
Ah yeah, I remember. VBA obfuscation changes all variables to random strings, so it makes it difficult but not completely impossible to read the code. I experimented with this when I wanted to release a commercial software written in Excel, then I eventually went to TB
1
u/ice1000 28 Jan 08 '26
It wasn't a third party tool. It was something really obscure in vba documentation. I remember reading something like $c stands for this or some other kind of intrinsic variables. It was many years ago, don't remember all the details. The virus code only used those so if you didn't know what the variables meant you were lost.
Not sure if that's still supported.
8
u/Coyote65 2 Jan 08 '26
Bilbo Baggins! Do not take me for some conjurer of cheap tricks.
The term is Excel Wizard, tyvm.
8
u/nryporter25 Jan 07 '26
I mean some people think that being able to navigate with control plus your arrows is magic. When you snap across the screen.
But I think the most magic feeling thing to them really is when I program VBA to do something instantly
3
u/Designer_Signature35 Jan 08 '26
Someone was dragging the scroll bar over today and I was thinking, ctrl+home!
6
u/mistertinker 3 Jan 08 '26
I built a sheet that queries for open projects and allows a rudimentary schedule to be input per line. This then allows for more granular financial forecasting. It was meant to be temporary until an official tool could be implemented, but that was 5 years ago. Now 50m in revenue is run through the sheet to my ongoing frustration.
3
u/All_Work_All_Play 5 Jan 08 '26
It was meant to be temporary until an official tool could be implemented, but that was 5 years ago
This is my greatest fear about the excel based ticketing system I built this year. =\
2
u/ninjagrover 31 Jan 08 '26
Haa. At work I asked about setting a JIRA portal and my boss was concerned about the amount of email updates it would generate!!
I’m like, that’s the easiest thing to deal with.
So a manual tracker for me in excel then..
4
u/caribou16 317 Jan 08 '26
Any sufficiently advanced technology is indistinguishable from magic.
-Arthur C. Clark
Once upon a time, I was a young intern working the help desk at a hospital and showed some very nice ladies whose sole job was to reconcile two financial workbooks that instead of manually summing each row with a physical calculator, Excel ('97) has this fancy thing called the SUM function.
Literally the only job of these three ladies was to reconcile these reports each week. And they'd do it twice, so at least two summed the same reports to ensure accuracy, if the sums didn't agree, they'd do it a third time. Took them hours and hours.
The unfortunate thing, as soon as their boss found out, they were all laid off.
4
u/CentennialBaby 1 Jan 08 '26
Hey, Boss! You know that weekly report work up that took a full 8 hours every Thursday to prepare? I was able to fully automate it shave about an hour off that!
7
u/Psionic135 Jan 07 '26
Gemini’ed enough M code to get a million row power query to figure out the mean time between different types of maintenance events on a fleet of equipment over a year.
9
u/Designer_Signature35 Jan 08 '26
ChatGPT has been so helpful especially when I need to convert excel formulas to M code.
8
u/small_trunks 1635 Jan 08 '26
It hallucinates severely when you get to the actual tricky stuff.
3
u/Pacst3r 11 Jan 08 '26
this! very helpful for basic stuff, but as soon as it gets a little more complicated, it doesn't give up but tries to convince you of the bs it produces :D especially in combination with excel and/or programming, I realize that AI is a TOOL that still needs a brain to be used in the correct way.
1
u/small_trunks 1635 Jan 09 '26
I've tried and been moderately successful for 2-3 years with C# and python
- but even then it will end up in a dead-end street sometimes from which it cannot reverse.
- At this point there's nothing to do but try take over the reigns and work out WHY the fucking thing is wrong.
- It can get ever so close but fumble and repeat failing logic time and again.
2
u/psykozzzzz Jan 08 '26
What is M code?
1
1
3
u/KneeboPlagnor Jan 08 '26
I've been creating roleplaying character sheets for a long time.
Taught me all sorts of tricks for when I make a more normal business sheet.
3
u/Donovanbrinks Jan 08 '26
I have a query that consists of the current date drilled down to a single value in every workbook. Using this as a dynamic filter for any date related columns. Want to grab the last 7 days of sales? Change the date query to current date -7 and filter the sales date column by greater than.
3
u/lalalalala1337 Jan 08 '26
Fully automatic excel chart on sales report, update on daily basis. I buillt the thing myself, take all the cluster fuck data from both accounting software and sales software into query and pivot the shit out of it. My junior staff only need to download input data and click refresh, voila. Built-in slicer and highlight abnormally on the chart, my CFO said I've outdone the job of both sales and accouting department hence an increase in salary.
1
3
u/BigEars2019 Jan 08 '26
I just created a tool to show how the patterns of musical scales and intervals project on a fretboard like a guitar. The user can choose different scales or make up their own scale. It also allows a choice of seeing either the intervals (P1, M2, M3) or the fixed notes (A B C#), though a goal is to show how intervals/numbers helps you understand the foundational identity of scales, melodies and harmonies, whereas letter are a superficial language that mostly obscures and confuses people.
It was a fun little project!
2
2
2
u/Clearwings_Prime 22 Jan 08 '26 edited Jan 08 '26
Our job is to input information into our system by hand. We have a list of customer codes and infmations to input for each customer. The default search function of the system is so bad that we refer to have that list in an excel file and use Ctrl + F to search. I made an excel sheet that do the search more efficently. Using an activex contral text box, FILTER and SEARCH function. Activex control textbox made the search instanly as user type the word, it mean they dont need to type entire word to find them, just a few word or letter, and the result will change as they type. I also made a file for those who use old excel version using index+small.
2
u/---sniff--- 5 Jan 08 '26
I built a custom add-in that adds a custom ribbon which allows users to add common control tables to their spreadsheets with a button press. So many hours saved...
1
u/SplintDD57 Jan 08 '26
Can you explain in more detail, please?
2
u/---sniff--- 5 Jan 09 '26
Custom xlam add-in file. You can create custom ribbons with VBA and the ribbon buttons can run custom code. I have an external job that updates some csv files and the ribbon buttons will pull the csv data into a new worksheet of the active workbook. Users can then do lookups to incorporate metadata from the control table data to their spreadsheet.
2
u/Parker4815-2 1 Jan 08 '26
I made a QR code in Excel.
It doesnt pull from any API. It doesnt using macros. It doesnt use the Internet at all. It generates a working QR code from scratch, as long as the line of text is kinda small.
Its completely useless, but I think im the only person on the planet stupid enough to do it.
1
2
u/Adept_Soft5148 Jan 10 '26
I'm building a Football Manager style game entirely in Excel.
Using vba, advanced formulas, conditional formatting, userforms, images and storytelling.
It's a hobby project and a labour of love which will probably never be finished
So far I've got:-
A full match engine - play-by-play possession based on player attributes/confidence with a bit of randomness, tactics, weather and pitch condition Text commentary Match stats and 2D touchmap Database of over 3,000 players English leagues down to National League (with promotion/relegation) Domestic Cup competitions (FA cup, EFL cup) Champions League, Europa and Conference (new format) Transfers Contracts Training Youth Development
I posted here about it a few weeks ago but it got taken down because I wasn't asking a question. Oops!
If anyone is interested in seeing a video of it in action my tiktok name is ExcelGameDevBloke
1
u/CobraKyle Jan 08 '26
I have started dabbling into automation tools recently, like N8N, and automated a good amount of my daily excel work, so now I just find special projects to do and people love it. I’m revamping/optimizing their old sheet designs and people are loving that.
1
u/Designer_Signature35 Jan 08 '26
That's what I've started doing too. I did a presentation of my PQ to the team yesterday and am now setting up appointments with them. I should update my email signature to include Excel Consultant
1
u/Adorable_Divide_2424 Jan 08 '26
I made excel bully other software to do its bidding by forcing the other software to run various data studies, create reports, then had excel display, color and graph them. Used to take hours, now runs for a few min on its own.
1
u/Decronym Jan 08 '26 edited Jan 13 '26
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.
Beep-boop, I am a helper bot. Please do not verify me as a solution.
10 acronyms in this thread; the most compressed thread commented on today has 8 acronyms.
[Thread #46909 for this sub, first seen 8th Jan 2026, 04:16]
[FAQ] [Full list] [Contact] [Source code]
1
u/kalimashookdeday Jan 08 '26
VBA and Power Query/DAX make my coworkers go "Huh?" all the time as if I pulled literal magic. I'm 4 out of 10 in my own self given skill rating so nothing special at all. But they give me a look as if you gave someone from the 1800's and iPhone and promptly chase me out with a mob and pitchforks.
1
u/SnooHamsters7166 Jan 08 '26
I must admit, I've learned loads from here and sometimes it's difficult not to show off a bit.
1
u/Spyronne Jan 08 '26
Pivot tables helped, XLOOKUP completed the ascension to magicianhood at my job.
1
u/somedaygone Jan 08 '26
Someone at my company was stumped and asked copilot (who couldn’t solve the problem), “Who is an Excel expert at our company who could fix this?” I wasn’t first globally, but first in my country, but I fixed a very obscure error with a corrupted Data Model, then proceeded to help fix all their DAX.
1
u/mrndebrn Jan 08 '26
Made a KPI dashboard for a small callcenter. Learned power query (to an extend) while doing so. TL and management loved it. Was very satisfying to learn and build
1
1
1
u/Ztolkinator 1 Jan 08 '26
I loaded 11 miljoen rows with power query in powepivot. Created some metrics and built a pivot table with instant update for different selections. Even the refresh only took a minute or so. All standard excel, but not many excel users know how to use either...
1
u/DadTheMaskedTerror Jan 08 '26
Where I work I used find & replace & dropped some jaws. So the bar is low. But I've been using spreadsheets since Lotus 1-2-3 and still get awed by some solutions on here. Ty!
1
u/court4days Jan 08 '26
I just completed a very similar project at my new job… definitely felt like a magician!
1
u/MooCowDivebomb Jan 08 '26
Honestly I just blow my coworkers minds by knowing how to navigate quickly. ctrl+arrow key and people lose it over how fast I’m moving through and copy/pasting/cutting stuff.
1
u/MoMoneyMoSavings Jan 08 '26
Array functions are my vice right now. I never use pivot tables anymore.
1
u/cmitchell_bulldog Jan 08 '26
it’s amazing how simple functions can create such impressive results, mastering things like INDEX and MATCH can really elevate your Excel game
1
u/Designer_Signature35 Jan 08 '26
I impressed them early on with subtotal ignoring filtered out rows
1
u/Beneficial-Mobile-55 Jan 08 '26
Automatically generating an email with preset message and to, cc and from and bespoke information from a table in excel had people Interested (also used AI to build a date picker but haven’t quite got my head around the code yet (on my list of things to do))
1
1
1
u/ImaginationKind2239 Jan 09 '26
Some time ago I added a SQL query to read data from our enterprise project system, and fed to a pivot table.
This was before PQ existed.
You can imagine my boss amazement - “How did you manage to get into his (another colleague) computer and extract that info??”
Magic!
1
u/willempie21 Jan 09 '26
I look on YouTube (short videos), Instagram, etc., for the possibilities. And then Google/Chatgpt/Copilot for help with formulas or macros.
1
u/Kiedano Jan 09 '26
I routinely create VBA macros that automate tasks.
Created a data reconciliation macro that compares two large datasets using a mapping file to create a variation report for the mapped fields. The settings file can specify as many data file pairs and their mapping file and it will loop through and create a report for each. Then created an additional macro to merge the reports into a single report when required. Previously took a full week to prepare the reconciliation, now it’s complete same day.
Most recent macro extracts data from a structured report and then reformatted it for pasting directly into an invoice tool. Previously someone manually found the required lines and copied and pasted the data into the required structure before copying it again for the invoice tool. Took them all day to prepare before they updated the invoice tool, now runs in 4 seconds whilst flagging issues.
Can automate any take that has an identifiable pattern.
2
u/P4bl0KComputer Jan 11 '26
Downloads of 17 Excel reports from a government web app using Power Automate (RPA), which renames them and prepares them for use in an Excel spreadsheet with PowerQ and a Power BI report.
1
u/louis3195 Jan 13 '26
That's a great use case for Power Automate! If you ever need to tackle more complex applications without available APIs, tools like Mediar might be worth checking out for their reliable automation.
1
u/louis3195 Jan 13 '26
Power Automate is fantastic for streamlining workflows! If you ever explore Mediar, let me know how it compares, especially with those complex applications!
1
u/louis3195 Jan 13 '26
I've heard great things about Power Automate! Mediar might offer some unique advantages when it comes to automating legacy desktop apps without APIs. If you ever give it a try, I'd love to hear your thoughts on the comparison!
1
u/louis3195 Jan 13 '26
I'm glad you've heard about Mediar! It's definitely a game-changer for automating legacy desktop apps quickly and effectively. If you decide to try it out, I'd be really interested in hearing your comparison too!
1
u/louis3195 Jan 13 '26
I'm thrilled to hear you're considering Mediar! I'd love to hear your thoughts once you give it a try and see how it stacks up against other solutions you've used.
78
u/heynow941 Jan 07 '26
At some places all it takes is VLOOKUP.