How To Use Excel

Return to site

How To Use Excel

Excel 2013 training. LinkedIn Learning Excel training. Excel templates. Office templates. Get support Contact us.
How To Use Excel
How To Use Excel 13
Without doubt, an Excel spreadsheet is one of the most advanced tools for working with raw data—and one of the most feared. The application looks complicated, way too advanced, and like something that would take hours to figure out.
Entering Data Into Your Worksheet. Entering data into worksheet cells is always a three-step.
If you want to use an Excel template (e.g., a budget planner template), scroll down until you find the template you want to use and then click it once to open its window. If you just want to open a new blank Excel document, click the Blank option in the upper-left side of the page and then skip the next step.
Apart from using Excel as a database for storing business data, it can also be used for doing various operations on your data. Excel can do various mathematical operations – like summing up sales over a period, logical operations – like comparing values and statistical operations – like finding maximum, minimum, average sales for various period.
Learn how to use Excel with this step-by-step tutorial. As full disclosure, I work at Microsoft as a full-time employee.Here's the workbook I used throughout.
I wouldn't be surprised if upon hearing that you had to start using MS Excel, your heart started to pound. Is there any way to make Microsoft Excel less scary and intimidating? Yes.
By learning a few spreadsheet tricks, you can bring Excel down to your level and start looking at the application in a different light. We rounded up some of the simplest yet powerful MS Excel spreadsheet tips you can start using on your data.
1. Use MS Excel Format PainterTo start you off, get yourself familiar with formatting your spreadsheet cells. A visually organized spreadsheet is highly appreciated by others as it can help them follow your data and calculations easily. To quickly apply your formatting across hundreds of cells, use the Format Painter:
Select the cell with the formatting you wish to replicate
Go to the Home menu and click on the Format Painter. Excel will display a paintbrush next to the cursor.
While that paintbrush is visible, click to apply all of the attributes from that cell to any other.
To format a range of cells, double-click the Format Painter during step 1. This will keep the formatting active indefinitely. Use the ESC button to deactivate it when you're done.
2. Select Entire Spreadsheet Columns or RowsAnother quick tip– use the CTRL and SHIFT buttons to select entire rows and columns.
Click on the first cell of the data sequence you want to select.
Hold down CTRL + SHIFT
Then use the arrow keys to get all the data either above, below or adjacent to the cell you're in.
You can also use CTRL + SHIFT + * to select your entire data set.
3. Import Data Into Excel CorrectlyThe benefit of using is Excel is that you can combine different types of data from all kinds of sources. The trick is importing that data properly so you can create Excel drop down lists or pivot tables from it.
Don't copy-paste complex data sets. Instead, use the options from the Get External Data option under the Data tab. There are specific options for different sources. So use the appropriate option for your data:
4. Enter The Same Data Into Multiple CellsAt one point, you may find yourself needing to enter the same data into a number of different cells. Your natural instinct would be to copy-paste over and over again. But there's a quicker way:
Select all the cells where you need the same data filled in (use CTRL + click to select individual cells that are spread across the worksheet)
In the very last cell you select, type in your data
Use CTRL+ENTER. The data will be filled in for each cell you selected.
5. Display Excel Spreadsheet FormulasJumping into a spreadsheet created by someone else? Don't worry. You can easily orient yourself and find out which formulas were used. To do this, use the Show Formulas button. Or you can use CTRL + ` on your keyboard. This will give you a view of all formulas used in the workbook.
6. Freeze Excel Rows And ColumnsThis is a personal favourite of mine when it comes to viewing lengthy spreadsheets. Once you scroll past the first 20 rows, the first row with the column labels annoyingly disappear from view and you begin to lose track of how the data was organized.
To keep them visible, use the Freeze Panes feature under the View menu. You can opt to freeze the top row or, if you have a spreadsheet with numerous columns, you can opt to freeze the first column.
7. Enter Data Patterns InstantlyOne great feature in Excel is that it can automatically recognize data patterns. But what's even better is that Excel will let you enter those data patterns to other cells.
Simply enter your information in two cells to establish your pattern.
Highlight the cells. There will be a small square in the bottom right hand corner of the last cell. 
Place your cursor over this square until it becomes a black cross. 
Then click and drag it with your mouse down to populate the cells within a column
8. Hide Spreadsheet Rows and ColumnsIn some cases, you may have information in rows or columns that are for your eyes only and no one else's. Isolate these cells from your work area (and prying eyes) by hiding them:
Select the first column or row in the range you want to hide.
Go to Format under the Home menu.
Select Hide & Unhide>Hide Rows or Hide Columns. 
To unhide them, click on the first row or column that occur just before and after the hidden range. Repeat steps 2 and 3, but select Unhide Rows or Unhide Columns.
9. Copy Formulas Or Data Between WorksheetsAnother helpful tip to know is how to copy formulas and data to a separate worksheet. This is handy when you're dealing with data that's spread across different worksheets and requires repetitive calculations.
With the worksheet containing the formula or data you wish to copy opened, CTRL + click on the tab of the worksheet you want to copy it to.
Click on or navigate to the cell with the formula or data you need (in the opened worksheet).
Press F2 to activate the cell.
Press Enter. This will re-enter the formula or data, and it will also enter it into the same corresponding cell in the other selected worksheet as well.
These general tips won't turn you into an Excel guru overnight. But they can help you take that first step towards becoming one! Are you a well-seasoned Excel user? Which of your spreadsheet tricks would you add?
Seven Secrets of Excel. Top Tips and Tricks for Microsoft Excel 2016
The topics covered will be useful regardless of your level of Excel skills. Guaranteed or your money back! Actually, there is no charge. I provide these Webinars as a service to help people become more productive. Learn how to better Understand Key Excel Functions, Maximize your Excel Skills in Minutes, Save Valuable Time, Enhance Your Productivity, Impress Your Friends….Get a Better Job!
This is great Webinar for Prophet CRM users, since Prophet makes it easy to export from Excel.
When: Thursday, October 29th at 11:00am Pacific (12:pm Mountain, 1:00pm Central, 2:00pm Eastern)
Seven Secrets of Excel
In this webinar you will learn:
How to Create a Decision-Making Tool in Excel 
How to Create Dropdown Lists & Quote Forms
Detecting and Removing Duplicates
Conditional Formatting in Excel
Combining Data in Excel (First Name, Last Name)
Basic Excel Functions (Formatting Tips, Common Formulas, Linking Cells)
Creating Powerful Charts in Excel – Quickly

