An Excel formula is an equation entered into a cell on the spreadsheet software. It is used to perform calculations on the values entered to obtain the desired result. There are numerical/mathematical, statistical, and logical formulas that make easy work of complex calculations.

The formula bar is a top section just above the cells showing the contents of the current/chosen cell and allows you to view and create formulas. You use this bar to start creating a formula.

A good grasp of the elements that go into Excel formulas allows you to use and manipulate numbers and results according to your unique needs. Formula basics are easy to master, and after consistent application, you can move on to more niche formulas for specialized tasks.

Excel stores information as a formula or a value. The value is constant – such as ‘name’, ‘cost of the product’, or ‘quantity’. A formula is dependent on values and changes accordingly. For instance, say the spreadsheet contains a list of quantities of different products purchased by customers and profit on each product. You can add up the profit on all the listed products to calculate the total profit. Perhaps a customer revises his product purchase and now buys more quantities of the product. You just have to enter the new quantity, and the relevant formula will calculate the new profit. With a calculator, you would have had to know what numbers and calculations you had used originally and redo them. With Excel formulas, you can recalculate instantly.

Once you gain proficiency with basic Excel formulas and start using them for more complicated tasks, you may realize the need for a formula that the software does not provide. The preset formulas included in Excel are called functions, and you can create your own. Excel allows you to create custom formulas known as ‘user-defined functions’ or UDFs, for advanced mathematics. With UDFs, you can create a custom math function, simplify long formulas (mega formulas) by breaking them down into smaller chunks, and perform diagnostics, such as assessing cell formats.

When formulas throw up errors or unintended results, you may be at a loss for what to do next. The error message ‘There’s a problem with this formula’ calls for an investigation. One or more things could have gone wrong with your formula. You may have made a typing mistake, added mismatched parentheses, or referenced data in ranges that don’t exist. A good working knowledge of basic Excel formulas can help you fix broken formulas quickly. Picking up new formulas also becomes easier if you have a strong foundation to build from.

A function is a predefined formula that performs calculations using values indicated in a particular order. To use functions correctly, you’ll need to be aware of the different parts of a function.

The specific way in which a function is written is referred to as syntax. The syntax for a function is: An equals sign (=), the function name (SUM, for instance), and one or more arguments. Let’s look at these three parts individually.

A formula in Excel always starts with an equal sign (=). It informs Excel that the succeeding characters represent a formula. Everything placed after the equals sign will be used to calculate the final value displayed in the cell.

This is the name denoting a particular mathematical, logical, or statistical function. For instance, SUM is the function name for addition, MAX and MIN are function names for the largest and smallest results from a range of numbers, and IF is used to determine whether a statement is True or False.

Arguments supply the information for the function to calculate, such as a range of cell references. You can include a maximum of 1024 arguments in a function provided that not even a single string of characters in the function statement exceeds 255 characters.

Arguments must be enclosed within parentheses. The individual values or cell references inside the parentheses are separated by commas or colons.

=AVERAGE(E4:E9)

=SUM(D4,D9)

=COUNT(C4:C9,C17:C21)

Colons apply the formula to the indicated range of cells. For instance, the first formula above will calculate the average contents of the cell ranging from E4 through E9.

Use commas when you want to apply a function to specific cells, but not over a range of cells. You can separate the required cell references with commas. For instance, for the formula =SUM(C4, E4), the contents of cells C4 and E4 will be added together.  If you were to use a colon instead, i.e. =SUM(C4:E4)”, the contents of cells C4, D4, and E4 would be added.

The third example above will count the three cells in the three arguments included within the parentheses.

Click on the Formulas tab to access the Function Library. The library houses hundreds of functions. Of these, you may use just a few, depending on the type of data held by your workbooks and the calculations necessary for work.

Excel places the functions you’ve used for calculations on the ‘Recently Used’ list. This is a handy feature if you use a handful of functions for most of your Excel tasks.

Marketers, for instance, can get by with basic financial functions, which can be used to perform calculations ranging from marketing budgets to monthly social media marketing metrics. A good hang of basic Excel functions, therefore, can support marketing goals in a meaningful way.

Investment professionals may find many of the 50+ financial functions – which calculate the future value of an investment, the yield on security, and loan repayments – among others, useful.

On the other hand, the engineering, scientific, and academic community may leverage the Math & Trig, Engineering functions, and Statistical functions.

Choose the cell to which you want to apply the function. Click on the Insert Function tab on the formula bar. Excel will insert the equals (=) sign in the formula bar and simultaneously open the ‘Insert Function’ dialog box. Here, you can select a category and function to proceed. If you are not sure of the function, you can search for it in the top search box by entering a relevant description of what you want to do.

