1. Creating a table is fast You can create an Excel Table in less than 10 seconds. First, remove blank rows and make sure all columns have a unique name, then put the cursor anywhere in the data and use the keyboard shortcut Control + T. When you click OK, Excel will create the table. 2. Navigate directly to tables Like named ranges, tables will appear in the namebox dropdown menu....
Where “color” is the named range B5:B12 and “amount” is the named range C5:C12. The second test is more complex: Here, we filter amounts to make sure that only values associated with the color in E5 (blue) are retained. The filtering is done with the IF function like this: The resulting array looks like this: Notice the value from the amount column only survives if the color is “blue”. Other amounts are now FALSE....
Note: CF formulas are entered relative to the “active cell” in the selection, B5 in this case. Effectively, this causes the rule to ignore values in columns B, C, and E and only test values in column D. When the value in column D for in a given row is “Bob”, the rule will return TRUE for all cells in that row and formatting will be applied to the entire row....
Note: with conditional formatting, it’s important to enter the formula relative to the “active cell” in the selection, which is assumed to be A1 in this case. The key to this formula is the =0 at the end, which “flips” the logic of the formula. For each value in A1:A10, COUNTIF returns the number of times the value appears in C1:C10. As long as the value appears at least once in C1:C10, COUNTIF will return a non-zero number and the formula will return FALSE....
Let’s say your company has started a membership program of some kind and your boss just sent you a set of data. She’s given you a list of 1,000 people that have renewed a membership in the last year or so, and she’s looking for several things. First, she wants you to calculate an expiration date one year in the future, on the last day of the same month that membership was last renewed....
Here I have a set of sales data for chocolate products. I’ve also got another month’s worth of sales data in this second sheet. We’ll get to that in a minute. To start off, I’m going to create a pivot table that shows sales broken down by Product, with the top selling products on top. Now that we have the pivot table set up, we have a nice breakdown of sales by Product....
Here we have a simple set of generic shipping data. We have a date, quantity, and a field to indicate batch number. Let me insert a standard column chart, and let’s run through some options in adjusting the labels that appear in the horizontal category axis. The first thing you probably notice is that there are some gaps in the columns. This happens because Excel automatically sets the axis type to date, which makes sense since we have dates in the data....
Here again we have a music collection with some missing data. The twist, in this case, is that the Artist isn’t in the right column. Instead of column B, it appears in column C. You’ll sometimes see this kind of pattern when the data has been exported from another system. As before, we’ll use formulas to fill in the missing data. But this time, we need to take care of the artist rows as a first step....
where score (C5:C9) and rating (B5:B9) are named ranges. One workaround is to restructure the lookup table itself, and move the lookup column to the left of lookup value(s). That’s the approach taken in this example, which uses the CHOOSE function reverse rating and score like this: Normally, CHOOSE is used with a single index number as the first argument, and remaining arguments are the values to choose from. However, here we give choose an array constant for index number containing two numbers: {1,2}....
where data (B5:C29) is an Excel Table. This formula returns the next 7 upcoming birthdays in the data based on the current date, which is November 17, 2021 in the example shown. Note: This formula displays upcoming birthdays. To sort and display all birthdays, see this formula. The main challenge with a problem like this is to sort the list of birthdays into a rolling list of upcoming dates. This formula works in two parts: (1) sort all birthdays according to a rolling calendar and (2) return the first 7 birthdays from the sorted list....
Where sales (D5:D15), dates (B5:B15) and values (C5:C15) are named ranges. Note: we are assuming date in G4 is a “first of month” date. The second criteria checks if dates are less than or equal to the last of the month, calculated with the EOMONTH function: When both criteria return TRUE, the date is in the given month, and MAXIFS returns the max of value of dates that meet criteria....
which outputs a series of 12 dates, incremented by one month, beginning on May 1, 2019. SEQUENCE can generate results in rows, columns, or both. In this case, SEQUENCE is configured to output an array of numbers that is 12 rows by 1 column: The start is zero, and step value defaults to 1, so SEQUENCE outputs an array like this: This array is returned to as the months argument inside the EDATE function....
In the example shown, the formula used to highlight every 3 rows in the table is: Where 3 is n (the number of rows to group) and 4 is an offset to normalize the first row to 1, as explained below. In this case, the first row of data is in row 5, so we use an offset of 4: The result goes into the CEILING function, which rounds incoming values up to a given multiple of n....
The result is the sum of all numbers in row 5. As new data is added to the table, the formula will continue to return a correct total. Full row references Excel supports full row references like this: You can see how this works yourself by typing 1:1 or 3:3 into the name box (left of the formula bar) and hitting return. You will see Excel select the entire row....
In non-leap years however, DATE will return the date March 1 of the year, because there is no 29th day in February, and DATE simply rolls the date forward to the next month. Finally, the MONTH function simply extracts the month from the result provided by DATE, which is compared to 2 using the equal sign. If the month is 2, the formula returns TRUE. If not, the month must be 3 and the formula returns FALSE....
In the example, the formula in D6 is: This formula adds 7 workdays days to Tuesday, Dec 22. Three holidays are supplied and weekends are set using 11 for the weekend argument, which means “Sunday only”. The result is Wednesday, December 30, 2015. We have also supplied a list of 3 holidays that all fall in the date range being calculated, which means that the holidays and Sundays will be excluded....
The result is 60, since (90+90+0)/ 3 = 60. To filter out the zero from the calculated average, the formula in E6 uses the AVERAGEIF function like this: The criteria supplied is “<>0”, which means “not equal to zero”. Blank data values The AVERAGE, AVERAGEIF, and AVERAGEIFS function all automatically ignore blank cells (and cells that contain text values), so there is no need to provide criteria to filter out empty cells....
In the example shown, the formula in G5 is: The AVERAGEIFS function is designed to average ranges based on multiple criteria. In this case, we configure AVERAGEIFS to average durations by month using two criteria: (1) matching dates greater than or equal to the first day of the month, (2) matching dates less than or equal to the last day of the month. To bracket dates by month, we use a simple trick to make things easier: In column F, instead of typing month names (“Jan”, “Feb”, Mar", etc....
where things is the named range E5:E7. This formula is based on another formula that checks a cell for a single substring. If the cell contains the substring, the formula returns TRUE. If not, the formula returns FALSE: When the SEARCH function finds a string, it returns the position of that string as a number. If SEARCH doesn’t find a string, it returns a #VALUE! error. This means ISNUMBER will return TRUE if there is a match and FALSE if not....
where data (B5:D15) and n (F5) are named ranges. This rule highlights the last n rows in the data. When n is changed, the highlighting is automatically updated. The formula uses the greater than operator (>) to check row in the data. On the left, the formula calculates a “current row”, normalized to begin at the number 1: On the right, the formula generates a threshold number: When the current row is greater than the threshold, the formula returns TRUE, triggering the conditional formatting....
