This tutorial is a simple and practical guide designed to help beginners understand Excel in an easy way. Instead of just looking at numbers and formulas, you will see how Excel can be used in everyday situations. Each trick is explained step by step with a clear formula and a friendly explanation so that you can understand without confusion. At the end of every example, you will also get a small practice idea to try on your own. By learning these 10 powerful tricks, you will not only save time but also make your work smarter and more organized.
1.
Dynamic Filtered List – Like a Coffee Strainer
You’re a sales manager with a list of
employees and their regions. Your boss asks, “Show me only the North region
sales.” Instead of filtering manually, Excel does it instantly.
Formula: =FILTER(A2:D5, C2:C5="North")
Explanation: This formula looks at the Region column
(C2:C5). Whenever it sees “North,” it brings that entire row into the result.
If you later add another salesperson from North, the formula updates
automatically.
Practice Exercise: Change “North” in the formula to
“South” or “East” and see how Excel updates the results.
✨ Pro Tip: Use this to instantly
filter by department, product, or category without ever touching your original
data.
2.
Two-Way Lookup with XLOOKUP – Like GPS Finding a House
You run an electronics shop. A customer
asks, “How much is the Laptop?” Your table has items (Laptop, Mobile,
Tablet) and details like Price and Stock. Instead of searching manually, let
Excel act like GPS.
Formula: =XLOOKUP("Laptop", A2:A4, XLOOKUP("Price", B1:D1, B2:D4))
Explanation: The first XLOOKUP finds the row
(Laptop). The second finds the column (Price). Together, they intersect at the
Laptop’s price, which is 900.
Practice Exercise: Replace “Price” with “Stock” in the
formula to find out how many laptops are left.
✨ Pro Tip: This formula is
brilliant for price lists, product catalogs, or inventory sheets.
3.
Combine Text with TEXTJOIN – Like Writing a Party Invite
You’re creating an invitation email. You
have names written in separate cells, but you want them neatly in one line:
Ali, Sara, Bilal, Ayesha.
Formula: =TEXTJOIN(", ", TRUE, A2:A5)
Explanation: TEXTJOIN combines multiple names into
one text string. The TRUE part makes Excel skip any empty cells so you don’t
end up with unnecessary commas.
Practice Exercise: Try changing the separator from “, ” to
“ | ” and see how the result looks.
✨ Pro Tip: Great for attendance
lists, project members, or emails.
4. Rank
with Criteria – Like a Fair Judge
You’re organizing a sports competition.
Players scored differently, and some even tied. How do you rank them fairly?
Formula: =1 + SUMPRODUCT( ($C$2:$C$5 > C2) / COUNTIF($C$2:$C$5, $C$2:$C$5) )
Explanation: This formula counts how many scores are
greater than the current score. If two players tie, they share the same rank,
and the next rank continues properly.
Practice Exercise: Enter four scores and test what happens
when two players have the same score.
✨ Pro Tip: Perfect for
leaderboards, employee performance rankings, or grading systems.
5.
Dynamic Unique Sorted List – Like a Guest Register
Imagine you ask people their region.
Some repeat answers like “North” twice. You want a clean, sorted list without
duplicates.
Formula: =SORT(UNIQUE(A2:A6))
Explanation: The UNIQUE function removes duplicates,
and SORT arranges the values alphabetically.
Practice Exercise: Try entering new values like “West” or
“East” again and see how the list updates.
✨ Pro Tip: Excellent for dropdown
lists in dashboards or surveys.
6.
Case-Sensitive Search – Like a Password Lock
You’re working with student codes like
STU101, stu202, STU303. Case matters! Normal search ignores case, but FIND
respects it.
Formula: =FILTER(A2:C5, ISNUMBER(FIND("STU", B2:B5)))
Explanation: FIND searches for uppercase “STU.” If
it exists, FILTER returns the row.
Practice Exercise: Try replacing “STU” with “stu” and
notice how the results change.
✨ Pro Tip: Useful for product
codes, employee IDs, or passwords.
7.
Running Total with SCAN – Like Climbing Stairs
You run a shop. Each day you sell more
units: 100, 200, 150, 250. You want to see how the total grows step by step.
Formula: =SCAN(0, B2:B5, LAMBDA(a,v, a+v))
Explanation: SCAN adds each new value to the total.
Day 1 = 100, Day 2 = 300, Day 3 = 450, Day 4 = 700.
Practice Exercise: Try replacing the numbers with expenses
(like 50, 30, 20) and track how your spending grows.
✨ Pro Tip: Perfect for savings,
running totals, or cumulative marks.
8.
Extract Domain from Email – Like Sorting Letters by Post Office
You have student emails like
ali@gmail.com and sara@yahoo.com. You want
to see only the domain part (gmail.com, yahoo.com).
Formula: =TEXTAFTER(A2, "@")
Explanation: TEXTAFTER grabs everything after the
“@.”
Practice Exercise: Enter your own email and see what Excel
extracts.
✨ Pro Tip: Use this to group
customers by email provider and design smarter campaigns.
9.
Highlight Errors Dynamically – Like a Teacher Spotting Mistakes
While checking scores, you see errors
like #N/A or #DIV/0!. Instead of scanning manually, Excel can pull out only
those rows.
Formula: =FILTER(A2:C5, ISERROR(C2:C5))
Explanation: ISERROR checks if a value is an error.
FILTER then returns only those rows.
Practice Exercise: Intentionally create an error (like
dividing by zero) and test this formula.
✨ Pro Tip: A must-have for data
cleaning in finance, grading, or analytics.
10.
Random List of Names – Like Picking Names from a Hat
You’re a teacher choosing three students
for a surprise quiz. Instead of slips of paper, Excel picks randomly for you.
Formula: =INDEX(A2:A7, RANDARRAY(3, 1, 1,
COUNTA(A2:A7), TRUE))
Explanation: RANDARRAY generates random positions,
and INDEX pulls the names from those positions.
Practice Exercise: Change the “3” in the formula to “2” or
“4” and see how many random names you get.
✨ Pro Tip: Great for lucky draws,
team selection, or random sampling.
Conclusion
You have now learned ten practical Excel
tricks that make everyday tasks faster and easier. These tips let you filter
data, look up information, combine text, rank results, create unique lists,
perform case-sensitive searches, track running totals, extract email domains,
highlight errors, and pick random items effortlessly. By practicing and
applying them to your own projects, you will become more confident, organized,
and efficient with spreadsheets. Keep exploring and practicing, and Excel will
transform from a challenge into a powerful tool that simplifies your work and
boosts your productivity.
0 Comments