How to make a Time Sheet in Excel

<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> This example teaches you how to create a simple timesheet calculator in Excel. Cells that contain formulas are colored light yellow. If you are in a hurry, simply download the Excel file.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 1. To automatically calculate the next 4 days and dates when you enter a start date, use the formulas below.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="Date Formulas" src="http://www.excel-easy.com/examples/images/time-sheet/date-formulas.png" title="Date Formulas" /></div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 2. Select the cells containing the times.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="Select Time Cells" src="http://www.excel-easy.com/examples/images/time-sheet/select-time-cells.png" title="Select Time Cells" /></div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 3. Right click, click Format Cells, and select the right Time format. Use the circled format for cell K12, K13, K14.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="Time Format" src="http://www.excel-easy.com/examples/images/time-sheet/time-format.png" title="Time Format" /></div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 4. To automatically calculate the hours worked each day, the total hours and the overtime hours, use the formulas below.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="Time Formulas" src="http://www.excel-easy.com/examples/images/time-sheet/time-formulas.png" title="Time Formulas" /></div>

• 
• 
• 
• 

1. 1
2. 2
3. 3
4. 4

• Previous
• Next

How to make a Time Sheet in Excel

This example teaches you how to create a simple timesheet calculator in Excel. Cells that contain formulas are colored light yellow. If you are in a hurry, simply download the Excel file. 1. To autom…

Read more »

How to Calculate the Days of the Year in Excel

<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> An easy formula that returns the day of the year for a given date. There's no built-in function in Excel that can do this.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 1. Enter the formula shown below.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="Day of the Year in Excel" height="110" src="http://www.excel-easy.com/examples/images/day-of-the-year/day-of-the-year.png" title="Day of the Year in Excel" width="604" /></div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> Explanation: Dates and times are stored as numbers in Excel and count the number of days since January 0, 1900. June 23, 2012 is the same as 41083. The DATE function accepts three arguments: year, month and day. DATE(YEAR(A1),1,1) or 1-jan-2012 is the same as 40909. Subtracting these numbers (41083 - 40909 = 174) and adding 1 gives the day of the year.</div>

How to Calculate the Days of the Year in Excel

An easy formula that returns the day of the year for a given date. There's no built-in function in Excel that can do this. 1. Enter the formula shown below. Explanation: Dates and times are stored as…

Read more »

How to Count Blank/Nonblank Cells in Excel

<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> This example shows you how to count the number of blank and nonblank cells in Excel.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 1. The COUNTBLANK function counts the number of blank cells.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="CountBlank Function in Excel" height="270" src="http://www.excel-easy.com/examples/images/count-blank-nonblank-cells/countblank-function.png" title="CountBlank Function in Excel" width="476" /></div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 2. The COUNTA function counts the number of nonblank cells. COUNTA stands for count all.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="CountA Function in Excel" height="270" src="http://www.excel-easy.com/examples/images/count-blank-nonblank-cells/counta-function.png" title="CountA Function in Excel" width="476" /></div>

• 
• 

1. 1
2. 2

• Previous
• Next

How to Count Blank/Nonblank Cells in Excel

This example shows you how to count the number of blank and nonblank cells in Excel. 1. The COUNTBLANK function counts the number of blank cells. 2. The COUNTA function counts the number of nonblank …

Read more »

How to Sum onle the Largest Numbers in Excel

