About This Shortcut This shortcut enters cell edit mode with the cursor at the end of the last line of text in the cell. If you want the cursor to move to the formula bar , see below. Once you are editing a cell, you can use the F2 / Ctrl+U again to toggle through available edit modes (edit, enter, point). “Point” and “Enter” work similarly and allow you to point to a cell....
Explanation In this example, the goal is to calculate a monthly mortgage payment based on three inputs: The loan amount The annual interest rate The loan term in years The worksheet shown also takes into account the down payment, which is calculated using a simple formula in C8 (see below) and then subtracted from the cost in cell C4. The mortgage payment is then calculated based on the loan amount in cell C9....
About This Shortcut This shortcut will copy from left to right. For adjacent cells, select one or more cells directly to the right of the data you want to copy and use the shortcut. For non-contiguous cells, select both source and target cells, then use the shortcut. This shortcut is especially handy in filtered lists, since it works with visible cells only. About This Shortcut This shortcut copies the formula in the cell above to the active (current) current cell and leaves the cell in edit mode....
Explanation In this example, the goal is to extract the first 3 values or the last 3 values from the named range data (B5:B15). We also want to exclude any empty cells from our results. In the worksheet shown the formula in cell D5 is: =INDEX(FILTER(data,data<>""),SEQUENCE(3,1,1,1)) Working from the inside out, we use the SEQUENCE function to construct a row number value for INDEX like this: SEQUENCE(3,1,1,1) We are asking SEQUENCE for an array of 3 rows x 1 column, starting at 1, with a step value of 1....
Transcript In this video, we’ll look at how to add a slicer to a pivot chart. Slicers provide a powerful way to filter data interactively, and they are a key building block of many dashboards. To filter a chart with a slicer, you can add a slicer to either the chart or pivot table. Once you do that, the slicer will control both the pivot table and the pivot chart....
Transcript In this lesson, we’ll look at how to insert and delete columns in Excel. You may want to insert columns to make room for new information. You may want to delete columns to remove information you no longer need or want. No matter how many columns you add or delete, the number of columns in the worksheet never changes. When you insert columns, columns are pushed off the worksheet at the far right edge....
Transcript One of the most basic and useful features in Excel is the ability to concatenate values with text. CONCATENATE is just a fancy word for “join.” In this video we’ll look at a simple way to use concatenation with the TEXT formula to highlight assumptions in a break-even model. This model calculates the break-even point for a single product based on assumptions about price, variable costs, and fixed costs. In addition, it shows operating income at different sales volumes....
Transcript In this video, we’ll look at the options available in Table slicers. Table slicers have a variety of settings. To access these settings, select a slicer and click “Options” under the Slicer Tools menu in the ribbon. Options appear grouped in 5 main categories Name and settings 2. Slicer styles 3. Commands to arrange and align slicers 4. Controls for slicer buttons, and 5. Inputs to set slicer size The Slicer Tools ribbon will appear whenever you select a slicer....
Transcript In this lesson we’ll look at the Text format. It may seem strange to format numbers as text, but there are times when it makes sense. The Text format displays numbers exactly as they were entered. Let’s take a look. In column B of our table we have a set of numbers that are displayed as we like, with both leading and trailing zeros. As we’ve seen in an earlier lesson, if we try to enter these numbers in cells that are using the General format, the leading and trailing zeros will be stripped as the values are entered....
Transcript In this video, we’ll look at how to use the AVERAGEIF function to calculate an average from numbers that meet a single criteria. Here we have a list of 16 properties with prices and other information. Let’s calculate some averages based on the conditions listed in column K. The AVERAGEIF function calculates an average for cells that meet a single criteria. It takes three arguments: range, criteria, and average_range ....
Transcript In this video, I’m going to show you the basics of Boolean logic. Boolean logic is a great tool for simplifying formulas, especially those with many IF statements. So, to start off, what’s a Boolean? A Boolean is a data type with only two possible values, TRUE or FALSE. You’ll often see Boolean results or Boolean expressions in Excel. For example, if I enter the formula =B5>30 here, we’ll get the Boolean result of TRUE....
Explanation The goal is to list the working days between a start date and an end date. In the simplest form, this means we want to list dates that are Monday, Tuesday, Wednesday, Thursday, or Friday, but exclude dates that are Saturday or Sunday. In addition, we need an option to exclude a list of given holidays. This article describes two ways to approach this problem, both of which use the SEQUENCE function to “spin up” a full range of dates and the FILTER function to remove dates that are not working days....
Explanation This formula relies on a helper column, which is column E in the example shown. The formula in E5, copied down, is: =(COUNTIFS($B$5:B5,B5)=1)+0 This formula returns a 1 for new customers and a 0 for repeat customers, and is explained in detail here . Once this formula is in place, the COUNTIFS function can be used to count new customers in each month. The first range and criteria inside COUNTIFS counts 1’s in the “new” column:...
The “Percentage” number format is one of the built-in number formats in Excel. In mathematics, a percentage is a number expressed as a fraction of 100. The word percent literally means “per one-hundred”. For example, 65% is read as “Sixty-five percent” and is equivalent to 65/100 or 0.65. To apply the percentage number format to a number, first select the number(s), then use any of these methods: Use the keyboard shortcut Control + Shift + Enter Select “Percentage” from the dropdown on the home tab of the ribbon Click the % button in the Number section on the home tab of the ribbon Control + 1 > Number > Percentage Applying percentage format does not change the number, only the display of the number....
Purpose Return value Syntax =PIVOTBY(row_fields,col_fields,values,function,[field_headers],[row_total_depth],[row_sort_order],[col_total_depth],[col_sort_order],[filter_array],[relative_to]) row_fields - The values to use when grouping rows. col_fields - The values to use when grouping columns. values - The values to aggregate. function - The calculation to run when aggregating. field_headers - [optional] 0 = No, 1 = Yes, don’t show, 2 = No, generate, 3 = Yes, show. row_total_depth - [optional] Enable or disable row totals. 0 = None, 1 = Grand Totals, 2 = Grand and Subtotals, -1 = Grand Totals at top, -2 = Both at top....
This chart is an example of a 100% stacked column chart. The data shown in the chart represents projects over a three year period, categorized as hit goals, missed goals, and exceeded goals. I think this is a good example of how a 100% stacked column chart can work well to show trends over time, in this case highlighting the worrying trend of more projects with missed goals. The data used to plot the chart looks like this:...
Explanation The Excel RANDBETWEEN function returns a random integer between given numbers. In the example shown, the formula in B5 is: =RANDBETWEEN(1,100) This formula is then copied down from B5 to B11. The result is random numbers between 1-100. RANDBETWEEN is a volatile function that recalculates when a worksheet is opened or changed. This includes any edits to the worksheet, or simply opening the workbook. To prevent random numbers from being recalculated again, you can replace the formulas with the values last calculated:...
Purpose Return value Syntax =RANK.EQ(number,ref,[order]) number - The number to rank. ref - A range that contains the numbers to rank against. order - [optional] Rank ascending or descending. Default is zero. Using the RANK.EQ function The RANK.EQ function returns the rank of a numeric value compared to a list of other numeric values. RANK.EQ can rank values from largest to smallest (i.e., the rank of highest test scores) and smallest to largest (i....
Explanation In this example, the goal is to create a running count for a specific value that appears in column B. The value to count is entered in cell E5, which is the named range value . The core of the solution explained below is the COUNTIF function , with help from the IF function to suppress a count for other values. Basic count Normally, the COUNTIF function is given a range and criteria like this:...
About This Shortcut When you select a formula and use this shortcut, this shortcut will select all cells that are referred to directly or indirectly by the formula(s) in your selection. To remember and understand the word “precedent” in this case, think of “precede” or “before”— literally, cells that come before. About This Shortcut This shortcut will select all cells with formulas that depend on the cells in the current selection....
