Explanation The CELL function can provide a wide range of information about cell properties. One property is called “protect” and indicates whether a cell is unlocked or locked. All cells start out “locked” in a new Excel workbook, but this setting has no effect until a worksheet is protected. The CELL function returns either 1 or zero to indicate “on” or “off”. In this case we are comparing the result to zero, so when CELL returns 0, the expression returns TRUE and the conditional formatting is triggered....

Transcript In addition to calculated fields, which behave like new columns in the data source, you can add a calculated item to a pivot table. A calculated item is an alternative to adding new rows to the data source. Let’s take a look. Here we have a pivot table that is summarizing sales data by region. As you can see, there are four regions total: East, Midwest, South, and West....

Transcript In this video we’re going to build a formula that counts the number of words in a cell. This cell has eight words in it, and we’re going to build a formula step by step that gives us the number “8”. We’re going to use “helper” formulas to do that, and when we’re finished, we’ll take those helper formulas and we’ll combine those into a single all-in-one formula that we can use anywhere....

Transcript In this video we look at several ways to handle current dates and times. You may often want to enter a current date and time into a worksheet. A simple way to do this is to enter a time or date stamp. You can do this using keyboard shortcuts. Control semicolon enters the current date. Control-Shift semicolon enters the current time. If you want to add both the current date and time use the date shortcut, add a space, and then use the shortcut for time....

Transcript In this lesson, we’ll look at how to enter dates in Excel . To enter a date in Excel, you need to type the date in a format that Excel can recognize. When checking for a date, Excel will look for a month, a day, and a year in a variety of formats. If you don’t supply a year, Excel will use the current year. Let’s take a look....

Transcript When you add a field to a pivot table , the name of the field in the pivot table comes from the column name in the source data. However, you can manually change the name to almost anything you like. Let’s take a look. Here we have a blank pivot table. Let’s add the product field as a row label and the total sales field as a value. Notice that value fields are automatically renamed to show the type of calculation being used on the field....

Transcript The MATCH function finds the relative position of an item in a list. MATCH can find exact matches or approximate matches. In this video, we’ll look at how to use MATCH to find an exact match. The MATCH function takes three arguments: the lookup_value, which is the value you’re looking up, the lookup_array, which is the list to look in, and match_type, which specifies exact or approximate matching. In this example, we’ll use MATCH to find exact matches, so we’ll be using 0 for match_type....

About This Shortcut This shortcut will insert the current time as a fixed value; it will not change. Note: In Mac 2016, Control + Shift + : stopped working to insert a time. The current shortcut on a Mac is Command + ; About This Shortcut This shortcut copied data from the cells above to cells below, without having to copy and paste. To fill down into more than one cell at once, make a selection that includes multiple rows below....

About This Shortcut This shortcut will insert function arguments needed for a function after the function name has been typed. About This Shortcut This shortcut will enter an array formula . When you use this shortcut to enter an array formula, you will see that curly braces surround the formula like so {=formula}.

Explanation When constructing more advanced formulas, it’s often necessary to figure out the last location of data in a list. Depending on the data, this could be the last row with data, the last column with data, or the intersection of both. We want the last relative position inside a given range not the row number on the worksheet: This formula uses the MATCH function configured to find the position of the last non-empty cell in a range....

About This Shortcut Maximize or restores the selected workbook window. This shortcut acts as a toggle. About This Shortcut Use this keyboard shortcut to select additional adjacent worksheets. When you hold the shift key down, and click another worksheet tab, all worksheets between the active worksheet and clicked worksheet will be selected.

Explanation In this example, the goal is to get the maximum value for each group in the data as shown. The easiest way to solve this problem is with the MAXIFS function. However, there are actually several options. If you need more flexibility (i.e. you need to work with arrays and not ranges), you can use the MAX function with the FILTER function. To create a dynamic summary table with a single all-in-one formula, you can use the BYROW function....

Purpose Return value Syntax =MINUTE(serial_number) serial_number - A valid date or time. Using the MINUTE function The MINUTE function extracts the minute from a time as a number between 0-59. For example, given a time of “7:45”, MINUTE will return 45. The MINUTE function takes just one argument , serial_number , which must be a valid Excel date or a valid Excel time . Times can be supplied to the MINUTE function as text (e....

About This Shortcut Moves current selection one cell to the right of the active selection. About This Shortcut Moves current selection one cell to the left of the active selection.

About This Shortcut If the active cell is empty, Excel will stop on the first non-empty cell above the active cell. If the active cell is non-empty, Excel will stop on the last non-empty cell above the active cell. On a Mac, the control key (⌃) can be used instead of the command key (⌘). About This Shortcut If the active cell is empty, Excel will stop on the first non-empty cell below the active cell....

Explanation This formula relies on a technique called “nested IFs” to handle a series of options and results. With nested IFs, one IF function is nested inside another, a process that is explained in some detail here . The formula in this example is purposely more verbose than necessary to “show” all possible options and results in a way that is easier to understand and maintain. The trick is to structure the formula with line breaks to show each IF on a separate line along with the “true result” for that IF....

Purpose Return value Syntax =NOMINAL(effect_rate,npery) effect_rate - The effective annual interest rate. npery - Number of compounding periods per year. Using the NOMINAL function The Excel NOMINAL function calculates the nominal interest rate when given an effective annual interest rate and the number of compounding periods per year. Nominal interest rate is typically the stated rate on a financial product. Effective annual interest rate is the interest rate actually earned due to compounding....

Explanation In this example, the goal is to retrieve the lowest 3 scores in column D that appear in a given group, entered as a variable in cell F5. If the group is changed, the formulas should calculate new results. The core of the solution is the SMALL function, which can be used to retrieve the “nth” smallest value in a set of data. The challenge is that the SMALL function does not offer any direct way to apply criteria before calculating a result, so we need to create our own logic to apply criteria....

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 . About This Shortcut This shortcut inserts a new worksheet (tab) into the active workbook. The new sheet is inserted to the left of the currently selected sheet.

Quick Links Overview Why Pivot? Tips Examples Training Pivot tables are a reporting engine built into Excel. They are the single best tool in Excel for analyzing data without formulas . You can create a basic pivot table in about one minute, and begin interactively exploring your data. Below are more than 20 tips for getting the most from this flexible and powerful tool. 1. You can build a pivot table in about one minute Many people think building a pivot table is complicated and time-consuming, but it’s simply not true....