About This Shortcut This shortcut will select the entire row of the current selection. If the current selection spans multiple rows, all rows that intersect the selection will be selected. About This Shortcut This shortcut will select the entire column of the current selection. If the current selection spans multiple columns, all columns that intersect the current selection will be selected.

Explanation The goal is to generate a series of dates one year apart. In the current version of Excel, the easiest way to do this is with the SEQUENCE function together with the DATE, YEAR, MONTH, and DAY functions. In older versions of Excel, you can use the same date functions and a more manual approach. Both methods are described below. SEQUENCE function The SEQUENCE function generates numeric arrays. For example, to generate the numbers 1 through 10 you can use SEQUENCE like this:...

Purpose Return value Syntax =STOCKHISTORY(stock,start_date,[end_date],[interval],[headers],[properties],...) stock - A ticker symbol in double quotes (“MSFT”, “AAPL”, “GOOG”, etc.). start_date - The start date for data to be retrieved. end_date - [optional] The end date for data to be retrieved. Default is start_date. interval - [optional] Time interval. Daily = 0, weekly = 1, monthly = 2. Default is 0. headers - [optional] No header = 0, basic header = 1, instrument + header = 2....

Transcript In this video, we’ll take a look at structured reference syntax inside a table. Inside a table, structured references work a lot like structured references outside a table, except the table name is not used when it’s implied. To illustrate, let’s look at some examples. The formula to calculate the Total in this table is just Quantity times Price. Notice when I point and click to enter the formula, Excel automatically builds the structured reference....

Explanation In this example, the goal is to sum hours per project across three different worksheets: Sheet1, Sheet2, and Sheet3. The data on each of the three sheets has the same structure as Sheet1, as seen below: 3D reference won’t work Before we look at a solution, let’s look at something that doesn’t work. You might wonder if we can provide the SUMIF function with a 3D reference like this:...

Explanation In this example, the goal is to calculate a sum for any given group (“A”, “B”, or “C”) across all three months of data in the range C5:E16. In other words, we want to perform a “sum if” with a data range that contains three columns. For convenience only, data (C5:E16) and group (B5:B16) are named ranges . The article below covers the following topics: Why SUMIFS won’t work FILTER solution SUMPRODUCT solution More advanced criteria Clever all-in-one-formula In Excel 2021 or later, the FILTER solution is easy and intuitive....

Purpose Return value Syntax =TRANSPOSE(array) array - The array or range of cells to transpose. Using the TRANSPOSE function The TRANSPOSE function converts a vertical range of cells to a horizontal range of cells or a horizontal range of cells to a vertical range of cells. In other words, TRANSPOSE “flips” the orientation of a given range or array: When given a vertical range, TRANSPOSE converts it to a horizontal range When given a horizontal range, TRANSPOSE converts it to a vertical range When a range or array is transposed, the first row becomes the first column of the new array, the second row becomes the second column of the new array, the third row becomes the third column of the new array, and so on....

The Treemap chart is a built-in chart type in Excel 2016+. A treemap chart displays hierarchical data in rectangles proportionally sized according to the amount of data in each category. Treemap charts are lacking many of the controls available in other chart types, but they are an interesting way to quickly visualize certain kinds of data. Pros Quick visualization of hierarchical data At-a-glance breakdown data in categories Cons Data must be sorted by category Missing many of the options available in other chart types The sunburst chart is a built-in chart type in Excel 2016+....

Explanation This example uses the UNIQUE function, which is fully automatic. When UNIQUE is provided with the range B5:B16, which contains 12 values, it returns the 7 unique values seen in D5:D11. UNIQUE is a dynamic function. If any data in B5:B16 changes, the output from UNIQUE will update immediately. Dynamic source range UNIQUE won’t automatically adjust the source range if data is added or deleted. To feed UNIQUE a dynamic range that will automatically resize as needed, you can use an Excel Table , or create a dynamic named range with a formula....

Explanation In geometry, a sphere is defined as a set of points that are all the same distance (r) from a given point in a three-dimensional space. The formula for calculating the volume of a sphere is: Where r represents radius, and the Greek letter π (“pi”) represents the ratio of the circumference of a circle to its diameter. In Excel, π is represented in a formula with the PI function , which returns the number 3....

Transcript What is a pivot table? A pivot table is a special Excel tool that allows you to summarize and explore data interactively. It’s a lot harder to explain a pivot table than to show you how one works, so let’s take a look. Here we have a worksheet that contains a large set of sales data for a business that sells specialty chocolate to retailers. This data contains columns for date, customer, city, state, region, product, category, quantity, total sales....

Transcript When you start using VLOOKUP , you’ll often run into situations where VLOOKUP can’t find a match and displays an error. In this video we’ll look how to avoid and handle that situation. Here we have a VLOOKUP example we’ve looked at previously which uses VLOOKUP to retrieve employee information based on an ID. When you’re using VLOOKUP this way you’ll probably run into situations where VLOOKUP can’t find the value you’re trying to look up....

About This Shortcut Apply the General number format. The General number format is the default format in Excel. It will display numbers as numbers, and text as text. Excel offers many types of number formatting . About This Shortcut This shortcut will apply the Currency format with two decimal places. Excel offers many types of number formatting .

The term “BigNum”, which stands for “Big Number”, is used to represent the largest allowed positive number in Excel, which is 9.99999999999999E+307 BigNum is used in certain lookup formulas constructed in a way to find the largest value that is less than or equal to a search value. Because BigNum is an improbably large value, the lookup will find the previous numeric value. For example, you can use the following formula to find the position of the last numeric value in a column:...

Purpose Return value Syntax =BIN2DEC(number) number - The binary number you want to convert to decimal. Using the BIN2DEC function The first bit of the binary number indicates whether the number is positive or negative. The other nine digits represent the size of the number, the max of which is 511 (2^9 - 1). A negative binary number is expected to use the two’s complement notation. Purpose Return value Syntax =BIN2HEX(number,[places]) number - The binary number you want to convert to hexadecimal....

Explanation In this example, the goal is to test the passwords in column B to see if they contain a number. This is a surprisingly tricky problem because Excel doesn’t have a function that will let you test for a number inside a text string directly. Note this is different from checking if a cell value is a number . You can easily perform that test with the ISNUMBER function ....

About This Shortcut This shortcut checks spelling on the current worksheet. The Spelling dialog box will be displayed if a problem is found. About This Shortcut This shortcut displays the Research window.

About This Shortcut This shortcut will close Excel. On a Mac, you can use Command + Q to quit almost any application. On Windows, you can use Alt + F4 for Excel. Also, you can press ALT + F followed by the X key. About This Shortcut This shortcut alternately expands and collapses the ribbon. Another useful option is to double click on any tab of the ribbon. This will also expand and collapse the ribbon....

Explanation The goal is to combine data from different worksheets with a formula. Note that we are not restructuring the data in any way, we are simply combining data in different worksheets that already have the same structure. At a high level, the formula we are using combines data from multiple sheets with the VSTACK function and removes empty rows with FILTER. The data in the workbook comes from 3 separate worksheets (Sheet1, Sheet2, and Sheet3) and is combined in another worksheet named Summary....

Explanation In this example, the goal is to convert the numbers in column B to either 1 or 0, depending on whether the number is greater than zero or not. If the number in column B is greater than zero, the result should be 1. If the number in column B is less than or equal to zero, the result should be zero. With the IF function One way to handle this problem is with the IF function ....