All within Microsoft Excel!
Space is limited and is expected to be full soon!
Chapter 1: The Basics: Setting the Foundation 

Chapter 2: Dig Deeper and Gain Insights with Formulas 

Chapter 3: Powerful Time Saving Excel Functions
​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. 
How to use Excel – The top formulas and functions you need to know 

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? 

They're faster than a calculator – Excel stores information as a formula or a value. The value is constant – such as ‘name', ‘cost of 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. 
You can create custom formulas – 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. 
You can fix broken formulas or use new ones quickly. –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. 

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. 
Equals (=) –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. 
Function name –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. 
Argument –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 the not even a single string of characters in the function statement exceed 255 characters. 
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) 
Colons –Colons apply the formula to the indicated range of cells. For instance, the first formula above will calculate the average of contents of the cell ranging from E4 through E9. 
Commas –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. 
How To Use ExcelThe 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. 
Chapter 2: Dig Deeper and Gain Insights with Formulas 

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 

1. Sum Function 

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

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. 
How it works 

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. 
Example 1: Consider the sales data from client 1 – Acme Paper Company.
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. 
Example 2: Consider the sales data from client 1 – Acme Paper Company
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 till H9. It will automatically fill cells H5, H6, H7, H8 and H9 with respective sums. 
2. Subtract 

What it is 

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. 
How it works 

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. 
Example 1: Consider the sales data from client 1 – Acme Paper Company
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: 