<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> This example shows you how to create an array formula that sums the largest numbers in a range.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 1. We use the LARGE function. For example, to find the second to largest number, use the following function.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="Large Function" height="310" src="http://www.excel-easy.com/examples/images/sum-largest-numbers/large-function.png" title="Large Function" width="410" /></div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 2. To sum the 4 largest numbers (don't be overwhelmed), add the SUM function and replace 2 with {1,2,3,4}.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="Array Formula" height="310" src="http://www.excel-easy.com/examples/images/sum-largest-numbers/array-formula.png" title="Array Formula" width="410" /></div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 3. Finish by pressing CTRL + SHIFT + ENTER.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="Sum Largest Numbers Result" height="310" src="http://www.excel-easy.com/examples/images/sum-largest-numbers/sum-largest-numbers-result.png" title="Sum Largest Numbers Result" width="409" /></div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> Note: The formula bar indicates that this is an array formula by enclosing it in curly braces {}. Do not type these yourself. They will disappear when you edit the formula.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> Explanation: The range (array constant) created by the LARGE function is stored in Excel's memory, not in an range. The array constant looks as follows.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> {22,10,8,6}</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> This array constant is used as an argument for the SUM function, giving a result of 46.</div>

• 
• 
• 

1. 1
2. 2
3. 3

• Previous
• Next

How to Sum onle the Largest Numbers in Excel

This example shows you how to create an array formula that sums the largest numbers in a range. 1. We use the LARGE function. For example, to find the second to largest number, use the following func…

Read more »

How to Sum Every nth Row in Excel

<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> This example shows you how to create an array formula that sums every nth row in Excel. We will show it for n = 3, but you can do this for any number.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 1. The ROW function returns the row number of a cell.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="Row Function in Excel" height="270" src="http://www.excel-easy.com/examples/images/sum-every-nth-row/row-function.png" title="Row Function in Excel" width="604" /></div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 2. The MOD function gives the remainder of a division. For example, for the first row, MOD(1,3) equals 1. 1 is divided by 3 (0 times) to give a remainder of 1. For the third row, MOD(3,3) equals 0. 3 is divided by 3 (exactly 1 time) to give a remainder of 0. As a result, the formula returns 0 for every 3th row.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="Mod Function" height="270" src="http://www.excel-easy.com/examples/images/sum-every-nth-row/mod-function.png" title="Mod Function" width="604" /></div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> Note: change the 3 to 4 to sum every 4th row, to 5 to sum every 5th row, etc.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 3. Slightly change the formula as shown below.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="TRUE or FALSE" height="270" src="http://www.excel-easy.com/examples/images/sum-every-nth-row/true-false.png" title="TRUE or FALSE" width="604" /></div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 4. To get the sum of the product of these two ranges (FALSE=0, TRUE=1), use the SUM function and finish by pressing CTRL + SHIFT + ENTER.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="Sum Every nth Row Result" height="290" src="http://www.excel-easy.com/examples/images/sum-every-nth-row/sum-every-nth-row-result.png" title="Sum Every nth Row Result" width="604" /></div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> Note: The formula bar indicates that this is an array formula by enclosing it in curly braces {}. Do not type these yourself. They will disappear when you edit the formula.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> Explanation: The product of these two ranges (array constant) is stored in Excel's memory, not in an range. The array constant looks as follows.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> {0;0;5;0;0;66;0;0;21}</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> This array constant is used as an argument for the SUM function, giving a result of 92.</div>

• 
• 
• 
• 

1. 1
2. 2
3. 3
4. 4

• Previous
• Next

How to Sum Every nth Row in Excel

This example shows you how to create an array formula that sums every nth row in Excel. We will show it for n = 3, but you can do this for any number. 1. The ROW function returns the row number of a …

Read more »

how to Separate Strings in Ms Excel

<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> This example teaches you how to separate strings in Excel.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="Separate Strings Example" height="230" src="http://www.excel-easy.com/examples/images/separate-strings/separate-strings-example.png" title="Separate Strings Example" width="420" /></div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> The problem we are dealing with is that we need to tell Excel where we want to separate the string. In case of Smith, Mike the comma is at position 6 while in case of Williams, Janet the comma is at position 9.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 1. To get the first name, use the formula below.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="First Name" height="230" src="http://www.excel-easy.com/examples/images/separate-strings/first-name.png" title="First Name" width="420" /></div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> Explanation: to find the position of the comma, use the FIND function (position 6). To get the length of a string, use the LEN function (11 characters). =RIGHT(A2,LEN(A2)-FIND(",",A2)-1) reduces to =RIGHT(A2,11-6-1). =RIGHT(A2,4) extracts the 4 rightmost characters and gives the desired result (Mike).</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 2. To get the last name, use the following formula.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="Last Name" height="230" src="http://www.excel-easy.com/examples/images/separate-strings/last-name.png" title="Last Name" width="420" /></div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> Explanation: to find the position of the comma, use the FIND function (position 6). =LEFT(A2,FIND(",", A2)-1) reduces to =LEFT(A2,6-1). =LEFT(A2,5) extracts the 5 leftmost characters and gives the desired result (Smith).</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 3. Select the range B2:C2 and drag it down.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="Separate Strings Result" height="230" src="http://www.excel-easy.com/examples/images/separate-strings/separate-strings-result.png" title="Separate Strings Result" width="420" /></div>

• 
• 
• 
• 

1. 1
2. 2
3. 3
4. 4

• Previous
• Next

how to Separate Strings in Ms Excel

This example teaches you how to separate strings in Excel. The problem we are dealing with is that we need to tell Excel where we want to separate the string. In case of Smith, Mike the comma is at p…

Read more »

Percentiles and Quartiles in Excel

<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> This example teaches you how to use the PERCENTILE and QUARTILE function in Excel. Below you can find a list of scores (green fill for illustration only).</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 1. Use the PERCENTILE function shown below to calculate the 30th percentile. Excel returns the value 12.7. This means that 30% (6 out of 20) of the scores are lower or equal to 12.7</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="30th Percentile in Excel" height="470" src="http://www.excel-easy.com/examples/images/percentiles-quartiles/30th-percentile.png" title="30th Percentile in Excel" width="604" /></div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> Note: The second argument of the PERCENTILE function must be a decimal number between 0 and 1. Excel uses a slightly different algorithm to calculate percentiles and quartiles than you find in most statistics books. If you're interested, download the Excel file.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 2. Use the PERCENTILE function shown below to calculate the 90th percentile. Excel returns the value 61.7. This means that 90% (18 out of 20) of the scores are lower or equal to 61.7</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="90th Percentile in Excel" height="470" src="http://www.excel-easy.com/examples/images/percentiles-quartiles/90th-percentile.png" title="90th Percentile in Excel" width="604" /></div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 3. Use the QUARTILE function shown below to calculate the 1st quartile. Excel returns the value 11.25. This means that 25% (5 out of 20) of the scores are lower or equal to 11.25</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="1st Quartile in Excel" height="470" src="http://www.excel-easy.com/examples/images/percentiles-quartiles/1st-quartile.png" title="1st Quartile in Excel" width="604" /></div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> Note: The second argument of the QUARTILE function must be a number between 0 and 4. PERCENTILE(A1:A20,0.25) gives the exact same result. It's up to you which function you would like to use for quartiles. Below you can find an overview.</div> <table border="1" style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; text-align: justify; width: 604px;"><tbody> <tr><td><span class="highlight" style="font-weight: bold;">Minimum Value</span></td><td height="36">PERCENTILE(A1:A20,0)</td><td>QUARTILE(A1:A20,0)</td><td>MIN(A1:A20)</td></tr> <tr><td><span class="highlight" style="font-weight: bold;">1st Quarter</span></td><td height="36">PERCENTILE(A1:A20,0.25)</td><td>QUARTILE(A1:A20,1)</td><td></td></tr> <tr><td><span class="highlight" style="font-weight: bold;">Median</span></td><td height="36">PERCENTILE(A1:A20,0.50)</td><td>QUARTILE(A1:A20,2)</td><td>MEDIAN(A1:A20)</td></tr> <tr></tr> <tr><td><span class="highlight" style="font-weight: bold;">3rd Quarter</span></td><td height="36">PERCENTILE(A1:A20,0.75)</td><td>QUARTILE(A1:A20,3)</td><td></td></tr> <tr><td><span class="highlight" style="font-weight: bold;">Maximum Value</span></td><td height="36">PERCENTILE(A1:A20,1)</td><td>QUARTILE(A1:A20,4)</td><td>MAX(A1:A20)<br /></td></tr> </tbody></table>

• 
• 
• 

1. 1
2. 2
3. 3

• Previous
• Next

Percentiles and Quartiles in Excel

This example teaches you how to use the PERCENTILE and QUARTILE function in Excel. Below you can find a list of scores (green fill for illustration only). 1. Use the PERCENTILE function shown below t…

Read more »

Substitute vs Replace in Excel

<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> This example shows the difference between the SUBSTITUTE function and the REPLACE function.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 1a. If you know the text to be replaced, use the SUBSTITUTE function.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="Substitute Function in Excel" height="110" src="http://www.excel-easy.com/examples/images/substitute-vs-replace/substitute-function.png" title="Substitute Function in Excel" width="604" /></div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 1b. The SUBSTITUTE function has a 4th optional argument. You can use this argument to indicate which occurrence you want to substitute.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="Substitute the Second Occurrence" height="110" src="http://www.excel-easy.com/examples/images/substitute-vs-replace/substitute-second-occurrence.png" title="Substitute the Second Occurrence" width="604" /></div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 2. If you know the position of the text to be replaced, use the REPLACE function.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="Replace Function in Excel" height="190" src="http://www.excel-easy.com/examples/images/substitute-vs-replace/replace-function.png" title="Replace Function in Excel" width="604" /></div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> Note: started at position 1 with length 3.</div>

• 
• 
• 

1. 1
2. 2
3. 3

• Previous
• Next

Substitute vs Replace in Excel

This example shows the difference between the SUBSTITUTE function and the REPLACE function. 1a. If you know the text to be replaced, use the SUBSTITUTE function. 1b. The SUBSTITUTE function has a 4th…

Read more »

How to Count with Or make an Criteria in Excel

<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> Counting with Or criteria in Excel can be tricky. This article shows several easy to follow examples.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 1. We start simple. For example, we want to count the number of cells that contain Google or Facebook (one column).</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="2x Countif Function in Excel" height="270" src="http://www.excel-easy.com/examples/images/count-with-or-criteria/2x-countif-function.png" title="2x Countif Function in Excel" width="604" /></div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 2a. However, if we want to count the number of rows that contain Google <u>or</u> Stanford (two columns), we cannot simply use the COUNTIF function twice (see the picture below). Rows that contain Google and Stanford are counted twice, but they should only be counted once. 4 is the answer we are looking for.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="Wrong Formula" height="270" src="http://www.excel-easy.com/examples/images/count-with-or-criteria/wrong-formula.png" title="Wrong Formula" width="604" /></div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 2b. What we need is an array formula. We use the IF function to check if Google <u>or</u> Stanford occurs.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="Count with Or Criteria, Step 1" height="270" src="http://www.excel-easy.com/examples/images/count-with-or-criteria/count-rows-with-or-criteria-step-1.png" title="Count with Or Criteria, Step 1" width="604" /></div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> Explanation: TRUE = 1, FALSE = 0. For row 1, the IF function evaluates to IF(TRUE+TRUE,1,0), IF(2,1,0), 1. So the first row will be counted. For row 2, the IF function evaluates to IF(FALSE+FALSE,1,0), IF(0,1,0), 0. So the second row will not be counted. For row 3, the IF function evaluates to IF(FALSE+TRUE,1,0), IF(1,1,0), 1. So the third row will be counted, etc.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 2c. All we need is a SUM function that counts these 1's. To achieve this (don't be overwhelmed), we add the SUM function and replace A1 with A1:A8 and B1 with B1:B8.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="Count with Or Criteria, Step 2" height="270" src="http://www.excel-easy.com/examples/images/count-with-or-criteria/count-rows-with-or-criteria-step-2.png" title="Count with Or Criteria, Step 2" width="604" /></div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 2d. Finish by pressing CTRL + SHIFT + ENTER.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="Count with Or Criteria, Step 3" height="270" src="http://www.excel-easy.com/examples/images/count-with-or-criteria/count-rows-with-or-criteria-step-3.png" title="Count with Or Criteria, Step 3" width="604" /></div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> Note: The formula bar indicates that this is an array formula by enclosing it in curly braces {}. Do not type these yourself. They will disappear when you edit the formula.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> Explanation: The range (array constant) created by the IF function is stored in Excel's memory, not in an range. The array constant looks as follows:</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> {1;0;1;0;1;0;1;0}</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> This array constant is used as an argument for the SUM function, giving a result of 4.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 3. We can go one step further. For example, we want to count the number of rows that contain Google <u>and</u>Stanford <u>or</u> Columbia.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="Count with And, Or Criteria" height="270" src="http://www.excel-easy.com/examples/images/count-with-or-criteria/count-rows-with-and-or-criteria.png" title="Count with And, Or Criteria" width="604" /></div>

