Explanation Dates and times are just numbers in Excel, so you can use them in any normal math operation. However, by default, Excel will only display hours and minutes up to 24 hours. This means you might seem to “lose time” if you are adding up time that is more than 1 day. In this example, the goal is to sum total hours in cell H5 and calculate total hours per person in the range H8:H10....

About This Shortcut This shortcut will toggle check and uncheck the checkbox for fields listed in the pivot table field list. Use to add or remove a field from a pivot table. On Windows, you can use the spacebar to toggle items that appear in row and column label filters as well. About This Shortcut This shortcut will group selected pivot table items. If you want to group a field automatically by date or number, you only need to select one item in the field before using this shortcut....

Explanation In this example, the goal is to check for “strong” passwords. What makes a password strong depends on the rules it must follow. In this case, a strong password must meet the following six conditions: At least 8 and not more than 15 characters long Contains at least one uppercase (A-Z) letter Contains at least one lowercase (a-z) letter Contains at least one number (0-9) Contains at least one punctuation character Does not contain whitespace Traditionally, this has been a difficult problem in Excel because there is no simple way to implement the logic....

Purpose Return value Syntax =VALUETOTEXT(value,[format]) value - The value to convert to text. format - [optional] Output format. 0 = concise (default), and 1 = strict. Using the VALUETOTEXT function The VALUETOTEXT function converts a value to a text string . By default, text values pass through unaffected. However, in strict mode, text values are enclosed in double quotes (""). VALUETOTEXT will always remove number formatting applied to numeric values, regardless of format ....

Purpose Return value Syntax =VARP(number1,[number2],...) number1 - First number or reference. number2 - [optional] Second number or reference. Using the VARP function The VARP function calculates the variance of an entire population of data. Variance provides a general idea of the spread of data. In the example shown, the formula in F5 is: =VARP(C5:C10) VARP ignores text and logicals passed into as cell references. For example, VARP will ignore FALSE when it appears in a range like A1:A10....

Explanation In this example, the goal is to join together three text values separated by line breaks. In Excel, you can use the keyboard shortcut Alt + Enter to add a line break in a cell that contains text, but the same approach won’t work in a formula. The trick is to use the CHAR function with the ASCII code 10 like this: =CHAR(10) // line break CHAR (10) returns a hidden character that Excel uses as a line break....

Purpose Return value Syntax =ADDRESS(row_num,col_num,[abs_num],[a1],[sheet]) row_num - The row number to use in the cell address. col_num - The column number to use in the cell address. abs_num - [optional] The address type (i.e. absolute, relative). Defaults to absolute. a1 - [optional] The reference style, A1 vs R1C1. Defaults to A1 style. sheet - [optional] The name of the worksheet to use. Defaults to current sheet. Using the ADDRESS function The ADDRESS function returns the address for a cell based on a given row and column number....

Purpose Return value Syntax =BITAND(number1,number2) number1 - A positive decimal number. number2 - A positive decimal number. Using the BITAND function The input numbers must be greater than or equal to zero and no larger than 2^48 - 1. How It Works The function performs a logical AND operation on each corresponding bit in the binary representation of the two numbers. Purpose Return value Syntax =BITLSHIFT(number,shift_amount) number - The number to be bit shifted....

Explanation The hyperlink function allows you to create a working link with a formula. It takes two arguments: link_location and, optionally, friendly_name . Working from the inside out, VLOOKUP looks up and retrieves a link value from column 2 of the named range “link_table” (B5:C8). The lookup value comes from column E, and VLOOKUP is configured for exact match. The result is fed into HYPERLINK as link_location , and the text in column E is used for friendly_name ....

Explanation For this example, we want to calculate cumulative principal payments over the full term of a 5-year loan of $5,000 with an interest rate of 4.5%. To do this, we set up CUMPRINC like this: rate - The interest rate per period. We divide the value in C6 by 12 since 4.5% represents annual interest: =C6/12 nper - the total number of payment periods for the loan, 60, from cell C8....

Explanation Loans have four primary components: the amount, the interest rate, the number of periodic payments (the loan term) and a payment amount per period. One use of the NPER function is to calculate the number of periodic payments for loan. For this example, we want to calculate the number of payments for a $5000 loan, with a 4.5% interest rate, and fixed payments of $93.22. The NPER function is configured as follows:...

About This Shortcut Pressing escape while editing an a cell will abort the operation without changing anything. About This Shortcut Inside a cell, this shortcut will extend the selection by one character to the right each time the right arrow key is pressed.

Explanation To convert an expense in one time unit (i.e. daily, weekly, monthly, etc.) to other time units, you can use a two-way INDEX and MATCH formula. In the example shown, the formula in E5 (copied across and down) is : =$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0)) This formula uses a lookup table with named ranges as shown below: Named ranges: data (O5:S9), vunits (N5:N9), and hunits (O4:S4). Introduction The goal is to convert an expense in one time unit, to an equivalent expense in other time units....

About This Shortcut This shortcut will embed a pivot chart on the same worksheet as the pivot table. Select a cell in the pivot table first. No Mac equivalent. About This Shortcut This shortcut will create a pivot chart on a new worksheet. Select a cell in the pivot table first.

Data validation is a feature in Excel used to restrict what a user can enter into a cell. For example, you can use data validation to make sure a value is a number between 1 and 6, make sure a date occurs in the next 30 days, or make sure a color is from an approved list only. Data validation can also be used to provide a dropdown menu with predefined choices when a cell is selected....

Purpose Return value Syntax =DAYS(end_date,start_date) end_date - The end date. start_date - The start date. Using the DAYS function The DAYS function returns the number of days between two dates. Both dates must be valid Excel dates or text values that can be coerced to dates. The DAYS function only works with whole numbers, fractional time values that might be part of a date are ignored. If start and end dates are reversed, DAYS returns a negative number....

Explanation In this example, the goal is to create a formula that performs a dynamic two-way average of all age and gender combinations in the range B5:D16 . The solution shown requires four general steps: Create an Excel Table called data List unique age groups with UNIQUE function List unique genders with UNIQUE function Generate all averages in AVERAGEIFS function Create the Excel Table One of the key features of an Excel Table is its ability to dynamically resize when rows are added or removed....

Transcript In this video, we’ll take a look at the tools that Excel provides for charting. The number of tools Excel that provides for working with charts can be a little overwhelming. But the tools get easier with practice. Let’s take a quick tour to get you acquainted. First, the ribbon. To create a chart, you can click Recommended Charts on the Insert tab of the ribbon. Excel will show you recommended charts based on the data you’ve selected....

About This Shortcut This shortcut will extend the selection from the active cell to the next cell to the left that is either empty or not empty , depending on the contents of the active cell: If the active cell is empty , the cursor will stop at the next non-empty cell to the left. If the active cell is not empty , the cursor will stop at the next empty cell to the left....

Details The problem There is one master test (Test A), and three variants (Test B, Test C, and Test D). All 4 tests have the same 19 questions, but arranged in a different order. The first table in the screen below is a “question key” and shows how questions in Test A are ordered in the other 3 tests. The second table is an “answer key” that shows the correct answers for all 19 questions in all tests....