To create a pivot table with a filter for day of week (i.e. filter on Mondays, Tuesdays, Wednesdays, etc.) you can add a helper column to the source data with a formula to add the weekday name, then use the helper column to filter the data in the pivot table. In the example shown, the pivot table is configured to show data for Mondays only. Pivot Table Fields In the pivot table shown, there are four fields in use: Date, Location, Sales, and Weekday....
This chart shows quarterly sales data, broken down by quarter into four regions plotted with clustered columns.Clustered column charts work best when the number of data series and categories is limited. In order to make the data labels fit into a narrow space, the chart uses a custom number format ([>=1000]#,##0,“K”;0) to show values in thousands. The data used to plot this chart is shown below: How to make this chart Select the data and insert a column chart: Select the clustered column option Initial chart: Switch rows and columns to group data by quarter: After switching rows and columns: Move legend to top: Add data labels: Select data labels (one series at a time), and apply the custom number format for thousands: Set series overlap and gap width: Select and delete primary axis Select and delete gridlines Final stacked column chart: At this point, you can finalize the chart by setting a title, and adjusting overall chart size, font size, and colors....
Explanation This formula breaks ties with a simple approach: this first tie in a list “wins” and is assigned the higher rank. The first part of the formula uses the RANK function normally: =RANK(C5,points) Rank returns a computed rank, which will include ties when the values being ranked include duplicates. Note that the RANK function by itself will assign the same rank to duplicate values, and skip the next rank value....
Explanation The first thing this formula does is check the date in column B against the start date: =IF(B4>=start If the date is not greater than the start date, the formula returns zero. If the date is greater than or equal to the start date, the IF function runs this snippet: (MOD(DATEDIF(start,B4,"m")+n,n)=0)*value Inside MOD, the DATEDIF function is used to get the number of months between the start date and the date in B4....
About This Shortcut Use this keyboard shortcut to select additional adjacent worksheets. When you hold the shift key down, and click another worksheet tab, all worksheets between the active worksheet and clicked worksheet will be selected. About This Shortcut Use this keyboard shortcut to select additional non-adjacent worksheets. When you hold the Control key down (Command on the Mac), and click another worksheet tab the active worksheet and clicked worksheet will be selected....
Transcript In this video, we’re going to cover shortcuts for worksheet navigation. These are shortcuts you can use move quickly around any single worksheet. In this worksheet, we have several thousand rows of data. Let me show you some shortcuts that will help you move around this data quickly. Every Excel worksheet has an active cell, and you’ll see the address of the active cell displayed in the name box, to the left of the formula bar....
Explanation Sometimes you encounter data that mixes units directly with numbers (i.e. 8km, 12v, 7.5hrs). Unfortunately, Excel will treat the numbers in this format as text, and you won’t be able to perform math operations on such values. To split a number from a unit value, you need to determine the position of the last number . If you add 1 to that position, you have the start of the unit text....
Explanation In this example, the goal is to sum values in the range D5:D16 when they are greater than the value entered in cell F5. This problem can be easily solved with the SUMIF function or the SUMIFS function. The main challenge in this problem is the syntax needed for criteria that uses the value in cell F5, which involves concatenation . SUMIF function The SUMIF function is designed to sum cells based on a single condition....
Explanation In this example, the goal is to sum the Amounts in C5:C16 when the Lead in D5:D16 is not blank (i.e., not empty). A good way to solve this problem is to use the SUMIFS function . However, you can also use the SUMPRODUCT function or the FILTER function , as explained below. Because SUMPRODUCT and FILTER can work with ranges and arrays , they are more flexible. Background study How to use the SUMIFS function Boolean operations in array formulas FILTER function basic example SUMIFS Function The SUMIFS function sums cells in a range that meet one or more conditions, referred to as criteria ....
Explanation In the example shown, we have a list of amounts in column C. The goal is to dynamically sum the last n amounts using the number that appears in cell E5 for n . Since the list may grow over time, the key requirement is to sum amounts by position. For convenience only, the values to sum are in the named range data (C5:C16). In the latest version of Excel, the best way to solve this problem is with the TAKE function , a new dynamic array function in Excel....
Explanation In this example, the goal is to return a count for each color that appears in column C, using the color values already in column E as criteria. When working with data, a common need is to perform summary calculations that show total counts in different ways. For example, total counts by category, color, size, status, etc. The COUNTIF function is a good way to generate these kinds of totals....
Explanation In this example, the goal is to calculate a count and percentage for each category shown in column B. For convenience, the category values in column B are in the named range category (B5:B122). To generate the count, we use the COUNTIF function . The formula in G5, copied through the range G5:G9 is: =COUNTIF(category,F5) The range is the named range category (B5:B122), and the criteria as is supplied as a reference to F5, which simply picks up the text value in column F....
Explanation In geometry, the standard formula for calculating the surface area of a cylinder is: In essence, this formula first calculates the area of the side of the cylinder, based on the circumference of the circle times the height of the cylinder, then adds two times the area of circle to account for the ends of the cylinder. The Greek letter π (“pi”) represents the ratio of the circumference of a circle to its diameter....
Lorem ipsum dolor pink amet selfies raclette williamsburg 3 wolf moon. Forage iPhone gochujang pitchfork. Godard listicle scenester retro pour-over bicycle rights street art. Irony glossier beard whatever live-edge, distillery church-key air plant. Sample h2 section I’m baby ullamco aliquip next level put a bird on it cronut salvia copper mug keffiyeh you probably haven’t heard of them deep v exercitation qui quinoa tilde brunch. VHS irony schlitz qui kickstarter pitchfork lyft green juice celiac asymmetrical velit....
Transcript In this video, we’ll look at how a pivot table and pivot chart are connected. Pivot charts are an extension of pivot tables. A pivot chart is always linked to a pivot table, and you can’t change one without changing the other. To illustrate this connection, I’ll start by creating a new pivot table and pivot chart. The first thing to notice is that both pivot tables and pivot charts change Excel’s interface when selected....
Explanation This formula uses XLOOKUP twice, by nesting one XLOOKUP inside another. The first (inner) XLOOKUP is used to perform an exact match lookup on the value in G5: XLOOKUP(G5,E5:E15,B5:E15) The lookup_value comes from cell G5 The lookup_array is E5:E15 (codes) The return_array is B5:E15 (all fields) The match_mode is not provided and defaults to 1 (exact match) The search_mode is not provided and defaults to 1 (first to last) The result is a match on “AX-160”, returned as an array of all four fields in the original order:...
An aggregate operation is an operation that returns a single value . For example, given a range of data, the following formulas return a single result, even when the data provided contains hundreds or thousands of values. =MAX(data) =MIN(data) =COUNT(data) =AVERAGE(data) =SUM(data) Common functions that perform aggregate operations include, MAX , MIN , COUNT , COUNTA , AVERAGE , AND , OR , and SUM . In many cases, an aggregate value is the desired result....
Purpose Return value Syntax =AVERAGE(number1,[number2],...) number1 - A number or cell reference that refers to numeric values. number2 - [optional] A number or cell reference that refers to numeric values. Using the AVERAGE function The AVERAGE function calculates the average of numbers provided as arguments . To calculate the average, Excel sums all numeric values and divides by the count of numeric values. AVERAGE takes multiple arguments in the form number1 , number2 , number3 , etc....
Explanation In this example the goal is to return rows in the range B5:E15 that have a specific state value in column E. To make the example dynamic, the state is a variable entered in cell H4. When the state in H4 is changed, the formula should return a new set of records. This is a perfect application for the FILTER function, which is designed to return values that meet specific logical criteria from a set of data....
Explanation In this example, the goal is to count the unique dates in a range of timestamps (i.e. dates that contain dates and times). In addition, we also want to create the table of results seen in E7:F9. For convenience, data is the named range B5:B16. Basic count To get a count of individual dates that occur in B5:B16, ignoring time values, the formula in F4 is: =COUNT(UNIQUE(INT(data))) Working from the inside out, the INT function is used to remove the time values from the timestamps like this:...