• 
• 
• 
• 
• 

1. 1
2. 2
3. 3
4. 4
5. 5

• Previous
• Next

How to Count with Or make an Criteria in Excel

Counting with Or criteria in Excel can be tricky. This article shows several easy to follow examples. 1. We start simple. For example, we want to count the number of cells that contain Google or Face…

Read more »

How to Remove Unwanted Characters in Excel

<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> The TRIM function returns a string with extra spaces, starting spaces and ending spaces removed. The CLEAN function removes nonprintable characters from a string.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 1. For example, cell A1 below contains the string " Excel   Easy  "</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="Trim Function in Excel" height="110" src="http://www.excel-easy.com/examples/images/remove-unwanted-characters/trim-function.png" title="Trim Function in Excel" width="604" /></div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> Note: the TRIM function returns the string "Excel Easy".</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 2. For example, cell A1 below contains a nonprintable character.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="Clean Function in Excel" height="110" src="http://www.excel-easy.com/examples/images/remove-unwanted-characters/clean-function.png" title="Clean Function in Excel" width="604" /></div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> Note: the CLEAN function removes this nonprintable character. Text imported from other applications may contain nonprintable characters.</div>

• 
• 

1. 1
2. 2

• Previous
• Next

How to Remove Unwanted Characters in Excel

