microsoft excel formulas pdf free download

Basic Excel Formulas Guide

Mastering the basic Excel formulas is critical for beginners to become highly proficient in financial analysis Financial Analyst Job Description The financial analyst job description below gives a typical example of all the skills, education, and experience required to be hired for an analyst job at a bank, institution, or corporation. Perform financial forecasting, reporting, and operational metrics tracking, analyze financial data, create financial models . Microsoft Excel Excel Resources Learn Excel online with 100's of free Excel tutorials, resources, guides & cheat sheets! CFI's resources are the best way to learn Excel on your own terms. is considered the industry standard piece of software in data analysis. Microsoft's spreadsheet program also happens to be one of the most preferred software by investment bankers Investment Banking Job Description This Investment Banking Job description outlines the main skills, education, and work experience required to become an IB analyst or associate and financial analysts in data processing, financial modeling What is Financial Modeling Financial modeling is performed in Excel to forecast a company's financial performance. Overview of what is financial modeling, how & why to build a model. , and presentation. This guide will provide an overview and list of basic Excel functions.

Once you've mastered this list, move on to CFI's advanced Excel formulas guide Advanced Excel Formulas Must Know These advanced Excel formulas are critical to know and will take your financial analysis skills to the next level. Download our free Excel ebook! !

Basic Terms in Excel

There are two basic ways to perform calculations in Excel: Formulas and Functions Formula vs Function A Formula is an equation designed by a user in Excel, while a Function is a predefined calculation in the spreadsheet application. This guide will walk you through Formula vs Function in Excel so you know exactly what the similarities and differences are. Excel allows users to perform simple calculations such .

1. Formulas

In Excel, a formula is an expression that operates on values in a range of cells or a cell. For example, =A1+A2+A3, which finds the sum of the range of values from cell A1 to cell A3.

2. Functions

Functions are predefined formulas in Excel. They eliminate laborious manual entry of formulas while giving them human-friendly names. For example: =SUM(A1:A3). The function sums all the values from A1 to A3.

Five Time-saving Ways to Insert Data into Excel

When analyzing data, there are five common ways of inserting basic Excel formulas. Each strategy comes with its own advantages. Therefore, before diving further into the main formulas, we'll clarify those methods, so you can create your preferred workflow earlier on.

1. Simple insertion: Typing a formula inside the cell

Typing a formula in a cell or the formula bar is the most straightforward method of inserting basic Excel formulas. The process usually starts by typing an equal sign, followed by the name of an Excel function.

Excel is quite intelligent in that when you start typing the name of the function, a pop-up function hint will show. It's from this list you'll select your preference. However, don't press the Enter key. Instead, press the Tab key so that you can continue to insert other options. Otherwise, you may find yourself with an invalid name error, often as '#NAME?'. To fix it, just re-select the cell, and go to the formula bar to complete your function.

Image: CFI's Free Excel Crash Course.

2. Using Insert Function Option from Formulas Tab

If you want full control of your functions insertion, using the Excel Insert Function dialogue box is all you ever need. To achieve this, go to the Formulas tab and select the first menu labeled Insert Function. The dialogue box will contain all the functions you need to complete your financial analysis Types of Financial Analysis Financial analysis involves using financial data to assess a company's performance and make recommendations about how it can improve going forward. Financial Analysts primarily carry out their work in Excel, using a spreadsheet to analyze historical data and make projections Types of Financial Analysis .

3. Selecting a Formula from One of the Groups in Formula Tab

Thisoption is for those who want to delve into their favorite functions quickly. To find this menu, navigate to the Formulas tab and select your preferred group. Click to show a sub-menu filled with a list of functions. From there, you can select your preference. However, if you find your preferred group is not on the tab, click on the More Functions option – it's probably just hidden there.

Image: CFI's Excel Courses.

4. Using AutoSum Option

For quick and everyday tasks, the AutoSum function Autosum The autosum Excel formula is a shortcut that can save time in financial modeling in Excel. Type "ALT=" to quickly sum all numbers in a series of data. This allows you to easily add up a series of numbers either vertically or horizontally without having to use the mouse or even the arrow keys is your go-to option. So, navigate to the Home tab, in the far-right corner, and click the AutoSum option. Then click the caret to show other hidden formulas. This option is also available in the Formulas tab first option after the Insert Function option.

5. Quick Insert: Use Recently Used Tabs

If you find re-typing your most recent formula a monotonous task, then use the Recently Used menu. It's on the Formulas tab, a third menu option just next to AutoSum.

Free Excel Formulas YouTube Tutorial

Watch CFI's FREE YouTube video tutorial to quickly learn the most important Excel formulas. By watching the video demonstration you'll quickly learn the most important formulas and functions.

Seven Basic Excel Formulas For Your Workflow

Since you're now able to insert your preferred formulas and function correctly, let's check some fundamental Excel functions to get you started.

1. SUM

The SUM function SUM Function The SUM function is categorized under Math and Trigonometry functions. The function will sum up cells that are supplied as multiple arguments. It is the most popular and widely used function in Excel. SUM helps users perform a quick summation of specified cells in MS Excel. For example, we are given the cost of 100 is the first must-know formula in Excel. It usually aggregates values from a selection of columns or rows from your selected range.

=SUM(number1, [number2], …)

Example:

=SUM(B2:G2) – A simple selection that sums the values of a row.

=SUM(A2:A8) – A simple selection that sums the values of a column.

=SUM(A2:A7, A9, A12:A15) – A sophisticated collection that sums values from range A2 to A7, skips A8, adds A9, jumps A10 and A11, then finally adds from A12 to A15.

=SUM(A2:A8)/20 – Shows you can also turn your function into a formula.

Image: CFI's Free Excel Crash Course.