How To Use Excel 13Type =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. 
Example 2: Consider the sales data from client 1 – Acme Paper Company:
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 subtraction of numbers in a range, the use of the SUM function is recommended. 
3. Multiply 

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

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. 
How it works 

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. 
Example 1: Consider the sales data from client #2 – Smith and Sons Plumbing
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. 
Example 2: Consider the sales data from client #2 – Smith and Sons Plumbing

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

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. 
How it works 

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. 
Example: Consider the sales data from client #1 – Acme Paper Company
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. 

Section 2: The IF Function

1. COUNTIF function
What it is 

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). 
How it works 

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. 
Example: Say you have a worksheet the date, time spent, agent name and action is recorded for the workweek (five days).
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 by 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 longer than 20 minutes from the number. 
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. 
2. SUMIF function 

What it is 

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). 
How it works 

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. 

Example: Let's get back to our sales data from client #1 – Acme Paper Company for this example.
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. 

Section 3 – IFS Function
What is it? 

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}…) 
How does it work? 

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. 

Example: Let's consider the records created under functions data for this example.
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 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. 
Method 2: 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. 
Let's take another example.
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. 

Section 4 – SumIFS Function
What is it? 

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. 
How does it work? 

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. 
Example: Consider the same records created under functions data.
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 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. 

Chapter 3: Powerful Time Saving Excel Functions 


Section 1: Pivot Tables
What is it? 

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. 
How does it work? 

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. 
Example 1: Consider the same records created under functions data.
Goal:  Sd host adapter driver. 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'. 
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). 
Example 2: Let's rearrange the data in the pivot table using the ‘Sort' function. It is indicated by the drop-down arrow on the row and column labels. 

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

How To Use Excel 13Type =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. 
Example 2: Consider the sales data from client 1 – Acme Paper Company:
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 subtraction of numbers in a range, the use of the SUM function is recommended. 
3. Multiply 

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

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. 
How it works 

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. 
Example 1: Consider the sales data from client #2 – Smith and Sons Plumbing
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. 
Example 2: Consider the sales data from client #2 – Smith and Sons Plumbing
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. 
4. Divide 

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. 
How it works 

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. 
Example: Consider the sales data from client #1 – Acme Paper Company
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. 

Section 2: The IF Function

1. COUNTIF function
What it is 

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). 
How it works 

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. 
Example: Say you have a worksheet the date, time spent, agent name and action is recorded for the workweek (five days).
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 by 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 longer than 20 minutes from the number. 
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. 
2. SUMIF function 

What it is 

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). 
How it works 

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. 
Example: Let's get back to our sales data from client #1 – Acme Paper Company for this example.
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. 

Section 3 – IFS Function
What is it? 

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}…) 
How does it work? 

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. 

Example: Let's consider the records created under functions data for this example.
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 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. 
Method 2: 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. 
Let's take another example.
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. 

Section 4 – SumIFS Function
What is it? 

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. 
How does it work? 

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. 
Example: Consider the same records created under functions data.
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 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. 

Chapter 3: Powerful Time Saving Excel Functions 


Section 1: Pivot Tables
What is it? 

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. 
How does it work? 

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. 
Example 1: Consider the same records created under functions data.
Goal:  Sd host adapter driver. 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'. 
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). 
Example 2: Let's rearrange the data in the pivot table using the ‘Sort' function. It is indicated by the drop-down arrow on the row and column labels. 

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.'
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
What is it? 

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. 
How does it work? 

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 
Example 1: Let's get back to the records created under the functions data and see how to apply AutoFilter. AutoFilter is a handy function to organize cells quickly, especially when you are working with columns containing a long list of data. 

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.
Example 2: Let's now use filtering to find quickly the days on which more than 10 minutes on phone or emails were spent. To determine this, the Time Spent column will have to be filtered. 

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 

What is it? 

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. 
How does it work? 

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. 
Example 1: On the same records as used in the previous function, let's use conditional formatting to highlight Time Spent exceeding 10.00 minutes for the week. 

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'. 
Example 2: Let's use conditional formatting to find and highlight Time Spent that is above the average Time Spent for the week. To see the average, quickly select the range B2:B41. Check the bottom display of the Excel window. The average is automatically generated. Here, it is 10.08. 

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.