The TRIM function returns a string with extra spaces, starting spaces and ending spaces removed. The CLEAN function removes nonprintable characters from a string. 1. For example, cell A1 below contai…

Read more »

Forecasting and Trends in Excel

<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> When you add a <span style="color: #339ccb;">trendline</span> to an Excel chart, Excel can display the equation in a chart (see below). You can use this equation to calculate future sales. The FORECAST and TREND function give the exact same result.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="Trendline in Excel" height="289" src="http://www.excel-easy.com/examples/images/forecast-trend/trendline.png" title="Trendline in Excel" width="481" /></div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> Explanation: Excel uses the method of least squares to find a line that best fits the points. The R-squared value equals 0.9295, which is a good fit. The closer to 1, the better the line fits the data.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 1. Use the equation to calculate future sales.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="Equation" height="370" src="http://www.excel-easy.com/examples/images/forecast-trend/equation.png" title="Equation" width="604" /></div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 2. Use the FORECAST function to calculate future sales.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="Forecast Function" height="370" src="http://www.excel-easy.com/examples/images/forecast-trend/forecast-function.png" title="Forecast Function" width="604" /></div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> Note: when we drag the FORECAST function down, the <span style="color: #339ccb;">absolute references</span> ($B$2:$B$11 and $A$2:$A$11) stay the same, while the relative reference (A12) changes to A13 and A14.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 3. If you prefer to use an array formula, use the TREND function to calculate future sales.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="Trend Function" height="370" src="http://www.excel-easy.com/examples/images/forecast-trend/trend-function.png" title="Trend Function" width="604" /></div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> Note: first, select the range E12:E14. Next, type =TREND(B2:B11,A2:A11,A12:A14). Finish by pressing CTRL + SHIFT + ENTER. The formula bar indicates that this is an array formula by enclosing it in curly braces {}. To delete this array formula, select the range E12:E14 and press Delete.</div>

