|PDF Name||MS Excel Formulas List|
|No. of Pages||8|
|PDF Size||0.44 MB|
|PDF Category||Education & Jobs|
|Published/Updated||June 25, 2021|
|Source / Credits||staff.brighton.ac.uk|
|Uploaded By||Ramji vishwakarma|
Use the link at the bottom to download the MS Excel Formulas List PDF.
MS Excel Formula means is calculated by adding up numbers and then subtracting the total by the number of those numbers.
The following functions can be used to calculate the mean in Microsoft Excel: AVERAGE- Returns an average of numbers. A formula is an expression which calculates the value for a cell. Excel already has functions that can be used to create predefined formulas.
- All formulae and functions start with =
- Select a cell range or cell group to be used in the formula using your mouse
- + Add * Multiply, Subtract / Divide are the operators for formulae.
- BODMAS rules are applicable to arithmetic (Brackets Over Division then Multiplication then Addition then Subtraction).
- Do not type variables in formulae (such as tax rates). Instead, put the variable in a separate column and refer to it in the formula
- To create formulae in a column again, first build it in the first column cell and then use autofill to copy it down the column.
- FunctionsFollow the format:Name(s) of argumentswhere: Name the function name (e.g. SUM, VLOOKUP)arguments The cell or range references that contain the values used by the function
- If a function has more than one argument it must be separated by a (comma).
- Text criteria within an argument must include “(quotation marks).
Use the link below to download the MS Excel Formulas and Functions List as a PDF file.
Most marketers find organizing and analyzing spreadsheets in Microsoft Excel frustrating. While you’re manually replicating columns, and writing long-form math on a piece of paper, you think, “There must be a better method to do this.”
You know what the truth is?
Excel can be difficult this way. Excel is a powerful tool that can be used to analyze and report on marketing data. It can be frustrating to feel that it is working against you without proper training. For starters, there are more than a dozen critical formulas Excel can automatically run for you so you’re not combing through hundreds of cells with a calculator on your desk.
Excel formulas allow you to identify relationships between the values of cells in your spreadsheet and perform mathematical calculations with those values. The resulting value is returned in the cell that you choose. You can automate formulas such as subtraction, percentage division, average, dates/times, and subtraction.
In this blog post, we’ll cover all of them and more.
It is possible that you are wondering what the tab “Formulas”, located at the top of the Excel navigation toolbar, means. This horizontal menu, which is shown below in Excel’s latest versions, allows you to insert Excel formulas in specific cells.
Excel makes it easier to use different formulas and make them easy to remember. The suite of icons is an easy way to browse the various formulas and keep track of them as you improve your spreadsheet skills.
Excel formulas can also be called “functions.” To insert one in your spreadsheet, highlight the cell where you want to run it, and then click on the far-left icon “Insert Function” to browse popular formulas. This is how the browsing window will look:
Want a more sorted browsing experience? To find formulas that relate to common topics such as finance and logic, you can use any of the icons (inside the red rectangle in the second screenshot). After you have found the right formula for your needs, click on “Insert Function” as shown in the window.
Let’s now take a deeper look at some of the most important Excel formulas, and show you how to use them in common situations.
- LEFT, MID, and RIGHT
We have compiled a list with keystroke shortcuts and essential formulas to help you use Excel better and save you a lot of time.
Excel formulas start with an equals sign (=), followed by a text tag indicating the specific formula Excel should perform.
Excel’s SUM formula allows you to calculate the sum or total of multiple values. Enter the values that you want to add using the format.=SUM(value 1, value 2, etc).
You can enter actual numbers into the SUM formula or the same value in a particular cell of your spreadsheet.
- For example, to find the SUM between 30 and 80, enter the following formula in a cell of your spreadsheet.=SUM(30-80). Enter to get the total of the two numbers in the cell: 110.
- For example, to find the SUM value of B2 and B11 values, enter the following formula in a cell of your spreadsheet.=SUM(B2,B11). Enter to calculate the sum of all the numbers in B2 and B11. If there are not numbers in any cell, the formula returns 0.
Remember that you can also calculate the total value of an asset.ListExcel. To calculate the SUM of all the values in cell B2ThroughType the following formula in a cell of your spreadsheet, B11=SUM(B2B11). Not a comma, but a colon between the cells. Below is an example of how it might look in an Excel spreadsheet designed for content marketers.
Excel’s IF formula is denoted=IF(logical_test value_if_true value_if_false. This allows you enter text values into the cell “if” another value in your spreadsheet is true/false. For example, =IF(D2=”Gryffindor”,”10″,”0″) would award 10 points to cell D2 if that cell contained the word “Gryffindor.”
Sometimes we need to know how often a value appears on our spreadsheets. There are times when we need to know how many times a value appears in our spreadsheets.
For this example, we’ll use Sprung’s. Instead of manually entering 10’s next each Gryffindor student, we will use the IF/THEN formula. This means that if the student is Gryffindor, then they should receive ten points.
- Here’s the formula:IF(logical_test value_if_true value_if_false).
- Logical_TestThe “IF” portion of a statement is the logical part. The logic in this example is D2=”Gryffindor”. Be sure to put the Logical_Test value in quotation marks
- Value_if_TrueThis value will be displayed if it is true, that is, the student lives in Gryffindor. To indicate that the student received 10 points, we want it be the number 10. If you prefer text to the number, only use quotation marks.
- False Value:If the value is incorrect, the student will know.NotGryffindor: We want the cell to display “0” for 0 points.
- Below is an example of the formula: =IF(D2=”Gryffindor”,”10″,”0″)
Enter the formula for the Excel percentage formula.=A1/B1. Highlight the cell and click the Home tab to convert the decimal value into a percentage.
Excel doesn’t have a formula for percentages, but Excel allows you to convert any value into a percentage. This is a great way to save time and not be stuck entering the numbers again and again.
Excel provides the basic settings to convert a cell value into a percentage.Tab Home. Select this tab and highlight the cells you want to convert to percentages. Next, click in the dropdown menu beside Conditional Formatting. (This button may initially say “General”) Then, select“Percentage”Choose from the available options. This will convert each cell’s value into a percentage. This feature is shown below.
Remember to keep this in mind when you use other formulas such as the division formula.=A1/B1If you want to return new values (e.g., ) your values may appear as decimals by default. Highlight your cells and change their format to “Percentage” in the Home tab.
Enter the formula below to perform Excel’s subtraction formula.=SUM(A1,-B1). This will subtract a single cell by using the SUM formula. To do this, add a negative sign to the cell you are subtracting. If A1 was 10, and B1 was 6, then =SUM(A1,-B1) would return a value of 10 + 6.
Subtracting doesn’t have a formula in Excel like percentages. However, it is possible. Any value (or the values within cells) can be subtracted in two different ways.
- Use the =SUM formula.Enter multiple values to subtract from each other using the format =SUM (A1, -B1) and a negative sign (denoted by a hyphen before the cell whose value is being subtracted). To return the difference between the cells in the parentheses, press enter. This is how it looks in the screenshot.
- Use the format =A1-B1. Simply type the equals sign, followed by the first or last value, a hyphen and the value you are subtracting. To return the difference between these values, press Enter.
Enter the formatted cells to perform Excel’s multiplication formula.=A1*B1. This formula multiplies cell A1 by B1. If A1 was 10, and B1 was 6, =A1*B1 would return 60.
Multiplying Excel values might seem complicated. Excel uses either a formula or the “x” character for multiplication. It’s really as simple as adding an asterisk — *.
Highlight an empty cell to multiply more than one value in an Excel spreadsheet. Next, highlight an empty cell and enter the values you wish to multiply in the format.=A1*B1*C1 … etc. Each value in the formula will be multiplied by adding an asterisk.
To return your product, press Enter. You can see how it looks in the above screenshot.
Enter the formula below to perform the Excel division formula.=A1/B1. To divide cell A1 and cell B1, this formula uses a forward-slash, “/,”. If A1 were 5 and B1 10 respectively, =A1/B1 would give a decimal value 0.5.
Excel division is one of the most simple functions that you can perform. You can do this by highlighting an empty cell and entering an equals sign “=,”. Next, add the values you want to divide using a forward slash “/,” between. This is how you should get the result:=B2/A2As shown in the screenshot below.
Click Enter to display your desired quotient in the cell that you highlighted.
Excel DATE is denoted=DATE (year, month, and day). This formula returns a date that matches the values in the parentheses, even if they are referred to from other cells. If A1 was 2018, and B1 was 7, then C1 would be 11, so =DATE(A1,B1,C1) would return 7/11/2018.
Sometimes it can be difficult to create dates in Excel spreadsheet cells. There is a simple formula that will make formatting your dates much easier. This formula can be used in two ways:
- You can create dates using a number of cell values.Highlight an empty cell and enter “=DATE” in parentheses. Next, enter the values of the desired date. This will include the year, the month number, and the day. This will create the final format: =DATE (year, month, and day). This is how it looks in the screenshot.
- Automately set today’s date.Highlight an empty cell, and then enter the following text string: =DATE_YEAR(TODAY ()), MOMENT(TODAY ()), DATE(TODAY ())). Enter will return the current date in Excel.
Excel has an array formula that surrounds a simple formula using brace characters and the format.=(Start Val 1:End Val 1)*(Start Val 2:EndVal 2). This will calculate the return value for multiple ranges by pressing CTRL+shift+center. Not just individual cells multiplied or added together, but also from multiple ranges.
It is simple to calculate the sum, product, and quotient of individual cells. Simply use the =SUM formula, then enter the values or cells that you wish to do the arithmetic. What about multiple ranges? How can you calculate the combined value of large numbers of cells?
Numerical arrays allow you to run multiple formulas at once in one cell. This allows you to see one final sum, difference or product. Excel’s array formula is ideal if you want to calculate total sales revenue for several units. Here’s how it would look:
- Type “=SUM” to start the array formula. In parentheses, enter:FirstYou can choose from two, three, or more ranges of cells that you would like to grow together. Here are some examples of your progress:=SUM(C2C5
- Next, add an underscore after the last cell in the formula for the first range. Multiplication is represented by this asterisk. Enter your second range of cells by following the asterisk. This will multiply the second range of cells by 1. This is how you should see your progress in the formula:=SUM(C2C5*D2D2:D5)
- Are you ready to hit the Enter key? You might not be ready to press Enter. Excel has a different keyboard command that Excel uses for arrays. After you have closed the parentheses in your array formula pressCtrl+Shift+Enter. This will recognize your formula and wrap it in brace characters. You can then successfully return your product from both ranges together.
This can reduce your time and effort when you are doing revenue calculations. The screenshot below shows the final formula.
Excel’s COUNT formula is indicated=COUNT (Start Cell:End cell). This formula returns a value equal to the number entries within your chosen range of cells. If eight cells have entered values between A1 to A10, =COUNT (A1:A10) will return 8.
Excel’s COUNT formula is especially useful.LargeExcel spreadsheets are used to determine how many cells have actual entries. Don’t let this fool you:This formula doesn’t compute the values of the cells.This formula simply identifies how many cells are occupied by something in a range of cells.
You can quickly count the active cells within your spreadsheet by using the formula in bold. This is how the result should look like:
Enter the data, cells or range of cells that you want to calculate the average Excel formula.=AVERAGE(number1, number2, etc.)Or =AVERAGE (Start Value:End Valu). This will calculate the average value or range of the cells contained in the parentheses.
Excel allows you to find the average of multiple cells without having to search for individual sums or perform separate division equations on your total. Use Excel to calculate the average of a range of cells.=AVERAGEExcel can be used as the initial text entry.
Referring to reference, the average value of a group is equal to the sum divided by the number items within that group.
Excel’s SUMIF formula is indicated=SUMIF(range, criteria, [sum range]). This will return the summation of all the values in a range of cells that meet one criterion. For example, =SUMIF(C3:C12,”>70,000″) would return the sum of values between cells C3 and C12 from only the cells that are greater than 70,000.
Let’s suppose you need to calculate the profit generated by a list of leads associated with certain area codes or the sum of certain employees salaries. But only if the amounts exceed a specific amount. It can be tedious to do this manually, to put it mildly.
It doesn’t need to be complicated with the SUMIF function. You can quickly add up the number of cells that satisfy certain criteria such as the salary example.
- The formula:=SUMIF (range, criteria, [sum_range]).
- The rangeYour criteria are used to determine the range being tested.
- Criteria:All Criteria_range1 criteria will be combined.
- [Sum_range]You can add an optional range of cells to the first.RangeEnter. This field can be omitted.
The example below shows how we want to calculate the total salary of all the employees who earned more than $70,000. With the formula, the SUMIF function added the dollar amounts above that in cells C3 throughC12.=SUMIF(C3:C12,”>70,000″).
Excel’s TRIM formula is indicated=TRIM (text). This formula will eliminate any spaces between the text and the cell. If A2 contains the name “Steve Peterson” without any spaces, =TRIM(A2) would return the name “Steve Peterson” in a new cell.
File sharing and email are great tools for today’s workplace. This is until one of your coworkers sends you a worksheet that has some very unusual spacing. These rogue spaces can make it hard to find data and also impact the results when you add up columns of numbers.
Instead of manually removing and adding space as necessary, you can use the TRIM function to clean up irregular spacing. This is not a method for manually removing spaces between words.
- The formula:=TRIM (text).
- Text:The cell or text from which you wish to delete spaces
This is an example of how the TRIM function was used to remove spaces from a list of names. To do so, we entered=TRIM (“A2”)Input the Formula Bar and repeat this process for each name below in a new column adjacent to the column with unwelcome spaces.
These are other Excel formulas that you might find helpful as your data management requirements grow.
13. LEFT, MID and RIGHT
Let’s suppose you have a section of text in a cell you wish to split into several segments. Instead of manually typing each code piece into its column, users can use a series string functions to deconstruct it as required: LEFT, MIX, or RIGHT.
- Scope: This is used to extract the first number or character from a cell.
- The formula:=LEFT(text, number_of_characters)
- Text: The string you want to extract.
- Number_of_characters: The number or characters you want to extract, starting with the left-most character.
In the following example, we enter=LEFT(A2,4)In cell B2, copy it into B3-B6. This allowed us to extract the first four characters of the code.
- Scope: This is used to extract characters and numbers from the middle, based on their position.
- The formula:=MID(text, start_position, number_of_characters)
- Text: The string you want to extract.
- Start_position: The position from which you wish to extract the most information. The first position, for example, is 1.
- Number_of_characters: The number characters you want to extract.
In this case, we entered=MID(A2,5,2)In cell B2, copy it to B3:B6. This allowed us to extract two numbers that begin in the fifth position.
- Scope:This is used to extract the last characters or numbers from a cell.
- Here’s the formula:=RIGHT(text, number_of_characters)
- Text: The string you want to extract.
- Number_of_characters: The number you wish to extract from the right-most character.
This formula is an oldie but a great one. It’s also a lot more detailed than the others we have listed. It’s particularly useful for when you have two sets data from two spreadsheets and you want to combine them into one spreadsheet.
Rachel Sprung, my colleague — Rachel’s “How to Use Excel” tutorial is a must read for anyone who wants to learn Excel — uses a list with names, email addresses, companies as an example. VLOOKUP is a great tool for displaying names, email addresses and company names.
You must ensure that at least one column appears identically in both spreadsheets when using this formula. You should check your data sets to ensure that the column you are using to combine your data is identical, with no additional spaces.
- Here’s the formula:VLOOKUP(lookup value, table array, column number, [range lookup])
- Check out the Value CalculatorYou should use the same value in both spreadsheets. The first value you have in both spreadsheets. This is the first value in your first spreadsheet. In Sprung’s example, cell 2 (C2) would be the appropriate choice.
- Table array:The number of columns in Sheet 2 from which you will pull your data, including the column that is identical to your lookup value (in this example, email addresses). Also, Sheet 1’s column of data that you want to copy to Sheet 1. This is Sheet2!A:B in our example. A refers to Column A on Sheet 2. This is the column where data that is identical to our lookup value (email), is listed. Column B is the “B”, which refers to information found only in Sheet 2 and that you wish to translate to Sheet 1.
- Column number:Excel’s table array indicates where (in which column) you want to copy data to Sheet 1. This would be column number 2, the “House” column in our example.
- Range lookupTo ensure that you only get exact value matches, use FALSE
- Below is the formula using variables taken from Sprung’s Example: =VLOOKUP(C2,Sheet2!A:B,2,FALSE)
Sheet 1 and Sheet 2, respectively, contain lists that describe different information about the same person. The common thread is their email addresses. Let’s suppose we want to combine both datasets to ensure that all information about the houses in Sheet 2 is transferred to Sheet 1. Let’s look at how it would work.
There’s a great article that likens Excel’s RANDOMIZE formula to shuffling a deck of cards. Each card in the deck, 52 cards in total, is a column. Steve McDonnell writes that “to shuffle the deck, you can compute a new column, populate each column with random numbers, and then sort the workbook according to the random number field.”
This feature is useful in marketing when you need to assign random numbers to contacts. For example, if you are trying out a new email campaign but had to choose blindly who would get it. You could assign numbers to these contacts and then apply the rule “Any contact with a number of 6 or more will be added to this new campaign.”
- Here’s the formula:RAND()
- Begin with one column of contacts. Next, start with a single column of contacts.
- Take a look at the following example:RANDBETWEEN(bottom, top)
- RANDBETWEEN lets you specify the number of numbers you wish to assign. I chose to use 1 through 10 in this case.
- Bottom:The lowest number in this range.
- Top:The maximum number in the range
- Below is an example of the formula:=RANDBETWEEN (1,10)
Helpful stuff, right? The icing on top: Once you have mastered the Excel formula, you can replicate it in other cells without having to rewrite the formula. There’s also an Excel function that can do this. It’s available below.
Enter a formula in Excel to insert it into a column. Highlight the cell in question and double-click its bottom-right corner to copy it into all the columns below.
Sometimes you may want to use the same formula in every row or column of your spreadsheet. For example, let’s say you have a list with numbers in columns A, B, and you want to enter the individual totals for each row in column C.
It would be tedious to adjust each cell’s values using the formula so that you find the sum of all the numbers in each row. Excel makes it easy to automatically compare the columns. All you need to do is to enter the formula in your first row. These are the steps to follow:
- To find the sum of the cells C2 and B2, enter your formula in an empty cell. src=”https://blog.hubspot.com/hs-fs/hubfs/sum-formula-two-columns.png?width=415&name=sum-formula-two-columns.png”/>
- Move your cursor to the bottom-right corner containing the formula. A small, bold “+” symbol will appear.
- Double-clicking this symbol will fill the entire column with the formula. However, you can click and drag your mouse down to only fill a particular length of the column. Simply check that each value corresponds with the correct cells.
1. Select rows, columns or the entire spreadsheet quickly.
Maybe you are short on time. Who isn’t? No time, no problem. Select the entire spreadsheet with one click. All you have to do is simply click the tab in the top-left corner of your sheet to highlight everything all at once.
Do you just want to select all the rows in a column? These shortcuts make it easy:
- Select ColumnCommand + Shift + Up/Down
- Select Row =Command + Shift + Left/Right
- Select ColumnControl + Shift + Up/Down
- Select Row =Control + Shift + Left/Right
This shortcut is especially helpful when you’re working with larger data sets, but only need to select a specific piece of it.
2. You can quickly open, close, and create a workbook.
Do you need to quickly open, close or create a new workbook? The following keyboard shortcuts will enable you to complete any of the above actions in less than a minute’s time.
- Offen =Command + O
- Close =Command + W
- Make a New =Command + N
- Offen =Control + O
- Close =Control + F4
- Make a New =Control + N
Do you have raw data that needs to be converted into currency? The solution to your problem is easy, regardless of whether it’s salary figures, marketing budgets or ticket sales for an event. Highlight the cells that you want to reformat and then selectControl + Shift + $.
The numbers will automatically translate into dollar amounts — complete with dollar signs, commas, and decimal points.
This shortcut works for percentages as well. You can label a column with numerical values “percent” by replacing “$” with “%”.
4. Insert the current date and time in a cell.
Whether you’re logging social media posts, or keeping track of tasks you’re checking off your to-do list, you might want to add a date and time stamp to your worksheet. Select the cell in which you wish to add the information.
Depending on the content you wish to include, choose one of these options:
- Insert current date =Control + ; (semicolon)
- Insert current time =Control + Shift + (semicolon)
- Insert current date and/or timeControl +(semicolon)SPACE, thenControl + Shift +(semicolon).
1. You can customize the color of your tabs.
You can make it easier to find the right tabs if you have a lot of sheets in your workbook. You might, for example, label the marketing reports from last month with red and the ones this month with orange.
Just right-click a tab to select“Tab Color.”You will see a popup that allows you choose from an existing theme or create one.
2. 2.Add a comment to a Cell
To make a comment or take a note in a particular cell of a worksheet, click on the right-click icon and then clickAdd Comment. To save your comment, type it in the text box and click outside of the comment box.
Comment cells will display a small red triangle at the corner. To view the comment, hover over it.
You probably know that formatting sheets is not the most fun activity. It can be tedious.
It’s unlikely that you’ll want to do this again next time. Excel’s Format Painter allows you to easily copy formatting from one area to another.
Choose the item you want to reproduce, and then choose the option.Format PainterThe dashboard will display the option — the paintbrush icon. The pointer will display a paintbrush and prompt you to choose the cell, text or entire worksheet you wish to apply this formatting.
4. Identify duplicate values.
If duplicate values are not corrected, it can lead to problems in many cases. You don’t have to be aware of duplicate values in all cases.
No matter what the situation, you can easily find duplicate values in your worksheet by following a few simple steps. Click here to do this.Conditional FormattingChoose from the following optionsHighlight Cell Rules > Duplicate Values
Using the popup, create the desired formatting rule to specify which type of duplicate content you wish to bring forward.
The example below shows how we used yellow to format duplicate cells and identify duplicate salaries within the range.
Excel’s use in marketing is almost inevitable. But with these tricks it doesn’t have to be so difficult. It’s true that practice makes perfect. These shortcuts and formulas will become second nature as you practice them.
Editor’s note: This post was originally published in January 2019 and has been updated for comprehensiveness.