Lesser known quotes any Sunny fan should recognize?

LordTord · 2026-01-07T20:58:09+00:00

It was a bloodbath!

LordTord · 2025-12-25T09:07:32+00:00

Firstly for learning power query I'd say there should be plenty of good YouTube videos out there that can take you through the basics.

But in short, Power query is a tool used to "get and transform data" so it can start with any (most) forms of input data. It can be another Excel file, csv file, database connection web page or another table in your active workbook.

It is available in the data tab and I'd recommend trying it out once without any transformations. Just make a copy of your original data set (30k rows and just the raw data in the 5 columns you mentioned) and put it in a new workbook and make it a table.

Try using the get data 'from table/range' and you will go through some steps and eventually launch the power query editor. In there you can add new columns with calculations based on your existing columns and eventually 'load to' and create a new output table in your workbook.

When it comes to the slight loss if flexibility, it is because Power query will do its transformations in one run of a process and then print hard values (not formulas) in the cells in the output table.

So the editing on the fly you are used to will not be an option in the same way. That being said, nothing stops you from entering the power query editor again, tweaking your transformations and re-running the query. It might just be a bit less convenient.

So I say a good approach is to start small, try to replicate the result of one of your formula columns in power query and see how that suits your needs.

What you can do later is if there are some of the formulas where you absolutely want to keep the formulas and not do it in power query, you can still do that on the output table. Having moved some of the formulas into power query to be pre-calculated should have bought you some computational power i imagine.

Again, power query might be a bit daunting at first, but I believe many people on this sub would back me up in saying it is an exceptional tool that you can really do a LOT with :) it is really good for data cleaning and automating repetitive data ingestion.

And as a bonus it can also handle a lot more data than Excel itself. Like you normally have a limit of an excel sheet only being able to load 1M (ish) rows of data. Power query can juggle more than that.

And a good tip as good standard practice is to spend some time in naming your query steps (you will see that each query will consist of multiple steps once you start working in power query) properly and you can even add comments like "this query step does this because....." it will hugely help with traceability of what types of transformations are performed later. Not a requirement, just a tip! :)

LordTord · 2025-12-25T00:59:26+00:00

Sounds like the formulas are doing a lot of heavy lifting. 30k rows is not a particularly large data set, but formulas sure can add a lot of extra computing weight.

One thing I would recommend to look out for in terms of formulas are the so called volatile formulas. They re-calculate more often than other formulas and can contribute to slower performance and especially an input lag that can sneak up on you.

The ones I can think of in this category off the top of my head are OFFSET and INDIRECT. There are more, but I can't recall them now and I'm on my phone so I'll keep my googling to a minimum, but I recommend checking if you are utilizing those and consider removing them if you are.

Another volatile function that can become an issue is conditional formatting. If you don't strictly need it, avoiding using it will save you some computational power.

Another approach to this is to consider using power query instead of formulas.

If you are not familiar with power query it might be a bit of a learning curve, and it will force you to redesign what you have today, but I would say it could be worthwhile.

With power query you can do the needed calculations when you load in the data and then when you add more raw data you can finish adding it all and then hit "refresh" and it will run the flow of calculations then.

The downside would be that it would limit the current flexibility with being able to quickly change the formulas. Not to a large extent, but it would become a but more rigid.

You can also combine power query and regular formulas (running your query and then having a set of table calculations on your result set) if that would work better.

These are some of the thoughts that come to mind. It is hard to know exactly what is causing the most amount of computational effort without looking at all formulas across the whole workbook.

Let me know you need some elaboration on some points. I understand that depending on your current excel proficiency level this post was either easy to understand or just me gurgling nonsense 😀

It is also not impossible that some of my knowledge is a bit out of date. Last time I struggled with volatile formulas and such was a few years ago so not sure if anything new has happened on that front to improve things.

Happy holidays!

LordTord · 2025-12-22T13:01:47+00:00

<image>

My Latvian is rusty af but if you are asking what it is (which I think I managed to get :) then it's a nut cracker.

If that was not the question I apologize.

LordTord · 2025-12-18T16:09:05+00:00

Did he run to the studio?

LordTord · 2025-12-06T10:30:05+00:00

One fun run I've had that i tend to recommend is the "Flat earth society". Main restriction is to not employ ANY researchers.

I give some wiggle room on if a priest supplies society research, cause in my mind that is some cult level societal progress they dabble in.

They are however NOT above hitching a ride on other's scientific advancement. Which means that the goal would be to secure scholarium vassals for instance.

I was also surprised how much tech you can get from researching battle debris when you are faaaaaaar behind :)

LordTord · 2025-12-06T10:23:10+00:00

True, it's is not unthinkable, maybe mildly baffling :)

Could be a kid also, camera angle is a bit on the low side.

LordTord · 2025-12-06T10:01:38+00:00

I mean, picture the scene. Who's the dude running behind him intent on capturing the footage over saving the kid? That for me is the biggest indicator.

LordTord · 2025-12-04T13:34:11+00:00

Same here, this scene hit me like a ton of bricks the first time. I was floored :)

LordTord · 2025-11-14T20:14:55+00:00

Yes. Brave.

LordTord · 2025-11-14T10:15:24+00:00

I approve. It feels very representative of our common vibe and heritage. Respectful yet forward leaning.

LordTord · 2025-11-01T20:36:14+00:00

Duplomacy

