How To Use Excel
- Other Tips for Using Excel. Once a spreadsheet has been formatted with the data that you would like to see, and the necessary formulas, entering and comparing data is relatively simple.
- Learn how to use Microsoft Excel, free online tutorial. In this video I am using the 2013 version but they are basically the same. This is a basic excel tu.
These options appear when you use the right-click method. When you use the Ribbon, your options are right there in the Ribbon. It all depends on which is easier for you. If you have been using Excel for a while, the right-click method, with the resulting number format dialog box (shown below) may be easier to understand.
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.
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.
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:
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.
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: 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.
How To Use Excel To Calculate
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:
How To Use Excel For Budgeting
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.
How To Use Excel Solver
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.
How To Use Excel To Make A Graph
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.
How To Use Excel Spreadsheet
6. Click Ok.
7. Click Ok.