Excel tips & how-to, page 2
Convert Excel table to range and turn data range into table
Table is one of the most useful things in Excel that makes managing, calculating and updating data a lot easier. Under certain circumstances, however, a table format may not suite your needs. This short tutorial will teach you how to turn a table into a normal range without losing your data. Continue reading
How to remove characters/text from string in Excel
In this tutorial, we will look at the most common cases of removing characters in Excel. Want to delete specific text from multiple cells? Or maybe strip the first or last character in a string? Or perhaps remove only a specific occurrence of a given character? Whatever your task is, you will find more than one solution for it! Continue reading
How to remove special (unwanted) characters from string in Excel
When importing data to Excel from somewhere else, a whole lot of special characters may appear in your worksheets. Even more frustrating is that some characters are invisible, which produces extra white space before, after or inside text strings. Continue reading
How to delete text before or after a certain character in Excel
In the recent couple of articles, we've looked at different ways to remove characters from strings in Excel. Today, we'll investigate one more use case - how to delete everything before or after a specific character. Continue reading
How to remove first (left) or last (right) characters in Excel
When working with unstructured text data, you often need to parse it to retrieve relevant information. This article will teach you a few simple ways to remove any number of characters from the left or right side of a text string. Continue reading
How to remove text and leave numbers in Excel or vice versa
Imagine this: you receive raw data for analysis and find out that numbers are mixed with text in one column. In most cases, it will certainly be more convenient to have them in separate columns for closer examination. Continue reading
How to remove table formatting in Excel
By default, Excel tables are equipped with a number of great features including predefined table styles. In some situations, however, you may want to remove formatting keeping all other functionality of a table. This tutorial will teach you how to quickly do that. Continue reading
How to highlight duplicate text strings or words in Excel cell
Excel Conditional Formatting makes it possible to highlight duplicates in every possible way you can think of. But, as usual, there is a "but" - conditional formatting rules work on a cell level while you may want to highlight duplicate text rather than entire cells. This can only be done with macros. Continue reading
How to find and replace multiple values at once in Excel (bulk replace)
How do people usually search in Excel? Mostly, by using the Find & Replace tool, which works just fine for single values. But what if you have tens or even hundreds of items to replace? Luckily, there are a few more effective methods to perform mass replace in Excel, and we are going to look at each of them in detail. Continue reading
How to write recursive LAMBDA function in Excel with examples
With the introduction of the LAMBDA function, recursive computations in Excel have become available to anyone, not just VBA programmers. To put it simply, you can now construct formulas that behave like a programming language and allow you to achieve so much, with so little, so fast :) Continue reading
Excel LAMBDA function: how to write and use
Until recently, user-defined functions were the prerogative of programmers. The introduction of the LAMBDA function has made them available for everyone. With this wonderful addition to Excel's toolbox, we no longer need VBA skills to create our own custom functions. Continue reading
How to fix #SPILL! error with Excel VLOOKUP, INDEX MATCH and SUMIF
It is sad enough when a brand-new feature refuses to work in your Excel. But even more frustrating is when a good old thing stops working all of a sudden, and you are receiving an error for a formula that worked perfectly for years. This tutorial will teach you how to fix a #SPILL error in some popular formulas. Continue reading
How to find and remove duplicate cells in Excel
Is duplicate data in your worksheets causing you a headache? This tutorial will teach you how to quickly find, select, color or delete duplicate cells. Continue reading
Spilling in Excel: what it means and how to use it
The recent dynamic array update has bought a wealth of inspiring new capabilities. But all is centered around the core concept of "spilling". So, let's take a close look at this feature and how you can leverage it to improve your Excel experience. Continue reading
How to remove duplicates in Excel cell
When it concerns removing duplicate cells or rows, Microsoft Excel offers a number of different options. But when it comes to removing duplicate text within a given cell, Excel provides… nothing. Well, if there is no inbuilt function we need, let's go write our own one. Continue reading
Excel spill range explained
Spilling is one of the new features introduced in Excel 365 as part of the dynamic arrays functionality. Understanding the spill range concept is the focus of this tutorial. Continue reading
Implicit intersection and @ operator in Excel 365 and older versions
You have the feeling you know everything about your Excel workbooks, and then a @ character appears from nowhere in the beginning of your formulas. What does this mean? And how does it work precisely? Continue reading
Excel #SPILL! error - causes and fixes
Just imagine this: you upgraded to Microsoft Office 365 with the latest Excel updates and for some reason the tried and tested formulas you've been using for years have suddenly stopped working. Whatever you do, you are now getting a #SPILL error. Does anyone know what it means? Continue reading
Excel: prevent text from spilling over into next cells
When entering a long text in a cell, you may notice that sometimes it spills out into adjacent columns and at times gets hidden behind the neighboring cells. How does Excel determine what to do and how do you stop words from overflowing into next cells? Continue reading
Using NPER function in Excel with formula examples
When building corporate funds, financial analysts often wish to know how long it will take to reach the desired corpus. When applying for a loan, you may want to find out how many payments are required to repay it in full. For such tasks, Excel provides the NPER function. Continue reading
