All Excel Functions Explained Briefly Under 15 Major Categories

Have you ever been placed in a position where you were thrown with a gigantic Excel sheet which demands you to figure out certain results or to organize it properly?

I’ve been there many times and spent hours manually trying to fix everything the best way I could or break down calculations in the calculator to reach the outcome.

But once I got to learn the Excel functions, my struggle with these sheets have drastically gone away! With a few clicks of mouse or keypresses, I was able to achieve the same result that took me days in the past!

I won’t lie, these are not few in number and easy to learn, but with my guideline, you’ll be up to speed in almost no time and surprise your bosses with the most refined, accurate and not to mention – superfast outcomes!

What Are Excel Functions?

What are Excel functions

Excel functions are some of the most vital features of this software that are made to automate calculative, filtering, sorting, refining or similar tasks to save time and generate accurate outcomes.

While there are certain differences between functions and formulas in Excel, these two actually go hand in hand while producing the desired results.

Whether you have a complex set of calculations in your hand or a boring database that requires drawing out data for further analysis, Excel functions will land as your savior in every regard.

Also, to be claimed as an Excel master, you can’t ignore learning the functions because they take up the majority of advanced Excel tasks and are being performed everyday in the official works.

Excel Functions Explained By Expert

Here, I’ve broken down all the Excel functions under 15 broad headers so that you can isolate their features for a better understanding. I’ve provided necessary examples and function names so that you can further apply them to your work easily.

Compatibility Functions

There are some older MS Excel functions that didn’t always generate the most accurate results. From Office 2010, these functions had been recollected and added under the Compatibility banner so that users still can use them for certain needs.

Among the 41 Compatibility functions, I’m highlighting 5 of the most popular and recurrent functions that you should have good knowledge about.

CONCATENATE Function

CONCATENATE

This function does exactly what it sounds like – it concatenates or brings together text strings in two cells into one. Suppose you have different columns of text string pairs that you have to merge into one cell each, this function comes in handy during such operations.

For instance, if you have values of cell A1 and cell B1 and you want to merge them together in cell C2, click C2 and insert the CONCATENATE function like this:

=CONCATENATE(A1,B1)

The former value will sit in front, followed by the latter value. If you want a space in between the two text strings, type in your formula like this:

=CONCATENATE(B2,“ ”,A2)

In this manner, you’ll be able to merge two text strings into one with a space. Drag that over to the rest of the column if you want this function to be applied to a bulk of data.

FLOOR Function

FLOOR

The FLOOR function usually rounds up a number down toward zero, which means the rounded up result will be equal or less than the original number. The argument for this function looks like this:

=FLOOR(number, significance)

The number part is easy to comprehend, it’s the value you’ll be dealing with. Significance is the multiplication factor based on which your flooring down will function. Let me explain.

Say you have a number of 13 that you want to round up with the FLOOR function. You need your rounding up to be the multiplication value of 5 – and this is your significance. When applied, the outcome is going to be 10, since it’s the closest 5 multiplicator lower than 13.

That’s how this function basically works. The significance can be in decimals so there’s also that for convenience.

MODE Function

MODE

The Mode function basically picks out the most recurring number within a set or range of data. It’s pretty simple and straightforward really and not at all complicated. The formula for this function looks something like this:

=MODE(A3:A15)

The A3:A15 signifies the data range out of which the most repeated number will be picked out from.

RANK function

RANK function

The RANK function works somewhat like the filters in MS Excel, but without disrupting the existing order of data. That is why this function is widely used as it doesn’t tamper with the pre-existing standing. The argument looks like something like this:

=RANK(number,ref,[order])

I’ll break down the formula for you with an example. Suppose, you have a product list with their quantity and want to figure out the quantity in numeric order without having to go through large quantity numbers.

You’ll type in the RANK formula, where the “number” argument will locate the first data in the range, against which you will be comparing the rest of the data. The “ref” argument will select your data range.

The “order” will determine whether it’s in ascending order (highest quantity gets ranked 1) or descending order. Order is either 0=descending or 1=ascending.

Let me show you how the complete formula can look like:

=RANK(B2,B2:B100,0)

If you leave the order portion empty, it’ll still rank in the descending order. The order numbers will place themselves according to the cell you have selected for the function.

VAR Function

VAR Function

The VAR function determines the variance from a range of data samples. You have to select the range of data for this function to work, about which you’ll find in detail in our Variance article. The argument looks something like this:

=VAR( number1, [number2, … number_n] )

Using this, Excel will calculate the variance value for you in the blink of an eye and you’ll get to move on with your task effortlessly!

Cube Functions

Cube Functions

If you’re using the Pivot Table or the PowerPivot functions frequently, you’ll find its limitation that you can’t alter or add to the existing data once you’ve generated your table. This confinement has led Excel users to this error quite often: “We can’t make this change for the selected cells because it will affect a PivotTable …”

Cube functions are some of the most underrated features from Office 2013 that walk right past this problem. These functions allow you to insert data columns or rows almost anywhere within your database without having to run into such errors.

These connections are live and you can pretty much alter the data in the same manner as you would with regular Excel functions. You can input other regular Excel functions within that database and have it updated instantly.

Cube functions currently work with the Excel Data Model and so you are allowed to literally copy and paste data columns that Pivot Table would never make room for.

The Cube functions include: CUBEKPIMEMBER, CUBEMEMBER, CUBEMEMBERPROPERTY, CUBERANKEDMEMBER, CUBESET, CUBESETCOUNT & CUBEVALUE.

Database Functions

Database Functions

The Database functions basically work with an Excel spreadsheet that’s structured like a database. You might ask: what are the criterias for a spreadsheet to be claimed as a database?

The answer is, when there is data spread across columns and rows in a bulk, rows include unique data for individual data sets and labeled columns are divided by specific types or genres of information spread across the sheet.

The database function arguments look something like this:

=DSUM(database, “field”, criteria)

I’ve used the DSUM formula as an example to elucidate on the arguments. The database argument implies the range of data that you’ll be using.

The field argument refers to the column that you’ll be holding against your data. You can either use the field label or the column name within the inverted commas like “Salary” or “C2”.

The criteria is the range of data within the database that you’ll be analyzing against the field criteria. A complete formula may resemble this:

=DSUM(A1:H100, “Salary”, C3:C100)

This way, you’ll be able to generate the summation of the salary for your specified category of staff, and so on.

Excel database functions include: DAVERAGE, DCOUNT, DCOUNTA, DGET, DMAX, DMIN, DPRODUCT, DSTDEV, DSTDEVP, DSUM, DVAR, DVARP.

Date And Time Functions

Date and Time

The Date and Time functions are very straightforward features of MS Excel. They insert date or time within your Excel spreadsheet in ways you want to. For instance, if you select an empty cell and type in:

=NOW()

It’ll place the present date with that exact time, even up to the second. The DATE function places just the date, the YEAR function inputs only the year and so on.

Other functions like DATEVALUE convert the date into a text series, similarly TIMEVALUE generates results alike.

There are even more advanced functions under this category like the DATEDIF function which identifies the difference of days between two dates given in Excel sheets. You can customize your command to receive that difference in year, month or even days.

The NETWORKDAYS goes a step further. It counts only the workdays between two dates excluding the weekends. This is a very useful function for office spaces or educational institutions.

Engineering Functions

Engineering Functions

Excel has something for everyone – that includes the engineers as well. These Engineering functions can perform a wide range of tasks that you might have not known about.

For instance, the conversion functions can convert between measuring units. You can convert miles to kilometers, pounds to KGs and the list can go on.

It can also convert between bases without having to seek refuge in scientific calculators. Specific functions can convert between binary to decimal, or even deal with complex numbers in your desired way.

There are plenty of options you can choose from while using the Engineering functions, just you’ll need to put in some good practice to get hold of them before implementing them in your project.

Prominent Engineering functions include: CONVERT, BESSELI, BIN2DEC, ERF.PRECISE, BITAND, DELTA, COMPLEX and so on.

Financial Functions

Financial Functions

The Financial functions are some of the most frequently used Excel features that are seen being practiced in every financial organization, multinational companies or investment banking agencies.

These functions aid the companies to evaluate, assess or assume financial data using models that are made in Excel. From data analysis to tracking company’s future growth – these functions have the capability to do it all under one banner.

Such analyses are further used to create charts or other visual presentations that can create demonstrations of assumptions and overall financial condition.

For instance, the Future Value or FV function can determine the future value of products held under the company ties. The formula to achieve the results look something like this:

= FV(5%, 4, 2, –400)

In hindsight, it might look like random gibbering, but when I break down the arguments for you, you’ll get a better understanding of what this function actually does.

In the aforementioned formula, the argument line should look like this:

=FV(rate,nper,[pmt],pv,[type])

Here, FV is the formula indicator, Rate refers to the interest rate, Nper refers to the period number, pmt means the payment period, PV is the present value of the products and Type indicates when the payment has been made (leaving it blank will lead to the assumption that it was made at the end of the period).

There are many Financial functions you can play around with according to the type of your data and your desired outcome. Some of the commonly used Financial functions include: PV, NPV, PPMT, IRR, RATE, EFFECT, NOMINAL etc.

Information Functions

Information functions are widely used in Excel operations that tell the users certain information on the selected cell or a range of data. That information is outputted on your desired cell which gives you information on error, functions returning a constant value, general info or other data type info.

CELL Function

CELL Function

The Cell function highlights specific information about your mentioned cell on its location, the type of formatting or what kind of data it’s holding. The formula to get such information resemble this:

=CELL(info_type, [reference])

It provides outputs in codes that require some understanding on the user’s account. Once you’re familiarized with that, you can easily draw out knowledge about specific cells to assess what that data is about.

ERROR.TYPE Function

You’ll probably find different errors like #DIV/0 error or so on after applying any formula to a set of data. The ERROR.TYPE function will break down what that error means and how you can make amends. Although, having a thorough understanding of the error codes will mean a smoother application of this function.

INFO function

The INFO function among this group specifies the basic information of the spreadsheet, all the way up to the system information based on the command. There are a total of 7 commands that this function works with and you’ll need to understand each of them before applying for the specific information of your choice.

Within the Information functions, some stand out more than the rest in terms of usage: ISERROR, ISNUMBER, NA, ISBLANK, TYPE, SHEET and so on.

Logical Functions

Logical Functions

The Logical functions are probably some of the most interesting and intriguing ones that can be stringed together to make into complex argumentative outcomes. The basic principle of these functions dwell in TRUE and FALSE and the outcomes are delivered based on these two parameters.

To many, the Logical functions are like a game of chess with unprecedented modularities. Let me give you an example using the AND function:

When you’re dealing with a data set and have to analyze or verify something from them, the AND function comes in handy. You can input a command line to check if all the required criterias are met and if so, the output will show accordingly. The AND function argument looks like this:

=AND(logical1,[logical2], …)

Say your dataset is of 5th grade students and their marks obtained in certain courses. In this gigantic spreadsheet, you want to assess if they have passed in each subject so that you can classify them as PASSED as a whole. You can merge it with the IF function like this:

=IF(AND(B2:B10>50), “PASS”, “FAIL”)

It means, if each of the marks between B2 and B10 are equal to or over 50 (where 50 is the passing mark), then the output will write PASS, otherwise it’ll simply write FAIL.

This way, you can merge prominent Logical functions like AND, IF, OR, XOR, NOT etc. to create logical outputs easily within a large data set for a straightforward analysis.

Lookup And Reference Functions

The Lookup functions in Excel are used for matching something with the reference you provide in the command line to see if one set of data matches with the one you’re intending your search upon.

The basic attributes of these functions allow the users to stretch it beyond just a Ctrl+F search feature to a more advanced position where you’ll be able to perform expert analysis without scrolling through large chunks of data.

These lookups can be done horizontally using HLOOKUP, vertically using VLOOKUP, MATCH function to find a match of a certain cell’s value to a wide data array or INDEX to find out specific data within the interaction point of certain row and column number of your choice.

For example, you can use the LOOKUP function under the broad Lookup and Reference umbrella to identify the value of a certain category without even diving into the data sheet. The argument looks like this:

=LOOKUP(lookup_value, lookup_vector, [result_vector])

Using this, you can select a specific data with the lookup_value argument, the lookup_vector refers to the data array and the result_vector is an optional string that you can use to specify one column or one row of result.

Math And Trigonometry Functions

Math and Trig functions perform the most basic to the advanced mathematical and trigonometric calculations in Excel. From basic arithmetic, conditional sums, logarithms to trigonometric calculations – everything can be done using such functions and these are often unknown to the Excel users.

Some of these functions are further made into advanced mode of calculations within the Engineering and Statistical functions, but the basics remain all at one place.

If I highlight the categories of these functions in Excel, then it can provide you with a comprehensive idea of their operations. The categories are as follows:

  • Basic Numeric Info
  • Basic Mathematical Calculations
  • Rounding-up Numbers
  • Matrix Functions
  • Randomizing Numbers
  • Conditional Sums
  • Advanced Mathematical Calculations
  • Trigonometric Functions and so on

