Explanation Excel handles dates and time using a scheme in which dates are serial numbers and times are fractional values . For example, June 1, 2000 12:00 PM is represented in Excel as the number 36678.5, where 36678 is the date portion and .5 is the time portion. If you have dates that include time, you can use the INT function to extract just the date part. The INT function returns the integer portion of a number that includes a decimal value....

Explanation The SMALL function retrieves the smallest values from data based on a given rank. For example: =SMALL(range,1) // smallest =SMALL(range,2) // 2nd smallest =SMALL(range,3) // 3rd smallest In the worksheet shown, the rank (which is provided to SMALL as the k argument) comes from numbers in column E. Retrieve associated values To retrieve the name of the company associated with the smallest bids, we can use an INDEX and MATCH formula ....

About This Shortcut This shortcut is a gateway to many formatting options, even when the ribbon is collapsed. When regular cells are selected, it displays the Format Cells dialog box with the “last tab used” selected. This is a very fast way to access font controls, borders and fills, alignment options, and number formats from anywhere in Excel. When you’re working with a chart, the same shortcut will open various formatting dialogs, depending on what you have selected....

Explanation In this example, the goal is to calculate and display profit margin as a percentage for each of the items shown in the table. In other words, given a price of $5.00 and a cost of $4.00, we want to return a profit margin of 20%. Each item in the table has different price and cost, so the profit varies across items. Profit margin is the ratio of profit divided by revenue....

Purpose Return value Syntax =HEX2OCT(number,[places]) number - The hexadecimal number you want to convert to octal. places - [optional] Pads the resulting binary number with zeros up to the specified number of digits. If omitted returns the least number of characters required to represent the number. Using the HEX2OCT function Excel only converts to octal numbers of 10-digits or less, restricting the range of valid input to [0, 7777777777] (octal). The input number must be less than or equal to ten alpha-numeric characters, otherwise the function returns the #NUM!...

Explanation The goal is to mark records with an “x” when the color is “Red” and the size is “Small”. To perform this task, you can use the IF function in combination with the AND function . IF function The IF function runs a test, then returns one value if the result is TRUE, and a different value if the result is FALSE. The generic syntax for IF looks like this:...

Explanation This is a more advanced formula. For basics, see How to use INDEX and MATCH . Normally, an INDEX MATCH formula is configured with MATCH set to look through a one-column range and provide a match based on given criteria. Without concatenating values in a helper column , or in the formula itself, there’s no way to supply more than one criteria. This formula works around this limitation by using boolean logic to create an array of ones and zeros to represent rows matching all 3 criteria, then using MATCH to match the first 1 found....

This article explains the function list separator used in Excel functions. By default, Excel uses the list separator defined under regional settings in Control Panel. The US English version of Excel uses a comma (,) for list separator by default, while other international versions may use a semicolon (;). This impacts how functions are entered in Excel. In the United States, and countries like Canada, Australia, United Kingdom, etc. functions are entered with arguments separated by commas....

Purpose Return value Syntax =MID(text,start_num,num_chars) text - The text to extract from. start_num - The location of the first character to extract. num_chars - The number of characters to extract. Using the MID function The MID function extracts a given number of characters from the middle of a supplied text string. MID takes three arguments, all of which are required. The first argument , text , is the text string to start with....

Explanation In this example, the goal is to retrieve the largest 3 (top 3) values in the named range data , which appears in the range B6:B16. The standard solution to get “nth largest values” is the LARGE function. However, one potential problem with LARGE is that it will return duplicate values if they are present in the source data. Named range For convenience, all values are in the named range data (B6:B16)....

Explanation In this example, the goal is to round a number to a given number of significant figures while preserving trailing zeros when needed. This is a tricky problem because Excel’s rounding functions return numbers, and numbers don’t preserve trailing zeros. This article uses “significant figures” and “significant digits” interchangeably. What are significant figures? How significant figures work The challenge in Excel Formula structure Calculating decimal places (dp) Adjusting decimal places for ROUND Formatting the result Simple formula with numeric result What are significant figures?...

Explanation In the example, cell C6 contains this formula: =ROUND(B6,-3) The value in B6 is 1,234,567 and the result is 1,235,000. With the ROUND function, negative numbers for the second argument round to the left of the decimal and positive numbers round to the right of the decimal. In this case, by supplying -3, we are telling ROUND to round the number to the 3rd place on the left – the 1000’s place....

Purpose Return value Syntax =SEARCH(find_text,within_text,[start_num]) find_text - The substring to find. within_text - The text to search within. start_num - [optional] Starting position. Optional, defaults to 1. Using the SEARCH function The SEARCH function returns the position (as a number) of one text string inside another. In the most basic case, you can use SEARCH to locate the position of a substring in a text string. You can also use SEARCH to check if a cell contains specific text....

About This Shortcut About This Shortcut This shortcut will extend the selection around the active cell to include the “current region” around the active cell. See Ctrl A, and ⌘ A for very similar behavior.

About This Shortcut This shortcut will move focus to the name box , located above column A: The current selection on the worksheet is unaffected, so you can use this shortcut to quickly select the name box and define a new named range . Alt + F3 works in Windows only and it appears to have been introduced in Excel 2016. On the Mac there is no equivalent shortcut. However, you can use another shortcut, F6, to cycle through “panes”....

Transcript In this lesson, we’ll look at two useful shortcuts for entering the current date and time. Control-semicolon will enter the current date; control-shift-semicolon will enter the current time. Let’s take a look. To enter the current date in Excel, use the shortcut control-semicolon. To enter the current time, use the shortcut control-shift-semicolon. If you want to enter both the current date and the current time, type control-semicolon, a space, then type control-shift-semicolon....

Explanation In this example, the goal is to sum the numbers in column E when the item in column B appears in the range G5:G7. The named range things is not required. It is used only for convenience and can be expanded as needed to include additional criteria. The article below explains several ways to solve this problem. SUMIFS with SUMPRODUCT One way to accomplish this is to give the SUMIFS function all three values in the named range things (G5:G7) as criteria, then use the SUMPRODUCT function to calculate a total....

Explanation In the example shown, we want to look up employee departments and groups using VLOOKUP by matching the first and last name of an employee. One limitation of VLOOKUP is that it only handles one condition: the lookup_value, which is matched against the first column in the table. This makes it difficult to use VLOOKUP to find a value using multiple criteria. However, if you have control over the source data, you can add a helper column that concatenates 2 or more fields together, and then give VLOOKUP a lookup value that does the same....

Explanation This formula is a classic example of a nested IF formula that tests threshold values in ascending order. To match the schedule shown in G5:G11, the formula first checks the late by time in D5 to see if it’s less than 5 minutes. If so, zero points are assigned: IF(D5<VALUE("0:05"),0, If the result of the logical test above is FALSE, the formula checks to see if D5 is less than the next threshold, which is 15 minutes:...

Explanation In this example, the goal is to calculate an average for any given group (“A”, “B”, or “C”) across all three months of data in the range C5:E16. For convenience only, data (C5:E16) and group (B5:B16) are named ranges . In the article below, we look at several approaches to this problem: Why the AVERAGEIFS function won’t work. A solution based on AVERAGE + FILTER A solution based on AVERAGE + IF function A solution based on SUMPRODUCT and Boolean algebra In the latest version of Excel, the FILTER option (#2) is easy and intuitive....