All posts tagged: Excels

Excel’s LET function is the most underrated thing in the entire program

Excel’s LET function is the most underrated thing in the entire program

Published by skeptic

If you’ve spent any time writing formulas in Excel, you’ve likely written that one long formula like a sentence that never ends. These formulas may work, but they’re long, complicated, and if someone other than you needs to replicate them, they’re not going to have a good time understanding what’s going on. However, problems like this shouldn’t exist, especially considering Excel can now write its own formulas. But if you don’t trust Excel, or simply prefer to write your own formulas, the LET function is here to help you out. Related This Excel Trick Lets Me Write Formulas Like a Human Smarter Excel formulas with the simplicity of everyday language. This function changes how Excel formulas work What LET actually does—and why it matters Image taken from Microsoft Excel support page.Credit: Microsoft The LET function is built for one simple function, and if you’ve ever written code before, you can probably guess what it is: it lets you name things inside a formula. You give a value or calculation a name, and then you can use …

Excel’s data validation is far more powerful than just dropdowns — here’s what it can actually enforce

Excel’s data validation is far more powerful than just dropdowns — here’s what it can actually enforce

Published by skeptic

Most people set up a dropdown list to make spreadsheets smarter with Excel’s data validation. However, the Custom option in the Data Validation dialog accepts any formula that returns TRUE or FALSE, which means you can enforce rules that dropdowns were not built to handle. Duplicate prevention, text pattern requirements, date restrictions, and calculated limits all run through one setting that most people skip right past. Here’s how I use custom formulas to lock down my spreadsheets properly. Related Excel’s SEQUENCE function made me realize I’d been wasting time manually filling date columns It replaced my date-filling workflow, and I’m not going back. Custom formulas let you control far more than a list The Custom option accepts any TRUE/FALSE formula as a validation rule Excel’s data validation has several built-in options such as whole numbers, decimals, dates, text length, and, of course, dropdown lists — it’s one of those Excel tricks worth learning early. These cover common scenarios, but they’re rigid. The moment you need a rule that doesn’t fit neatly into one of those …

5 real world examples where Excel’s MAP function shines

5 real world examples where Excel’s MAP function shines

Published by skeptic

If you’re using Microsoft Excel 365, 2021, or 2024, then you have access to dynamic array functions. These functions take productivity to another level thanks to their “spill” functionality. This means a single dynamic array function returns multiple results that spill into adjacent cells, simplifying complex array operations. One that Excel pros use widely is the MAP function. Specifically, I would like to highlight some real-world examples that demonstrate just how powerful the MAP function is. It can be confusing, considering that you have to use it with LAMBDA functions. However, once you see how it actually works in the wild, you’ll realize that the MAP function can save you a ton of time. This is especially true if you deal with a lot of repetitive tasks. Multi-column logic Process multiple arrays in a single formula A common use case of the MAP function is creating multi-column logic. One formula can process multiple arrays at once. This means you no longer need to rely on the traditional fill-down method, where you enter a formula in …