With the newest release of Excel in Office 365, Microsoft has released a new functionality called
dynamic arrays. Along with that they have released a few new functions that use these dynamic arrays.
Previously when using functions you would use one formula for each value you wanted, if you wanted
another value you would write or copy that formula over. With dynamic arrays that's different.
Now you can write one formula and hit enter and get an array of values
returned and it'll automatically resize as the amount of results changes. Let's
take a look at these formulas. If you have office 365 feel free to follow
along. The first new function is sort. We're going to sort these names here.
As you can see the formula spilled into the rows below. The next function is unique.
It'll show us all the unique entries in a list. Let's try it out here. You'll notice
that instead of returning the values it returned an error called spill. This is
because we have a period entered in one of the cells where the results are supposed to
be spilling into. Let's see what happens when I delete it. As you can see all the
results are automatically returned as soon as the period is deleted .
The next function is filter, so you can look at a table of values and filter ad
by a certain criteria. Here we're going to look at only the people who chose vegetarian.
The next function we'll look at is Xlookups this is a new version of vlookup. Similar to filter
we can find all the data that matches a certain criteria. Now since these functions are very new
they're not compatible with all versions of Excel. We can see that when we check for compatibility
here. So keep that in mind when making use of them but if your whole organization has
upgraded to the newest Office 365 you can make use of these to do some really powerful things.
Let's go back to our previous Excel spreadsheets and see what we can do
to make use of these functions to enhance the workbooks we already have. If we go to
the production summary sheet in the DataLink exercises workbook you can add a table called
lowest production days and filter our results by a certain criteria. Here you can see I've
made use of both the sort and filter functions and we're filtering out all of the weeks in
which the production total was lower than a certain criteria. Let's say we have a bunch
of compressed data from cdm158 as you can see this is the digital state tag,
you can use the unique function to look at all the unique digital states that occurred during
this time. Let's look at another Excel feature that can also be used to enhance the production
summary sheet that we've already created. These are called sparklines. Let's say we don't have
room for another big chart but we want to be able to see an overview of which are the highest and
lowest production weeks. I'm going to copy the format and extend the table here and then I'll
add a sparkline. I'll choose the production total as my data range and hit OK and now we have a
mini sparkline graph. You can extend that over and have mini graphs for average and maximum as well.
With both sparklines and our new Excel functions in Office 365 we
can further enhance our DataLink reports and really get the most out of our PI data.