Posts

Which parses a 6-character time string in hhmmss format into a valid Excel time. Note: the examples above use different time format codes as indicated in the screenshot. Context Excel expects times in Excel to be entered with the hour and minute separated by a colon. If you are entering a time with seconds, you’ll need to add another colon to separate minutes and seconds, as seen in the table below: The example on this page shows one way to skip the colons and enter a simple 4-digit or 6-digit text string that represents a time, then parse the text into a proper Excel time with a formula in a helper column....

Fields The source data contains three fields: Date, Sales, and Color. Only two fields are used to create the pivot table: Date and Color. The Color field has been added as a Row field to group data by color. The Color field has also been added as a Value field, and renamed “Count”: The Date field has been added as a Column field and grouped by year: Helper column alternative As an alternative to automatic date grouping, you can add a helper column to the source data, and use a formula to extract the year....

Fields The source data contains three fields: Name, Age, and Group. Only Name and Age are used in the pivot table as shown: Age is used as a Row field. After Age has been added to the pivot table, it has been grouped as below: Starting and ending value are automatically entered based on the source data. The “by” is set to 10 years, but can be customized as needed. The pivot table maintains age grouping when fields are added or reconfigured....

Fields In the pivot table shown, there are three fields in the source data: Date, Sales, and Filter. Filter is a helper column with a formula flagging the last 12 months. The Date field has been grouped by Year and Month: Formula The formula used in E5, copied down, is: This formula returns TRUE when a date is greater than or equal to the first day of the month 12 months earlier and when the date is less than the last day of the previous month....

How to make this pivot table Author 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.

In the example shown, the formula in C5 is: To get an ordinal suffix for a small set of numbers, you can use the CHOOSE function like this: Here CHOOSE simply picks up a number from column B and uses that number as an index to retrieve the right suffix. A universal formula With a larger range of numbers it’s not practical to keep adding values to CHOOSE. In that case, you can switch to a more complicated formula that uses the MOD function: This formula first uses MOD with AND to “trap” the case of numbers like 11, 12, 13, 111, 112, 113, etc that have a non-standard suffix with is always “th”....

When the link is clicked in Excel, the default email client will create a new email with the information supplied. The link text (“link”) can be customized as desired. Note: the formula above is entered with line breaks for better readability. Mailto link protocol The mailto link protocol allows five variables as shown in the table below: Notes: (1) separate multiple email addresses with commas. (2) Not all variables are required....

Excel has many useful shortcuts for selecting cells. This includes shortcuts for selecting rows, columns, groups of cells, and even the entire worksheet. Let’s take a look. When you’re selecting individual cells, you can use the arrow keys to move the selection around. If you hold down the shift key, and then press an arrow key, you can extend this selection in any direction without using the mouse. To select an entire column, press control-spacebar....

where “lower” is the named range I5, and “upper” is the named range I6. Thanks to Robert Johnson for his fix to the original formula, which broke in certain cases. Current version should work when upper > lower. To calculate total hours between start and end time, the formula in D5 is: This formula is explained in more detail here. The formula in E5 works in two parts, using IF to control flow....

The main reason SUMPRODUCT appears so often in Excel formulas is that it supports array operations natively, and array operations combined with Boolean logic are a very good way to solve many problems in Excel. In the past (Excel 2019 and older) Excel’s formula engine did not handle most array operations without special handling. As a result, SUMPRODUCT has always been a simple way to create an array formula that “just works”....

A worksheet is a collection of cells where all your data and formulas are stored. Each cell can contain either data (numeric or text) or a formula. Cells are arranged in rows and columns in the workbook. Rows are labelled with numbers going from 1 at the very top to 1,048,576 at the very bottom. Columns are labelled with letters going left to right starting with A and going to XFD....

which performs a wildcard match with the value in H4 and returns all 4 fields as the result. The TRANSPOSE function is optional and used here only to convert the result from XLOOKUP to a vertical array. The lookup_value comes from cell H4 The lookup_array is the range D5:D15, which contains Last names The return_array is B5:E15, which contains all all fields The not_found argument is set to “Not found” The match_mode is is 2, to allow wildcards The search_mode is not provided and defaults to 1 (first to last)...

What’s so great about these files? Smaller File Sizes – I usually see about 30% reduction in file size when compared to xlsx or xlsm type files.Supports VBA/Macros – similar to xlsm and xls files the xlsb type is also VBA/macro enabled.Open and Save Quicker – computers speak binary, so it makes sense that they will be able to read and write these much quicker since it’s already in their language and they don’t need to translate it first....

If you work with Excel day in and day out, Excel Keyboard Shortcuts can help you shorten your workday and save a lot of time. Additionally, it also has the power to enchant your co-workers and boss into thinking you’re an Excel Wizard. When I started my career as an analyst, I was completely in awe with one of my colleagues who was super-fast while working with Excel. He always seemed to have a keyboard shortcut for everything....

In the example shown, the formula in C5 is: In this part of the formula, MID, ROW, INDIRECT, and LEN are used to convert a string to an array or letters, as described here. MID returns an array of all characters in the text. {“W”;“i”;“l”;“l”;“i”;“a”;“m”;" “;“S”;“h”;“a”;“k”;“e”;“s”;“p”;“e”;“a”;“r”;“e”} This array is fed into the CODE function, which outputs an array of numeric ascii codes, one for each letter. Separately, ROW and INDIRECT are used to create another numeric array: This is the clever bit....

In this part of the formula, COUNTIF will return a positive number if any cell in the range is less than 65, and zero if not. In the range B5:F5, there is one score below 65 so COUNTIF will return 1. The NOT function is used to convert the number of from COUNTIF into a TRUE or FALSE result. The trick is that NOT also “flips” the result at the same time:...

Here is the data used to plot this chart: How to make this chart 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.

Which retrieves data where the State = “TX”. Since there are 11 cells in the range E5:E11, this expression returns an array of 11 TRUE and FALSE values like this: This array is used by the FILTER function to retrieve matching data. Only rows where the result is TRUE make it into the final output. Finally, the if_empty argument is set to “not found” in case no matching data is found....

COUNTIF returns 1, since there is one cell in C5:C16 with a value less than 75. The test scores in the range C5:C16 and we want to count scores less than 75, so we configure COUNTIF like this: Since there is only one score in C5:C16 that is less than 75, COUNTIF returns 1 as a result. Notice that criteria is given as a text value in double quotes ("")....

The result is 9, since nine cells in the range C5:C16 contain values. COUNTA function While the COUNT function only counts numbers, the COUNTA function counts both numbers and text. This means you can use COUNTA as a simple way to count cells that are not blank. In the example shown, the formula in F6 uses COUNTA like this: Since there are nine cells in the range C5:C16 that contain values, COUNTA returns 9....