Introduction
Mastering Excel is not just about remembering formulas — it is about knowing how they work and how to use them step by step. In this tutorial, we will go through 8 advanced Excel examples where you will see the formulas along with a clear breakdown of how each part works. These examples include tasks like calculating totals, making lists, finding the right value from a table, working with multiple conditions, and switching between different results. By the end, you will not only know what to type in Excel but also understand why it works and when to use it. This will help you become more confident and faster when working in Excel.
1.
Calculate Total Revenue from Product Sales
If you’re managing an electronics store, you often need to calculate the total revenue from selling gadgets like smartphones, chargers, and smartwatches. By defining quantities and prices inside the formula, you can quickly multiply each product’s sales and get the final total without manual work. This method is widely used in retail, e-commerce, and sales reporting.
Formula: =LET(qty,B2:B11,price,C2:C11,SUMPRODUCT(qty,price))
Step-by-Step Breakdown:
- qty,B2:B11 → Defines “qty” as the column
of quantities.
- price,C2:C11 → Defines “price” as the
column of prices.
- SUMPRODUCT(qty,price) → Multiplies each
product’s quantity by its price, then sums the results.
- LET → Keeps the formula neat by storing
“qty” and “price” as names, instead of repeating ranges.
Why This is Useful:
This method is powerful for financial reports where you want cleaner formulas.
It reduces errors and improves readability when datasets grow large.
2.
Create a Combined List of Products from a Specific Region
Suppose you are working as a regional manager in an office furniture company. For an upcoming marketing campaign, you need to quickly prepare a list of all the products that are available in the East region. Instead of typing each product one by one, you want Excel to automatically gather the names and show them in a single cell, neatly separated by commas. This way, you can copy the list directly into emails, reports, or promotional material without extra work.
Formula: =TEXTJOIN(", ",TRUE,FILTER(A2:A11,B2:B11="East"))
Step-by-Step Breakdown:
- FILTER(A2:A11,B2:B11="East") →
Pulls out only the products where the region = East.
- TEXTJOIN(", ",TRUE, … ) → Joins
those product names with commas between them.
- The TRUE argument ignores empty cells,
keeping the result clean.
Why This is Useful:
This is excellent for creating dynamic summaries. For example, your dashboard
could instantly show: “Products in East: Desk Lamp, Whiteboard, Sofa, Rug.”
3.
Retrieve Sales Figures Based on Both Product and Region
As a sales analyst in a tech company, you regularly work with a large dataset of products and regional sales. Management often asks questions like: “What were the Printer sales in the East region last month?” Instead of manually searching through the grid of numbers, you can use a formula that performs a two-way lookup. With this method, Excel will quickly locate the exact intersection of the product (Printer) and the region (East) from your data table. This saves time, reduces errors, and ensures accurate reporting whenever you need to pull product sales for a specific region.
Formula: =INDEX(B2:E11,MATCH("Printer",A2:A11,0),MATCH("East",B1:E1,0))
Step-by-Step Breakdown:
- MATCH("Printer",A2:A11,0) →
Finds the row number for “Printer.”
- MATCH("East",B1:E1,0) → Finds
the column number for East.
- INDEX(B2:E11, … ) → Picks the exact cell
at the row & column found.
Why This is Useful:
This is a two-way lookup — useful when you want precise intersections,
like “sales of Projectors in West” or “Router sales in North.”
4. Tracking
Down the Most Recent Transaction Date for Appliances
In the retail field, store managers often need to track the latest sale date of specific products to manage inventory. For example, if you’re running a home appliance store, you may want to know when the last refrigerator was sold. This helps decide whether it’s time to restock, run a promotion, or adjust pricing. Using a reverse-search formula, Excel scans the sales log from bottom to top and instantly gives you the most recent sale date, saving time and reducing manual checking.
Formula: =XLOOKUP("Refrigerator",A2:A11,B2:B11,,,-1)
Step-by-Step Breakdown:
- "Refrigerator" → The item to
search for.
- A2:A11 → The product column.
- B2:B11 → The sales date column.
- The last argument -1 → Tells Excel to
search bottom-to-top (reverse order).
Why This is Useful:
Perfect for tracking the latest activity — whether it’s the most recent sale,
most recent delivery, or latest inspection date.
5.
Build a Simple Custom Formula for Profit Margin
As a financial analyst in a furniture retail company, you often need to check the profit margins of different products before finalizing pricing or preparing reports. Instead of manually calculating each time, you build a reusable formula that works for any product. For example, when analyzing a sofa, if the cost is $200 and the revenue is $300, the formula instantly returns the margin (300-200)/300 = 33%. This approach can be applied to chairs, tables, or any other furniture item, making financial analysis faster and more consistent.
Formula: =LAMBDA(cost,revenue,(revenue-cost)/revenue)(200,300)
Step-by-Step Breakdown:
- LAMBDA(cost,revenue, … ) → Defines a
reusable formula with cost and revenue as inputs.
- (revenue-cost)/revenue → Profit margin
calculation.
- (200,300) → Inputs cost = 200, revenue =
300.
Why This is Useful:
This allows you to create custom reusable formulas. Instead of writing (Revenue-Cost)/Revenue
each time, you can apply it to any row or dataset instantly.
6.
Automatically Add Tax to Every Price
You’re managing a clothing store where each product has a base price, but customers need to see the final price including a 15% sales tax. Instead of manually calculating the tax for every item, you can use Excel to apply the tax across the entire price list in one step. This saves time, reduces errors, and ensures that all displayed prices are accurate for billing and customer transparency.
Formula: =MAP(B2:B11,LAMBDA(x,x*1.15))
Step-by-Step Breakdown:
- B2:B11 → Range of prices.
- LAMBDA(x, x*1.15) → Takes each value (x)
and adds 15% tax.
- MAP → Applies this logic to every value in
the range.
Why This is Useful:
If tax rates change, you only need to edit once. This keeps price lists
consistent and dynamic.
7.
Switch Between Different Business Metrics Instantly
As a stationery store manager, you often prepare weekly reports for your team. Sometimes management asks for the total sales volume across all items like pens, notebooks, and bags. Other times, they want to quickly see the total profit made in the same period. Instead of writing two separate formulas, you can use one flexible formula that lets you switch instantly between sales or profit. This saves time and ensures you can adjust your analysis depending on what information is required.
Formula: =CHOOSE(2,SUM(B2:B11),SUM(C2:C11))
Step-by-Step Breakdown:
- SUM(B2:B11) → Calculates total sales.
- SUM(C2:C11) → Calculates total profit.
- CHOOSE(2, … ) → Picks the second option
(profit).
Why This is Useful:
This acts like a switch between multiple KPIs. You can easily extend it to
handle revenue, expenses, or margins by adding more options.
8.
Look Up a Value Using Multiple Conditions
Imagine you work in a home appliances company that sells products like Fans, ACs, Heaters, and Coolers across different regions (North, South, East, West). The sales manager asks you to quickly report how many Fans were sold in the North region.
Instead of manually searching through the dataset with many rows, you can use a formula that matches both the product name (Fan) and the region (North) at the same time. This instantly returns the exact sales figure you need, which is very useful for regional sales reports, performance tracking, and business decision-making.
Formula: =XLOOKUP(1,(A2:A11="Fan")*(B2:B11="North"),C2:C11)
Step-by-Step Breakdown:
- (A2:A11="Fan") → TRUE/FALSE for
rows where the product = Fan.
- (B2:B11="North") → TRUE/FALSE
for rows where the region = North.
- Multiplying both → Only rows that satisfy both
conditions equal 1.
- XLOOKUP(1, … ,C2:C11) → Returns the Sales
value where both match.
Why This is Useful:
This is powerful for multi-criteria lookups, such as “Revenue of AC in
South” or “Profit of Cooler in East.”
Conclusion
You have now
learned 8 useful and advanced Excel examples. Each one showed the full formula,
the step-by-step breakdown, and how it gives the right result. If you practice
these methods, you will find it easier to handle different tasks in Excel,
whether it is adding totals, finding information, or creating lists. Keep
practicing these formulas on your own data, and soon you will be able to use
them quickly without any help. The more you practice, the more confident and
skilled you will become in Excel.
0 Comments