How to identify circular reference in excel 2010

Example For Add Data to Your Spreadsheet

Add Data

In Section 1 on the Data page, complete each column of the spreadsheet to arrive at the desired calculations. Use Excel formulas to demonstrate that you can perform the calculations in Excel. Remember, a cell address is the combination of a column and a row. For example, C11 refers to Column C, Row 11 in a spreadsheet.

Reminder: Occasionally in Excel, you will create an unintentional circular reference. This means that within a formula in a cell, you directly or indirectly referred to (back to) the cell. For example, while entering a formula in A3, you enter =A1+A2+A3. This is not correct and will result in an error. Excel allows you to remove or allow these references.

Hint: Another helpful feature in Excel is Paste Special. Mastering this feature allows you to copy and paste all elements of a cell, or just select elements like the formula, the value or the formatting.

"Names" are a way to define cells and ranges in your spreadsheet and can be used in formulas. For review and refresh, see the resources for Create Complex Formulas and Work with Functions.

Ready to Begin?

1. To calculate hourly rate, you will use the annual hourly rate already computed in Excel, which is 2080. This is the number most often used in annual salary calculations based on full time, 40 hours per week, 52 weeks per year. In E11 (or the first cell in the Hrly Rate column), create a formula that calculates the hourly rate for each employee, by referencing the employee’s salary in Column D, divided by the value of annual hours, 2080. To do this, you will create a simple formula: =D11/2080 Complete the calculations for the remainder of Column E. If you don’t want to do this cell by cell, you can create a new formula that will let you use that same formula all the way to the end of the column. It would look like this: =$D$11:$D$382/2080

2. In Column F, calculate the number of years worked for each employee by creating a formula that incorporates the date in cell F9 and demonstrates your understanding of relative and absolute cells in Excel. For this, you will need a formula that can compute absolute values to determine years of service. You could do this longhand, but it would take you a long time. So, try the YEARFRAC formula, which computes the number of years (and even rounds for you). Once you start the formula in Excel, the element will appear to guide you. You need to know the “ending” date (F9) and the hiring date (B11). The formula looks like this: =YEARFRAC($F$9,B11) and the $ will repeat the formula calculation down the column as before if you grab the edge of the cell and drag it to the bottom of the column.

3. To determine if an employee is vested or not In Column I, use an IF statement to flag with a "Yes" any employees who have been employed 10 years or more. Here is how an IF statement works: =IF(X is greater (or less than) Y, “Answer”, IF not, “Answer”). To create this as a formula it would look like this: =IF(F11>=10,"Yes","No") You can drag this formula down the column or highlight the starting cell, hold down the shift key, and zip down to cell 382 and release and the whole column should compute properly.

