Dec
18
How to merge sheets in Excel and combine their data
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
"Excel" category
Dec
9
How to lookup and return multiple matches in Excel
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
Dec
2
Excel XLOOKUP and XMATCH with regex match – formula examples
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
Nov
26
Excel PERCENTOF function to calculate percentage of total
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
Nov
18
How to create Excel progress bar based on checkboxes
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
Nov
11
How to add a checkbox in Excel 365 and use in formulas
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
Mar
12
How to VLOOKUP and XLOOKUP in Excel with Copilot
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
Sep
18
How to use custom Lambda functions in Excel GROUPBY and PIVOTBY formulas
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
Jul
31
Auto-format Excel GROUPBY and PIVOTBY results with conditional formatting
Learn how to turn your data into clear and colorful summary reports using GROUPBY and PIVOTBY formulas, enhanced with conditional formatting. Continue reading
Jul
10
Excel PIVOTBY function - formula alternative to pivot tables
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
Jun
26
Excel GROUPBY function to group rows and aggregate values
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
Mar
6
How to create timeline for Excel pivot tables and charts
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
Feb
28
How to make pivot charts in Excel
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
Feb
14
How to add and use Copilot in Excel with examples
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
Jan
24
XLOOKUP vs INDEX MATCH in Excel: which is better and faster?
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
Jan
17
XLOOKUP vs VLOOKUP in Excel: differences and advantages
If you work with Excel, you probably know how useful the VLOOKUP function is. But did you know that there is a newer and more powerful function called XLOOKUP that can do even more? In this tutorial, we will compare these two functions and see how they differ in syntax, functionality and performance. Continue reading
Jan
9
Excel XLOOKUP with multiple criteria
In Excel, there's this awesome function called XLOOKUP, which makes it really easy to find specific values in your tables. And guess what? It doesn't just look for one thing, it can also search using different conditions, so you can get exactly what you need from your data. Continue reading
Nov
29
How to repeat the last action in Excel with shortcut or command
Picture this: you've just performed some useful task in your worksheet, such as typing in numbers or formatting cells. Now, here's the exciting part - you can easily do that exact same thing with a simple keyboard shortcut. Continue reading
Nov
22
How to redo in Excel: comprehensive guide
As you work on complex spreadsheets in Excel, you may find yourself making changes, undoing them, and sometimes needing to re-apply your actions again. The Redo function is a handy feature that allows you to reverse the effects of an "undo" and restore your spreadsheet to a specific point in time. Continue reading
Nov
1
Excel Quick Analysis tool with examples
Microsoft Excel is a powerful spreadsheet application that can handle various types of data, but sometimes, you need to quickly get insights from your without diving deep into complex functions and formulas. That's where the Quick Analysis tool comes into play. Continue reading
Oct
18
How to use Power Query in Excel – practical examples
In the previous article, we laid the groundwork by introducing the basics of Power Query. Now, it’s time to put it to use in real-world scenarios. Continue reading
Oct
12
Power Query in Excel tutorial: how to get, use and auto-refresh
Here's the conundrum - how can we import, transform, and analyze data in Excel without spending hours on manual manipulations? The solution is Power Query, an invaluable tool for any data analyst, saving hours of time, reducing manual errors, and transforming an unruly informational chaos into a harmonious symphony of insights. Continue reading
Oct
4
Excel formula to get day of week from date
When you're using Excel to manage project deadlines, schedule appointments, or analyze data trends, it's often handy to know the day of the week for specific dates. Excel has a variety of tools to help you figure this out. Continue reading
Sep
27
How to count frequency of word / text in Excel using custom function
Microsoft Excel is a fantastic tool for working with numbers, but it can become challenging when it comes to counting specific words in your spreadsheets. If you've ever struggled with Excel's complex formulas for this task, there's an effective solution - creating a custom function that makes text frequency analysis a breeze. Continue reading
Sep
21
Excel crashes or freezes when highlighting cells in big datasets: reasons and solutions
If you frequently work with extensive spreadsheets, you've likely encountered a scenario where Excel becomes irresponsive or stops working when trying to select numerous cells at once. You may have also experienced the frustration of losing unsaved changes in your workbook. Continue reading
Sep
13
How to extract word from string in Excel: first, last, Nth, and more
Have you ever faced the challenge of extracting specific words from text within an Excel cell? Getting the first or last word can be relatively easy using built-in Excel functions. However, extracting an arbitrary word from the middle of a sentence can be much more complex. Continue reading
Sep
6
Adding second axis in Excel: chart with two X or Y axes
In Excel graphs, you're used to having one horizontal and one vertical line to display your data. But things can get a bit tricky when you're dealing with different types of data, like numbers and percentages, or when you want to compare two sets of data that don't quite match up in terms of time or size. This is where a second axis comes into play. Continue reading
Aug
30
How to extract first line in Excel cell
In the world of data analysis, Excel stands as a powerful tool that offers various features to optimize complex tasks. One such task is extracting specific content from a cell, a technique that can prove invaluable in managing and presenting data efficiently. Continue reading
Aug
22
How to omit / remove the first line in Excel cell
When dealing with multi-line data in Excel, you may come across scenarios where you need to omit the first line of text from a cell. This could be to remove headers or unnecessary information from imported data, clean up data for analysis, or simply extract relevant information. Continue reading
Aug
2
How to undo in Excel: shortcut, multiple actions, not working
Whether you're a seasoned Excel user who has been navigating spreadsheets for years or just starting to explore its capabilities, knowing how to correct mistakes without unnecessary hassle is essential. Excel's Undo function is like a safety net, allowing you to backtrack and make adjustments without fear of losing vital information or wasting time. Continue reading