Let’s take a look. Here we have some raw data for subscriptions to an email newsletter. Each row shows the date that a user subscribed. Let’s quickly build a pivot table that summarizes new subscriptions by month. For convenience, I’ll first convert the data into a table. This will speed things up later, when we start working with the source data. Now I’ll summarize the table with a pivot table....
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. For example, this sheet contains a table showing the busiest airports in the world....
One problem with tables is that you can’t use structured references directly to create data validation or conditional formatting rules. However, as a workaround, you can define named a named range that points to a table, and then use the named range for data validation or conditional formatting. To illustrate, here I have a list of projects representing a sales pipeline. The last column in the table uses data validation to provide a dropdown list of allowed values....
In the example shown, the formula in cell H6 is: {=MAX(IF(ISERROR(SEARCH(H5&"*",files)),0,ROW(files)-ROW(INDEX(files,1,1))+1))} where “files” is the named range C4:C11. Note: this is an array formula and must be entered with control + shift + enter. Context In this example, we have a number of file versions listed in a table with a date and user name. Note that file names are repeated, except for the code appended at the end to represent version (“CA”, “CB”, “CC”, “CD”, etc....
which sorts the text values in column B by string length, in descending order. In this example, we want to sort the values in B5:B15 by the number of characters each string contains. Working from inside out, we use the LEN function to get the length of each value: Because we give LEN an array with 11 values, we get an array with 11 lengths: Each number represents the character length of a value in B5:B11....
Original data Data with helper columns Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
where data is an Excel Table in the range B5:D16, and the quarters in column E are generated by another formula explained below. Background study What is an Excel Table (3 min. video) Introduction to structured references (3 min. video) Excel Tables (overview) Calculating quarters The first step in this problem is to generate a quarter for each date in column B. In the table shown, column D is a helper column with quarter numbers calculated with a separate formula....
The result is $21,875. This is the total of amounts in column C that occur in the last 30 days, based on a current date of December 30, 2022. Excel date logic To understand the logic of the formulas explained below, the first step is to understand Excel’s date system, where dates are serial numbers beginning on January 1, 1900. January 1, 1900 is 1, January 2, 1900 is 2, and so on....
What the heck is that, and what is it doing? The double negative (sometimes called the even more nerdy “double unary”) coerces TRUE or FALSE values to their numeric equivalents, 1 and 0. It’s used in formulas where numbers are needed for a particular math operation. That might sound pretty vague, so I’ll illustrate with the example above. Let’s say you have a list of words in a range, and you want to count how many contain more than 5 characters....
Note: this is an array formula that must be entered with Control + Shift + Enter across the entire range H5:I9, except in Excel 365. However, if a source cell is blank (empty) TRANSPOSE will output a zero. To fix that problem, this formula contains an IF function that checks first to see if a cell is blank or not. When a cell is blank, the IF function supplied an empty string ("") to transpose....
which returns the average pay per week, excluding weeks where no hours were logged. This is an array formula, but it is not necessary to enter with control + shift + enter because the SUMPRODUCT function can natively handle most array operations. Working from the inside out, we first calculate total pay for all weeks: This is an array operation that multiplies hours by rates to calculate weekly pay amounts....
where “sales” is the named range C5:C11. The core of this formula is the RANK function, which is used to generate a rank of sales values, where the highest number is ranked #1: Here, RANK uses the named range “sales” (C5:C11) for convenience. By default, RANK will assign 1 to the highest value, 2 to the second highest value, and so on. This works perfectly as long as numeric values are unique....
Next, break time is subtracted from work time to get “net work hours”. This formula uses the the MOD function to handle times that cross a day boundary (midnight). By using MOD with a divisor of 1, positive results are unchanged, but negative results (which occur when start time is greater than end time) are “flipped” to get a correct duration. For more details, see: How to calculate number of hours between two times...
Note: This example assumes that today is the issue date, so the next payment will occur in exactly six months. See note below on finding the value of a bond on any date. The value of an asset is the present value of its cash flows. In this example we use the PV function to calculate the present value of the 6 equal payments plus the $1000 repayment that occurs when the bond reaches maturity....
When a ticket is open, the result is the number of days since the ticket was opened using the “Opened” date in column C. When a ticket is closed, the result is the number of days between the “Opened” and “Closed” date in column D. Note: this example was authored on April 18, 2021, so the calculations seen in the screen above are relative to that date. The core operation of this formula is controlled by the IF function....
which returns a value of 1. Because each hour can be represented as 1/24, you can convert an Excel time into decimal hours by multiplying the value by 24. For example, with the time value 6:00 cell A1, you can visualize the conversion like this: Format result as number When you multiply a time value by 24, Excel may automatically format the result using a time format like h:mm, which will display the value incorrectly....
And yet, a huge amount of time is wasted every day by intelligent people trying to solve typical business problems with Excel formulas. It’s not their fault. Excel is complicated, and almost no one gets good training on formulas. Core Formula is designed to give you the formula training you should have had to begin with. What’s in the course? Core Formula covers a lot of ground. Using bite-sized videos in razor-sharp HD format, Core Formula teaches you how to use formulas for text, dates and times, VLOOKUP, INDEX & MATCH, summing and counting, nested IFs, dynamic ranges, troubleshooting, and a wide variety of techniques for referencing cells....
where dates is the named range B5:B15. In this case, we are giving YEAR and array of dates in the named range dates: Because dates contains 11 cells, we get back 11 results in an array like this: Each date is compared to the year in column D, which creates a new array of TRUE FALSE values: In this array, TRUE corresponds to dates in the year 2017, and FALSE corresponds to dates in different years....
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: Same month and year To test that a date is the same month and year is another date, you can use this clever formula proposed by reader Eric Kalin: Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....
