Let’s take a look. The easiest way to create a custom pivot table style is to first apply a built-in style and then duplicate and customize that style as needed. For example, let’s apply a medium style to this pivot table. You can check that this style is applied by selecting any cell in the pivot table and then checking the PivotTable styles group. The applied style will be highlighted....
The first is that when you enter a 2-digit year, Excel will assign a century to create a valid date using a special rule. When the year is 29 or less, Excel will use the twenty-first century. When the year is 30 or greater, Excel will use the twentieth century. The second thing to be aware of is that Excel does not recognize any dates before Jan 1, 1900. Let’s take a look....
However, #N/A errors can also be caused by extra space characters, misspellings, or an incomplete lookup table. The functions mostly commonly affected by the #N/A error are classic lookup functions, including VLOOKUP, HLOOKUP, LOOKUP, and MATCH. The best way to prevent #N/A errors is to make sure lookup values and lookup tables are correct and complete. If you see an unexpected #N/A error, check the following first: Note: if you get an incorrect result, when you should see a #N/A error, make sure you have exact matching configured correctly....
Voting results source data Many people stop with this basic automatic grouping, but pivot tables can also group data in more sophisticated ways. For example, you can also use a pivot table to group data by numbers – summarizing data by age range, price range, or any numerical range that makes sense for your data. To illustrate, let’s assume you have a list of voting results that includes voter age, and you want to summarize the results by age group....
In this worksheet, we have the first 10 letters in the alphabet in the range B5:B14. How can we sort this data in random order? One way to do this is to add a helper column and use RAND function to generate random values. Then we can use the SORT function to sort data by the helper column. This works, but it clutters up the worksheet. This is a situation where it makes sense to use the SORTBY function, which can use arrays that don’t exist in the source data to sort....
One of the nice things about slicers, compared to report filters, is that they can control multiple pivot tables or charts. This makes slicers very useful for building dashboards that are based on pivot tables. In this worksheet, we have a simple pivot table that shows total sales by Product. I’m going to duplicate the pivot table, and set the second pivot table to show sales by City. Next, I’ll add a slicer to filter by Region....
Let’s take a look. In this example, we have a table of commission rates on the right and a list of sales numbers in a table on the left. I’ve already created a named range for the Sales numbers in column F. Let’s use MATCH to look up the correct commission tier for each sales number. I’ll start off entering the MATCH formula in C6. The match value comes from column B, and the lookup_array is “sales”....
The result is $17.00, the Price of a Large Red T-shirt. This is an array formula and must be entered with with Control + Shift + Enter in Legacy Excel. Note: In the current version of Excel, you can use the same approach with the XLOOKUP function. Normally, an INDEX MATCH formula is configured with MATCH set to look through a one-column range and provide a match based on given criteria....
In the example, we need to map the numbers 1-6 as follows: Since there is no way to derive the output (i.e. it’s arbitrary), we need to do some kind of lookup. The VLOOKUP function provides an easy way to do this. In the example shown, the formula in F7 is: Although in this case we are mapping numeric inputs to numeric outputs, the same formula will handle text values for both inputs and outputs....
As the formula is copied down, it calculates a 3-day moving average based on the sales value for the current day and the two previous days. Below is a more flexible option based on the OFFSET function which handles variable periods. About moving averages A moving average (also called a rolling average) is an average based on subsets of data at given intervals. Calculating an average at specific intervals smooths out the data by reducing the impact of random fluctuations....
Inside the REPT function, the text to repeat is provided as a single asterisk ("*"). The number of asterisks needed for each value is determined with with the LEN function in this bit of code here: We start with 12, then subtract the length of the text in column B. In cell B5, “Sebastian” is 9 characters, so the result is 3. The formula is evaluated like this: The results in column C is formatted with a monospaced font (Courier New) to clearly show all strings are the same length....
Fields The pivot table shown is based on two fields: Name and Color. The Color field is configured as a row field, and the name field is a value field, as seen below: The Name field is configured to summarize by count: You are free to rename “Count of Name” as you like. Steps Notes Author Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....
Note: When there are ties in top or bottom values, Excel will display all tied records. In the example shown, the pivot table is filtered on top 3, but displays 4 players, because Borg and Djokovic are tied for third place. Data The source data contains three fields: Year, Country, and Champion. This data is contained in an Excel Table starting in cell B4. Excel Tables are dynamic and will automatically expand and contract as values are added or removed....
One thing to note is the approval and disapproval ratings don’t add up to 100%. This is due to some survey respondents expressing no opinion. I played with plotting “no opinion” as a another data series in the chart, but decided to leave it out to keep things simple. Update: The Washington Post now has a full page of fancy poll result charts. Steps to create Data Sources: langerresearch.com, Washington Post-ABC Poll (Reagan-Trump), Gallup polls (Eisenhower-Carter) | wapo....
where n=5 because each week contains 5 rows of data. Calculating ranges The OFFSET function returns a reference to a range constructed with five inputs: (1) a starting point, (2) a row offset, (3) a column offset, (4) a height in rows, (5) a width in columns. In the example shown, we use OFFSET inside of SUM like this: The starting reference is C5, since this is the first value in the data....
When you create a chart in Excel, you’re plotting numeric data organized into one or more “data series”. A data series is just a fancy name for a collection of related numbers in the same row, or the same column. For example, this data shows yearly sales of shorts, sandals, t-shirts, and hoodies for an online surf shop. If I create a column chart with the default options, we get a chart with three data series, one for each year....
Convert negative numbers in place If you only need to convert negative numbers once, you can convert in-place with Paste Special: The video on this page shows this technique and many other paste special shortcuts. 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 data is the named range B5:B15. COUNTIF function One way to count individual errors is with the COUNTIF function like this: This is an odd syntax since technically errors are not text values. But COUNTIF is in a group of eight functions that have some quirks, and this is one of them. During calculation, COUNTIF is able to resolve the text into the given error and return a count of that error....
The result is 8, since there are eight cells in the range B5:B15 that contain numeric values. COUNT function The COUNT function counts the number of cells in a range that contain numeric values. In this example, we simply need to give COUNT the range B5:B15: The COUNT function is fully automatic, so there is nothing to configure. The result is 8, since there are eight cells in the range B5:B15 that contain numeric values....
Date configuration By default, ACCRINT will calculate accrued interest from the issue date. If the settlement date is in the first period, this works. However, if the settlement date is not in the first period, you probably don’t want total accrued interest from the issue date but rather accrued interest from the last interest date (previous coupon date). As a workaround, based on the article here: set issue date to the previous coupon date set first_interest date to the previous coupon date...
