Counting the number of hours between dates and times in Excel is normally a straightforward process. Since Excel stores dates as decimal numbers, you can just subtract the two to get your result. But when you are working with business hours, like for time sheets or hours worked, you need to take weekends and holidays into account. Excel has a function called NETWORKDAYS, but this only works with complete days. To calculate the Net Work Hours between two dates, we need to build our own formula. Here’s how… Continue reading How to Calculate Net Work Hours Between Two Dates
How to Combine (Concatenate) Data from Multiple Rows into One Cell
Often, 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
Excel 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
When 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
VLOOKUP with Multiple Values or Criteria Using INDEX and MATCH (How To)
VLOOKUP 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 VLOOKUP with Multiple Values or Criteria Using INDEX and MATCH (How To)
How to Create Waterfall Charts in Excel
When 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
Excel 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 an Excel 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
Dates 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
Excel 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
Dates 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