1. Click on the cell you want to apply the function to.

2. Click on the Formulas tab.

3. Enter an equal (=) sign.

4. Click the appropriate tab from the library containing the function you want to use.

5.  Excel inserts the selected function into the formula box with parenthesis and opens the ‘Function Arguments’ dialog box. You can then enter the argument or select the cells or range of cells to obtain the answer.

SUM is a standard built-in function in Excel that performs the basic mathematical operation of addition. You can use it to add as many numbers in your worksheet as you like, provided they be all located in the same row or column.

The SUM function requires you to select an array of cells the values of which you want to add. After selection, proceed to the toolbar and click on the ‘Formulas’ tab. Under this tab, you’ll see the ‘Function Library’, which has an ‘AutoSum’ command. From the drop-down menu of the ‘AutoSum’ command, select the ‘SUM’ option. Excel will add the values from the cells and display the sum just adjacent to, or below the last cell of your selected array.

There’s another way to use the SUM function for addition – manually typing the SUM formula in the appropriate cell. For this, type “=SUM” in the cell and then select the range of cells, the values of which you want to add. Alternatively, you can type the entire formula for SUM as well. Let’s see it as an example.

Goal: To calculate the subtotal of all items ordered.

Solution: You can calculate the sum in any of the following ways:

• In cell E10, type =SUM(E4: E9) and press enter. It will show your total in cell E10.
• In cell E10, type =SUM and then use your mouse to manually select the cells from E4 to E9. Press Enter upon selection.
• Alternatively, begin by manually selecting the cells and then, use the toolbar to find the ‘SUM’ option, as discussed above.

Goal: To calculate the sum of unit price and subtotal for each item ordered individually. Thus, for Legal Sized Paper, you want to calculate the unit price and the subtotal.

Solution: Again, you can use any of the three ways discussed above. However, instead of entering the SUM function in cells H4, H5, H6, H7, H8, and H9 separately, you can simply copy the sum function from cell H4. To do this, type the formula in cell H4 then click and drag the bottom right-hand corner of the cell, down to H9. It will automatically fill cells H5, H6, H7, H8, and H9 with respective sums.

Unlike addition, subtraction does not have a dedicated formula built in Excel. You can, however, use the SUM function for the subtraction of two or more numbers in an array.

The minus operator (-) is used in the SUM function to obtain the result of the subtraction, in this case. Use of the minus sign before the numbers you want to subtract turns them into negative numbers. Since the addition of any negative number to a positive number is the same as its subtraction from the positive number, you get the desired results.

Goal: To subtract the unit prices of Blue Pens and Red Pens from the unit price of Letter Sized Paper.

Solution: You can do so in any of the following ways:

• Type =SUM( -D8, -D9, D5) in cell D11 and hit enter. The subtracted result (33.66) will appear in D11.
• In cell D11, type =SUM(  to begin the formula. Next, type the minus operator in the bracket and click on cell D8. Similarly, enter the minus sign again and click on cell D9 using the mouse. Repeat till all cells have been selected individually. Press Enter to view the result.

Goal: To subtract the subtotal of Sticky Notes (cell E6) from its unit price (cell D6)

Solution: Another approach to finding the result is the use of the minus operator alone (without the SUM function that is). To obtain the result of the subtraction, simply type =D6-D5 in cell H6 and hit enter. It displays the answer (-36.06) in H6.

For the subtraction of numbers in a range, the use of the SUM function is recommended.

PRODUCT is a standard built-in function in Excel that performs the mathematical operation of multiplication. The formula can be used to multiply two or more numbers from a range of cells, with each other. It can also be used to multiply the numbers contained in different cells with a standalone constant.

Just like the SUM function, PRODUCT requires you to provide a range of cells from your worksheet. You can choose the cells using your mouse, refer to cells using their location, or, enter the values directly. The PRODUCT function multiplies the values in the parenthesis and returns the result of the multiplication upon hitting Enter.

Goal: To find out the subtotal of individual office supply items sold.

Solution: You can find the subtotal for each item by multiplying the unit quantity with the unit price for each item. This can be done in any of the following ways:

• Type =PRODUCT(C17, D17) in cell E17. Hit enter. Now, click on the bottom right corner of E17 and drag down till E21 to get the subtotal for each item separately.
• Type =PRODUCT in cell E17 and manually select the cells using your mouse.

Goal: To calculate the cost of purchasing five units of each office supply item.

Solution: The unit price of each item can be multiplied with the constant ‘5’ to obtain the cost.

Type =PRODUCT(D17, D21, 50) in cell H17 (Say) and press enter. You know the drill after that, don’t you? Click on the bottom corner of the cell and drag it down to cell H21 to get the product for each item.

Note: For the multiplication of two numbers, you can directly use the mathematical operator ‘*’ used for multiplication. So to multiply 34 and 44, type =(34*44) in the cell.

Unlike Multiplication, there is no built-in formula in Excel for division. You can, however, use the standard mathematical operator ‘/’ along with cell references to get the result of division.

Place the division symbol, either directly with numbers or with cell references, in the parenthesis after the ‘equal to’ sign. Hit Enter to get the result of the division.

Goal: To find the number of Sticky Notes sold when you have data pertaining to the unit price and the subtotal only.

Solution: Divide the subtotal of the sticky notes sold by the unit price.

Type =(E6/D6) in the cell where you want the answer to go. Hit Enter. And voila! The result of the division appears instantly in the chosen cell.

COUNTIF is a Conditional Function built in Excel, which counts the number of cells that meet a certain criterion. You need to specify the criterion and the range of cells within which Excel must check whether or not the criteria have been met. The syntax of the COUNTIF function appears as follows – COUNTIF(range; criteria).

Within the range specified by you, COUNTIF checks the value in each cell against the criteria. For every cell that matches the criteria, the count is increased by one. For instance, to calculate the number of entries greater than 500 in your worksheet, you can use the COUNTIF function. Your criteria here would be >500 and the function will count the number of cells within your specified range if the value inside the cell is greater than 500.

Goal: To calculate the number of emails sent in the week.

Solution: You can find the number of emails sent by calculating the number of cells from cells D2 to D41, which have data for “email”.

In cell E2, type =COUNTIF(D2:D41,D41) and hit Enter. Cell E2 will show the result as ’20’. So, there were 20 emails sent in the workweek.

Goal: To calculate the number of marketing communications sent either by Matt or Mike.

Solution: In cell E3, type =COUNTIF(C2:C41,C3) + COUNTIF(C2:C41,C11)  and hit Enter. The function will return ’20’ as the result.

Goal: To calculate the total number of marketing communications (phone call or email) where agents spent between 10 and 20 minutes (both limits inclusive).

Solution: You can arrive at the result by counting the number of conversations that were 10 minutes or longer, and then subtracting the number of conversations that will be longer than 20 minutes from the number.

SUMIF is another inbuilt Conditional Function that performs mathematical operations on cells that meet a certain criterion. The function adds the values of cells that match your condition. The syntax is – SUMIF(range, criteria, sum_range).

The first argument Range specifies the range of cells on which you want the SUMIF function to work. The second argument Criteria specifies the condition that must be met for a cell to be included in the sum. The final argument Sum_Range is the range of cells that will be finally added up.

It is mandatory to provide the first two arguments. You may choose to leave the third one out. The third argument is typically used when you want to check the criteria for one range and add values from a different range.

For instance, the command =SUMIF(B3:B6, “>10”) will check for the criterion in cells B3 through B6 and add appropriate values from the same range. On the other hand, the command =SUMIF(B3:B6, “apples”, C3:C6) will check for the criterion in cells B3 through B6 and then add the corresponding values from cells C3 through C6.

Goal: To calculate the total sales for those items whose individual sale was greater than $3.25.

Solution: In cell D11 (or the cell where you want the answer), type =SUMIF(D4:D9,”>3.25″) and hit Enter. The function will return $59.91 as the result in cell D11.

COUNTIFS is a function found in Microsoft Excel that helps you determine the number of cells in a range that meet a number of conditions. The syntax is – COUNTIFS(criteria_range1, criteria1; [criteria_range2, criteria2}…)

CountIFS adds to the existing CountIF function by permitting you to enter from 2 to 127 criteria unlike CountIF, which permits only 1. The first two arguments in the syntax (criteria_range1) and (criteria1) are required, while the remaining are optional. Criteria_range1 is the range of cells in which you want Excel to evaluate certain criteria. Criteria1 is the certain criteria that defines, which cells will be counted. It can take the form of numbers, cell references, expressions, or text. You can add as many of such criteria, and associated criteria ranges using the optional arguments.

CountIFS work with rows of data, which are referred to as records. In this record, the data in each field or cell in the row is related. For instance, the data could contain information such as a company’s name, address or contact details. CountIFS helps you look for specific criteria in multiple fields in the record and counts the record only when a match is found for each specified field.

Note: Every additional criteria you enter using the optional arguments, must have an associated criteria_range that has the same number of rows and columns as the criteria_range1. In addition, these ranges must not be located next to each other. 

Goal: To calculate the number of times Susan made a phone call for 5 minutes or more.

Solution: You can use the COUNTIFS function to determine – a) the time spent was greater than 5.00 minutes in how many rows, b) the agent was Susan how many times, and c) the marketing action was a phone call, how many times. The COUNTIFS will return the number of times all three conditions were met.