• 
• 
• 
• 

1. 1
2. 2
3. 3
4. 4

• Previous
• Next

Forecasting and Trends in Excel

When you add a trendline to an Excel chart, Excel can display the equation in a chart (see below). You can use this equation to calculate future sales. The FORECAST and TREND function give the exact …

Read more »

How to solve Quadratic Equation in Excel

<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> A quadratic equation is of the form ax² + bx + c = 0 where a ≠ 0. A quadratic equation can be solved by using the quadratic formula. You can also use Excel's Goal Seek feature to solve a quadratic equation.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 1. For example, we have the formula y = 3x² - 12x + 9.5. It's easy to calculate y for any given x. For x = 1, y = 0.5</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="Excel Formula, x = 1" height="130" src="http://www.excel-easy.com/examples/images/quadratic-equation/formula-1.png" title="Excel Formula, x = 1" width="604" /></div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 2. For x = 2, y = -2.5</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="Excel Formula, x = 2" height="130" src="http://www.excel-easy.com/examples/images/quadratic-equation/formula-2.png" title="Excel Formula, x = 2" width="604" /></div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 3. But what if we want to know x for any given y? For example, y = 24.5. We need to solve 3x² - 12x + 9.5 = 24.5. We can solve the quadratic equation 3x² - 12x + 9.5 - 24.5 = 0 by using the quadratic formula.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 3x² - 12x -15 = 0<br />a = 3, b = -12, c = -15<br />D = b²- 4ac = (-12)² - 4 * 3 * -15 = 144 + 180 = 324</div> <table cellpadding="0" cellspacing="0" class="fraction" style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 2em; margin-bottom: 0.5em; margin-top: 0em; text-align: center; vertical-align: middle;"><tbody> <tr><td align="left" nowrap="nowrap" rowspan="2" width="20">x =</td><td nowrap="nowrap" width="65">-b + √D</td><td nowrap="nowrap" rowspan="2" width="50">or</td><td align="left" nowrap="nowrap" rowspan="2" width="20">x =</td><td nowrap="nowrap" width="65">-b - √D</td></tr> <tr><td class="upper_line" style="border-top-color: black; border-top-style: solid; border-top-width: 1px;">2a</td><td class="upper_line" style="border-top-color: black; border-top-style: solid; border-top-width: 1px;">2a</td></tr> </tbody></table> <table cellpadding="0" cellspacing="0" class="fraction" style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 2em; margin-bottom: 0.5em; margin-top: 0em; text-align: center; vertical-align: middle;"><tbody> <tr><td align="left" nowrap="nowrap" rowspan="2" width="20">x =</td><td nowrap="nowrap" width="65">12 + √324</td><td nowrap="nowrap" rowspan="2" width="50">or</td><td align="left" nowrap="nowrap" rowspan="2" width="20">x =</td><td nowrap="nowrap" width="65">12 - √324</td></tr> <tr><td class="upper_line" style="border-top-color: black; border-top-style: solid; border-top-width: 1px;">6</td><td class="upper_line" style="border-top-color: black; border-top-style: solid; border-top-width: 1px;">6</td></tr> </tbody></table> <table cellpadding="0" cellspacing="0" class="fraction" style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 2em; margin-bottom: 0.5em; margin-top: 0em; text-align: center; vertical-align: middle;"><tbody> <tr><td align="left" nowrap="nowrap" rowspan="2" width="20">x =</td><td nowrap="nowrap" width="65">12 + 18</td><td nowrap="nowrap" rowspan="2" width="50">or</td><td align="left" nowrap="nowrap" rowspan="2" width="20">x =</td><td nowrap="nowrap" width="65">12 - 18</td></tr> <tr><td class="upper_line" style="border-top-color: black; border-top-style: solid; border-top-width: 1px;">6</td><td class="upper_line" style="border-top-color: black; border-top-style: solid; border-top-width: 1px;">6</td></tr> </tbody></table> <table cellpadding="0" cellspacing="0" class="fraction" style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 2em; margin-bottom: 0.5em; margin-top: 0em; text-align: center; vertical-align: middle;"><tbody> <tr><td align="left" nowrap="nowrap" rowspan="2" width="20">x =</td><td nowrap="nowrap" rowspan="2" width="65">5</td><td nowrap="nowrap" rowspan="2" width="50">or</td><td align="left" nowrap="nowrap" rowspan="2" width="20">x =</td><td nowrap="nowrap" rowspan="2" width="65">-1</td></tr> </tbody></table> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> </div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 4. You can use Excel's Goal Seek feature to obtain the exact same result. On the Data tab, in the Data Tools group, click What-If Analysis, Goal Seek.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="Click Goal Seek" height="239" src="http://www.excel-easy.com/examples/images/quadratic-equation/click-goal-seek.png" title="Click Goal Seek" width="604" /></div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> The Goal Seek dialog box appears.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 5. Select cell B2.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 6. Click in the 'To value' box and type 24.5</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 7. Click in the 'By changing cell' box and select cell A2.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 8. Click OK.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="Goal Seek Parameters" height="142" src="http://www.excel-easy.com/examples/images/quadratic-equation/goal-seek-parameters.png" title="Goal Seek Parameters" width="209" /></div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> Result.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="Solve a Quadratic Equation in Excel" height="130" src="http://www.excel-easy.com/examples/images/quadratic-equation/solve-quadratic-equation.png" title="Solve a Quadratic Equation in Excel" width="604" /></div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> Note: Excel returns the solution x = 5. Excel finds the other solution if you start with a x-value closer to x = -1. For example, enter the value 0 into cell A2 and repeat steps 4 to 8. To find the roots, set y = 0 and solve the quadratic equation 3x² - 12x + 9.5 = 0. In this case, set 'To value' to 0.</div>