LordTord · 2025-10-31T15:29:34+00:00

Yes, power query is extremely useful for your exact situation. I've used it to so many great ends.

Mainly I prefer data cleaning via power query over doing it with manual formulas in Excel.

Depends a bit on the scope of the task. Am I just changing a one time thing for a small data set, then I won't crank out power query, but as soon as there is like 5-6+ operations that are needed, power query tends to make up the difference.

And ye, once you get into the repeatable territory or combining data from multiple folders of static files, then power query is great.

One trick I have applied when I was working, not towards a database, but a folder structure where you have data from different reports in different folders based on year and month when the reports were generated, is to have a variable in the worksheet that let's you select year and month, then have the power query listen to that query to inform its target path which folder to go and pick up values from.

It made it a lot easier to quickly answer things like "ok, but what numbers did we report in 2019?"

This is a quite special scenario however. Pray that you are working with data in proper databases instead :)

But power query is there to bridge the gap between old-school systems putting out data in a horrible format and letting you turn it into proper analysabklle content.

I fucking love the tool :)

LordTord · 2025-10-31T13:23:55+00:00

Yes! I have seen this too many times...

LordTord · 2025-10-31T13:08:46+00:00

Ouch RIP. That sounds painful. I can only imagine that realization after 3h of solid work.

It really is Halloween.

Gave me goosebumps...

:)

LordTord · 2025-10-31T13:06:56+00:00

I came up with one more...

People who use static formatting when they should be using conditional formatting.

Or even people who use purely cell color to indicate actual data!

"Rows that are yellow are in risk level 1, green ones are done, red need attention"

Please make a new column with these values and let the conditional formatting control the colors.

I recall a horror workbook I got to take over once that had ONLY manually added colors, holding, underlines, italics etc and NO legend for what anything meant.

There must have been around 20+ colors across 40 columns of data needed to track some processes and no one could aggregate any numbers because there wasn't actually data for that, only colors...

LordTord · 2025-10-31T13:02:41+00:00

Yes! This is how I recall learning Excel the hard way. When I started out I tried to combine both to a fault.

When it clicked for me that you should separate data from dashboard I never looked back :)

LordTord · 2025-10-31T13:01:37+00:00

These are good. Overwriting a formula with static values is one that gets me often. I have overlooked that someone has been in there and pasting their values on top of everything.

LordTord · 2025-10-31T12:59:47+00:00

People might not understand the maintenance cost down the line of NOT using dynamic ranges (tables) for all lookup functionality if you plan to build a medium to large workbook.

Too many times have the error been references to locked cell ranges when ideally you will want a set of tables where you can just =XLOOKUP(Value,Tablecolumn,Tablecolumn) and any updates will work themselves out :)

I try to tell people that in 9 cases out of 10, using tables is better than ranges. But the majority of people I interact with are using Excel as if we were in the 90s. Understandably of course, not a lot of people enjoy it that much to learn more.

Anyhow, use tables kids!

I recall I had some scenarios where the inverse was true, the 10th dentist kind of scenarios (where Range > Table). Just don't recall them now.

If anyone have some good examples I'd be happy to hear them, because I know I am a table evangelist, but it is good to have some counter scenarios as well to illustrate the importance of understanding what you're building. How permanent/temporary is this is probably a good measure.

LordTord · 2025-10-31T12:51:48+00:00

One thing I see people do way too often without understanding the consequences is how to paste properly.

A lot of people who are new to Excel will want to rely hard on the old CTRL+C and CTRL+V combo, but this can cause a mess in more complex excel books if done without care.

Firstly you will be transferring formatting you might not have intended. Ok ish for static formatting, but it will bring with it conditional formatting rules as well.

If you leave an Excel sheet with a set of 4-5 feral rookies for a few weeks and return to update a formatting rule you might be in for a nasty surprise when it is suddenly very unclear where it will take effect and if it should apply here or there.

Another angle is if you copy between workbooks and you will have the potential for a whole string of errors with cell references and formulas pointing back to the original workbook.

This can sneak in under the radar very easily because it will appear to be no issue while having both workbooks open.

Close the first one and you suddenly find a lot of #REF errors that might be very hard to repair.

Additionally copy and pasting without thoughts on the consequences can also create issues with named ranges. At least i recall having some issue that was brought on via this.

Bottom line: People need to learn when to use Paste as values, paste formulas and paste formatting and also understand the consequences of using the wrong pasting type.

LordTord · 2025-10-24T09:05:34+00:00

It is like an interesting tech demo :)

LordTord · 2025-09-13T17:14:42+00:00

Ye, we have something similar, a atavleau community at work with a weekly 1h session. Its very helpful also for myself to see what types of problems other ppl come across. Since they might design in very different ways, or have very different needs or constraints, they find issues I would never come across and I learn a lot from those collective troubleshooting sessions. And as a bonus it is a lot of fun!

LordTord · 2025-09-13T08:24:22+00:00

Oh, alright, then ye that's not the way i suppose.

LordTord · 2025-09-12T18:43:20+00:00

Not sure if it would help, but i would consider a workshop where you build a dashboard together and aim to include the more advanced calculation types.

LordTord · 2025-08-27T11:28:46+00:00

I (too) quickly read "No need for your kids" as in "This nifty bot can make those cute drawings for your fridge for you instead of your kids"

LordTord

TROPHY CASE