Note: the Scroll Lock status message may or may not be visible on the left side of the status bar, since it can be disabled. See How to disable Scroll Lock for for a full run-down on how to turn off scroll lock if you don’t have an extended keyboard. 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....

The T function takes one argument, value, which can be a cell reference, a formula result, or a hardcoded value. Examples The T function returns text when given a text value and an empty string ("") for numbers, dates, and the logical values TRUE and FALSE. For example: In most cases, the T function is unnecessary, because Excel automatically converts values when needed. The T function is provided for compatibility with other spreadsheet programs....

Volatile function list The following functions are considered volatile: Author 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.

In the example shown, generate a value of 60 every 3 months. The formula in B8 is: Which returns 60 every 3rd month and zero for other months. In this case, the number is created with the COLUMN function, which return the column number of cell B8, the number 2, minus 1, which is supplied as an “offset”. We use an offset, because we want to make sure we start counting at 1, regardless of the actual column number....

In the example, the date is March 9, 2016, which is the serial number 42,438. So: This means there are 13,033 days before January 1, 2050, when counting from March 9, 2016. Without TODAY Note: you don’t need to use the TODAY function. In the second example, the formula in D6 is: Concatenating with text In the third example, the same basic formula is used along with concatenation operator (&) to embed the calculated days in a simple text message: Since there are 15 days between December 10, 2014 and December 25, 2014, the result is this message: Just 15 days left!...

In this formula, the SEARCH function is used to search cells in column B like this: When SEARCH finds a match, it returns the position of the match in the cell being searched. When search can’t find a match, it returns the #VALUE error. Because we are giving SEARH more than one thing to look for, it will return more than one result. In the example shown, SEARCH returns an array of results like this: This array is then used as a divisor for the number 1....

You won’t find an import style command in Excel, or any other direct method for moving a custom style from one workbook to another. However, there are a couple easy workarounds. One option is to copy an entire worksheet into another workbook. In this workbook, I have a custom style already defined, as you can see in the styles menu. In a second open workbook, there are no custom table styles....

Let’s take a look. This pivot table shows a breakdown of sales and orders by product. Let’s use manual grouping to organize these products into 2 custom groups. When you group items manually, hold down the Control-key and select each item that you want to include in the first group. With these cells selected, click “Group Selection” from the Options tab on the PivotTable Tools Ribbon. Excel will put the selected items into their own group and each remaining item into other groups....

Let’s take a look. To illustrate how Shrink to fit works, let’s look at a typical layout problem. Here we have a basic feature table that needs some clean up. Let’s start by applying a horizontal and vertical alignment of center for all cells. We don’t have a lot of long text in this table, so center alignment will work well. Now let’s increase the row height to get some more white space into the layout....

As you work in Excel, you’ll often spend a considerable amount of time formatting data to get it to look good. And although formatting tools in Excel are easy to use, applying formatting manually can quickly get tedious. One tool you can use to speed things up is the Format Painter. The Format Painter copies formatting from one group of cells to another. In this example, I have a table that has a variety of custom formats applied: the header has a color fill and a white font; the movie name is in italics; year and rank are centered; the movie length is formatted as a time; and all the data cells have a gray border....

where data is the named range B5:D10. If you want only the first column number, you can use the MIN function to extract just the first column number, which will be the lowest number in the array: Once we have the first column, we can add the total columns in the range and subtract 1 to get the last column number. Index version Instead of MIN, you can also use INDEX to get the last row number: This is possibly a bit faster for large ranges, since INDEX just supplies a single cell to COLUMN....

In the example shown, the formula in G7 is: Where “Sex” is a named range for C3:C15 and “Time” is the named range D3:D15. Note: this is an array formula and must be entered using Control + Shift + Enter. The problem in this case is that we don’t want SMALL to operate on every value in the range, just values that are either male or female (M or F)....

where data is the named range B5:B15. The result is 7, since the number in B11 (the seventh row in data) contains 99. This is an array formula and must be entered with Control + Shift + Enter, except in Excel 365. One solution is to convert the numeric values to text with the TEXT function like this: This is an array formula and must be entered with Control + Shift + Enter, except in Excel 365....

Fields In the pivot table shown, there are four fields in the source data: Date, Sales, Week, and Filter. Filter is a helper column with a formula flagging the last 4 weeks. Week is a helper column that returns the Monday for any given date (for convenience only). Sales is the value field, and Week has been added as a Row field to group by week: Formula The formula used in D5 (Week), copied down, is: This formula returns the Monday of week for any given date....

First, the keyboard shortcut for editing a cell is F2 on Windows, and Control + U on a Mac. With Excel’s default settings, this will put your cursor directly in the cell, ready to edit. You can also double-click a cell to edit. If you want to always edit cells in the formula bar, even when you use a keyboard shortcut to edit a cell, you’ll need to change a setting....

where data is an Excel Table in the range B5:C16. The result is the sum of the top 3 values in Group A (65,45,25) which is 135. Note: FILTER is a newer function not available in all versions of Excel. See below for an alternative formula that works in older versions. This problem can be solved with a formula based on the FILTER function, the LARGE function, and the SUM function....

where data (D6:H16), diameter (D5:H5), material (B6:B16), and hardness (C6:C16) are named ranges used for convenience only. Note: this is an array formula and must be entered with Control + Shift + Enter This can be done with a two-way INDEX and MATCH formula. One MATCH function works out the row number (material and hardness), and the other MATCH function finds the column number (diameter). The INDEX function returns the final result....

Here we have a simple cost calculator which looks up cost based on a material’s width and height. The match needs to be approximate. For example, if the width is 250, and the height is 325, the correct result is $1,800. If the width is 450, and the height stays at 325, the correct result is $3,600. We can build a formula that does this lookup using INDEX and MATCH....

So, today we’re going to tackle the question Why Pivot Tables? And this question comes up a lot when people first run into Pivot Tables, because they’re wondering…why should they care? And you’re trying to explain that pivot tables are really fast, that you can summarize data interactively in different ways…blah, blah blah And you can kind of see their eyes just glaze over because, until you’ve actually used a pivot table yourself, it’s really hard to understand why they would be useful to you....

This formula adds 7 workdays days to Tuesday, Dec 22. Three holidays are supplied using the named range “holidays” (B9:B11) and weekends are set using the special syntax “0000000” which means all days in a week are workdays. The result is Thu, December 31, 2015. The weekend argument is supplied as 7 characters that represent Monday-Sunday. Use one (1) to indicate weekend, and zero (0) to indicate a working day....