Microsoft Excel is generally considered the industry standard in spreadsheet software, specifically because it can adapt to multiple functions and because it is easy to use. Once you know how to use Excel, you know how to use Excel for life. Excel has a variety of uses, from preparing simple order sheets to calculating to creating complex graphic statistical analyses. In this article, we will give you a walk-through on how to find outliers in Excel and why finding outliers is an essential piece of data analytics in statistics.

Before learning how to find outliers in Excel, you should first know that there is an outliers function embedded in the software that makes it easy to calculate what is and isn't an outlier. In fact, there are two methods of doing this, including a helpful graph that gives you a visual of the outliers and a formula that helps identify the outlier without forcing you to identify the outliers by hand.

Let's take a look at some vocabulary you'll need to know before you start learning how to find outliers in Excel.

Outlier

An outlier is a value that is significantly higher or lower than most of the values in your data and skews your data enough that you might draw an incorrect conclusion about the statistics represented by your data. Outliers aren't bad, but they definitely aren't good, either. You can think of an outlier as a bit of imperfection that can ruin your data - like finding a fly on your cake.

Box Plots

A box plot is a kind of graph that makes it easy to visually spot outliers. You can, of course, use Excel to create a box plot if you are so inclined, although that information will be on another tutorial. To put it simply, a box plot is useful because the box is the central tendency of the data. These are plots that show you how data is clustered around a central measure such as the median (middle) value in the data. The upper bound line is the limit of the centralization of that data. If you have values that are outliers on a box plot, then they become very obvious and stick out like a sore thumb.

Quartiles

Essentially, quartiles represent how data is broken up into quarters. Quartiles are actually a central tool in learning how to find outliers in Excel, since the formula for outliers relies on quartiles to make a calculation. A quartile is a dividing point which splits the data into quarters. There are 3 quartiles: Q1, Q2, and Q3. The first quartile (Q1) marks the lower quarter of your data where lesser values live. The middle values live between Q1 and Q3, with Q2 representing the center of your range, and values above Q3 are the upper portion of the data. Outliers live outside the inner quartile range. We'll go over how to calculate quartiles below.

Array and Quart

The array is the range of values that you are evaluating. And the quart is a number that represents the quartile you wish to return (e.g., 1 for the 1st quartile, 2 for the 2nd quartile, and so on). We mention array and quart under this vocabulary lesson because it is vital that you get these two meanings ingrained in your head since they are the values that you need to actually perform the calculation in Excel. Be careful not to get the two confused.

Now that you have a firm grasp on the terms you will be using to input the formulas for how to find outliers in Excel, it's time to get down to the step-by-step instructions.

As you know, Excel has a ton of functions for statistical analysis that can greatly improve your data analysis. One of those functions is the ability to input a formula to find outliers in statistical formulas. Outliers are pests in statistical analysis since the extreme data points can skew your results and cause misleading assumptions. Outliers can drag your statistical average up or down, which means you could be looking at wildly inaccurate data. Removing outliers gives you a better idea of your actual data.

So, how do you do it? There are a few steps involved in how to find outliers in Excel - enough steps that it could be potentially intimidating to get a good grip on the process. But if you stay with it and follow each step, you will be finding outliers very quickly.

When performing data analysis, you usually assume that your values cluster around some central data point (a median). But sometimes a few of the values fall too far from the central point. These values are called outliers (they lie outside the expected range). Outliers can skew your statistical analyses, leading you to false or misleading conclusions about your data. Using outliers is an easy way of proving or disproving your statistical assumption. If you have more outliers than you have accurate data points, then the chances are that your conclusion about your statistical data is not going to fall in line with your hypothesis.

It's easier than you think to learn how to find outliers in Excel. And since an outlier is a value that causes a misleading assumption, it's actually pretty important to know how to find them and how to get rid of them in order to accurately interpret your data. Excel makes this highly involved piece of statistical analysis into something that anybody can perform.

Featured Image by mohamed Hassan from Pixabay

We get it. Statistics can be intimidating. All those numbers and all those graphs and all of the rules on how to interpret everything - it's enough to make your head spin. Which is why we're happy to tell you that some things can be less stressful with the use of modern technology. Specifically, the little helper we're talking about is - no surprise - Microsoft Excel. Keep scrolling to learn everything you need to know about how to calculate z score in Excel. You'll be glad you did. It's easier than you think to learn how to calculate z score in Excel!

