How to Combine (Concatenate) Data from Multiple Rows into One Cell

Combine (Concatenate) Data From Multiple Rows into a List LeadOften, our data comes to us in ways that are better for calculating than they are for reading. One example of this is a standard data table that repeats headers and labels for each row. When we are sorting, alphabetizing, and filtering, this format is incredibly useful. However, when we want to read a summary of the information, these tables are difficult to work with. Sometimes, it is better to have all the options in a table listed in a comma-separated list rather than broken out across many rows. Unfortunately, Excel doesn’t have a simple function to make this possible. Here is a quick set of data manipulation steps that will build a comma-separated list in a single cell from multiple rows of data… Continue reading How to Combine (Concatenate) Data from Multiple Rows into One Cell

How to Do a Reverse String Search in Excel Using FIND

Reverse String Search LeadExcel has some great functions available for working with long phrases (strings). It has RIGHT, LEFT, and TRIM to manage sub-strings. It also has FIND and SEARCH to look for specific characters or sub-strings inside a phrase. The only problem is that FIND and SEARCH look from left to right in a string. If you need to look for something starting at the end of a string, there isn’t a good tool built into Excel to do so. Fortunately, it is possible to build a formula to do just that – a reverse string search. Here’s how… Continue reading How to Do a Reverse String Search in Excel Using FIND

Extract a List of Values Filtered by Criteria with Sub-Arrays

Sub Array LeadWhen you are working with large data sets, Excel’s built-in filters are a lifesaver, letting you get straight to the sub-set of data that you need. Sometimes, though, you need to be able to pull a set of data dynamically based on criteria that change. When the filter conditions change often, Excel’s filters fall short. Instead, there is a set of functions that can extract a data table from a larger data set based on specific criteria that you set. This tutorial will show you how to list values from a table based on filter criteria using sub-arrays and the SMALL function. Continue reading Extract a List of Values Filtered by Criteria with Sub-Arrays

How to VLOOKUP with Multiple Criteria Using INDEX and MATCH

Multiple VLOOKUP LeadVLOOKUP is a great tool for pulling data from tables, but it has a handicap: it can only work with one criteria for matching information. If there are multiple rows in your sheet with the same information, you’ll only get the first one. If you need to use two or more conditions to match a specific piece of data, you’re out of luck. Fortunately, Excel has a pair of functions called INDEX and MATCH that can help produce the same results as VLOOKUP with multiple criteria. Here’s a quick tutorial to help you learn how… Continue reading How to VLOOKUP with Multiple Criteria Using INDEX and MATCH

How to Create Waterfall Charts in Excel

Waterfall LeadWhen you want to see how different parts of a total contribute to the final calculation, a waterfall chart (sometimes called a cascade chart or a bridge chart) can be a very useful visualization tool. Unfortunately, Excel doesn’t have a built-in waterfall chart option. With a bit of creativity, however, it’s possible to build one using a modified stacked bar chart. This tutorial will show you how to build your own waterfall chart, complete with different colors for positive and negative values and connecting lines… Continue reading How to Create Waterfall Charts in Excel

The Definitive Guide to Custom Number Formats in Excel

Number Format LeadExcel has a lot of built-in number formats, but sometimes you need something specific. Whether you’re representing a little-used currency, tracking in-stock units, or want to color code profits and losses, you are in need of a custom number format. Number formatting in Excel is pretty powerful but that means it is also somewhat complex. This is the definitive guide to Excel’s custom number formats… Continue reading The Definitive Guide to Custom Number Formats in Excel

Converting Decimal-Separated Dates to Excel Date Format

Decimal Date LeadDates don’t always get imported into Excel in a nice, clean, ready to use format. When they don’t, it can be a huge hassle – un-formatted dates can’t be added or subtracted, or even filtered or sorted easily. It is usually best to convert them to the standard Excel date format. Excel has an entire array of functions you can use to work with them once you do (you can learn about them here). Excel’s string manipulation functions can help you convert date text to the Excel date format. Let’s walk through an example… Continue reading Converting Decimal-Separated Dates to Excel Date Format

The Definitive Guide to Excel Error Types and Error Handling

Catching Errors LeadExcel functions like VLOOKUP, MATCH, and INDEX are great tools, but when they don’t work, they throw errors that can break an entire spreadsheet if referenced incorrectly. Error messages can be especially bad if they show up on end-user worksheets like reports and dashboards. Fortunately, Excel has a way to catch errors like #VALUE!, #NUM!, and #REF! before they show up. Learn how to handle error messages in Excel here… Continue reading The Definitive Guide to Excel Error Types and Error Handling

The Definitive Guide to Using Dates and Times in Excel

Date Time LeadDates and times are two of the most common data types people work with in Excel, but they are also possibly the most frustrating to work with, especially if you are new to Excel and still learning. This is because Excel uses a serial number to represent the date instead of a proper month, day, or year, nevermind hours, minutes, or seconds. It’s made more complicated by the fact that dates are also days of the week, like Monday or Friday, even though Excel doesn’t explicitly store that information in the cells. Here is the definitive guide to working with dates and times in Excel… Continue reading The Definitive Guide to Using Dates and Times in Excel

How to Automatically Delete Blank Cells to Organize Data

Delete Blanks LeadData doesn’t always import or paste into Excel as nicely as you want. Sometimes, all the information is there, but extra blanks and spaces are carried along with the data cells. At first glance, this looks like it could be a very tedious problem to solve… It could take ages to manually take them out, and sometimes the data order is sensitive, so it can’t be sorted (to bring all the blanks together). Fortunately, there is a way to select all the blanks in a selection simultaneously for deletion. Let’s walk through an example of how… Continue reading How to Automatically Delete Blank Cells to Organize Data