Contents
In this lesson, we will get acquainted with the most voluminous and frequently visited section of Microsoft Excel – function library. We will consider the structure of the library, what categories and commands it consists of. Each category has a short description that will give you a general idea of the purpose of the functions included in the library. At the end of the lesson, we will use an example to analyze how to insert a function from the library.
Microsoft Excel has hundreds of different functions, which are divided into categories. All these functions make up a common library. You don’t need to go through every feature, but getting to know a few of the main ones from each category will be very helpful.
How to access the library
To access the Excel Functions Library, select the Formulas tab. You can see all possible categories and commands in the group Function Library.
Let’s analyze what task each of the commands of the group performs:
Insert function
If you are having trouble finding the desired function in Excel, the command Insert function allows you to find it using keywords.
AutoSum
Team AutoSum allows you to automatically calculate results for the most common Excel functions such as SUM, AVERAGE, COUNT, MAX and MIN.
Recent ones
Team Recent ones Gives you access to Excel features you’ve worked with recently.
Financial
Category Financial contains functions for financial calculations, for example, the amount of the periodic payment PLT or interest rate on a loan RATE.
Functions from the category brain teaser are used to test arguments against a specific value or condition. For example, if the order amount is less than $50, then the shipping cost of $4.99 is added, and if more, then the shipping cost is not charged. In this example, it is advisable to use the function IF.
Text
In category Text contains functions that operate on text as argument values. They can be used to perform tasks such as converting text to lowercase (LOWER) or replacing part of the text with another value (ZAMENIT).
Date and Time
Category Date and Time contains functions for working with dates and times in formulas. For example, the function TODAY returns the current date, and the function The TDATA in addition to the date and time.
References and arrays
In category References and arrays contains functions that are designed to view and search for information. For example, you can add a hyperlink (HYPERLINK) into a cell or return a value that is located at the intersection of the given row and column (INDEX).
Mathematical
Category Mathematical includes functions for processing numeric arguments that perform various mathematical and trigonometric calculations. For example, you can round the value (ROUNDWOOD), find the value of Pi (PI), work (PRODUCT), subtotals (SUBTOTALS) and much more.
Other Functions
Section Other Functions contains additional function library categories such as Statistical, Engineering, Analytic, Property and Value Check, and functions retained to maintain compatibility with previous versions of Excel.
How to insert a function from a library
In the following example, you will see how to insert a function from the Excel library that allows you to calculate the number of business days during which an item must be delivered. In our case, we will use the data in columns B and C to calculate the delivery time.
- Select the cell that will contain the formula. In our example, this is cell D3.
- Select a tab formula on the Ribbon to open function library.
- In a command group Function Library, select the desired category. In our case, we will choose Date and Time.
- Select the desired function from the drop-down menu. We will choose a function PURE WORKERSto calculate the number of working days between the date of order and receipt of the item.
- A dialog box will appear Function arguments. Here you can enter or select the cells that will make up the arguments. We will enter B3 in the field start_date and C3 in the field End_date.
- If the arguments are correct, press OK.
- The function will be evaluated and you will see the result. The result shows that the delivery of the order took 4 business days.
Just like formulas, functions in Excel can be copied to adjacent cells. Hover over the cell that contains the function. Then press the left mouse button and, without releasing it, drag the autofill marker over the cells that need to be filled. The function will be copied and the values calculated depending on the rows or columns.