Using these functions, you can sum up a wide range of numbers, find the aggregate, floor or ceiling, roundup numbers, generate summations with conditions that can aid you to generate refined outcomes and almost all the trigonometric calculations.

It’s a dream function category for ones who have to constantly perform mathematical calculations and this has the power to reduce down the effort drastically in the most organized way.

Statistical Functions

The Statistical functions deal with large groups of numbers as a whole and generate tasks according to your preference. The charm of these functions is that you can play around with large data quantities in a very short amount of time without having to scroll through each of them separately.

You’ll be able to generate average, median, mode, deviation, minimum & maximum value, largest or smallest number based on your ranking preference, slope, so on and so forth.

Each of the functions have their unique arguments that you need to adjust to get your desired outcome. These are very easy to master, all you require is some time, patience and courage to get through to the end.

Text Functions

Text Functions

The Text functions are the features in Excel that allows you to deal with text strings within spreadsheets. Whether it’s unwanted spaces or removal of text characters from the beginning, middle or the end, these functions let you count, add, and deduce text strings the way you want to.

Additionally, these functions will help you refine your text strings within the spreadsheet to reach a clean data set that’s ready for presentation or analysis.

There are many Text functions that you can put to use and each of them follow unique arguments based on their criteria of task. Some of the most important Text functions include:

LEN, FIND, SEARCH, CLEAN, DOLLAR, FIXED, PHONETIC, TRIM, VALUE etc.

Web Functions

The Web functions are more recent additions to MS Excel, starting from Office 2013 and prevailing onwards. These functions will make room for you to import data from a webpage to your Excel sheet directly.

For instance, the following command will import the data from a Wikipedia web page directly into your sheet:

=WEBSERVICE(“https://en.wikipedia.org/wiki/Roger_Federer”)

Once you have the data imported in XML format, you can apply a secondary Web function named FilterXML to draw out the necessary data into your selected cell. It goes like this:

=FILTERXML(A2,”//item/title”)

A2 is where the XML data from the Roger Federer page had been stored. Using this function, you’ll be able to filter the title from that data range. Similarly, you can isolate other means of information from an imported set using a Web function.

User Defined Functions

User defined functions are basically the ones that are created by users merging multiple functions from Excel. This way, you can generate custom functions that’ll ease up your workflow without having to manually apply each of the functions individually or piling them up repetitively from time to time.

There are certain benefits to applying User-defined functions:

  • Lets you create custom math functions
  • Makes diagnosing the sheet effortless
  • Create text alterations easily
  • Calculate complex data sets without complicating things
  • Specialized functions for unique data sets

This way, User-defined functions can speed up your work tempo and remove barriers for specific data types and sets to work out a smooth result!

How To Apply Excel Functions?

There are a lot of ways that you can apply these Excel functions to your work. I’ll be showing you two of the most convenient ways which you can endorse for an effortless application without getting lost in the hundreds of functions.

Method 1: Typing Formulas Manually

For each function, there are specific formulas you can type into your spreadsheet to generate the result that you’ve been looking for. For a better understanding of the formulas, please follow our Excel Formulas guide and apply them to make your functions work.

You can select a cell and type the relevant formula there or type the formula in the Formula Tab to make your function work. It’s the most straightforward way of going about it, but you’ll need some knowledge on the formula lines.

Method 2: From Excel Formulas Tab

The second method is quite organized as Excel itself presents it in such a way. What you need to do is, open Excel, go to the Formulas tab on top and open it. There, you’ll see every formula categorized by their nature of function for your ease of understanding.

Once you have your data set in your spreadsheet and your output cell selected, you can directly implement the necessary functions from the tab where they are categorically sorted. From your pattern of work, it’ll be quite easy to pick out the relevant one without having to type in formulas manually.

Thoughts

This marks the concluding point of my article on Excel functions. Now that you have the familiarity with the basic tasks and how they operate on data sets, the rest is up to you to further refine your expertise by constantly practicing them.

There are a lot of function articles that only show you the list and what they do, but they don’t actually help out the beginners at all.

With my article here, almost every category of Excel users will be benefited in short to long term, and take a step further into the endless possibilities that MS Excel has in store for offer.

So go out there and don’t hesitate to implement Excel functions in your work, the result will most certainly comfort you and render you as one of the most capable, highly skilled workers in your field.

Leave a Comment