In cell E2, or wherever you want the answer, type =COUNTIFS(B2:B41,”>=5.00″,C2:C41,”Susan”,D2:D41,”Phone Call”). You’ll see the result ‘8’ appear after hitting enter. This function calculates the number of cells in the range B2 to B41 with time spent greater than or equal to 5 minutes. It then calculates the number of cells in the range C2 to C41 where the agent is Susan. Finally, calculates the number of cells in the D2 to D41 where the action is in “Phone Call”. The result returned is the number of times all three criteria were met in their respective cell ranges.

An alternate method is to use the Function Wizard instead of writing the function manually in cell E2. For this, select cell E2, head over to the Wizard and choose the COUNTIFS function. A dialog box will open asking you to provide the different arguments. Enter the values as hit ‘Okay’ to see the answer.

Goal: To calculate the number of emails sent in the first four days of the week.

Solution: You can use the COUNTIF function with two criteria for this problem. The number of cells with “Email” in the range D2 to D41 is the first criteria. As the second criteria, you can count the number of cells with dates before the last day (February 19). Thus, type =COUNTIFS(D2:D41,”Email”,A2:A41,”<=02/18/2015″) in cell E2 and hit enter.

SUMIF function is a combination of the Sum and IF functions found in Excel. What it means is that both these functions combined, are used to add data to cells that meet specific criteria.

The SUMIF function improves the SUM function by giving you the freedom to add data that meets certain criteria. Similarly, the newer SUMIFS function allows you to add data that meets a set of criteria.

The SUMIFS function uses a range for adding values along with one criteria range and a particular criteria. The function allows you to use 127 specified conditions to add data.

The SUMIFS formula syntax is SUMIFS (sum_range; criteria_range1; criteria1; ; …). Here sum_range is the range from where you want appropriate cells to be added up. Criteria_range1 is the range of cells where your first criteria will be checked, and Criteria1 is the first condition.  The first three arguments are required while the remaining are optional.

The best part about the SUMIFS function is that it can work with wildcard data just like the COUNTIF and SUMIF functions. For instance, you can use the function in the following manner:

SUMIFS (D3:D16, B3:B16, “Water Melons”, C3:C16, “*day”) to find out how many watermelons were sold on Monday and Tuesday.

Goal: To calculate the total time spent by Christy on email in the week.

Solution: You can use the SUMIFS function to find the Sum of Time Spent if the Action is Email, and the Agent is Christy. Therefore, in cell E2, or wherever you want the answer, type =SUMIFS(B2:B41, D2:D41, “Email”, C2:C41, “Christy”). Hit Enter and Excel tells you she spent 87 minutes in total on Email.

Goal: To sum up the total time Mike spent on phone calls or emails where individual time spent was 10 minutes or more.

Solution: Type =SUMIFS(B2:B41, B2:B41, “>=10.00”, C2:C41, “Mike”) in any target cell of your choice and hit enter. The answer, ’56’ minutes appears in the cell.

Again, for both problems, you could use the SUMIFS function from the Function Wizard, instead of typing it manually.

When your spreadsheets hold hundreds of rows of data, identifying correlations between different data elements or finding trends can be difficult. With a pivot table, you can tabulate, summarize, and analyze large data sets. If you have to write lots of formulas to summarize data in Excel, a pivot table offers different views of detailed data sets and drills down data to gain insights that cannot be otherwise discovered easily.

A pivot is an interactive summary of data. You can easily change it to get different insights into your data. With a few clicks, you can ‘pivot’ the summary to change row headings into column headings and vice-versa. To create a pivot table, you need data organized in columns with correct headings. The data in all the cells must be consistent. That is if you have a date column, all the values in that column must be dates. Once your data is complete, organized, and ready to be summarized, you can apply a pivot table, as shown in the examples below.

Goal: To apply a pivot table to the dataset and determine, which agent spent the most time making phone calls or sending emails to customers.

Solution:

