tag:computers.tutsplus.com,2005:/categories/microsoft-officeEnvato Tuts+ Computer Skills - Microsoft Office2017-07-26T12:55:42Ztag:computers.tutsplus.com,2005:PostPresenter/cms-29127How to Change Your Microsoft Office Theme in 60 Seconds<p>In today's tutorials, learn how to give Microsoft Office apps, like PowerPoint, Excel, and Word, a different interface look by changing the app theme. <br></p><h2>How to Quickly Change Your Microsoft Office Theme<br>
</h2><figure><div class="wistia_responsive_padding"><div class="wistia_responsive_wrapper"><div class="wistia_embed wistia_async_kbnu7bf05n videoFoam=true"> </div></div></div></figure><p><strong><em>Note</em></strong><em>: Watch this short tutorial screencast or follow the quick steps below, that compliment this video.</em></p><h3>1. Open the Options Tab to Change the Office Theme</h3><p>So the default view in Office is what they call the <strong>Colorful</strong> view, which in Excel, features a bright green menu. Let's change this up. I'll go to the <strong>File</strong> menu and choose <strong>Options</strong>.</p><figure class="post_image"><img alt="" data-src="https://cms-assets.tutsplus.com/uploads/users/23/posts/29127/image/change-microsoft-themes-options.jpg"><figcaption>Open the Microsoft Office Options tab.</figcaption></figure><h3>2. Try a Different Microsoft Office Theme in Excel</h3><p>Make sure you're on the <strong>General</strong> tab, and find the area labeled <strong>Office Theme</strong>. Let's try out a different theme. I'll choose <strong>Dark Grey</strong> from the drop down for a darker look, one that's great for working at night. When I press <strong>OK</strong>, it applies a different look to Excel.</p><figure class="post_image"><img alt="Change Microsoft Office Theme in Excel to Dark Grey" data-src="https://cms-assets.tutsplus.com/uploads/users/23/posts/29127/image/change-microsoft-office-themes-to-dark-grey.jpg"><figcaption>Apply the Microsoft Office Theme Dark Grey.</figcaption></figure><h3>3. Change Your Microsoft Office Theme in Word</h3><p>As I switch to PowerPoint and then to Word, you can see the themes are applied in these other Office apps as well. Let's reenter the options while we're still in Word and try out one more theme, the <strong>White</strong> theme. I'll choose that from the drop down and press <strong>OK</strong>.</p><figure class="post_image"><img alt="Change Your Microsoft Office Theme in Word to White" data-src="https://cms-assets.tutsplus.com/uploads/users/23/posts/29127/image/change-microsoft-office-theme-in-word-to-white.jpg"><figcaption>Set your Microsoft Office Theme in Word to White.</figcaption></figure><h3>Finishing Up!</h3><p>You can see that this is a light and airy theme, and it's applied to each app as well inside the Office suite. These are great visual tweaks to customize your Office setup. Why not change up the apps you spend all day working in? </p><h2>Discover More Helpful Microsoft Office Tutorials</h2><p>Learn more in our <a href="https://business.tutsplus.com/categories/microsoft" target="_self">Microsoft tutorials</a> on Envato Tuts+. We have an assortment of material to help you work more productively on Windows, covering PowerPoint, Excel, Word and more: </p><ul class="roundup-block__contents posts--half-width roundup-block--list">
<li class="roundup-block__content"><a class="roundup-block__content-link" href="https://business.tutsplus.com/tutorials/how-to-make-and-give-great-powerpoint-presentations--cms-28734"><img class="roundup-block__preview-image" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/28734/preview_image/presentations-icon.jpg"><div class="roundup-block__primary-category topic-business">Microsoft PowerPoint</div>
<div class="roundup-block__content-title">How to Make & Give Great PowerPoint Presentations (In 5 Simple Steps)</div>
<div class="roundup-block__author">Andrew Childress</div></a></li>
<li class="roundup-block__content"><a class="roundup-block__content-link" href="https://business.tutsplus.com/tutorials/how-to-protect-cells-sheets-and-workbooks-in-excel--cms-28736"><img class="roundup-block__preview-image" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/28736/preview_image/protect-excel-icon.jpg"><div class="roundup-block__primary-category topic-business">Microsoft Excel</div>
<div class="roundup-block__content-title">How to Protect Cells, Sheets, and Workbooks in Excel</div>
<div class="roundup-block__author">Andrew Childress</div></a></li>
<li class="roundup-block__content"><a class="roundup-block__content-link" href="https://business.tutsplus.com/tutorials/how-to-simplify-word-document-formatting-with-styles--cms-21069"><img class="roundup-block__preview-image" data-src="https://cms-assets.tutsplus.com/uploads/users/60/posts/21069/preview_image/word-styles-preview-image.png"><div class="roundup-block__primary-category topic-business">Microsoft Word</div>
<div class="roundup-block__content-title">How to Simplify Word Document Formatting With Styles</div>
<div class="roundup-block__author">Bob Flisser</div></a></li>
</ul><p>What themes color is your favorite? Do you like to customize the look of your default apps? </p>2017-07-26T12:55:42.000Z2017-07-26T12:55:42.000ZAndrew Childresstag:computers.tutsplus.com,2005:PostPresenter/cms-28331Quick Start: How to Make a Basic Formula in Excel<p>If you've written off spreadsheets as a tool that can help you as a freelancer or creative, now's the time to rethink it. In this tutorial, I'll help you learn <strong>how to make a basic formula </strong><strong>in Excel </strong>and get you thinking about how you can use them.</p><p>You might think of a spreadsheet as a tool for your accountant or banker, but here are ways that I use spreadsheets on a daily basis as part of my creative work:<br></p><ul>
<li>Planning and executing courses and tutorials that I <a href="https://photography.tutsplus.com/tutorials/how-its-made-follow-a-tuts-course-production-from-start-to-finish--cms-26580" rel="external" target="_blank">produce for Tuts+</a>.</li>
<li>To manage other freelancers that help me run my freelance business, with shared spreadsheets in Dropbox.</li>
<li>Capturing research and reference information while learning a new skill or app.</li>
</ul><p>Formulas drive the usefulness of spreadsheets. Let's start learning how to use them.</p><p><em>A quick note: if you prefer another spreadsheet app like Apple Numbers or Google Sheets, don't stop reading. Many of the skills and formulas that we'll work with in this tutorial work in those apps as well.</em><br></p><h2>Embrace the Spreadsheet</h2><p>Excel spreadsheets are a blank canvas of columns, ready for you to add data to. One of my favorite things about Excel, and spreadsheets in general, are that they scale up with your data. You can keep adding more sheets, formulas, and functions as your needs grow.</p><figure class="post_image"><img alt="Excel Basics - Rows and Columns" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/28331/image/rows-columns.jpg"><figcaption>Rows are the horizontal lines that are numbered, while columns are the vertical lines that are lettered.</figcaption></figure><p>Before we write our first formula, here are four key things to know about how spreadsheets work:<br></p><ol>
<li>Spreadsheet files are usually called <strong>workbooks, </strong>with multiple tabs called <strong>sheets.</strong>
</li>
<li>
<strong></strong>Excel spreadsheets are made up of <strong>rows</strong> (lines running from left to right)<strong> </strong>and <strong>columns </strong>(lines running up and down.)</li>
<li>When rows and columns meet, spreadsheets form <strong>cells.</strong>
</li>
<li>
<strong>Cells </strong>can hold data you type in yourself, or <strong>formulas </strong>and <strong>functions </strong>to modify and work with data.</li>
</ol><figure class="post_image"><img alt="Cell A2" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/28331/image/cell-a2.jpg"><figcaption>When rows and columns meet, it forms <strong>cells, </strong>where we can input formulas and functions. The intersection of row 2 and column A is cell <strong>A2.</strong></figcaption></figure><h2>How to Make Basic Excel Formulas & Functions (Quick Video Tutorial)</h2><p>In this screencast, I'll show you the essential information for how to get started with Excel formulas, from how to make your first formula in <strong>Excel</strong> to working with basic functions.</p><figure><div class="wistia_responsive_padding"><div class="wistia_responsive_wrapper"><div class="wistia_embed wistia_async_6vbyxni42v videoFoam=true"> </div></div></div></figure><p>Read on to find out about more formulas, including ten functions built into Excel to work with data.<strong><br></strong></p><h2>Your First Excel Formula</h2><p>Let's write our first formula in Microsoft Excel. Write your first formula by double clicking in any Excel skill. Let's do some basic<strong> multiplication.</strong></p><p>I'll type this into my cell:</p><p><code class="inline">=5*4</code><br></p><p>When I press <strong>enter, </strong>Excel will calculate the result and print <strong>20 </strong>in the cell. The formula is still behind the scenes, and you can see it in the <strong>formula bar </strong>just above the spreadsheet.</p><figure class="post_image"><img alt="Basic Math in Excel" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/28331/image/basic-math-formula.jpg"><figcaption>This basic math formula is an example of how Excel will calculate things for us, but the formula remains behind the scenes.</figcaption></figure><p>This is how Excel works. Formulas operate on data, and print the output. This is an extremely basic example of how to work with data in Excel. Let's graduate to <strong>functions.</strong></p><h2>How to Use Functions in Excel</h2><p>Many people use the terms <strong>formula </strong>and <strong>function </strong>interchangeably in Microsoft Excel. In reality, the two are different:</p><ul>
<li>
<strong>Formulas</strong> - Are basic operations on cells, such as using math operators like <strong>+ </strong>or<strong> / </strong>to add and divide numbers in your formula, for example. Formulas are simple.<br>
</li>
<li>
<strong>Functions</strong> - Use built-in Excel functionality, such as <strong>=AVERAGE</strong> to perform operations easily on your data. Excel comes with built-in functions that have predefined calculations you can work with, like ready-made recipes.<br>
</li>
</ul><p>Most of the time, Excel users will collectively call these "formulas." Functions are actually Excel magic behind the scenes, where you can give Excel some values and Excel automates the work for you.</p><p>Functions start with an equals sign, and then the function name, and then typically an open parentheses. Then, you'll feed the function some data to work magic upon.</p><figure class="post_image"><img alt="Excel Function Examples" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/28331/image/function-examples.jpg"></figure><p>Let's use the AVERAGE function, for example. Instead of adding values up and dividing by the number of data, Excel automates this with the AVERAGE function. Let's look at how to use them. </p><p>To get started with the AVERAGE function, double click in an Excel cell and type <strong>=AVERAGE(, </strong>and now, we have two choices for how to average out some values:</p><h3>1. Inline Data</h3><p>You can type data right into functions in the formula. In the case of the average function, you can input values, separated by commas, and Excel will output the average of those values.</p><p><code class="inline">=AVERAGE(1,3,5,7,9)</code></p><figure class="post_image"><img alt="Average Example" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/28331/image/average-example.jpg"><figcaption>Separate values with commas to average them.</figcaption></figure><h3>2. Cell References</h3><p>Let's say that we've already got data typed into a spreadsheet. You can use functions, and point them to other cells to average those cells instead. In the example below, I'll average a list of data with this formula:</p><p><code class="inline">=AVERAGE(B1:B3)</code><br></p><figure class="post_image"><img alt="Average on Data in Excel" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/28331/image/average-on-data.jpg"><figcaption>Average in Excel with cell references.</figcaption></figure><p>The formula I'm using averages all of the data in the range that I specify, in this case cells B1 to B3.</p><p>Most functions will allow you to either work with data right inside of the formula, or run the function on data you've typed elsewhere.</p><h2>10 Helpful Excel Formulas and Functions</h2><p>So, now we've mastered how to use a basic function. The good news is that Excel offers many functions that you can use on your data. If you already know how to use one function, all others work similarly.</p><p>I spend plenty of time working in Excel every day. Here are ten formulas that I use over and over to clean up my spreadsheets or work with data, and examples of how to use them. </p><p><em>Note: all of these formulas work on in-line data, but would typically be used on cells you've already typed in a cell.</em><br></p><h3>1. =AVERAGE</h3><p>We've already touched on the AVERAGE function, but I think it's one of the most important ones in Excel. Whether you're averaging your grades or an NBA player's stats, this is one of the easiest and most useful formulas.</p><p><em>In-line usage example:</em></p><p><code class="inline">=AVERAGE(1,3,5,7,9)</code></p><p><em>Usage on cells:</em></p><p><code class="inline">=AVERAGE(A1:A5)</code></p><p><em>Example in action:</em></p><figure class="post_image"><img alt="Average Example 2" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/28331/image/average-example-nba.jpg"></figure><h3><figure class="post_image">2. =COUNT</figure></h3><p>COUNT will help you find out how many items are in a column or a list. Count would typically only be used on a list of data, and not in-line.</p><p><em>Usage on cells:</em></p><p><code class="inline">=COUNT(A1:A5)</code><br></p><p><em>Example in action:</em></p><figure class="post_image"><img alt="" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/28331/image/count-example.jpg"></figure><h3>3. =UPPER, =LOWER, & =PROPER</h3><p>UPPER, LOWER, and PROPER will help you clean up text that you insert into your spreadsheets. These related formulas modify text in different ways:</p><ul>
<li>
<strong>UPPER</strong> - Converts a text string into all uppercase letters.<br>
</li>
<li>
<strong>LOWER</strong> - Converts text into all lowercase letters.<br>
</li>
<li>
<strong>PROPER</strong> - Converts a word into an attempted "proper" capitalization, capitalizing proper nouns.<br>
</li>
</ul><p><em>Usage on cells:</em></p><p><code class="inline">=UPPER(A1)</code><br></p><p><code class="inline">=LOWER(A1)</code></p><p><code class="inline">=PROPER(A1)</code></p><p><em>Example in action:</em></p><figure class="post_image"><img alt="Upper Lower Proper" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/28331/image/upper-lower-proper.jpg"></figure><h3>4. =LEFT and =RIGHT</h3><p>The LEFT and RIGHT functions will help you grab data from a part of a cell. Imagine using this formula to grab the prefix from a list of names, such as "Mr." or "Ms.", for example.</p><p>The LEFT and RIGHT formulas will grab</p><p><em>Usage on cells:</em></p><p><code class="inline">=LEFT(A1,5)</code><br></p><p>Gets the first five characters from cell A1.</p><p><code class="inline">=RIGHT(A1,5)</code><br></p><p>Gets the last five characters from cell A5.</p><p><em>Example in action:</em></p><figure class="post_image"><img alt="Right Example" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/28331/image/right-example.jpg"></figure><h3>5. =&</h3><p>The <strong>=& </strong>function is used to join text and cells together. You can use it to join data from multiple cells into a single cell.</p><p>This formula works a bit differently. The formula doesn't open up with <strong>&; </strong>instead, you place it in between text or cells.</p><p><em>In-line usage example:</em><br></p><p><code class="inline">="Andrew "&"Childress"</code><br></p><p><em>Usage on cells:</em></p><p><code class="inline">=A1&A2</code><br></p><p><em>Example in action:</em></p><figure class="post_image"><img alt="And Example" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/28331/image/and-example.jpg"></figure><h3>6. =MAX and =MIN</h3><p>MAX and MIN are related formulas to identify the largest and smallest values in a list of data. This formula is typically used on data that you've already typed in Excel, particularly lists where you want to find the largest and smallest value in the list.</p><p><em>In-line usage example:</em><br></p><p><code class="inline">=MAX(1,3,5,7,9)</code><br></p><p><code class="inline">=MIN(1,3,5,7,9)</code></p><p><em>Usage on cells:</em></p><p><code class="inline">=MAX(A1:A10)</code><br></p><p><code class="inline">=MIN(A1:A10)</code><br></p><p><em>Example in action:</em></p><figure class="post_image"><img alt="Max Min Example" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/28331/image/max-min-example.jpg"></figure><h3>7. =TODAY</h3><p>TODAY is one of the simplest and most useful functions. Simply insert it, and Excel will always keep today's date in the cell. Each time you re-open the spreadsheet, Excel will refresh the formula and put today's date in the cell.</p><p><em>In-line usage example:</em><br></p><p><code class="inline">=TODAY()</code><br></p><p><em>Example in action:</em></p><figure class="post_image"><img alt="Today Example" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/28331/image/today-example.jpg"></figure><h3>8. =TRIM</h3><p>=TRIM is a function that helps you clean up your text in formulas. If your text has spaces at the beginning or end, TRIM will help to remove it.</p><p><em>Usage on cells:</em></p><p><code class="inline">=TRIM(A1)</code><br></p><p><em>Example in action:</em></p><figure class="post_image"><img alt="Excel Trim" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/28331/image/trim-example.jpg"></figure><h3>9. =DAYS</h3><p>=DAYS helps you calculate the number of calendar days between two dates. I like to use the DAYS formula when I'm looking at the amount of time between today and a future event, for example.</p><p><em>In-line usage example:</em><br></p><p><code class="inline">=DAYS("2/28/2016","1/1/2016")</code><br></p><p><em>Usage on cells:</em></p><p><code class="inline">=DAYS(B1,B2)</code><br></p><p><em>Example in action:</em></p><figure class="post_image"><img alt="Days Example" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/28331/image/days-example.jpg"></figure><h3>10. =NETWORKDAYS</h3><p>=NETWORKDAYS is a function to help you calculate the number of <em>work</em> days between two dates. Picture yourself using this when you're calculating the number of business days left before you'll receive a package, for example.</p><p><em>In-line usage example:</em></p><p><code class="inline">=NETWORKDAYS("1/1/2016","2/28/2016")</code><br></p><p><em>Usage on cells:</em></p><p><code class="inline">=NETWORKDAYS(A2,B2)</code><br></p><p><em>Example in action:</em></p><figure class="post_image"><img alt="Net Work Days Example" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/28331/image/networkdays.png"></figure><p>These functions are powerful ways to work with data. Mastering Excel is all about combining these functions to work with data and automate your life.</p><h2>Recap and Keep Learning</h2><p>If you enjoyed this tutorial on how to make a formula in Excel, and familiarized yourself with spreadsheet formulas and basic functions. I highly recommend the following resources to keep learning:</p>
<ul>
<li>
<a href="https://business.tutsplus.com/tutorials/how-to-manage-excel-formulas-copy-paste-and-autofill--cms-28210" rel="external" target="_blank">How to Manage Your Excel Formulas</a> is a great follow-up tutorial to learn how to copy and paste and work with formulas.</li>
<li>Tidying up a spreadsheet includes essential skills like knowing <a href="https://business.tutsplus.com/tutorials/how-to-find-and-remove-duplicates-in-excel-quickly--cms-27635" rel="external" target="_blank">How to Find and Remove Duplicate</a> rows.</li>
<li>Gridmaster's <a href="https://gridmaster.io/courses/intro-to-functions/challenge--basic-calculations/1" rel="external" target="_blank">Introduction to Functions</a> course allows you to practice many of these skills right inside your web browser.</li>
</ul><p>How did you get started with using spreadsheets? What do you want to learn more about? Check in with a comment to let me know.</p>2017-03-07T12:55:53.000Z2017-03-07T12:55:53.000ZAndrew Childresstag:computers.tutsplus.com,2005:PostPresenter/cms-28086How to Start Using COUNTIF, SUMIF, and AVERAGEIF in Excel<p>Learning Microsoft Excel is all about adding more and more formulas and functions to your toolbelt. Combine enough of these, and you can do practically anything with a spreadsheet.</p><p>In this tutorial, you'll learn how to use three powerful Excel formulas: <strong>SUMIF, COUNTIF, </strong>and <strong>AVERAGEIF.</strong></p><figure class="post_image"><img alt="IF Math Example" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/28086/image/intro-example-sumif.jpg"><figcaption>Here we have a list of transactions for the month with some data on expenses.</figcaption></figure><p>In the screenshot above, you can see that we have a list of transactions on the left side. If I want to keep an eye on my spending, I can use these three formulas to monitor it. </p><p>On the right side, the <strong>Dining Out Expense </strong>box uses the three formulas to help me track my expenses:</p><ul>
<li>
<strong>COUNTIF </strong>- Used to count the number of times that "Restaurant" appears in the list.</li>
<li>
<strong>SUMIF </strong>- Calculates the total expense for items labeled "Restaurant."<br>
</li>
<li>
<strong>AVERAGIEF </strong>- Averages out all of my "Restaurant" expenses in the list.<br>
</li>
</ul><p>More generically, here's what each of those formulas do for you, and how you might use them to your advantage:</p><ul>
<li>
<strong>SUMIF</strong> - Add values if a condition is met, such as adding up all purchases from one category.</li>
<li>
<strong>COUNTIF - </strong>Count up the number of items that meet a condition, such as counting the number of times a name appears in a list.</li>
<li>
<strong>AVERAGEIF</strong> - Conditionally average values; for instance, you could average your grades for only exams.</li>
</ul><p>These formulas allow you to add logic to your spreadsheet. Let's look at how to use each formula.</p><h2>
</h2><p>COUNTIF, SUMIF, and AVERAGEIF in Excel (Quick Video Tutorial)</p><p>Screencasts are one of the best ways to watch and learn a new skill, including getting started with these three key formulas in Microsoft Excel. Check out the video below to watch how I work in Excel. Make sure and <a href="https://cms-assets.tutsplus.com/uploads/users/151/posts/28086/attachment/SourceFiles-SUMIF%20COUNTIF%20AVERAGEIF%20Tutorial.zip" target="_self">download the free example workbook</a> to use with this tutorial.</p><figure><div class="wistia_responsive_padding"><div class="wistia_responsive_wrapper"><div class="wistia_embed wistia_async_t8r8wr6eim videoFoam=true"> </div></div></div></figure><p>If you prefer to learn with written, step-by-step instructions, keep reading. I'll share tips for how to use these formulas and ideas for why they're useful.</p><h2>How to Use SUMIF in Excel</h2><p><em>Use the tab titled SUMIF <strong></strong>in the <a href="https://cms-assets.tutsplus.com/uploads/users/151/posts/28086/attachment/SourceFiles-SUMIF%20COUNTIF%20AVERAGEIF%20Tutorial.zip" target="_self">free example workbook</a> for this section of the tutorial.</em><br></p><p>Think of SUMIF <strong></strong>as a way to add values that meet a rule. We can add up a list of values that are from a certain category, or all values greater than or less than a specific amount.</p><p>Here's how the SUMIF formula works:</p><p><code class="inline">=SUMIF(Cells to check, what to check for, Sum of cells that meet the rules)</code><br></p><p>Let's go back to my restaurant expense example to learn the SUMIF formula. Below, I show a list of my transactions for the month.</p><figure class="post_image"><img alt="SUMIF Summary" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/28086/image/sumif-summary.jpg"><figcaption>I have a list of transactions, and I'm going to use SUMIF to help me keep an eye on my spending.</figcaption></figure><p>I want to know two things:</p><ol>
<li>The total of what I spent at restaurants for the month.</li>
<li>All purchases greater than $50 for the month, from any category.</li>
</ol><p>Instead of manually adding data up, we can write two <strong>SUMIF </strong>formulas to automate the process. I'll put the results in the green <strong>Restaurant Expense </strong>box on the right side. Let's look at how.</p><h3>Total Restaurant Expense</h3><p>To find my total restaurant expense, I'll sum up all values with the expense type of "Restaurant", which is in column B.</p><p>Here's the formula I'll use for this example:</p><p><code class="inline">=SUMIF(B2:B17,"Restaurant",C2:C17)</code><br></p><p>Notice that each section is separated by a comma. This formula does three key things:</p><ul>
<li>Looks at what's in cells B2 to B17 for the category of expense</li>
<li>Uses "Restaurant" for the criteria of what to sum up</li>
<li>Uses the values in cells C2 to C17 to total up the amounts</li>
</ul><figure class="post_image"><img alt="SUMIF calculation" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/28086/image/sumif-calculated.jpg"><figcaption>In this example, I'm summing up all values from the expense type "Restaurant."</figcaption></figure><p>When I press enter, Excel calculates the total of my restaurant expenses. Using SUMIF, it's easy to create these quick statistics to help you monitor data of certain types.<br></p><h3>Purchases Greater Than $50</h3><p>We've checked for a specific category, but now let's sum up all values that are <strong>greater than </strong>an amount from any category. In this case, I want to find all purchases that were more than $50.</p><p>Let's write a simple formula to find the sum of all purchases greater than $50:</p><p><code class="inline">=SUMIF(C2:C17,">50")</code></p><p>In this case, the formula is a bit simpler: since we're summing up the same values that we're testing (C2 to C17), we just need to specify those cells. Then, we'll add a comma and ">50" to only sum values greater than $50.</p><figure class="post_image"><img alt="Example SUMIF excel formula" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/28086/image/sumif-values.jpg"><figcaption>Sum up all purchases greater than $50 with a simple Excel formula.</figcaption></figure><p>This example uses a greater than sign, but for bonus points: try to sum up all small purchases, such as all purchases $20 or less.</p><h2>How to Use COUNTIF in Excel</h2><p><em>Use the tab titled COUNTIF <strong></strong>in the <a href="https://cms-assets.tutsplus.com/uploads/users/151/posts/28086/attachment/SourceFiles-SUMIF%20COUNTIF%20AVERAGEIF%20Tutorial.zip" target="_self">free example workbook</a> for this section of the tutorial.</em><br></p><p><em></em>While <strong>SUMIF </strong>is used to add values that meet a certain condition, <strong>COUNTIF </strong>will count up the number of times something appears in a given set of data.<br></p><p>Here's the general format for the <strong>COUNTIF </strong>formula:</p><p><code class="inline">=COUNTIF(cells to count, criteria to count)</code><br></p><p>Using the same set of data, let's count two key pieces of information:</p><ul>
<li>The number of clothing purchases I made in a month</li>
<li>The number of purchases $100 or greater</li>
</ul><h3>Number of Clothing Purchases</h3><p>My first COUNTIF will look at the expense type and count up the number of "Clothing" purchases in my transactions.</p><p>The final formula will be:</p><p><code class="inline">=COUNTIF(B2:B17,"Clothing")</code><br></p><p>That formula looks at the "Expense Type" column, counts up the number of times it sees clothing, and counts them. The result is 2.</p><figure class="post_image"><img alt="COUNTIF Transactions" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/28086/image/countif-results.jpg"><figcaption>The COUNTIF formula counts the number of expenses marked "Clothing" and counts them.</figcaption></figure><h3>Number of $100+ Purchases</h3><p>Now, let's count the number of transactions that were $100 or greater in my list. </p><p>Here's the formula I'll use:</p><p><code class="inline">=COUNTIF(C2:C17,">100")</code><br></p><p>This is a simple, two part formula: simply point Excel to the list of data to count, and the rule to count. In this case, we're checking cells C2 to C17, for all values greater than $100. </p><figure class="post_image"><img alt="COUNTIF Transactions on Value" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/28086/image/countif-values.jpg"><figcaption>Count the number of transactions $100+ with this Excel COUNTIF formula.</figcaption></figure><h2>How to Use AVERAGEIF in Excel</h2><p><em>Use the tab titled AVERAGEIF <strong></strong>in the <a href="https://cms-assets.tutsplus.com/uploads/users/151/posts/28086/attachment/SourceFiles-SUMIF%20COUNTIF%20AVERAGEIF%20Tutorial.zip" target="_self">free example workbook</a> for this section of the tutorial.</em><br></p><p>Last up, let's look at how to use an <strong>AVERAGEIF </strong>formula. By now, it should be no surprise that AVERAGEIF can be used to average specific values, based on a condition that we'll give Excel.</p><p>The format for an AVERAGEIF formula is:</p><p><code class="inline">=AVERAGEIF(Cells to check, what to check for, Average of cells that meet the rules)</code><br></p><p>The format of the AVERAGEIF formula is most similar to the SUMIF formula.</p><p>Let's use the AVERAGEIF formula to calculate two key stats about my spending:</p><ol>
<li>The average of my restaurant expenses.</li>
<li>The average of all expenses less than $25.</li>
</ol><h3>Average of Restaurant Purchases</h3><p>To average my restaurant expenses, <em></em>I'll write an AVERAGEIF formula to average all amounts based on the category.</p><p><code class="inline">=AVERAGEIF(B2:B17,"Restaurant",C2:C17)</code><br></p><p>There are three parts to this formula, each separated by a comma:</p><ul>
<li>B2:B17 specifies the cells to check a condition for. Since the expense type is specified in this column.</li>
<li>"Restaurant" gives the formula something to look for.</li>
<li>Finally, C2:C17 are the cells to average out in our calculation.</li>
</ul><figure class="post_image"><img alt="AVERAGEIF Expenses" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/28086/image/averageif-category.jpg"><figcaption>Here I'm using an AVERAGEIF to average the expense of my annual restaurant trip.</figcaption></figure><p>At the end, Excel averages out the expense of my restaurant trips. I've given it that formula.</p><p>You can also try out this formula by replacing "Restaurant" with another category, like "Clothing."</p><h3>Average of Expenses Less than $25</h3><p>If I'm keeping an eye on my smaller purchases and want to know my average, I can write an AVERAGEIF for all purchases less than an amount.</p><p>Here's the formula that I'll use to do that:</p><p><code class="inline">=AVERAGEIF(C2:C17,"<25")</code><br></p><p>This simple formula just checks the values in column C, and averages all values greater than $25.</p><figure class="post_image"><img alt="AVERAGEIF Values" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/28086/image/averageif-values.jpg"><figcaption>Simple Excel AVERAGEIF formula for expenses Less than $25.</figcaption></figure><h2>Recap and Keep Learning</h2><p>In this tutorial, you learned how to use three conditional math formulas to review your data. Whether you're summing, counting, or averaging data, these functions are advanced Excel skills you can put to good use.</p><p>For all of the "IF" formulas in this tutorial, the key takeaway is that you can apply conditions to your calculations in Excel.</p><p>Learning begets learning. Here are three more Excel tutorials to keep growing:</p><ul>
<li>In addition to COUNTIF, SUMIF, and AVERAGEIF, there are also general "IF" statements that can be used for other conditions. Check out our tutorial on <a href="https://business.tutsplus.com/tutorials/how-to-use-simple-if-statements-in-excel--cms-27819" rel="external" target="_blank">How to Use Simple IF Statements</a>.</li>
<li>Learn to use <a href="https://business.tutsplus.com/tutorials/excel-date-and-time-formulas--cms-28023" rel="external" target="_blank">Excel Dates and Times</a> in conjunction with these formulas to work with values based upon date.</li>
<li>The Excel VLOOKUP function can be used to match values from multiple lists. Learn more about <a href="https://business.tutsplus.com/tutorials/how-to-use-the-excel-vlookup-function--cms-27514" rel="external" target="_blank">How to Use the Excel VLOOKUP Function</a> in this tutorial.</li>
</ul><p>Can you think of other ways to use these formulas to add logic and conditions to your spreadsheet? Let me know with a comment below.</p>2017-01-31T12:55:50.000Z2017-01-31T12:55:50.000ZAndrew Childresstag:computers.tutsplus.com,2005:PostPresenter/cms-28023How to Work With Date and Time Formulas in Excel<p>If you use Excel regularly, I'm sure you've come across <strong>dates </strong>and <strong>times </strong>in your cells. Data often has a record of when it was created or updated, so knowing how to work with this data is essential.</p><p>Here are three key skills that you'll learn in this tutorial:</p><ul>
<li>How to <strong>format dates </strong>in Excel so that they appear in your preferred style</li>
<li>Formulas to calculate the number of days, months, and years between two dates</li>
<li>An Excel date formula to log today's date, and a keyboard shortcut to add the current time</li>
</ul><p>Microsoft Excel can basically do anything with data, if you just know how. This tutorial is another key step to adding skills to your Excel toolbelt. Let's get started.</p><h2>Excel Date and Time Formulas (Quick Video Tutorial)</h2><p>This screencast will walk you through how to work with dates and times in Excel. I cover formatting dates to different styles, as well as Excel date formulas to calculate and work with dates. Make sure to <a href="https://cms-assets.tutsplus.com/uploads/users/151/posts/28023/attachment/SourceFiles-Date%20&%20Time%20Tutorial.zip" rel="external" target="_blank">download the free Excel workbook</a> with exercises that I've attached to this tutorial.</p><figure><div class="wistia_responsive_padding"><div class="wistia_responsive_wrapper"><div class="wistia_embed wistia_async_4zdsty6pf7 videoFoam=true"> </div></div></div></figure><p>Keep reading for a written reference guide on how to format dates and times in Excel, and work with them in your formulas. I'll even share several tips that weren't covered in the screencast.</p><h2>Typing Dates and Times in Excel</h2><p><em>For this part of the tutorial, use the tab titled "</em><i>Typing Dates & Times" in the <a href="https://cms-assets.tutsplus.com/uploads/users/151/posts/28023/attachment/SourceFiles-Date%20&%20Time%20Tutorial.zip" rel="external" target="_blank">example workbook</a>.</i><em></em></p><p>One of the keys to working with dates and times in Excel is capturing the data correctly. Here's how to type dates and times in your Excel spreadsheets: </p><h3>1. How to Type Dates</h3><p>I recommend typing dates in the same format that your system uses. For our American readers, a full date would be in the <strong>"day/month/year"</strong> format. European style dates are <strong>"month/day/year."</strong></p><figure class="post_image"><img alt="Date in Excel" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/28023/image/typed-date.png"></figure><p>When I'm typing dates, I always type in the full date with the month, day and year. If I only want to show the month and the year, I'll simply format it that way (more on that in a minute.)</p><h3>2. How to Type Times</h3><p>It's easy to type times in Excel. We can specify anything from just an hour of the day, to the exact second that something took place.</p><p>If I wanted to log the time as 4PM, I'd type "4 pm" into a cell in Excel and then press enter:</p><figure class="post_image"><img alt="Excel times" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/28023/image/excel-auto-time.jpg"></figure><p>Notice once we press enter, Excel converts what we've typed into a hours : minutes: seconds data format. </p><p>Here's how to log a more specific time in your spreadsheet:</p><figure class="post_image"><img alt="More specific time format in Excel" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/28023/image/time-in-spreadsheet.jpg"></figure><p>The key is to use colons to separate the section of the time data, and then add a space plus "AM" or "PM."<br></p><h3>3. How to Type Date-Time Together</h3><p>You can also type combinations of dates and times in Excel for highly specific timestamps.</p><p>To type a date-time combination, simply use what we've already learned about typing dates, and typing times. </p><figure class="post_image"><img alt="Combined date and time formats in Excel" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/28023/image/date-time.jpg"></figure><p>Notice that Excel has converted the time to a 24 hour format when it's used in conjunction with a date, by default. If you want to change the style of this date, keep reading.</p><h3>Bonus: Excel Keyboard Shortcut for Current Time</h3><p>One of my favorite Excel keyboard shortcuts inserts the current time into a spreadsheet. I use this formula often, when I'm noting the time I made a change to my data. Try it out:<br></p><p><code class="inline">Control + Shift + ;</code></p><h2>Formatting Dates in Excel</h2><p><em>For this part of the tutorial, use the tab titled "</em><i>Formatting Dates & Times" in the <a href="https://cms-assets.tutsplus.com/uploads/users/151/posts/28023/attachment/SourceFiles-Date%20&%20Time%20Tutorial.zip" rel="external" target="_blank">example workbook</a>.</i></p><p>What can you do when your dates are European style dates? That is, they're in a day-month-year format, and you need to convert them to the more familiar month-day-year format?</p><figure class="post_image"><img alt="Excel Multiple Date-Time Formats" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/28023/image/multiple-formats.png"><figcaption>All of these cells contain exactly the same data, they're just formatted in different ways.</figcaption></figure><p>In the screenshot above, what might surprise you is that all six of those cells contain exactly the same data - "1/22/2017." What differs is how they're formatted in Excel. The original data is identical, but it can be formatted to show in a variety of ways.</p><p>In most cases, it's better to use <strong>formatting </strong>to modify the style of our dates. We don't need to modify the data itself - just change how it's presented.</p><h3>Format Excel Cells</h3><p>To change the appearance of our date and time data, make sure that you're working on the <strong>Home </strong>tab of Excel. On the <strong>Ribbon </strong>(menu at the top of Excel), find the section labeled <strong>Number. </strong></p><p>There's a small arrow in the lower right corner of the section. Click it to open the <strong>Format Cells menu.</strong></p><figure class="post_image"><img alt="Excel Format Cells" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/28023/image/excel-format-cells.jpg"><figcaption>To format cells in Excel with built in styles, make sure you're working on the <strong>Home </strong>tab and click the dropdown arrow next to the word <strong>"Number</strong>" in this screenshot.</figcaption></figure><p>The <strong>Format Cells</strong> menu has a variety of options for styling your dates and times. You could turn "1/22/2017" into "Sunday, January 22nd" with just formatting. Then, you could grab the format painter and change all of your cell styles.</p><figure class="post_image"><img alt="Format Cells Excel" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/28023/image/format-cells-options.jpg"><figcaption>The Format Cells menu allows you to change the styles of your dates and times without the work of changing the original date.</figcaption></figure><p><strong></strong>Spend some time exploring this menu and trying out the different styles for your Excel dates and times.<br></p><h2>Get Data From Dates and Times</h2><p>Let's say that we have a list of data that has very specific dates and times, and we want to get simpler versions of those formulas. Maybe we have a list of exact transaction dates, but we want to work with them at a higher level, grouping them by year or month.</p><p>You can get the year from a date with this Excel formula:<br></p><p><code class="inline">=YEAR(CELL)</code></p><figure class="post_image"><img alt="Year Month" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/28023/image/year-formula.jpg"></figure><p>To get just the month from a date cell, use the following Excel formula:<br></p><p><code class="inline">=MON(CELL)</code></p><figure class="post_image"><img alt="Month Formula" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/28023/image/month-formula.jpg"></figure><h2>Find the Difference Between Dates and Times</h2><p><em>For this part of the tutorial, use the tab titled "</em><i>DATEDIF" in the <a href="https://cms-assets.tutsplus.com/uploads/users/151/posts/28023/attachment/SourceFiles-Date%20&%20Time%20Tutorial.zip" rel="external" target="_blank">example workbook</a>.</i><br></p><p>While formats are used to change how dates and times are presented, <strong>formulas</strong> in Excel are used to modify, calculate, or work with dates and times programatically.<br></p><p>The <strong>DATEDIF </strong>formula is powerful for calculating differences between days. Give the formula two dates and and it will return the number of days, months, and years between two dates. Let's look at how to use it.</p><h3>1. Days Between Dates</h3><p>This Excel date formula will calculate the number of days between two dates:<br></p><p><code class="inline">=DATEDIF(A1,B1,"d")</code><br></p><p>The formula takes two cells, separated by commas, and then uses a "d" to calculate the difference in days. </p><figure class="post_image"><img alt="DATEDIF days" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/28023/image/datedif-days.png"><figcaption>The DATEDIF formula takes two date cells and calculates the days between them.</figcaption></figure><p>Here are some ideas for how you could use this Excel date formula to your advantage:</p><ul>
<li>Calculate the difference between today and your birthday to start a birthday countdown</li>
<li>Use a DATEDIF to calculate the difference between two dates and divide your stock portfolio's growth by the number of days to calculate the growth (or loss!) per day</li>
</ul><h3>2. Months Between Dates</h3><p>DATEDIF also calculates the number of months between two dates. This date formula in Excel is very similar, but substitutes an "m" for "d" to calculate the difference in months:</p><p><code class="inline">=DATEDIF(A1,B1,"m")</code><br></p><p>However, there's a quirk in the way Excel applies DATEDIF: it calculates <strong>whole months </strong>between dates. See the screenshot below.</p><figure class="post_image"><img alt="Datedif Months" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/28023/image/datedif-m.png"></figure><p>To me, there are three months between January 1st and March 31st (all of January, all of February, and almost all of March.) However, because Excel uses whole months, it only considers January and February as completed, whole months, so the result is "2." </p><p>Here's my preferred way to calculate the number of months between two dates. We'll find the date difference in days, and then divide it by the average number of days in a month - 30.42 . </p><p><code class="inline">=(DATEDIF(A1,B1,"d")/30.42)</code><br></p><p>Let's apply our modified DATEDIF to two dates:</p><figure class="post_image"><img alt="" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/28023/image/better-datedif-months.png"></figure><p>Much better. The output of 2.99 is very close to 3 full months, and this will be much more useful in future formulas.</p><p>The official Excel documentation has a <a href="https://support.microsoft.com/en-us/kb/214134#bookmark-2" rel="external" target="_blank">complex method to calculate months</a> between dates, but this is a simple and easy way to get it pretty close. Writing a good Excel formula is about finding the sweet spot of precision and simplicity, and this formula does both.</p><h3>3. Years Between Dates</h3><p>Finally, let's calculate the number of years between two dates. The official way to calculate years between dates is with the following formula:</p><p><code class="inline">=DATEDIF(A1,B1,"y")</code><br></p><p>Notice that this is the same as our past DATEDIF formulas, but we've simply substituted the last part of the formula with "y" to calculate the number of years between two dates. Let's see it in action:</p><figure class="post_image"><img alt="Excel formula for calculating years between dates" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/28023/image/datedif-y.png"></figure><p>Notice that this works like the DATEDIF for months: it counts <em>only </em>full years that have passed. I'd rather include partial years passing as well. Here's a better DATEDIF for years:</p><p><code class="inline">=(DATEDIF(A1,B2,"d")/365)</code></p><p>Basically, we're just getting the date difference in days, and then dividing it by 365 to calculate it as a year. Here's the results:</p><figure class="post_image"><img alt="Better Excel formula for calculating years between dates" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/28023/image/betterdatedif-y.png"></figure><p>DATEDIF is extremely powerful, but watch out for how it works: it's going to only calculate full months or years that have passed by default. <em>Use my modified versions for more precision in the results.</em></p><h3>Bonus: Work Days Between Dates</h3><p>The Excel date formulas covered above focus on the number of business days between dates. However, it's sometimes helpful to just calculate the number of workdays (basically weekdays) between two dates.</p><p>In this case, we'll use <strong>=NETWORKDAYS </strong>to calculate the number of workdays between two dates.</p><p><code class="inline">=NETWORKDAYS(A1,B1)</code><br></p><p>In the screenshot below, I show an example of using NETWORKDAYS. You can see the calendar showing how the formula calculated a result of "4." </p><figure class="post_image"><img alt="Using Excel Date Formula with NETWORKDAYS" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/28023/image/network-days.jpg"></figure><p>If you have known holidays in the timeframe that you want to exclude, check out the official <a href="https://support.office.com/en-us/article/NETWORKDAYS-function-48e717bf-a7a3-495f-969e-5005e3eb18e7" rel="external" target="_blank">NETWORKDAYS documentation</a>.</p><h2>Recap and Keep Learning</h2><p>Dates and times are ubiquitous to spreadsheets. Excel date formulas and formatting options are helpful. The techniques in this tutorial can take your Excel skills to the next level so that you can incorporate date-driven data seamlessly into your spreadsheets.</p><p>You've added one skill to your Excel toolbox - why stop here? Chain Excel formulas and skills to create powerful spreadsheets. To keep learning more about working with Excel spreadsheets, check out these other resources:</p><ul>
<li>IF Statements are logic built into your spreadsheet that show results based on conditions. You could combine an IF Statement with a date range to show data based on a date or time. Check out <a href="https://business.tutsplus.com/tutorials/how-to-use-simple-if-statements-in-excel--cms-27819" rel="external" target="_blank">this tutorial</a> to learn them.</li>
<li>You could combine the <a href="https://business.tutsplus.com/tutorials/how-to-use-the-excel-vlookup-function--cms-27514" rel="external" target="_blank">VLOOKUP tutorial</a> with date and time formulas to match values based on a date or time.</li>
<li>
<a href="https://business.tutsplus.com/tutorials/how-to-find-and-remove-duplicates-in-excel-quickly--cms-27635" rel="external" target="_blank">Find and Remove Duplicates</a> is an Excel function used in combination with date and time data, as it's often one of the best bits of data to check for duplicates.</li>
</ul><p>How do you work with calendar and time data in your Excel spreadsheets? Are there are any Excel date or time formulas that I'm missing from this tutorial that you use regularly? If so, let me know in the comments.</p>2017-01-19T15:21:11.329Z2017-01-19T15:21:11.329ZAndrew Childresstag:computers.tutsplus.com,2005:PostPresenter/cms-27819How to Use Simple IF Statements in Excel<p>Excel is one of the most powerful and easiest-to-use tools for working with data. A spreadsheet is really just a tool for organizing, calculating, and reviewing your data with.</p><p>What happens when we need to build options into our spreadsheet? This is where if statements really come into play. </p><p>Imagine that we have a spreadsheet with a list of scores from our school courses. I want to remind myself if I'm passing or failing a class with a simple "passing" or "failing" text. I can write an <strong>IF statement formula </strong>to look at the final grade, and then print "passing" or "failing" based on the score.</p><figure class="post_image"><img alt="Excel IF statement example" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/27819/image/if-statement-example.jpg"><figcaption>This simple IF statement example looks at the score in column B and reminds me if I'm passing or failing a class based on the score.</figcaption></figure><p>An Excel IF formula makes your spreadsheets much smarter. We can give a cell different choices for what to show based on a condition. An IF formula lets us build in logic to our spreadsheet. In this tutorial, I'll teach you how to use them.</p><h2>How to Use Excel IF Statements (Video Tutorial)</h2><p>In this screencast, you'll see how I use IF statements in Excel to make a spreadsheet smarter. Watch this video to walk through building IF statements, and then check out the tutorial below for written instructions.</p><figure><div class="wistia_responsive_padding"><div class="wistia_responsive_wrapper"><div class="wistia_embed wistia_async_47ezejo7vl videoFoam=true"> </div></div></div></figure><p>Before we move on with the tutorial, I'd recommend downloading the free <a href="https://cms-assets.tutsplus.com/uploads/users/151/posts/27819/attachment/SourceFiles-If%20Statement%20Tutorial.zip" rel="external" target="_blank">example workbook</a> I've created for this tutorial.<br></p><h2>How Does an Excel IF Statement Work?</h2><p>There are three key parts to a successful IF statement:</p><ol>
<li>
<strong>Something to Check </strong>- Basically, what should the IF statement check for? We could check to see if a cell equals a certain number, or to see if it contains a certain string of text, for example. </li>
<li>
<strong>What to Show If True - </strong>If the statement contains what we're checking for, what should the cell show?</li>
<li>
<strong>What to Show If False - </strong>If the statement <em>doesn't </em>contain what we're looking for, what should the fallback be?</li>
</ol><p>An Excel IF statement begins with =IF( .The official Excel documentation shows the structure of an Excel IF statement:</p><p><code class="inline">=IF(logical_test,[value_if_true],[value_if_false])</code><br></p><p>In plain English, here's how I think of using an IF Statement:</p><p><code class="inline">=IF(what to check For, what to show if true, what to show if false)</code></p><p>Simple enough? If this still seems a bit vague, let's move onto our very first example of how to use an IF statement, to see how it works.</p><h2>Writing Your First IF Statement</h2><p><em>For this example, use the tab titled "Check for Blank" in the <a href="https://cms-assets.tutsplus.com/uploads/users/151/posts/27819/attachment/SourceFiles-If%20Statement%20Tutorial.zip" target="_self">example workbook.</a></em><br></p><p>We'll start off with the simplest possible IF statement example. In this case, we're just going to check to see if a cell is blank and print a message.</p><p>Let's say that we're creating a sign up list for our office holiday party. We need to write a formula to determine who to remind to sign up for a dish. At the beginning, our data is a simple list of names and what they've signed up to bring:</p><figure class="post_image"><img alt="Before Example of If Statement" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/27819/image/holiday-party-sign-up-list.jpg"></figure><p>Even though I can see that some cells are blank, let's write an IF formula to print out who we need to remind in column D. My IF formula should check if the cells in column C are blank, and print a reminder to the person if they've not signed up yet.</p><p>Here's what I wrote:</p><p><code class="inline">=IF(C2="","remind them!","they've already signed up.")</code><br></p><p>Let's dissect this formula in four parts:</p><ol>
<li>Every IF statement opens with =IF(</li>
<li>Next, I'm going to check if the values in column C are empty, so I wrote <code class="inline">C2="",</code>Writing two quotation marks with no text between them will check to see if a cell is empty. The comma at the end helps us move onto the next part of the formula. </li>
<li>The next part of the formula is what will show anytime the formula is true. Since we're checking to see if the cell is blank, we're going to print "remind them!" when it is blank.</li>
<li>If C2 does contain some text, we'll print that the person has already signed up.</li>
</ol><p>And finally, here's the results when we finish the formula and pull it down:</p><figure class="post_image"><img alt="IF Statement walkthrough" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/27819/image/excel-if-usage.jpg"><figcaption>After writing the IF statement and pulling it down, each cell in column D outputs the correct value.</figcaption></figure><p>Perfect! Notice that when the adjacent cell in column C is blank, Excel prints that we need to remind that person to sign up. We could filter for Column D now and then send them a reminder via email.</p><p>This is a simple example for how we can use if statement formulas in Excel to give our spreadsheets some logic. Let's look at some others.</p><h2>Testing for Values</h2><p><em>Use the Test Values tab in the <a href="https://cms-assets.tutsplus.com/uploads/users/151/posts/27819/attachment/SourceFiles-If%20Statement%20Tutorial.zip" target="_self">example workbook</a> for this exercise.</em><br></p><p>So far, we used an IF statement to see if a cell contained no text. Now, let's use an IF statement to test for a numerical value.</p><p>In the first example, we checked to see if the cells in column C contained a specific bit of text. Notice that we used the equals sign to see if a cell was equal to a blank. We can use any of the math operators to test for values, such as:</p><ul>
<li>
<strong>= </strong>- check to see if a cell is <strong>equal to</strong> a specific value</li>
<li>
<strong><> </strong>- check to see if a cell is <strong>not equal to</strong> a specific value</li>
<li>
<strong>> </strong>- check to see if a cell is <strong>greater than</strong> the value in the formula<br>
</li>
<li>
<strong><</strong> - check to see if a cell is <strong>less than</strong> the value in the formula</li>
<li>
<strong>>=</strong> - check to see if a cell is <strong>greater than or equal to</strong> the value in the formula</li>
<li>
<strong><=</strong> - check to see if a cell is <strong>less than or equal to</strong> the value in the formula</li>
</ul><p>Let's say that we need to take inventory of our warehouse. We sell our product in batches of 10, so every batch we count should have exactly that amount. Let's write a formula to check for it.</p><p>In the data below, I've gone out and taken stock of products in my warehouse. I need to write a formula in column E to check and make sure that we have exactly 10 of each product. We should print a warning message if we don't have 10 of each item.</p><p>In column E, here's the formula that I'll write:</p><p><code class="inline">=IF(D4<>10,"needs attention","")</code><br></p><p>Let's dissect the formula:</p><ul>
<li>First, I use the <strong><> </strong>operator to check to see if the value in cell D4 <em>doesn't </em>equal 10, which indicates a problem.</li>
<li>Next, I've got a message in quotations of "needs attention" which will show anytime the adjacent cell isn't 10.</li>
<li>Finally, I add a comma and two quotation marks with nothing between them so that if the count was 10, my formula simply prints nothing.</li>
</ul><figure class="post_image"><img alt="If Statement Value Test" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/27819/image/if-statement-value-test.jpg"><figcaption>Needs attention follow-up action result.</figcaption></figure><p>In the screenshot above, you'll see how I used the formula to add warning messages for each of my inventory counts. If the value in column D isn't exactly 10, Excel will print "needs attention" in column E.</p><h2>How to Use IFERROR</h2><p>There's a special IF statement formula built into Excel for fixing errors in your formulas. The IFERROR formula is similar to an IF statement, but catches a wide array of errors and replaces them with more useful data.</p><p>According to Microsoft's documentation, here are errors in your spreadsheet that IFERROR can catch and revise:</p><ul>
<li>#N/A</li>
<li>#VALUE!</li>
<li>#REF!</li>
<li>#DIV/0!</li>
<li>#NUM!</li>
<li>#NAME?</li>
<li>#NULL!</li>
</ul><p>To use IFERROR, bracket an existing formula with an IFERROR formula, and then provide a fallback value.</p><p>This is how I think of using IFERROR:</p><p><code class="inline">=IFERROR([your existing formula],[what to replace the error with])</code></p><p>Let's walk through an extremely useful case for IFERROR. <br></p><h3>An IFERROR Example</h3><p><em>Use the IFERROR </em><em>tab in the <a href="https://cms-assets.tutsplus.com/uploads/users/151/posts/27819/attachment/SourceFiles-If%20Statement%20Tutorial.zip" target="_self">example workbook</a> for this section.</em><br></p><p>One place that you'll run into errors is with the VLOOKUP formula. If you're using a VLOOKUP to match values, there's a chance that your lookup table won't contain a match. When that happens, you're going to get an "N/A" error in your spreadsheets.</p><p>In the spreadsheet below, I've got a list of freelancers working for me on a job. I've got their company listed, and want to know what each person is doing. For most of the freelancers, I have a lookup table to the right that's pulling in their roles.</p><figure class="post_image"><img alt="IFERROR Example Implementation " data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/27819/image/if-error-example-usecase.jpg"><figcaption>This spreadsheet has some N/A errors because my lookup table is missing matches.</figcaption></figure><p>I've already written a VLOOKUP to pull in the job role, which matches the companies in column B with my lookup table:</p><p><code class="inline">=VLOOKUP(B2,$E$4:$F$6,2,FALSE)</code><br></p><p>Here's the problem: several of the companies aren't in my lookup table, which is why we have these nasty N/A errors. Let's replace them with something more elegant. The IFERROR that we'll want to write will follow this structure:</p><p><code class="inline">=IFERROR(the existing formula, what to replace an error with)</code><br></p><p>In my case, I'm going to replace the errors with a reminder to contact my HR manager to check what the freelancer is working on. Here's my proposed formula:</p><p><code class="inline">=IFERROR(VLOOKUP(B3,$E$4:$F$6,2,FALSE),"Contact HR")</code><br></p><p>This formula looks complex, but just remember: all that we're doing is bracketing an existing VLOOKUP with a replacement value. We simply surround our current formula with an IFERROR and give Excel a fallback value.</p><figure class="post_image"><img alt="IFERROR Implemented Example" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/27819/image/iferror-implemented.jpg"><figcaption>I've replaced the N/A errors with a much more useful and readable "Contact HR" text.</figcaption></figure><p>In short, IFERROR is a special IF statement that Microsoft designed for our convenience. It automatically catches formula errors and outputs a replacement value.</p><h2>Recap and Keep Learning</h2><p>IF statements are a method to add conditional logic to your spreadsheets. In this tutorial, you learned to use the formula and some possible uses for it in your spreadsheets.</p><p>No matter how long I use Excel, I keep finding out that I have more to learn. If you want to keep learning how spreadsheets can be used, here are some follow-up tutorials:</p><ul>
<li>The official Microsoft documentation is always my first stop when learning a new formula, and their <a href="https://support.office.com/en-us/article/IF-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2" rel="external" target="_blank">page on IF statements</a> is no slouch.</li>
<li>If you're thinking about IF statements, you're probably on the advanced side of Excel users. A complementary skill is <a href="https://business.tutsplus.com/tutorials/how-to-use-the-excel-vlookup-function--cms-27514" rel="external" target="_blank">using VLOOKUP to match</a> elements of lists. </li>
<li>If you want to get advanced, try this <a href="https://exceljet.net/formula/nested-if-function-example" target="_self">Nested IF functions tutorial</a> from Exceljet. Instead of a simple IF statement that has only two possible outcomes, nesting a combination of these formulas give you more options.</li>
</ul><p>If you have an Excel IF statement issue, let me know in the comments. Or, if you have ideas on how to use these formulas to share with other readers, the comments are open.</p>2017-01-04T12:55:10.000Z2017-01-04T12:55:10.000ZAndrew Childresstag:computers.tutsplus.com,2005:PostPresenter/cms-27554How to Work With Excel Math Formulas (Guide to the Basics)<p>It's hard to get excited about learning math. It's something that most of us spend our lives avoiding. It's also one of the best reasons to use Microsoft Excel for perfect calculations, every time.</p><p>Don't think of these Excel formulas as <em></em>math for math's sake. Instead, imagine how these formulas can help you automate your life and skip the trouble of making manual calculations. At the end of this tutorial, you'll have the skills you need to do all of the following, for example:</p><ul>
<li>Calculate the average score of your exams.</li>
<li>Quickly subtotal the invoices you've issued to clients.</li>
<li>Use basic statistics to review a set of data for trends and indicators.</li>
</ul><p>You don't have to be an accountant to master math in Excel, however. To follow along with this tutorial, make sure to <a href="https://cms-assets.tutsplus.com/uploads/users/151/posts/27554/attachment/SourceFiles-Math%20Formula%20Tutorial.zip" target="_self"><strong>download the example Excel workbook</strong></a> here or click on the blue <strong>Download Attachment </strong>button on the right side of this tutorial—in either case it's free to use. </p>
<p>The Excel workbook has comments and instructions for how to use these formulas. As you follow along in this tutorial, I'll teach you some of the essential "math" skills. Let's get started.</p><h2>Basic Excel Math Formulas Video (Watch and Learn)</h2><p>If learning from a screencast video is your style, check out the video below to walk through the tutorial. Otherwise, keep reading for a detailed description of how to work with each Excel math formula.</p><figure><div class="wistia_responsive_padding"><div class="wistia_responsive_wrapper"><div class="wistia_embed wistia_async_dlslorirzj videoFoam=true"> </div></div></div></figure><h2>Formula Basics</h2><p>Before we get started, let's look at how to use any formula in Microsoft Excel. Whether you're working with the math formulas in this tutorial or any others, these tips will help you master Excel.</p><h3>1. Each Formula in Excel Starts with "="</h3><p>To type a formula, click in any cell in Microsoft Excel and type the equals sign on your keyboard. This starts a formula.</p><figure class="post_image"><img alt="Basic Excel Formula" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/27554/image/basic-formula.png"><figcaption>Every basic Excel formula starts with the equals sign, and then the formula itself.</figcaption></figure><p>After the equals sign, you can put an incredible variety of things into the cell. Try typing <strong>=4+4 </strong>as your very first formula, and press enter to return the result. Excel will output <strong>8</strong>, but the formula is still behind the scenes in the spreadsheet.</p><h3>2. Formulas are Shown in Excel's Formula Bar</h3><p>When you're typing a formula into a cell, you can see the results of the cell once you press enter. But when you select a cell, you can see the formula for that cell in the formula bar.</p><figure class="post_image"><img alt="Excel formula bar" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/27554/image/excel-formula-bar.png"><figcaption>Click on a cell in Excel to show the formula in it, such as the multiplication formula that evaluates to 125.</figcaption></figure><p>To use the example from above, the cell will show <strong>"8" , </strong>but when we click on that cell, the formula bar will show that the cell is adding 4 and 4.</p><h3>3. How to Build a Formula</h3><p>In the example above, we typed a simple addition formula to add two numbers. But, you don't have to type numbers, you can also reference other cells. </p><p>Excel is a grid of cells, with the <strong>columns </strong>running left to right, each assigned a letter, while the <strong>rows </strong>are numbered. Each cell is an intersection of a row and a column. The cell where column A and row 3 intersect is called <strong>A3, </strong>for example.</p><figure class="post_image"><img alt="Excel formula format" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/27554/image/excel-evaluated-cells.png"><figcaption>Excel formulas can be written to use the values in multiple cells, such as multiplying A1 and B1 to get the value in C1, which is 125.</figcaption></figure><p>Let's say that I have two cells with simple numbers, like 1 and 2, and they are in cells A2 and A3. When I type a formula, I can start the formula with <strong>"=" </strong>as always<strong>. </strong>Then, I can type:</p><p><strong><code class="inline">=A2+A3</code><br></strong></p><p>...to add those two numbers together. It's very common to have a sheet with values, and then a separate sheet where calculations are performed. Keep all of these tips in mind while working with this tutorial. For each of the formulas, you can reference cells, or directly type numerical values into the formula. </p><p>If you need to change a formula that you've already typed, double click on the cell. You'll be able to adjust the values in the formula.</p><h2>Arithmetic in Excel</h2><p>Now we'll cover Excel math formulas and walk through how to use them. Let's start off by exploring the basic arithmetic formula. These are the foundation of math operations.</p>
<h3>1. Add and Subtract</h3><p>Addition and subtraction are two essential math formulas in Excel. Whether you're adding up your list of business expenses for the month or balancing your checkbook digitally, the addition and subtraction operators are incredibly useful.</p><p><em>Use the tab titled "Add and Subtract" </em><em>in the workbook for practice.</em><br></p><h4>Add Values</h4><p><em>Example:</em></p><p><code class="inline">=24+48</code><br></p><p>or, reference values in cells with:</p><p><code class="inline">=A2+A3</code></p><blockquote>Tip: try adding five or six values to see what happens. Separate each item with a plus sign.</blockquote><figure class="post_image"><img alt="Add Formulas Excel" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/27554/image/add-formulas-excel.png"><figcaption>Adding values in Excel is as easy as typing two numbers and a "+" sign, or adding two cell references.</figcaption></figure><h4>Subtract Values</h4><p><em>Example:</em></p><p><code class="inline">=75-25-10</code><br></p><p>or, reference values in cells with:<br></p><p><code class="inline">=A3-A2</code></p><figure class="post_image"><img alt="Subtract Formulas Excel" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/27554/image/subtraction.png"><figcaption>Subtract two values in Excel by typing numbers directly in a box, separated with "<strong>-" </strong>sign, or reference two cells with the "<strong>-" </strong>sign between them.</figcaption></figure><h3>2. Multiplication</h3><p>To multiply values, use the <strong>* </strong>operator. Use multiplication instead of adding the same item over and over.</p><p><em>Use the tab titled "Multiply" </em><em>in the workbook for practice.</em><br></p><p><em>Example:</em><br></p><p><em><code class="inline">=5*4</code></em></p><p>or, reference values in cells with:</p><p><code class="inline">=A2*A3</code></p><figure class="post_image"><img alt="Excel Multiplication formula" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/27554/image/excel-multiplication-formula.png"><figcaption>Multiplication in Excel uses the <strong>* </strong>symbol between two values or cell references.</figcaption></figure><h3>3. Division</h3><p>Division is helpful when splitting items into groups, for example. Use the <strong>"/" </strong>operator to divide numbers or the values in cells in your spreadsheet.</p><p><em>Use the tab titled "Divide" </em><em>in the workbook for practice.</em><br></p><p><em><strong></strong>Example:<br></em></p><p><em><strong><code class="inline">=20/10</code></strong><br></em></p><p>or, reference values in cells with:<br></p><p><code class="inline">=A5/B2</code></p><figure class="post_image"><img alt="Excel Division Formula" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/27554/image/excel-division-formula.png"><figcaption>Divide values in excel with the <strong>/ </strong>operator, either with values in a formula or between two cells.</figcaption></figure><h2>Basic Statistics</h2><p><em>Use the tab titled "Basic Statistics" </em><em>in the workbook for practice.</em><br></p><p>Now that you know the basic math operators, let's move onto something more advanced. Basic statistics are helpful to review a set of data and to make informed judgments about it. Let's cover several popular, simple statistic formulas.</p><h3>1. Average</h3><p>To use an average formula in Excel, open your formula with <strong>=AVERAGE( </strong>and then input your values. Separate each number with a comma. When you press enter, Excel will calculate and output the average.</p><p><code class="inline">=AVERAGE(1,3,5,7,10)</code><br></p><p>The best way to calculate an average is to input your values into separate cells in a single column.</p><p><code class="inline">=AVERAGE(A2:A5)</code></p><figure class="post_image"><img alt="Excel Averages " data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/27554/image/excel-averages.png"><figcaption>Use the <strong>=AVERAGE </strong>formula to average a list of values separated by commas, or a set of cells as the bottom example shows.</figcaption></figure><h3>2. Median</h3><p>The median of a data set is the value that's in the middle. If you took the numerical values and ordered them from smallest to largest, the median would be smack dab in the middle of that list.<br></p><p><code class="inline">=MEDIAN(1,3,5,7,10)</code><br></p><p>I'd recommend typing your values into a list of cells, and then using the median formula over a list of cells with values typed in them.</p><p><code class="inline">=MEDIAN(A2:A5)</code></p><figure class="post_image"><img alt="" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/27554/image/excel-median.png"><figcaption>Use the <strong>=MEDIAN</strong> formula to find the middle value in a list of values when you separate them with commas, or use the formula on a list of cells with values in them.</figcaption></figure><h3>3. Min</h3><p>If you have a set of data and want to keep your eye on the smallest value, the <strong>MIN </strong>formula in Excel is useful. You can use the <strong>MIN </strong>formula with a list of numbers, separated by commas, to find the lowest value in a set. This is very useful when working with large datasets.</p><p><code class="inline">=MIN(1,3,5,7,9)</code><br></p><p>You might want to find the minimum value in a list of data, which is totally possible with a formula such as:</p><p><code class="inline">=MIN(A1:E1)</code><br></p><figure class="post_image"><img alt="Excel Min Formula" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/27554/image/min-excel-formula.png"><figcaption>Use the Excel <strong>MIN </strong>formula with a list of values separated by commas, or with a range of cells to monitor the lowest value in the set.</figcaption></figure><h3>4. Max</h3><p>The <strong>MAX</strong> formula in Excel is the polar opposite of <strong>MIN</strong>; it tells you which value in a set is the largest. You can use it with a list of numbers, separated by commas:</p><p><code class="inline">=MAX(1,3,5,7,9)</code><br></p><p>Or, you can select a list of values in cells, and have Excel return the largest in the set, with a formula like this:</p><p><code class="inline">=MAX(A1:E1)</code><br></p><figure class="post_image"><img alt="Excel Max Formula" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/27554/image/max-excel-formula.png"><figcaption>The Excel <b>MAX </b>formula is very similar to <strong>MIN, </strong>but will help you keep an eye on the largest value in a set, and can be used on a comma separated list of values or data list.</figcaption></figure><h2>Recap and Keep Learning</h2><p>I hope this Excel math formulas tutorial helped you think more about what Excel can do for you. With enough practice, your Excel skills will soon seem more natural than grabbing a calculator or doing math on paper. Spreadsheets capture data, and formulas help us understand or modify that data. The operations can be basic, but are at the foundation of important spreadsheets in every company.</p>
<p>It's always a great idea to use one tutorial to launch more learning. Here are some suggestions</p><ul>
<li>For more advanced math in equation-like formats, Microsoft's official documentation has a page titled "<a href="https://support.office.com/en-us/article/Insert-or-edit-an-equation-or-expression-2878ad40-4162-4231-8e8a-4fe0e6fc5358?CorrelationId=e1d2864b-1bf0-4659-b978-fffe76277c20&ui=en-US&rs=en-US&ad=US&ocmsassetID=HA102928635" rel="external" target="_blank">Insert or edit an equation or expression</a>."</li>
<li>Tuts+ instructor Bob Flisser has a nice tutorial on <a href="https://business.tutsplus.com/tutorials/excel-calculate-percentage-formulas--cms-26630" rel="external" target="_blank">calculating percentages in Excel.</a>
</li>
</ul><p>What are some other math skills that you use in Excel? Leave a reply in the comments if you know a great formula that I don't.</p>2016-12-06T12:55:29.000Z2016-12-06T12:55:29.000ZAndrew Childresstag:computers.tutsplus.com,2005:PostPresenter/cms-27635How to Find and Remove Duplicates in Excel Quickly<p>If you're a Microsoft Excel user, you've likely been asked to work with messy data in a spreadsheet before. Your data might contain duplicate records that repeat. You certainly don't have time to go line by line in a large spreadsheet and manually find and remove the duplicate records.</p><p>The good news is that Microsoft anticipated this exact need in Excel. The <strong>Remove Duplicates </strong>feature helps you quickly find and remove the duplicate records in your spreadsheet.</p><figure class="post_image"><img alt="Example of Removing Duplicates in Microsoft Excel" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/27635/image/example-remove-duplicates-excel.jpg"><figcaption>In just a couple of clicks, I removed the duplicate rows in my spreadsheet with Excel's Remove Duplicates function.</figcaption></figure><p>In this tutorial, I'll teach you to use <strong>Remove Duplicates. </strong>You'll learn to use the function to clean up a spreadsheet with duplicate rows. I'll teach you tips on how to use the feature correctly so that only true duplicate rows are removed.</p><p>To follow along with this tutorial, I recommend downloading the example spreadsheet. You can find the blue <strong>Download Attachment</strong> link to the right of this tutorial or <a href="https://cms-assets.tutsplus.com/uploads/users/151/posts/27635/attachment/SourceFiles-Remove%20Duplicates%20Tutorial.zip" target="_self">download the free spreadsheet workbook here</a>.</p><h2>Get Started With Remove Duplicates in Excel</h2><p><em>If you're following along with the example workbook, use the spreadsheet tab titled <strong>Order List</strong> for this portion of the tutorial.</em><br></p><p>Let's start off with a simple example on how to remove duplicates in Excel. In the spreadsheet below, you can see that there are several lines that are duplicated. We need to remove the duplicate orders to make the ingredient list simpler.</p><figure class="post_image"><img alt="Duplicate Order List Excel" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/27635/image/duplicate-orders-microsoft-excel.jpg"><figcaption>This list has several exact duplicates that we need to remove.</figcaption></figure><h3>1. Highlight Your Data</h3><p>To remove the duplicate rows, the first thing you should do is highlight your data. If your sheet doesn't have data above your table, you can highlight the entire columns at the top of the spreadsheet. In this case, I'm going to highlight the data table to remove duplicates.</p><figure class="post_image"><img alt="Highlight Data to Remove Duplicates Excel" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/27635/image/highlight-data-remove-duplicates.jpg"><figcaption>I've highlighted the rows that contain my data to prepare to remove duplicates.</figcaption></figure><h3>2. Find the Excel Remove Duplicates Feature</h3><p>The <strong>Remove Duplicates </strong>feature lives on Excel's ribbon on the <strong>Data </strong>tab. Specifically, you'll find the <strong>Remove Duplicates </strong>feature in the <strong>Data Tools </strong>section of the ribbon. Once you find it, simply click on it to launch the wizard.</p><figure class="post_image"><img alt="Excel Remove Duplicates Feature" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/27635/image/data-remove-duplicates-ribbon-option.jpg"><figcaption>The Remove Duplicates feature is on the Data tab of the Excel ribbon, in the Data Tools section.</figcaption></figure><h3>
<strong>3. Select Your Duplicate Criteria</strong><br>
</h3><p><strong></strong>After you click on the Remove Duplicates option, a new window will pop up with some checkboxes. This list of options asks you to define which fields need to be checked for duplicates. The default behavior is to keep all of them checked.</p><p>For our simple example, don't make any changes on this window. Press "OK" to remove the duplicates from our table.</p><figure class="post_image"><img alt="List of Criteria for Duplicates Excel" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/27635/image/list-of-criteria-excel.jpg"><figcaption>After clicking on Remove Duplicates, Excel will open a new window with a list of check boxes; make no changes for our simple example.</figcaption></figure><h3><strong>4. Review the Results</strong></h3><p><strong></strong>Once you press OK, Excel will remove the duplicates in the table and provide feedback on what was removed. When working with any dataset, I urge you to review the results. </p><p>The purpose of Remove Duplicates is to delete the repetitive rows, but it requires care in how you use it. You can always undo the last step if you applied it incorrectly.</p><figure class="post_image"><img alt="Duplicate Values Found Removed" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/27635/image/duplicate-values-removed-excel.jpg"><figcaption>Remove Duplicates provides feedback on how many rows were removed and how many rows were left untouched.</figcaption></figure><h2>Quick Remove Duplicates - Video Tutorial </h2><p>Check out the screencast below for a walkthrough on how to use Remove Duplicates in Excel. Keep reading the tutorial below for a written, in-depth guide to the Remove Duplicates feature and some additional techniques to find and delete duplicates to get the most from it.</p><figure><div class="wistia_responsive_padding"><div class="wistia_responsive_wrapper"><div class="wistia_embed wistia_async_i4coo6z3z7 videoFoam=true"> </div></div></div></figure><h2>Remove Duplicates on Multiple Criteria</h2><p>Let's return to the seemingly simple menu that popped up when we highlighted our data. The list on this window represents each of the columns in our Excel spreadsheet. You'll notice that each column in our Excel table has a checkbox next to it. </p><p>What does this menu mean? Basically, this box is asking you how specific Excel should be when removing duplicates. Let's look at an example:</p><figure class="post_image"><img alt="Remove Duplicates on 1 Factor" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/27635/image/before-and-after-remove-dupes-1-factor.jpg"><figcaption>Example of Remove Duplicates on one factor.</figcaption></figure><p>In the screenshot above, I started off by highlighting my data in the table again and launched the <strong>Remove Duplicates </strong>feature. Then, I unchecked all boxes except for <strong>"Chef" </strong>in the Remove Duplicates window. The result is shown in the bottom half of the image. Notice that our table has been reduced to just three rows, with each chef's name appearing once.</p><p>When we left only the "<strong>Chef" </strong>box checked, we asked Excel to only check the Chef column for any duplicates. The first time it saw a repeated chef's name, it deleted the entire row; it didn't matter that the other columns differed.</p><h3>Take Care With Excel's Remove Duplicates</h3><p>This is why it's important to be careful when using the "Remove Duplicates" feature. If you just leave it set to remove a single factor, you may accidentally remove data that you want to keep.<br></p><p><em><strong>Tip</strong>: The boxes you leave checked in the Remove Duplicates window are the combination Excel checks for duplicates. Leave multiple boxes checked for precise duplicate removal.</em><br></p><p><em></em>Often, a single column of data won't be enough to judge Duplicates by. If you maintain an online shop and have a database of customers, chances are that you'll have more than one "Mike Smith" for example in your data. You need to check multiple columns - such as the customer name, plus the customer's address or registration date to check for duplicates.<i> </i>This is exactly why we check multiple columns when removing duplicates.</p><p>If you want a precise way to remove duplicates, leave multiple boxes (columns) checked when running the Remove Duplicates feature. And of course, always double-check your data after running Remove Duplicates.</p><h3>When to Be Selective</h3><p><em>If you're following along with the example workbook, use the spreadsheet tab titled <b>Duplicate Shifts </b>for this portion of the tutorial.</em><br></p><p>You might be wondering: is there a situation where you would uncheck some of the boxes? Absolutely. Let's take a look at an example.</p><p>In the spreadsheet below, I've got employee shift data, and I've accidentally downloaded the report two different times. I have each employee's time in and time out, plus a column with the date that I downloaded the report. Two of each row exists, with the only unique factor being column F, the Report Download date.</p><figure class="post_image"><img alt="Duplicates with fewer criteria" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/27635/image/duplicates-fewer-criteria.jpg"><figcaption>I need to remove duplicates because the report was downloaded twice (see the last column) but need to exclude the last column from my duplicate check.</figcaption></figure><p>Let's think about this: if I check <strong>all </strong>columns for duplicates, Excel won't find any duplicates. But, I actually want to remove the duplicate employee shift data. I don't really care about what day that I downloaded the report, so I need to exclude column F when removing duplicates.</p><figure class="post_image"><img alt="No Duplicates Found Error" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/27635/image/remove-duplicates-too-many-fields.jpg"><figcaption>My rows are almost all exactly the same; column F is the only factor that differs and I don't want Excel to check it as part of Remove Duplicates. If I leave all boxes checked, Excel won't find or remove any dupes.</figcaption></figure><p>These rows aren't <em>exact</em> duplicates—they don't share all fields—but I need to remove Duplicates based on columns A-E. </p>
<p>To do this, I'll highlight the table again and run <strong>Remove Duplicates </strong>again. This time, I'll leave all boxes checked except for the <strong>Report Download Date.</strong></p><figure class="post_image"><img alt="Removed duplicates in Excel while ignoring a column" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/27635/image/select-criteria-remove-duplicates.jpg"><figcaption>Notice that Excel has removed the duplicates because it ignored checking column F as part of the process.</figcaption></figure><p>Think of it like this: the boxes that you leave checked are the columns that Excel includes in its duplicate check step. If there are <em>extra</em> columns that shouldn't be checked for duplicates, uncheck them from the Remove Duplicates window.</p><h2>Use Remove Duplicates for Blank Rows</h2><p>One of my favorite uses for Remove Duplicates is to get rid of multiple blank rows in my data.</p><figure class="post_image"><img alt="Remove Blank Rows Excel" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/27635/image/remove-blank-rows.jpg"><figcaption>Remove Duplicates is also fantastic for removing blank rows in a messy spreadsheet.</figcaption></figure><p>To remove blank rows, highlight the data in your table. Then, Remove Duplicates and leave all boxes checked. Excel will remove all of the blank rows - except the first one! Since the first blank row technically isn't a duplicate, Excel leaves it untouched. You'll just need to manually delete that row.</p><p>Bear in mind that this will remove all duplicate rows, not just duplicate blank rows.</p><h2>Recap and Keep Learning</h2><p>Removing duplicates from an Excel spreadsheet is an essential data cleanup skill. When you're working with a set of data, you hardly have time to manually find and remove duplicate records. That's why Excel's built-in "Remove Duplicates" function is worth learning.</p><p>Here are two other spreadsheet tutorials to continue mastering Excel data cleanup:</p><ul>
<li>Microsoft's official documentation on <a href="https://support.office.com/en-us/article/Filter-for-unique-values-or-remove-duplicate-values-ccf664b0-81d6-449b-bbe1-8daaec1e83c2" rel="external" target="_blank">Remove Duplicates</a> is a helpful, second source for learning the feature.</li>
<li>Bob Flisser's tutorial <a href="https://business.tutsplus.com/tutorials/how-to-use-excel-12-techniques-for-power-users--cms-26304" rel="external" target="_blank">12 Techniques for Power Users</a> contains a brief summary of the Remove Duplicates function, and other key techniques for data management.</li>
</ul><p>How are you using Remove Duplicates to manage your Excel spreadsheets? If you run into any problems, make sure and leave a comment for help.</p>2016-11-29T12:55:28.000Z2016-11-29T12:55:28.000ZAndrew Childresstag:computers.tutsplus.com,2005:PostPresenter/cms-27514How to Use the Excel VLOOKUP Function - With Useful Examples <h2>What is a VLOOKUP in Excel?<br>
</h2><p>A <strong>VLOOKUP</strong>, short for "vertical lookup" is a formula in Microsoft Excel to match data from two lists. Instead of jumping between spreadsheets and typing out your matching data, you can write a VLOOKUP formula to automate the process.</p><figure class="post_image"><img alt="Combining two lists is a a great situation to use VLOOKUP" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/27514/image/two-lists.jpg"><figcaption>Combing two lists is a perfect situation to use a VLOOKUP. </figcaption></figure><p>On the left (in the image above), we have employee shift information. We want to add the employee's job title to the shift data. With a separate list of employees and their job titles, we can write a VLOOKUP formula to pull in the title from a lookup list.</p><p>A successful VLOOKUP needs three things:</p><ul>
<li>A <strong>primary key </strong>in each list that you can use to match your data up. The two lists need to share at least one piece of data in common (in the Excel VLOOKUP example above, this is the employee ID)</li>
<li>A <strong>Lookup List, </strong>which contains your "database", or basically the information (the list of employee job titles)</li>
<li>Your <strong>data</strong>, which you want to pull a match into (the shift data)</li>
</ul><h3>Quick Example of an Excel VLOOKUP Formula in Action</h3><p>VLOOKUP is a Microsoft Excel formula that's essential for working with multiple sets of data. In this tutorial, I'll teach you how to master and use it.</p><figure class="post_image"><img alt="Example VLOOKUP formula used to look up employee data" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/27514/image/vlookup-illustrated.jpg"><figcaption>Example VLOOKUP formula used to look up employee data.</figcaption></figure><p>Using the example above, I've now written a VLOOKUP formula that looks up the employee's ID and inserts the job title into the shift data. Because both sheets have an employee ID, Excel can lookup the matching job title. The best part of VLOOKUP is that I can now drag the same formula down and it will look up each unique job title.</p><h3>Free Excel Spreadsheet Download </h3><p><em>Before we move on, make sure to </em><strong><a href="https://cms-assets.tutsplus.com/uploads/users/151/posts/27514/attachment/SourceFiles-VLOOKUP%20Tutorial.zip" target="_self"><em>Download the free Attachment</em></a></strong><em> to follow along. It's an example spreadsheet workbook that I created, which we'll use to walk through this tutorial.</em></p><h2>Watch and Learn: VLOOKUP</h2><p>For the fastest way to learn the basics of the VLOOKUP formula, check out the screencast below. The video walks through several examples of the VLOOKUP formula, using the example workbook.</p><figure><div class="wistia_responsive_padding"><div class="wistia_responsive_wrapper"><div class="wistia_embed wistia_async_awy0aprg7s videoFoam=true"> </div></div></div></figure><p>Keep reading to walk through the written instructions, and learn some additional techniques that aren't covered in the screencast.</p><h2>How to Use VLOOKUP in Excel: Walk Through </h2><p><em>Use the tabs "Ingredient Orders" and "Supplier List" for this Excel VLOOKUP example.</em><br></p><p>Let's say that we manage a restaurant and are placing our weekly orders to suppliers. The chefs have given us a list of ingredients to order, and we need to insert information about the supplier.</p><p>There are three pieces we need to add for each order:</p><ul>
<li>The Supplier Name</li>
<li>The Supplier Phone Number</li>
<li>The Supplier Delivery Day</li>
</ul><p>In this workbook, there are two tabs:</p><ul>
<li>
<strong>Ingredient Orders - </strong>contains the list of ingredient requests from our chefs.</li>
<li>
<strong>Supplier List - </strong>contains information about the suppliers, such as the supplier name and phone number.<br>
</li>
</ul><figure class="post_image"><img alt="Excel VLOOKUP list ingredient order and supplier list example" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/27514/image/example-3.jpg"><figcaption>At the top, we have our order data, which is on the "Ingredient Orders" tab. Below is the "Supplier List", which will act as our lookup list.</figcaption></figure><p>The common field between the two tables is the <strong>Ingredient </strong>tab. Let's use it to lookup each of the three fields and add it to the order list.</p><h3>Step 1: Start Our Excel VLOOKUP Formula</h3><p>On the <strong>Ingredient Orders </strong>tab, let's click in the first blank Supplier cell, <strong>F5</strong>, and press the equals sign to start the VLOOKUP formula. Then, type " <strong>VLOOKUP( </strong><strong>" </strong>to start the formula.</p><p> <code class="inline">=VLOOKUP(</code></p><p>Remember that our primary key—the piece of data that appears in both lists—is the <strong>ingredient, </strong>so we'll use it for the lookup. Either click on cell <strong>B5, </strong>or type it into the formula. Next, add a comma after "B5" so that we can enter the next part of the formula. </p>
<p><code class="inline">=VLOOKUP(B5,</code><br></p><p>Now, we need to give the formula our lookup list. With the formula still open, click on the <strong>Supplier List </strong>tab. Now, click on cell A3, and click and drag to highlight and select <strong>A3</strong> to <strong>G13,</strong> the whole lookup table. Make sure and press <strong></strong><strong></strong>the <strong>F4 key on your keyboard </strong>to make the formula an absolute reference (more on this later). Finally, enter another comma.</p><figure class="post_image"><img alt="Select the Excel lookup list" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/27514/image/corrected-list-link.jpg"><figcaption>Point Excel to the lookup list.</figcaption></figure><p>After you enter the comma for the lookup cell, switch tabs and point Excel to the lookup list. Click and drag between cells A3 and G3 to select the data to lookup from. Make sure and press <strong>F4 </strong>on your keyboard during this step to create an absolute reference, which will lock in the cells to use for the lookup.<br></p><p><code class="inline">=VLOOKUP(B5,'Supplier List'!$A$3:$G$13,</code><br></p><p>Next up, we'll need to tell Excel which column to pull from. Remember that our first item to insert is the <strong>Supplier </strong>name, which is in the second column of the lookup list. Add the number <strong>2 </strong>to the formula to pull from the second column of the lookup, and another comma.</p><p><code class="inline">=VLOOKUP(B5,'Supplier List'!$A$3:$G$13,2,</code></p><p>Finally, we'll add FALSE for an exact lookup, and then close the parentheses:</p><p><code class="inline">=VLOOKUP(B5,'Supplier List'!$A$3:$G$13,2,FALSE)</code></p><figure class="post_image"><img alt="Pulldown formula is working perfectly in Excel" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/27514/image/pulldown-formula.jpg"><figcaption>Our formula is working up perfectly, pulling in the supplier for the Potatoes. </figcaption></figure><p>The good news is that we don't need to rewrite this same formula over and over—just double-click in the lower right corner of cell <strong>F5 </strong>(<em>you can see the small green box on the corner of the cell</em>) to extend the formula down (<em>as shown above</em>).</p>
<p>The formula is working perfectly! Okay, now let's move on to pulling in the data for the other two fields: the supplier phone number and delivery day.</p><h3>Step 2: Pull More Data With Our VLOOKUP</h3><p>Because we used an absolute reference, we can basically reuse the same formula we wrote with a minor tweak. Let's add in the Supplier Phone Number next.</p><p>We'll start off by copying and pasting cell <strong>F5 </strong>(our Supplier cell) to cell <strong>G5. </strong>I typically just use the keyboard shortcuts <strong>Ctrl + C </strong>and <strong>Ctrl + V </strong>to copy and paste the entire cell.<strong> </strong>At first, this won't be working, and you'll see an <strong>N/A </strong>in the cell. </p><figure class="post_image"><img alt="Copying and pasting formula in Excel Lookup Moved" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/27514/image/lookup-again.jpg"><figcaption> Copying and pasting lookup with adjustments in Excel.</figcaption></figure><p>It's much easier to copy and paste our formula into another cell, but it requires some tweaking. At first, Excel will be looking to cell C5 for the lookup, but we need to adjust it to "B5" in the formula bar. Once we do that, the lookup will be working - almost.<br></p><p>We'll need to go up to the formula bar, and change the first part of the formula from <strong>C5 back </strong>to <strong>B5. </strong>When we moved the <strong></strong>formula over by one column, Excel updated other parts of the formula. We were getting an "N/A" because Excel was attempting to match the quantity (Column C) to our lookup list, but our lookup list doesn't include the quantity.</p><p>So far, our formula so should be:</p><p><code class="inline">=VLOOKUP(B5,'Supplier List'!$A$3:$G$13,2,FALSE)</code><br></p><p>However, notice in the screenshot above that we're pulling the wrong bit of data into the "Phone" column. We're still pulling the second column in the lookup list with the "<strong>2" </strong>in the formula. We need to change this to the column number with our supplier's contact phone.</p><h3>Step 3: Fix VLOOKUP N/A Error Issue</h3><p>Let's go back and check out the lookup list. You'll notice that the phone number is in the 7th column in the lookup list. Let's go back to our Excel formula and update the lookup to pull from the 7th column.</p><figure class="post_image"><img alt="Lookup List Column numbers need to be updated" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/27514/image/column-numbering.jpg"><figcaption>We need to update the "2" in our Excel formula to a "7" to pull from the 7th column in our lookup list.</figcaption></figure><p>All that we need to do is update the column section of our lookup list from a "2" to a "7" and it now works great!</p><figure class="post_image"><img alt="Our Excel vlookup formula is Updated for Supplier Phone" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/27514/image/updated-lookup-to-7-column.jpg"><figcaption>Notice that the supplier phone is working great now that we've updated our formula to use the 7th column from the lookup list. Now, just drag the formula down to update it for all cells.</figcaption></figure><p>Our final formula for the supplier phone number lookup:</p><p><code class="inline">=VLOOKUP(B5,'Supplier List'!$A$3:$G$13,7,FALSE)</code><br></p><h3>Step 4: Add Another Column to Finish Our VLOOKUP Formula</h3><p>Finally, let's work in the Supplier Delivery Day column. Copy and paste cell <strong>G5 </strong>to cell <strong>H5. </strong>Again, we'll need to fix our formula by changing <strong>C5</strong> back to <strong>B5 </strong>to use the ingredient as the primary key. Then, just update the "7" to a "5" to use the 5th column from our lookup table.</p><figure class="post_image"><img alt="Final supplier delivery day lookup formula applied" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/27514/image/supplier-delivery-day.jpg"><figcaption>Final supplier delivery day lookup formula applied.</figcaption></figure><p>Our final formula for the supplier delivery day:</p><p><code class="inline">=VLOOKUP(B5,'Supplier List'!$A$3:$G$13,5,FALSE)</code><br></p><p>That's it! We wrote basically one formula and tweaked it to pull in every bit of data we need to place our next order.</p><h2>Troubleshooting VLOOKUP</h2><p>So, you've written the VLOOKUP formula following the instructions step-by-step, but it's still not working. Excel takes things pretty literally, so we need to be careful with the data and the VLOOKUP formula. Let's take a look at several ideas for correcting a VLOOKUP formula that's just not working.</p><h3>1. Multiple Matches</h3><p>One of the most common issues with VLOOKUP is when there are multiple matches in your lookup list. When you're using a VLOOKUP, it will match to the first item that's in the list.</p><figure class="post_image"><img alt="Troubleshooting Example of Excel VLOOKUP not working " data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/27514/image/carrie-richard-1.jpg"><figcaption>Our VLOOKUP says that Carrie Richard is our Director of Marketing, but we know she was recently promoted to the company president. Why isn't our lookup working?</figcaption></figure><figure class="post_image"><img alt="Lookup problem with two listings conflicting in our VLOOKUP" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/27514/image/lookup-fixed.jpg"><figcaption>Whoops, it looks like there were two listings for Carrie Richard in the lookup list - one for Director of Marketing, and one for President. Once we delete the "Director of Marketing" line from the lookup list, our data is correct.</figcaption></figure><p>The point of VLOOKUP is to look up a matching item against a list. The lookup list shouldn't contain duplicates for the <strong></strong>primary key, the item that you're using to match. Otherwise, Excel gets confused and only shows you the first match.</p><h3>2. Leading or Trailing Spaces</h3><p>Another common issue is that our data might not actually match in Excel's eyes. Data with a space before it is said to have a "leading" space, while data with a space after it has a "trailing space."</p><figure class="post_image"><img alt="Trailing Space VLOOKUP Example Problem" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/27514/image/trailing-space-1.jpg"><figcaption>This VLOOKUP is perfectly written, but it isn't working. You can clearly see the name is in the lookup list, but Excel isn't return a match. Read on to find out why.</figcaption></figure><p>If your matching piece of data has a space before or after it, Excel sees these two pieces of data as totally different, and won't return a match. Excel views "_Andrew", "Andrew_" and "Andrew" as three unique pieces of data that won't be matched in VLOOKUP.</p><figure class="post_image"><img alt="Resolving Excel VLOOKUP Trailing Space Issue " data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/27514/image/trailing-space-2.jpg"><figcaption>Turns out there is a "trailing space", or a single space after the name in the cell. It's impossible to see, but it can break a VLOOKUP because Excel treats "Alyssa Reddall" and "Alyssa Reddall(space)" differently. Once you delete the space, the VLOOKUP will work normally.</figcaption></figure><p>If your data has leading or trailing spaces, use the <strong><a href="https://exceljet.net/formula/remove-leading-and-trailing-spaces-from-text" target="_self">TRIM </a></strong><a href="https://exceljet.net/formula/remove-leading-and-trailing-spaces-from-text" target="_self">function</a> in Excel to clean it up. VLOOKUP should work great after using it to remove leading and trailing spaces.</p><h3>3. Lock in Cell References</h3><p>As you might know, you can drag a cell down by the handle to paste the formula into other cells. However, this sometimes breaks our VLOOKUP because the cell references change. </p><figure class="post_image"><img alt="Bad reference VLOOKUP problem " data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/27514/image/vlookup-problem.jpg"><figcaption>In this screenshot, I dragged the VLOOKUP down the list to apply it to other cells. However, notice that the lookup reference changed from A2 to B15, to A16 to B29, hence why the formula isn't working. My lookup list is actually in A2 to B15, so the lookup is now broken.</figcaption></figure><p>As you pull the formulas down, it pulls the reference for the lookup table out of alignment. Suddenly, the lookup table is missing rows from the lookup table and our VLOOKUP isn't working.<br></p><p>The fix is to make the formula an <strong>absolute reference, </strong>so that when you drag the formula down, the list it's pointing to doesn't change. Click in the cell where you've written your VLOOKUP, and then click somewhere in the lookup list reference. Then, press <strong>F4 . </strong>You'll notice that the formula changes to include dollar signs.</p><figure class="post_image"><img alt="Updated Excel VLOOKUP Formula with Absolute Reference" data-src="https://cms-assets.tutsplus.com/uploads/users/151/posts/27514/image/updated%20with%20absolutt.jpg"><figcaption>Notice that the formula (shown at the top of this screenshot in the formula bar) now includes dollar signs in the lookup list reference, which shows that it uses an <strong>absolute reference. </strong>Now, when I drag the formula down, it locks the formula for the lookup list and works perfectly.</figcaption></figure><h2>Recap and Keep Learning</h2><p>VLOOKUP is one of those essential formulas for being a productive Excel users. There's simply not enough time to manually look up data and re-type it over and over again, so formulas like VLOOKUP are important to learn.</p><ul>
<li>If you want to learn an assortment of more advanced Excel skills, check out Bob Flisser's <a href="https://business.tutsplus.com/tutorials/how-to-use-excel-12-techniques-for-power-users--cms-26304" rel="external" target="_blank">12 Techniques for Power Users</a> tutorial. </li>
<li>Additionally, Bob's course <a href="https://business.tutsplus.com/courses/introduction-to-spreadsheets" rel="external" target="_blank">Introduction to Spreadsheets</a> is the perfect guide to getting started in Excel</li>
<li>Sometimes, it helps to learn similar material from another resource. The Microsoft Office <a href="https://support.office.com/en-us/article/VLOOKUP-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1" rel="external" target="_blank">official documentation</a> on the VLOOKUP formula is another great resource for mastering VLOOKUP.</li>
</ul><p>If you're having any problems with using the VLOOKUP formula, feel free to leave a comment below for troubleshooting.</p>2016-11-15T12:55:19.000Z2016-11-15T12:55:19.000ZAndrew Childresstag:computers.tutsplus.com,2005:PostPresenter/cms-26630How to Calculate Percentages in Excel With Formulas<p>If you need to work with percentages, you’ll be happy to know that Excel has tools to make your life easier. </p><p>You can use Excel to calculate percentage increases or decreases to track your business results each month. Whether it’s rising costs or percentage sales changes from month to month, you want to keep on top of your key business figures. Excel can help you do that.</p><p>You’ll also learn how to work with advanced percentage calculations using the scenario of calculating grade point averages, as well as discover how to figure out percentile rankings, which are both relatable examples that you can apply to a variety of use cases.</p><p>In this tutorial, learn how to calculate percentages in Excel with step-by-step workflows. Let’s look at some Excel percentage formulas, functions, and tips using a sheet of business expenses and a sheet of school grades.</p><p>You’ll walk away with the techniques needed to work proficiently with percentages in Excel. </p><h2>Screencast</h2><p>Watch the complete tutorial screencast, or work through the step-by-step written version below. First, download the source files for free: <a href="https://cms-assets.tutsplus.com/uploads/users/23/posts/26630/attachment/excel-percentages-source-files.zip" target="_self">Excel percentages worksheets</a>. We'll use them to work through the tutorial exercises.<br></p><figure data-video-embed="true" data-original-url="https://www.youtube.com/watch?v=Mre_K5w10SA&feature=youtu.be" class="embedded-video">
<iframe data-src="//www.youtube.com/embed/Mre_K5w10SA?rel=0" frameborder="0" webkitallowfullscreen="webkitallowfullscreen" mozallowfullscreen="mozallowfullscreen" allowfullscreen="allowfullscreen"></iframe>
</figure><h2>
<span class="sectionnum">1.</span> Input
Initial Data in Excel</h2><p>Input the data as follows (or start with the download file
<strong>"percentages.xlsx"</strong> contained in the tutorial source files). This worksheet is for Expenses. Later in this tutorial, we’ll
use the Grades worksheet.</p><figure class="post_image"><img alt="Excel percentages starting screen" data-src="https://cms-assets.tutsplus.com/uploads/users/60/posts/26630/image/percentages-expense-sheet.jpg" title="Image: https://cms-assets.tutsplus.com/uploads/users/60/posts/26630/image/percentages-expense-sheet.jpg"><figcaption>Excel percentages worksheet data</figcaption></figure><h2>
<span class="sectionnum">2.</span> Calculate a Percentage Increase</h2><p>Let’s say you anticipate that next year’s costs will be 8%
higher, so you want to see what they are.</p><p>Before writing any formulas, it’s helpful to know that Excel
is flexible enough to calculate the same way whether you type percentages with
a percent sign (like 20%) or as a decimal (like 0.2 or just .2). To Excel, the
percent symbol is just formatting.</p><p>We want to show the total estimated amount, not just the increase.</p><h3><b>Step 1</b></h3><p>In <b>A18</b>, type the
header <b>With 8% increase</b>. Since we
have a number mixed with text, Excel will treat the entire cell as text.</p><h3><b>Step 2</b></h3><p>Press <b>Tab</b>, then
in <b>B18</b>,<b> </b>enter this Excel percentage formula: <code class="inline">=B17 * 1.08</code></p><p>Alternatively, you can enter the formula this way: <code class="inline">=B17 * 108%</code></p><p>The amount is 71,675, as shown below:</p><figure class="post_image"><img alt="Finding 8 increase" data-src="https://cms-assets.tutsplus.com/uploads/users/60/posts/26630/image/percentages-8-percent-higher.jpg" title="Image: https://cms-assets.tutsplus.com/uploads/users/60/posts/26630/image/percentages-8-percent-higher.jpg"><figcaption>Calculating a percentage increase in Excel</figcaption></figure><h2>
<span class="sectionnum">3.</span> Calculate a Percentage Decrease<br>
</h2><p>Maybe you think your expenses will decrease by 8 percent
instead. To see those numbers, the formula is similar. Start by showing the
total, lower amount, not just the decrease.</p><h3><b>Step 1</b></h3><p>In <b>A19</b>, type the
header <b>With 8% decrease</b>.</p><h3><b>Step 2</b></h3><p>Press <b>Tab</b>, then
in <b>B19</b>,<b> </b>enter this percentage formula in Excel: <code class="inline">=B17 * .92</code></p><p>Alternatively, you can enter the formula this way: <code class="inline">=B17 * 92%</code></p><p>The amount is 61,057.</p><figure class="post_image"><img alt="Finding 8 decrease" data-src="https://cms-assets.tutsplus.com/uploads/users/60/posts/26630/image/percentages-8-percent-lower.jpg" title="Image: https://cms-assets.tutsplus.com/uploads/users/60/posts/26630/image/percentages-8-percent-lower.jpg"><figcaption>Calculating a percentage decrease in Excel</figcaption></figure><h3><b>Step 3</b></h3><p>If you want a little more flexibility in changing the
percentage by which you think the costs will rise or drop, you can enter the
formulas this way, instead:</p><p>For the 8% increase, enter this formula in <b>B18</b>: <code class="inline">=B17 + B17 * 0.08</code></p><h3><b>Step 4</b></h3><p>For the 8% decrease, enter this Excel percentage formula in <b>B19</b>: <code class="inline">=B17 – B17 * 0.08</code></p><p>With these formulas, you can simply change the .08 to
another number to get a new result from a different percentage.</p><h2>
<span class="sectionnum">4.</span> Calculate a Percentage Amount</h2><p>Now to work through an Excel formula for a percentage amount. What if you want to see the 8% <i>amount itself</i>, not the new total? To do that, multiply the total
amount in <strong>B17</strong> by 8 percent.</p><h3><b>Step 1</b></h3><p>In <strong>A20</strong>, enter the header <b>8% of total</b>.</p><h3><b>Step 2</b></h3><p>Press <b>Tab</b>, then
in <strong>B20 </strong>enter the formula: <code class="inline">=B17 * 0.08</code></p><p>Alternatively, you can enter the formula this way: <code class="inline">=B17 * 8%</code></p><p>The amount is 5,309.</p><figure class="post_image"><img alt="Finding 8 of total" data-src="https://cms-assets.tutsplus.com/uploads/users/60/posts/26630/image/percentages-percent-of-total.jpg"><figcaption>Calculate a percentage total in Excel</figcaption></figure><h2>
<span class="sectionnum">5.</span> Make Adjustments Without Rewriting Formulas<br>
</h2><p>If you want to change the percentage without having to
rewrite the formulas, put the percentage in its own cell. We’ll start by
entering row titles.</p><h3><b>Step 1</b></h3><p>In <strong>A22</strong>, type <b>Adjustment</b>.
Press <b>Enter</b>.</p><h3><b>Step 2</b></h3><p>In <strong>A23</strong>, type <b>Higher
total</b>. Press <b>Enter</b>.</p><h3><b>Step 3</b></h3><p>In <strong>A24</strong>, type <b>Lower
total</b>. Press <b>Enter</b>.</p><p>The worksheet should now look like this:</p><figure class="post_image"><img alt="Sheet with additional row titles" data-src="https://cms-assets.tutsplus.com/uploads/users/60/posts/26630/image/percentages-adjustment-titles.jpg" title="Image: https://cms-assets.tutsplus.com/uploads/users/60/posts/26630/image/percentages-adjustment-titles.jpg"><figcaption>Excel sheet with additional row titles</figcaption></figure><p>Now enter the percentage and the formulas to the right of
the titles. </p><h3><b>Step 4</b></h3><p>In <b>B22</b>, type <b>8%</b>. Press <b>Enter</b>.</p><h3><b>Step 5</b></h3><p>In <b>B23</b>, enter
this formula to give you the total plus another 8%: <code class="inline">=B17 + B17 * B22</code></p><h3><b>Step 6</b></h3><p>In <b>B24</b>, enter this
formula to give you the total less 8%: <code class="inline">=B17 * (100% - B22)</code><br></p><p>When you type the 8% in <strong>B22</strong>, Excel automatically formats the
cell as a percentage. If you type .08 or 0.08, Excel will leave it like that.
You can always format it as a percent later on by clicking the <strong>Percent Style</strong>
button on the Ribbon:</p><figure class="post_image"><img alt="Percent Style button and its effect" data-src="https://cms-assets.tutsplus.com/uploads/users/60/posts/26630/image/percentages-cell-format.jpg" title="Image: https://cms-assets.tutsplus.com/uploads/users/60/posts/26630/image/percentages-cell-format.jpg"><figcaption>Excel Percent Style button and its effect</figcaption></figure><p><em>Tip: you can also format the numbers as Percent Style using
a keyboard shortcut: press </em><em><b>Control</b>-</em><em><b>Shift</b>-</em><b><em>%</em></b><em> in Windows or </em><strong><em>Command-Shift-</em></strong><strong><em>%</em></strong><em> on the Mac. </em></p><h2>
<span class="sectionnum">6.</span> Calculate a Percentage Change</h2><p>You might also want to calculate the percentage change from
one month to the next month. That would give you a picture of whether costs
were heading up or heading down. So let’s do that down column C. </p><p>The general rule to calculate a percentage change is:</p><p><b>=(new value - old
value) / new value</b></p><p>Since January is the first month, it doesn’t have a
percentage change. The first change will be from January to February, and we’ll
put this next to February’s number. </p><h3><b>Step 1</b></h3><p>To calculate the first percentage change, enter this percent change formula in <b>C5</b>: <code class="inline">=(B5-B4)/B5</code></p><h3><b>Step 2</b></h3><p>Excel displays this as a decimal, so click the <strong>Percent Style</strong>
button on the Ribbon (or use the above mentioned shortcuts) to format it as a
percent.</p><figure class="post_image"><img alt="Percent Style button for percent changes" data-src="https://cms-assets.tutsplus.com/uploads/users/60/posts/26630/image/percentages-first-change.jpg" title="Image: https://cms-assets.tutsplus.com/uploads/users/60/posts/26630/image/percentages-first-change.jpg"><figcaption>Excel Percent Style button for percent changes</figcaption></figure><p>Now that we know what the percent change is from January to
February, we can AutoFill the formula down column C to show the remaining percent
changes for the year.<br></p><h3><b>Step 3</b></h3><p>Roll the mouse pointer over the dot in the lower-right
corner of the cell that shows -7%.</p><figure class="post_image"><img alt="AutoFill dot" data-src="https://cms-assets.tutsplus.com/uploads/users/60/posts/26630/image/percentages-autofill-dot.jpg" title="Image: https://cms-assets.tutsplus.com/uploads/users/60/posts/26630/image/percentages-autofill-dot.jpg"><figcaption>Excel AutoFill dot</figcaption></figure><h3>
<b>Step 4</b><br>
</h3><p>When the mouse pointer becomes a crosshair, <strong>Double-click</strong>.</p><p>If you’re unfamiliar with the AutoFill feature, see technique 3 in my Excel power techniques article:</p><ul class="roundup-block__contents posts--half-width roundup-block--list"><li class="roundup-block__content"><a class="roundup-block__content-link" href="https://business.tutsplus.com/tutorials/how-to-use-excel-12-techniques-for-power-users--cms-26304"><img class="roundup-block__preview-image" data-src="https://cms-assets.tutsplus.com/uploads/users/23/posts/26304/preview_image/how-to-use-excel-power-techniques.png"><div class="roundup-block__primary-category topic-business">Microsoft Excel</div>
<div class="roundup-block__content-title">How to Use Excel: 12 Techniques for Power Users</div>
<div class="roundup-block__author">Bob Flisser</div></a></li></ul><h3><b>Step 5</b></h3><p>Click cell <b>B3</b>
(the “Amount” header).</p><h3><b>Step 6</b></h3><p>Put the <strong>mouse pointer</strong> on its <strong>AutoFill dot</strong> and drag one cell to
the right, into <b>C3</b>. That duplicates the
header, including the formatting.</p><h3><b>Step 7</b></h3><p>In <b>C3</b>, type <b>% Change</b>, replacing the text that’s already
there.</p><figure class="post_image"><img alt="All percent changes calculated" data-src="https://cms-assets.tutsplus.com/uploads/users/60/posts/26630/image/percentage-all-changes.jpg" title="Image: https://cms-assets.tutsplus.com/uploads/users/60/posts/26630/image/percentage-all-changes.jpg"><figcaption>All percent changes calculated</figcaption></figure><h2>
<span class="sectionnum">7.</span> Calculate a Percentage of Total<br>
</h2><p>The final technique on this sheet is to find the percent of
total for each month. For a percent of total calculation, think of a pie chart
where each month is a slice, and all the slices add up to 100%.</p><p>Whether with Excel or with pencil and paper, the way to
calculate a percentage of total is with a simple division:</p><p><b>Component
number/total</b></p><p>… and format it as a percentage.</p><p>In this example, we divide each month by the total at the
bottom of column B.</p><h3><b>Step 1</b></h3><p>Click on <b>C3</b> and
<strong>AutoFill</strong> one cell across to <b>D3</b>.</p><h3><b>Step 2</b></h3><p>Change <b>D3</b> to <b>% of Total</b>.</p><h3><b>Step 3</b></h3><p>In <b>D4</b>, type this
formula, but <i>don’t press Enter, yet</i>: <code class="inline">=B4/B17</code></p><h3><b>Step 4</b></h3><p>Before entering the formula, we want to be sure of preventing
AutoFill errors. Since we’re going to AutoFill down the column, the denominator,
which is now B17, shouldn’t change. If it does, the numbers for February
through December will be wrong.</p><p>Make sure the text cursor is still in the formula, on the “B17”
denominator.</p><h3><b>Step 5</b></h3><p>Press the <b>F4</b> key
(on the Mac, press <b>Fn</b>-<b>F4</b>).</p><p>That inserts dollar signs before the column and the row, turning
the denominator into <strong>$B$17</strong>. The <strong>$B</strong> means that column B won’t increase to column
C, etc. and the <strong>$17</strong> means the row won’t increase to <strong>$18</strong>, etc.</p><h3><b>Step 6</b></h3><p>Make sure the percentage formula in excel is now: <b><code class="inline">=B4/$B$17</code></b>.</p><figure class="post_image"><img alt="Percent of total formula" data-src="https://cms-assets.tutsplus.com/uploads/users/60/posts/26630/image/percentages-absolute-refs.jpg" title="Image: https://cms-assets.tutsplus.com/uploads/users/60/posts/26630/image/percentages-absolute-refs.jpg"><figcaption>Percent of total formula</figcaption></figure><h3>
<b>Step 7</b><br>
</h3><p>Now we can enter and AutoFill.</p><p>Press <b>Control-</b><b>Enter</b> (on the Mac, press<b> Command-</b>↩)
to enter the formula without moving the cursor down to the next row.</p><h3><b>Step 8</b></h3><p>Click the <b>Percent
Style</b> button on the ribbon or use the <strong>Control-</strong><strong>Shift-</strong><strong>%</strong> (<strong>Command-Shift-%</strong>) shortcut.</p><h3><b>Step 9</b></h3><p>Roll the mouse pointer over the <strong>AutoFill dot</strong> in the
lower-right corner of <b>D4</b>.</p><h3><b>Step 10</b></h3><p>When the cursor becomes a crosshair, <strong>double-click</strong> to
AutoFill the formula down to the bottom.</p><p>Each month will now show how much of a percentage of the
grand total it is.</p><figure class="post_image"><img alt="Percent of totals filled in" data-src="https://cms-assets.tutsplus.com/uploads/users/60/posts/26630/image/percentage-completed-exercise.jpg" title="Image: https://cms-assets.tutsplus.com/uploads/users/60/posts/26630/image/percentage-completed-exercise.jpg"><figcaption>Percent of totals filled in</figcaption></figure><h2>
<span class="sectionnum">8.</span> Percentage Ranking</h2><p>Ranking numbers by percent is a statistical technique.
You’re probably familiar with it from school: you and your classmates have
grade point averages, so each of you is ranked in a percentile. The higher your
grades, the higher your percentile. </p><p>The list of numbers (grades, in this
example) are in a range of cells that Excel calls an array. There’s nothing
special about an array and you don’t have to define it. That’s just what Excel
calls the range of cells you plug into a formula.</p><p>Excel has two functions for percentage ranking. One function
includes the beginning and ending numbers of the array and the other function
doesn’t.</p><p>Look at the second tab in the worksheet: <strong>Grades</strong>.</p><figure class="post_image"><img alt="Percent ranking - second worksheet" data-src="https://cms-assets.tutsplus.com/uploads/users/60/posts/26630/image/percentages-grades-start.jpg" title="Image: https://cms-assets.tutsplus.com/uploads/users/60/posts/26630/image/percentages-grades-start.jpg"><figcaption>Excel Percent ranking - Grades worksheet</figcaption></figure><p>This shows a list of 35 grade point averages, sorted in
ascending order. The first thing we want to do is find the percentile rank for
each one. To do this, we’ll use the =PERCENTRANK.INC function. The “INC” part
of that function means “inclusive," as it will include the first and last grades
in the list. If you want to exclude the first and last numbers in the array,
you would use the =PERCENTRANK.EXC function. </p><p>The function takes two mandatory arguments and the syntax
is: <b><code class="inline">=PERCENTRANK.INC(array,
entry)</code></b></p><ul>
<li>
<b>array</b> is the
range of cells that contains the list (in this example, it’s <strong>B3:B37</strong>)</li>
<li>
<b>entry</b> is any
number or cell in the list</li>
</ul><h3><b>Step 1</b></h3><p>Click <b>C3</b>, the
first one in the list.</p><h3><b>Step 2</b></h3><p>Start typing this formula, but <i>don’t press Enter, yet</i>: <code class="inline">=PERCENTRANK.INC(B3:B37</code></p><h3><b>Step 3</b></h3><p>This range needs to be an absolute reference so we can
AutoFill to the bottom.</p><p>Press <b>F4</b> (on the
Mac, press <b>Fn</b>-<b>F4</b>) to insert dollar signs.</p><p>The formula should now look like this: <code class="inline">=PERCENTRANK.INC($B$3:$B$37</code></p><h3><b>Step 4</b></h3><p>In each case down column C, we want to know the percent rank
of the entry in column <b>B</b>.</p><p>Click <b>B3</b>, then
close the parenthesis.</p><p>The formula is now
this:<b> </b><code class="inline">=PERCENTRANK.INC($B$3:$B$37,B3)</code></p><figure class="post_image"><img alt="Percentrank function" data-src="https://cms-assets.tutsplus.com/uploads/users/60/posts/26630/image/percentages-percentrank-formula.jpg" title="Image: https://cms-assets.tutsplus.com/uploads/users/60/posts/26630/image/percentages-percentrank-formula.jpg"><figcaption>Excel Percentrank function</figcaption></figure><p>Now we can fill in and format the numbers. </p><h3><b>Step 5</b></h3><p>If necessary, click back on <b>C3</b> to select it.</p><h3><b>Step 6</b></h3><p><strong>Double-click</strong> the <strong>AutoFill dot</strong> on <strong>C3</strong>. That automatically fills down the column.</p><h3><b>Step 7</b></h3><p>On the Ribbon, click the <strong>Percent Style</strong> button to format the
column as percentages. You should now see the finished result:</p><figure class="post_image"><img alt="Percentrank function result filled and formatted" data-src="https://cms-assets.tutsplus.com/uploads/users/60/posts/26630/image/percentages-percent-rank-filled.jpg" title="Image: https://cms-assets.tutsplus.com/uploads/users/60/posts/26630/image/percentages-percent-rank-filled.jpg"><figcaption>Excel Percentrank function result filled and formatted</figcaption></figure><p>So if you had very good grades and your GPA was 3.98, you
would say that you ranked in the 94<sup>th</sup> percentile. </p><h2>
<span class="sectionnum">9.</span> Finding the Percentile</h2><p>You can also find the percentile directly, using the PERCENTILE
functions. With these functions, you enter a percent rank, and it will return a
number from the array that corresponds to that rank. If the exact number you
want isn’t listed, Excel will interpolate the result and return the number that
“should” be there.</p><p>When would you use this? Let’s say you plan on applying for
a graduate program, and the program accepts only students who score in the 60th percentile or higher. So you want to know what GPA is exactly 60%. Looking at
this list, we can see 3.22 is at 59% and 3.25 is at 62%, so we can use the
=PERCENTILE.INC function to find the answer.</p><p>The syntax is: <b>=PERCENTILE.INC(array,
percent rank)</b></p><ul>
<li>
<b>array</b> is the
range of cells that contains the list (same as the previous example, <strong>B3:B37</strong>)</li>
<li>
<b>rank</b> is a
percentage (or a decimal between and including 0 and 1)</li>
</ul><p>Just like with the percent ranking functions, you could use =PERCENTILE.EXC
to exclude the first and last entries in the array, but that’s not what we want
in this example.</p><h3><b>Step 1</b></h3><p>Click in <b>B39</b>,
below the list.</p><h3><b>Step 2</b></h3><p>Enter this formula: <code class="inline">=PERCENTILE.INC(B3:B37,60%)</code></p><p>Since we aren’t going to AutoFill this formula, there is no
need to make the array an absolute reference.</p><h3><b>Step 3</b></h3><p>On the Ribbon, click the <b>Decrease Decimal</b> button once, to round the number to two decimal
places.</p><p>The result is that in this array, a 3.23 GPA is the 60th percentile. Now you know the grades you need for acceptance into the program.</p><figure class="post_image"><img alt="Percentile function result" data-src="https://cms-assets.tutsplus.com/uploads/users/60/posts/26630/image/percentages-percentile-calc.jpg" title="Image: https://cms-assets.tutsplus.com/uploads/users/60/posts/26630/image/percentages-percentile-calc.jpg"><figcaption>Percentile function result</figcaption></figure><h2>Conclusion</h2><p>Percentages aren’t complicated, and Excel calculates them
using the same rules of math as you would use with pencil and paper. Excel also
adheres to the standard order of operations when you have addition, subtraction, and multiplication in a formula:</p><ol>
<li>Parentheses</li>
<li>Exponents</li>
<li>Multiplication</li>
<li>Division</li>
<li>Addition</li>
<li>Subtraction</li>
</ol><p>I always remember this with the mnemonic <b>P</b>lease <b>E</b>xcuse <b>M</b>y <b>D</b>ear <b>A</b>unt <b>S</b>ally.</p>2016-06-30T12:15:00.398Z2016-06-30T12:15:00.398ZBob Flissertag:computers.tutsplus.com,2005:PostPresenter/cms-26446Excel What-If Analysis: How to Use the Scenario Manager<p>The Scenario Manager is a great, but often overlooked What-If Analysis feature of Excel that will let you swap multiple sets of data in a worksheet
and even compare them side-by-side. This technique can help you decide between
multiple courses of action or what the implications are among several
possibilities.</p><p>For example, let’s say we are concert promoters and want to
produce a show. We need to decide what venue to use because that will determine
costs, revenues, profit or loss, and what talent to contract for. </p><p>In this
exercise, we’ll use the Scenario Manager to compare four sets of numbers:
small, medium, large and very large locations and their associated costs and
revenues, assuming each show sells out.</p><h2>Screencast</h2><figure data-video-embed="true" data-original-url="https://www.youtube.com/watch?v=Cl3Xvv6pDnA&feature=youtu.be" class="embedded-video">
<iframe data-src="//www.youtube.com/embed/Cl3Xvv6pDnA?rel=0" frameborder="0" webkitallowfullscreen="webkitallowfullscreen" mozallowfullscreen="mozallowfullscreen" allowfullscreen="allowfullscreen"></iframe>
</figure><p>Watch the complete tutorial screencast above, or work through the step-by-step written version below, but first download the practice worksheet so you can work through the excercises.<br></p><h2>Download the Practice Worksheet</h2><p>You can re-create the sheet below or <a href="https://cms-assets.tutsplus.com/uploads/users/60/posts/26446/attachment/practice-files.zip" target="_self"><strong>download </strong></a><strong><a href="https://cms-assets.tutsplus.com/uploads/users/60/posts/26446/attachment/practice-files.zip" target="_self">practice-files.zip</a></strong>, which contains the <strong>scenarios.xlsx</strong>
worksheet below and a worksheet of the completed Excel Scenario Manager exercise.</p><figure class="post_image"><img alt="Excel Scenerio Manager Practice Worksheet" data-src="https://cms-assets.tutsplus.com/uploads/users/60/posts/26446/image/scenario-original.jpg"><figcaption>Excel What-If Analysis Scenerio Manager - Practice Worksheet</figcaption></figure><p>This sheet currently displays the smallest of the venues, which
has 300 seats. The numbers in orange boxes are calculated, so we won’t adjust
them in the scenarios. Here are the formulas the calculated numbers use:</p><ul>
<li>B13: <i>Total costs</i>
adds the costs from the cells above.</li>
<li>B19: <i>Ticket sales</i>
multiplies the number of seats x ticket price (B4*B17).</li>
<li>B20: <i>Merchandising</i>
(t-shirts, souvenirs) assumes patrons purchase an average of $5/seat (5*B4).</li>
<li>B21: <i>Food &
beverage</i> assumes patrons purchase an average of $15/seat (15*B4).</li>
<li>B22: <i>Total revenue</i>
adds the revenues from the cells above.</li>
<li>B24: <i>Profit or loss</i>
subtracts total cost from total revenue (B22-B13).</li>
</ul><p><b>Tip</b>: press <b>Ctrl</b> + <b>`</b> (accent mark) to display all the formulas on the sheet at once.
Press <b>Ctrl</b> + <b>`</b> again to return the sheet to normal. This shortcut is identical
in both Windows and Mac.<br></p><figure class="post_image"><img alt="Display formulas using Excel Keyboard Shortcut" data-src="https://cms-assets.tutsplus.com/uploads/users/60/posts/26446/image/all-formulas.jpg"><figcaption>Display formulas using Excel keyboard shortcut</figcaption></figure><h2>
<span class="sectionnum">1.</span> Make Your First Scenario</h2><h3>Step 1: Set up the First Scenario</h3><p>Now we'll dig into What-If Analysis in Excel. We'll open up the Scenario Manager and begin:</p><ol>
<li>First, select all the cells that will change. To do that, click B4,
hold the <b>Ctrl</b> key (<b>Command</b> key on the Mac) while dragging from B6 down to B12, then <b>Ctrl</b> + click (<b>Command</b> + click on the Mac) B17.<br>
</li>
<li>On the ribbon, select the <b>Data</b> tab > <b>What-If
Analysis</b> > <b>Scenario Manager</b>.</li>
</ol><figure class="post_image"><img alt="Excel What-If Analysis Scenario Manager" data-src="https://cms-assets.tutsplus.com/uploads/users/60/posts/26446/image/selected-cells.jpg"><figcaption>Excel What-If Analysis: Scenario Manager</figcaption></figure><p>This displays the Scenario Manager dialog box. Since we
haven’t created any scenarios yet, it says there are none defined.</p><figure class="post_image"><img alt="Excel Scenario Manager dialog box" data-src="https://cms-assets.tutsplus.com/uploads/users/60/posts/26446/image/scenario-box-empty.jpg"><figcaption>Scenario Manager dialog box</figcaption></figure><p>Each scenario will be a set of the cells you just selected,
containing unique values. The first set will be the current values.</p><h3>Step 2: Now Create the First Scenerio</h3><ol>
<li>In the dialog box, click <b>Add</b>.</li>
<li>Enter the name <b>Original
values</b>.</li>
<li>The changing cells are what you selected. If you selected
different cells by mistake, you can enter the correct ones here (<em>see image below</em>).</li>
<li>Enter a comment if you want. This is optional.</li>
<li>The checkboxes for <strong>Protection</strong> are only if you want to protect the
sheet from changes. We won’t do that in this exercise, so ignore these choices.</li>
</ol><figure class="post_image"><img alt="Excel Scenario Protection options" data-src="https://cms-assets.tutsplus.com/uploads/users/60/posts/26446/image/add-the-first.jpg"><figcaption>Scenario Protections options</figcaption></figure><p>Click <b>OK</b>. The Scenario Values dialog box shows you a list of all the
cells in the scenario and what their current values are. Note that you can’t
resize this box, so use its scrollbar to see all of them.</p><figure class="post_image"><img alt="Scenario Values dialog box " data-src="https://cms-assets.tutsplus.com/uploads/users/60/posts/26446/image/first-values.jpg"><figcaption>Scenario Values dialog box </figcaption></figure><p>For now, there’s nothing to change, but note the <strong>Add</strong> button.
A quick way of creating several scenarios one after another is to click this
<strong>Add</strong> button after entering values. That will immediately display the <strong>Add
Scenario</strong> screen.</p><p>For now, click <b>OK</b>.
That brings back the main Scenario Manager dialog, showing the first one
listed.</p><figure class="post_image"><img alt="Scenario Manager dialog" data-src="https://cms-assets.tutsplus.com/uploads/users/60/posts/26446/image/first-one-listed.jpg"><figcaption>Back to the main Scenario Manager dialog box</figcaption></figure><p>The Manager has buttons for adding a new scenario, deleting
one, editing one, merging in a scenario from another open workbook, and
creating a summary. The summary is the coolest part, and we’ll do that below.</p><h2>
<span class="sectionnum">2.</span> Create Additional Scenarios</h2><h3>Step 1: Add More Scenarios</h3><p>Click <strong>Add</strong>. This
is the same thing as clicking the <strong>Add</strong> button in the previous step.<br></p><p>Create 3 more scenarios using the data from the table below.
The general concept is that larger venues will have higher costs – not always
in proportion – along with the ability to charge higher ticket prices resulting
in greater revenues. For the sake of simplicity, assume that if a concert has
more than one act, they’re combined in the <strong>Artist</strong> category.</p><p>The fastest way of entering the numbers is not to use the
mouse. Just type a number, press the <b>Tab</b>
key, type another number, press the <b>Tab</b>
key, and so on.</p><table>
<tbody>
<tr>
<td>
<p><b>Description</b></p>
</td>
<td>
<p><b>Value</b></p>
</td>
</tr>
<tr>
<td>
<p><b>Scenario name</b></p>
</td>
<td>
<p>Medium venue</p>
</td>
</tr>
<tr>
<td>
<p><b>B4 (# of seats)</b></p>
</td>
<td>
<p>800</p>
</td>
</tr>
<tr>
<td>
<p><b>B6 (artist)</b></p>
</td>
<td>
<p>7500</p>
</td>
</tr>
<tr>
<td>
<p><b>B7 (venue rental)</b></p>
</td>
<td>
<p>1000</p>
</td>
</tr>
<tr>
<td>
<p><b>B8 (amplification)</b></p>
</td>
<td>
<p>600</p>
</td>
</tr>
<tr>
<td>
<p><b>B9 (lighting)</b></p>
</td>
<td>
<p>350</p>
</td>
</tr>
<tr>
<td>
<p><b>B10 (ticketing)</b></p>
</td>
<td>
<p>250</p>
</td>
</tr>
<tr>
<td>
<p><b>B11 (security)</b></p>
</td>
<td>
<p>300</p>
</td>
</tr>
<tr>
<td>
<p><b>B12 (insurance)</b></p>
</td>
<td>
<p>250</p>
</td>
</tr>
<tr>
<td>
<p><b>B17 (ticket price)</b></p>
</td>
<td>
<p>35</p>
</td>
</tr>
<tr>
<td>
<p><b> </b></p>
</td>
<td>
<p> </p>
</td>
</tr>
<tr>
<td>
<p><b>Scenario name</b></p>
</td>
<td>
<p>Large venue</p>
</td>
</tr>
<tr>
<td>
<p><b>B4 (# of seats)</b></p>
</td>
<td>
<p>1500</p>
</td>
</tr>
<tr>
<td>
<p><b>B6 (artist)</b></p>
</td>
<td>
<p>12000</p>
</td>
</tr>
<tr>
<td>
<p><b>B7 (venue rental)</b></p>
</td>
<td>
<p>3500</p>
</td>
</tr>
<tr>
<td>
<p><b>B8 (amplification)</b></p>
</td>
<td>
<p>1000</p>
</td>
</tr>
<tr>
<td>
<p><b>B9 (lighting)</b></p>
</td>
<td>
<p>700</p>
</td>
</tr>
<tr>
<td>
<p><b>B10 (ticketing)</b></p>
</td>
<td>
<p>350</p>
</td>
</tr>
<tr>
<td>
<p><b>B11 (security)</b></p>
</td>
<td>
<p>1000</p>
</td>
</tr>
<tr>
<td>
<p><b>B12 (insurance)</b></p>
</td>
<td>
<p>500</p>
</td>
</tr>
<tr>
<td>
<p><b>B17 (ticket price)</b></p>
</td>
<td>
<p>50</p>
</td>
</tr>
<tr>
<td>
<p><b> </b></p>
</td>
<td>
<p> </p>
</td>
</tr>
<tr>
<td>
<p><b>Scenario name</b></p>
</td>
<td>
<p>Very large venue</p>
</td>
</tr>
<tr>
<td>
<p><b>B4 (# of seats)</b></p>
</td>
<td>
<p>5000</p>
</td>
</tr>
<tr>
<td>
<p><b>B6 (artist)</b></p>
</td>
<td>
<p>25000</p>
</td>
</tr>
<tr>
<td>
<p><b>B7 (venue rental)</b></p>
</td>
<td>
<p>10000</p>
</td>
</tr>
<tr>
<td>
<p><b>B8 (amplification)</b></p>
</td>
<td>
<p>2500</p>
</td>
</tr>
<tr>
<td>
<p><b>B9 (lighting)</b></p>
</td>
<td>
<p>2000</p>
</td>
</tr>
<tr>
<td>
<p><b>B10 (ticketing)</b></p>
</td>
<td>
<p>500</p>
</td>
</tr>
<tr>
<td>
<p><b>B11 (security)</b></p>
</td>
<td>
<p>2500</p>
</td>
</tr>
<tr>
<td>
<p><b>B12 (insurance)</b></p>
</td>
<td>
<p>2500</p>
</td>
</tr>
<tr>
<td>
<p><b>B17 (ticket price)</b></p>
</td>
<td>
<p>50</p>
</td>
</tr>
</tbody>
</table><p>After entering the last scenario, click <b>OK</b> to return to the main Scenario Manager screen. It should look
like this:</p><figure class="post_image"><img alt="Scenario Manager screen" data-src="https://cms-assets.tutsplus.com/uploads/users/60/posts/26446/image/all-entered.jpg"><figcaption>Scenario Manager screen</figcaption></figure><h3>Step 2: Switch Between </h3><p>The sheet still shows the original values, so here’s the
first cool feature:<strong> Double-click</strong> one of the scenario names in the list. The
sheet updates with those values.</p><figure class="post_image"><img alt="Updated Values" data-src="https://cms-assets.tutsplus.com/uploads/users/60/posts/26446/image/switched.jpg"><figcaption>Updated values</figcaption></figure><h3>Step 3: View All the S<b>cenarios at Once</b>
</h3><ol>
<li>Click the <b>Summary</b>
button.</li>
<li>That confirms you want to create a summary, not a
PivotTable, so leave the default radio button selected.</li>
<li>It also confirms the main result cell is the <strong>Profit or
Loss</strong> in <strong>B24</strong>.</li>
</ol><figure class="post_image"><img alt="Profit or Loss Cell Result" data-src="https://cms-assets.tutsplus.com/uploads/users/60/posts/26446/image/create-summary.jpg"><figcaption>Profit or Loss cell Result</figcaption></figure><p> Click <b>OK</b>. That creates a new sheet in the workbook, called <strong>Scenario
Summary</strong>.</p><figure class="post_image"><img alt="Scenario Summary worksheet" data-src="https://cms-assets.tutsplus.com/uploads/users/60/posts/26446/image/summary-sheet.jpg"><figcaption>Scenario Summary worksheet</figcaption></figure><h3>Step 4: Engaging With the <strong>Scenario Summary</strong>
</h3><p>This shows the values that the sheet currently displays (you
could have changed these manually) as well as the sets of numbers from all four scenarios.</p><p>Notice the small <strong>plus</strong> and <strong>minus</strong> symbols in the margins.
These are part of Excel’s Group and Outline feature, which you can use
separately from Scenario Manager. The <strong>Outline</strong> button is also on the ribbon’s
<strong>Data</strong> tab, all the way on the end.</p><p>Click any of the <strong>minus</strong> signs to collapse the sheet so it
shows only summary data, or click the <strong>plus</strong> signs to expand and show detail.</p><figure class="post_image"><img alt="Outline feature" data-src="https://cms-assets.tutsplus.com/uploads/users/60/posts/26446/image/collapsed.jpg"><figcaption>Outline features</figcaption></figure><h3>Step 5: Two Things to Be Aware Of</h3><ol>
<li>None of the values are dynamic. If you change the
underlying data on the original sheet, the values on this sheet will <em>not</em> change. You will need to create a
new summary.</li>
<li>Down <strong>column C</strong>, you see Excel lists the cell references,
not their labels (<strong>Artist, Venue rental</strong>, etc.). If you want to see the labels,
stretch out <strong>column C</strong> and type them manually.</li>
</ol><figure class="post_image"><img alt="Scenario Summary Issues" data-src="https://cms-assets.tutsplus.com/uploads/users/23/posts/26446/image/summary-renamed.jpg"><figcaption>Scenario Summary issues</figcaption></figure><h2>Conclusion</h2><p>The next time you want to compare several sets of data,
maybe to decide among multiple courses of action, give the Excel What-If Analysis - Scenario Manager a
try. It might show exactly what you need to make a decision.</p>2016-05-16T12:55:55.000Z2016-05-16T12:55:55.000ZBob Flissertag:computers.tutsplus.com,2005:PostPresenter/cms-26304How to Use Excel: 12 Techniques for Power Users<p>Excel is great, but trying to figure it out how to use Excel on your own can get you only so far because it isn’t intuitive. But if you use the techniques and tips in this tutorial, you’ll be able to get your work done faster and without a lot of stress. </p><p>Learn how to apply Excel formulas, calculations, filtering, data manipulation, workflow efficiencies, and more. Here are a dozen Excel techniques and features you need to know.</p><h2>Screencast</h2><figure data-video-embed="true" data-original-url="https://www.youtube.com/watch?v=6kBsMoB7a3Q&feature=youtu.be" class="embedded-video">
<iframe data-src="//www.youtube.com/embed/6kBsMoB7a3Q?rel=0" frameborder="0" webkitallowfullscreen="webkitallowfullscreen" mozallowfullscreen="mozallowfullscreen" allowfullscreen="allowfullscreen"></iframe>
</figure>
<p><span class="sectionnum">Before you begin, go ahead and download the <a href="https://cms-assets.tutsplus.com/uploads/users/23/posts/26304/attachment/how-to-use-excel-worksheet.xlsx" target="_self">free Excel file worksheet</a>. There are multiple sheets included in the file, one to practice each of the twelve Excel techniques. Watch the complete tutorial screencast above or work through the step-by-step written version below.<br></span></p><h2>
<span class="sectionnum">1.</span> The Basics of Writing a Formula</h2><p>Every formula <em>must</em> start with an equal sign. If you don’t have an equal sign, Excel won’t do the calculation; it will just display what you typed. It’s Excel’s way of saying, “Hey, calculate this.” Think of the result of your formula as what should be on the left side of the equal sign if you were using pencil and paper.</p><p>And where do you write a formula? Write it in the cell where you want the answer to appear. Refer to the 'simple' sheet in the free Excel file for this exercise.</p><p>Example: add the values of <strong>B2</strong>, <strong>B3</strong> and <strong>B4</strong> and put the total in <strong>B6</strong></p><p>In <strong>B6</strong>, enter this formula:</p><p><code class="inline">=B2 + B3 + B4</code></p><figure class="post_image"><img alt="Simple formula" data-src="https://cms-assets.tutsplus.com/uploads/users/23/posts/26304/image/simple-formula.jpg"><figcaption>Simple formula</figcaption></figure><p>Then press <strong>Enter</strong> to get the result. If you don’t press <strong>Enter</strong> (or click the small <strong>check mark</strong> just above <strong>column A</strong>), nothing will happen. </p><figure class="post_image"><img alt="Simple Formula Entered" data-src="https://cms-assets.tutsplus.com/uploads/users/23/posts/26304/image/simple-formula-entered.jpg"><figcaption>Simple formula entered</figcaption></figure><p>Subtraction, multiplication, division and exponents work the same way. For the most part, use the symbols you’d normally use or refer to this chart:<br></p><table>
<thead><tr>
<th>Operation</th>
<th>Symbol</th>
</tr></thead>
<tbody>
<tr>
<td>Addition</td>
<td>+</td>
</tr>
<tr>
<td>Subtraction</td>
<td>-</td>
</tr>
<tr>
<td>Multiplication</td>
<td>*</td>
</tr>
<tr>
<td>Division</td>
<td>/</td>
</tr>
<tr>
<td>Exponent</td>
<td>^ (example: B5^2 is the value of B5 squared)</td>
</tr>
<tr>
<td>Greater than</td>
<td>></td>
</tr>
<tr>
<td>Less than</td>
<td><</td>
</tr>
<tr>
<td>Greater than or equal to</td>
<td>>=</td>
</tr>
<tr>
<td>Less than or equal to</td>
<td><=</td>
</tr>
<tr>
<td>Join</td>
<td>&</td>
</tr>
</tbody>
</table><h2>2. For More Complex Calculations, use Functions<br>
</h2><p>If you want to do a calculation that’s a little more involved than a simple formula, insert a function into your formula. Excel has about 400 of them, so you’ll probably find some that you need. There are functions for business, statistics, finance, date and time, text, document information and more.<br></p><p>The syntax of all functions is:</p><p><b><code class="inline">=NAME OF FUNCTION(......)</code></b></p><p>You always start with an equal sign if the function is at the beginning of a formula, then the name of the function, then a set of parentheses. There’s usually something in the parentheses, but not always.<br></p><p>Example: Find the total and the average of a column of numbers. Refer to the 'functions' sheet in the free Excel file for this exercise.</p><p>In B10, enter the function:</p><p><code class="inline">=SUM(B2:B8)</code></p><p>In B11, enter the function:<br></p><p><code class="inline">=AVERAGE(B2:B8)</code></p><figure class="post_image"><img alt="Sum and Average" data-src="https://cms-assets.tutsplus.com/uploads/users/23/posts/26304/image/sum-and-average.jpg"><figcaption>Sum and average</figcaption></figure><p>Press <strong>Enter</strong> to get the results. </p><figure class="post_image"><img alt="Sum and average result" data-src="https://cms-assets.tutsplus.com/uploads/users/23/posts/26304/image/sum-and-average-results.jpg"><figcaption>Sum and average result</figcaption></figure><h2>
<span class="sectionnum">3.</span> For Fast, Intelligent Copy & Paste, Use AutoFill</h2><p>Let’s say you write a formula at the top of a column and want to re-use the formula down the column (or you have a formula at the beginning of a row and want to re-use the formula across the row). Instead of manually copying and pasting, use the <strong>AutoFill</strong> feature to make quick work out of it and have the formula adjust itself automatically. You can also use <strong>AutoFill</strong> to enter months or days of the week automatically.</p><p>To use <strong>AutoFill</strong>, look for the tiny dot in the lower-right corner of the current cell. (If you have several cells selected, they share a common dot.) When you put the mouse pointer on the dot, the pointer turns into a crosshair. Then drag the crosshair down the column or across the row.</p><p>Example: Do simple multiplication in <strong>D2</strong>, then <strong>AutoFill</strong> down to <strong>D8</strong>. Refer to the 'autofill' sheet in the free Excel file for this exercise.</p><p>Write the first formula as you normally would.</p><figure class="post_image"><img alt="Autofill formula" data-src="https://cms-assets.tutsplus.com/uploads/users/23/posts/26304/image/autofill1.jpg"><figcaption>Autofill formula</figcaption></figure><p>Enter the formula. If necessary, click back on <strong>D2</strong>. Roll the mouse pointer over the heavy dot in the cell’s lower-right corner, and the mouse pointer will become a crosshair. </p><figure class="post_image"><img alt="Crosshair" data-src="https://cms-assets.tutsplus.com/uploads/users/23/posts/26304/image/autofill2.jpg"><figcaption>Mouse becomes a crosshair</figcaption></figure><p>Either drag the crosshair down <strong>column D</strong>, or just <strong>double-click</strong>. The column will have the correct formulas and correct results down the column. </p><figure class="post_image"><img alt="Double-click to select the column" data-src="https://cms-assets.tutsplus.com/uploads/users/23/posts/26304/image/autofill3.jpg"><figcaption>Double-click to select the column</figcaption></figure><h2>
<span class="sectionnum">4.</span> Filtering and Sorting Data</h2><p>You can use Excel as a small database, sorting rows of data alphabetically or numerically, and filtering just the data you want. It works best when columns have headers, like First Name, Last Name, and so on. </p><p>The most important thing to remember is <em>don’t</em> select a column before sorting. That will sort the column independently of the rest of the data, and that’s probably not what you want. When you click in a column, Excel is smart enough to know what you’re doing.</p><p>When it comes to filtering, Excel has great automatic tools.</p><p>Example: Sort a list by last name, city or other column, in ascending or descending order. Refer to the 'filter and sort' sheet in the free Excel file for this exercise.</p><figure class="post_image"><img alt="Simple sort" data-src="https://cms-assets.tutsplus.com/uploads/users/23/posts/26304/image/simple-sort.jpg"><figcaption>Simple Sort in Excel</figcaption></figure><p>Click any cell in the <strong>Last Name</strong> column. On the <strong>Home</strong> tab of the <strong>Ribbon</strong>, click <strong>Sort and Filter</strong>, then choose <strong>A to Z</strong> or <strong>Z to A</strong>. Try this with any of the other columns.</p><p>You may have noticed that you can do filtering from the same drop-down menu. </p><p>Example: Now Filter the list to show addresses only from <strong>Alaska</strong> and <strong>California</strong>.</p><p>Click anywhere inside the data area and from the <strong>Sort and Filter</strong> menu, choose <strong>Filter</strong>.</p><figure class="post_image"><img alt="Simple Filter" data-src="https://cms-assets.tutsplus.com/uploads/users/23/posts/26304/image/simple-filter.jpg"><figcaption>Simple Filter</figcaption></figure><p>As soon as you do, you’ll notice the column headers get drop-down arrows. Now: </p><ul>
<li>
<strong>Click</strong> the <strong>drop-down arrow</strong> in the <strong>State</strong> column.</li>
<li>Then <strong>click</strong> the <strong>Select All</strong> box to clear all the state checkboxes.</li>
<li>
<strong>Re-select</strong> just <strong>AK</strong> and <strong>CA</strong>, then click <strong>OK</strong>.</li>
</ul><figure class="post_image"><img alt="Filter box" data-src="https://cms-assets.tutsplus.com/uploads/users/23/posts/26304/image/filter-box.jpg"><figcaption>Filter Box</figcaption></figure><p>The list now shows only the rows for those two states. If you want, sort the states A to Z. That will group all the Alaska rows above the California rows (<em>highlighted in the screen shot below</em>).</p><p>If you want to clear the filter, <strong>click the drop-down arrow</strong> on the <strong>State</strong> column again and choose <strong>Clear Filter</strong>.</p><figure class="post_image"><img alt="Clear Filter" data-src="https://cms-assets.tutsplus.com/uploads/users/23/posts/26304/image/clear-filter.jpg"><figcaption>Clear Filter</figcaption></figure><p>You can remove the drop-down arrows the same way you got them: click the <strong>Sort and Filter</strong> menu and choose <strong>Filter</strong>, again.<br></p><h2>
<span class="sectionnum">5.</span> Removing Duplicate Rows</h2><p>If you have a lot of data, especially if it’s imported from somewhere such as a web form, you might have duplicate rows. Excel does a great job of removing the duplicates and allows you to decide how similar rows need to be for them to be considered duplicates.</p><p>Example: Use address information to determine which rows are duplicates. Refer to the 'remove duplicates' sheet in the free Excel file for this exercise.</p><p>When we scroll down this example file, we see there are three identical rows where the name is Samantha Carter: the data in all the columns are identical, so we know it’s the same person listed three times. There are also two rows where the name is Sharon Valeri, but all the other info is different, so we know these are two people with the same name. </p><figure class="post_image"><img alt="Select both Sam and Sharon " data-src="https://cms-assets.tutsplus.com/uploads/users/23/posts/26304/image/sam-and-sharon-selected.jpg"><figcaption>Select both Sam and Sharon </figcaption></figure><p>Make sure to click somewhere in the data area. Then in the <strong>Data</strong> tab of the <strong>ribbon</strong>, click <strong>Remove Duplicates</strong>.</p><figure class="post_image"><img alt="Remove Duplicates dialog box" data-src="https://cms-assets.tutsplus.com/uploads/users/23/posts/26304/image/remove-dup-dialog.jpg"><figcaption>Remove Duplicates dialog box</figcaption></figure><p>Leave all the checkboxes selected. This way, Excel will consider rows to be duplicates only if all their columns are identical.</p><p>Click <strong>OK</strong>. It tells us that two duplicates were removed, which is what we expected. When we scroll down, we see two of the three Samantha Carter records were removed, and both the Sharon Valeri records remain.</p><figure class="post_image"><img alt="Duplicates removed" data-src="https://cms-assets.tutsplus.com/uploads/users/23/posts/26304/image/duplicates-removed.jpg"><figcaption>Duplicates removed</figcaption></figure><h2>
<span class="sectionnum">6.</span> Separating Data in One Column Into Multiple Columns<br>
</h2><p>If you have a column of data that you need to split into multiple columns, use the <strong>Text to Columns</strong> command. Just be aware that it isn’t perfect because it can’t read your mind, so you might have to do some manual cleanup.</p><p>Example: Split a column containing full names into separate columns for first and last names. Refer to the 'splitting' sheet in the free Excel file for this exercise.</p><p>Here we have similar data as in previous examples. Note that three people in the list have three names, so we will need two empty columns.</p><p>First, insert two blank columns to the right of <strong>column A</strong>:</p><p>Place the <strong>mouse pointer</strong> on the header of <strong>column B</strong>, so it becomes a <strong>downward-pointing arrow</strong>. <strong>Drag to the right</strong>, so columns <strong>B</strong> and <strong>C</strong> are <strong>selected</strong>.</p><figure class="post_image"><img alt="Selecting Excel columns" data-src="https://cms-assets.tutsplus.com/uploads/users/23/posts/26304/image/select-columns.jpg"><figcaption>Selecting Excel columns</figcaption></figure><p><strong>Right-click</strong> and select <strong>Insert</strong> from the popup menu. Since you had two columns selected, you’ll now have two blank ones.</p><p><strong>Select</strong> all the names in <strong>column A</strong>. (Quick way: click the first one in <strong>A3</strong>, then press <strong>Ctrl + Shift + Down arrow</strong>. Then <strong>scroll back to the top</strong>.)</p><p>On the <strong>Data</strong> tab, select <strong>Text to Columns</strong>. Choose <strong>Delimited</strong>, then click <strong>Next</strong>.</p><figure class="post_image"><img alt="Text to Tabs columns" data-src="https://cms-assets.tutsplus.com/uploads/users/23/posts/26304/image/text-to-columns-1.jpg"><figcaption>Text to Tabs columns</figcaption></figure><p>Choose <strong>Space</strong> as the only delimiter. Treating consecutive delimiters as one will be selected by default, so leave that. Scroll down the list to see the names that will span into three columns.</p><figure class="post_image"><img alt="Text to Tabs columns span" data-src="https://cms-assets.tutsplus.com/uploads/users/23/posts/26304/image/text-to-columns-2.jpg"><figcaption>Text to Tabs columns span</figcaption></figure><p>Click <strong>Next</strong>, then click <strong>Finish</strong>. <strong>Column A</strong> will now have just first names, <strong>column B</strong> will have last names, and <strong>column C</strong> will have three names that spilled over. </p><figure class="post_image"><img alt="Split names" data-src="https://cms-assets.tutsplus.com/uploads/users/23/posts/26304/image/split-names.jpg"><figcaption>Split names</figcaption></figure><p>You can manually edit these three names, then <strong>delete column C</strong>, which should be empty.</p><h2>
<span class="sectionnum">7.</span> Joining Multiple Columns Into a Single Column</h2><p>Excel does a good job of going the other way, too: if you have multiple columns and want to join them into one. There are a couple of ways of doing this, but I’ll show you the simplest, which is to use a formula. Refer to the 'joining' sheet in the free Excel file for this exercise.</p><p>Like you did above, insert a new column after <strong>column B</strong>. Position the <strong>mouse pointer </strong>on the <strong>header</strong> of <strong>column C</strong> so the mouse pointer becomes a <strong>downward pointing arrow</strong>…</p><figure class="post_image"><img alt="Joining" data-src="https://cms-assets.tutsplus.com/uploads/users/23/posts/26304/image/joining1.jpg"><figcaption>Insert a new column</figcaption></figure><p>…then select <strong>Insert</strong> from the popup menu. Give the new column a title of <strong>Full Name</strong>.</p><p>Click in cell <strong>C3</strong> at the top of the new column to do the first formula. To make it more understandable, we’ll do it with a small error, then fix it.</p><p>Since we want to join cell contents, not add numbers, the operator to use is an ampersand. So make the formula:</p><p><code class="inline">=A3 & B3</code></p><figure class="post_image"><img alt="Joining formula" data-src="https://cms-assets.tutsplus.com/uploads/users/23/posts/26304/image/joining2.jpg"><figcaption>Joining formula</figcaption></figure><p>But when you enter it, the first and last names are squished together, without a space in between. So delete it. Rewrite the formula again, but with another item included:</p><p><code class="inline">=A3 & " " & B3</code></p><p>Putting a space inside the double quotation marks means the formula should literally include a blank space. </p><figure class="post_image"><img alt="Joining formula with space" data-src="https://cms-assets.tutsplus.com/uploads/users/23/posts/26304/image/joining3.jpg"><figcaption>Joining formula with space</figcaption></figure><p><strong>Enter</strong> it, and <strong>AutoFill</strong> down to the bottom.</p><figure class="post_image"><img alt="AutoFill down" data-src="https://cms-assets.tutsplus.com/uploads/users/23/posts/26304/image/joining4.jpg"><figcaption>AutoFill down</figcaption></figure><h2>
<span class="sectionnum">8.</span> Quick Formatting of Numbers and Worksheets<br>
</h2><p>Here are some quick ways of formatting numbers (dollars, percent, etc.) and worksheets (colors, fonts). </p><p>Refer to the 'formatting' sheet in the free Excel file for this exercise.</p><p>The numbers in this worksheet should have commas to separate thousands, but don’t need decimals. You might want to apply dollar signs to the first row, the <strong>Total row</strong> and the <strong>Total column</strong>. Also, the last column is formatted as decimals but should look like percentages.</p><p>Select the top row of numbers through the <strong>Total column</strong>.</p><p>Hold down the <strong>Ctrl key</strong> and select across the <strong>Total</strong> row at the bottom (the top and bottom rows should now both be selected).</p><figure class="post_image"><img alt="Formatting selection" data-src="https://cms-assets.tutsplus.com/uploads/users/23/posts/26304/image/formatting1.jpg"><figcaption>Formatting selection</figcaption></figure><p>On the ribbon, click the <strong>Dollar sign</strong> button or press <strong>Ctrl + Shift + $</strong>. That applies dollar signs, commas and decimals. (We’ll remove the decimals in a minute.)</p><p>Now select all the numbers in between and click the <strong>Comma style button</strong> or press <strong>Ctrl + Shift + !</strong>. That applies commas and decimals.</p><p>To remove all the decimals, select all the numbers in the sheet except the <strong>Percent column</strong> at the end and click the <strong>Decrease Decimal</strong> button twice.</p><figure class="post_image"><img alt="Formatting decrease decimal" data-src="https://cms-assets.tutsplus.com/uploads/users/23/posts/26304/image/formatting2.jpg"><figcaption>Excel formatting - decrease decimals</figcaption></figure><p>Now let’s apply percentages.</p><p>Select all the numbers down the <strong>% of Total</strong> column, then click the <strong>%</strong> sign or press <strong>Ctrl + Shift + %</strong>.</p><p>If you want, click the <strong>Increase Decimal</strong> button once or twice.</p><figure class="post_image"><img alt="Applying percentages" data-src="https://cms-assets.tutsplus.com/uploads/users/23/posts/26304/image/formatting3.jpg"><figcaption>Applying percentages</figcaption></figure><p>To format the sheet, there are three ways of doing it:</p><ul>
<li>Select the row and column headers and individually apply colors, fonts, etc.</li>
<li>Select the row and column headers and apply built-in styles.</li>
<li>Format the whole sheet as a table and let Excel apply the design.</li>
</ul><p>For speed, here are the second and third methods.</p><p>Select all the column headers, then on the ribbon, click <strong>Cell Styles</strong>. Choose one.</p><figure class="post_image"><img alt="Formatting cell styles" data-src="https://cms-assets.tutsplus.com/uploads/users/23/posts/26304/image/formatting4.jpg"><figcaption>Excel column formatting cell styles</figcaption></figure><p>Do the same for the row headers.</p><figure class="post_image"><img alt="Excel header formatting cell styles" data-src="https://cms-assets.tutsplus.com/uploads/users/23/posts/26304/image/formatting5.jpg"><figcaption>Excel header formatting cell styles</figcaption></figure><p>Undo a couple of times. </p><p>Now click <strong>Format as Table</strong> and choose a design.</p><p>Click <strong>OK</strong> in the dialog box that comes up. Notice that the columns have drop-down arrows so you can filter them.</p><h2>
<span class="sectionnum">9.</span> Manipulating Columns, Rows, Page Breaks and Worksheets</h2><h3>Changing Row & Column Size<br>
</h3><p>If a column is too narrow, you might have text that’s cut off or numbers that display as pound signs. If a line is too short, you might not be able to read it. Refer to the 'rows & columns' sheet in the free Excel file for this exercise.</p><p>Adjusting column width and row height works basically the same way: put the mouse pointer on the right or bottom edge of the row or column so the mouse pointer becomes a 2-headed arrow, then drag until the row or column is the size you want. Or you can <strong>double-click</strong> to get the row or column sized automatically. (This works the same way in <strong>Windows File Explorer</strong> in details view.)</p><figure class="post_image"><img alt="Windows File Explorer column width row height" data-src="https://cms-assets.tutsplus.com/uploads/users/23/posts/26304/image/col-width-row-height.jpg"><figcaption>Windows File Explorer column width row height</figcaption></figure><h3>Manipulating Page Breaks</h3><p>There are two ways of creating page breaks:</p><ul>
<li>Insert them manually, from the Page Layout tab</li>
<li>Using Page Break Preview</li>
</ul><p>To insert a page break manually, select the row or column header where you want the page to break, then from the <strong>Page Layout tab</strong> of the ribbon, click the <strong>Breaks drop-down</strong> and select <strong>Insert Page Break</strong>.</p><figure class="post_image"><img alt="Insert page break" data-src="https://cms-assets.tutsplus.com/uploads/users/23/posts/26304/image/insert-page-break.jpg"><figcaption>Insert page break</figcaption></figure><p>To adjust page breaks, use <strong>Page Break Preview</strong>. <br></p><p>Click the <strong>Page Break Preview</strong> icon in the lower-right corner, then drag the lines. Dotted lines are automatic page breaks, and if you insert page breaks, those lines will be solid.</p><figure class="post_image"><img alt="Adjust page breaks" data-src="https://cms-assets.tutsplus.com/uploads/users/23/posts/26304/image/adjust-page-breaks.jpg"><figcaption>Adjust page breaks</figcaption></figure><h3>Adjusting Worksheets</h3><p>To insert, delete, move or rename worksheets, use the tabs in the lower-left corner of the screen.</p><p>This workbook has just one sheet, called <strong>Sheet1</strong>. To rename it:</p><ul>
<li>
<strong>Double-click</strong> the tab. </li>
<li>Type a new name (like N. America), then press <strong>Enter</strong>.</li>
</ul><figure class="post_image"><img alt="Working with sheets" data-src="https://cms-assets.tutsplus.com/uploads/users/23/posts/26304/image/tabs1.jpg"><figcaption>Working with sheets</figcaption></figure><p>To insert a new, blank sheet, <strong>Click</strong> the round <strong>Plus sign</strong> next to the tab.</p><p>To duplicate the sheet:</p><ul>
<li>
<strong>Drag</strong> the sheet tab to the right until you see a <strong>downward-pointing triangle</strong> next to it. </li>
<li>Press <strong>Ctrl</strong> so you see a small plus sign in the mouse pointer. </li>
</ul><figure class="post_image"><img alt="Duplicating a sheet" data-src="https://cms-assets.tutsplus.com/uploads/users/23/posts/26304/image/tabs2.jpg"><figcaption>Click the triangle</figcaption></figure><p>Release the mouse first. You’ll see a new sheet with the same name and a “<strong>(2)</strong>” after it.<br></p><figure class="post_image"><img alt="Your new sheet is created" data-src="https://cms-assets.tutsplus.com/uploads/users/23/posts/26304/image/tabs3.jpg"><figcaption>Your new sheet created</figcaption></figure><p>You can drag the sheets into any position you want. Sheets get dropped where you see the downward-pointing triangle.<br></p><h2>
<span class="sectionnum">10.</span> Keeping Column Headers Visible</h2><p>When you have a long sheet, it’s easy for a column header to scroll out of view, but there’s a feature to prevent that. When you print a long sheet, you want to make sure the column headers appear at the top of each page—and please, don’t do it by copying and pasting. That will become a nightmare.</p><p>Refer to the 'headers' sheet in the free Excel file for this exercise.</p><h3>Keeping Headers Visible When Scrolling</h3><p>In this sheet, click on cell <strong>A3</strong>, which is just below the row of column headers. Then in the <strong>View</strong> tab of the ribbon, click <strong>Freeze Panes. </strong>And select <strong>Freeze Panes</strong> from the drop-down. </p><figure class="post_image"><img alt="Select Freeze Panes" data-src="https://cms-assets.tutsplus.com/uploads/users/23/posts/26304/image/headers1.jpg"><figcaption>Select Freeze Panes</figcaption></figure><p>Now you can scroll up and down the sheet and still see the headers.</p><p>You can unfreeze the panes the same way: click <strong>Freeze Panes</strong>, then select <strong>Unfreeze Panes</strong> from the drop-down.</p><p>However, this has no effect on printing. To print column headers on every page:</p><ul>
<li>Go to the <strong>Page Layout</strong> tab of the ribbon and click <strong>Print Titles</strong>. </li>
<li>
<strong>Click</strong> inside the <strong>Rows to Repeat</strong> at <strong>Top</strong> box.</li>
<li>On the worksheet, select the <strong>headers of rows 1 and 2</strong>. That puts <strong>$1:$2</strong> in the box.</li>
</ul><figure class="post_image"><img alt="Select the headers" data-src="https://cms-assets.tutsplus.com/uploads/users/23/posts/26304/image/headers2.jpg"><figcaption>Select the headers</figcaption></figure><p>To see that it works, go to <strong>Print Preview: File > Print</strong> or press <strong>Ctrl + P.</strong><br></p><p>Click the page tabs on bottom to scroll through the pages and see the headers.</p><figure class="post_image"><img alt="Scroll through the headers" data-src="https://cms-assets.tutsplus.com/uploads/users/23/posts/26304/image/headers3.jpg"><figcaption>Scroll through the headers</figcaption></figure><h2>
<span class="sectionnum">11.</span> IF Function: Making Cell Value Depend on a Condition</h2><p>Excel has several functions that can make decisions of what value to display. These are all based on the IF function. Once you’re familiar with it, it will be easier for you to use the more complex ones.</p><p>The IF function has three arguments:</p><ul>
<li>What is the condition you’re testing.</li>
<li>What the cell should display if the condition is true.</li>
<li>What the cell should display if the condition is false.</li>
</ul><p>When a function has several arguments, you separate them with commas. So this is the syntax:</p><p><code class="inline">=IF(condition to test, what to return if true, what to return if false)</code><br></p><p>Refer to the 'functions' sheet in the free Excel file for this exercise.<br></p><p>Example: We have a list of orders placed by people in various states. If an order is from New Jersey, apply a 7% sales tax. If an order is from anywhere else, don’t apply tax.</p><p>At the top of the <strong>Tax column (H3)</strong>, enter this formula:</p><p><code class="inline">=IF(C3="nj", G3 * .07, "")</code></p><p>This means:<br></p><ul>
<li>The condition to test is: does the cell in the <strong>State</strong> column contain the value “<strong>nj</strong>”? (Note this is not case sensitive, and because it’s text, it must be in quotes.)</li>
<li>If the condition is true, the cell should have a value of Sale <strong>* 7%</strong>.</li>
<li>If the condition is false, the cell should have no value (empty set of quotes). </li>
</ul><figure class="post_image"><img alt="IF Funcation results" data-src="https://cms-assets.tutsplus.com/uploads/users/23/posts/26304/image/if-sheet-1.jpg"><figcaption>IF Function results</figcaption></figure><p><strong>AutoFill</strong> down to the bottom. Most cells in the Tax column will be blank, and those that aren’t have a sales tax because that row shows <strong>NJ</strong> in the <strong>State</strong> column, as shown below.<br></p><figure class="post_image"><img alt="If sheet Autofill" data-src="https://cms-assets.tutsplus.com/uploads/users/23/posts/26304/image/if-sheet-2.jpg"></figure><h2>
<span class="sectionnum">12.</span> SUMIF and AVERAGEIF: Adding and Averaging Depending on a Condition</h2><p>Similar to the IF function, the SUMIF and AVERAGEIF functions will find the sum and the average of a range of cells where a certain value or condition exists.</p><p>The functions get three arguments:</p><ul>
<li>What range do we look in to find the condition?</li>
<li>What is the condition or value to look for?</li>
<li>In the rows where that value exists, from what range do we take the sum or the average?</li>
</ul><p>The syntax of the two functions are similar—different names, but the same arguments:</p>
<p><b><code class="inline">=SUMIF(range to look in, value to pick, range to add)</code></b></p><p><b><code class="inline">=AVERAGEIF</code></b><b><code class="inline">(range to look in, value to pick, range to add)</code></b></p><p>Refer to the 'SUMIF' sheet in the free Excel file for this exercise.</p><p>Example: find the total of all orders to California, and the average order of Kona.</p><h3>Do a SUMIF First</h3><p>At the bottom of the <strong>Sale</strong> column, in <strong>G203</strong>, enter this formula to find the total of orders to California:</p><p><code class="inline">=sumif(C3:C201,"ca",G3:G201)</code></p><p>This means:<br></p><ul>
<li>Look down <strong>column C</strong>.</li>
<li>In <strong>column C</strong>, cherry-pick the rows that contain “<strong>ca</strong>”.</li>
<li>In the same row where we find “<strong>ca</strong>”, add the value of <strong>column G</strong> to the total.</li>
</ul><figure class="post_image"><img alt="Add the value of column G" data-src="https://cms-assets.tutsplus.com/uploads/users/23/posts/26304/image/sumif1.jpg"><figcaption>Add the value of the column</figcaption></figure><h3>Do an AVERAGEIF<br>
</h3><p>Now use the same technique to find the average of all sales of Kona.</p><p>Go into <strong>G204</strong> and enter this formula:</p><p><code class="inline">=averageif(D3:D201,"kona",G3:G201)</code></p><p>This means:<br></p><ul>
<li>Look down <strong>column D</strong>.</li>
<li>In <strong>column D</strong>, cherry-pick the rows that contain “<strong>kona</strong>”.</li>
<li>In the same row where we find “<strong>kona</strong>”, include the value of <strong>column G</strong> in the average.</li>
</ul><figure class="post_image"><img alt="Average the column" data-src="https://cms-assets.tutsplus.com/uploads/users/23/posts/26304/image/sumif2.jpg"><figcaption>Average the column</figcaption></figure><p>This should be the result:</p><figure class="post_image"><img alt="Final result" data-src="https://cms-assets.tutsplus.com/uploads/users/23/posts/26304/image/sumif3.jpg"><figcaption>Final result</figcaption></figure><h2>Using These Excel Techniques</h2><p>Learning how to use Excel is worth the effort. With these techniques and tips, you can put more of Excel's powerful, time-saving features to use in your own spreadsheets. Practice the lessons above, then work on incorporating these techniques into your workflow. </p>2016-04-18T13:49:12.000Z2016-04-18T13:49:12.000ZBob Flissertag:computers.tutsplus.com,2005:PostPresenter/fsw-40400Setting Up a Hosted Email Solution With Microsoft Office 365<p>Last time in our series <em>Setting Up Your Business Online Without Coding</em>, I showed you <a href="http://freelanceswitch.com/start/hosted-email-with-google-apps/">how to set up a hosted email service with <em>Google Apps</em></a>, providing you with a custom Gmail service that uses your own domain name.</p>
<p>Google isn't the only player in the hosted email game and another software giant looking to provide you with a hosted solution is none other than Microsoft. In this part of our series, I'll show you how to set up Microsoft's <em>Office 365</em> service that provides a hosted email solution that proves an excellent alternative to <em>Google Apps</em>.</p>
<p></p>
<p><em>Before continuing, I'd recommend revisiting the previous article in this series, <a href="http://freelanceswitch.com/start/hosted-email-with-google-apps/">Setting Up a Hosted Email Solution With Google Apps</a>, where I discuss the benefits of setting up a hosted email service.</em></p>
<h2>How Office 365 Works</h2>
<blockquote><p>Google isn't the only player in the hosted email game... </p></blockquote>
<p>Unlike <em>Google Apps</em>, <em>Office 365</em> hasn't been around for very long, only being officially launched in June 2011. For most, it provides a competing set of services to Google's offering, providing hosted email and calendar solutions as well as online storage and even an online office suite, known as <strong>Office Web Apps</strong>. All of these services require paid subscriptions based upon the number of users, per month, just like Google. </p>
<p>What sets <em>Office 365</em> apart from <em>Google Apps</em> is the way some of these services work. With certain subscription plans, you not only receive access to the Office Web Apps but are also provided with a full copy of Microsoft Office for Windows or Mac for the duration of your subscription. For many freelancers and small businesses just starting out, this could save quite a lot of money since it would spread out the cost of the software along with the online services, instead of having to purchase Microsoft Office separately.</p>
<p>You may have already heard about this as both Microsoft and Adobe have shifted towards subscription-based software which, whilst generating polarising opinions, provides you with all updates and new versions so long as your subscription remains active.</p>
<p>Returning to the main focus of this article, <em>Office 365</em> offers <strong>Microsoft Exchange</strong> based hosted email. Exchange is one of the most popular mail platforms around, offering features such as push email and widespread compatibility. In fact, Google used to use this service, offering a facility known as <a href="http://www.google.com/sync/index.html">Google Sync</a> . What this allowed was for Gmail users to add their account as an <em>Exchange</em> account on devices such as iPhone and Windows Mobile, instead of a standard <em>IMAP</em> or <em>Gmail</em> setup, allowing for true push email when configured. </p>
<p>Google has removed this functionality and reserved it only for subscribers to <em>Google Apps for Business and Enterprise</em>. </p>
<ul>
<li>
<em>Office 365 Small Business</em> costs from <strong>$5 per user per month</strong> on their lowest service plan. This price point requires it paid annually but you can pay monthly for <strong>$6</strong> instead.</li>
<li>
<em>Office 365 for Small Businesses Premium</em> costs $12.50 per month, payable annually or $15 monthly.</li>
</ul>
<p>It's a little bit more than <em>Google Apps</em>, but at just $1 a month extra, it's really not much more. For the <em>Premium</em> subscription, you also receive a copy of Microsoft Office for Windows or Mac that you can run on up to 5 computers that you use. </p>
<p>You can compare all of the <em>Office 365</em> price plans at their <a href="http://office.microsoft.com/en-us/business/compare-office-365-for-business-plans-FX102918419.aspx">Microsoft Office 365 comparison page</a>.</p>
<h2>Google Apps and Office 365 Compared</h2>
<p>Overall, both services offer the same sort of functionality, but it's the way their functionality is implemented that sets them apart. </p>
<h3>1. Push Email</h3>
<p>As <em>Office 365</em> uses Microsoft Exchange, it can provide true push email facilities to devices that support Windows Mobile. <em>Google Apps for Business and Enterprise</em> plans do offer this functionality by setting up Google Sync which requires reconfiguring your Google account on your mobile device as <strong>Microsoft Exchange</strong>.</p>
<p>However, Google Sync only works on mobile devices so you'll not be able to have push email on your desktop computer. With most apps providing the ability to check for emails every minute, then it's not a feature that would be entirely missed, but <em>Office 365</em>'s Exchange platform still provides push.</p>
<h3>2. Email Structure</h3>
<p>Perhaps one of Gmail's most popular functions is it's incredible <strong>labels</strong> feature. Emails can be tagged with multiple labels and easily found, meaning that invoices could be tagged as both "to pay" and "invoices". With a more traditional email setup, we're limited to just folders for email organisation.</p>
<blockquote><p><em>Office 365</em> doesn't offer any such label functionality, instead it relies upon a traditional folder structure for filing messages away.</p></blockquote>
<p>Unfortunately, <em>labels</em> do have a downside. Unless you spend most of your time in the Gmail web app, on an Android device or within Google's official Gmail apps for iOS, managing labels can be a bit of a headache. So that Google can provide support for traditional email apps such as Apple Mail and Microsoft Outlook, it allows IMAP support. IMAP doesn't support labels and, instead, treats them as folders. This has the undesired effect of downloaded messages multiple times, meaning searching for messages can result in duplicate emails being shown. </p>
<p>Moreover, trying to file an email with labels within a standard email app can be tricky. There are Gmail-orientated apps out there such as <a href="http://airmailapp.info">Airmail</a> which offers a more Gmail-orientated workflow whilst still providing you with all the benefits of a desktop mail app.</p>
<p><em>Office 365</em> doesn't offer any such label functionality, instead it relies upon a traditional folder structure for filing messages away. If you're someone who spends most of their time within a desktop mail app, and would prefer to avoid the hassle of Gmail's label implementation, then <em>Office 365</em> might be the better choice.</p>
<p>I've used both services over the years and, for now at least, I've migrated to <em>Office 365</em> simply because I prefer to use my default mail apps and never really use labels. However, I wouldn't recommend one over the other simply because it all depends on how you will use it.</p>
<h2>Setting Up Office 365</h2>
<p>In order to use <em>Office 365</em>, you'll need to have the following:</p>
<ul>
<li>A domain name.</li>
<li>Access to your domain name settings (known as DNS records).</li>
<li>About 20 minutes of free time.</li>
</ul>
<p>Similar to <em>Google Apps</em>, there is a free 1-month trial of <em>Office 365</em> and the service I'll be configuring today is <em>Office 365 For Small Business</em>. </p>
<h3>Step 1: Sign Up for Office 365</h3>
<p>You can register for a free 1-month trial of <em>Office 365</em> at the <a href="http://office.microsoft.com/en-gb/try/">Office 365 Trial</a> page. You'll need to scroll down to find the business options. </p>
<p>Select <strong>Try Now</strong> and then complete the following registration form. </p>
<p>In the second part of the form, you'll be asked to enter your desired user ID. Once you enter the first part (your username), the company name is autofilled into the box next to it. </p>
<p>So we can be up and running straight away, Microsoft provide all users with a <em>companyname.onmicrosoft.com</em> address which can be replaced by a domain name at any time. </p>
<p>Once you've signed up, you'll be automatically logged into your new <em>Office 365</em> account, where you'll be prompted to enter a mobile number and confirm your backup email address in case you lose access to your account. </p>
<p>And with that, you're now registered for Microsoft <em>Office 365</em>! You can access your service at any time by visiting the <a href="https://portal.microsoftonline.com">Office 365 portal</a> and signing in with your account. </p>
<h3>Step 2: Configure for Email</h3>
<p>Now that we have a fully working <em>Office 365</em> service, it's time to configure it for email. Doing so will not only verify our domain name but also configure it to use for mail routing in one go, making it a little more straightforward than having to verify the domain name and <em>then</em> configure it for mail routing. </p>
<p>At the moment, we could simply use our <em>companyname.onmicrosoft.com</em> email address straight away, but since we're wanting a more professional email address anyway, let's just go ahead and set everything up now. </p>
<p>Within the <em>Office 365</em> portal, select the first option called <strong>email address</strong>. </p>
<p>You'll then be shown a screen entitled <em>Change your Office 365 email address to use@yourcompany.com</em>. </p>
<p>When you're ready, click on <strong>start now</strong> and enter your domain name that you wish to use with <em>Office 365</em>. </p>
<p>Select the options that best suit you with regards to existing email and website. I've selected that I've not been using email with this domain name before and that I <em>don't</em> have a website, even if you do. You'll find out why a little later on.</p>
<p>Once you've chosen your selected options, click <strong>next</strong>.</p>
<h2>Completing the Steps</h2>
<p>As you can see, Microsoft has provided me with multiple steps to complete:</p>
<ul>
<li>Confirm I own my domain name.</li>
<li>Update existing <em>Office 365</em> email addresses.</li>
<li>Add more users to my <em>Office 365</em> service plan.</li>
<li>Complete the process.</li>
</ul>
<p>We can perform all of these steps consecutively and can even start them straight away. Let's go through these step-by-step. </p>
<h3>Step 1. Confirm My Domain Name</h3>
<p>When you're ready, select <strong>start step 1</strong>.</p>
<p>You can select your domain registrar from the list or get the options manually. Similar to setting up <em>Google Apps</em>, selecting a registrar on the list provides step-by-step instructions to making the necessary changes. </p>
<p>Although my registrar, Godaddy, <em>is</em> on the list, for the purposes of this guide I'll be configuring the options manually. Select <strong>general instructions</strong> from the drop down menu and then click <strong>next</strong>.</p>
<p>Similar to <em>Google Apps</em>, verifying our domain name requires adding a <strong>DNS record</strong>. In a new tab or window, log in to your domain registrar and launch their <strong>DNS editor</strong>.</p>
<p>Microsoft provide two ways of verifying your domain name and either can be used. Unless you have a specific requirement, I would recommend just adding the <strong>TXT</strong> record. </p>
<p>We just need to add a new <strong>TXT</strong> record with the information Microsoft has provided us. Here's what it would look like when adding it to <em>Godaddy</em>.</p>
<p>It can take up to a few hours for the changes to take effect, depending on your domain registrar. I'd suggest waiting a few minutes after making and saving the changes, then attempting to verify. </p>
<p>Once Microsoft has verified your domain name, we can move on to the next step. Click <strong>finish</strong>.</p>
<h3>Step 2. Update Existing Email Addresses</h3>
<p>As the email address created when we registered used <em>@companyname.onmicrosoft.com</em>, <em>Office 365</em> will update this to our newly verified domain name. Simply confirm you wish to do so and that's all there is to it. </p>
<p>Now that we've changed our email address, we'll need to log back in. Enter your <em>new</em> email address and existing password (the one we specified when setting up the <em>Office 365</em> trial) and you'll be logged back in to the portal. </p>
<p>Don't worry that you might have to start the step-by-step guide again, we can carry on where we left off just by selecting the <strong>email address</strong> button again, which will have <em>2 of 4 steps completed</em> (written underneath).</p>
<h3>Step 3: Add Users</h3>
<p>At this stage, you can add more users if you wish. Remember, you will be paying per user per month for additional users once the trial expires. I'll be skipping this step as it will just be me using the service, for now. </p>
<h3>Step 4: Complete the Process</h3>
<p>Now that we have verified our domain name and updated our email settings, the final task is to configure how our mail is routed.</p>
<p>Every domain name has something called an <strong>MX Record</strong>. These records are basically a domain name or IP address of a particular mail server. So when an email is sent to <em>yourname@yourdomain.com</em>, the mail server sending the email looks at the domain name <em>yourdomain.com</em> to see what the mail server details are. Once it has them, the message is then passed to it, where it's then placed in the user's inbox. Without these records, email just doesn't function. </p>
<p>Here's where we're going to deviate slightly from the step-by-step guide. If you remember earlier in the article when we began the step-by-step guide and were asked if we had a website, I said "no". That was actually a lie as I'd previously set up a website with with Squarespace in a previous article in this series, <a href="http://freelanceswitch.com/start/online-setup-with-squarespace/">How to Set Up Your Online Business Presence With Squarespace</a>.</p>
<p><em>Office 365</em> prefers, if possible, to manage your entire domain name and that means being in control of your domain's DNS settings. Rather than adjusting your domain name's MX records, <em>Office 365</em> would prefer to manage your entire domain name and ask you change the <strong>name servers</strong>. What this means is that instead of going to your registrar to alter DNS settings, you'd do so within the <em>Office 365</em> portal.</p>
<p>Whilst this is meant for ease of use, I'd recommend avoiding this approach. There's actually nothing wrong with having your DNS managed by <em>Office 365</em> and, in some ways, it is more convenient. For me, I prefer to have my email, web hosting and DNS all managed separately. That way, if either my mail or web host go down then there's less chance of it having a knock-on effect on another service. Instead, we'll just do the same as we do with <em>Google Apps</em> and adjust the MX records manually. </p>
<p>Within the step-by-step guide, select <strong>save and close</strong> and, when you confirm this, you'll be back at the main portal screen. On the far right, select <strong>Manage your website and email domains</strong>.</p>
<p>Select your domain name from the list and then click where it says <strong>Verified</strong>. </p>
<p>It doesn't look like there's much information here but it's actually hidden from view. Click where it says <strong>DNS records created automatically by Office 365</strong> to reveal the information we need. </p>
<p>If you completed our <em>Google Apps</em> article then you'll know there were a number of MX records to configure. With <em>Office 365</em>, there's actually just one, but there are two more DNS records that we need, a <strong>TXT</strong> record and another called a <strong>CNAME</strong>. This lets us add our email address into many Exchange mail apps and it will automatically configure the settings appropriately, something that proves very useful. </p>
<p>Make a note of both the <strong>MX</strong> and <strong>CNAME</strong> labelled <strong>autodiscover</strong>, as well as the <strong>TXT</strong> record that starts <strong>v=spf1</strong>.</p>
<p>Add these three DNS records to your registrar's control panel and your mail routing configuration is set up and ready to go. </p>
<h2>Test Drive</h2>
<p>Now we've finished all our hard work, it's time to actually test our mail settings and see if they're all working. Remember that DNS settings can take time to update so if you find something doesn't work straight away, give it a little more time and try again. </p>
<p>Launch your preferred mail app, in my case it's Apple Mail, and use the following settings to log in:</p>
<ul>
<li>Account Type: <strong>Microsoft Exchange</strong>
</li>
<li>Username: <strong>Your email address</strong>
</li>
<li>Your Password: <strong>The password you have set</strong>
</li>
<li>Server: <strong>Your domain name</strong>
</li>
</ul>
<p>As we added an option for email apps to "autodiscover" our mail server settings, any Microsoft Exchange-compatible email app should notice this and automatically discover the correct settings. Without any further information, we have successfully added a new email account that will allow us to send and receive mail.</p>
<h2>Beyond Email</h2>
<p><em>Office 365</em> provides far more than just email and, as we touched upon before, includes features such as contacts and calendar syncing, in addition to online storage with their <em>SkyDrive</em> service.</p>
<h2>Wrapping Up</h2>
<p>Microsoft's <em>Office 365</em> provides a great alternative to <em>Google Apps</em> for those who would prefer to use an Exchange-based email service that offers similar features at a similar price. For users looking to purchase Microsoft Office at some point in the future who are also looking at hosted email, Microsoft <em>Office 365</em> provides a very tempting option that could save you money. </p>
<p>Whether you are a Google fan or prefer Microsoft's long-established Exchange platform, you'll not be disappointed with whichever provider you decide to go with. </p>
<h2>Further Reading</h2>
<ul>
<li><a href="http://office.microsoft.com/en-gb/products/?CTT=97">Office 365</a></li>
<li><a href="http://office.microsoft.com/en-gb/business/compare-office-365-for-business-plans-FX102918419.aspx">Compare Subscription Plans</a></li>
<li><a href="http://office.microsoft.com/en-gb/try/">Office 365 Trial</a></li>
</ul>2013-07-09T12:00:13.000Z2013-07-09T12:00:13.000ZJordan Merrick