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.