Other functions in Excel

1. Other functions in Excel

Now that we've become familiar with aggregate functions, we'll now look at understanding the other functions Excel has to offer.

2. Function categories

Out of the box, Excel has 12 different function categories with hundreds of built-in functions that we can use. These categories include text, date and time, maths and trig, statistical, financial, logical, lookup references, engineering, cube, information, web, and compatibility. We will not cover all the various categories in this course. So all the categories on the left are inside the scope, and all categories on the right are outside the scope of this course. In the following slides, we'll walk through some examples of different functions. It's important to note that these functions can be used on cell references, table references, and static values.

3. Text

You'll likely use many functions inside the text category, especially when preparing data. It contains many functions to help clean, format, and work with your data. A set of functions that can be very useful for cleaning your data is the left and right functions. With the left function, we return the first characters in a string based on a specified number of characters. Similarly, the right function follows the same premise, except it returns the last characters in a string. With each function, you can see how this works in practice with a static value.

4. Text

Another set of functions that can also be utilized is upper and lower. The purpose of these functions are to convert data with the type text into uppercase or lowercase respectively.

5. Date & Time

For many of us, we'll regularly be working with data that requires some date or time interval. Out of the box, Excel has many options that can be used to create, convert, and format dates and times. One of the most important dates functions to know is date. This function returns a calendar date based on inputs that contain a year, a month, and a day. Let's say you have three columns, each containing year, month, and day information. We can use a date formula to combine and format these into a new calendar date.

6. Date & Time

However, let's say we have the opposite issue. We do not want to have the information in a calendar format, but instead want to return the day or even year of the date. We can use functions such as day and year, which will return the corresponding part of the date you have specified.

7. Maths & Trig

Next, we'll be looking at some maths and trigonometry functions. One of the common use cases for working with Excel is working with quantitative data. These functions can be handy for all types of data preparation, formatting, and summarizing. First, we'll look at the round function. This straightforward function returns a number rounded to the specified number of digits. The example above shows that if we use the round functions on -1.475 to two digits, we will get -1.48. There are multiple variations of round, including round up and round down. Another function that might come in useful is abs. This returns the absolute value of a number without its sign. For example, the absolute value of -2 would be two. We also have the sum function that we've shown you in a previous example.

8. Statistical

Finally, we'll briefly look at statistical functions. You've already seen these in this course with the functions average, count, min, and max. There are far more functions available in this category, but we'll explore that in future courses.

9. Let's practice!

Time to put your knowledge to the test.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.