Explanation Note: Excel contains many built-in “presets” for highlighting values with conditional formatting, including a preset to highlight cells that equal a specific value. However, for more flexibility, you can use your own formula, as explained in this article. If you want to highlight cells that equal a specific value, you can use a simple formula that returns TRUE when the condition is met. For example, to highlight any cells in the range C5-C11 that contain the text “dog”, you can use conditional formatting with this formula:...

Transcript In this video, we’ll look at how to keep your chart up to date with the latest values, and how to add more data to your chart when needed. After you create a chart, you don’t have to worry about updating the chart when values change. As long as worksheet calculation is set to Automatic, Excel will automatically update the chart when values in the source data change. You can verify that Calculation is set to automatic on the Formulas tab of the ribbon....

Transcript By default, a new pivot table includes Grand Totals for both rows and columns. But you can enable and disable Grand Totals for rows and columns independently. Let’s take a look. When you create a new pivot table, you’ll see Grand Totals displayed below the table and to the right of the table. Column Grand Totals appear in the last row of the table, and row Grand Totals appear in the last column of the table....

Transcript In this video, we’ll look at how you can resize and copy charts. After you’ve created a chart, you can select the chart and resize manually using any one of the 8 drag handles. To constrain the chart to the cell grid as you drag, hold down the Alt key. You’ll then see the chart snap to the gridlines as you go. If you know what size chart you want exactly, you can double click the chart area, and enter a height and width manually in the Format Task Pane....

Transcript In this lesson we’ll show you how to create a basic chart in Excel. The charting system in Excel is flexible and deep. You can create charts with very little effort, and you have the power to customize almost every aspect of a chart. Let’s take a look. Here we have annual sales figures for a small company. We’ll use this data to build a basic column chart. The first step in creating a chart in Excel is to prepare the data....

Transcript The ability to group date fields automatically is one of the most powerful and useful features of pivot tables. With a few clicks, you can neatly roll up thousands of dates into various units of time, including years, quarters, months, weeks and days. Let’s take a look. This pivot table shows total sales by Product. Let’s add the Date field as a column label, and then group to show sales by year....

Transcript In this video, we’ll look at a few ways that you can see or visualize arrays in a formula. One of the best things about the new dynamic array formula engine in Excel is that it’s much easier to see and visualize arrays. Let’s take a look at a few examples. The first way to see arrays is to enter formulas or expressions directly on the worksheet. For example, here we have some numbers in the range B5:B14....

Transcript By default, when you add a label field to a pivot table, the items in that field are sorted in Alphabetical order. However, you can override this default behavior. Let’s take a look. When you add a label field to a pivot table, the items in that field are sorted alphabetically. For example, if we add Product as a row label to this pivot table. We get sales by Product, with all products listed alphabetically....

Transcript In this video we’ll look at how to trace a formula error. Here we have a simple sales summary for a team of salespeople over a period of 4 months. You can see that we have monthly totals in the bottom row and totals for each salesperson in the last column. Below the table, we have a sales target and calculations that are meant to calculate a bonus when the sales target is exceeded....

Explanation The goal is to take a specific action when a value begins with “x”, “y”, or “z”. As is often the case in Excel, there are multiple ways to approach this problem. The simplest way is to use the OR function with the LEFT function to create the required logical test. Another option is to use the COUNTIF function. Both approaches are explained below. Note: this formula is more advanced because we need to test for “cell begins with”....

Explanation In the image shown, the current date is August 24, 2019. Excel dates are serial numbers , so they can be manipulated with simple math operations. The TODAY function always returns the current date. Inside the AND function , the first logical test checks to see if the date in B5 is greater than or equal to the Monday two weeks previous. B5>=TODAY()-WEEKDAY(TODAY(),3)-14 This is based on a formula described here which gets the Monday of the current week....

Explanation In this example, the goal is to calculate a count for the longest winning streak in a set of data. In the worksheet shown, wins (“W”) and losses (“L”) are recorded in column C, so this means we want to count the longest consecutive series of W’s. Although we are specifically counting the longest winning streak in this example, the same approach can be used to count many other things, including:...

Explanation In this example, the goal is to look up and retrieve all names for a given team and return them in a single cell as a comma‑separated list. At the core, this is a lookup problem, but the twist is that we want to return multiple matches for each team, not just one. That means traditional lookup functions like VLOOKUP, XLOOKUP, etc., won’t work because they only return the first match ....

Explanation Working from the inside out, the MIN function is used to find the lowest bid in the range C5:C9: MIN(C5:C9) // returns 99500 The result, 99500, is fed into the MATCH function as the lookup value: MATCH(99500,C5:C9,0) // returns 4 Match then returns the location of this value in the range, 4, which goes into INDEX as the row number along with B5:B9 as the array: =INDEX(B5:B9, 4) // returns Cymbal The INDEX function then returns the value at that position: Cymbal....

About This Shortcut This shortcut will move the active cell left one cell when there are multiple cells selected. About This Shortcut This shortcut extends the selection from the active cell to the cell immediately to the right. If multiple cells in the same column are selected to start with, Excel will extend the entire right edge of the current selection. If cells in multiple columns are selected to start with, Excel will extend the selection by one column to the right, away from the active cell....

About This Shortcut Inside a cell, this shortcut will move the cursor by one word to the left each time the left arrow key is pressed. About This Shortcut Inside a cell, this shortcut will extend the selection by one word to the right each time the right arrow key is pressed.

Named ranges are one of these crusty old features in Excel that few users understand. New users may find them weird and scary, and even old hands may avoid them because they seem pointless and complex. But named ranges are actually a pretty cool feature. They can make formulas a lot easier to create, read, and maintain. And as a bonus, they make formulas easier to reuse (more portable). In fact, I use named ranges all the time when testing and prototyping formulas....

Explanation This formula depends on the CEILING function , which rounds numbers up to a given multiple. It works because of how dates work in Excel’s default 1900 date system, where the first day is the number 1, which is Sunday, January 1, 1900. In this scheme, the first Friday is day number 6, the second Friday is day number 13, and day 14 is the second Saturday. What this means is that all second Saturdays in the future are evenly divisible by 14....

Purpose Return value Syntax =NPV(rate,value1,[value2],...) rate - Discount rate over one period. value1 - First value(s) representing cash flows. value2 - [optional] Second value(s) representing cash flows. Using the NPV function NPV calculates the net present value (NPV) of an investment using a discount rate and a series of future cash flows. The discount rate is the rate for one period, assumed to be annual. NPV in Excel is a bit tricky, because of how the function is implemented....

Explanation This formula depends on two helper columns. The first helper column holds random values created with the RAND() function. The formula in C5, copied down is: =RAND() The RAND function generates a random value at each row. Note: RAND is a volatile function and will generate new values with each worksheet change. The second helper column holds the numbers used to sort data, generated with a formula. The formula in D5 is:...