2. AVERAGE

The AVERAGE function AVERAGE Function Calculate Average in Excel. The AVERAGE function is categorized under Statistical functions. It will return the average of the arguments. It is used to calculate the arithmetic mean of a given set of arguments. As a financial analyst, the function is useful in finding out the average of numbers. should remind you of simple averages of data such as the average number of shareholders in a given shareholding pool.

=AVERAGE(number1, [number2], …)

Example:

=AVERAGE(B2:B11) – Shows a simple average, also similar to (SUM(B2:B11)/10)

3. COUNT

The COUNT function COUNT Function The COUNT Function is an Excel Statistical function. This function helps count the number of cells that contain a number, as well as the number of arguments that contain numbers. It will also count numbers in any given array. It was introduced in Excel in 2000. As a financial analyst, it is useful in analyzing data counts all cells in a given range that contain only numeric values.

=COUNT(value1, [value2], …)

Example:

COUNT(A:A) – Counts all values that are numerical in A column. However, you must adjust the range inside the formula to count rows.

COUNT(A1:C1) – Now it can count rows.

Image: CFI's Excel Courses.

4. COUNTA

Like the COUNT function, COUNTA COUNTA Function The COUNTA Function will calculate the number of cells that are not blank within a given set of values. The =counta() function is also commonly referred to as the Excel Countif Not Blank formula. As a financial analyst, the function is useful count cells that are not blank or empty in a given range. counts all cells in a given rage. However, it counts all cells regardless of type. That is, unlike COUNT that only counts numerics, it also counts dates, times, strings, logical values, errors, empty string, or text.

=COUNTA(value1, [value2], …)

Example:

COUNTA(C2:C13) – Counts rows 2 to 13 in column C regardless of type. However, like COUNT, you can't use the same formula to count rows. You must make an adjustment to the selection inside the brackets – for example, COUNTA(C2:H2)will count columns C to H

5. IF

The IF function IF Function The Excel IF Statement function tests a given condition and returns one value for a TRUE result, and another for a FALSE result. For example, if sales total more than $5,000, then return a "Yes" for Bonus, else, return a "No". We can also create nested IF statements is often used when you want to sort your data according to a given logic. The best part of the IF formula is that you can embed formulas and function in it.

=IF(logical_test, [value_if_true], [value_if_false])

Example:

=IF(C2<D3, 'TRUE,' 'FALSE') – Checks if the value at C3 is less than the value at D3. If the logic is true, let the cell value be TRUE, else, FALSE

=IF(SUM(C1:C10) > SUM(D1:D10), SUM(C1:C10), SUM(D1:D10)) – An example of a complex IF logic. First, it sums C1 to C10 and D1 to D10, then it compares the sum. If the sum of C1 to C10 is greater than the sum of D1 to D10, then it makes the value of a cell equal to the sum of C1 to C10. Otherwise, it makes it the SUM of C1 to C10.

6. TRIM

The TRIM function TRIM Function The TRIM function is categorized under Excel Text functions. TRIM helps remove the extra spaces in data and thus clean up the cells in the worksheet. In financial analysis, the TRIM function can be useful in removing irregular makes sure your functions do not return errors due to unruly spaces. It ensures that all empty spaces are eliminated. Unlike other functions that can operate on a range of cells, TRIM only operates on a single cell. Therefore, it comes with the downside of adding duplicated data in your spreadsheet.

=TRIM(text)

Example:

TRIM(A2) – Removes empty spaces in the value in cell A2.

Image: CFI's Free Excel Crash Course.

7. MAX & MIN

The MAX MAX Function The MAX Function is categorized under Excel Statistical functions. MAX will return the largest value in a given list of arguments. From a given set of numeric values, it will return the highest value. Unlike MAXA function, the MAX function will count numbers but ignore empty cells and MIN MIN Function The MIN function is categorized under Excel Statistical functions. MIN will return the minimum value in a given list of arguments. From a given set of numeric values, it will return the smallest value. Unlike the MINA function functions help in finding the maximum number and the minimum number in a range of values.

=MIN(number1, [number2], …)

Example:

=MIN(B2:C11) – Finds the minimum number between column B from B2 and column C from C2 to row 11 in both columns B and C.

=MAX(number1, [number2], …)

Example:

=MAX(B2:C11) – Similarly, it finds the maximum number between column B from B2 and column C from C2 to row 11 in both columns B and C.

More Resources

Thank you for reading CFI's guide to basic Excel formulas. To continue your development as a world-class financial analyst Become a Certified Financial Modeling & Valuation Analyst (FMVA)® CFI's Financial Modeling and Valuation Analyst (FMVA)® certification will help you gain the confidence you need in your finance career. Enroll today! , these additional CFI resources will be helpful:

• Advanced Excel Formulas Advanced Excel Formulas Must Know These advanced Excel formulas are critical to know and will take your financial analysis skills to the next level. Download our free Excel ebook!
• Benefits of Excel Shortcuts Excel Shortcuts Overview Excel shortcuts are an overlooked method of increasing productivity and speed within Excel. Excel shortcuts offer the financial analyst a powerful tool. These shortcuts can perform many functions. as simple as navigation within the spreadsheet to filling in formulas or grouping data.
• List of Excel Functions Functions List of the most important Excel functions for financial analysts. This cheat sheet covers 100s of functions that are critical to know as an Excel analyst
• Valuation Modeling Excel Valuation Modeling in Excel Valuation modeling in Excel may refer to several different types of analysis, including discounted cash flow (DCF) analysis, comparable trading multiples

Source: https://corporatefinanceinstitute.com/resources/excel/study/basic-excel-formulas-beginners/

Posted by: stantonrightmyere0193989.blogspot.com

Share :