Microsoft Office
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.
andemsp;andemsp;Aandemsp;andemsp;andemsp;andemsp;andemsp;Bandemsp;andemsp;andemsp;andemsp;andemsp;andemsp;Candemsp;andemsp;andemsp;andemsp;andemsp;andemsp;D
1andemsp;Clientandemsp;andemsp;andemsp;Campaignandemsp;andemsp;Impressionsandemsp;andemsp;CTR
2andemsp;TechCoandemsp;andemsp;Socialandemsp;andemsp;andemsp;andemsp;500000andemsp;andemsp;andemsp;andemsp;2.5%
3andemsp;FoodIncandemsp;andemsp;Displayandemsp;andemsp;andemsp;750000andemsp;andemsp;andemsp;andemsp;1.8%
4andemsp;EduServandemsp;andemsp;Searchandemsp;andemsp;andemsp;300000andemsp;andemsp;andemsp;andemsp;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.
andemsp;andemsp;andemsp;andemsp;andemsp;andemsp;Aandemsp;andemsp;andemsp;andemsp;andemsp;Bandemsp;andemsp;andemsp;andemsp;andemsp;andemsp;andemsp;C
1andemsp;andemsp;andemsp;Firstandemsp;andemsp;andemsp;andemsp;andemsp;andemsp;Middleandemsp;andemsp;andemsp;andemsp;Last
2andemsp;andemsp;andemsp;Johnandemsp;andemsp;andemsp;andemsp;andemsp;andemsp;A.andemsp;andemsp;andemsp;andemsp;andemsp;andemsp;Smith
3andemsp;andemsp;andemsp;Sarahandemsp;andemsp;andemsp;andemsp;andemsp;andemsp;andemsp;andemsp;andemsp;andemsp;andemsp;andemsp;Johnson
4andemsp;andemsp;andemsp;Michaelandemsp;andemsp;andemsp;andemsp;andemsp;R.andemsp;andemsp;andemsp;andemsp;andemsp;andemsp;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.
andemsp;andemsp;andemsp;andemsp;A
1andemsp;andemsp;Feature Request
2andemsp;andemsp;Dark Mode
3andemsp;andemsp;Offline Access
4andemsp;andemsp;Dark Mode
5andemsp;andemsp;Voice Commands
6andemsp;andemsp;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.
andemsp;andemsp;andemsp;andemsp;andemsp;Aandemsp;andemsp;andemsp;andemsp;andemsp;andemsp;andemsp;Bandemsp;andemsp;andemsp;andemsp;andemsp;andemsp;andemsp;andemsp;C
1andemsp;andemsp;andemsp;andemsp;Clientandemsp;andemsp;andemsp;andemsp;andemsp;Typeandemsp;andemsp;andemsp;andemsp;andemsp;andemsp;andemsp;Completion Date
2andemsp;andemsp;andemsp;andemsp;ABC Corpandemsp;andemsp;andemsp;Corporateandemsp;andemsp;andemsp;andemsp;andemsp;5/10/2024
3andemsp;andemsp;andemsp;andemsp;XYZ Incandemsp;andemsp;andemsp;andemsp;Commercialandemsp;andemsp;andemsp;andemsp;22/09/2024
4andemsp;andemsp;andemsp;andemsp;123 Ltdandemsp;andemsp;andemsp;andemsp;Corporateandemsp;andemsp;andemsp;andemsp;andemsp;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.
andemsp;andemsp;andemsp;andemsp;Aandemsp;andemsp;andemsp;andemsp;andemsp;Bandemsp;andemsp;andemsp;andemsp;andemsp;andemsp;Candemsp;andemsp;andemsp;andemsp;andemsp;andemsp;D
1andemsp;andemsp;andemsp;Clientandemsp;andemsp;andemsp;Campaignandemsp;andemsp;Platformandemsp;andemsp;andemsp;andemsp;Ad Spend
2andemsp;andemsp;andemsp;TechCoandemsp;andemsp;Socialandemsp;andemsp;andemsp;andemsp;Facebookandemsp;andemsp;andemsp;5000
3andemsp;andemsp;andemsp;TechCoandemsp;andemsp;Displayandemsp;andemsp;andemsp;andemsp;Googleandemsp;andemsp;andemsp;andemsp;3000
4andemsp;andemsp;andemsp;FoodIncandemsp;andemsp;Socialandemsp;andemsp;andemsp;andemsp;Instagramandemsp;andemsp;andemsp;4000
5andemsp;andemsp;andemsp;TechCoandemsp;andemsp;Searchandemsp;andemsp;andemsp;andemsp;Googleandemsp;andemsp;andemsp;andemsp;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.
andemsp;andemsp;andemsp;andemsp;Aandemsp;andemsp;andemsp;andemsp;andemsp;andemsp;Bandemsp;andemsp;andemsp;andemsp;andemsp;Candemsp;andemsp;andemsp;andemsp;andemsp;andemsp;D
1andemsp;andemsp;andemsp;Productandemsp;andemsp;Versionandemsp;andemsp;andemsp;andemsp;User Groupandemsp;andemsp;Satisfaction Score
2andemsp;andemsp;andemsp;App Xandemsp;andemsp;andemsp;2.1andemsp;andemsp;andemsp;andemsp;andemsp;andemsp;Noviceandemsp;andemsp;andemsp;andemsp;7.5
3andemsp;andemsp;andemsp;App Xandemsp;andemsp;andemsp;2.1andemsp;andemsp;andemsp;andemsp;andemsp;andemsp;Expertandemsp;andemsp;andemsp;andemsp;8.2
4andemsp;andemsp;andemsp;App Yandemsp;andemsp;andemsp;1.0andemsp;andemsp;andemsp;andemsp;andemsp;andemsp;Noviceandemsp;andemsp;andemsp;andemsp;6.8
5andemsp;andemsp;andemsp;App Xandemsp;andemsp;andemsp;2.2andemsp;andemsp;andemsp;andemsp;andemsp;andemsp;Expertandemsp;andemsp;andemsp;andemsp;9.1
6andemsp;andemsp;andemsp;App Xandemsp;andemsp;andemsp;2.2andemsp;andemsp;andemsp;andemsp;andemsp;andemsp;Noviceandemsp;andemsp;andemsp;andemsp;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.
andemsp;andemsp;andemsp;A
1andemsp;andemsp;landscape
2andemsp;andemsp;portrait
3andemsp;andemsp;macro
4andemsp;andemsp;aerial
5andemsp;andemsp;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.
andemsp;andemsp;Aandemsp;andemsp;andemsp;andemsp;andemsp;andemsp;andemsp;andemsp;andemsp;andemsp;Bandemsp;andemsp;andemsp;andemsp;andemsp;C
1andemsp;Post Titleandemsp;andemsp;andemsp;andemsp;andemsp;andemsp;andemsp;Viewsandemsp;andemsp;andemsp;Shares
2andemsp;Excel Tipsandemsp;andemsp;andemsp;andemsp;andemsp;andemsp;1000andemsp;andemsp;andemsp;andemsp;50
3andemsp;SEO Strategiesandemsp;andemsp;andemsp;andemsp;1500andemsp;andemsp;andemsp;andemsp;75
4andemsp;UX Designandemsp;andemsp;andemsp;andemsp;andemsp;andemsp;800andemsp;andemsp;andemsp;andemsp;andemsp;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 Strategiesandemsp;andemsp;1500andemsp;andemsp;andemsp;andemsp;75
Excel Tipsandemsp;andemsp;andemsp;andemsp;1000andemsp;andemsp;andemsp;andemsp;50
UX Designandemsp;andemsp;andemsp;andemsp;800andemsp;andemsp;andemsp;andemsp;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.