Z-Score Fundamentals

First things first! We need to break down the whole concept of a z score. Getting some clarity on the topic will make it that much easier to learn how to calculate a z-score in Excel when the time comes. You have to learn to walk before you can run, right?

Actually, depending on what you're using the z score for, there are two different z score formulas to choose from - and choosing the right formula is important. But rest assured that for any z score formula you will need two numbers: the mean (μ) and also the population standard deviation (σ). You will need both of these when you learn how to calculate the z-score in Excel.

Now that you know what a z score is and the basic formula you will be using, it's time to learn how to calculate z score in Excel. Be prepared by having your data points easily accessible and be forewarned that there will be a lot of data entry. Just as a reminder - double check your numbers and always calculate your z score twice!

image source: unsplash.com via Helloquence

See how easy that was? Learning how to calculate z score in Excel is a cakewalk because Excel does all the really hard work for you. Of course, the job of interpreting what the z score means still falls to your shoulders, but at least you don't have to make your head ache by hand-writing and calculating the z score formula. Simply use Excel to find your mean formula and your standard deviation, and then you're only one simple input away from finding the z score for any point of data you want.

Still not seeming so simple? Try it for yourself! Trust us, it gets easier after inputting the formula a few times!

Featured Photo by rawpixel.com from Pexels

Microsoft Excel is a ubiquitous product, and one that gets used for many organizational tasks. Most people are familiar with the program's basic format even if they don't own it themselves. In addition, simple operations such as adding or deleting columns or populating individual cells with data are easy to figure out with little instruction. There's a lot more to Excel than the basics, though: the program contains many logical and mathematical operations that are useful when dealing with large amounts of data, and these operations require more knowledge to use effectively. One of the most useful among these is the ability to calculate a value known as variance. Knowing how to find variance in Excel is a very helpful skill to have while designing any spreadsheet, especially if one understands the nuances of the function.

What Is Variance?

The Six Variance Functions In Excel

Plenty of Options

Knowing how to find variance in Excel isn't as simple as knowing the one function that will calculate the value. Excel has six different functions that can calculate variance, each of which has unique properties. Using the functions well requires knowing the qualities of each and understanding what situations they work best in. Fortunately, Excel's six VAR functions can be split into three easily explained groups.

VAR.P and VAR.S: Population and Sample Functions

VAR.P and VAR.S are the most basic functions for calculating variance. The letter after the period specifies the variance being calculated: VAR.P calculates population variance, and VAR.S calculates sample variance.

These are the most commonly used functions, but there is one complication involved in using them: the function does not exist in older versions of Excel and opening a spreadsheet that uses it with one of these versions will cause compatibility issues.

VAR and VARP: The Old Functions

Before VAR.P and VAR.S were implemented, VAR and VARP were Excel's basic variance functions. Like the more modern functions, they calculate the two basic kinds of variance: VAR calculates sample variance while VARP calculates population variance. These functions may eventually be phased out of Excel, but for the time being, they exist in every version of the program.

Microsoft considers VAR and VARP 'compatibility functions', and they exist to provide reliable choices for spreadsheets that will be opened on older versions of the software. However, they are slightly less accurate than the modern functions.

VARA and VARPA: Assigned Values

Of all the variance functions in Excel, VARA and VARPA are probably the most complicated of the options. VARA and VARPA calculate sample variance and population variance, respectively, but these two functions interact differently with other contents within the spreadsheet.

While Excel's other four variance functions only recognize the content of referenced cells if that content is a number, VARA and VARPA read any assigned values within referenced cells or arrays. This means that VARA and VARPA will recognize written numbers in any cells or arrays referenced in the function: if one contains the word 'four', the functions will calculate the variance as though that cell contained the number 4. The functions will recognize all other text, including an empty cell, as a 0.

VARA and VARPA can also recognize logical values in the same way: if the contents of a cell or array contain the word 'TRUE', the functions will count it as a 1, and if they contain 'FALSE' the functions will count it as a 0.

If a spreadsheet contains many written elements or logical values these functions can save a lot of time, but they should be used cautiously. Overlooked text can lead to cells registering a value they shouldn't and causing an inaccurate result.

