Office 365 Archives - Peltier Tech

Office 365 keeps bringing us new stuff

Last week, I wrote my first blog post in a few months. I wrote about Watching my Weight with SPC (Statistical Process Control).

In the first comment, Derek asked for a box plot, so I quickly recompiled my data and built one.

Several new features of Office 365 made it very easy to mush my data around, and I realized that this is a good example to show them in action. I used Dynamic Arrays, the XLOOKUP function, and the new LET function.

Dynamic Arrays

Joe McDaid of Microsoft’s Excel team wrote about Dynamic Arrays in Dynamic Arrays and New Functions in Excel! These formulas break the old paradigm of one formula per cell. In classic Excel, you needed a formula for every value you calculated. Sure, we had array formulas, but you had to include all output cells together and enter an array formula in all of the cells at once. If you didn’t use the right number of formulas, or if your model expanded, you had to rebuild the formula. Ugh! Plus array formulas, amirite?

The new Dynamic Arrays allow you to enter a formula into one cell. Excel figures out how many cells you need, and “spills” the formula into the required range of cells. One formula, multiple cells. And with a retooling of Excel’s underlying grid, any formula can become a Dynamic Array.

In Preview of Dynamic Arrays in Excel, Joe tells us that Dynamic Arrays have rolled out to Office 365 subscribers, with users in the Semi-Annual (Targeted) channel getting them starting last month. I presume that users in the Semi-Annual channel getting them soon as well.

I used a few Dynamic Array Formulas in my example.

XLOOKUP

For years there has been a debate about which is better, VLOOKUP or INDEX/MATCH. I don’t know why there was a debate, because INDEX/MATCH is clearly the superior choice. Like Zoolander, VLOOKUP can’t turn left, and there were many things that could go wrong with your lookups. INDEX/MATCH has its own problems as well, mostly because of the default Match_Type setting which could lead to bad results.

In Announcing XLOOKUP, again by Joe McDaid, we learned of this new lookup function that eliminates the perils and shortcomings of VLOOKUP. It’s simpler to use and more flexible, and best of all, it begins with XL. You can read documentation in XLOOKUP Function. XLOOKUP is on about the same release schedule as Dynamic Arrays.

LET

According to Announcing LET, the new function allows you to assign a name to a value or intermediate calculation at the beginning of the formula, then use this named argument in the main calculation. The LET function improves performance by computing an intermediate calculation just once and allowing reuse of the computed value, and it helps readability of your formulas by allowing you to use descriptive labels for these intermediate calculations.

LET is only available to Office Insiders at this writing, so you’ll have to be patient.

Recompiling the Weight Data

The Data

In my previous post, I examined fluctuations in my body weight between last September and this April. The data is in a two-column Table. I will construct a table in the worksheet, starting in cell D2, which has month and year as column headers and day numbers in the first column of each row. This will produce a grid of mostly weights with some missing values, and it feeds directly into Peltier Tech Charts for Excel, the VBA add-in I use to make box plots.

Set up the Grid Headers with Dynamic Arrays

I need to show the months from September through April across the top. I will use the new SEQUENCE function to make a list. In cell E2, I enter the formula

=SEQUENCE(1,8,9,1)

Which tells Excel to make a sequence with 1 row, 8 columns, start the sequence at 9, and increment by 1. And you see the Dynamic Array in E2:L2 filled with this sequence. If the active cell is within this range, the entire Dynamic Array range is highlighted as shown.

Dynamic Arrays: Sequence to populate to row of chart source data

But Jon, you may say, you need the months to go up to 12, then start again at 1. And the answer is, no I don’t. A few rows below the first Dynamic Array, I have written a temporary formula to check the dates:

=DATE(2019,E2#,1)

Which tells Excel to give me a date using 2019 as the year, E2# as the month, and 1 as the day. The hash or pound sign appended to E2# tells Excel to use the Entire Dynamic Array that is anchored in cell E2. So I’ve entered this formula in cell E6 only, but it spills into the range E6:L6 to accommodate E2#.

And we see that if I define a date using the year 2019 and the month 13, it uses 12 months to increment the year to 2020, and uses the leftover month, so I get January 2020. Well, you probably new that, but it’s a nice little trick.

In row 9 I repeat the previous formula, but with 31 days. Excel does the same thing with excess days, and converts September 31 to October 1, etc.

So Dynamic Arrays, pretty cool, right?

Now to get the dates I want, I’ll wrapt SEQUENCE in the DATE function:

=DATE(2019,SEQUENCE(1,8,9,1),1)

and Excel gives me the first of each month in the study.

Now let’s fill in the column of days. I’ll use another SEQUENCE function in cell D3, an easier one this time:

=SEQUENCE(31,1,1,1)

Which is a sequence of 31 rows and 1 column, starting with 1 and incrementing by 1.

Dynamic Arrays: Day numbers in first column

Populate the Grid with Dynamic Array Dates

In cell E3, I entered a formula to calculate the dates for my lookups.

=DATE(YEAR(E2#),MONTH(E2#),D3#)

Meaning, give me the data using the year and month from the date E2# and the day from D3#. This new Dynamic Array fills up the whole rectangular range defined by the two Dynamic Arrays it references. Note the extra dates in the September, November, February, and April columns.

Dynamic Arrays: Grid full of dates for lookup

Make Dynamic Arrays Perform the XLOOKUP

The dates are fine (except for those extras, which we’ll take care of soon). So let’s wrap the date in XLOOKUP.

=XLOOKUP(DATE(YEAR(E2#),MONTH(E2#),D3#),
Table6[Date],
Table6[Weight],
"")

I’ve put each argument onto its own line for clarity. Basically, I’m telling Excel to look for the date we’ve calculated, look in the Date column of Table 6, and if you find it, return the value from the Weight column of Table 6. And if you don’t find it, give me a cell that looks blank with “”.

And here are the weights, including some duplications: note that the 10/1/2019 weight of 167 appears at the top of the October column but also at the bottom of the September column.

Remove Duplicate Weights

So how do I eliminate those duplicates? Even though Excel computes DATE(2019,9,31) using month 9, the result is month 10. So I’ll enter a formula that checks whether the month calculated in the lookup cell matches the month in the header cell: if so, perform the XLOOKUP, but if not, return “”.

=IF(MONTH(DATE(YEAR(E2#),MONTH(E2#),D3#))=MONTH(E2#),
XLOOKUP(DATE(YEAR(E2#),MONTH(E2#),D3#),
Table7[Date],Table7[Weight],""),"")

Clever trick to skip those duplicates, Jon, but that’s an unwieldy formula. That big chunk MONTH(DATE(YEAR(E2#),MONTH(E2#),D3#) appears twice, and we’ve all written monstrosities which has more than one chunk like this repeated more than twice.

LET’s Try Out LET

So I’m going to take the new function LET out for a spin. I’ll create an argument called TheDate, and it will stand for MONTH(DATE(YEAR(E2#),MONTH(E2#),D3#). Then in the calculation, I’ll use TheDate where the big chunk was before.

=LET(TheDate,DATE(YEAR(E2#),MONTH(E2#),D3#),
IF(MONTH(TheDate)=MONTH(E2#),
XLOOKUP(TheDate,Table8[Date],Table8[Weight],""),""))

This formula is only a few characters shorter than the original, but it’s certainly a lot easier to read. And if I had to change something in the definition of TheDate, I would only have to change it in one place, and not worry that I forgot to change it somewhere else.

LET. XLOOKUP, and Dynamic Arrays in action

Now all I have to do is select the data and insert my box plot.

Peltier Tech Charts for Excel Box Plot Dialog

More About Dynamic Arrays, LET, and LAMBDA

Power Query

Of course, Excel often gives you many ways to accomplish the same objective. You can use Power Query to manipulate this data in the same way, as I show in a follow-up article, Use Power Query to Manipulate Your Data.

Microsoft Excel has introduced new features that help you work with geographical data. Excel has new “Rich Data” types, including Geography and Stocks, and a new chart type called Filled Maps. The rich data types were introduced since Excel 2016, and are not included in Excel 2016 or 2019, only in Office 365; the filled maps are not available in Excel 2016 but are included in Excel 2019 and in Office 365. To introduce you to these powerful rich data types and filled maps, I’ll run through a quick example.

States Data

I’ll start with a simple list of the 50 United States.

Next I’ll convert this list into a Table. select a cell in the list and click Ctrl+T, and make sure you check My Table Has Headers.

Geography Rich Data Type

On the Data tab of Excel’s ribbon, you’ll see a group labeled Data Types. It currently includes Stocks and Geography, but there’s room for expansion.

Select the column of state names, and click on Geography in the Data Types group. Excel thinks for a minute, then inserts an icon in front of each state name. The icon looks like a representation of an old-style foldable paper map.

Table of Geographic Data Elements of the fifty United States

In fact, Excel phones home to get help determining which geographical entities are selected, so you need an internet connection to use this feature.

What’s neat is that, if you start typing some recognizable state names in a sheet, after you’ve entered about four names, Excel realizes you’re entering geographical labels, and offers to Convert to Geography, if you click on the little pop-up.

Excel recognizes when you're typing geographical names.

What Makes These Data Types “Rich”?

These Geography data types contain more information than just the name of a region and a cute little folded up map icon.

The first hint of all of this information appears when you select a cell or range containing Geography entities. A small icon pops up next to the selection.

If you mouse over this icon, it identifies itself, and offers to extract a field (of Geography data) to a cell or table column.

Click on the icon to see a list of available fields.

List of Rich Data Fields for the Geography Data Type

If you ignore the icon and start typing a new column header, Excel will again show you the list of available fields.

Click on a field name, and the table column populates itself. Pun not included (well, maybe not).

Wasn’t that a lot easier than looking up the data on Wikipedia and doing the ugly copy-and-paste (or the slightly less ugly Power Query operation) to create the table?

The rich data formula syntax is interesting, and maybe not unusual to those who have done a bit of VBA or other programming. You can reference a field by typing the Geography reference (cell address) followed by a dot.

You can see this dot notation in the formula for the population shown in cell C2:

All cells in the population column of the table use this notation in their formulas.

What Was That About Maps?

First we can use the Geography data type to look up information. Next we can visualize the data using a filled map.

I’ve include more details in my table. In addition to Population, I have added the field Area, which gives me the state’s area in square kilometers. Then I inserted a column for Population Density.

The formula in cell C1 is

Population, Area, and Population Density for the fifty United States

=[@State].Population

the formula in cell D1 is

=[@State].Area

and the formula in cell B1 is

=[@Population]/([@Area]/2.58999)

I selected the first two columns of the table, including the header row, and went hunting on the ribbon for something relevant.

On the Insert tab of Excel’s ribbon, in the group labeled Charts, you can find a control labeled Maps. Mouse over this control, and Excel offers to insert a “Map Chart”, which is a funny name.

Click on the control’s down arrow, and Excel offers to insert a more aptly named “Filled Map”.

Filled Map sounds interesting, so let’s click it and see what happens.

Not bad, and pretty easy. These filled maps are pretty flexible. For example, I’ve filtered out most of the states below to focus on the northeastern quadrant of the country, and I’ve changed from a blue color gradient to purple.

Population Density of a Portion of the United States

You can map anything that is fillable, countries, states or provinces, counties, even regions covered by zip codes. It’s important to remember that the maps are powered by Bing Maps, so you need an internet connection in order to render them.

Further Steps

Geography and Stocks Rich Data Types are powerful ways to acquire data for analyses and reports. Filled Maps are a great way to enhance a dashboard, with an eye-catching map showing performance across regions.

Excel makes it easy to use these new features, with some amusing caveats. Sometimes, for example, Excel has trouble disambiguating similar abbreviations (e.g., the abbreviations for Washington State and Western Australia are the same, WA). So find a little data, and dig in.

Office 365

Dynamic Arrays, XLOOKUP, LET – New Excel Features