On the Mac, Ctrl + X also works 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.
When you’re working with a chart, the same shortcut will open various formatting dialogs, depending on what you have selected. For example, if you have the chart area selected, it will open the Format Chart Area dialog. If you have data bars selected, it will open the Format Data Series dialog, and so on. You can also also use this shortcut when working with shapes and smart art. Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....
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.
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.
Standard deviation is a measure of how much variance there is in a set of numbers compared to the average (mean) of the numbers. The STDEVPA function is meant to estimate standard deviation for an entire population. If data represents a sample, use the STDEVA function. In the example shown, the formula in E5 is: Standard Deviation functions in Excel The table below summarizes the standard deviation functions provided by Excel....
Formulas Formula Basics Formula Examples Formula Criteria Formula Errors Dynamic Arrays Formula Challenges Formula Training Functions Function Guide 101 Excel Functions You Should Know INDEX and MATCH Nested IF examples Function Training Dynamic Arrays Dynamic Array Formulas in Excel Alternatives to Dynamic Array Functions Dynamic Array Formula Training Pivot Tables Pivot Table Introduction Pivot Table Tips Pivot Table Examples Pivot Table Problems Pivot Table Training Excel Shortcuts 200+ Excel Shortcuts 50 Excel shortcuts you should know 30 Excel Shortcuts in 12 minutes Shortcut Training...
The number 1 is divided by this array, which creates a new array composed of either 1’s or #DIV/0! errors: This array is used as the the lookup_vector. The lookup_value is 2, but the largest value in the lookup_array is 1, so lookup will match the last 1 in the array. Finally, LOOKUP returns the corresponding value in result_vector, from the dates in the range C$4:G$4. Note: the result in column H is a date from row 5, formatted with the custom format “mmm” to show an abbreviated month name only....
Note: this is an array formula and must be entered with Control-Shift-Enter. So, we need to build the functionality we need by nesting formulas. On way to do that is to use an array function that “tests” cells and returns an array of TRUE/FALSE values that we can feed into the MATCH function. Working from the inside out, the ISBLANK function evaluates the cells in the range B3:B11 and returns an array that looks like this: Each FALSE represents a cell in the range that is not blank....
Here’s the chocolate sales data we looked at earlier. Using the techniques discussed in this section, let’s build a simple dashboard to show sales by city and top 5 chocolate flavors. Notice, I’ve already added a tab for the dashboard with some placeholders. This makes it easier to visualize what we’re trying to build. So, the next step is to create the charts we need. First, I’ll create a new pivot table and chart on a new worksheet....
A pivot chart is an extension of a pivot table, so in order to have a pivot chart you must also have a pivot table. Excel provides several ways insert a pivot table and pivot chart in one step. First, try Recommended Charts. If you don’t have too much data, recommended charts will suggest pivot chart options with the small pivot icon in the upper right. This icon represents a pivot chart....
Let’s take a look. As we’ve seen previously, Excel provides a built-in conditional format preset to highlight duplicates in a set of values. In simple situations, like this list of colors, the format is easy to use and makes all duplicate values clear. But what about cases where the list of data contains more than one column, and individual values are naturally duplicated? When we select the data, and apply the preset for duplicate values, the entire table is highlighted, since all values are repeated at least once....
Let’s take a look. To modify a calculated field, you need to navigate to the Insert Calculated Field dialog box. First, select any cell in the pivot table. Then, on the Options tab of the PivotTable Tools ribbon, click “Fields, Items & Sets”, then choose Calculated Field. Next, select the calculated field you want to work with from the name drop-down list. You can now update the formula as you like....
Let’s take a look. Before you create a pivot table, make sure your source data is in good shape. Rows and columns need to be consecutive. If you have any blank rows or columns in the data, remove them first. Next, make sure that each column has a unique label in the first row. These labels will appear in the field list once the pivot table has been created. Now select any cell inside the data....
Let’s take a look. Here we have a worksheet that tracks the number of hours worked and gross pay of a small group of people. Everyone is paid the same hourly wage, so we need to set up a formula that calculates gross pay based on hours worked and a fixed hourly wage. In cell D9, let’s enter the first formula we need: =C9*C6 This works fine. John worked five hours at $11 dollars per hour, so his gross pay is $55....
Let’s take a look. Here we have the set of test scores we looked at previously. Let’s explore using conditional formatting with icon sets to highlight values in the table. Icon sets are another primary category in the Conditional Formatting menu. Here, you’ll see a large number of icons divided into four main area: Directional, shapes, indicators, and ratings. As with other conditional formats, Excel will build a live preview of each option on the worksheet Let’s choose a simple set of green, yellow, and red circles from the Shapes area....
Let’s take a look. In this worksheet, we have a list of materials to build a dog house in column B, and the cost of each item in column D. We also have the quantity needed for each item in column C. Let’s create a formula that calculates the total cost for each item, taking into account the quantity. This is a perfect application for relative cell references. To start off, we’ll create our first formula in cell E6....
and MAX returns the larger value. Elegant alternative to IF The MAX function can be a compact and elegant replacement for the IF function. For example, the formula above can be written with IF like so: However, the MAX version is 6 characters shorter (11 vs. 17), and contains no redundant references, so it is less prone to errors. Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....
where “values” is the named range B5:B14. Here, the number 4 specifies MAX, the number 6 is an option to ignore errors, and “values” is the named range B5:B14. With these settings, AGGREGATE returns the maximum in the remaining eight values, 100. Alternative with MAXIFS The MAXIFS function can return the max value in a set of data, after applying one or more criteria to filter out unwanted values. If values in the data set are known to be positive, you can use the following formula to return the maximum value while ignoring errors: This works because the “greater or equal to zero” expression effectively filers out error values, and MAXIFS returns the maximum value from the remaining 8 values, 100....