Calculating Variance Using Excel's VAR Function

How to Find Variance in Excel

Now that all of the individual variance functions in Excel have been explained, it's time to learn how to implement them. Fortunately, all six of the functions use the same syntax, and aside from the differences already mentioned, work in the same way.

In order to calculate the variance of a set, you must first select a cell in your spreadsheet you want to display the variance in. Then, type an '=' sign followed by the name of the function you intend to use into that box, followed by a set of parentheses.

Place the numbers you want the variance to be calculated from inside the parentheses, separated by commas: these arguments can be numbers, logical values, or arrays. The function should automatically calculate the variance of the data you have selected and display it in the cell.

Example Using the Arguments List

Now that we've explained the principle, here are some specific examples of how to find variance in excel. First, we'll use a simple example: This one only uses numbers written directly into the function as arguments and does not interact with other cells in the spreadsheet.

Let's say we have a set that contains the numbers 13, 15, 18, 17, 24, 22 and 10. We know that this a sample of a larger set and for the sake of this example we expect the spreadsheet to be opened on older versions of Excel, so we use VAR to calculate the sample variance. To do this, we type this into the cell:

=VAR(13, 15, 18, 17, 22, 10)

Example Using an Array

Now that we've seen a basic example of how to find variance in Excel, here's an example that's more in line with how you would use the functions in an average spreadsheet.

This time let's say we have a set that's contained in a 10 x 4 box of cells in our spreadsheet, with cell A1 at the top left-hand corner of the box and cell D10 in the bottom right. We use VAR to calculate the sample variance and this time we enter the arrays of the cells containing the data as the arguments. That will look like this:

=VAR(A1:D10)

Conclusion

With this, we've gone over everything you need to know about how to find variance in Excel. You've learned what variance is and have a general understanding of both its importance in statistics and how to derive useful values from it.
 
You've learned about population and sample variance and what situations each form of variance is used in. You've seen all six of the functions Excel provides for calculating the value. You know what functions to use to have your functions recognize assigned values, and you know which functions to use when compatibility is a concern.
 
You understand the syntax of the functions and have seen firsthand what the use of those functions looks like in various situations.

Congratulations! You are now fully prepared to design spreadsheets that calculate and display variance, and are one step closer to being an Excel pro!

Featured Image: Image by Christian Dorn from Pixabay

There are times where you will want to print from any number of applications on your computer. This includes Microsoft Excel. The software allows you to not only create spreadsheets but also labels. If you have several labels you need to produce, whether for mailing or for personal identification, you will want to generate the labels in Excel and then go about printing the information. Here is what you need to know regarding how to print labels from Excel.

Prepare the Worksheet and Enter the Data

Before you can begin with looking at how to print labels from Excel, you need to properly create your worksheet, format it and then set up the data to be used within Microsoft Excel. This example will focus on producing mailing labels for print (which you can print on sticky paper, so it is easy to remove the label and slap it onto an envelope or mailing box), although you can use the steps for generating any kind of labels for printing.

Creating the Labels In Excel

Open up Microsoft Excel. You don't need to adjust the number of columns or rows you're working in, so just dive right into the entering of information. You will want to type in the first cell of each column the basic information you will need for the label.

In the case of a mailing label, the first column should be the person's "Title" (as in Mr, Ms., Dr., and so on). The second column should be their first name, the third column should be their last. The fourth column should be street address, followed by city, state, and ZIP code.

If you're making something other than a mailing label, you need to break down the information into individual columns. Perhaps you're printing off ID labels for use by individuals at a conference. You can still use the first and last name columns, only now add in their business title, company they work for, and so on.

Type In the Data

Now that you have the columns created you can go about entering in the data. Fill out all the information you have, making sure not to leave blank rows or columns. Once you have completed the process of entering all the information into your file, make sure to save it.

Formatting the File

You will need to connect to an Excel worksheet from Word to print your data. As this is likely the first time you have done this, you will need to enable a conversion format between the two Microsoft programs.

To do this, click the "File" button in the top left corner of the program window. From here, select "Options." A new "Word Options" window will open up. In the display window, click "Advanced" along the left side of the screen, then choose "General." This will bring up a new series of information. From the new listed material, check "Confirm File Format Conversion on Open," then click "OK" to confirm everything. This will allow you to create the labels as you import the data.

