Prophet CRM is seamlessly integrated with Microsoft Outlook.

Participate in a demo personalized to your needs with our CRM experts. We will provide you with valuable resources, tips and tricks for Outlook, in addition to an overview of how Prophet will organize, accelerate & automate your sales team and processes.

Excel offers a matrix platform where you can enter and transform data into formatted information. Whether you want to create charts, analyze trends in data points, clean data, automate a task, or run a complex system of equations, Excel can help you quickly get it done.

Whether you’re a beginner, power user, or somewhere in between, this Excel resource can be used again and again to brush up.

Chapter 1: The Basics: Setting the Foundation

Want to add a long row of numbers? Need a simple way to calculate a sales quote? Excel can help. It’s a nifty piece of software that takes the pain and stress from number crunching.

To be able to use Excel for calculations effectively, you must have a good idea of how formulas work.

What are Excel formulas?

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.

Where can you find them?

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.

What are the advantages of learning Excel formulas?

Parts of a function

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.

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

Examples:

=AVERAGE(E4:E9)

=SUM(D4,D9)

=COUNT(C4:C9,C17:C21)

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

Function Library

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.

Marketers, for instance, can get by with the 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.

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.

Insert Function

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.

An alternate way to enter a function from the Function Library is to:

Click on the cell you want to apply the function to

Click on the Formulas tab

Enter an equal (=) sign

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

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.

Now that you are familiar with the concept of Excel formulas, we will review the most commonly used formulas.

Section 1: Quick review of the basics

Addition in Excel can be carried out easily through the SUM function.

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.

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.

Unlike addition, subtraction does not have a dedicated formula inbuilt in Excel. You can, however use the SUM function for 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.

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.

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

Multiplication in Excel can be carried out easily by the PRODUCT formula.

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.

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.

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 inbuilt 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.

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.

Section 2: The IF Function

1. COUNTIF function

COUNTIF is a Conditional Function inbuilt 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 has 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.

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.

In cell E4, type =COUNTIF(B2:B41, “>=10”) – COUNTIF(B2:B41, “>20”) and hit Enter. The function returns ’18’ as the result in cell E4.

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.

Section 3 – IFS Function

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.

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 the 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. And finally, calculates the number of cells in the D2 to D41 where the action in “Phone Call”. The result returned is the number of times all three criteria were met in their respective cell ranges.

Section 4 – SumIFS Function

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 a specific criteria.

The SumIF function improves the SUM function by giving you the freedom to add data that meets a 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.

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

Chapter 3: Powerful Time Saving Excel Functions

Section 1: Pivot Tables

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.

Click on any single cell in the data table.

Go to the ‘Insert’ tab and click ‘PivotTable’.

This dialog box will appear:

4. 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.

The pivot table generated will look like this:

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

Click on the sort drop-down arrow on the Agent/Row Label column.

Click on ‘More sort options.’

3. Choose 'Descending.'

4. 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.

3. 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.

Section 2: Filtering

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:

1. The top rows of each column have a heading

2. The data in each column is consistent, without any mixing of text and numbers

3. There aren’t any blank rows or columns

4. 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.

1. Click on the filter button next to sales column.

2. Go to ‘Number Filters’ and select the ‘Greater than’ option.

3. 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.

4. Click Ok.

5. More than 10 minutes were spent on 16th, 17th, 18th and 19th February.

6. To go back to the original table, click on the filter arrow on the Time Spent column and select 'Clear Filter from Time Spent'.

Section 3: Conditional Formatting

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.

1. Select the range B2:B41.

2. On the ‘Home’ tab, click on ‘Conditional Formatting’. A drop-down menu will appear.

3. Hover on ‘Highlight Cell Rules’. Another drop-down menu will appear.

4. Click on ‘Greater Than’. A dialog box will appear.

5. Enter 10.00 and choose ‘Red Text’ from the drop-down menu next to it

6. Click OK

7. The cells with Time Spent more than 10.00 are highlighted.

8. To remove the highlights, click on ‘Conditional Formatting’ once again.

9. Hover over ‘Clear Rules’. A drop-down menu will appear.

10. Click on ‘Clear Rules from Entire Sheet’.

1. With the range already selected, follow steps (2) and (3) in the above example.

2. Hover on ‘Top/Bottom Rules’. A drop-down menu will appear.

3. Click on “More Rules”. A dialog box will appear.

4. Click on Format only values that are above or below average. In the Edit Rules Description: section, select “above” from the drop-down.

5. Click the Format button and select a unique format option for the values you want to be conditionally formatted.

6. Click Ok.

7. Click Ok.

or call 1-800-399-8980 24 hours!

Success/Error Message Goes Here

"We decided to switch to Prophet, and were impressed with how easy it was compared to the other solutions out there. I just open my e–mail and have all the production schedules, customer tracking, and analysis I needed, right there at my fingertips. Because of Prophet, we saw a sales increase of over 30% in the first 6 months. I would definitely recommend it to any small manufacturing company."

- Frank Mueller, CEO R&R Design

"Each salesperson is like their own business. I tell them to look at their business, understand their business, and take responsibility for it. Prophet helps each of us do just that. It keeps us focused so we don’t get lost in the day-to-day. In our business, it is grow or die. And Prophet helps us grow."

- Charlie Frysinger, President Tyoga Container Company

"As we consider expanding CRM systems to our other affiliates our familiarity with Prophet and comfort level with Avidian’s expertise made the analysis easier. It just works, and their supporting services are good — which is refreshing considering the everyday challenges my team face as an IT service provider."

- Eric Shelton, VP Information Technologies

See More Testimonials