Explanation In this example, the goal is to count cells equal to a specific value. In this case, we want to count cells that contain “red” in the range D5:D16. This problem can be solved with the COUNTIF function and the SUMPRODUCT function, as explained below. COUNTIF function One way to solve this problem is with the COUNTIF function, which is designed to count cells in a range that meet one specific condition....

Explanation In this example, the goal is to count “z” or “c” values in the named range data , but only when the column header is “A” or “B”. The formula used to perform this calculation is based on the SUMPRODUCT function : =SUMPRODUCT(ISNUMBER(MATCH(headers,{"A","B"},0))*ISNUMBER(MATCH(data,{"z","c"},0))) Working from the inside out, note that SUMPRODUCT contains a single argument , which is composed of this expression: ISNUMBER(MATCH(headers,{"A","B"},0))*ISNUMBER(MATCH(data,{"z","c"},0)) This expression is formed from two parts, each representing a logical test....

Explanation This formula uses structured referencing , a syntax that allows table parts to be called out by name. When a table is called with the name only, Excel returns a reference to the data region of the table only. In this case, the entire table range is B4:F104 so Table1 returns the range B5:F105 to the ROWS function. =ROWS(Table1) =ROWS(B5:F105) ROWS then returns a final result of 100. Note that the header row is not included in this count....

Purpose Return value Syntax =COUNTA(value1,[value2],...) value1 - An item, cell reference, or range. value2 - [optional] An item, cell reference, or range. Using the COUNTA function The COUNTA function counts cells that contain values, including numbers, text, logicals, errors, and empty text (""). COUNTA does not count empty cells. The COUNTA function returns the count of values in the list of supplied arguments . COUNTA takes multiple arguments in the form value1 , value2 , value3 , etc....

Purpose Return value Syntax =DDB(cost,salvage,life,period,[factor]) cost - Initial cost of asset. salvage - Asset value at the end of the depreciation. life - Periods over which asset is depreciated. period - Period to calculation depreciation for. factor - [optional] Rate at which the balance declines. If omitted, defaults to 2. Using the DDB function The DDB function calculates the depreciation of an asset in a given period using the double-declining balance method....

About This Shortcut This shortcut will display the Insert dialog box. If an entire row or entire column is selected, this shortcut will insert a new row or new column. Note: In Mac Excel 365, the Control key can be substituted for the Command key. Before Mac Excel 2016, this shortcut was Control + I. About This Shortcut This shortcut will insert rows as long as at least one row is selected....

The dot operator (.) is used to “trim” empty rows and/or columns from a range reference. It was introduced to Excel in 2024 along with the TRIMRANGE function and can be used to create a simple dynamic range that automatically adjusts to fit the data it contains. TRIMRANGE has more options than the dot operator, but both tools do the same thing: they remove unused rows and columns from a range reference, working from the outside in — empty rows and columns inside the data are not removed....

About This Shortcut The default behavior for drag and drop is to cut data in cells (similar to using Edit > Cut). Just select the cells you want to cut, hover the mouse cursor over the selection until the cursor turns into a four headed arrow, and drag the selection to a new location. About This Shortcut The default behavior for drag and drop is to cut data in cells, but you can also copy cells during drag and drop....

About This Shortcut If the active cell is empty, Excel will extend the selection to the first non-empty cell to the right. If the active cell is non-empty, Excel will extend the selection on the last non-empty cell to the right. On a Mac, the command key (⌘) can be used instead of the control key. About This Shortcut If the active cell is empty, Excel will extend the selection to the first non-empty cell to the left....

Explanation Starting from the inside out, the MID function is used to extract all text after “@”: MID(B5,FIND("@",B5),LEN(B5)) The FIND function provides the starting point, and for total characters to extract, we just use LEN on the original text. This is a bit sloppy, but it avoids having to calculate the exact number of characters to extract. MID doesn’t care if this number is bigger than the remaining characters, it simply extracts all text following “@”....

If you’ve ever had a formula like =A1=B1 return FALSE—even though the values in A1 and B1 seem exactly the same—you’ve run into one of Excel’s most puzzling quirks: the floating-point error. These tiny differences are usually invisible, but they can cause formula checks and even conditional formatting rules to fail in unexpected ways. This article explains why these errors happen, how to spot them, and how to fix them with simple techniques like rounding....

Purpose Return value Syntax =FREQUENCY(data_array,bins_array) data_array - An array of values for which you want to get frequencies. bins_array - An array of intervals (“bins”) for grouping values. Using the FREQUENCY function The FREQUENCY function counts how often numeric values occur in a set of data and returns a frequency distribution – a list that shows the frequency (count) of each value in a range at given intervals (bins). FREQUENCY returns the distribution as a vertical array of numbers that represent a “count per bin”....

Explanation The AND function takes multiple arguments and returns TRUE only when all arguments return TRUE. The TODAY function returns the current date. Dates in Excel are simply large serial numbers, so you can create a new relative date by adding or subtracting days. TODAY() + 30 creates a new date 30 days in the future, so when a days is greater than today and less than today + 30, both conditions are true, and the AND function returns true, triggering the rule....

Transcript Borders build directly on Excel’s grid system. They are one of the best ways to visually organize content in Excel, or to call attention to certain information. Let’s take a look. Before you apply borders, you may want to turn off the gridlines that appear by default in Excel. This will make it easier to see the borders you create. You can turn off gridlines by unchecking Gridlines on the Layout tab of the ribbon....

Transcript In this video we’ll look at how to calculate due dates with the WORKDAY and WORKDAY.INTL functions. The WORKDAY function returns a date in the future or past that takes into account weekends and, optionally, holidays. You can use the WORKDAY function to calculate things like ship dates, delivery dates, and completion dates that need to take into account working and non-working days. Now, to review, date calculations can be really simple....

Transcript By default, fields that you add to the values area of a pivot table are either summed or counted. Numeric fields are summed, and text fields are counted. However, you can change the math used for numeric fields to several other functions. Let’s take a look. Here we have the product sales data we’ve looked at previously, with an empty pivot table, ready to use. Let’s start off by adding Product as a row label....

Transcript In this video, we’ll look at how to copy and paste a pivot table without the underlying data. When you create a pivot table with source data in a worksheet, Excel quietly creates a hidden data cache that travels along with the pivot table. If you copy and paste the pivot table into a new worksheet, remember that the data will come along with the pivot table in the hidden cache....

Transcript In this lesson, we’ll look at how to create a custom time format. Excel provides a good selection of time codes that can be used to assemble a variety of custom time formats. Let’s take a look. Let’s look first at the Time code reference table. This table shows the time codes available for custom time formats. There are codes for hours, minutes, and seconds with and without leading zeros....

Transcript In this video we’ll look at the CONCATENATE function , which is an alternative to using the ampersand character to join values. This is the same example we looked at previously: a table which contains first, middle, and last names. In column E, I’ll add a formula that uses the CONCATENATE function to join these names together into a full name. With the CONCATENATE function, just enter each value you want to join together as a separate argument....

Transcript In this video, we’ll look at how to quickly find formulas and trace how they are related to one another using the concept of precedents and dependents. Here we have a simple model that shows the expense of making coffee at home vs. buying coffee in a coffee shop. Let’s take a look through the formulas in this model to see how they work. First, let’s find all the formulas....