Excel has a wide variety of functions that you can utilize to accomplish a myriad of tasks. We have put a list of tutorials together on the functions that you absolutely have to know and what’s even better is that these tutorials are all on the ExcelDemy blog. These functions will greatly increase your efficiency in Excel and mastering them will make you a power user in no time at all, we guarantee it.

# Most Useful Excel Functions

1) VLOOKUP & HLOOKUP Function

VLOOKUP or the Vertical Lookup Function and the related HLOOKUP Function, are used to lookup a value in a table based on an input value, a specified column number (or row number in the case of HLOOKUP) – you can specify whether the function returns an exact or approximate match. We have put together a list of our tutorials showing you how to use the VLOOKUP and HLOOKUP Functions:

VLOOKUP Function: In our VLOOKUP Function in Excel: Learn with Examples, tutorial we not only provide an overview of VLOOKUP but an example to get you started with the VLOOKUP Function.

HLOOKUP Function: In our how to use the HLOOKUP Function in Excel, tutorial we cover an overview of the HLOOKUP Function in terms of syntax as well as an example of how to use the HLOOKUP Function.

2) INDEX AND MATCH Function

The INDEX and MATCH combo form a powerful lookup combo that can handle the situations VLOOKUP cannot, so in order to assist you with learning about INDEX and MATCH we have put together a list of tutorials to assist you:

INDEX and MATCH Function: In our Index Function Excel [Examples, Make Dynamic Range, INDEX MATCH], tutorial we go through the INDEX Function and the INDEX and MATCH combo. In addition, you will learn how to use the INDEX Function in both array and reference form.

Using the INDEX and MATCH Functions in VBA code: Did you know that you can use the worksheet INDEX and MATCH combo in your VBA code as well? Well, in our using Excel worksheet functions Index & Match in VBA code! tutorial, we show you how to do just that.

Related: Advanced Excel Formulas & Functions Course (Affiliate Link)

3) IF Function

The IF Function is a very useful logical function that you can use to test a variety of conditional situations, we have put together a list of tutorials on our blog that covers the IF Function:

IF Function: In our IF Function tutorial, we cover the syntax of the IF Function as well as illustrating its use with a simple example.

IF Function & AND Function: In our how to use the IF & AND functions to test multiple conditions! tutorial, we show you how to use the IF & AND Functions in one formula to test multiple conditions.

IF VBA Function & AND Function: One can also use conditional logic in VBA, in our how to use the IF & AND Functions in Excel VBA to test multiple conditions [using a medical example], tutorial, we show you how to use conditional logic in VBA.

4) DATE and TIME Functions

Date calculations and manipulating dates in Excel is always a popular topic, so we have put together a list of our most helpful tutorials on date and time functions in Excel:

Understanding Dates and Times in Excel: In our date & time in Excel – how to enter them in worksheet cells effectively? tutorial, we cover the main concepts associated with dates and times in Excel. You will understand how dates and times work in Excel, much better after reading this tutorial.

The DAYS Function: In our how to use the Excel DAYS Function with a practical example tutorial, we go over how to use the DAYS Function in order to calculate the number of days between two dates. We also include a worked practical example.

The DATEDIF Function:  In our [Excel Date Functions] How to Use the DATEDIF Function in Excel tutorial, we go over how to use the very useful DATEDIF Function in Excel using an anaerobic digestion worked example. The DATEDIF Function can be used to return the number of days, months or years between two dates.

The TODAY and NOW Functions: The TODAY Function does not take any arguments and is used to return the current date, and the NOW Function likewise does not take any arguments and is used to return both the current date and time. In our how to use the TODAY() and NOW() Functions in Excel tutorial we go over these functions and how to use them in one’s workbook.

5) OFFSET FUNCTION

The OFFSET Function at first glance, may not seem very useful to a beginner user of Excel, however, it is an extremely useful function that can be used in a variety of situations and formulas, so we have selected one tutorial dealing quite comprehensively with the OFFSET Function:

The OFFSET Function: In our use of Offset Function in Excel [Offset – Match Combo, Dynamic Range] tutorial, we go over how to use the OFFSET Function, how to use it to create a dynamic range, and how to automate calculations by combining the OFFSET and COUNT Functions. Once you read this tutorial all your questions about the OFFSET Function and its uses will be answered.

6) GETPIVOTDATA()

Many people often ask questions about using data from Pivot Tables in their other formulas, and there is a way to do this by using the GETPIVOTDATA() Function in Excel, so we have selected one tutorial that will show you how to accomplish this:

GETPIVOTDATA() Function: In our how to use Pivot Table data in Excel formulas tutorial we go over how to use pivot table data in Excel formulas. Knowing how to do this will enable you to create powerful worksheet formulas that utilize all the useful information from your Pivot Tables.

7) FORMULATEXT FUNCTION

Are you learning for an Excel or a Finance exam and want to see the formulas alongside their results without having to toggle on and off, well the FORMULATEXT Function can help you with this, so we have selected a tutorial that covers how to use the FORMULATEXT Function in Excel:

The FORMULATEXT Function: In our how to use the FORMULATEXT Function in Excel tutorial, we show you how to use the FORMULATEXT Function in Excel, as well as other ways to see the formulas in your worksheet.

# Advanced Excel Functions

What happens when you want to take your Excel skills to the next level – and want to solve complex financial homework problems or engineering problems – well you need in-depth knowledge of the advanced functions in Excel which will help you to tackle the more advanced problems. So, we have put together a list of tutorials that deal with advanced functions.

1) SUMPRODUCT FUNCTION

The SUMPRODUCT Function is utilized when you want to multiply corresponding items in arrays and then return the sum of the results. So, we have selected one tutorial that covers how to use the SUMPRODUCT Function in Excel:

The SUMPRODUCT Function in Excel: In our SUMPRODUCT() Function in Excel tutorial, we go over a basic introduction to what the SUMPRODUCT Function is and then how to use it with real-time examples.

2) INDIRECT FUNCTION

The INDIRECT Function is used to return a reference specified by a text string. Now it may not sound like much but it is used extensively in a variety of situations. So, we have selected one tutorial that reviews the INDIRECT Function:

The INDIRECT Function: In our practice Excel indirect function with 3 case studies tutorial, we cover using the INDIRECT Function with 3 case studies. It is an in-depth tutorial that really explores ways in which one can use the INDIRECT Function.

3) DATABASE FUNCTIONS

Database Functions in Excel are similar to their Access counterparts, and you can use them in Excel to lookup data, however, your worksheet must be setup correctly in order to use them. So, in order to help you with this, we have selected one tutorial that covers database functions in Excel.

Database Functions: In our how to use Excel Database Functions DGET, DAVERAGE, & DMAX tutorial, we go over how to setup your worksheet for database functions as well as how to use them with a practical worked example.

4) ARRAY FORMULAS

Array formula creation is an advanced topic, but it is worth taking the time and effort to learn since you can perform many different kinds of complex calculations if you know how to use array formulas in Excel. So, we have put together a list of tutorials to help you to understand how to use array formulas:

Introduction to Array Formulas: In our what is an array in Excel & how to work with it? array formulas basic tutorial, we take you through the definition of an array formula and how to enter it in a cell.

Advanced Array Formulas: In our breakdown of an array formula – array formula basic part 2 tutorial, we take you through how to use array formulas in your spreadsheets by using worked examples.

# Useful Functions for Financial Analysts

Financial analysts work in financial industries such as investment banking and utilize macroeconomic and microeconomic data in order to prepare financial models and predictions. Excel, of course, is utilized extensively in the financial analysis industry and we’ve put together a list of tutorials on functions that are useful for predictive analysis:

1) Using the Forecast Function in Excel

The FORECAST Function returns a predicted future value based on known existing input values. We have chosen a tutorial for you that covers how to use the Forecast Function:

The FORECAST Function in Excel:  In this tutorial from TechOnTheNet, the FORECAST Function is covered with some nice simple examples.

2) The Trend Function in Excel

The TREND Function gives one the values along a linear trend, it fits a straight line. We have chosen a tutorial for you that covers how to use the TREND Function:

The TREND Function: In this tutorial from Excel Easy, the TREND Function is covered in a simple manner and compared to the FORECAST Function.

3) Creating a Histogram in Excel

Statisticians often use histograms as visual representations of numerical data, that indicates the number of data points that fall within specified ranges.  So we have put together a list of tutorials that go over what histograms are and how to create them in Excel:

The Definition of a Histogram: In this tutorial from the ThoughtCo. website, the definition of what a Histogram is – is covered. This will get you up to speed with what a histogram actually is.

How to Create a Histogram in Excel: In their how to make a histogram in Excel (step-by-step guide), from the Trump Excel website, how to create a histogram using the Data Analysis Toolpak is covered as well as how to create a histogram using the FREQUENCY Function.

The Differences between a Bar Graph and a Histogram: In our the main differences between a bar graph and a histogram, we review this commonly asked question, as well as how to create each in Excel. We also look at the types of data sets that the bar chart and the histogram are used to visually showcase.

4) Correlation in Excel

Correlation evaluates the linear relationship between two variables and gives one an indication of the strength of the relationship between the two variables of interest. We have selected one tutorial that covers how to calculate the correlation between two variables in Excel:

Calculating Correlation in Excel: In this how to calculate correlation in Excel tutorial from the Investopedia website, calculating correlation is explained in detail as well as what correlation actually denotes. In addition, common errors associated with correlation are reviewed. The article also comes with an accompanying video tutorial.

5) Regression in Excel

Regression analysis is a commonly utilized predictive technique and analyses how well predictor variables or independent variables do in predicting the outcome or dependent variable. So we have put together a list of tutorials to explain to you what regression is all about as well as how to calculate it in Excel:

Regression: In this article on regression from the Investopedia website regression, linear regression, multiple regression, and regression in investing is explained.

Using the Regression Data Analysis Tool in Excel: In this tutorial from the for dummies website, how to calculate regression using the Data Analysis Toolpak in Excel is explained.

Multiple Regression Analysis: Multiple regression is used when you want to investigate the relationship between a dependent variable and two or more independent variables. In our multiple regression analysis with Excel tutorial, we cover how to do multiple regression using a worked example.

6) Using the Analysis Toolpak

As you no doubt have already seen –  the Data Analysis Toolpak is loaded with advanced statistical, and engineering functions, so it’s a good idea as an analyst, to get to know this useful suite of functions. So, we have selected a list of tutorials that goes over the functions incorporated in the Data Analysis ToolPak:

Data Analysis ToolPak: In the use the Analysis ToolPak to perform complex data analysis tutorial, from the Office website provided by Microsoft, the full suite of functions of the ToolPak is listed as well as a definition of what each function is used for. This is a good tutorial to review just to get a handle on what the ToolPak is all about.

Data Analysis ToolPak Further Explored: The Excel Easy website has a set of tutorials that show how to use the Data Analysis ToolPak in various situations so click on the links below in order to see more:

1. Analysis ToolPak
2. Creating a Histogram
3. Descriptive Statistics
4. ANOVA
5. F-test
6. T-test
7. Moving Average
8. Exponential Smoothing
9. Correlation
10. Regression

Hope this learning plan will help you. Do you want to add any important functions (that we missed) to this list? Let us know via comments or email.

The post Most Useful & Advanced Excel Functions List (Learning Plan) appeared first on ExcelDemy.