Cleaning up text in Excel: CLEAN, TRIM, and SUBSTITUTE

In Excel, you will occasionally deal with text fields containing line breaks, multiple spaces, hyphenation, currency and letterlike symbols, etc. This is common when dealing with unrestricted user-inputted data and can cause issues. In particular, these kinds of values will be hard to connect with related values without cleanup. When we MATCH those values with related data tables, errors are bound to occur.   Basic cleanup approaches include manual replacement of all of the unnecessary characters with a desired or

Line breaks in Excel

A line break is the termination of one line of text, and the beginning of the next line. If needed inside of a single Excel cell, line breaks can be achieved in two ways: the Wrap Text feature allows you to make text appear to be structured into multiple lines, manual line breaks can be entered by pressing Alt + Enter.   Consider the following sentence: As of Unicode version 14.0, there are 144,697 characters with code points, covering 159

Combining INDIRECT with ADDRESS

We’ve previously covered how we can use any function to generate a text that could be a valid cell reference, both column and row part of the address, and then use the INDIRECT function to convert that text to a cell reference. This process can be greatly enhanced by the ADDRESS function.   The ADDRESS function returns a text string that represents the address of a particular cell. Row, column, type of reference (locked or absolute), reference style (A1 or

How to MATCH numbers formatted as text in Excel

You will sometimes encounter errors while trying to MATCH or LOOKUP data in Excel if your numbers are formatted as numbers in one of your tables, and as text in another table. While special formats are available in Excel, they are relatively rarely used and are limited. Some “numbers” such as identification numbers are more often stored as text. This is done in order to add leading zeros, hyphens and other characters to that numbers. However, if we try to

Top 10 lists in Excel

When analyzing large amounts of data in Excel, the best approach often is to retrieve top (10) values. Consider the following example: Here, we have a table with the invoices from the 29th of January. There are 904 invoices for that day. Values in the invoice column are unique as those are invoice numbers. Values in the user column are not unique, as some users have multiple invoices on that day, and values in the team column are not unique

Combining SUMIFS with INDEX MATCH

You will sometimes want to retrieve data from a table with values both in rows and columns and based on multiple (three or more criteria). There is, as usual, more than one way to do this. However, most of those ways require you to use something called array formulas – in essence, you will have to press Ctrl Shift Enter combination on your keyboard every time you want to calculate that formula. Combining SUMIFS with INDEX MATCH enables us to

Lookup with unique identifiers

INDEX function The INDEX function returns the value from a range of cells based on row and column specified in the function arguments. We are simplifying here, the INDEX function can actually return values from one or more arrays – i.e. broader term for lists of data which includes cell ranges. But the most common use is with cell ranges, as that is the typical way of structuring data in Excel.   Syntax of the INDEX function is as follows: