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 a Mac, the control key (⌃) can be used instead of the command key (⌘). 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.
For example, for an asset with an initial cost of $10,000, a useful life of 5 years, and a salvage value of $2,000: In the example shown, the formula in C6, copied down, is: where named ranges are defined as follows: 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....
The slope of a line is a measure of steepness. Mathematically, slope is calculated as “rise over run”, or change in y over the change in x. For example, if a line has a slope of 2/1 (2), then if y increases by 2 units, x increases by 1 unit. Example In the example shown, the formula in E5 is: This formula returns -2, based on known_ys in C5:C9, and known_xs in B5:B9....
The SMALL function takes two arguments, array and k. Array is an array or range of numeric values. The argument k represents position or rank. For example, to return the smallest value in array, provide 1 for k. To return the fifth smallest value in array, provide 5 for k. To get nth largest values, see the LARGE function. Examples In the formula below, the SMALL function returns the third smallest value in a list of five numbers provided in an array constant: Note values do not need to be sorted....
where ct (G3), data (B3:E52), and helper (E3:E52) are named ranges. This formula deals with this challenge by using a helper column that returns a numeric value that can be used to easily extract multiple matches. The formula in the helper column looks like this: The helper column tests each row in the data to see if the Department in column C matches the value in I3 and the Building in column D matches the value in J3....
Although you can reference any cell in a pivot table with a normal reference (i.e. F8) the GETPIVOTDATA will continue to return correct values even when the pivot table changes. In this case, we want a subtotal of the “sales” field, so we provide the name the field in the first argument, and supply a reference to the pivot table in the second: This will give us the grand total....
As the formula is copied down, it returns the previous Sunday for each date shown in column B. See below to customize the formula to return different days of the week. Note: the formulas below use simple math to adjust the date. This works because Excel dates are large serial numbers. WEEKDAY solution The WEEKDAY function accepts a date and returns a number between 1-7 representing the day of week....
In the example shown, the range B5:F15 is formatted with a conditional formatting rule based on this formula: Note: it’s important that CF formulas be entered relative to the “first cell” in the selection, B5 in this case. Once you save the rule, you’ll see the rows that contain one or more blank cells highlighted. In this case, the column references are locked to prevent columns from changing as the formula is evaluated, but the row references are relative so that row numbers are free to change....
Let’s take a look. Let’s look first at the Time code reference table. This table shows the time codes available for custom time formats. There are codes for hours, minutes, and seconds with and without leading zeros. There are also two options for AM/PM code, and square brackets, which we’ll get to in a moment. Now let’s look at Table A. In the left column, we have a list of times....
This table contains data for ten properties. I can easily create a named range for this data. For example, I can create a range called “data.” Then, using the ROWS function, I can get a total count of properties. The problem is that this range is not dynamic. If I copy and paste data for six more properties below the existing data, the range doesn’t expand, and any formulas that refer to the range will show incorrect results....
A simple way to do this is to enter a time or date stamp. You can do this using keyboard shortcuts. Control semicolon enters the current date. Control-Shift semicolon enters the current time. If you want to add both the current date and time use the date shortcut, add a space, and then use the shortcut for time. Now let me add a formula in column D to pick up the values in column C, so you can see the actual values....
Let’s take a look. Here we have a pivot table that shows Sales and Orders by product, sorted by Sales. Let’s filter the products to show only the top 3 products by Sales. Notice the Value Filter is now active. Now, let’s add a Label Filter to show only products that end with the word “chocolate”. Notice that when we click OK to apply this filter, we see all products that end in chocolate, as expected, but the Value Filter is removed in the process....
Let’s take a look. The TEXT function has just one purpose—to convert a number into a text value using a specific number format. The function takes two arguments: the value or number, and something called “format_text” which is the number formatting you want to use when the number is converted. In the first table, we have some random numbers in the left column. I’ll add a formula in column C to show just the raw values in the General number format....
Let’s take a look. Here we have a table that contains monthly sales data for a list of customers. In addition to monthly sales, it includes the customer name, city, state, date of first order, and total orders. As we’ve seen previously, we can easily sort data like this using the quick sort buttons on the Data tab of the ribbon. For example, we could sort by the date of the first order, or by the state....
This model calculates the break-even point for a single product based on assumptions about price, variable costs, and fixed costs. In addition, it shows operating income at different sales volumes. If we change an assumption, the model recalculates. In the sales volume table, it would be nice to show the values used for selling price, variable cost per unit, and unit contribution margin so that the current assumptions are always clear....
A row or column of related numbers plotted in a chart is called a data series. Excel can plot data in arranged in rows or columns. When you create a new chart, Excel makes a decision about data series. When the data contains more rows than columns, Excel uses columns for the data series. When the data contains more columns than rows, Excel uses rows for the data series. For example, here we have chocolate sales data for 6 months....
When an item is “red”, and either “small” or “medium”, the formula returns “x”. All other combinations return an empty string (""). To extend the statement to return TRUE when items are red and either small or medium, we can nest the OR function inside the AND function like this: This snippet will return TRUE only if the value in B6 is “red” AND the value in C6 is either “small” or “medium”....