Opening Word

Now that you are all set and ready to use Word to help aid you with the printing process, you'll want to launch Microsoft Word and open a blank Word document (this should open automatically). Click on the "Mailings" tab, then select "Start Mail Merge" and follow up by clicking "Labels."

Scroll through and select the brand you'd like in the "Label Vendors" box and select a product number. This will be displayed on the label package. You can also choose "New Label" if you'd like to enter in any custom label dimensions (if not, go by the size of the label you are interested in, which should be listed, unless you are specially crafting the label yourself and are not using a pre-made label size).

Connecting Labels to Word

Microsoft Word is now all set up to receive your information from Excel. The next step for how to print labels from Excel is to click on the "Mailings" tab and choose "Select Recipients" from the Mail Merge group. In the new pull-down menu select "Use an Existing List."

Now, choose the Excel worksheet you created earlier and click "Open." Now, select "OK" in order to confirm that you want to use this particular list. Click "OK" a second time when asked to select the table. In a moment, the Microsoft Word screen will generate new labels and say "Next Record."

Connecting Mail Merge Fields

With everything connecting as it should, your next step for how to print labels from Excel is to connect mail merge fields. This way, the labels will appear correctly within Microsoft Word, allowing you to print the data.

In order to do this, you will need to select the first label listed on your Microsoft Word document and then click on "Mailings" from the top of the screen. Now choose "Write & Insert Fields" from the pull-down menu and finally click on "Address Block."

A new set of options will appear on the screen. Choose "Insert Address Block" from the window and then click on "Match Fields."

The window will now display what the labels will look like should you accept the input information. Look over the displayed information to make sure it is entered in properly (it should be, as long as you typed everything into Excel correctly). Scroll through the display window and, if any changes need to be made, click on the drop-down arrow next to the display and make the necessary edits.

Now, click "OK." Click "OK" a second time to exit out of the dialog box and then click back on the "Mailing" tab, followed by "Write & Insert Fields," and finally "Update Labels."

Merge

With everything set, now all you need to do is merge the information from Excel into the document. Choose the "Mailing" tab, click "Finish" and then choose "Finish & Merge" from the list of options. From there, choose "Edit Individual Documents" to look over how all the printed labels will eventually appear in the document. If it looks as it should, choose "All" and then "OK."

From here a brand new document will load on your screen. This imports all the labels from your Microsoft Excel worksheet. You can now make any necessary edits or adjustments to the information if need be.

Previewing and How to Print Labels From Excel

Everything has now been imported and your labels are ready to go. You are now at the final stages of the how to print labels from Excel instructions. You need to make sure everything looks good to print.

Click on the "File" tab in the program, then choose "Print Preview." This will load a window and display exactly how the document will look after you print it. Go over everything to make sure it looks correct. When you are satisfied with how the information is presented choose "OK."

If you are printing on sticky labels, you need to make sure the paper is inserted correctly. If you are using a standard desktop printer and the paper is inserted in a tray on the bottom of the printer, you will typically want to insert the labels facing down (the paper is spun up, so when it rotates the ink will be applied to the correct side of the document). If, on the other hand, you use a printer that is rear fed, with the paper positioned vertically behind the printer, the label side will face outward, toward you.

Once the paper is in and you're ready to print, click on the "File" tab again and then choose "Print." A printer window prompt will appear on the screen. As long as you don't need to make any paper size adjustments you should be good to go. Click "Print" again and the software will send out the information to the printer. Momentarily, the printer will begin.

Make sure you monitor the first sheet that is printed. This way, if there is an error in paper alignment, you have the wrong size stickers inserted, or there is another issue, you can cancel the print job. Sticky label paper is not cheap, so it's best to stop the printing process as early as possible to avoid destroying any other paper you might need.

Conclusion

The beauty of Microsoft Excel is that it is more than just a standard spreadsheet program designed to help organize information. It can also be used to print information, such as labels. If you have a large mailing list and you need to send out information, typing in the mailing list and creating the labels directly in Microsoft Word is time-consuming and often difficult to format. Additionally, you may already have the information typed in Excel anyway. By following these how to print labels from Excel instructions, you'll be able to quickly and easily print any label from Excel, regardless of the information or how many labels you need.

Featured Image: Image by Pexels from Pixabay