To perform the multiplication formula in Excel, enter the cells you're multiplying in the format, =A1B1. This formula uses an asterisk to multiply cell A1 by cell B1. For example, if A1 was 10 and B1 was 6, =A1B1 would return a value of 60. You might think multiplying values in Excel has its own formula or uses the "x" character to denote multiplication between multiple values. Actually, it's as easy as an asterisk -- . To multiply two or more values in an Excel spreadsheet, highlight an empty cell. Then, enter the values or cells you want to multiply together in the format, =A1B1*C1 ... etc. The asterisk will effectively multiply each value included in the formula. Press Enter to return your desired product. See how this looks in the screenshot above.
To perform the subtraction formula in Excel, enter the cells you're subtracting in the format, =SUM(A1, -B1). This will subtract a cell using the SUM formula by adding a negative sign before the cell you're subtracting. For example, if A1 was 10 and B1 was 6, =SUM(A1, -B1) would perform 10 + -6, returning a value of 4. Like percentages, subtracting doesn't have its own formula in Excel either, but that doesn't mean it can't be done. You can subtract any values (or those values inside cells) two different ways.
To perform the percentage formula in Excel, enter the cells you're finding a percentage for in the format, =A1/B1. To convert the resulting decimal value to a percentage, highlight the cell, click the Home tab, and select "Percentage" from the numbers dropdown. There isn't an Excel "formula" for percentages per se, but Excel makes it easy to convert the value of any cell into a percentage so you're not stuck calculating and reentering the numbers yourself. The basic setting to convert a cell's value into a percentage is under Excel's Home tab. Select this tab, highlight the cell(s) you'd like to convert to a percentage, and click into the dropdown menu next to Conditional Formatting (this menu button might say "General" at first). Then, select "Percentage" from the list of options that appears. This will convert the value of each cell you've highlighted into a percentage. See this feature below. Keep in mind if you're using other formulas, such as the division formula (denoted =A1/B1), to return new values, your values might show up as decimals by default. Simply highlight your cells before or after you perform this formula, and set these cells' format to "Percentage" from the Home tab -- as shown above.
The IF formula in Excel is denoted =IF(logical_test, value_if_true, value_if_false). This allows you to enter a text value into the cell "if" something else in your spreadsheet is true or false. For example, =IF(D2="Gryffindor","10","0") would award 10 points to cell D2 if that cell contained the word "Gryffindor." There are times when we want to know how many times a value appears in our spreadsheets. But there are also those times when we want to find the cells that contain those values, and input specific data next to it. We'll go back to Sprung's example for this one. If we want to award 10 points to everyone who belongs in the Gryffindor house, instead of manually typing in 10's next to each Gryffindor student's name, we'll use the IF-THEN formula to say: If the student is in Gryffindor, then he or she should get ten points.
Logical_Test: The logical test is the "IF" part of the statement. In this case, the logic is D2="Gryffindor." Make sure your Logical_Test value is in quotation marks. Value_if_True: If the value is true -- that is, if the student lives in Gryffindor -- this value is the one that we want to be displayed. In this case, we want it to be the number 10, to indicate that the student was awarded the 10 points. Note: Only use quotation marks if you want the result to be text instead of a number. Value_if_False: If the value is false -- and the student does not live in Gryffindor -- we want the cell to show "0," for 0 points.
All Excel formulas begin with the equals sign, =, followed by a specific text tag denoting the formula you'd like Excel to perform. The SUM formula in Excel is one of the most basic formulas you can enter into a spreadsheet, allowing you to find the sum (or total) of two or more values. To perform the SUM formula, enter the values you'd like to add together using the format, =SUM(value 1, value 2, etc). The values you enter into the SUM formula can either be actual numbers or equal to the number in a specific cell of your spreadsheet.
In many instances, duplicate values -- like duplicate content when managing SEO -- can be troublesome if gone uncorrected. In some cases, though, you simply need to be aware of it. Whatever the situation may be, it's easy to surface any existing duplicate values within your worksheet in just a few quick steps. To do so, click into the Conditional Formatting option, and select Highlight Cell Rules > Duplicate Values Using the popup, create the desired formatting rule to specify which type of duplicate content you wish to bring forward. In the example above, we were looking to identify any duplicate salaries within the selected range, and formatted the duplicate cells in yellow. In marketing, the use of Excel is pretty inevitable -- but with these tricks, it doesn't have to be so daunting. As they say, practice makes perfect. The more you use these formulas, shortcuts, and tricks, the more they'll become second nature. To dig a little deeper, check out a few of our favorite resources for learning Excel. Want more Excel tips? Check out this post on how to create a pivot table with medians. Editor's note: This post was originally published in January 2019 and has been updated for comprehensiveness.
To perform the average formula in Excel, enter the values, cells, or range of cells of which you're calculating the average in the format, =AVERAGE(number1, number2, etc.) or =AVERAGE(Start Value:End Value). This will calculate the average of all the values or range of cells included in the parentheses. Finding the average of a range of cells in Excel keeps you from having to find individual sums and then performing a separate division equation on your total. Using =AVERAGE as your initial text entry, you can let Excel do all the work for you. For reference, the average of a group of numbers is equal to the sum of those numbers, divided by the number of items in that group.
If you've ever spent some time formatting a sheet to your liking, you probably agree that it's not exactly the most enjoyable activity. In fact, it's pretty tedious. For that reason, it's likely that you don't want to repeat the process next time -- nor do you have to. Thanks to Excel's Format Painter, you can easily copy the formatting from one area of a worksheet to another. Select what you'd like to replicate, then select the Format Painter option -- the paintbrush icon -- from the dashboard. The pointer will then display a paintbrush, prompting you to select the cell, text, or entire worksheet to which you want to apply that formatting, as shown below:
The SUMIF formula in Excel is denoted =SUMIF(range, criteria, [sum range]). This will return the sum of the values within a desired range of cells that all 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 say you want to determine the profit you generated from a list of leads who are associated with specific area codes, or calculate the sum of certain employees' salaries -- but only if they fall above a particular amount. Doing that manually sounds a bit time-consuming, to say the least. With the SUMIF function, it doesn't have to be -- you can easily add up the sum of cells that meet certain criteria, like in the salary example above.
Range: The range that is being tested using your criteria. Criteria: The criteria that determine which cells in Criteria_range1 will be added together [Sum_range]: An optional range of cells you're going to add up in addition to the first Range entered. This field may be omitted. In the example below, we wanted to calculate the sum of the salaries that were greater than $70,000. The SUMIF function added up the dollar amounts that exceeded that number in the cells C3 through C12, with the formula =SUMIF(C3:C12,">70,000").
Let's say you have a line of text within a cell that you want to break down into a few different segments. Rather than manually retyping each piece of the code into its respective column, users can leverage a series of string functions to deconstruct the sequence as needed: LEFT, MID, or RIGHT.