Posts

Explanation In this example, the goal is to count the people shown in the table by both Department (Dept) and Group as shown in the worksheet. A simple way to do this is with the COUNTIFS function. COUNTIFS function The COUNTIFS function is designed to count things based on more than one condition. Conditions are supplied to COUNTIFS in range/criteria “pairs” like this: =COUNTIFS(range1,criteria1,range2,criteria2) In this problem, the first step is to create the shell of the summary table that contains one set of criteria in the left-most column, and the second set of criteria in the top row as column headers like this:...

Transcript In this video, we’ll take a closer look at data series. When you create a chart in Excel, you’re plotting numeric data organized into one or more “data series”. A data series is just a fancy name for a collection of related numbers in the same row, or the same column. For example, this data shows yearly sales of shorts, sandals, t-shirts, and hoodies for an online surf shop....

About This Shortcut Unhide any hidden columns that intersect the current selection. Notes: (1) May not work in Excel 2010 on Vista or Windows 7. Discussion and fix is here: http://superuser.com/questions/183197/whats-the-keyboard-shortcut-to-unhide-a-column-in-excel-2010 (2) May not work in Excel 2007. See http://www.techrepublic.com/blog/microsoft-office/office-solution-keyboard-shortcuts-for-hiding-and-unhiding-columns-and-rows/ (3) In Windows 10, there is a conflict with a keyboard-switching shortcut. You can resolve this by unassigning the keyboard layout hot key. You may need to unassign the input language as well....

Purpose Return value Syntax =VARPA(number1,[number2],...) number1 - First number or reference. number2 - [optional] Second number or reference. Using the VARPA function The VARPA function calculates the variance for data that represents an entire population. Variance provides a general idea of the spread of data. The difference between the VARPA function and the VARP function is in how these functions handle logical values and numbers as text. The VARP function will ignore text values and logicals when the appear in references , while the VARPA function will evaluate text as zero, TRUE as 1, and FALSE as zero....

Explanation In this example, VLOOKUP is configured to look up 1000 invoice numbers in a table that contains 1 million invoices. The catch is that not all of the 1000 invoice numbers exist in the source data. In fact, most of the invoice numbers do not appear in column B . This means we need to configure VLOOKUP to use an exact match , and exact match lookups on large data sets can be painfully slow....

Explanation Note: a simpler approach would be to alter the table used by VLOOKUP directly. But this example explains the mechanics of testing and overriding output from VLOOKUP. This formula is based on a simple grading example explained in detail here . For a given score, VLOOKUP uses a existing table, the named range key (B5:C9), to calculate a grade. Note match mode is set to approximate. To override output, VLOOKUP is nested in an IF statement:...

About This Shortcut This shortcut will apply the Scientific number format with two decimal places. Excel offers many types of number formatting . About This Shortcut This shortcut applies the Date format “dd-mmm-yy” to the selection. You can also use the shortcut Control + 1 to open the Format cells dialog box, which allows you to choose other date formats and to apply a custom date format . Once you have applied a date format, you can use Paste Special > Formats to apply the format to other cells....

Explanation In this example, the goal is to calculate an average of the quiz scores in columns C, D, E, and F for each person. However, the result needs to ignore any zeros that appear in the data. This formula can be easily solved with the AVERAGEIF function or the AVERAGEIFS function . It can also be solved with a combination of FILTER and AVERAGE . See below for details....

Purpose Return value Syntax =BITOR(number1,number2) number1 - A positive decimal number. number2 - A positive decimal number. Using the BITOR function The input numbers must be greater than or equal to zero and no larger than 2^48 - 1. Purpose Return value Syntax =BITRSHIFT(number,shift_amount) number - The number to be bit shifted. shift_amount - The amount of bits to shift to the right, if negative shifts bits to the left instead....

Explanation For this example, we want to calculate the interest paid during each year in a 5-year loan of $30,000 with an interest rate of 5%. To do this, we set up CUMIPMT like this: rate - The interest rate per period. We divide 5% by 12 because 5% represents annual interest. nper - the total number of payment periods for the loan, 60. pv - The present value, or total value of all payments now, 30000....

Explanation In this example, the goal is to count codes that appear as substrings in a case-sensitive way. The functions COUNTIF and COUNTIFS are both good options for counting text values, but these functions are not case-sensitive, so they can’t be used to solve this problem. The solution is to use the FIND function together with the ISNUMBER function to check for substrings and the SUMPRODUCT function to add up the results....

Explanation In this example, the goal is to count the number of cells in a range that do not contain errors. The best way to solve this problem is to use the SUMPRODUCT function together with the ISERROR function. You can also use the COUNTIF function or COUNTIFS function to exclude specific errors. Both approaches are explained below. COUNTIF function One way to count cells that do not contain errors is to use the COUNTIF function like this:...

Explanation In this example, the goal is to count cells in the range B5:B16 that end with specific text, which is provided in column D. For convenience, the range B5:B16 is named data . COUNTIF function The simplest way to solve this problem is with the COUNTIF function and a wildcard. COUNTIF supports three wildcards that can be used in the criteria argument : question mark (?), asterisk(), or tilde (~)....

Explanation In this example, the goal is to compare two columns and return the count of matches in corresponding rows. A good way to solve this problem is to use the SUMPRODUCT function or the SUM function, as explained below. SUMPRODUCT function The SUMPRODUCT function is a versatile function that handles array operations natively without any special array syntax. Its behavior is simple: it multiplies, then sums the product of arrays....

Explanation In this case, Excel extracts the month from the date in cell B6 as a number, and the month in the cell C6 as a number, then tests for equivalency using the equal sign. Both dates are in January, so the formula is solved as follows and returns TRUE. =MONTH(B6)=MONTH(C6) =1=1 =TRUE Same month as today If you need to test a date to see if has the same month as the current date (today), you can use this formula:...

About This Shortcut This shortcut will delete the contents of selected cells, leaving formatting intact. On the Mac, Control-B also deletes cell contents of one or more cells. About This Shortcut Hide columns that intersect the current selection,

You can download the worksheet I used in the training below. In this video, I run through 23 tips you can use to save time with Excel formulas today. We try hard to make our videos quick and to the point, so this is a rapid-fire list, with more than 20 tips in about 10 minutes. We trust that you can rewind the video as needed :) More formula videos in this series:...

Transcript In this video, we’ll take a look at the TEXTSPLIT function. TEXTSPLIT is an Excel function designed to split text into separate cells using a given delimiter. In this worksheet, we have a list of email addresses. The goal is to split each email into a separate name and domain. As I start to enter TEXTSPLIT, Excel will match the function and I can use Tab to autocomplete. Although TEXTSPLIT accepts six arguments, only the first two arguments, text and col_delimiter , are required....

About This Shortcut This shortcut extends the selection from the active cell to the cell immediately above. If cells in multiple rows are selected to start with, Excel will shrink the selection by moving the bottom row up. About This Shortcut This shortcut extends the selection from the active cell to the cell immediately below. If multiple rows are selected, Excel will extend the entire bottom edge of the current selection by one row....

About This Shortcut The distance moved up depends on the size of the worksheet window. About This Shortcut The distance moved down depends on the size of the worksheet window.