Posts

Note: you can adjust speed and quality with the gear icon in the lower right. 

Negative Values Excel internally represents Hexadecimal numbers in binary using 40 bits. The first bit indicates whether the number is positive or negative. The remaining bits indicate the magnitude of the number. Negative numbers are internally represented in binary using two’s complement notation. Two’s complement notation, when converted to hexadecimal, starts at the highest 10 digit hexadecimal number and goes backwards. Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....

In the example shown, the formula in C6, copied down, is: Notes: Only lowercase “j” and “i” are accepted by IMREAL. Other values will result in the #NUM error. 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....

Grab the sample data and give it a try. Learning Pivot Tables is a skill that will pay you back again and again. Pivot tables can dramatically increase your efficiency in Excel. What is a pivot table? You can think of a pivot table as a report. However, unlike a static report, a pivot table provides an interactive view of your data. With very little effort (and no formulas) you can look at the same data from many different perspectives....

On the Mac, Control-B also deletes cell contents of one or more cells. 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.

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.

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.

On the Mac, options are called “preferences”, and the shortcut command + , will open preferences in most applications, not just Excel. 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.

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.

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.

Syntax The syntax for the SUMIFS function depends on the number of conditions needed. Each separate condition will require a range and a criteria. The generic syntax for SUMIFS looks like this: The first argument, sum_range, is the range of cells to sum, which should contain numeric values. The second argument, range1, is the range to which the first condition should be applied. The third argument, criteria1, contains the condition that should be applied to range1, along with any logical operators....

Vertical data | Column Numbers | Only looks right | Matching Modes | Exact Match | Approximate Match | First Match | Wildcard Match | Two-way Lookup | Multiple Criteria | #N/A Errors | Videos Introduction VLOOKUP is probably the most famous function in Excel, for reasons both good and bad. On the good side, VLOOKUP is easy to use and does something very useful. For new users in particular, it is immensely satisfying to watch VLOOKUP scan a table, find a match, and return a correct result....

where list1 (B5:B16) and list2 (D5:D14) are named ranges. The result returned by FILTER includes only the values in list1 that appear in list2. Note: FILTER is a new dynamic array function in Excel 365. working from the inside out, the COUNTIF function is used to create the actual filter: Notice we are using list2 as the range argument, and list1 as the criteria argument. In other words, we are asking COUNTIF to count all values in list1 that appear in list2....

The result is a filtered set of data that contains only columns A, C, and E from the source data. In Boolean algebra, multiplication corresponds to AND logic, and addition corresponds to OR logic. In the example shown, we are using Boolean algebra with OR logic (addition) to target only the columns A, C, and E like this: After each expression is evaluated, we have three arrays of TRUE/FALSE values: The math operation (addition) converts the TRUE and FALSE values to 1s and 0s, so you can think of the operation like this: In the end, we have a single horizontal array of 1s and 0s: which is delivered directly to the FILTER function as the include argument: Notice there are 6 columns in the source data and 6 values in the array, all either 1 or 0....

This function can be used to find the first occurrence of a text string within another text string. Syntax FIND(Text to find, Text, Start position) Text to find (required) – This is the text you want to find within another text string.Text (required) – This is the text you want to search within.Start position (optional) – This is the result the function will return if the expression is false. If nothing is entered here the function will return FALSE when the expression is false....

You must save the worksheet in order to get the a result. Note: the CELL function is a volatile function and can cause performance problems in larger or more complicated worksheets. In this example, we want the path, name, and sheet for the current workbook. To get this information, the info_type argument is set to “reference” in a formula like this: With this configuration, CELL will return the name in this format: Note that you must save the worksheet in order to get the a result....

Although you can reference any cell in a pivot table with a normal reference (i.e. F11) the GETPIVOTDATA will continue to return correct values even when the pivot table changes. In this case, we want the grand total of the “sales” field, so we simply provide the name the field in the first argument, and supply a reference to the pivot table in the second: The pivot_table reference can be any cell in the pivot table, but by convention we use the upper left cell....

Note: In the generic form of the formula “domain” represents a domain or email address like “www.domain.com”. At the core, this formula uses the RIGHT function to extract characters starting from the right. The other functions in this formula just do one thing: they figure out how many characters need to be extracted, n: At a high level, the formula replaces the last dot “.” in the domain with an asterisk () and then uses the FIND function to locate the position of the asterisk....

If you want to highlight cells that contain unique values in a set of data, you can use a formula that returns TRUE when a value appears just once . For example, if you have values in the cells A1:D10, and want to highlight cells with duplicate values, you can use this formula: Note: with conditional formatting, it’s important that the formula be entered relative to the “active cell” in the selection, which is assumed to be A1 in this case....

If you want to highlight cells that are “greater than X” with conditional formmatting, you can use a simple formula that returns TRUE when a cell value is greater than X. For example, if you have numbers in the cells B4:G11, and want to highlight cells with a numeric value over 100, you select B4:G11 and create a conditional formatting rule that uses this formula: It’s important that the formula be entered relative to the “active cell” in the selection....