Purpose Return value Syntax =SHEET([value]) value - [optional] The value to check. Using the SHEET function The SHEET function returns the index number of a sheet in Excel. You can use the SHEET function to get a numeric index that represents the order of sheets in an Excel workbook, starting with 1 on the left and ending with N on the right, where N is the total number of sheets in the workbook....
Transcript In this video, we’ll look at a shortcut recipe based on a formula, that lets you easily pull in missing data from other cells. I have a spreadsheet that contains music data. The table contains columns for Genre, Artist, Album, Year, Song, and Time, but only the first value is filled in, much like an outline. I want to run this data through a pivot table to analyze the music in different ways, but because the data isn’t well structured… most cells are blank, so the counts in the pivot table summary are off....
Explanation At the core, this is a simple formula that simply divides the total amount by the number of months given: =amount/months The trick is to “cancel out” this amount in months where it doesn’t apply. To do this, we use this logical expression: AND(E4>=start,E4<(start+months)) Here we use the AND function to test each month in row 4 to see if it’s both greater than or equal to the given start month, and less than the end month, calculated by adding the start month to total months....
Explanation In this example, the goal is to use a formula to split a text string into an array of characters. For example, if the text string is “Apple”, the resulting array should be {“A”,“p”,“p”,“l”,“e”}. For a long time, this was quite a difficult problem that required a complicated array formula approach. When the SEQUENCE function was introduced, the problem became simpler since SEQUENCE could generate numbers that could be used with the MID function to extract characters one by one....
Explanation In this example, the goal is to sum the amounts in column C by quarter in column G. Column D is a helper column , and the formula to calculate quarters from the dates in column B is explained below. All data is in an Excel Table named data in the range B5:E16. This problem can be solved with the SUMIFS function and the helper column, or without a helper column using the SUMPRODUCT function ....
Explanation The goal in this example is to sum Prices in column C when the Items in column B contain an asterisk (). The challenge is that the asterisk () is reserved as a wildcard in functions like the SUMIFS function, so you can’t match a literal occurrence of this character without using a special syntax. Wildcards Excel functions like SUMIF and SUMIFS support the wildcard characters “?” (any one character) and “” (zero or more characters), which can be used in criteria....
Transcript Of all the many functions in Excel, the IF function is often the first function that new users turn to. It’s a very flexible function that you can use in all sorts of ways. Let’s take a look. To illustrate how IF works, let’s look first at a case where we need to assign a “pass” or “fail” to a group of students. We have five test scores in columns D through H, and an average in column I....
Purpose Return value Syntax =ACOT(number) number - The value of the cotangent of an angle. Using the ACOT function The Excel ACOT function returns the arc cotangent of a number. Given the input 1, the function returns 0.785398163 radians as the output. =ACOT(1) // Returns 0.785398163 radians Explanation The arc cotangent function is the inverse of the cotangent function, with a branch cut that maps to related angles. =ACOT(COT(a)) // returns the angle or a branch cut angle For example, the angle π/4 is mapped to π/4 with no branch cut....
About This Shortcut On Windows, this shortcut only works within the Format Cells dialog box, on the Borders tab. If you use this shortcut in Format Cells, you’ll see a left border added or removed from the border preview area. On the Mac, this shortcut works directly on the worksheet, and adds a left border to each cell in the selection. About This Shortcut On Windows, this shortcut only works within the Format Cells dialog box, on the Borders tab....
Explanation In this example, the goal is to calculate a running total in column D of the worksheet as shown. A running total, or cumulative sum, is a set of partial sums that changes as more data is collected. Each calculation represents the sum of values up to that point. In this example, each calculation takes into account another month of the year. There are several ways to approach this problem, as explained below....
Explanation This formula relies on the AND function to test two conditions at the same time: Count of words from named range inc is >0 Count of words from named range exc is =0 If both conditions are TRUE, the formula returns TRUE. If either condition is FALSE, the formula returns FALSE. The test for multiple words is done using the SEARCH function with help from COUNT. When SEARCH receives more than one item to look for, it returns an array of results, one per item....
Purpose Return value Syntax =COS(number) number - The angle in radians for which you want the cosine. Using the COS function The COS function returns the cosine of an angle provided in radians. In geometric terms, the cosine of an angle returns the ratio of a right triangle’s adjacent side over its hypotenuse. For example, the cosine of PI()/6 radians (30°) returns the ratio 0.866. =COS(PI()/6) // Returns 0.886 Using Degrees To supply an angle to COS in degrees, multiply the angle by PI()/180 or use the RADIANS function to convert to radians....
Explanation In this example, the goal is to count cells in the range B5:B15 that contain either “x” or “y”, where x and y are both text strings . When you count cells with “OR logic”, you need to be careful not to double count. For example, if you are counting cells that contain “blue” or “green”, you can’t just add together two COUNTIF functions, because you may double count cells that contain both “blue” and “green”....
About This Shortcut This shortcut will create a pivot chart on a new worksheet. Select a cell in the pivot table first. About This Shortcut This shortcut will launch the PivotTable Wizard dialog box. Note: In Mac 2016, the Pivot Table Wizard appears to be gone . If you find a workaround, let us know .
Dynamic Array Formulas are one of the biggest changes to Excel ever . They completely change the way you solve hard problems in Excel, with excellent new functions and an upgraded formula engine that can return multiple results at the same time. With Dynamic Arrays, you can extract unique values, sort and filter data, look up multiple values, split text values effortlessly, and build dynamic lists that instantly respond to new information....
The key benefit of Dynamic Arrays is the ability to work with multiple values at the same time in a formula. This is a big upgrade and a welcome change. Dynamic Arrays solve some really hard problems in Excel, and will fundamentally change the way worksheets are designed. Once you see how they work, you’ll never want to go back. Availability Dynamic arrays and the new functions below are only available Excel 365 and Excel 2021....
Explanation Note: This example assumes the start date will be provided as the first of the month. See below for a formula that will dynamically return the first day of the current month. With the layout of the grid as shown, the main problem is to calculate the date in the first cell in the calendar (B6). This is done with this formula: =start-CHOOSE(WEEKDAY(start),0,1,2,3,4,5,6) This formula figures out the Sunday before the first day of the month by using the CHOOSE function to “roll back” the right number of days to the previous Sunday....
Explanation In this example, we have dates in B5:B16 and sales in C5:C16. Both ranges are named ranges . The goal is to create a dynamic range between two specific dates: the start date in cell F5 and the end date in cell F6. We then use a formula in F8 to sum the dynamic range, and a formula in F9 to count the dynamic range. In the current version of Excel, this problem can be easily solved with the XLOOKUP function....
Quick Links Overview Validation Formulas Dependent Dropdown Lists Introduction Data validation is a feature in Excel used to control what a user can enter into a cell. For example, you could use data validation to make sure a value is a number between 1 and 6, make sure a date occurs in the next 30 days, or make sure a text entry is less than 25 characters. When a user enters an invalid value, data validation can display a message telling the user what is allowed, as shown below:...
The problem Have you ever noticed how many keyboard shortcuts Excel has? There are well over 200 shortcuts, many of which have been around for decades, evolving in subtle ways. And what if you need to use Excel on both a Mac and a PC? If you’re in this situation, you know how frustrating it is trying to keep track of your most productive shortcuts on two different platforms. The Solution Exceljet’s Excel Shortcut Book brings together Excel’s most important shortcuts in one easy to use PDF....
