Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
As with dates, the key to entering a time in Excel is to enter it in a format that Excel will recognize as a time. When checking for a time, Excel will look for hours, minutes, seconds, and the AM or PM designation. Let’s take a look. You can, of course, enter a time with all components. For example, 7:00 PM can be entered like this: 7:00:00 PM And 8:30 AM like this: 8:30:00 AM However, Excel can understand the date in progressively shorter formats....
Setting a custom table style as the default table style is a little tricky. You need to create an Excel template that contains the custom style, and then make sure Excel uses the template when it starts up. Let’s walk through the process. First, create a new blank workbook. As a first step, I recommend that you highlight cell A1 in yellow. After you set up Excel to use a custom template, this makes it easy to verify that Excel is indeed using the template....
This article explains how join tables using VLOOKUP and a calculated column index. This is one way to use the same basic formula to retrieve data across more than one column. In the example shown, we are using VLOOKUP to pull Name and State into the invoice data table. The VLOOKUP formula used for both is identical: In this case, the first instance of the formula in column E returns 5, since column E is the 5th column in the worksheet....
In the example shown, the formula in L5 is: By nesting multiple VLOOKUPs inside the IFERROR function, the formula allows for sequential lookups. If the first VLOOKUP fails, IFERROR catches the error and runs another VLOOKUP. If the second VLOOKUP fails, IFERROR catches the error and runs another VLOOKUP, and so on. Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel....
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. They help me get formulas working faster. I also use named ranges because I’m lazy, and don’t like typing in complex references :)...
Note: for simplicity, we are using decimal (base 10) values, but there is a binary standard as well. See below. This formula works because digital units have a “power of 10” relationship. At the core, this formula separates the number part of the size from the unit, then divides the number by the appropriate divisor to normalize to Gigabytes. The divisor is calculated as a power of 10, so the formula reduces to this: To get the number, the formula extracts all characters from the left up to but not including the units: To get “power”, the formula matches on the unit in a hard-coded array constant: Which returns the position of the unit in the array constant....
In the example shown, the formula in C6 is: If you need to perform more than one replacement at the same time, you’ll need to nest multiple SUBSTITUTE functions. See the “clean telephone numbers” example linked below. If you need to replace a character at a specific location, see the REPLACE function. Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel....
In this worksheet, we have a set of data without formatting. To illustrate how undo and redo work, I’ll make some changes to the worksheet. But first, notice that I’ve set up the Quick Access Toolbar to show the Undo, Redo, and Repeat commands. This will make it easier to see and understand the history of changes. First, I’ll add a formula to calculate total price… Next, I’ll apply currency formatting to the Unit Price and Total columns....
Note: this is an array formula and must be entered with control + shift + enter, except in Excel 365. This looks pretty complicated but the gist is that we create an array of all characters in B5, and test each character to see if it’s a number. If so, we discard the value and replace it with an empty string (""). If not, we add the non-numeric character to a “processed” array....
where data is the named range C5:H16. The result is 303, the sum of values in the range F5:H16. Note: The TAKE function is new in Excel. See below for options that will work in older versions. TAKE function The TAKE function returns a subset of a given array or range. The size of the array returned is determined by separate rows and columns arguments. When positive numbers are provided for rows or columns, TAKE returns values from the start or top of the array....
where “data” is the named range B4:F104 Note: This approach is simple, but limited. For a more powerful solution that does not require a helper column, see this advanced formula based on Boolean logic. Also consider a more direct approach based on INDEX and MATCH or XLOOKUP. One limitation of VLOOKUP is that it only handles one condition: the lookup_value, which is matched against the first column in the table....
Since all data in the C5:F8 is provided as the return_array XLOOKUP returns the range E5:E8 as a result, which spills into the range H5:H8. In the example shown, we want to retrieve all values associated with Q3. The formula in H5 is: The lookup_value comes from cell H4, which contains “Q3” The lookup_array is the range C4:F4, which quarters in a header The return_array is C5:F8, which contains all data The match_mode is not provided and defaults to 0 (exact match) The search_mode is not provided and defaults to 1 (first to last)...
Pie charts work best to display data with a small number of categories (2-5). The Bar of Pie Chart provides a way to add additional categories to a pie chart without generating a pie chart too complex to read. When configuring a Bar of Pie chart, Excel provides a setting that moves the smallest n slices of the pie to the bar, where n can be adjusted to suit the data....
where color is the named range D5:D15. The result is 7, since “red” appears 4 times and “blue” appears 3 times in the range D5:D16. See below for an explanation and for other ways to solve this problem. Note: we are using COUNTIF in this example since it does what we need, but the COUNTIFS function would work equally well. Also note that because we are counting text values “red” and “blue” are enclosed in double quotes....
which returns 2, since there are 2 unique employee ids in building A. Note: this is an array formula and must be entered with control + shift + enter in Legacy Excel. The Excel FREQUENCY function returns a frequency distribution, which is a summary table that contains the frequency of numeric values, organized in “bins”. We use it here as a roundabout way to count unique numeric values. To apply criteria, we use the IF function....
To allow only numbers in a cell, you can use data validation with a custom formula based on the ISNUMBER function. In the example shown, the data validation applied to C5:C9 is: The ISNUMBER function returns TRUE when a value is numeric and FALSE if not. As a result, all numeric input will pass validation. Be aware that numeric input includes dates and times, whole numbers, and decimal values. Note: Cell references in data validation formulas are relative to the upper left cell in the range selected when the validation rule is defined, in this case C5....
ARABIC takes just one argument, roman_text, which should be a Roman number provided as a text string. For example, the formula below converts “VII” to the number 7: Other examples The ROMAN function performs the opposite conversion: Roman numbers The table below lists available Roman numbers with their equivalent Arabic number value. Notes The maximum length of roman_text is 255 characters. The ARABIC function performs the opposite conversion as the ROMAN function....
The BASE function takes three arguments: number, radix, and min_length. Number should be an integer between 1 and 2^53. If number is negative, BASE returns a #NUM! error. The radix argument is used to specify base. Radix represents the number of digits used to represent numbers and should be an integer between 2 and 36. The optional min_length argument is the minimum string length that BASE should return. When min_length is provided, BASE will pad the output with zeros as needed to achieve the length specified....
Examples In the example shown, the formula in cell E6 is: COUNTBLANK returns 3 since there are 3 blank cells in the range. Note that cell B12 is not included because it contains a space character (" “). Cell B13 contains a formula that returns an empty string: COUNTBLANK considers B13 blank and includes it in the count. Formulas that return empty strings The IF function is often used to return empty strings....
