Once you have a chart in Excel, you can easily change from one chart type to another. Just select the chart and click “change chart type” on the Design tab of the ribbon. Excel will open the change chart type window, where you can browse and select other chart types. Notice that Excel builds a preview for each type using the data you’ve specified in the existing chart. If you Hover over the image with your mouse, you’ll see a larger preview....

Empty array An empty array can trigger a #CALC! error, and this is the most common reason you may see a #CALC! error in a worksheet, especially when using the FILTER function. This is because FILTER returns a #CALC! error when no values meet criteria – in other words, FILTER returns an empty array. For example, in the screen below, the FILTER function is set up to filter the source data in B5:D11....

Let’s take a look. You might inherit a worksheet that comes from someone else that contains a lot of existing formatting, including heavy use of borders and fills. Rather than try to work with what’s there, piece by piece, it’s often easier and faster to just clear all borders and fills and start fresh. Both borders and fills can be removed using menus on the ribbon. First, select the cells you’d like to change....

The effect of showing “Closed” in light gray is accomplished with a conditional formatting rule. Display nothing if cell is blank To display nothing if a cell is blank, you can replace the “value if false” argument in the IF function with an empty string ("") like this: Alternative with ISBLANK Excel contains a function made to test for blank cells called ISBLANK. To use the ISBLANK, you can revise the formula as follows:...

where values in B5:B9 are sorted in descending order. Context Suppose you have a product that is sold in rolls of 100 feet, and orders are allowed in whole rolls only. For example, if you need 200 feet of material, you need two rolls total, and if you need 275 feet, you’ll need to buy three rolls. In this case, you want the formula to return the “next highest” tier whenever you cross over an even multiple of 100....

The goal in this example is to have 100,000 at the end of 10 years, with an interest rate of 5%. Payments are made annually, at the end of each year. The formula in cell C9 is: where: rate - from cell C6, 5%. nper - from cell C7, 25. pv - from cell C4, 0. fv - from cell C5, 100000. type - 0, payment at end of period (regular annuity)....

Fields In the pivot table shown, there are four fields in use: Date, Location, Sales, and Filter. Location is a Column field, Date is a Row field, Sales is a Value field, and Filter (the helper column) is a Filter field, as seen below: Formula The formula used in E5, copied down, is: This formula returns TRUE when a record date is greater than or equal to today’s date - 7, and also less than today’s date....

The SEQUENCE function can be used to generate numeric sequences of all kinds. Since Excel times are just numbers, SEQUENCE works well for generating times. In the first worksheet, let’s generate 9 times, one hour apart, starting at 9:00 AM. For start I’ll use 9:00 AM. For times, I use 9. And for step I use 1 hour. In cell E5, I’ll set up the SEQUENCE function to use these values....

Inside a table, structured references work a lot like structured references outside a table, except the table name is not used when it’s implied. To illustrate, let’s look at some examples. The formula to calculate the Total in this table is just Quantity times Price. Notice when I point and click to enter the formula, Excel automatically builds the structured reference. The syntax here means to multiply the value in the current row of the Quantity column by the value in the current row of the Price column....

The result is 6, the sum of the numbers in column C when the text in column B contains both “red” and “blue” in any order. Note that SUMIFS is not case-sensitive. SUMIFS function The SUMIFS function sums cells in a range that meet one or more conditions, referred to as criteria. The syntax for the SUMIFS function depends on the number of conditions needed. Each separate condition will require a range and a criteria....

The result is 61,600, the sum of Amount in column C5:C16 when the Status in D5:D16 is not blank. SUMIFS Function The SUMIFS function sums cells in a range that meet one or more conditions, referred to as criteria. To apply criteria, the SUMIFS function supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching. In this case, we need to test for only one condition, which is that the cells in D5:D16 are not blank....

Pros Simple to create in Excel 2016+ At-a-glance visual display of positive and negative changes Cons Less common chart type not as familiar to many people Built-in waterfall chart type has limited options Author 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....

So, what is an Excel Table? An Excel table is a rectangular range of data that has been defined and named in a particular way. To illustrate, here I have two rectangular ranges of data. Both ranges contain exactly the same data but neither one has been defined as a table. Next, I’ll convert the range on the right to a proper Table. By the way, when I say “proper table”, I mean Excel Table....

To get the rate (which is the period rate) we use the annual rate / periods, or C6/C8. To get the number of periods (nper) we use term * periods, or C7 * C8. There is no periodic payment, so we use zero. By convention, the present value (pv) is input as a negative value, since the $1000 “leaves your wallet” and goes to the bank during the term. The solution goes like this this:...

where keywords is the named range E5:E14, and categories is the named range F5:F14. Note: this is an array formula and must be entered with control + shift + enter. Inside the MATCH function, we use the SEARCH function to search cells in column B for every listed keyword in the named range keywords (E5:E14): Because we are looking for multiple items (in the named range keywords), we’ll get back multiple results like this: The #VALUE!...

In the example shown, the formula in C5 is: This results in a text string like “A1” which is passed into the INDIRECT function. Next, the INDIRECT function transforms the text into a proper Excel reference and hands the result off to the COLUMN function. Finally, the COLUMN function evaluates the reference and returns the column number for the reference. Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....

In the example shown, the formula first divides the time stamp value in B5 by 86400, then adds the date value for the Unix Epoch, January 1, 1970. The formula evaluates like this: When C5 is formatted with the Excel date “d-mmm-yyyy”, the date is displayed as 1-Oct-2018. How Excel tracks dates time The Excel date system starts on January 1, 1900 and counts forward. The table below shows the numeric values associated with a few random dates: Notice the last date includes a time as well....

where n comes from cell F4, which contains 40. The result is 5, since there are 5 cells in B5:B15 that contain more than 40 characters. The formula returns 5 since there are five cells in B5:B15 that contain more than 40 characters. Reference calculation The formula in C5, copied down, is based on the LEN function: This calculation is provided for reference only and is not used by the formula above....

In the example shown, the formula in C4 is: To supply a number to ROUNDUP, we are using this expression: Without a reference, COLUMN generates the column number of the cell it appears in, in this case 3 for cell C4. The number 2 is simply an offset value, to account for the fact column C is column 3. We subtract 2 to normalize back to 1. Cell B4 holds the value that represents the number of times to “repeat” a count....

Using the example above, you can get the average value from the field “Price” for records where the color is “red” and quantity is > 2 with these formulas: Criteria options The criteria can include a variety of expressions, including some wildcards. The table below shows some examples: Note: support for wildcards is a little different wildcards with COUNTIFS, SUMIFS, MATCH etc. For example, the pattern ??? will match strings with 3 exactly characters in these more recent functions, but not in database functions....