# Posts tagged "Excel tips & how-to", page 3

## Case-sensitive SUMIF and SUMIFS in Excel and Google Sheets

The SUMIF and SUMIFS functions available in Microsoft Excel and Google Sheets are case-insensitive by nature. To conditionally sum cells treating lowercase and uppercase letters as different characters, you'll have to come up with something else. Continue reading

## How to group columns in Excel

If you feel overwhelmed or confused about the extensive content of your worksheet, you can organize the columns in groups to easily hide and show different parts of your sheet, so that only the relevant information is visible. Continue reading

## How to find sum of largest N numbers in Excel

In some situations, you may need to sum specific numbers in a range, say top 3, 5, 10 or n. That might be a challenge because Excel has no inbuilt function for this. But as always, there is nothing that would prevent you from constructing your own formulas :) Continue reading

## SUMIF multiple columns with one or several criteria in Excel

Doing a conditional sum in Excel is a piece of cake as long as all the values to be totaled are in one column. Summing multiple columns is a problem because both the SUMIF and SUMIFS functions require the sum range and criteria ranges to be equally sized. Continue reading

## How to make a heat map in Excel with or without numbers

Microsoft Excel has a number of inbuilt features to create graphs. Regrettably, a heat map is not on board. Luckily, there is a quick and simple way to create a heat map in Excel with conditional formatting. Continue reading

## How to convert column number to letter in Excel

When building complex formulas in Excel, you may sometimes need to get a column letter of a specific cell or from a given number. This can be done in two ways: by using inbuilt functions or your own one. Continue reading

## Using ISERROR with VLOOKUP in Excel

VLOOKUP is one of the most confusing Excel functions plagued with many issues. Using VLOOKUP with ISERROR can help you catch all possible errors and handle them in a way most appropriate to your situation. Continue reading

## How to use ISERROR function in Excel

When you write a formula that Excel does not understand or cannot calculate, it draws your attention to the problem by showing an error message. The ISERROR function can help you catch errors and provide an alternative when an error is found. Continue reading

## Excel split screen - view different parts of a sheet at once

When working with large datasets, it can be helpful to see a few areas of the same worksheet at a time to compare different subsets of data. This can be done by using Excel's Split Screen feature. Continue reading

## How to open Excel files in separate windows and multiple instances

Having spreadsheets in two different windows makes many Excel tasks easier. One of the possible solutions is viewing workbooks side by side, but this eats a lot of space and isn't always the best option. Continue reading

## Excel IFNA function to handle #N/A errors

Getting a lot of #N/A errors in your worksheets and are curious to know if there is a way to replace them with a user-friendly message? IFNA formula is the solution you need. Continue reading

## How to number columns in Excel and convert column letter to number

The tutorial talks about how to return a column number in Excel using formulas and how to number columns automatically. Continue reading

## ISNA function in Excel with formula examples

When Excel cannot find what it is asked for, a #N/A! error appears in a cell. To intercept and handle such errors, you can use the ISNA function. Continue reading

## How to change Excel CSV delimiter to comma or semicolon

Imagine this: you want to export your Excel data to another application. Whatever CSV option you use, the result is a semicolon-separated file instead of comma-separated you really wanted. The setting is default, and you have no idea how to change it. Continue reading

## How to highlight active row and column in Excel

When viewing a large worksheet for a long time, you may eventually lose track of where your cursor is and which data you are looking at. To know exactly where you are at any moment, get Excel to automatically highlight the active row and column for you! Continue reading

## VLOOKUP to compare two columns in Excel for common values and missing data

When you have data in two different lists, you may often need to compare them to see what information is missing in one of the lists or what data is present in both. Comparison can be done in many different ways, which method to use depends on exactly what you want from it. Continue reading

## How to use and store custom functions in Excel

Today we’ll continue exploring custom Excel functions. Now that you know how to create UDFs, let’s dig a bit deeper and learn how to use and store user-defined functions in Excel. Continue reading

## RegEx in Excel: using regular expressions in formulas

At first sight, Excel has everything you could ever need for text string manipulations. Hmm… what about regular expressions? Oops, there are no built-in Regex functions in Excel. But no one says we cannot create our own ones :) Continue reading

## Debug user defined functions

As you already know how to create UDFs (and, I hope, you’ve also tried applying them in your Excel), let’s dig a bit deeper and see what can be done in case your user defined function is not working. Continue reading

## How to remove whitespace and empty lines in Excel with Regex

Whichever input data you are using, you'll hardly encounter a dataset without spaces. In most cases, whitespace is good. In some situations, however, it may become evil - extra spaces can mess up your formulas and make your worksheets almost unmanageable. Continue reading

## How to extract substrings in Excel using regular expressions (Regex)

Microsoft Excel provides a number of functions to extract text from cells. Those functions can cope with most of string extraction challenges in your worksheets. Most, but not all. When the Text functions stumble, regular expressions come to rescue. Continue reading

## Why is Excel User Defined Function not working?

This article shows how to add a help text to UDF and explains why these functions are not recalculated. You will see the differences between volatile and non-volatile custom functions. Continue reading

## 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

## 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

## 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

## 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 join 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