Posts

Purpose Return value Syntax =HEX2BIN(number,[places]) number - The hexadecimal number you want to convert to binary. 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 HEX2BIN function Excel only converts to binary numbers of 10-digits or less, restricting the input range to [-512, 511] (decimal). The input number must be less than or equal to ten alpha-numeric characters, otherwise the function returns the #NUM!...

About This Shortcut This shortcut toggles the display of objects on the worksheet (e.g. pictures, smart art, charts, etc.) About This Shortcut This shortcut will display the Modify Styles dialog box.

About This Shortcut Hide rows that intersect the current selection. About This Shortcut Unhide any hidden rows that intersect the current selection.

Explanation When you use a formula to apply conditional formatting, the formula is evaluated for each cell in the range, relative to the active cell in the selection at the time the rule is created. So, in this case, if you apply the rule to B4:G11, with B4 as the active cell, the rule is evaluated for each of the 40 cells in B4:G11 because B4 is entered as a fully relative address....

Transcript In this video, we’ll look at how to create a 100% stacked column chart. A 100% stacked column chart is a sub type of a regular stacked column chart. The difference is that the 100% stacked version is meant to show the proportional breakdown of multiple data series in stacked columns, where the total height is always 100%. In this worksheet, we have quarterly sales data broken down by region and quarter....

Transcript In this video, we’ll look at how to create a line chart in Excel. Here we have historical, 30-year mortgage interest rates going back to 1971. Let’s plot this data in a line chart. Line charts are especially good at visualizing trends over time. To start off, I’ll place my cursor anywhere in the data, then navigate to the insert tab and click the line chart icon. The first option is the one I want....

Transcript In this video, I want to show you how to build a quick summary table using the COUNTIF and SUMIF functions. Here we have a sample set of data that shows t-shirt sales. You can see we have columns for date, item, color, and amount. So let’s break this data down by color. Now, before we start, I want to mention that Pivot Tables would be an excellent way to summarize this data, but you can certainly use formulas for basic summaries, and that’s what I’m going to demonstrate here....

Transcript When you look at a complex formula in Excel you may be completely baffled at first glance, but all complex formulas are just small steps added together. Let me show you an example. Here we have a list of names. We want to pull out the first name from the full name. There’s an Excel function called LEFT that will extract text from the left, so let’s start there....

Explanation About spilling and the #SPILL! error With the introduction of Dynamic Arrays in Excel , formulas that return multiple values " spill " these values directly onto the worksheet. The rectangle that encloses the values is called the " spill range “. When data changes, the spill range will expand or contract as needed. You might see new values added, or existing values disappear. Video: Spilling and the spill range...

Update: in the current version of Excel you can use the FILTER function to get all matches and the XLOOKUP function to get the last match only. Depending on your needs, FILTER might be a better option than first or last match. One of the more confusing aspects of lookup functions in Excel is understanding how to get the first or last match in a set of data with more than one match....

Transcript In this lesson we’ll show you how to adjust the size and location of a chart. Let’s take a look. By default, Excel charts will automatically move and resize when cells underneath them change. We can see this behavior in action if we increase or decrease the width of any columns underneath the chart. The same is true of rows. If we increase or decrease row heights, the chart automatically expands and contracts....

Transcript In this video, we’ll look at how to plot results from a survey question in an Excel bar chart. Here we have data from a survey we ran recently. We’ve got around 3900 responses to the question “What version of Excel do you use most?”. Let’s plot the data in a chart. The first step is to build a summary table. First, I’ll convert the data to an Excel table....

Transcript In this video, we’ll look at how to use the SUMIFS function with an Excel Table. On this worksheet, I have two identical sets of order data. I’m going to walk through the process of constructing a summary of sales by item for both sets of data. With the data on the left, I’ll use standard formulas and no table. On the right, I’ll create an Excel Table, and build formulas that use structured references....

Explanation The goal is to “flag” records that are neither “Red” nor “Green”. More specifically, we want to check the color in column B, and leave an “x” in rows where the color is NOT “Red” OR “Green”. If the color is “Red” OR “Green”, we want to display nothing. IF function logic The IF function is commonly used for simple tests. For example, to return “OK”, when a value is over 100 and “Fail” if not, you can use the IF function in a formula like this:...

Purpose Return value Syntax =IMCOT(complex_num) complex_num - The complex number in the form “x+yi”. Using the IMCOT function The Excel IMCOT function returns the cotangent of a complex number. For example, given “3+4i” as input, the function returns “-0.000187588-1.000644392i” as output. =IMCOT("3+4i") // returns -0.000187588-1.000644392i Explanation In math, the cotangent of a complex number is defined using the complex sine and cosine functions. In Excel, the cotangent of a complex number is equivalent to the following formula....

Explanation This example is based on a fitness assessment where the goal is to award points based on how much weight was lifted at a given age. The solution described below is based on an INDEX and MATCH formula , but there are several tricky elements that must be considered, making this problem much more difficult than your average lookup problem. The formula in L12 is: =INDEX(points,MATCH(L6,INDEX(data,0,MATCH(L5,--LEFT(age,2),1)),-1)) The formulas in L8:L10 are for additional explanation only....

Explanation Excel does not provide a dedicated “contains” function, but you can create a custom function to test if a cell contains one or many strings with the LAMBDA function . LAMBDA functions do not require VBA, but are only available in Excel 365 . The first step in creating a custom LAMBDA function is to verify the logic needed with a Excel standard formula. This LAMBDA formula is based on a Excel formula created with three functions: SUMPRODUCT , ISNUMBER , and SEARCH :...

Excel Tables provide a special kind of formula syntax called a structured reference . In a nutshell, structured references makes it possible to refer to parts of an Excel Table by name, so you can write formulas like this: =SUM(Table1[Total]) // sum total column =MAX(Table1[Total]) // max of total column One of the more complicated examples of this syntax occurs when locking column references. Although it may not look like it, a reference to a table column is relative and will change as a formula is copied....

Explanation Note: If you are new to INDEX and MATCH, see: How to use INDEX and MATCH In a standard configuration, the INDEX function retrieves a value at a given row and column. For example, to get the value at row 2 and column 3 in a given range: =INDEX(range,2,3) // get value at row 2, column 3 However, INDEX has a special trick – the ability to retrieve entire columns and rows....

Explanation The IFERROR function is designed to trap errors and perform an alternate action when an error is detected. The VLOOKUP function will throw an #N/A error when a value isn’t found. 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....