Posts

About This Shortcut This shortcut will calculate all worksheets in all open workbooks. About This Shortcut This shortcut will calculate the active worksheet only.

Quick Links Quick Start Examples Troubleshooting Training Conditional formatting is a fantastic way to quickly visualize data in a spreadsheet. With conditional formatting, you can do things like highlight dates in the next 30 days, flag data entry problems, highlight rows that contain top customers, show duplicates, and more. Excel ships with a large number of “presets” that make it easy to create new rules without formulas. However, you can also create rules with your own custom formulas....

Explanation In this example the goal is to parse feet and inches out in the text strings shown in column B, and create a single numeric value for total inches. The challenge is that each of the two numbers is embedded in text. The formula can be divided into two parts. In the first part of the formula, feet are extracted and converted to inches. In the second part, inches are extracted and added to the result....

About This Shortcut This shortcut will a create a chart using data in current range on a separate worksheet. This is a very good way to quickly visualize data to check for patterns, outliers, or inconsistencies. About This Shortcut This shortcut converts a range to an Excel Table. To use this shortcut, first select the range of data you want to convert. Note: in Mac Excel 365, you can also use Control + T to convert a range to a table....

Explanation Data validation rules are triggered when a user adds or changes a cell value. This formula takes advantage of this behavior to provide a clever way for the user to switch between a short list of cities and a longer list of cities. In the worksheet shown, the data validation applied to C4 looks like this: =IF(C4="See full list",long_list,short_list) The IF function is configured to test the value in cell C4....

Purpose Return value Syntax =DAVERAGE(database,field,criteria) database - Database range including headers. field - Field name or index to count. criteria - Criteria range including headers. Using the DAVERAGE function The Excel DAVERAGE function gets the average in a given field for a subset of records that match criteria. The database argument is a range of cells that includes field headers, field is the name or index of the field to get a max value from, and criteria is a range of cells with headers that match those in database....

Purpose Return value Syntax =DGET(database,field,criteria) database - Database range including headers. field - Field name or index to count. criteria - Criteria range including headers. Using the DGET function The Excel DGET function gets a single value from a given field in a record that matches criteria. The database argument is a range of cells that includes field headers, field is the name or index of the field to get a max value from, and criteria is a range of cells with headers that match those in database....

Purpose Return value Syntax =EFFECT(nominal_rate,npery) nominal_rate - The nominal or stated interest rate. npery - Number of compounding periods per year. Using the EFFECT function The EFFECT function calculates the effective annual interest rate, given a nominal interest rate and the number of compounding periods per year. Nominal interest rate is the stated rate on the financial product. Effective annual interest rate is the interest rate actually earned due to compounding....

Purpose Return value Syntax =EXP(number) number - The power that e is raised to. Using the EXP function The EXP function finds the value of the constant e raised to a given number, so you can think of the EXP function as e ^(number), where e ≈ 2.718. The exponential function can be used to get the value of e by passing the number 1 as the argument. =EXP(0) // returns 1 =EXP(1) // returns 2....

Explanation In this example, the goal is to extract a date in a format like mm/dd/yy from a text string with a formula. The position of the date is not known, so the date must be located as a first step. This article explains two ways to solve this challenge: A “classic” formula based on the SEARCH function and the MID function that will work in any version of Excel. A modern formula based on the REGEXEXTRACT function, which is only available in the Beta channel of Excel 365....

Explanation The DATE function builds dates from separate year, month, and day values. One of its tricks is the ability to roll forward to correct dates when given days and months that are “out of range”. For example, DATE returns April 9, 2016, with the following arguments: =DATE(2016,1,100) There is no 100th day in January, so DATE simply moves forward 100 days from January 1 and returns the correct date....

Explanation In this example, the goal is to calculate the previous Sunday based on any given date. At a high level, this means we need to subtract some number of days from the given date. For example, if the given date is a Monday, we need to subtract 1 day. If the given date is a Tuesday, we need to subtract 2 days, and so on. The main challenge is to figure out how many days to subtract and for this, we use the WEEKDAY function ....

Transcript When you’re working with text in Excel, you’ll frequently need to change case. In this video we’ll look at three functions that allow you to easily change case of text in Excel: UPPER, LOWER, and PROPER. In this worksheet, we have two columns that contain names. Column B contains last names in uppercase text, and column C contains first names with the first letter capitalized. In column D, I’ll add a formula that capitalizes the first name using the UPPER function ....

Transcript In this video, we’ll look at how to chart average daylight hours for each month of the year, using sunrise and sunset data. This is the final chart. This project has a couple of interesting challenges. First let’s look at the available data. You can see we have data for both sunrise and sunset. Both columns contain valid Excel times. Now, if I try to create a column chart with just this data, we’ll have some problems....

Transcript When you have a pivot table set up, you might find that you want another pivot table to show a different view of the same data. In this video, I’ll show you how to base one pivot table on another. Here I have a set of sales data for chocolate products. I’ve also got another month’s worth of sales data in this second sheet. We’ll get to that in a minute....

Transcript In this video, we’ll look at how to remove a table from an Excel worksheet. In this workbook, we have a number of Excel Tables . Let’s look at some ways you can remove these tables. You won’t find a “delete table” command in Excel. To completely remove an Excel table, and all associated data, you’ll want to delete all associated rows and columns. If a table sits alone on a worksheet, the fastest way is to delete the sheet....

Transcript Excel’s conditional formatting has a few special purpose formats that can be really useful in certain situations. One of these, is the ability to highlight duplicates. If you’ve ever faced a situation where you need to quickly identify duplicates in a large list or table, this is a perfect solution. Here we have a table full of numbers, some of which are duplicates. As you can see, it’s very difficult to see at a glance which numbers appear more than once....

Transcript In this video, we’ll use the FILTER function to show the top or bottom results in a set of data. Here we have some test scores for a group of students. In column F, I want to set up a formula to display the top students by score. Now, I’m going to use the FILTER function, but we’ll need a way to determine the highest score, the second-highest score, and so on, and for this, I’ll use the LARGE function ....

Transcript In this lesson, we’ll look at another approach to centering text across more than one column. It’s called Center Across Selection. Unlike merging, Center Across Selection leaves all cells in place but still centers text across columns. Let’s take a look. Here we have the same table we looked at in an earlier lesson on aligning text across cells using Merge. Recall that Merge & Center physically merges cells and centers the remaining text....

This article explains in simple terms how to use INDEX and MATCH together to perform lookups. It takes a step-by-step approach, first explaining INDEX, then MATCH, then showing you how to combine the two functions together to create a dynamic two-way lookup. There are more advanced examples further down the page. The INDEX Function The MATCH function INDEX and MATCH together Two-way lookup with INDEX and MATCH Left lookup with INDEX and MATCH INDEX and MATCH with multiple criteria Case-sensitive lookup Finding the closest match INDEX and XMATCH More examples The INDEX Function The INDEX function in Excel is fantastically flexible and powerful, and you’ll find it in a huge number of Excel formulas, especially advanced formulas....