• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Top Excel Functions for Data Analysts

You are here: Home / Excel Formulas / Top Excel Functions for Data Analysts
top excel functions for data analysts
January 12, 2023 by Mynda Treacy

A data analyst gathers, cleans, models, analyses, and interprets data. The gathering and cleaning should be done with Power Query. The modelling and analysis can be done with a combination of PivotTables/Power Pivot and functions.

The other day I stumbled upon a post claiming to list the top Excel functions for data analysts. I disagreed with half the functions listed in that post, so I did a search and found a load more posts with pretty much the same list (they probably copied each other). I’m going to try and right that wrong in this post by listing what I know are the top functions for data analysts and the functions that you don’t need to worry about learning so much.

Watch the Excel Functions for Data Analysts Video

Subscribe YouTube


Download Excel Functions for Data Analysts Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.
Download the Excel Workbook and follow along. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.


Excel Functions for Data Analysts

Function 1: IF, Nested IFs and IFS

The IF functions belong to the logic group of functions and are a must for every Excel user to know.

Get started with the IF function and nested IFs. And if you have Excel 2019 or later, or Microsoft 365, check out the newer IFS function.

Subscribe YouTube


Function 2: IF(AND and IF(OR

The AND and OR functions extend the number and type of logical tests you can specify when nested in the IF function. A lot of people struggle with them, but once you master these functions you won’t look back.

Still stuck? Download our IF formula builder and have your IF formulas written for you.

Function 3: SUMIFS

The SUMIFS function is one of the ‘IFS’ family of functions which enables you to aggregate data that matches criteria you specify. Once you know one of these functions, the others are easy because they all have the same syntax, as you can see below:

other ifs functions

These functions treat all criteria as AND criteria, meaning each condition must be TRUE for the value to be included in the SUM/AVERAGE etc. If you need more flexibility, the SUMPRODUCT function can handle AND and OR criteria.

Function 4: XLOOKUP (or VLOOKUP)

The XLOOKUP function, available in Excel 2021 onward and Microsoft 365, is the new improved version of the much loved and sometimes hated VLOOKUP function. XLOOKUP gets around the limitations of VLOOKUP and removes the risk of inexperienced users inadvertently building formula errors into their work.

Subscribe YouTube


Subscribe YouTube


Function 5: INDEX & MATCH

INDEX and MATCH formulas can be scary for many users, but they get around the limitations of VLOOKUP. If you don’t have the XLOOKUP function in your version of Excel, then I encourage you to master INDEX & MATCH.

I’ll also go as far to say that the INDEX function is probably the MOST IMPORTANT EXCEL FUNCTION! If you’re already familiar with INDEX, then check out this tutorial that covers 5 things most people don’t know about the INDEX function:

Subscribe YouTube


Function 6: FILTER

The FILTER function is new in Excel 2021 and Microsoft 365. It’s one of the most important new functions we have in Excel. FILTER enables you to extract rows from a table of data based on criteria you specify. Not only that, you can extract some of the columns or all, or even extract the columns in a different order.

Subscribe YouTube


Function 7: IFERROR

The IFERROR function is a must for all report builders as it automatically hides errors returned by formulas ensuring a polished finish.

Function 8: EOMONTH and EDATE

The majority of Excel users work with date and time at some stage, especially accountants. The EOMONTH function, short for End of Month, and the EDATE Function, which I’m guessing is short for Extend Date, are super handy.

EOMONTH returns the last day of the month, n months before or after the specified date. It can even return the start of the month with a simple trick.

EDATE returns the same date of the month, n months before or after the specified date.

If you’d like to learn more about working with dates and times in Excel, check out my Excel Date & Time 101 and grab a copy of my eBook and examples file.

Function 9: NETWORKDAYS.INTL

As a data analyst you’ll typically be working with dates a lot. The NETWORKDAYS.INTL function returns the number of working days between two dates, excluding weekends and holidays.

Function 10: GETPIVOTDATA

The GETPIVOTDATA function is often misunderstood and harshly judged, but it’s the secret weapon of PivotTable users who want to create a custom styled report without the bulk of a PivotTable and is super robust.

Subscribe YouTube


Function 11: UNIQUE

The UNIQUE function generates a list of distinct items from an array, or a list of items that only occur once. It’s available in Excel 2021 onward, or with Microsoft 365.

It simplifies what once required super complex array formulas into a single easy function.

Function 12: SORT

The SORT function sorts the contents of a column, columns (range) or array in ascending or descending order. It's super handy for wrapping around functions that return lists like the UNIQUE function, or tables of data like the FILTER function. It’s available in Excel 2021 onward, or with Microsoft 365

Function 13: SEQUENCE

The SEQUENCE function returns a list of sequential numbers in an array. Among other things, it replaces the need to use the ROW or COLUMN function to return an array of values for use in other functions. It’s available in Excel 2021 onward and Microsoft 365.

More Excel Functions

Now that you’ve mastered the above functions, you can find more resources here:

Advanced Excel Formulas course – get up to speed with advanced formulas in 5 hours.

Excel Function Library – Looking for help on a specific Function? Check out our Excel Function Library.

Absolute References Explained – Absolute references are a cornerstone of writing formulas and are therefore a must have skill for every Excel user.

Absolute References for Tables – Tables have their own absolute reference language which is not as obvious as it should be.

Writing Formulas Efficiently – In this post I share how to write formulas in such as way that it’s quick to write, and quick to maintain.

Named Ranges – Unlock some more advanced techniques with named ranges and make your formulas easier to read, write and maintain.

Time Calculations – Working with dates and time in Excel is tricky if you don’t know the fundamentals of how Excel handles date-time values.

Excel Forum – Have a specific question you need help with? Our forum volunteers are eager to help you.

Functions You Don't Need

Below are some of the functions that appear in lists of ‘top Excel functions for data analysts’ which I don’t think are important, or there are better tools for the tasks they perform:

TRIM – removes all spaces from a text string except those between words. Data cleaning is a fundamental part of data analysis. The savvy data analyst will be using Power Query to automate these laborious repetitive tasks. They won’t be using functions like TRIM.

LEN – calculates the number of characters in a cell. This is often used when cleaning data to find if there are non-printing characters in a cell or to validate that data in a column is a consistent length. Why waste time with this when Power Query can automate these tasks.

DAYS – calculates the number of days between two dates, however this function is redundant. You can simply subtract one cell containing a date from another to calculate the number of days between two dates. E.g. =DAYS(B3,B2) is the same as =B3-B2

CONCATENATE or CONCAT – joins text together form multiple cells. You can do this with the ampersand character e.g. =CONCAT(A1,A2,A3) is the same as =A1&A2&A3. Other options are Flash Fill or you can automate this in Power Query.

ISBLANK – checks if the cell is empty. This is also often used to validate data quality and can be done more efficiently in Power Query.

As previously mentioned, Power Query is an essential tool for data analysts and as important as mastering functions. If you’d like to get your skills up to speed quickly, check out the video below and please consider taking my Power Query course.

Subscribe YouTube

 

top excel functions for data analysts

More Excel Formulas Posts

excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.

Top 10 Intermediate Excel Functions

Take your Excel skills to the next level with this top 10 intermediate Excel functions. These are must know functions for all Excel users.

5 Excel SUM Function Tricks

Excel SUM Function tricks uncover a load of hidden features that aren’t obvious at first glance. Some are super useful in other functions too.
Category: Excel Formulas
Previous Post:excel advanced formula environmentExcel Advanced Formula Environment
Next Post:Excel Speedometer Chartsexcel speedometer charts

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Shopping Cart

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x