Table of Contents
1. Track
Cumulative Sales for Each Region
2. Calculate Total Cost for Each Product Automatically
3. Build Dynamic KPI Calculations with Stored Variables
4. Find Average Ratings for Each Product Row
5. Combine Regional Profits into a Single Total
6. Retrieve Information Dynamically from a Data Table
7. Count Unique Employee Names from Multiple Departments
8. Combine Specific Regional Data into a Single List
9. Display the Top 3 Performers Automatically
10. Create a Region-Wise Product Summary in a Single Cell
1.
Track Cumulative Sales for Each
Region
One of the most powerful Excel capabilities is dynamically
calculating cumulative totals. In this example, we use a dataset showing sales
from different regions such as East and West. By using a dynamic formula, we
can calculate running totals that automatically update as new data is added.
Formula: =SCAN(0, FILTER(B2:B6, A2:A6="East"), LAMBDA(a,b, a+b))
This formula filters only the sales for the East region and then applies the SCAN function to calculate the cumulative total step by step. Each new value adds to the previous one, creating a running total. It’s an excellent way to track progressive performance by region or time period.
2.
Calculate Total Cost for Each
Product Automatically
In a business scenario, calculating total costs per product can
save time when you’re dealing with multiple items. Using this method, you can
automatically calculate Quantity × Price without manually creating new columns.
Formula: =MAP(B2:B4, C2:C4, LAMBDA(q,p, q*p))
Here, the MAP function goes through each pair of Quantity and Price, while the LAMBDA function multiplies them together. The result is a dynamic list of total costs for each product. You can extend this formula to handle thousands of rows, making it perfect for inventory or billing systems.
3.
Build Dynamic KPI Calculations
with Stored Variables
When working with performance metrics, you often calculate key
indicators like Profit by subtracting Cost from Sales. Using the LET function
allows you to store values in memory, making formulas more efficient and
readable.
Formula: =LET(sales, XLOOKUP("Sales", A2:A3, B2:B3), cost,
XLOOKUP("Cost", A2:A3, B2:B3), sales - cost)
This formula first locates the Sales and Cost values using XLOOKUP. It then assigns them to variables called “sales” and “cost.” Finally, it calculates profit by subtracting cost from sales. The beauty of the LET function is that you can reuse variables multiple times without repeating complex formulas.
4.
Find Average Ratings for Each
Product Row
If you’re analyzing performance scores, survey results, or product
ratings across multiple quarters, this approach helps summarize data
efficiently. Instead of writing separate formulas for each row, you can
calculate averages dynamically.
Formula: =BYROW(B2:E4, LAMBDA(r, AVERAGE(r)))
The BYROW function applies the LAMBDA formula to each row. For every product, it calculates the average of quarterly ratings. This method is dynamic, meaning if you add new columns or data, the results automatically adjust.
5.
Combine Regional Profits into a
Single Total
Companies with multiple branches often need to find the total
profit across all regions. The REDUCE function makes this process much cleaner
by iterating through a range and combining the results step by step.
Formula: =REDUCE(0, B2:B4, LAMBDA(a,b, a+b))
The REDUCE function starts with an initial value of zero and adds each region’s profit one by one. It is a powerful and elegant way to perform cumulative operations like total profit, total expenses, or total revenue.
6.
Retrieve Information Dynamically
from a Data Table
Sometimes, data is stored in a cross-tab format, and you need to
extract specific information—such as profit for a certain region—without
manually searching. This can be achieved using a combination of XMATCH and INDEX.
Formula: =INDEX(B2:D3, XMATCH("Profit", A2:A3), XMATCH("West",
B1:D1))
Here, XMATCH locates the position of “Profit” in the metric list and “West” in the column headers. The INDEX function then returns the value from that intersection. This combination is extremely useful for creating dynamic dashboards and summary reports.
7.
Count Unique Employee Names from
Multiple Departments
When managing staff data, you might have employee names repeated
across departments. To get an accurate count of unique employees, you can use a
combination of the TOCOL, UNIQUE, and COUNTA functions.
Formula: =COUNTA(UNIQUE(TOCOL(A2:C4)))
The TOCOL function converts the entire table into a single column, UNIQUE removes duplicate names, and COUNTA counts the remaining unique entries. This method is perfect for HR or project management sheets to prevent double counting of employees.
8.
Combine Specific Regional Data
into a Single List
If you need to view sales from selected regions like North and East
together, Excel’s new array functions can merge filtered data seamlessly. You
no longer need to manually copy and paste values from multiple sheets.
Formula: =VSTACK(FILTER(A2:B5, A2:A5="North"), FILTER(A2:B5,
A2:A5="East"))
The FILTER function extracts rows for North and East separately. The VSTACK function then stacks both sets into one list. It’s ideal for combining regional, departmental, or product-based data dynamically without manual effort.
9.
Display the Top 3 Performers
Automatically
Ranking top performers, stores, or employees is common in business
reports. Instead of sorting data manually, you can use Excel’s SORTBY and TAKE
functions to automatically display the top results.
Formula:=TAKE(SORTBY(A2:B5, B2:B5, -1), 3)
This formula sorts the data based on revenue in descending order (indicated by “-1”) and then takes the top three records. It’s a smart and automated way to build leaderboards or monthly performance highlights in dashboards.
10. Create a
Region-Wise Product Summary in a Single Cell
Sometimes, you may want to combine all product names belonging to a
specific region into one summarized list. This technique helps create clean and
compact reports for quick insights.
Formula: =TEXTJOIN(", ", TRUE, FILTER(A2:A5, B2:B5="East"))
This formula filters all products belonging to the East region and then joins them together using commas. The result is a neatly formatted string such as “Laptop, Monitor, Keyboard.” It’s especially useful for management summaries and reports.
Conclusion
These ten advanced Excel examples show how powerful modern Excel
functions have become. Functions like LAMBDA, LET, SCAN, and REDUCE allow you
to move beyond basic formulas and perform dynamic calculations that
automatically adjust as your data changes.
With these techniques, you can:
- Analyze performance data across multiple
dimensions.
- Combine and filter records without manual
effort.
- Build automated reports and dashboards.
- Create cleaner, more efficient formulas.
If you practice these functions on real data, you will see how they can save hours of work and make your spreadsheets smarter. Each of the formulas in this guide can be used in business analytics, HR management, finance tracking, or project reporting. Once you master them, you will be able to design dashboards, generate insights, and automate repetitive tasks like a professional data analyst. So open Excel, try these examples step by step, and experience how advanced formulas can completely transform the way you work with data.
0 Comments