which returns the ten album titles in the XML. Note: FILTERXML is not available in Excel on the Mac, or in Excel Online. In the example shown cell B5 contains XML data that describes 10 music albums. For each album, there is information about the title, the artist, and the year. To parse this XML, the FILTERXML function is used 3 times in cells D5, E5, and F5 are as follows: In each case, the XPath expression targets a specific element in the XML....
Note if you type "" directly into a cell in Excel, you’ll see the double quote characters. However, when you enter as a formula like this: You won’t see anything, the cell will look blank. Also, if you are new to Excel, note numeric values are not entered in quotes. In other words: Wrapping a number in quotes (“1”) causes Excel to interpret the value as text, which will cause logical tests to fail....
Excel contains over 400 built-in functions, and a number of related shortcuts. To start off, there’s a shortcut for the Autosum feature, which automatically enters the SUM function. Select a range that includes a blank cell, then use Alt + = on Windows, Command + Shift + T on a Mac. You can use this for both rows and columns. You can even Autosum an entire table. When you’re entering a function, you can use autocomplete....
As you might already know, Paste special is a gateway to many powerful operations in Excel. To use Paste Special, just copy normally, then use the shortcut Ctrl + Alt + V in Windows, Ctrl + Command + V on the Mac. Using this shortcut doesn’t actually finish the Paste, it simply displays the Paste Special dialog, where you can choose which options you want. In Windows, you can type a letter to select options....
where dept (B5:B16) and group (C5:C16) are named ranges. The explanation below also includes a solution where source data is in an Excel Table. COUNTIFS function The COUNTIFS function is designed to count things based on more than one condition. Conditions are supplied to COUNTIFS in range/criteria “pairs” like this: In this problem, the first step is to create the shell of the summary table that contains one set of criteria in the left-most column, and the second set of criteria in the top row as column headers like this:...
where value (C5:C15), name (B5:B15), and stage (D5:D15) are named ranges. The result is a table that shows summary totals for each name by stage. In the example shown, the data in the range B5:E15 shows a sales pipeline where each row is an opportunity owned by a salesperson, at a specific stage. The formula in H5 is: The first part of the formula sums opportunities by salesperson: Sum range is the named range values Criteria range 1 is the named range name Criteria 1 comes from cell G5...
where values (C4:C15) and dates (B4:B15) are named ranges. MAX is nested inside XLOOKUP, and returns a value directly as the first argument: The lookup_value is delivered by MAX The lookup_array is the named range values (C4:C15) The return_array is is the named range dates (B4:B15) The match_mode is not provided and defaults to 0 (exact match) The search_mode is not provided and defaults to 1 (first to last)...
So, what can you see with this chart? Primarily, you can see that sales of Retrobags are collapsing while Organic Flannel makes up the slack. Hammocks and Banjo Packs are relatively stable. But what you can’t see is if overall sales are increasing (they are) or decreasing. Have you got a good example of a useful 100% stacked area chart? Leave a comment below if so, I’d like to add more good examples....
The data used to plot the chart looks like this: How to create this chart 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.
Excel has gridlines in each sheet or your workbook. These are the light gray lines that outline each cell in the sheet. Gridlines can help you distinguish between the rows, columns, and any data they contain. But maybe you don’t want to see the gridlines in your final report or dashboard. In this case, you will want to hide these gridlines. Removing the gridlines is very easy and it can be done in many ways....
Let’s take a look. Here we have a basic column chart. The chart itself—the rectangle that holds everything else—is referred to as the “chart area.” When you select a chart, Excel will wrap the chart in a frame that contains eight handles. You can use these handles to resize the chart. At the top of the chart is the chart title, and to the left is the legend. A column chart has two axis: the horizontal axis, which is also referred to as the “category axis,” and the vertical axis....
One of the trainers was a 40 something guy with awesome Excel skills. I used to call him the Excel Superman. I still remember the first thing that he told us was to avoid the use of the mouse as much as possible and use the keyboard instead. Till date, I completely agree with him. If you want to be super fast in Excel, start using the keyboard. BUT… Don’t dump the Mouse....
For this example, we want to find the payment for a $5000 loan with a 4.5% interest rate, and a term of 60 months. To do this, we configure the PMT function as follows: rate - The interest rate per period. We divide the value in C6 by 12 since 4.5% represents annual interest, and we need the periodic interest. nper - the number of periods comes from cell C7; 60 monthly periods for a 5 year loan....
where pv (G4), rate (G5), and term (G6) are named ranges. Present value (pv) is the named range G4 Rate is provided as as annual rate/periods, or rate/C5 Number of periods (nper) is given as periods * term, or C5*term There is no periodic payment, so we use zero (0) 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....
As the name suggests, you can use conditional formatting in Excel when you want to highlight cells that meet a specified condition. It gives you the ability to quickly add a visual analysis layer over your data set. You can create heat maps, show increasing/decreasing icons, Harvey bubbles, and a lot more using conditional formatting in Excel. Using Conditional Formatting in Excel (Examples) In this tutorial, I’ll show you seven amazing examples of using conditional formatting in Excel:...
Because B6 contains 60 (representing 360 minutes) the result is 60/1440 = 0.04167, since 60 minutes = 1 hour = 1/24 day. Cell D6 shows the same result formatted as time, which displays 1:00. This means if you have a decimal number for minutes, you can divide by 1440 (24 x 60) to get the correct representation of minutes in Excel. After dividing by 1440, you can apply a time format of your choice, or use the result in a math operation with other dates or times....
where table is an Excel Table holding call times as shown. The data is in an Excel table called table. By creating a proper Excel table, we make the formulas easier to read and write. In addition, any new data that is added to the table will be automatically picked up by the formulas in columns H and I. The summary table on the right is constructed by entering Excel times in the Start and End columns....
where data is the named range B5:B15. The result is 4 since there are four odd numbers in the range B5:B15. SUMPRODUCT with ISODD The SUMPRODUCT function works directly with arrays. One thing you can do quite easily with SUMPRODUCT is perform a logical test on a range, then count the results. In this case, we want to count odd numbers, and the simplest way to do that is with the ISODD function....
where data is an Excel Table in the range B5:D16. As the formula is copied down, it returns a count of non-blank dates by Group as seen in the worksheet. COUNTIFS function The Excel COUNTIFS function returns the count of cells that meet one or more criteria. COUNTIFS accepts ranges and criteria in pairs. For example, to count cells in A1:A10 that are equal to “red”, you would use COUNTIFS like this: To count cells in the range A1:A10 that have a corresponding value in B1:B10 that is greater than 5, you would add another range/criteria pair like this: Note: COUNTIFS is in a group of 8 functions that share a quirky syntax....
which returns 3, since three different people worked on on project Omega. Note: this is an array formula and must be entered with control + shift + enter. The result from MATCH is an array like this: Because MATCH always returns the position of the first match, values that appear more than once in the data return the same position. For example, because “Jim” appears 4 times in the list, he shows up in this array 4 times as the number 1....
