Transcript In this video, we’ll cover shortcuts you can use to insert or delete rows, columns, or cells. To start off, if you first select an entire row or column, you can use a single shortcut to insert new rows or columns. You can select an entire row with shift and the spacebar. Then, to insert a row, use Control shift + in Windows, Control + I on a Mac....
Transcript In this video, we’ll look at shortcuts you can use the navigate through one or more workbooks. In a workbook that contains multiple worksheets, you can go to the next worksheet using Control + PgDn on Windows, Fn + Control + down arrow on a Mac. To move to the previous worksheet, use Control + Page up on Windows, Fn + Control + up arrow on a Mac. On a Mac, you can also use Option plus right and left arrow keys to move through worksheets....
Explanation A while back, I got from a reader about investing for his grandkids. The email asks the following question: I hope you can assist me with an Excel calculation. I am keen to show my grandchildren the power of compound interest and the value of investing (patiently) for the long term. And I want to graph this table of annual returns to demonstrate the effect of compounding. I have started an investment program and have invested $100 per quarter for each grandchild for several years....
Purpose Return value Syntax =STDEVA(number1,[number2],...) number1 - First number or reference in the sample. number2 - [optional] Second number or reference. Using the STDEVA function The STDEVA function calculates the standard deviation for a sample set of data, evaluating text and logicals as numbers as part of the calculation. TEXT is evaluated as zero, TRUE is evaluated as 1, and FALSE is evaluated as zero. Standard deviation is a measure of how much variance there is in a set of numbers compared to the average (mean) of the numbers....
Explanation In this example, the goal is to strip (i.e., remove) non-numeric characters from a text string with a formula. Until 2024, this was a tricky problem in Excel, partly because Excel did not support regex (Regular Expressions), and partly because there wasn’t a good way to convert a text string into an array of characters where they could be easily processed with other functions. However, with the introduction of Regular Expressions in Excel in late 2024, the problem became much simpler....
Purpose Return value Syntax =SUMSQ(number1,[number2],...) number1 - The first argument containing numeric values. number2 - [optional] The first argument containing numeric values. Using the SUMSQ function The SUMSQ function returns the sum of the squares of the numbers provided. SUMSQ takes multiple arguments in the form number1 , number2 , number3 , etc. up to 255 total. Arguments can be a hardcoded constant, a cell reference, or a range . All numbers in the arguments provided are squared then summed....
About This Shortcut This shortcut toggles strikethrough formatting on and off for the current selection. Strikethrough formatting is one of four basic formatting options for text, which also includes bold, italic, and underline formatting. You can see what each option looks like in the samples below, which also include the shortcut to apply the formatting: This example shows strikethrough formatting (Control + 5) This example shows bold formatting (Control + B) This example shows italic formatting (Control + I) This example shows underline formatting (Control + U) Applying Strikethrough formatting in Excel In general, the best way to apply Strikethrough formatting in Excel is to use the keyboard shortcut Control + 5 in Windows and Command + Shift + X on a Mac....
Quick Links Overview Why Pivot? Tips Examples Training Why Pivot Tables? This question comes up a lot when people are just getting started with pivot tables. You can try to explain how pivot tables are cool, how they make it easy to analyze data, how they’re really good at summarizing in different ways, how they’re interactive, blah, blah, blah… And, at this point, most people’s eyes glaze over because it’s really hard to see why you should care about pivot tables until you actually use one....
Explanation In this example, the goal is to use XLOOKUP to find the first “red” or “pink” record in the data as shown. All data is in an Excel Table named data in the range B5:E14. This means the formulas below use structured references . As a result, the formulas will automatically include new data added to the table. XLOOKUP function In the worksheet shown, the formula in cell G5 is:...
When you want to pull information from a table, the Excel VLOOKUP function is a great solution. The ability to dynamically lookup and retrieve information from a table is a game-changer for many users, and you’ll find VLOOKUP everywhere. And yet, although VLOOKUP is relatively easy to use, there is plenty that can go wrong. One reason is that VLOOKUP has a major design flaw — by default, it assumes you’re OK with an approximate match....
Explanation Ever needed to calculate someone’s retirement date? Or figure out when a 30-year mortgage will end? Or maybe you’re setting a contract expiration date? In each case, you need a way to add years to a date. Here’s something that might surprise you: most Excel users tackle this problem with a formula that’s 3x more complex than it needs to be. And worse, the solution can fail in leap years....
An array operation is an operation that touches or manipulates the values in an array directly. This can be a logical comparison, a math operation, concatenation , or even feeding an array into another function. In the example shown above, the following formulas appear, each representing a different type of array operation: =B5:B13>6 // logical comparison =B5:B13+1 // math operation =B5:B13&"x" // concatenation =INT(B5:B13) // another function Array formulas include one or more array operations....
About This Shortcut Pressing the escape key while editing a cell aborts the operation and leaves the cell in it’s original state. About This Shortcut When multiple cells are selected, this shortcut will leave only the active cell selected.
About This Shortcut This shortcut clears or resets the filter for a selected slicer. You must select the slicer first before using the shortcut. Note: if you want to reset all filters applied to an Excel Table at the same time, you can use a shortcut to toggle filters off then on again. About This Shortcut This shortcut toggles the visibility of the total row in an Excel Table. It’s equivalent to using the “Total Row” checkbox on the Table tab of the ribbon....
Explanation In this example, the goal is to count cells that contain a specific substring. This problem can be solved with the SUMPRODUCT function or the COUNTIF function. Both approaches are explained below. The SUMPRODUCT version can also perform a case-sensitive count. COUNTIF function The COUNTIF function counts cells in a range that meet supplied criteria. For example, to count the number of cells in a range that contain “apple” you can use COUNTIF like this:...
Explanation In this example, the goal is to count non-blank dates in column D by group. All data is an Excel Table named data in the range B5:D16. This problem can be solved with the COUNTIFS function , as explained below. COUNTIFS function The Excel COUNTIFS function returns the count of cells that meet one or more criteria. COUNTIFS accepts ranges and criteria in pairs. For example, to count cells in A1:A10 that are equal to “red”, you would use COUNTIFS like this:...
Explanation Working from the inside out, COUNTIF is configured to values in the range B5:B14, using all of these same values as criteria: COUNTIF(B5:B14,B5:B14) Because we provide 10 values for criteria, we get back an array with 10 results like this: {3;3;3;2;2;3;3;3;2;2} Each number represents a count – “Jim” appears 3 times, “Sue” appears 2 times, and so on. This array is configured as a divisor with 1 as the numerator....
Purpose Return value Syntax =DATEVALUE(date_text) date_text - A valid date in text format. Using the DATEVALUE function Sometimes, dates in Excel appear as text values that are not recognized as proper dates. The DATEVALUE function is meant to convert a date represented as a text string into a valid Excel date . Proper Excel dates are more useful than text dates since they can be formatted as a date, and directly manipulated with other formulas....
Transcript In this video we’ll take a look at a few reasons why you shouldn’t hard code values that may change into your formulas. Here we have a simple table for a driving trip across the United States. The route begins in San Francisco and ends in New York City. The route is divided into segments, with each segment starting and ending in a larger city. Let’s add formulas in columns E and F to calculate the estimated fuel usage and fuel cost....
Purpose Return value Syntax =DURATION(settlement,maturity,coupon,yld,freq,[basis]) settlement - Settlement date of the security. maturity - Maturity date of the security. coupon - The security’s annual coupon rate. yld - The security’s annual yield. freq - Number of coupon payments per year (annual = 1, semi-annual = 2, quarterly = 4). basis - [optional] Day count basis (see below, default =0). Using the DURATION function In finance, duration is a measure of the price sensitivity to changes in interest rates for an asset that pays interest on a periodic basis, like a bond....
