Adjustable display units in report

When we create a sales report, sometimes we want to show the values in their original form, and sometimes they can be recalculated values, such as thousands or millions. Such a report is created in a very simple way using the Data Validation option, which allows you to select the desired unit size, and recalculate values with help of the Excel LAMBDA function. Let’s create a report like this with a chart to visualize the data.

Let’s start with a simple table that shows an sales overview by item classification. Above it, we have added a drop-down list from which we select the display unit. Display units are selected based on the range with their names in the auxiliary table.

On the bottom we should add a table in which we’re going to do the recalculation of the value. In cell B14, let’s write the following formula:

=B6/LAMBDA(x,VLOOKUP(x, $D$6:$E$8, 2, FALSE))($B$3)

Let’s copy the formula to the rest of the cells in the table. In the formula, we divide the corresponding value from the table above by the conversion coefficient. It is specified using the LAMBDA function, which searches the auxiliary table based on the selected display unit in the list above.

Now we can hide unnecessary rows. Each time we change the display unit in the drop-down list, we get a different representation of the values in the table.