Posts

Each subsequent array is appended to the bottom of the previous array. The result from VSTACK is asinglearray thatspillsonto the worksheet into multiple cells. VSTACK works equally well forrangeson a worksheet or in-memoryarrayscreated by a formula. The output from VSTACK is fully dynamic. If data in the given arrays changes, the result from VSTACK will immediately update. Use VSTACK to combine rangesverticallyandHSTACKto combine rangeshorizontally. Basic usage VSTACK stacks ranges or arrays vertically....

The Waterfall Chart is a built-in chart pop in in Excel 2016+. Waterfall charts are used to plot the cumulative result of values as a visual running total. Waterfall charts are sometimes called bridge charts.

Just select some data then go to Insert > Recommended Charts. Excel will pop swing open the Insert Chart window and list recommended charts, each with a preview. Excel’s recommended charts are meant to fit the data. They’re based on professionally designed templates, and generally they look pretty good. Notice when you use Recommended charts, the window has two tabs. The first tab is “Recommended charts” and the second tab is “All charts”....

So let’s go over some ways to exclude this data. First, it’s possible for you to always exclude data when the chart is created. And, I can extend this approach to exclude the quarterly totals as well. I just need to hold down the control key and make separate selections. So, selecting specific data first is a handy way to plot just the data you want. Let me undo these changes, and let’s look at another way to filter the data....

The WEBSERVICE function is only available in Excel 2013 and later for Windows. A web service uses a protocol like HTTP to retrieve data in a machine-readable format like XML or JSON. In the example above, WEBSERVICE is used to fetch breaking news from NASA. In the example shown, this is how the data and title of each article is extracted. ENCODEURL Function The Excel ENCODEURL function returns a URL-encoded string composed of US-ASCII characters....

Note: the WEEKDAY function will return a value even when the date is empty. Take care to trap this result if blank dates are possible.

Typically the last week number in a year is 53. However, WEEKNUM will return 54 at the end of some years, like 2000 and 2028. The WEEKNUM function accepts twoarguments,serial_numandreturn_type. Theserial_numargument must have avalid Excel date. Whenreturn_typeis set to 2, week numbers begin on Monday. Withreturn_type21, week 1 is the week containing the first Thursday of the year, followingISO 8601. The table below summarizesreturn_typeoptions. By default,return_typeis 1, and numbers increment on Sunday....

In other words, some values have more “weight”. In a nutshell, SUMPRODUCT multipliesrangesorarraystogether and returns the sum of products. Seethis pagefor an overview. See below for an example where weights do not add up to 1. However, the scores in C5:E5, which arerelative reference, change with each new row. The result is a weighted average for each name in the list as shown. Transposing weights The SUMPRODUCT function requires that array dimensions be compatible to run correctly....

Let me take you through some of the material covered in the course. Core Excel begins with the basics. We also look at Excel’s powerful find and replace tools. Then we get you started with formulas and cell references. Next, we enter the world of Excel formatting. Then we get to something really fun - conditional formatting. Conditional formatting is simply an awesome way to quickly visually data. Next is managing data....

Dynamic Array formulas are the biggest change to the Excel formula engine in years. Maybe the biggest change ever. This is because Dynamic Arrays make it easy to work with many values at the same time. For many users, this will be the first time they understand and use array formulas. Let’s look at a basic example. Here we have the shell of a multiplication table. What formula can we use to fill in the data?...

So, what are pivot tables good for? In the last video, we looked at the question Why Pivot Tables? and I focused on their speed. I showed you how a pivot table will beat even an advanced user every time. If you missed that one,you should have a look. Have a look at the video, then share your thoughts or questions about pivot tables below. This is the 2nd video in this series on Pivot Tables....

Abstract Transcript The key to formulas in Excel is cell references. Cell references can refer to one cell, a range of cells, or even entire columns and rows. Let’s take a look. Every cell in an Excel worksheet has a unique address. The address of each cell is defined by its location on the grid. The address “B7” refers to the cell in the seventh row of column B. To check the address of any cell, just watch the Name Box as you select different cells....

Let’s take a look. In this first worksheet, we have a list of ten properties set up in a normal way. So, what happens if I add some more properties to this list? Let’s try it out and see. On the data worksheet, I have data for another six properties. I’ll copy this data and paste it below. As it’s possible for you to see, none of the formulas have calculated new results....