Some Excel calculations aren't interested only in the final answer, they need every step in between. The SCAN function is designed specifically for this type of problem. Continue reading
Checking whether a text string matches a specific format in Excel can sometimes require multiple nested functions and complicated logic. REGEXTEST simplifies this process by using regular expressions to test patterns directly. Continue reading
Excel has plenty of useful text functions, but sometimes they still can't handle complex extraction tasks. When standard functions are not flexible enough, REGEXEXTRACT comes to the rescue. Continue reading
Some Excel tasks are simple. Others involve cleaning up text that looks like it survived a storm. If you often work with messy imported data or inconsistent strings, the REGEXREPLACE function can save a lot of formula gymnastics. Continue reading
Cleaning up text in Excel sheets isn't always as simple as it should be. Especially, when all you want is a normal-looking sentence and not a mix of uppercase and lowercase chaos. Although there is no built-in function for sentence case in Excel, a properly written formula or macro can take care of it in seconds. Continue reading
Messy data happens to everyone. One minute you're working with a neat list, and the next you're staring at extra spaces, inconsistent formatting, and mystery characters. The good news? Excel gives you plenty of ways to clean things up, you just need to know where to look :) Continue reading
Text in Excel has a funny way of getting out of control. One column is ALL CAPS, another is all lowercase, and somewhere in the middle is a mix that is neither here nor there. If your data looks like it can't decide on a writing style, Excel's case functions are here to bring a little order and sanity back :) Continue reading
Sometimes Excel data looks perfectly normal, until a simple task turns into a head-scratcher. You import some data into your worksheet, everything looks fine… and then suddenly your formulas stop working, searches fail, and nothing matches. Welcome to the world of nonprintable characters. Continue reading
Excel formulas often start in a single cell, but your data rarely stays that small. As your dataset grows, manually copying formulas can quickly turn into a tedious task, not to mention how easy it is to make mistakes. Continue reading
When working with continuously updating datasets in Excel, it's common to reference ranges that contain extra blank rows or columns. These empty cells usually don't break formulas, but they can produce messy results, such as zeros or errors for empty rows, or unnecessary blank space in summary tables and reports. Continue reading
If you ever find yourself writing the same formula for multiple columns, BYCOL can save you time and effort. Instead of copying, adjusting references, and making sure nothing gets accidentally overwritten, BYCOL just takes the entire range and runs the calculation for each column automatically. Continue reading
When working with large datasets in Excel, you often hit the same question: "How do I apply this calculation to every row in a range without copying formulas down?". BYROW is made for exactly that. Continue reading
If your worksheet looks fine until you start typing and then words suddenly get cut off or hidden, you've discovered one of Excel's favorite tricks. While Excel doesn't automatically resize every cell, it does offer a few easy ways to expand them. Continue reading
We've got used to expressing our emotions in online communications with emojis and smileys. But do you know that you can add them to your spreadsheets as well? In Excel, emojis work like text characters, which means you can insert them into cells, copy and paste them, and even use in formulas. Continue reading
Many Excel users work with data scattered across several sheets: one per month, per region, or per employee. If you've ever copied and pasted numbers from different sources to create a report, you know how time-consuming it can be. Continue reading
Today we'll tackle a problem that many Excel users are struggling with daily – merging multiple Excel sheets into one without spending hours copying and pasting. Continue reading
Finding a single match in Excel is easy with VLOOKUP or XLOOKUP. The challenge comes when you need to pull all matching values, not just the first one. To accomplish the task in pre-dynamic Excel, you had to rely on several nested functions working together, which was quite complex and far from beginner-friendly. Continue reading
Excel XLOOKUP and XMATCH are strong functions on their own, and regex match makes them even more versatile. Instead of searching for exact text, you can now use full-blown regular expressions to define what you are after. Continue reading
Calculating how much each value contributes to a total is something many Excel users do every day. In older versions, this usually meant creating your own formula that divided a part by the whole. The PERCENTOF function gives you a quick and direct way to return that percentage without building a formula manually. Continue reading
If you've already used Excel checkboxes to track project milestones, a progress bar is the perfect next step. It gives you a clear visual snapshot of how close you are to reaching a goal. In Excel, you can easily create a progress bar using conditional formatting or charts, no coding required. Continue reading
Whenever you want to create a to-do list or project tracker, attendance sheet or review checklist, there is one small element you'll definitely need – a checkbox. In modern versions of Excel, adding checkboxes is much simpler than you might think, and you can set it up in under a minute. Continue reading
With Excel Copilot, creating VLOOKUP and XLOOKUP formulas is as easy as explaining your task in plain language. This guide walks you through using Copilot in both the desktop Excel app and in browser to perform quick, accurate lookups – no advanced formula skills needed! Continue reading
Among the latest additions to the Excel family of dynamic array functions are two aggregation tools: GROUPBY and PIVOTBY. While useful in their basic form, they truly shine when paired with custom Lambda functions, enabling you to summarize data in ways not possible with predefined Eta lambdas. Continue reading
Learn how to turn your data into clear and colorful summary reports using GROUPBY and PIVOTBY formulas, enhanced with conditional formatting. Continue reading
For decades, Excel users have relied on pivot tables to transform rows and columns of information into meaningful reports. Now, there's something even better: the new PIVOTBY function. This handy tool lets you summarize and analyze data right within a formula without the need for separate pivot tables. Continue reading
Microsoft Excel has always been great for sorting through lots of data and making sense of it. But now, there's a simple way to do it all with just a formula. Introducing the GROUPBY function – a handy and efficient tool to group and aggregate data without leaving the comfort of your formula bar. Continue reading
You've got your data organized in a pivot table and are ready to dig in, but there is a catch – it has a time element. Maybe it's sales figures over months or project deadlines scattered throughout the year. How do you make sense of it all without drowning in numbers? The answer is timelines! Continue reading
If you've ever felt overwhelmed by a large and cluttered spreadsheet, you're not alone. Fortunately, Excel pivot charts provide an easy way to create stunning visualizations of your data. Pivot charts are like magic: they let you summarize, analyze and explore your data with just a few clicks. Continue reading
In the world of spreadsheet software, Excel has long reigned as the go-to tool for crunching numbers and analyzing data. But what of you had a smart assistant helping you navigate Excel's vast capabilities, giving you expert advice and quick insights. Continue reading
One of the most common tasks in Excel is to look up values in a table based on some criteria. In the past, Excel users relied on the VLOOKUP function for this job. However, it had its fair share of limitations, leading savvy Excel enthusiasts to craft a more resilient solution using the INDEX MATCH combination. Continue reading