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.
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.
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.
The SORT function takes four arguments: array, sort_index, sort_order, and by_col. The first argument, array, is the range or array to be sorted. This is the only required argument. By default, the SORT function will sort values in ascending order using the first column in array. Use the optional arguments sort_index and sort_order to control which column to sort by, and the order to sort by (ascending or descending). Sort_index should be a number corresponding to the column (or row) used for sorting....
The TOCOL function takes three arguments: array, ignore, and scan_by_column. Array is the only required argument and represents the array or range to be transformed. The ignore argument controls what values TOCOL will optionally ignore. The options for ignore are as follows: The scan_by_column argument is a boolean value that controls how TOCOL reads values from the source array. By default, scan_by_column is FALSE and TOCOL will read values “by row” from left to right....
where data (B5:D15), color (C5:C15), and list (J5:J7) are named ranges. MATCH is configured to look for each color in C5:C15 inside the smaller range J5:J7. The MATCH function returns an array like this: Notice numbers correspond to the position of “found” colors (either “red”, “blue”, or “black”), and errors correspond to rows where a target color was not found. To force a result of TRUE or FALSE, this array goes into the ISNUMBER function, which returns: The array above is delivered to the FILTER function as the include argument, and FILTER returns only rows that correspond to a TRUE value....
The result in column D is the fiscal year for each date, based on a fiscal year start in July. On the left, the YEAR function first returns the year from the date in B5: To adjust this value to return the fiscal year for a July start, the following boolean expression is added to the year value: Here, the MONTH function returns the month from the date in B5 (1), and this result is compared to the start month in C5 (7)....
Where “names” is the named range C4:C11. Note: this is an array formula and must be entered with control + shift + enter. Working from the inside out, this part of the formula will generate a relative set of row numbers: The result of the above expression is an array of numbers like this: Notice we get 8 numbers, corresponding to the 8 rows in the table. See this page for details on how this part of the formula works....
In the example shown, the formula in cell B5 is: Same date in previous year To get the same date in a previous month, use -12: 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.
Note: it’s important that CF formulas be entered relative to the “active cell” in the selection, which is assumed to be B4 in this case. Once you save the rule, you’ll see only values that are integers (whole numbers) highlighted. We use this fact to construct a simple formula that tests the result of MOD. When the result is zero (i.e. when the number is an integer) the formula returns TRUE, triggering the conditional formatting....
Here we have some sample data and, in column B, we have text values with a number at the end. What we want to do is increment these numbers using the value in column C. Now if I try to do this directly, with a formula that adds C5 to B5, I’ll get an error because the value in B is text, and Excel won’t let you add numbers and text....
How can you make one chart look like another? You have several options. One option is to copy formatting, and use Paste Special. For example, let’s say we want the chart on the right to look like the chart on the left. First, select the chart on the left and copy. Next, navigate to the home tab of the ribbon and, under the Paste menu, select Paste Special. Then choose Formats....
In some cases, you may want to create a chart that shows more than one level of axis labels. This is easy to do as long as you structure the source data in a particular way. Here we have a list of the busiest US airports. We have columns for region, city, airport code, and activity, which represents total take offs and landings. Holding down the control key, it’s a simple matter to plot activity by city in a column or bar chart....
Let’s take a look. Here we have a blank pivot table. Let’s add the product field as a row label and the total sales field as a value. Notice that value fields are automatically renamed to show the type of calculation being used on the field. In this case, we get Sum of Total Sales. If we change the calculation to Count, the field is automatically renamed to Count of Total Sales....
Let’s take a look. Here we have a pivot table showing sales by Region. Let’s add a column chart. Notice our chart is using the general number format. That’s because we haven’t yet set a format in the pivot table. Let’s do that now. Notice that both the pivot table and the pivot chart pick up the currency format. Now since they take up a lot of space, let’s remove field buttons that appear in the chart....
Let’s take a look. Here we have a set of random times in column B. First, I’ll add a formula to column C to pick up the time values in B and format them in General format, so you can see the raw time value. Now let’s extract the components of each time using Excel’s HOUR, MINUTE, and SECOND functions. I’ll extract the hour value using a function called HOUR....
where “data” is the named range B5:D10 To get only the first row number, we use the MIN function like this: which returns the lowest number in the array, 5. Once we have the first row, we can just add the total rows in the range then subtract 1 to get a final result. We get total rows in the range with the ROWS function, and a final result is determined like this:...
which returns the lowest Monday tide in the data, -0.64 To retrieve the date of the lowest Monday tide, the formula in I7 is: Where the worksheet contains the following named ranges: date (B5:B124), day (C5:C124), time (D5:D124), pred (E5:E124), tide (F5:F124). Both are array formulas and must be entered with control + shift + enter. Data from tidesandcurrents.noaa.gov for Santa Cruz, California. working from the inside out, the first IF checks if the day is “Mon”, based on the value in I5: If the result is TRUE, we run another IF: In other words, if the day is “Mon”, we check if the tide is “L”....
The formula returns the formatted strings as seen in column D. To format a text string with 12 characters in the same way, you can use a formula like this: Working from the inside out, the SEQUENCE function is used to generate an array of 6 numbers used as the start_num argument in the MID function: These are returned directly to the MID function: With the text “112233445566” in B5, the MID function returns an array of 6 strings: This array is returned to the TEXTJOIN function as the text1 argument, and with the colon (:) as the delimiter from C5, we have: The TEXTJOIN function concatenates the 6 strings together using a colon, and returns a single string as a final result: 11:22:33:44:55:66 The formula in D6 works exactly the same, except it uses the hyphen in C6 to join the strings: 11-22-33-44-55-66...
Note: this is an array formula and must be entered with control + shift + enter, unless you are using Excel 365, where array formulas are native. Because we are comparing one cell value to values in four other cells, the result is an array with four TRUE or FALSE values. In row 5, all values are equal, so all values are TRUE: This array is returned directly to the AND function, which returns TRUE, since all values in the array are TRUE....
