In the example shown, the formula in G6 is: 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.
Note: In Mac 2016, Control - also works (same as Windows). 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.
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.
The SUBTOTAL function can either include or exclude values in hidden rows, which makes it especially useful in Excel Tables or filtered data. By default, SUBTOTAL excludes values in rows hidden by a filter. Regardless of the calculation performed, SUBTOTAL returns an aggregate result from a set of data. Note: the SUBTOTAL function automatically ignores other SUBTOTAL formulas that exist in references to prevent double-counting. Examples Below are examples of SUBTOTAL configured to SUM, COUNT, and AVERAGE the values in a range....
Syntax The generic syntax for SUMIF looks like this: The SUMIF function takes three arguments. The first argument, range, is the range of cells to apply criteria to. The second argument, criteria, is the criteria to apply, along with any logical operators. The last argument, sum_range, is the range that should be summed. Note that sum_range is optional. If sum_range is not provided, SUMIF will sum cells in the first argument, range....
In the example shown, the formula in D5 is: Next, the formula calculates the number of days required to “roll back” to the last requested weekday in the month prior (i.e. the month of the original date): Inside WEEKDAY, EOMONTH is again used to get the first day of the next month. From this date, the value for day of week is subtracted, and the result is fed into WEEKDAY, which returns the number of days to roll back....
where “holidays” is the named range G5:G9. In the example shown, we have the project start date in column C, and days in column D. Days represents the duration of he project in work days. In column E, the WORKDAY function is used to calculate an end date. Holidays are provided as the named range “holidays”, G5:G9. With these inputs, WORKDAY add days to the start date, taking into account weekends and holidays, and returns January 7, 2019 as the calculated end date....
Here we have data for monthly sales of a small business for the year 2016. Let’s build a column chart to plot this data. To start off, put the cursor anywhere in the data, then visit the Insert tab on the ribbon. Since I want a column chart, I’ll use the bar icon to the right of Recommended Charts, which contains both column and bar chart options. Now let’s make this a better chart....
Let’s take a look. Normally, when you choose a chart type on the ribbon, Excel creates an embedded chart which floats over the worksheet grid below. But if you’re short on space, or, if you’d just rather have a chart in its own worksheet, you can easily do so. To move a chart into its own sheet, first, select the chart. Then, go to the Design tab under Chart Tools and click the Move Chart button....
Let’s take a look. By default, a new pivot table will summarize all of the source data. However, the report filter gives you a way to quickly limit the information that appears in the table by filtering on one or more fields. For example, this pivot table shows Total Sales by product. If we add the City field as a Column Label, we see a breakdown of sales by city in each column....
Preventing #REF errors The best way to prevent #REF! errors is to prevent then from occurring in the first place. Before you delete columns, rows, or sheets be sure they aren’t referenced by formulas in the workbook. If you are copying and pasting a formula to a new location, you may want to convert some cell references to an absolute reference to prevent changes during the copy operation. If you cause a #REF!...
Let’s take a look. Here we have a workbook that contains several sheets. Let’s rearrange these worksheets into alphabetical order. To move a worksheet, just click on the tab with your mouse and drag the sheet to a new location. Excel will display a small black triangle to let you know exactly where the new sheet will be placed. To complete the move, just release the mouse. The worksheet tab will appear at the new location....
Let’s take a look. Here we have a small table of data that represents widget sales over a 3-month period. We have the quantity sold of each widget already in the table. We just need to add Sales, which equals the quantity sold x the price. The price appears in column C. For January, we just need a simple formula. The formula for Widget A is D9 * C9. And if we copy this formula down, we get the correct result for each widget type....
Let’s take a look. Like our previous examples, we have a simple worksheet that highlights several cell references. Following the instructions in the table, let’s build the formulas we need using division. The first formula doesn’t use any cell references. We just input = 10 / 2, which gives a result of five. The second example is D6 divided by 7, so we need to use both a number and a cell reference....
Let’s take a look. Here we have a list of employees in a table. Let’s use VLOOKUP to build a simple form that retrieves the information for a given employee based on their ID number. The first thing I’ll do is create a named range for the data in the table, and a named range for the ID that we’ll be using to look things up. By using named ranges, our lookup formulas will be easier to understand and easier to copy....
This page describes just one approach, where you make a line chart with two data series (one high, one low) and then use “up/down bars” to create the floating columns. The advantage of this approach is simplicity. There is no need to add calculations and a ghost data series to float the columns, as you’ll see in other examples. The data used to make the chart on this page looks like this:...