4. Using the VLookup function, use the Region Key located at F417:G420 to fill in the cells in Column N to identify the region in which the employee is located based on the state listed in Column M. (If this function is new to you – hang in there – this one is worth it

Using the VLookup function, use the Region Key located at F417:G420 to fill in the cells in Column N to identify the region in which the employee is located based on the state listed in Column M. (If this function is new to you – hang in there – this one is worth it

There are some video resources available that address some common "hard spots" in this Excel assignment. Do not be confused if you see a data set that is different than yours - the principles are the same! Remember, if you have any questions, ask.

Excel sheet with region column populated

Snip is used by courtesy of Microsoft.

You will devise a formula that will match the state to a region (in position 2). We will use the $ function to enable a repeat of the formula down the column. =VLOOKUP(M11,$F$417:$G$420,2,FALSE)

To view videos that explain these formulas, please refer back to Step 1 under the link entitled Access Tutoring Help and Other Resources. The videos were created for another class but pertain to this same data set.

Remember: if you have any questions, ask!

Resources

· Paste Special Options in Excel

· Remove or Allow a Circular Reference

· Data Presentations

· Excel Tutorials: Create Complex Formulas

· Excel Tutorials: Define a Name

· Excel Tutorials: Relative and Absolute Cells in Excel

· Excel Tutorials: Use an IF Statement

· Excel Tutorials: Using the Function VLookup

· Interpreting Statistics

Step 4: Use Functions to Summarize the Data

Summarize the Data

You are now ready to move into Section 2 to prepare the data for future analysis, to include some simple statistical analyses and charts and graphs to present the data. To start, begin by presenting categories of data in summary tables and counting them, totaling them, and calculating percentages. This basic analysis helps you begin to describe patterns in the data and starts to form the story of the workforce.

Complete each table in Section 2. Use the Countif Function to count each item in each table. Use the Sum Function to total the tables when required. Calculate percentages for each table as required. Format cells appropriately. Remember to make smart use of reference cells in formulas (avoid typing in numbers or text into formulas – point to other cells) and use mixed and fixed cell references to make copying formulas easier/faster. Your supervisor will look for this!

Example summary tables for graphing.

Used with permission from Microsoft.

Please consult the Resources section below for more information and links regarding this topic.

Resources

· Countif Function

Using the COUNTIF Function

· Step 1

The COUNTIF function allows the student to tabulate the instances (or frequency) of a specified value (character or numeric) within a range of cells in Excel. To begin, you select a cell and type:

COUNTIF function showing the 'range' and 'criteria.'

· Step 2

After entering the initial text, it will ask you to select your range and criteria. The range represents your highlighted selection of cells that you are tabulating a specified value. To select your range, you can highlight the cells. When you do that, you’ll see how the function automatically inserts the cell range into your formula (below). When you do that, you can insert a comma (,) after the range (as shown by the circle) and then move to the criteria portion of the formula.

COUNTIF function applied showing where to insert comma after the range is selected.

· Step 3

Your criteria is the investigate component of the formula. So if the formula is asking for cell values less than 2, it will tabulate all selected cells in your range that are less than 2. In the formula, it would resemble the following: =COUNTIF(A4:A12, <2). Note: In terms of tabulating the frequency of text (such as region), it’s important to note the need to use “ “ to make the variable character (below). When you entered your criteria and are ready to tabulate, you can close your formula by entering a close parenthesis --- ).

· COUNTIF function applied showing the selection criteria as 'north.'

· Note: If you are dealing with numeric data, the “ “ are not needed before and after the text of your key variable.

COUNTIF function applied showing the range and selected criteria specified.

· Sum Function

https://edu.gcfglobal.org/en/excel2010/working-with-basic-functions/1/

Step 5: Analyze the Workforce

Descriptive or Summary Statistics

In this section, you will expand your analysis by employing descriptive statistics or summary statistics to further describe characteristics of the workforce. Your summary table described "how many" and also offered proportions in relation to the entire workforce, but through this analysis, you can describe much more for the following variables: salary, hourly rate, years of service, education, and age.

You will be calculating:

mean, median, mode – average, middle, and most frequent data points in a set.

standard deviation – how spread out individual data points are from the mean. If data points vary greatly, the result is a higher standard deviation and vice versa. It is referred to as a "measure of dispersion" calculated as the square root of the variance. (Note: when calculating manually in Excel, you might see a slight difference between the result you get manually and the result you get using the Analysis Toolpak, depending on whether your version of Excel requires you to use the function DSTDEV or STDEV.S).

variance – the average squared distance between the mean and each data value. It is also a measure of dispersion, which is used to calculate the standard deviation. It is always nonnegative because the calculated squares are positive or zero. A small variance means that the data points are very close to the mean and to each other; thus, a high variance tells us that the data points are very spread out from the mean and from each other.

range – the difference between the highest (MAX) and lowest (MIN) values in a data set.

skewness and kurtosis – numerical measures to describe the shape of a data set and how close it is to a normal distribution. Skewness describes how symmetrical the data is around the mean. Kurtosis describes height and sharpness of the central peak (its peakedness or flatness) in comparison to a normal curve.

Resources

· Skewness and Kurtosis

Skewness and Kurtosis
Shapes of Distributions
We saw distributions in Distributions that shapes of distributions can di_er in skew and/or kurtosis.

This part presents numerical indexes of these two measures of shape.

1. Skew
The figure below shows a distribution with a very large positive skew. Recall that distributions with positive skew have tails that extend to the right.

A distribution with a very large positive skew.

A distribution with a very large positive skew. This histogram shows the salaries of major league baseball players (in thousands of dollars).

Distributions with positive skew have larger means than medians. The mean and median of the baseball salaries shown in the figure above are $1; 183; 417 are and $500; 000 respectively. Thus, for this highly-skewed distribution, the mean is more than twice as high as the median. The relationship between skew and the relative size of the mean and median lead the statisticial Pearson to propose the following simple and conventient numerical index of skew:

3(Mean−Median)σ3(Mean−Median)σ

The standard deviation of the baseball salaries is 1; 390; 922. Therefore, Pearson's measure of skew for this distribution is 3x(1,183,417−500,000)1,390,922=1.473x(1,183,417−500,000)1,390,922=1.47.

Just as there are several measures of central tendency, there is more than one measure of skew. Although Pearson's measure is a good one, the following measure is more commonly used. It is sometimes referred to as the third moment about the mean.

∑(X−μ)3σ3∑(X−μ)3σ3

2. Kurtosis
The following measure of kurtosis is similar to the denition of skew. The value "3" is subtracted to define "no kurtosis" as the kurtosis of a normal distribution. Otherwise, a normal distribution would have a kurtosis of 3.

∑(X−μ)4σ4−3∑(X−μ)4σ4−3

Glossary
Definition 1: Skew

A distribution is skewed if one tail extends out further than the other. A distribution has positive skew (is skewed to the right) if the tail to the right is longer. See figure above for an example. A distribution has a negative skew (is skewed to the left) if the tail to the left is longer. See figure below for an example.

Kurtosis graph

Kurtosis measures how fat or thin the tails of a distribution are relative to a normal distribution.

It is commonly defined as:

∑(X−μ)4σ4−3∑(X−μ)4σ4−3

Licenses and Attributions
Shapes of Distributions by David Lane from OpenStaxCNX is available under a Creative Commons Attribution 1.0 Generic license. © July 7, 2003, David Lane. UMGC has modified this work and it is available under the original license. Download for free at http://cnx.org/contents/4b365746-ef07-424f-b3cc-e5aa7e74df5c@4.

· Descriptive Statistics in Excel

Descriptive Statistics in Excel
How to Get Descriptive Statistics in Excel
Problem:
You are analyzing a dataset and before modeling/analyzing you need to generate descriptive statistics on a field. You have the data loaded in Excel and wonder if there's a way to do that in Excel.

Solution:
There's an out-of-the-box solution that will support your needs to generate descriptive statistics on a field. Here are the steps:

Note: for the purpose of this blog post, I am using Excel 2013 but the Data Analysis ToolPak is available in Excel 2007+.

1. Activate "Data Analysis" ToolPak.

Follow these steps: File > Options > Add-ins > Manage: Excel Add-ins > "GO"

Description: excel data analysis toolpak (add-ins stage). File > Options > Add-ins > Manage: Excel Addins > “GO”

2. Make sure to check the "Analysis ToolPak" checkbox.

3. Now you should see a "data analysis" option under the "Data" pane:

Description: Excel Data Analysis Descriptive Statistics. “data analysis” option under the “Data” pane.

4. Now click on "Data Analysis" and select one of the following options:

Anova, Correlation, Covariance, Descriptive Statistics, Exponential Smoothing, F-Test Two-Sample for Variances, Fourier Analysis, Histogram, Moving Average, Random Number Generation, Rank and Percentile, Regression, Sampling, t-Test, z-Test.

In this case, let's go with Descriptive Statistics, but you can see that you can perform other tasks as well.

5. Once you click on the Descriptive Statistics, a dialog box will show up and you will have to enter some data like your input range to generate descriptive statistics. Once you have filled in the data needed, click on OK and it should generate descriptive statistics for you in EXCEL!

· Calculating Range

Calculating Range

Step 1

Calculating range involves a few functions in Excel. It allows you to gain a sense of the distribution of the data. That is, you’ll be able to tell the largest and smallest value within the data. First, you need to use the minimum function (=MIN) to obtain the minimum value for the dataset. After entering “=MIN”, you’ll then be prompted to highlight your data (below):

Column of values showing calculation of minimum value.

Step 2

To obtain the maximum value of our data, we need to use the maximum function (=MAX). After entering “=MAX”, you’ll then be prompted to highlight your data (below):

Column of values showing calculation of maximum value.

This will give you the maximum value of the data (which is “55” in our visual example). So the range of the data in our visual example is 1 – 55.

· Standard Deviation

· Standard Deviation

· Method 1

· Calculating the Mean

· Image titled Calculate Mean and Standard Deviation With Excel 2007 Step 1

· 1. Use the "AVERAGE" function in Excel to find the mean of a set of numbers. Enter the range of numbers in your Excel spreadsheet. Click where you want the mean (Average).

· Image titled Calculate Mean and Standard Deviation With Excel 2007 Step 2

· 2. Click "Formulas" and select the "Insert Function" tab. Enter the numbers in your Excel spreadsheet in either a row or a column.

· Image titled Calculate Mean and Standard Deviation With Excel 2007 Step 3

· 3. Scroll down and select the "Average" function.

· Image titled Calculate Mean and Standard Deviation With Excel 2007 Step 4

· 4. Enter the cell range for your list of numbers in the number 1 box, for instance D4:D13 and click "OK"

· Image titled Calculate Mean and Standard Deviation With Excel 2007 Step 5

· 5. The mean (average) for the list will appear in the cell you selected.

· Method 2

· Calculating the Standard Deviation

· Image titled Calculate Mean and Standard Deviation With Excel 2007 Step 6

· 1. Use the STDEV function to compute the standard deviation. Place your cursor where you wish to have it appear.

· Image titled Calculate Mean and Standard Deviation With Excel 2007 Step 7

· 2. Click on "Formulas" and select the "Insert Function" (fx) tab again.

· Image titled Calculate Mean and Standard Deviation With Excel 2007 Step 8

· 3. Scroll down the dialog box and select the STDEV function.

· Image titled Calculate Mean and Standard Deviation With Excel 2007 Step 9

· 4. Enter the cell range for your list of numbers in the number 1 box and click OK.

· Image titled Calculate Mean and Standard Deviation With Excel 2007 Step 10

The standard deviation will appear in the cell you selected.

Normal Distribution

Normal Distribution
The Normal Distribution
Student Learning Outcomes
By the end of this chapter, the student should be able to:

· recognize the normal probability distribution and apply it appropriately

· recognize the standard normal probability distribution and apply it appropriately

· compare normal probabilities by converting to the standard normal distribution

Introduction
The normal distribution, a continuous distribution, is the most important of all the distributions. It is widely used and even more widely abused. Its graph is bell-shaped. You see the bell curve in almost all disciplines. Some of these include psychology, business, economics, the sciences, nursing, and, of course, mathematics. Some of your instructors may use the normal distribution to help determine your grade. Most IQ scores are normally distributed. Often real estate prices fit a normal distribution. The normal distribution is extremely important but it cannot be applied to everything in the real world.

In this chapter, you will study the normal distribution, the standard normal, and applications associated with them.

Optional Collaborative Classroom Activity
Your instructor will record the heights of both men and women in your class, separately. Draw histograms of your data. Then draw a smooth curve through each histogram. Is each curve somewhat bell-shaped? Do you think that if you had recorded 200 data values for men and 200 for women that the curves would look bell-shaped? Calculate the mean for each data set. Write the means on the x-axis of the appropriate graph below the peak. Shade the approximate area that represents the probability that one randomly chosen male is taller than 72 inches. Shade the approximate area that represents the probability that one randomly chosen female is shorter than 60 inches. If the total area under each curve is one, does either probability appear to be more than 0.5?

The normal distribution has two parameters (two numerical descriptive measures), the mean (μ) and the standard deviation (σ). If X is a quantity to be measured that has a normal distribution with mean (μ) and the standard deviation (σ), we designate this by writing

NORMAL: X ~ N(μ, σ)
Description: Empty normal distribution curve.

The probability density function is a rather complicated function. Do not memorize it. It is not necessary.

f(x)=1σ⋅2⋅π−−−−−√⋅e−12.(x−μσ)2f(x)=1σ⋅2⋅π⋅e−12.(x−μσ)2

The cumulative distribution function is P (X < x). It is calculated either by a calculator or a computer or it is looked up in a table. Technology has made the tables basically obsolete. For that reason, as well as the fact that there are various table formats, we are not including table instructions in this chapter.

The curve is symmetrical about a vertical line drawn through the mean, μ. In theory, the mean is the same as the median since the graph is symmetric about μ. As the notation indicates, the normal distribution depends only on the mean and the standard deviation. Since the area under the curve must equal one, a change in the standard deviation, σ, causes a change in the shape of the curve; the curve becomes fatter or skinnier depending on σ. A change in μ causes the graph to shift to the left or right. This means there are an infinite number of normal probability distributions. One of special interest is called the standard normal distribution.

Glossary
Normal Distribution
A continuous random variable (RV) with pdf f(x)=1σ2π√e−(x−μ)2/2σ2f(x)=1σ2πe−(x−μ)2/2σ2, where μ is the mean of the distribution and σ is the standard deviation. Notation: X ~ N (μ, σ). If μ=0 and σ=1, the RV is called the standard normal distribution.

The Standard Normal Distribution
The standard normal distribution is a normal distribution of standardized values called z-scores. A z-score is measured in units of the standard deviation. For example, if the mean of a normal distribution is 5 and the standard deviation is 2, the value 11 is 3 standard deviations above (or to the right of) the mean. The calculation is as follows:

x = μ + (z)σ = 5 + (3)(2) = 11

The z-score is 3.

The mean for the standard normal distribution is 0 and the standard deviation is 1. The transformation z=x−μσz=x−μσ produces the distribution Z ~ N (0,1). The value x comes from a normal distribution with mean μ and standard deviation σ.

Glossary
Standard Normal Distribution
A continuous random variable (RV) x ~ N (0,1). When X follows the standard normal distribution, it is often noted as Z ~ N (0,1).

z-score

The linear transformation of the form z=x−μσz=x−μσ. If this transformation is applied to any normal distribution x ~ N (µ, σ), the result is the standard normal distribution Z ~ N (0,1). If this transformation is applied to any specific value x of the RV with mean μ and standard deviation σ, the result is called the z-score of x. Z-scores allow us to compare data that are normally distributed but scaled differently.

CALCULATE MEAN, MEDIAN, AND MODE FOR THE CATEGORIES OF DATA AND DERIVE THE DEVIATION, VARIANCE, AND DISPERSION, AND DISTRIBUTION

In this step, you will use Excel formulas to find information about the five variables Salary, Hrly rate, Yrs of Svc, Education, and Age. Remember that Excel recognizes the = sign as the start of a formula and will suggest some formulas. We’ve suggested some formulas to get you going. You can also look for help by selecting Formulas on the toolbar.

Used with permission from Microsoft

Remember that you must tell Excel what you want to do and where the data is. So, to compute the mean of the salary of the personnel in this company, you will create a formula = average (data range). The data for salary starts on D11 (see below) and ends on D382. This is the data range for this column. You will repeat these steps for all columns and functions.

Used with permission from Microsoft

Step 6: Use the Analysis Toolpak

Descriptive Analysis Using the Analysis

Toolpak

The steps you just followed enabled you to calculate descriptive statistics using individual Excel functions. Did you know that you can generate the same descriptive statistics in one easy step?

1. First, make sure you have enabled the data analysis toolpak feature. When you completed that successfully, you will see the words “Data Analysis” or an icon on the top right on the Data functions. Select that and then choose “Descriptive Statistics” from the list.

2. The next step will be to provide the input and output. Since you want to have statistics for all the selected categories, you will provide the location of the data on the spreadsheet in the input box.

o Provide the inclusive cells for the five categories by typing in the field, or capture the columns with your mouse and the field will show in the input range.

o Check the labels box so you have titles for the categories. Then select "New Worksheet Ply." Your output will be now be in a new sheet on your tab.

3. Label your new sheet Summary Stats and format the columns for readability.

4. Compare your calculations using the data analysis feature to the results you obtained in the previous step, when you calculated the results manually with individual functions. You should not have a large disparity.

This tool is handy for quick computations, and you will use it again to create your histogram in Step 7.

Step 7: Create Charts and a Histogram

Work with Data to Create Charts

It is often helpful to view and interpret analytical results when they are presented visually. Graphs and charts help readers digest and interpret information quickly, consistent with the familiar adage "a picture is worth a thousand words." Let’s see what we can see in your data analysis.

Create the following graphs in your workbook on a separate tab named Graphs_Charts:

1. Create separate pie charts that show percentages of employees by (1) gender, (2) education level, and (3) marital status. Explore pie chart formats.

2. Create separate bar charts that show the (1) number of employees by race and (2) the number of employee per state.

3. Create a line graph for the sales summary provided.

4. Create a histogram that shows the number of employees in incremental salary ranges of $10,000. Here, you want to show how many employees are making $0–$20,000; $20,001–$30,000; $30,001–$40,000; and so forth, up to the highest salary range. This involves counting how many employees are in each "salary bucket" to create a frequency distribution table and histogram. Histograms seem hard, but mastering how to visualize the frequency of events is helpful for analysis.

Screenshot of a pie chart, bar chart, and line chart in Microsoft Excel.

Used with permission from Microsoft.

Note: Your Excel spreadsheet template has the upper limit and labels already identified. Complete the table and histogram by engaging the Data Analysis Toolpak. Place the output on a new worksheet and label it Histogram.

Resources

· Working with Excel Charts

https://edu.gcfglobal.org/en/excel2010/working-with-charts/1/

· How to Enable Data Analysis Toolpak

How to Enable Data Analysis Toolpak

PART 1: Installing the Analysis ToolPak Add-In for Excel 2010 and 2013

Make sure that the add-in is installed. Before you can use the Histogram tool in Microsoft Excel, you'll need to make sure that the Analysis ToolPak Add-in is ready to use. [1]

Ensure that the Analysis Toolpak is installed in your add-ins menu.

Navigate to the "Excel Add-ins" dialog box. You can do this from the main Home screen once you've opened the program.

Click Options on the File menu.

Then, click Add-Ins in the navigation pane.

In the Manage list, choose Excel Add-ins. Then, click Go.

Ensure that the Analysis Toolpak is installed in your add-ins menu.

Select the Analysis ToolPak Add-in. Once you're in the Add-Ins dialog box, select the Analysis ToolPak check box under Add-Ins available, if it is not already selected, Then, click OK.

Note that the Analysis ToolPak Add-in will not appear in the Add-Ins dialog box if it is not already installed on your computer. If you do not see Analysis ToolPak in the Add-Ins dialog box, run Microsoft Excel Setup. Add the ToolPak to the list of installed items.

PART 2: Installing the Analysis ToolPak Add-In for Excel 2007

Navigate to the Excel Add-ins dialog box.

Navigate to the "Excel Add-ins" dialog box. This is where you can check whether the Analysis ToolPak is already installed on your computer.

From the Home screen, click the Microsoft Office button. Then, select Excel Options.

Click Add-Ins from the navigation pane.

Pick Excel Add-ins from the Manage list. Then, click Go.

Select the ToolPak. In the Add-Ins dialog box, make sure that the Analysis ToolPak check box under Add-Ins available has been selected.

Select the ToolPak. In the Add-Ins dialog box, make sure that the Analysis ToolPak check box under Add-Ins available has been selected. Then, click OK. This should activate the Analysis ToolPak—and thus the Histogram function—on your computer. [2]

Sources and Citations

[1] https://support.microsoft.com/en-us/kb/214269

[2] http://www.excel-easy.com/examples/histogram.html

Creating the Histogram

1 Enter your data. Organize your data into two adjacent columns on the spreadsheet.

Fill the left-hand column with your input data, and the right-hand column with your bin

numbers.

Input data is the set of numbers that you want to analyze with the Histogram tool. Bin numbers represent the intervals that you want the Histogram tool to use for

measuring and analyzing the input data. For instance, if you want to separate grades into

EXCEL- DATA

2

categories of A, B, C, D, and F, you could makebins for 60, 70, 80, 90, and 100.

2 Open the Data Analysis box. The process is consistent for all versions of Excel released since 2007. If you areusing an earlier version of the software, then you will

need to follow a slightly different process.

In Excel 2013, Excel 2010 and Excel 2007: navigate to the Data tab. Then, click

Data Analysis in the Analysisgroup.

For Excel 2003 and earlier versions: select Data Analysis from the Tools menu. If there is not a Data Analysisoption under the Tools menu, then you may need to install the add-in.

3 Select Histogram. Once you're in the Data Analysis dialog box, find Histogram

listed amid the other analysis tools. Then click OK. This will open the Histogram

dialog box.

4 Select the input range and the bin range. The input range is the range of cells that contain data. If your inputdata is a set of ten numbers, and you have copied it

into the A column (from A1 to A10), then enter your data range as A1:A10. The bin range is the range of cells that contain your bin numbers. If you have four bins at the top ofColumn B, then enter your bin range as B1:B4.

5 Check the chart output box. Under Output Options, click New Workbook. Then select the Chart Output check box.

6 Click OK to finish the job. Excel will generate a new workbook with a histogram table and an embedded chart. The chart should be a column chart that arranges the

data from your histogram table.

Tips

By default the output is placed into a new worksheet. You can keep the output in the same worksheet by selecting theoutput range.

Check out the website to download a simple template to create histograms.Note that a frequency table is also created.

Warnings

Be careful to understand what the bins mean. The bin range should not be confused with the data.

Make sure your histogram makes sense before drawing any conclusion from it.

Sources and Citations

1. https://support.microsoft.com/en-us/kb/214269

2. http://www.excel-easy.com/examples/histogram.html

3. https://support.office.com/en-us/article/Create-a-histogram-B6814E9E-5860-4113-BA51- E3A1B9EE1BBE

4. http://www.math.kent.edu/~honli/teaching/statistics/Chapter2/Excell_Histogram.html

5. https://www.ncsu.edu/labwrite/res/gt/gt-bar-home.html

6. https://support.microsoft.com/en-us/kb/214029`

Set Up Your Spreadsheet

1. Open the spreadsheet.

2. Save as and re-name it:

LASTNAME_Workforce_Composition_Analysis_Semester_Year

3. Format the spreadsheet for Landscape with NarrowMargins.

https://edu.gcfglobal.org/en/excel2013/page -layout/1/

4. Enter your name, date, and page number in the Footer area of the sheet.

5. Format the entire spreadsheet for Calibri 11 font.

6. The gray box at the top of the spreadsheet indicates what cell formatting to use for

each column. Format columns accordingly.

https://edu.gcfglobal.org/en/excel2013/page -layout/1/