Excel tutorials, functions and advanced formula examples, page 3
Converting CSV to Excel: problems and solutions
The name CSV (comma separated values) implies the use of comma to separate data fields. But that is in theory. In practice, many so-called CSV files separate data using other characters such as semicolon or tabs. The lack of standard spawns various problems with CSV to Excel conversions. Continue reading
Video: How to replace special characters in Google Sheets
There's a small add-on for Google Sheets we offer that proves itself super useful. See how it quickly replaces accented characters, smart quotes with straight ones, codes with their symbols and vice versa. Continue reading
How to create custom user defined functions in Excel
This article will introduce you to user defined functions in Excel. You’ll see different variants of user defined functions and learn how to create and apply them. Continue reading
CELLCOLOR & VALUESBYCOLORALL: functions for Google Sheets to work with colored cells
This tutorial introduces 2 new functions from our Function by Color add-on for Google Sheets: CELLCOLOR & VALUESBYCOLORALL. Use them to sum & count cells not only by their colors but also by the common contents. Ready-made SUMIFS & COUNTIFS formulas are included ;) Continue reading
How to merge multiple CSV files into one Excel workbook
If you often export files in the CSV format from different applications, you may end up having a bunch of individual files relating to the same subject matter. The question is - is there a simple way to import them all into a single Excel workbook? Continue reading
Video: How to use CELLCOLOR & VALUESBYCOLORALL in Google Sheets
Function by Color add-on supplies your Google Sheets with 2 new custom functions: CELLCOLOR & VALUESBYCOLORALL. Watch how to use them for your colored cells in any of your own formulas. Continue reading
Excel Regex to remove certain characters or text from strings
Have you ever thought how powerful Excel would be if someone could enrich its toolbox with regular expressions? We have not only thought but worked on it :) And now, you can add this wonderful RegEx function to your own workbooks and wipe out substrings matching a pattern in no time! Continue reading
Excel Regex to find and replace strings using regular expressions
Microsoft Excel provides a number of options to replace one piece of text with another. Why would one want to complicate things with regexes? Because Excel's standard features can only process an exact string that you specify. Continue reading
Excel Data Validation using regular expressions
When it comes to restricting user input in Excel worksheets, Data Validation is indispensable. It can do anything you can possibly imagine. What if I want to allow entering only valid email addresses or strings that match a specific pattern? Alas, that's not possible. Regex you say? Hmm… that might work! Continue reading
Regex Match in Excel: regular expression matching with examples
When you need to find a certain value in a range of cells, you use the Excel MATCH function. When looking for a specific string in a cell, the FIND and SEARCH functions come in handy. And how do you know if a cell contains information that matches a given pattern? Obviously, by using regular expressions. Continue reading
CONCAT function in Excel to concatenate text strings
Traditionally, string concatenation in Excel is done with the help of the CONCATENATE function or the ampersand symbol. In modern Excel, these old-school methods give way to CONCAT - a newer and improved function to combine text from multiple cells into one cell. Continue reading
How to add text or character to every cell in Excel
When working with text data in Excel, you may sometimes need to add the same text to existing cells to make things clearer. For example, you might want to put some prefix at the beginning of each cell, insert a special symbol at the end, or place certain text before a formula. Continue reading
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
Video: How to count colored cells in Google Sheets
If you haven't tried our Function by Color add-on for Google Sheets yet, you will be pleasantly surprised. It calculates values based on their font, fill, or both colors using 13 aggregation functions. Today's video will show you how it works. Continue reading
How to view shared files in OneDrive and stop sharing
OneDrive is a perfect place for sharing your files and collaborating with your colleagues. After someone has shared something with you, the next question is - where do I find those files? 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 share files securely with OneDrive
There are a few different ways to share documents on OneDrive, and you may be asking yourself which is the best one. For the most part, the answer depends on the situation and your ultimate goals. But I can show you the most secure way. Continue reading
War in Ukraine. 