A web service uses a protocol like HTTP to retrieve data in a machine-readable format like XML or JSON. For example, a formula that uses WEBSERVICE to call a fictitious web service hosted at somewebservice.com might look something like this: The result from the WEBSERVICE function is returned directly to the worksheet. In cases where the result from a webservice is in XML format, you can use the FILTERXML function to parse the XML....
where data is an Excel Table in the range B5:C15. Problem The formula below returns a #CALC! error because there are no rows in the data with dates between April 1 and April 30: This happens because when FILTER returns no data, it returns a #CALC error by default and this error “bubbles up” to SORT. You might try to handle this problem by providing a value for the if_empty argument in FILTER like this: However, this causes the SORT function to return a #VALUE!...
The replacement is done with SUBSTITUTE and REPT here: Then, the RIGHT function extracts the last 100 characters of the string returned by SUBSTITUTE. Finally, TRIM removes all leading and trailing space characters and returns the result. Note: the use of 100 in this formula is arbitrary, and represents the maximum number of characters to be found after the final forward slash (/) in the original text. Adjust to suit your use case....
Let’s take a look. This pivot table shows sales data by Product. If we check the source data, we see that we have columns for Quantity and Total sales. However, the data does not contain a unit price. We could add a new column to the source data that calculates unit price by dividing Total Sales by Quantity, but we can also add a unit price as a calculated field....
Let’s take a look. For most basic underlining, you can just use the Underline button on the ribbon. The Underline button toggles underlining on and off. It also provides a menu for selecting single and double-underlining. The Underline button remembers your last selection. For example, if you apply a Double Underline, the button will keep that selection until you select Single Underline again. Excel actually provides two more underline options for use with the Accounting number format....
Stacked area charts make sense when you want to show changes in a part-to-whole relationship over time. For example, here we have sales data for an eclectic website with 4 product lines: banjos, hammocks, cycling bags, and organic flannel. In this case, the four product lines have a part-to-whole relationship - together, they represent total sales. Let’s plot this data in a stacked area chart. Since we have totals in the table, I’ll exclude those from the selection....
Here we have data that represents one month of sales for a camping store. You can see the store sells tents, backpacks, headlamps, and so on. In fact, after I summarize the data with a pivot table, I can get an exact list of items sold by adding the Item field to the Rows area. And, when I add Amount as a value field, I’ll get a breakdown of total sales by item....
Let’s take a look. Here we have a list of operators, their names, and a sample formula for each. Let’s take a quick run through the list and enter the formulas shown in column E. Addition and subtraction use standard plus and minus symbols. So, G6 + I7 is 28, and G6 - I7 equals 14. Notice I can type the cell references directly, or I can point and click to add the address....
Welcome to our course on Excel shortcuts. You’ve made a great decision to learn more shortcuts. Excel shortcuts are extremely powerful and they can really upgrade your skills in Excel. In fact, I’ve got 6 good reasons that Excel shortcuts they’re totally worth your time: Shortcuts allow you to work faster and more accurately in Excel Shortcuts really save you sanity you have to do a lot of tedious, repetitive work Shortcuts allow you to focus on the work, and not on Excel’s complicated interface....
Remove deleted items from a pivot table In Excel 2019, and Excel 365, you can remove deleted items by changing a pivot table setting: Set option for all new pivot tables To change this setting for all new pivot tables: Older Excel versions Older versions of Excel don’t have the setting shown above. To remove deleted items, you’ll need to use a macro. Debra Dalgleish has a detailed explanation here. Author...
The recommended charts button was added in Excel 2013. It provides access to more than 150 professionally design charts and, in general, it’s a good way to create new charts in later versions of Excel. The charts are good looking and in most cases they fit the data pretty well. To create a new chart in Excel with recommended charts, just select the data then the Recommended Charts button on the Insert tab of the ribbon....
The result is 5,775, the sum of the first six values in the range C5:C16. Note: The TAKE function is new in Excel. See below for aj OFFSET formula that will work in older versions of Excel. TAKE function The TAKE function returns a subset of a given array. The number of rows and columns to return is provided by separate rows and columns arguments. For example, you can use TAKE to return the first 3 rows or columns of an array like this: In this problem, the values we want to sum are in the range C5:C16, and the number of rows to return is a variable entered in cell E5....
Where range1 (C5:C16), range2 (D5:D15), and range3 (F5:F13) are named ranges. UNIQUE function The UNIQUE function makes it very easy to extract unique values from a range. Just give UNIQUE a range, and it will give you back the unique values: Like other dynamic array formulas, the results from UNIQUE will spill onto the worksheet into multiple cells. The challenge in this example is to provide more than one range to UNIQUE at the same time....
where “include” is the named range E5:E9, and “exclude” is the named range G5:G6. In the right SUMPRODUCT, SEARCH looks for all strings in the named range “exclude”. In both parts of the formula, SEARCH returns numeric positions when strings are found, and errors when not. The ISNUMBER function converts the numbers to TRUE and errors to FALSE, and the double negative converts the TRUE FALSE values to 1 and 0....
This formula extract the year, month, and day values separately, and uses the DATE function to assemble them into the date October 24, 2000. Background When you’re working with data from another system, you might run into a situation where dates are not properly recognized by Excel, which instead treats the dates like text. For example, you might have text values like this: When Excel has evaluated a date value as text, one option is to use a formula to parse the text into its components (year, month, day) and use these to make a date with the DATE function....
The result is the monthly exchange rate for the USD > EUR currency pair shown in cells F5 and F6. Note that STOCKHISTORY returns an array of values that spill onto the worksheet into multiple cells. The STOCKHISTORY is only available in Excel 365. Overview Although the name suggests otherwise, the STOCKHISTORY function can work with a variety of financial instruments, including bonds, index funds, mutual funds, bonds, and currency pairs....
To allow only values from a list in a cell, you can use data validation with a custom formula based on the COUNTIF function. In the example shown, the data validation applied to C5:C9 is: In this case, the COUNTIF function is part of an expression that returns TRUE when a value exists in a specified range or list, and FALSE if not. The COUNTIF function simply counts occurrences of the value in the list....
For example, if cell A1 contains the text: The Graduate and you want wrap that text inside double quotes (""), you can use this formula: Because the text on either side of A1 consists of only of a double quote, you need """" . The outer quotes (1 & 4) tell Excel this is text, the 2nd quote tells Excel to escape the next character, and the 3rd quote is displayed....
First, to recap, the rules that govern the errors that Excel flags are located at options > formulas > error checking rules Each rule can be disabled individually or, you can turn off background error checking completely. If you turn off background error checking, you’re disabling all the rules that visually flag information on the worksheet in the background. With background error checking disabled, you can still ask Excel to check for errors by clicking the “Check for Errors” button on the Formulas tab of the ribbon....
Note: BYROW is a beta function available only through the Insiders channel of Excel 365. The BYROW function takes two arguments: array and lambda. Array is the array or range to process. Lambda is the LAMBDA function that should be run on each row in array. The LAMBDA function must return a single result for each column, or BYROW will return a #CALC! error. Examples To sum each row in a range, you can use the BYROW function like this: The BYROW function delivers the contents of range one row at a time to the LAMBDA function, which uses the SUM function to calculate a total for each row....
