Explanation In this example, the goal is to count visible rows where Region=“West”. Row 13 meets this criteria, but has been hidden. The SUBTOTAL function can easily generate sums and counts for visible rows. However, SUBTOTAL is not able to apply criteria like the COUNTIFS function without help. Conversely, COUNTIFS can easily apply criteria but is not able to distinguish between rows that are visible and rows that are hidden. One solution is to use Boolean logic to apply criteria, then use the SUBTOTAL function together with the OFFSET function to check visibility, and then tally up results with the SUMPRODUCT function....

Explanation Data validation rules are triggered when a user adds or changes a cell value. In this formula, the LEFT function is used to extract the first 3 characters of the input in C5. Next, the EXACT function is used to compare the extracted text to the text hard-coded into the formula, “MX-”. EXACT performs a case-sensitive comparison. If the two text strings match exactly, EXACT returns TRUE and validation will pass....

About This Shortcut This shortcut allows you to display various lists within Excel without using a mouse. With Alt + down arrow, you can access lists in 3 different contexts: (1) Access autocomplete items when doing repetitive data entry in a column – previously entered values in cells above will be displayed when autocomplete is enabled. (2) Access filter drop-down menus when working in a filtered list or table – unique values that appear in a list or column will be displayed with a checkbox to allow filtering....

About This Shortcut This shortcut will display the Modify Styles dialog box. About This Shortcut Use this shortcut to display the shortcut menu. This is the menu you see when right-clicking with the mouse. If you use TechSmith’s Snagit 12+ on Windows, be aware that it’s default shortcut keys may conflict with this Excel shortcut.

Purpose Return value Syntax =DSTDEVP(database,field,criteria) database - Database range including headers. field - Field name or index to count. criteria - Criteria range including headers. Using the DSTDEVP function The Excel DSTDEVP function gets the standard deviation of data that represents an entire population, extracted from records matching criteria. Basically, DSTDEVP calculates standard deviation with a subset of data, like a “standard deviation IF” formula. The database argument is a range of cells that includes field headers, field is the name or index of the field to query, and criteria is a range of cells with headers that match those in database....

About This Shortcut Use this shortcut to duplicate objects on the worksheet, including charts, shapes, smart art, and text boxes. This is a handy way to maintain consistent formatting across a number of like objects. Note: as of February 2022, the Mac shortcut doesn’t seem to work in Excel 365. About This Shortcut Use this shortcut to snap objects to the Excel grid, including charts, shapes, smart art, and text boxes....

Explanation In this example, the goal is to create a formula that performs a dynamic two-way sum of all City and Size combinations in the range B5:D17 . The solution shown requires four basic steps: Create an Excel Table called data List unique cities with the UNIQUE function List unique sizes with the UNIQUE function Generate sums with the SUMIFS function Create the Excel Table One of the key features of an Excel Table is its ability to dynamically resize when rows are added or removed....

Quick Links Overview Why Pivot? Tips Examples Training Pivot tables are one of the most powerful and useful features in Excel. With very little effort, you can use a pivot table to build good-looking reports for large data sets. If you need to be convinced that Pivot Tables are worth your time, watch this short video . Grab the sample data and give it a try. Learning Pivot Tables is a skill that will pay you back again and again....

Explanation The first thing this formula does is check the date in column D against the date in the header (E4:J4). =IF($D5<=E$4 Translated: if the date in column D is less than or equal to the date in row E. Note these are mixed references . The column in $D5 is locked, and the row in E$4 is locked. This allows the formula to be copied across the table. If the result of this test is TRUE, the IF function returns the expense from column C, otherwise IF returns zero (0)....

About This Shortcut Extends the current selection one screen to the left. Note: this shortcut is unusual on a Mac because t he Command key is required (Option doesn’t work), even on Macs with Page Up / Page down keys. About This Shortcut This shortcut will extend the selection from the active cell to the next cell to the left that is either empty or not empty , depending on the contents of the active cell:...

About This Shortcut If the active cell is empty, Excel will extend the selection to the first non-empty cell below the active cell. If the active cell is non-empty, Excel will extend the selection on the last non-empty cell below the active cell. On a Mac, the command key (⌘) can be used instead of the control key. About This Shortcut The distance moved up depends on the size of the worksheet window....

Explanation The gist: this formula “floods” the space between words in a text string with a large number of spaces, finds and extracts the substring of interest, and uses the TRIM function to clean up the mess. Working from the inside out, the original text in B5 is flooded with spaces using SUBSTITUTE: SUBSTITUTE(B5," ",REPT(" ",99)) This replaces each single space with 99 spaces. Note: 99 is just an arbitrary number that represents the longest word you need to extract....

Explanation The goal is to find the longest text string in the range B5:B16. At the core, this is a lookup problem that requires creating a value (the string length) that does not exist in the data as part of the formula. The easiest way to solve this problem is with the XLOOKUP function or the FILTER function . However in older versions of Excel without the XLOOKUP function, you can use an INDEX and MATCH formula ....

Explanation In this example, the goal is to find the longest text string in the range C5:C16 that belongs to the group entered in cell F5. The group is a variable that may be changed at any time. At the core, this is a lookup problem, and the challenge is that the value we need to look up (the string length) does not exist in the data. We need to create this as part of the formula....

Purpose Return value Syntax =FORECAST.ETS.CONFINT(target_date,values,timeline,[confidence_level],[seasonality],[data_completion],[aggregation]) target_date - The time or period for the prediction (x value). values - Existing or historical values (y values). timeline - Numeric timeline values (x values). confidence_level - [optional] A number between 0 and 1 (exclusive). Default = 0.95. seasonality - [optional] Seasonality calculation (0 = no seasonality, 1 = automatic, n = season length in timeline units). data_completion - [optional] Missing data treatment (0 = treat as zero, 1 = average)....

Explanation The formula first uses the ISNUMBER function to test if the value is a number, and applies a simple logical if so: =IF(ISNUMBER(B4) For any number less than the value in “input”, the formula will return TRUE and the conditional formatting will be applied. However, if the value is not a number, the formula then checks if the first character is a less than symbol (<) using the LEFT function:...

Transcript In this video, we’ll look at how to filter larger sets of data in a table with filters applied. When plotting unsummarized data, the chart filter may not be convenient. For example, here we have historical stock data for Microsoft covering more than 15 years in more than 100 rows of data. If I insert a line chart, Excel automatically builds a condensed horizontal axis and plots all the data....

Explanation With the introduction of Dynamic Arrays in Excel formulas , there is more emphasis on arrays . The #CALC! error occurs when a formula runs into a calculation error with an array. The #CALC! error is a “new” error in Excel, introduced with dynamic arrays. It will not appear in older versions of Excel. Empty array An empty array can trigger a #CALC! error, and this is the most common reason you may see a #CALC!...

Transcript One of the best uses of conditional formatting is to quickly highlight top or bottom values in a set of data. Scanning for high and low values in a large set of data is tedious and error-prone. With conditional formatting, you can highlight these values in an instant. Let’s take a look. Here we have a table that contains 12 months of sales data for a team of salespeople. Let’s use Conditional Formatting to quickly highlight the highest and lowest figures....

Transcript In this video we’ll look at the tools that Excel provides for managing named ranges. After you’ve created a named range , you may want to modify its name, or change the cells it references. You can easily do this by using a feature called the “Name Manager.” You can find the Name Manager on the Formulas tab of the ribbon. Just click to open. You can also open the Name Manager using the keyboard shortcut Control + F3....