• 
• 
• 
• 
• 

1. 1
2. 2
3. 3
4. 4
5. 5

• Previous
• Next

How to solve Quadratic Equation in Excel

A quadratic equation is of the form ax2 + bx + c = 0 where a ≠ 0. A quadratic equation can be solved by using the quadratic formula. You can also use Excel's Goal Seek feature to solve a quadratic eq…

Read more »

How to find out the Last Day of the Month in Excel

<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> To get the date of the last day of the month in Excel, use the EOMONTH (End of Month) function.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 1. For example, get the date of the last day of the current month.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="Last Day, Current Month" height="110" src="http://www.excel-easy.com/examples/images/last-day-of-the-month/last-day-current-month.png" title="Last Day, Current Month" width="604" /></div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> Note: the EOMONTH function returns the serial number of the date. Apply a <a href="http://www.excel-easy.com/examples/date-time-formats.html" style="color: #339ccb; text-decoration: none;">Date format</a> to display the date.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 2. For example, get the date of the last day of the next month.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="Last Day, Next Month" height="110" src="http://www.excel-easy.com/examples/images/last-day-of-the-month/last-day-next-month.png" title="Last Day, Next Month" width="604" /></div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 3. For example, get the date of the last day of the current month - 8 months = 6 - 8 = -2 = October (-2+12=10), 2011!</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="Last Day, 8 Months Before Current Month" height="110" src="http://www.excel-easy.com/examples/images/last-day-of-the-month/last-day-8-months-before-current-month.png" title="Last Day, 8 Months Before Current Month" width="604" /></div>