• Click on any single cell in the data table.
• Go to the ‘Insert’ tab and click ‘PivotTable’.
• Excel will automatically select the data for you. A ‘New Worksheet’ is the default location for a new pivot table. If you want the pivot table to appear on the same worksheet, click the ‘Existing Worksheet’ button. Then, in the ‘Location’ text box, indicate the location of the first cell of the new table (say) cell H1.
• Click OK. Excel will display a PivotTable Field List to the right of the worksheet and a field layout area with four sections under it.
• First, check the fields you want your table to include. We’ve checked Agent and Time Spent.
• Next, drag and drop each field to the area where you want to place it.
• We have moved Agent to the Row Labels area and Time Spent to the Values area.

From the table, you can easily determine that Christy spent the highest amount of time (112 minutes) and Mike the least (88 minutes).

Goal: To sort and place agent with maximum Time Spent on top of the Pivot Table. Then, to compare the ties spent and therefore performance, of Matt and Susan for an upcoming review.

Solution:

• Click on the sort drop-down arrow on the Agent/Row Label column.
• Click on ‘More sort options.’
• Choose ‘Descending.’
• From the drop-down, select “Sum of Time Spent.”
• Click OK.
• The new pivot table shows the best agent Christy (with 112 minutes), followed by Susan, Matt and Mike.

There is just one task left now:

• Click on the sort drop-down arrow on the Row Labels column. At the moment, the time spent by all the representatives is summarized in the pivot table.
• Uncheck the boxes next to all Agents other than Susan and Matt.

Click OK. The pivot table shows the Total Time Spent by Matt (95 minutes) and the Total Time Spent by Susant (108 minutes). So Susan’s performance was better in the week.

Filters narrow down the data in your worksheet so you can view only the information you need. They show only the rows you need to view while hiding others. Filters are helpful in finding information quickly in worksheets with many rows of data. Filtering is different from sorting in that it does not rearrange data, just hides it. You can then edit, format or print out the filtered data as you prefer.

When you add a filter to a spreadsheet’s header row, a drop-down menu appears on each cell of the header row. The menu contains different filter options that you can use to specify the rows you wish to display.

Before you filter the data in a worksheet, make sure that:

• The top rows of each column have a heading
• The data in each column is consistent, without any mixing of text and numbers
• There aren’t any blank rows or columns
• You keep the data to filter in a separate worksheet

• Click on any cell containing the data
• Go to the ‘Data’ tab and select ‘Filter’. Drop-down filter arrows will appear next to each column heading as seen below.
• To start filtering, click on the arrow next to the column heading
• Say you want to find out the marketing communications made on February 16.  Click on the filter button on the Date column.
• Uncheck the ticks from all the dates you do not want to see OR Choose ‘Date Filters’ and select ‘Equals’. Then, use the calendar icon to enter February 5, 2015. You’ll see only the sales data for 5th March.
• To remove the filter and arrows, click ‘Filter’ once again on the ‘Data’ tab.

• Click on the filter button next to sales column.
• Go to ‘Number Filters’ and select the ‘Greater than’ option.
• A ‘Custom AutoFilter’ dialog box opens. Under ‘Time Spent’ choose ‘is greater than’ from the drop-down menu. Then, enter 10.00 in the space next to it.
• Click Ok.
• More than 10 minutes were spent on 16th, 17th, 18th and 19th February.
• To go back to the original table, click on the filter arrow on the Time Spent column and select ‘Clear Filter from Time Spent’.

Spotting trends and patterns in a spreadsheet with several data rows is difficult. Conditional formatting is one of the tools Excel offers to simplify this task by highlighting cells with a certain color.

To quickly identify specific values, you enter certain conditions. Formatting is applied to the cells that satisfy your conditions. The conditions can be the highest or lowest value in the range, the average of a range, the top ten, and more. You can use formatting such as icons, colors, and data bars to highlight the cells.

• Select the range B2:B41.
• On the ‘Home’ tab, click on ‘Conditional Formatting’. A drop-down menu will appear.
• Hover on ‘Highlight Cell Rules’. Another drop-down menu will appear.
• Click on ‘Greater Than’. A dialog box will appear.
• Enter 10.00 and choose ‘Red Text’ from the drop-down menu next to it
• Click OK
• The cells with Time Spent more than 10.00 are highlighted.
• To remove the highlights, click on ‘Conditional Formatting’ once again.
• Hover over ‘Clear Rules’. A drop-down menu will appear.
• Click on ‘Clear Rules from Entire Sheet’.

• With the range already selected, follow steps (2) and (3) in the above example.
• Hover on ‘Top/Bottom Rules’. A drop-down menu will appear.
• Click on “More Rules”. A dialog box will appear.
• Click on Format only for values that are above or below average. In the Edit Rules Description: section, select “above” from the drop-down.
• Click the Format button and select a unique format option for the values you want to be conditionally formatted.
• Click Ok.