Posts

Explanation In this example, the goal is to count orders (rows) where the state is Texas (“TX”), the amount is greater than $100, and the month is March. In this case, we can’t use COUNTIFS , because COUNTIFS is a range-based function and it won’t accept a calculation for a range argument, which we need to determine the month. We could optionally add a helper column that uses the MONTH function to extract the month, then use COUNTIFS, but a better option is to use the SUMPRODUCT function with Boolean logic ....

Explanation In this example, the goal is to count orders where the color is “blue” and the quantity is greater than 15. All data is in the range B5:B15. There are two primary ways to solve this problem, one with the COUNTIFS function, the other with the SUMPRODUCT function. Both approaches are explained below. COUNTIFS function The COUNTIFS function returns the count of cells that meet one or more criteria. COUNTIFS can be used with criteria based on dates, numbers, text, and other conditions....

Data Types are a special kind of data in Excel with properties that can be accessed like fields. Example Data Types include Stocks, Geography, and Currency. A “linked” Data Type refers to a data type that is linked to an online data source. Linked data types retrieve data from an external source and can be refreshed to show the latest available information. Available Data Types You can find a list of supported data types on the Data tab of the ribbon....

This page provides examples of public Coronavirus data you can download to Excel with Power Query . Each example has a link, a screenshot to show what the data looks like in Excel after being imported, and an Excel workbook. The attached Excel workbooks include a working query, and each query returns data to an Excel Table . You can refresh the data by right-clicking in the table and selecting “Refresh”....

Purpose Return value Syntax =DROP(array,[rows],[col]) array - The source array or range. rows - [optional] Number of rows to drop. col - [optional] Number of columns to drop. Using the DROP function The DROP function returns a subset of a given array by “dropping” rows and columns. The number of rows and columns to remove is provided by separate rows and columns arguments. Rows and columns can be dropped from the start or end of the given array....

Explanation In this example, the goal is to use a single formula to extract high-value projects and list them in a simple table. We also want to remove unnecessary columns to create a clean, uncluttered view. The solutions explained below are based on a combination of several functions in Excel, including FILTER, SORT, TAKE, and CHOOSECOLS. This is a useful technique for creating a simple dashboard report with a dynamic set of data....

Purpose Return value Syntax =FILTER(array,include,[if_empty]) array - Range or array to filter. include - Boolean array, supplied as criteria. if_empty - [optional] Value to return when no results are returned. Using the FILTER function The FILTER function “filters” data based on one or more conditions, and extracts matching values. The conditions are provided as logical expressions that test the source data and return TRUE or FALSE. The result from FILTER is an array of matching values from the original data....

Explanation When given a single cell reference, the ROW function returns the row number for that reference. However, when given a range that contains multiple rows, the ROW function will return an array that contains all row numbers for the range. In the example shown the array looks like this: {5;6;7;8;9;10} If you want only the first row number, you can use the MIN function to extract just the first row number, which will be the lowest number in the array....

Explanation Most of the work in this formula is done by the TEXT function, which applies a custom number format for hours and minutes to a value created by subtracting the start date from the end date. TEXT(C5-B5,"h"" hrs ""m"" mins """) This is an example of embedding text into a custom number format, and this text must be surrounded by an extra pair of double quotes. Without the extra double quotes, the custom text format looks like this:...

Explanation The MINIFS function returns the smallest numeric value that meets supplied criteria, and the MAXIFS function returns the largest numeric value that meets supplied criteria. Like COUNTIFS and SUMIFS, these functions use range/criteria “pairs” to apply conditions. For both formulas, we need just one condition: the project name must equal the name in column G: data[Project],G5 // condition To get the earliest start date, we use: =MINIFS(data[Start],data[Project],G5) // earliest date Here, MINIFS returns the minimum value in the Start column where the project is equal to “Omega” (from cell G5)....

Explanation EDATE can get the “same date” in the future or past, based on the number of months supplied. When 12 is given for months, EDATE gets the same date next year. Same date in previous year To get the same date in a previous month, use -12: =EDATE(date,-12) // prior year Explanation The WEEKNUM function takes a date and returns a week number (1-54) that corresponds to the week of year....

Transcript In this lesson we’ll look at how to align text vertically in cells. Excel provides options to align text to the top, bottom, and middle of cells, as well as options to justify and distribute text vertically. Let’s take a look. Here we have a table with several rows of sample text. The top three rows contain a single line of text. The bottom three rows contain larger amounts of text....

Transcript In this lesson we’ll look at cell fills. Fills define the background color of cells. They are a key tool in building good-looking and easy to read worksheets. Let’s take a look. To apply a fill, start by selecting the cells you want to fill. Then, open the Format Cells dialog box and navigate to the Fill tab. There are two basic types of fills. The first is a solid background color....

Transcript Named ranges are one of the most useful features in Excel. They make your formulas much easier to read and understand; they automatically give you absolute references , and they reduce errors. Let’s take a look at a few ways to create named ranges. The simplest way to create a named range is to use the name box, which sits to the left of the formula bar. Simply select the cells you want to name, and type the name in the box....

Transcript Once you’ve created a calculated item in a pivot table, you can easily change it. Let’s take a look. Here we have the same pivot table we looked at earlier. Sales are summarized by Region, and we are using a calculated item, Eastern, to summarize the regions East and South. Before we make any changes, let’s add Product as a Row Label above the Region field. Now we have multiple instances of the calculated item Eastern....

Transcript Often, you’ll make some sort of change to a worksheet and need to undo it. Excel has a powerful system for tracking changes so that they can be reversed when needed. It’s called undo and redo. Let’s take a look. As you make changes to your worksheet, Excel tracks these changes in a way that they can be reversed if needed using the Undo command. For example, if you’ve made a list, and you decide you don’t want the last value you typed, you can just click the undo button, and Excel will step back in time before that change was made....

Transcript In this lesson, we’ll take a look at how to divide in Excel. To divide things in Excel, use the forward slash character “/” which appears together with the question mark on the keyboard. So, 10 divided by 2 is entered as 10 / 2. Let’s take a look. Like our previous examples, we have a simple worksheet that highlights several cell references. Following the instructions in the table, let’s build the formulas we need using division....

Transcript In this video we’ll look at how you can use the CHOOSE function . Let’s look at three examples. In this first example we have some items listed with a numeric color code. We want to bring these names into column D. Now, since I already have a table here, I could just use VLOOKUP and reference the table. I get the lookup_value from column C; the table is the range H5:I7, locked with F4; the column is 2, and I need to use FALSE to force an exact match....

Purpose Return value Syntax =LN(number) number - A number to take the natural logarithm of. Using the LN function The LN function returns the natural logarithm of a given number. The natural logarithm is equivalent to log base e of a number, where e is Euler’s number , a mathematical constant with the approximate value 2.71828182845904. The LN function is the inverse of the EXP function and is used to model exponential decay....

Purpose Return value Syntax =LOG(number,[base]) number - Number for which you want the logarithm. base - [optional] Base of the logarithm. Defaults to 10. Using the LOG function The LOG function returns the logarithm of a given number, using the provided base. The LOG function takes two arguments , number and base . The value provided for number should be a positive real number. The base argument represents the base of the logarithm....