Categories

Streamline Data Analysis with Excel Dynamic Arrays Feature

In Excel 365 and Excel 2021 Microsoft has provided Excel users with some new features which will fundamentally change the way worksheets are designed. Dynamic array formulas allow you to work with multiple values at the same time in a formula.

Dynamic arrays solve some challenging difficulties in Excel and will be a feature which will make Excel users capable of building more streamlined Excel models which more effectively can help decision makers.

Dynamic arrays are resizable arrays that calculate automatically and return values into multiple cells based on a formula entered in a single cell.

Dynamic arrays are a new feature in Excel that allows you to work with arrays of data more efficiently and will reduce time spend on updating/changing analysis and Excel reports.

Dynamic array formulas return a set of values into neighbouring cells, also known as an array. This behaviour is called spilling.

Microsoft expands the list of dynamic array formulas frequently but when this blogpost was written the list included the formulas:

ARRAYTOTEXT, BYCOL, BYROW, CHOOSECOLS, CHOOSEROWS, DROP, EXPAND, FILTER, HSTACK, ISOMITTED, LAMBDA, LET, MAKEARRAY, MAP, RANDARRAY, REDUCE, SCAN, SEQUENCE, SORT, SORTBY, STOCKHISTORY, TAKE, TEXTAFTER, TEXTBEFORE, TEXTSPLIT, TOCOL, TOROW, UNIQUE, VALUETOTEXT, VSTACK, WRAPCOLS, WRAPROWS, XLOOKUP, and XMATCH.

Example 1 – SEQUENCE

The SEQUENCE function allows you to generate a list of sequential numbers in an array, such as 1, 2, 3, 4.

=SEQUENCE (rows, [columns], [start], [step])

=SEQUENCE(5) will return this array:

=SEQUENCE(5,5) will return this array:

Start number and incremental steps can be entered as function arguments but in the above examples only number of rows and columns have been entered in the function.

Task: Build a dynamic Excel list which always shows expenses for the last two years starting from last day previous month and two years backward.

If the following is typed in the first cell (done 31/1/2024)

=TEXT(DATE(YEAR(TODAY())-2,SEQUENCE(24,1),1),”mmm-yyyy”)

It will result in:

To make this example easier to understand will it be broken down in a couple of steps:

DATE(YEAR(TODAY())-2,SEQUENCE(24,1),1)

The DATE functions arguments are DATE(YEAR,MONTH,DAY).

In the DATE function’s year argument, the year has been extracted from the TODAY() (current date) minus 2 to go two years backward.

In the DATE function’s month argument, the SEQUENCE function has been told to generate and array with 24 rows and 1 column. In the DATE function’s day argument is just entered 1 to start from the first of the month.

=TEXT(DATE(YEAR(TODAY())-2,SEQUENCE(24,1),1),”mmm-yyyy”)

A TEXT function has been put around to tell Excel to return the date format “mmm-yyyy”.

Example 2 – UNIQUE

The UNIQUE function returns a list of unique values in a list or range.

=UNIQUE (array, [by_col], [exactly_once])

=UNIQUE(G5:G12) will return this array:

=UNIQUE(G5:G12,,TRUE) will return this array (only distinct names):

Example 3 – Best practice designing a worksheet for data analysis by using dynamic array formulas.

Dynamic array formulas can make you able to design Excel worksheets which are fully automated and self-cleaning. No more time spend when the next month data are available. No need to delete old data. No need to update calculations or formulas to include newly added data. The dynamic array formulas can take you to a completely new level of efficiency as an Excel user.

Task: Build a dynamic Excel list which always shows advertising stats, number of sales calls, and sales figures for the last three years starting from last day previous month and three years backward.

This company generates a list with monthly advertising expenses and number of sales calls their sales team has done. The sales records are broken down on day, product, and sales reps.

The Excel analysis should provide the company with information about the correlation between sales and advertising expenses and sales calls.

In this example 3 dynamic array formulas are used.

The TAKE function returns a specified number of contiguous rows or columns from the start or end of an array.

=TAKE(array, rows,[columns])

The SORT function sorts the contents of a range or array.

=SORT(array,[sort_index],[sort_order],[by_col])

The SORTBY function sorts the contents of a range or array based on the values in a corresponding range or array.

=SORTBY(array,by_array,[sort_order],[array/order],…)

Step 1 – get the data from the source to the worksheet.

Both source lists are in tables. The list with advertising and sales calls is in a table named tblMarketing and the sales records in a table named tblSales.

To get the last 36-month dates from the tblMarketing table the TAKE function has been used.

=TAKE(tblMarketing[Date],-36)

The TAKE function has been told to create an array from the last 36 entries from the source table’s date column.

To make sure that it is always the dates from the last 36 month a SORT function has been nested inside the TAKE function.

SORT(tblMarketing[Date],,1)

The last argument in the SORT function is 1 to sort ascending.

All together the functions look like this:

=TAKE(SORT(tblMarketing[Date],,1),-36)

Step 2 is to get the advertising and sales calls from the source to the destination list.

Here the TAKE function has both columns in the array argument tblMarketing[[Advertising]:[Sales Calls]] and the last argument 2 tells the TAKE function to return a two column array and again the last 36 rows.

To take sure to get the last 36 month a SORTBY function is nested. The two columns need to be sorted by the Date column.

To be able to calculate the correlation the sales numbers also need to be brought in. Here is a SUMIFS function used.

Summary

Dynamic Arrays are a huge change to Excel formulas and maybe the biggest change ever. This is a game changer for all Excel users from industries such as finance, healthcare, and retail, well from all industries. This can dramatically reduce time heavy tasks and make Excel users much more efficient. This blog post just scratches the surface of how this impact the way we can work with dynamic arrays in Excel.