• 
• 
• 

1. 1
2. 2
3. 3

• Previous
• Next

How to find out the Last Day of the Month in Excel

To get the date of the last day of the month in Excel, use the EOMONTH (End of Month) function. 1. For example, get the date of the last day of the current month. Note: the EOMONTH function returns t…

Read more »

How to use COMPARE AND TEXT formula in Excel

<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> This example shows two ways to compare text in Excel. One is case-sensitive and one is case-insensitive.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 1. Use the EXACT function (case-sensitive).</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="Case-sensitive" height="210" src="http://www.excel-easy.com/examples/images/compare-text/case-sensitive.png" title="Case-sensitive" width="604" /></div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 2. Use the formula =A1=B1 (case-insensitive).</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="Case-insensitive" height="210" src="http://www.excel-easy.com/examples/images/compare-text/case-insensitive.png" title="Case-insensitive" width="604" /></div>

• 
• 

1. 1
2. 2

• Previous
• Next

How to use COMPARE AND TEXT formula in Excel

This example shows two ways to compare text in Excel. One is case-sensitive and one is case-insensitive. 1. Use the EXACT function (case-sensitive). 2. Use the formula =A1=B1 (case-insensitive). …

Read more »

Use of "Indirect" Formula in Excel

<div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> The INDIRECT function returns the reference specified by a text string.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 1. For example, the INDIRECT function below reduces to =INDIRECT("C2"), =C2, 5</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="Cell" height="110" src="http://www.excel-easy.com/examples/images/indirect/cell.png" title="Cell" width="604" /></div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> Do we really need the INDIRECT function for this? Yes. Below you can find the result without using the INDIRECT function.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="Without Indirect Function" height="110" src="http://www.excel-easy.com/examples/images/indirect/without-indirect-function.png" title="Without Indirect Function" width="604" /></div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 2. For example, the function below reduces to =SUM(INDIRECT("E3:E6")), =SUM(E3:E6), 27</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="Range" height="210" src="http://www.excel-easy.com/examples/images/indirect/range.png" title="Range" width="604" /></div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> Note: the & operator is used to <a href="http://www.excel-easy.com/functions/text-functions.html#join-strings" style="color: #339ccb; text-decoration: none;">join strings</a>. Border for illustration only.</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> 3. For example, the function below reduces to =AVERAGE(Scores), 9</div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> <img alt="Name" height="150" src="http://www.excel-easy.com/examples/images/indirect/name.png" title="Name" width="604" /></div> <div style="background-color: white; color: #666666; font-family: Arial, Helvetica, sans-serif; font-size: 12px; line-height: 21.600000381469727px; text-align: justify;"> Note: the <a href="http://www.excel-easy.com/examples/names-in-formulas.html" style="color: #339ccb; text-decoration: none;">named</a> range Scores refers to the range C2:C4.</div>

• 
• 
• 
• 

1. 1
2. 2
3. 3
4. 4

• Previous
• Next

Use of "Indirect" Formula in Excel

The INDIRECT function returns the reference specified by a text string. 1. For example, the INDIRECT function below reduces to =INDIRECT("C2"), =C2, 5 Do we really need the INDIRECT function for this…

Read more »