The result is a path without the filename like this: “C:\path". At a high level, this formula works in 3 steps: Get path and filename To get the path and file name, we use the CELL function like this: The info_type argument is “filename” and reference is A1. The cell reference is arbitrary and can be any cell in the worksheet. The result is a full path like this as text: Note the sheet name (Sheet1) appears at the end, and workbook name appears inclosed in square brackets, [workbook....

If you want to highlight cells that contain certain text, you can use a simple formula that returns TRUE when a cell contains the text (substring) that you specify. For example, if you want to highlight any cells in the range B2:B11 that contain the text “dog”, you can use: 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 B2 in this case....

Let’s take a look. Here we have the test score table we looked at previously. Currently, there is one conditional formatting rule applied to all test scores in the table. This rule applies a conditional format of a green fill with white text when the test score is greater than 95. Let’s add another conditional format rule to highlight test scores that are lower than 70, in red. As before, start by selecting the cells to format....

Let’s take a look. Pivot table styles are available on the Design tab of the PivotTable Tools ribbon. To apply a pre-built style, select any cell in the pivot table and navigate to the Design tab. All available styles are listed in the PivotTable styles group. As you hover your mouse over each style in the group, Excel will build a preview of that style applied to your pivot table in the background....

Stacked area charts make sense when you want to show changes in a part-to-whole relationship over time. 100% stacked charts are focused on proportions, at the expense of actual values. To better explain this, I’ll create a basic stacked area chart, then compare with a 100% stacked version. First, I’ll select data, excluding totals. Then I’ll use recommended charts to insert a stacked area chart. The result shows total sales, and how much each product line contributes to total sales....

To create a dynamic named range that refers to this data using the OFFSET function, first identify the first cell of the data in the upper left. In this case, that’s cell B6. To create a named range, we’re going to use the Name Manager. However, it will be easier to enter the formula using the formula bar, so I’ll start by entering the OFFSET function in cell K6, and then use that formula to create a named range in the next step....

Let’s take a look. Here is the same pivot table we’ve looked at previously, showing Sales and Orders by product. Let’s add a Value Filter on the Product field that limits products to the top 5 products by Sales. Top and bottom Value Filters are a special kind of Value Filter, so you’ll find the option under Value Filters in the drop-down menu for the field you want to filter....

Example 1 - space instead of colon In the screen below, the formula in C9 returns the #NULL error: In this case the input was meant to be the range C3:C7, but the colon did not get typed. Once the colon is added, the error is fixed: Example 2 - space instead of comma In the example below, the formula in C5 returns the #NULL error: Here, a space was typed instead of a comma between F2 and I2....

Let’s take a look. In column B of our table we have a set of numbers in General format. Let’s first copy the numbers to the rest of our table. Now let’s apply the Percentage format. Although percentage is listed in the Number format menu on the ribbon, it’s faster to use the Percentage button just below the menu. As we can see, Excel displays the numbers in standard percentage format....

Where “names” is the named range B6:B17, and times is the named range D6:D17. This is an array formula and must be entered with Control + Shift + Enter. This generates an array of TRUE / FALSE values, where TRUE corresponds to rows where the name matches the value in F6: The “value if true” for IF is the named range “times”, which returns the full set of times. The result is that the array from the logical test effectively “filters” the time values....

Fields The pivot table shown is based on three fields: City, Size, and Qty. The City field is added as a Row field, and the Size field is added as a Column field. The Qty field is is added as a Value field: The Qty field in the Values area is configured to Sum: By default the Pivot Table automatically sums values in the Qty field, so there is no need to change the calculation in this case....

which returns the 3 unique colors in group B with a quantity > 5. Working from the inside out, the FILTER function is used to collect source data in group B with a quantity greater than 5: Inside FILTER, the expression used for the include argument is: This is an example of using boolean logic to construct required logical criteria. The result is a boolean array like this: This array is used to filter data, and the FILTER function returns another array as a result: This array is returned to the UNIQUE function as the array argument....

Dynamic Array formulas are the biggest change to the Excel formula engine in years. Maybe the biggest change ever. This is because Dynamic Arrays make it easy to work with many values at the same time. For many users, this will be the first time they understand and use array formulas. Let’s look at a basic example. Here we have the shell of a multiplication table. What formula can we use to fill in the data?...

Number formats are used to control the display of cell values that contain numeric data. This numeric data can include things like dates, times, costs, percentages, and anything else expressed as a number. The most important thing to understand about number formats is that they only affect how a number looks—they have no effect on the actual value stored by Excel. Let’s take a look. The most common number formats are available on the home tab of the ribbon, in the Number format area....

In between each piece of text, the CHAR function appears with the character code 10. The character code for a line break in Excel varies depending on the platform. On Windows, the code is 10 and on a Mac it’s 13. The result of the concatenation is text with line breaks: Traci Brown¬ 1301 Robinson Court¬ Saginaw, MI 48607 Note: make sure you have Wrap Text enabled on cells that contain line breaks....

In the example show, the formula in D5 is: This formula divides the amount in column B by the denomination in C4 (100) and discards the remainder using the INT function. The formulas in column C are simpler than those in the next several columns because this is the first denomination – we don’t need to worry about previous counts. Next in D5 we first figure out what the value of existing denomination counts with: Here SUMPRODUCT is configured with two arrays, both configured carefully....

which returns 0.04167, the equivalent of 1 hours. Cell D6 shows the same result formatted as time, which displays 1:00. This means if you have a decimal number for hours, you can simply divide by 24 to get the correct representation of hours in Excel. After dividing by 24, you can apply a time format of your choice, or use the result in a math operation with other dates or times....

COLUMNS then returns a final result of 5, since there are 5 columns in the table. 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.

Note: with just the table name, ROWS will count data rows only. ROWS then returns a final result of 100. 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.

where target is the named range G5. In cell E5, the result is “Yes”, because the difference in D5 is less than or equal to 5%. Difference as percentage To calculate the difference as a percentage, we can use a general formula like this: After converting this to cell references, we have: As the formula is copied down, it returns a percentage difference in each row. Note that some values are negative....