How to Use Sparklines in Excel to Visualise Data Trends

Categories

Power BI – Dynamic Text for More Detailed Reports

Visualising data is crucial for decision making in any industry.

Power BI reports are a brilliantly efficient way to achieve this, as users have several useful visual options.

In this blog post you will learn how Power BI can display dynamic text for more detailed reports.

We will be using examples of sales data and HR data, but the logic, methods, and techniques can be used across all different industries, such as finance, healthcare, and retail and across all types of data sets.

DAX

To get the full benefit from this blog post, some experience and knowledge about DAX (Data Analysis eXpressions) is needed. We will use some DAX functions, but you could use a huge number of different DAX functions to create Dynamic text for more detailed reports.

Power BI Report Visuals

You have different options to provide your audience explanations or additional information to your Power BI report’s visuals. You can let Power BI desktop search for key influencers in your data model based on the visual by using the Artificial Intelligence Visual Smart Narrative.

You can write DAX measures and be in fully control of the text displayed in a text box (see the example below and the DAX used to achieve the example text).

Dyn text =

” The line chart above shows Profit for ” & VALUES ( Customers[Country] ) & ” over years by customer segment. The Sales growth for ”

& VALUES ( Customers[Country] ) & ” is ”

& FORMAT ( [Growth], “0.00” ) & “%, and the best selling product in ”

& VALUES ( Customers[Country] ) & ” is ”

& CONCATENATEX (

TOPN ( 1,  values(Items[Product Description]), [Sales],DESC ),

[Product Description],

“;”

) & “. At the moment we have ”

& COUNT ( Customers[Customer Id] ) & ” customers in ”

& VALUES ( Customers[Country] ) & ” and the average order sales is ”

& FORMAT ( DIVIDE ( [Sales], COUNTROWS ( Orders ), 0 ), “currency”, “en-gb” ) & “, and average quantity per order is ”

& FORMAT (

DIVIDE ( SUM ( Line_Items[Quantity] ), COUNTROWS ( orders ), 0 ),

“0,00”

) & ” units.”

The DAX in this example will take to account any filtering on the report page and change the dynamic text based on the filters.

You can use a combination of the two options, and you can also use the AI tool Q&A to generate your dynamic text boxes. Furthermore, you can create Custom Tooltips with dynamic text which will popup when your audience hover over visuals.

In Power BI desktop all dynamic text will be underlined (a blue line) and cannot be changed, but when the report has been published and shared, the audience will not see the blue lines.

Example 1 – Combination of Smart Narrative and your own DAX measures and text.

A line chart visual displaying sales over years and months has been selected and to get Power BI desktop to generate the text you will have to click “Narrative” on the Insert tab in the AI Visuals group.

You will get a text box where Power BI will write AI text based on the data set and selected visual.

You cannot control the AI text the Narrative tool writes, but to add your own text, just click inside the text box and type. The text “The total sales for all months” is entered here. After the added text, the total sales amount is going to be displayed.

When you click inside the text box a tool bar will appear. Click +VALUE and you can type in the DAX measure you want to display the result from. Here I want to display the result from a DAX measure called sales. I have formatted the result as currency, and I named the result “totsales.” The name can be used to display the result somewhere else in the text box.

In the same text box, you may want to explain to the audience, how the visual and the information have been filtered. In this example a slicer has been added to page to filter the data by country.

The following DAX measure will pick up if the column with the heading Country in the table “tblsales” is filtered. If it is, it will return the country name selected from the slicer. Otherwise, it will return the text string ” all countries”.

Filtered countries = if(ISFILTERED(tblsales[Country]),SELECTEDVALUE(tblsales[Country]),”all countries”)

If you look at the screenshot above, you can see that the total sales amount is added to the text box and a new line at the top has been typed:
“This page visuals and the information in this text box display data from”

Place the mouse course where you want to add the dynamic text and click “+Value” and again the options will appear.

Now the text will show this if the page has not been filtered by country:

And this if the page has been filtered by “UK”:

Example 2 – Combination of Q&A artificial intelligence and your own DAX measures and text.

If you do not want the smart narrative dynamic text, you can use the same method as in example 1 above. You will then just need to add a text box to the page.

In this example you will also see how the artificial intelligence tool Q&A can benefit you when you are creating dynamic text.

If we type “The percentage sales margin for” in a text box, then as in example 1 the DAX measure Filtered countries is used to return the country selected from the slicer. Then “is” is typed and then as in example 1, where a measure called Sales was added, another measure is added where the percentage sales margin was calculated. The same has been done to get the result from a profit measure.

At this point, the text box displays:

The percentage sales margin for Canada is 49.19 %,

the profit for Canada is £269,079,481,

and the total quantity for Canada is

You will not even always have to do measures to return calculated results in dynamic text. Here the artificial intelligence tool Q&A can be particularly useful.

To get the total quantity in the text box click “+Value.”

This will look in your data set and find a column with the name quantity and sum all the values in the column.

“top 1 ClientName profit” Q&A will look in the column ClientName and find top 1 client’s value based on a measure called profit.

And the result will look like this:

And this:

Will return the name of top 1 client based on profit.

Example 3 – Dynamic text in custom Tooltips for more detailed reports.

The methods are the same as above! The only difference is that the text box needs to be created on a tooltip page.

The dynamic text is also filtered by the data point you hover over on the visual. In the screen shot above, the mouse courser is on top of the data point for August 2019.

Conclusion

You can add much more information to your reports by creating dynamic text boxes. They enable you to explain the visuals, you can also point out specific details, which are important for the audience.

Filtering can be confusing in a Power BI report for the audience, but as you saw in this blog post, you can explain filters in dynamic text.