10 Excel functions you might not know and how to use them
Microsoft Excel is a powerful tool that many digital professionals use daily, yet its full potential often remains untapped. This article explores ten lesser-known Excel functions that can significantly enhance your productivity and data analysis capabilities, along with real-world examples and realistic data scenarios to illustrate their practical applications.
1. XLOOKUP
XLOOKUP is a more versatile and robust alternative to the traditional VLOOKUP and HLOOKUP functions. It allows you to search for a value in a range or array and return a result from a corresponding position in another range or array.
Syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Real-world example: A digital marketing agency manages campaigns for multiple clients. They have a spreadsheet with client names, campaign types, and performance metrics.
A B C D
1 Client Campaign Impressions CTR
2 TechCo Social 500000 2.5%
3 FoodInc Display 750000 1.8%
4 EduServ Search 300000 3.2%
To find the CTR for FoodInc, use:
=XLOOKUP("FoodInc", A2:A4, D2:D4)
This would return 1.8%.
2. TEXTJOIN
TEXTJOIN combines text from multiple ranges and/or strings, including a delimiter you specify between each text value that will be combined.
Syntax: =TEXTJOIN(delimiter, ignore_empty, text1, [text2],...)
Real-world example: A publishing company needs to create a comma-separated list of authors for a book's cover.
A B C
1 First Middle Last
2 John A. Smith
3 Sarah Johnson
4 Michael R. Brown
To combine these names, use:
=TEXTJOIN(", ", TRUE, A2:C4)
This would return "John A. Smith, Sarah Johnson, Michael R. Brown".
3. UNIQUE
The UNIQUE function returns a list of unique values in a list or range. It's particularly useful for removing duplicate values from a dataset.
Syntax: =UNIQUE(array, [by_col], [exactly_once])
Real-world example: A UX design team is analysing user feedback from multiple surveys. They have a long list of feature requests with many duplicates.
A
1 Feature Request
2 Dark Mode
3 Offline Access
4 Dark Mode
5 Voice Commands
6 Offline Access
To get a list of unique features, use:
=UNIQUE(A2:A6)
This would return a list with "Dark Mode", "Offline Access" and "Voice Commands".
4. FILTER
FILTER allows you to filter a range of data based on criteria you define. This function is incredibly useful for data analysis and reporting.
Syntax: =FILTER(array, include, [if_empty])
Real-world example: A video production company has a database of their projects, including client names, project types, and completion dates.
A B C
1 Client Type Completion Date
2 ABC Corp Corporate 5/10/2024
3 XYZ Inc Commercial 22/09/2024
4 123 Ltd Corporate 05/11/2024
To filter for corporate videos, use:
=FILTER(A2:C4, B2:B4="Corporate")
This would return rows 2 and 4.
5. SUMIFS
SUMIFS allows you to sum values in a range that meet multiple criteria. This function is particularly useful for complex data analysis and reporting.
Syntax: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Real-world example: A digital marketing agency wants to sum the ad spend for a specific client across multiple campaigns and platforms.
A B C D
1 Client Campaign Platform Ad Spend
2 TechCo Social Facebook 5000
3 TechCo Display Google 3000
4 FoodInc Social Instagram 4000
5 TechCo Search Google 2500
To sum TechCo's ad spend on Google:
=SUMIFS(D2:D5, A2:A5, "TechCo", C2:C5, "Google")
This would return 5500 (3000 + 2500).
6. MAXIFS
MAXIFS returns the maximum value among cells specified by a given set of conditions or criteria. This function is particularly useful when you need to find the highest value that meets specific conditions in your data.
Syntax: =MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Real-world example: A UX design agency wants to find the highest user satisfaction score for a specific product version across different user groups.
A B C D
1 Product Version User Group Satisfaction Score
2 App X 2.1 Novice 7.5
3 App X 2.1 Expert 8.2
4 App Y 1.0 Novice 6.8
5 App X 2.2 Expert 9.1
6 App X 2.2 Novice 8.7
To find the highest satisfaction score for App X version 2.2, use:
=MAXIFS (D2:D6, A2:A6, "App X", B2:B6, "2.2")
This would return 9.1, which is the highest satisfaction score for App X version 2.2 across all user groups.
This function is particularly useful in scenarios where you need to quickly identify top performers or peak values within complex datasets, making it valuable for performance analysis, quality control, or identifying best-case scenarios in various professional contexts.
7. RANDARRAY
RANDARRAY generates an array of random numbers between 0 and 1. This function is useful for simulations and statistical analysis.
Syntax: =RANDARRAY([rows], [columns], [min], [max], [whole_number])
Real-world example: A graphic designer is creating a generative art piece.
To generate random RGB values for a 3x3 grid, use:
=RANDARRAY(3, 3, 0, 255, TRUE)
This might return:
112 45 200
78 159 23
201 87 134
8. SEQUENCE
SEQUENCE generates a list of sequential numbers in an array. This can be particularly useful when you need to create a series of numbers quickly.
Syntax: =SEQUENCE(rows, [columns], [start], [step])
Real-world example: An e-learning company needs to automatically generate sequential student IDs for a new course.
To generate 5 student IDs, use:
=TEXT(SEQUENCE(5, 1, 1, 1), "STUDENT000")
This would return:
STUDENT001
STUDENT002
STUDENT003
STUDENT004
STUDENT005
9. XMATCH
XMATCH is an advanced version of the MATCH function. It searches for a specified item in an array and returns the relative position of the item.
Syntax: =XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
Real-world example: A digital asset management team has a large database of image files with metadata.
A
1 landscape
2 portrait
3 macro
4 aerial
5 street
To find the position of "macro":
=XMATCH("macro", A1:A5)
This would return 3.
10. SORT
The SORT function sorts the contents of a range or array. This can be incredibly useful for data organisation and analysis.
Syntax: =SORT(array, [sort_index], [sort_order], [by_col])
Real-world example: A content marketing team tracks the performance of their blog posts.
A B C
1 Post Title Views Shares
2 Excel Tips 1000 50
3 SEO Strategies 1500 75
4 UX Design 800 40
To sort by views in descending order, use:
=SORT(A2:C4, 2, -1)
This would instruct Excel to sort the range A2:C4 by the second column of data within the array, in descending order. The 2 in this case refers to column C, the Shares column, because the first column (column B) provides the labels. To sort in Ascending order of Share value, you would use 1, rather than -1.
SEO Strategies 1500 75
Excel Tips 1000 50
UX Design 800 40
Implementing these functions effectively
While these functions can significantly enhance your Excel capabilities, it's important to note that their effective implementation often requires a strong understanding of Excel's intricacies, gained from hands-on experience or professional training. The key to mastering these functions lies in practice and application, so try incorporating them into your daily work to see how they can streamline your processes and improve your data analysis capabilities.
Related Training Courses
Useful Resources
- Microsoft Excel BlogOfficial Microsoft Excel blog with the latest updates, tips, and advanced functions directly from the Excel team1.
- ExcelJetComprehensive resource for Excel formulas, functions, and tutorials, regularly updated with advanced techniques4.
- Chandoo.orgExpert-led Excel training site with in-depth articles on advanced functions and data analysis techniques4.
- Excel CampusExtensive library of Excel tutorials, including lesser-known functions and advanced applications4.
- Excel EasyClear, concise tutorials on Excel functions, from basic to advanced, with practical examples4.
- AblebitsBlog featuring advanced Excel tips, tricks, and functions, with regular updates and in-depth explanations4.
- Excel Off The GridResource for power users, focusing on advanced Excel techniques and lesser-known functions4.
- Excel TrickComprehensive Excel resource with tutorials on advanced functions and data analysis techniques4.
- Trump ExcelExcel blog with tutorials on advanced functions, VBA, and data analysis, regularly updated with new content4.
- ExcelForumActive community forum where Excel experts discuss and solve complex Excel problems, including advanced function usage4.