Purpose Return value Syntax =SHEETS([reference]) reference - [optional] A valid Excel reference. Using the SHEETS function The SHEETS function returns the total number of sheets in a given reference. SHEETS takes one argument, reference , which should be a cell reference, or a 3D reference . When no references are supplied SHEETS returns the total number of sheets in the workbook. The SHEETS function includes hidden sheets. Examples For example, in a workbook that contains 5 sheets, the following formula will return 5:...

Transcript In this video, we’re going to look at tips and techniques for formatting worksheets faster. This worksheet is a simple model that compares the cost of buying coffee at a coffee shop vs. making coffee at home. There’s very little formatting, so I’ll use a number of shortcuts to clean thing up. First, I’ll turn off gridlines to make borders easier to see. I want to change all text to a dark gray to match the chart....

Transcript In this video, we’ll cover shortcuts for working with the active cell. As I mentioned earlier, every worksheet has an “active cell”, which you can see displayed in the name box. When the active cell is off-screen, you can scroll it into view, without actually changing the active cell, using Control + backspace on Windows, and Command + Delete on a Mac. If you extend this selection in any direction, you’ll see that the active cell doesn’t change....

Explanation We have a list of 12 codes in Column B. Each code consists of a prefix (two letters), a color (variable), and a 4-digit number, all separated by hyphens (e.g., AX-Red-6387). The goal is to sort this list based on the color substring so that all codes with the same color are grouped together in the output in alphabetical order. The 2-letter prefix and 4-digit number should be ignored during sorting....

Explanation In this example, the goal is to calculate a total quantity for each color across the two ranges shown in the worksheet. The two ranges are “non-contiguous”, which means they are not connected or touching. Both ranges contain a list of colors in the first column and quantities in the second column. Although we have just two ranges in this example, we want an approach that will scale to handle more ranges....

Transcript In this video, we’ll introduce the UNIQUE function . One of the new functions that comes with the dynamic array version of Excel is UNIQUE. The UNIQUE function lets you extract unique values in a variety of ways. The UNIQUE function takes three arguments. The first argument, array is the source data you are working with. The second argument, by_col means by column. Use this when data should be compared across columns instead of rows....

Purpose Return value Syntax =TRUE() Using the TRUE function The TRUE function returns the Boolean value TRUE. In other words, the two formulas below based on the IF function are functionally equivalent: =IF(A1>65,TRUE()) =IF(A1>65,TRUE) Both formulas return TRUE if the value in A1 is greater than 65. The TRUE function is provided for compatibility with other spreadsheet applications and there is no need to use it if you are creating a spreadsheet in Excel....

Explanation In this example, the goal is to perform a two-way lookup, sometimes called a matrix lookup . This means we need to create a match on both rows and columns and return the value at the intersection of this two-way match The core of this formula is INDEX, which is simply retrieving a value from C6:G10 (the “data”) based on a row number and a column number. =INDEX(C6:G10,row,column) To get the row and column numbers, we use the MATCH function configured for an approximate match by setting the match_type argument to 1:...

Transcript In this video, we’ll look at how to use the FILTER function together with the UNIQUE function to limit results using logical criteria. There are many situations in which you may want to use logical criteria to filter or limit the values processed by the UNIQUE function. In this first worksheet, we have a list of values, some of which are duplicates. Notice the list also contains empty or blank cells....

Explanation In this example, the goal is to average the last n columns in a set of data, where n is a variable entered in cell K5 that can be changed at any time. Since more data may be added, a key requirement is to average amounts by position. For convenience, the values to average are in the named range data (C5:H16). In the latest version of Excel, the best way to solve this problem is with the TAKE function , a new dynamic array function in Excel....

Boolean algebra is a mathematical system that represents logical expressions and relationships using only two values: TRUE and FALSE. Boolean logic refers to the principles that support Boolean algebra, including logical operations like AND, OR, and NOT and rules that govern the manipulation of logical expressions in Boolean algebra. In the context of Excel, Boolean logic refers to a technique of building formulas to take advantage of the fact that TRUE can be represented by the number 1, and FALSE can be represented by the number 0....

Transcript In this video, we’ll look at the three special menus that appear whenever you select a chart in Excel. These menus have names, but you’ll see them as the plus icon, the paintbrush icon, and the filter icon. Whenever you select a chart in later versions of Excel, you’ll see three icons appear at the upper right edge: the plus icon, the paintbrush icon, and the filter icon. The plus icon is actually a fly-out menu called Chart Elements....

Explanation In this example, a conditional formatting rule highlights cells in the range D5:D14 when the value is greater than corresponding values in C5:C14. The formula used to create the rule is: =$D5>$C5 The rule is applied to the entire range D5:G14. The formula uses the greater than operator (>) to evaluate each cell in D5:D14 against the corresponding cell in C5:C14. When the formula returns TRUE, the rule is triggered and the highlighting is applied....

Explanation This example is based on the formula explained in detail here : =ROW()-INDEX(ROW(data),1,1)+1>ROWS(data)-n 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: =ROW()-INDEX(ROW(data),1,1)+1 // calculate current row On the right, the formula generates a threshold number: ROWS(data)-n // calculate threshold When the current row is greater than the threshold, the formula returns TRUE, triggering the conditional formatting....

Explanation Normally, you want to maintain numeric values in Excel, because they can be used in formulas that perform numeric calculations. However, there are situations where converting numbers to text makes sense. One example is when you want to concatenate (join) a formatted number to text. For example, “Sales last year increased by over 15%”, where the number .15 has been formatted with a percent symbol. Without the TEXT function, the number formatting will be stripped....

Explanation This formula works for times entered in a particular format as shown below: 00h01m13s 00h01m08s 08h02m59s Note the text string is always 9 characters long, and each component is 2 digits. The core of this formula is the TIME function, which assembles a valid time using individual hour, minute, and second components. Since these values are all together in a single text string, the MID function is used to extract each component:...

You’ve heard of data visualization, right? It’s the art and science of presenting data in a way so that people can “see” important information at a glance . Data visualization makes complex data more accessible and useful. In a world overflowing with data, it’s more valuable than ever. Excel has a great tool for visualizing data called Conditional Formatting. If you work with data in Excel (and who doesn’t these days?...

Explanation In this example, the goal is to count the value in cell B5 (“Steven”) in the sheets listed in B11:B13. The workbook shown in the example has four worksheets in total. The first sheet is named “Master” and contains the search string, the range, and the sheets to include in the count, as seen in the screenshot above. The next three sheets, “Sheet1”, “Sheet2”, and “Sheet3” each contain 1000 random first names in the range B4:F203....

About This Shortcut Inside a cell, this shortcut delete text starting from the cursor to the end of the current line of text. About This Shortcut The delete key on a Mac deletes to the left.

Explanation The INDIRECT function tries to evaluate text as a worksheet reference. This makes it possible to build formulas that assemble a reference as text using concatenation , and use the resulting text as a valid reference. In this example, we have Sheet names in column B, so we join the sheet name to the cell reference A1 using concatenation: =INDIRECT(B6&"!A1") After concatenation, we have: =INDIRECT("Sheet1!A1") INDIRECT recognizes this as a valid reference to cell A1 in Sheet1, and returns the value in A1, 100....