ADVANCED EXCEL

AdvancedExcelMA21.pptx

Home >Computer Science homework help >ADVANCED EXCEL

Applied Statistics

Advanced Excel

The purpose of this module is to either provide you with new Excel skills or to refresh your memory of these skills. In particular, we will focus on sorting, filtering, logic statements, pivot tables, scenario manager, and a few “tricks of the trade”– all of which can save you a huge amount of time and reduce the chance for human error. Pivot tables can significantly reduce the memory requirements for your file, also. You will need this material for many of the case studies.

Please download the file “Basic Data Set” from Canvas. This is data from a small hypothetical company. We will used this basic data set throughout the module. It is a very small file. Although most of the examples and exercises in this module using this data set could be answered by just counting people or some other simple brute force method, keep in the back of your mind that you may be analyzing Excel files of perhaps 80,000 rows and 25 columns. So these techniques can be applied to these huge Excel files. That is the main goal of this module – to help you save a huge amount of time and reduce the chance for human error when analyzing huge Excel files.

Advanced Excel

THE BASIC DATA SET

Here are the top rows of the basic data set. There are 26 rows and 9 columns of data in the complete file.

Advanced Excel

Place yourself in the position of a business analyst or some other kind of quantitative analyst for your organization. You are asked to produce a report with all nine columns which just shows the males for the organization. One way to do this is to sort all of the rows by gender (in this case, the field name is “Title” with males being “Mr.” and females being “Ms.”). There can be several ways of doing this, but here is a simple way.

Highlight the entire data set.

Click on Data/Sort. Here is the dialog box that should come up. Change the settings as appropriate.

SORTING

Advanced Excel

Apply or click on the following settings.

On the top right, click on “My data has headers.” This will ensure that the field/column names do not get sorted along with the data.

On the “Sort by” drop down, click the drop down arrow and select “Title.” This will separate all of the Mr. and Ms. Employees.

Advanced Excel

For the “Sort on” dropdown, click the arrow and select “Values”.

For the “Order” dropdown, click the arrow and select “A to Z.”

Click on “OK.”

Advanced Excel

Your spreadsheet should now look something like below. The important thing is to notice that all of the male employees are sorted to the top of the spreadsheet, although the order of their names might be different than below.

To produce the report, you can simply delete all of the female rows and what remains is the requirement, which can be printed out.

Advanced Excel

You may want to list the male employees in alphabetical order by last name.

Click on Data/Sort.

Keep the same settings as before, except click on “Add level” on the top left of the dialog box. The second sorting row will appear as below. In the “Then by” drop down, select “Name.”

In the second row, click on “Sort on” and choose “Values” and click on “Order” and choose “A to Z.”

Advanced Excel

Here is what the spreadsheet should look like after this second level of sort and with the females removed from the data set.

You are free to add many more levels of sorting as you see fit using this same procedure for each new sort level.

NOTE: ensure that all of the data is highlighted and headers are also highlighted before sorting. We want to make sure that all of the data stays corresponding with the names, etc.

Advanced Excel

Review: produce a report sorted by job series (from A to Z) and then sorted by salary level (largest to smallest), then sorted by last name (from A to Z). The answer is on the next page.

Advanced Excel

FILTERING

There is another way to produce a report showing only males in the organization. This is through a process called “filtering.” Sorting is sometimes the best way to support generation of reports, and filtering is sometimes the best way.

Here is a way to produce a report using filtering to show only the males in the organization.

1. Click on Data/Filter. The screen will change to look as below. Notice the new drop down arrows at the top of each column.

Advanced Excel

After the new drop down arrows are shown, click on the drop down arrow for “Title.” The drop down dialog box will come up, with (at the bottom of the dialog box) “Select All,” “Mr.,” and “Ms.” being displayed. To show only males, click off the “Select All” and “Ms.” so that the only check mark is by the “Mr.”

Although the names may be in a different order below than yours, the important point is that only “Mr.” are shown on the report.

Advanced Excel

Several layers of filtering are possible. For example, filter showing only the females from New York.

Click on Data/Filter to clear the current filtering.

Click on Data/Filter to start the new filtering.

From the “Title” drop down, click so that only “Ms.” is shown in the dialog box.

From the “Home State” drop down, click off all states other than New York.

The report is below, although the order of employees may be different than yours.

Advanced Excel

Review: filter the data set to show all employees from either New York or Virginia, who are female, and in the organization’s software development area.

Advanced Excel

LOGIC STATEMENTS

Below is a report showing all employees from New York. This was produced using filtering.

There is another way to generate a report showing all employees from New York, which can be very powerful in certain circumstances. It is by using a logic formula.

Advanced Excel

Here is the top part of the basic data set, sorted by “Name.” In cell J2, put in this formula.

=IF(G2="New York",1,"")

Read this formula as “if the cell value in G2 is “New York,” then place a “1” in cell J2, else place a blank in J2. We are looking here for certain character strings, which are always placed between quotation marks. “New York” is a character string. The “” at then end means “place nothing in the cell J2.”

Advanced Excel

Here is what the first row of the report looks like. Note that since employee is from New York, a “1” appears in cell J2.

Next, copy and paste the J2 formula down all of the cells in column J corresponding to the basic data set. The top of the spreadsheet now looks like this.

Advanced Excel

Now put a label in Column J’s header, and filter by “1” in column J. Here is the top of the report, although names may be in a different order.

“Trick of the trade.” Many people do a standard copy/paste or a drag procedure to copy the formula from J2 down to J27. That works. However, picture a situation where that formula needs to be copied down, say, 80,000 rows. That can take time and is open to human error. Instead, click on J2 and place the mouse cursor over the lower right cornet of J2. The cursor should become a “+” sign. While the “+” is showing, double left click the mouse. The formula will automatically copy down the entire column as long as the cell in column I has a value in it. See the next slide.

Advanced Excel

Notice that there is no “Rank” value in Ms. Golf’s cell. The automatic copying of cell J2 stops at Ms. Foxtrot’s row. Manually, the formula I J2 had to be recopied/pasted into Ms. Hotel’s cell in column J and then copied down the rest of the spreadsheet. So do the automat copying, go to the end of the spreadsheet to make sure that the copying was successful, and if not, then find the error and correct it. Here is the top of the report, although names may be in a different order.

Advanced Excel

Produce a report showing all company members who are from either Delaware or Virginia. Place a “1” in column J for all those who fit the criteria.

Here is the formula to use. Note that this is an “OR” statement. First click on the cell J2.

=IF(OR(G2="Delaware",G2 = "Virginia"), 1,"")

This formula is read as “if G2 = “Delaware” or if G2 = “Virginia”, then place a “1” into the cell J2, else place nothing into J2. Here is the top of the report, although names may be in a different order.

Advanced Excel

Produce a report showing all company members who are both from Virginia and have a Bachelor’s degree. Place a “1” in column J for all those who fit the criteria.

Here is the formula to use. Note that this is an “AND” statement. First click on the cell J2.

=IF(AND (G2=“Virginia",F2 = “Bachelor’s"), 1,"")

This formula is read as “if G2 = “Virginia” and if F2 = “Bachelor’s”, then place a “1” into the cell J2, else place nothing into J2. Here is the top of the report, although names may be in a different order.

Advanced Excel

Produce a report showing all company members who are either both from Virginia and have a Bachelor’s degree, or have a High School degree Place a “1” in column J for all those who fit the criteria.

Here is the formula to use. Note that this is an “AND” statement. First click on the cell J2.

=IF(OR(AND(G2="Virginia", F2="Bachelor's"),(F2 = "High School")),1,"")

This formula is read as “if G2 = “Virginia” and if F2 = “Bachelor’s”, or if F2 = “High School,” then place a “1” into the cell J2, else place nothing into J2. Here is the top of the report, although names may be in a different order.

Advanced Excel

REVIEW

Write the formula corresponding to –

If the value in cell A3 is greater than 5, then place “OK” in cell J2, else place “Error” onto J2.

Click on cell J2. Enter the following formula in it.

=if(A3>5,”OK”,”Error”)

Advanced Excel

Write the formula corresponding to –

If the value in cell A3 is either than greater than 5 or less than 0, then place “OK” in cell J2, else place “Error” onto J2.

Click on cell J2. Enter the following formula in it.

=IF(OR(A3>5, A3<0),”OK”, “Error”)

Advanced Excel

Review: write the formula corresponding to –

If the value in cell E2 is greater than 50, and the person is male, then place “OK” in cell J2, else place “Error” onto J2.

Click on cell J2. Enter the following formula in it.

=IF(AND(E2>50,B2 = “Mr.”),”OK”, “Error”)

Advanced Excel

Write the formula corresponding to –

If the value in cell E2 is greater than 50 and the person is from New York, or if the person’s Rank is Analyst, then place “OK” in cell J2, else place “Error” into J2.

Click on cell J2. Enter the following formula in it.

=IF(OR(AND(E2>50,G2="New York"),(I2 = "Analyst")),"OK","Error")

Advanced Excel Including Pivot Tables

ANCHORING

Please open the file of the Basic Data Set.

Suppose that we want to experiment with giving everyone in the company a percentage salary increase. One way to do this is to add two column headers – “New” Salary and Salary Increase. We want to see how the new salaries change with the proposed increases. Cell L2 shows an increase of 5%. Cell K2 is the “new” salary, a possible formula being =+L2*E2.

Advanced Excel Including Pivot Tables

There may be several ways to finish the calculations, but let’s try this one. COPY and PASTE the formula in K2 down the column to K27. Below shows the result.

What happened??? All new salaries below K2 are now $0. The formula for K2, copied down, says to multiple the value to the right of the K column cell by the corresponding cell in column E. Any cell below L2 basically carries a value of 0 for this example.

Advanced Excel Including Pivot Tables

What is really needed is to, instead of the K column formulas referring to the cell to their right in L, to refer only to the cell L2. This may make it easy in huge spreadsheets to change a value in only one cell which will affect one or more long columns.

Instead of using the formula =+L2*E2, change it to =+$L$2*E2. What this does, when copying and pasting the formula down column K, to refer not to the cell at the left of the K cell, but only to cell L2. The first part, $L, means “only refer to column L values.” The second part, $2, means “only refer to row 2 values.” Therefore, only the cell L2 will be referred to. This method has the advantage that only the value in cell Ls needs to change if one wants to experiment with different values.

Advanced Excel Including Pivot Tables

This procedure is called “anchoring” -- in this case the cell L2 is anchored in the copying and pasting.

It is also possible to just anchor a row, such as =+L$2*E2, or just anchor a column, such as =+$L2*E2 – depending on the situation.

Anchoring can be extremely helpful in certain situations, so it is certainly recommended when possible.

Advanced Excel

PIVOT TABLES

There are times when it is necessary to produce reports from a large Excel file in the format of a table. For example, build a table from the basic data set with the rows being gender, and the columns being job series. The cells in the table represent the count of employees by gender in each job series. Such a table might be as below. From the table, we see that both the CEO and CFO are male, and the CIO is female.

Advanced Excel

Although building such a table for this company and its 26 people should be relatively easy, go back and imagine building a table from a data set of 80,000 rows and 25 columns. Such a task can be undaunting.

Excel has a feature which builds such a table in a user-friendly manner. This is called the “pivot table” feature. This feature can save huge amounts of time and significantly reduce the chance for human error.

Please return to the basic data set. Start by sketching how the final table will look. One such sketch is below. Note that the table has four sections: a blank, a gender, a Job Series, and the Counts.

Table rows

Table columns

Table cells

Advanced Excel

Now go into the Basic Data Set and build such a table semi-automatically.

Bring up the Basic Data Set.

Click on Insert from the ribbon.

Click anywhere inside the Basic Data Set.

Click on the upper left corner of the ribbon “PivotTable.”

A dialog box as below will appear.

Advanced Excel

Notice that “Select a table or range” is automatically entered. This is the entire data set.

Now tell Excel where you want the completed table to appear. I suggest for this exercise clicking on “Existing Worksheet” and cell K7.

Advanced Excel

This is what the dialog box looks like at this stage.

If your dialog box is the same as this one, click on “OK.” The Excel screen will change with the blank table on the right.

Advanced Excel

Note that the bottom part of this dialog box has four sections. These correspond to the four sections of the sketch from slide 29.

This is called the “field list” dialog box.

Advanced Excel

Reproduce the sketch here. For example, first go to “Title” on the top half. Left click on “Title” and drag it down to the “Row Labels” section. Your table should look as it is here.

Then left click on “Job Series” on the top half, and then drag it down to “Column Labels.” Finally. Left click on “Title” on the top half and drag it down to “Values.”

Advanced Excel

Here is what the dialog box should look like now.

If yours matches this, then on the Excel spreadsheet, slide over to K7 area. The pivot table has been built and all calculations are finished. See next slide.

Advanced Excel

This is what might be called a “raw table.” It needs some formatting to make it presentable, but all of the heavy lifting has been done.

Foe example, it might be easier to read if all of the columns are centered. Highlight the entire table, and click on the centering button on the ribbon as you would center any columns.

Advanced Excel

Notice that there are two filtering arrows. Click on the filter for Row Labels. This allows changing the table to show the results of the filtering. For example, suppose we wanted to show only females in the company. Filter by “Ms.” and the table will automatically change. On the column labels, filter by “Software Development.” The result is a count of all of the females in the Software Development area.

Advanced Excel

There are other format changes that can be made. Go back to the pivot table, go to each filter, and then include all items (“Select All”) for each filter.

Note that the column labels are in alphabetical order. Suppose the table needed to be completed with the CEO, CFO, and CIO occupying the first three columns. We need to move each of those one column to the left.

Advanced Excel

Go to the CEO cell and left click. Then right click. A drop down will appear. Find “Move.” Left click on “Move,” then left click on “Move CEO Left.” This is what the pivot table now looks like.

Advanced Excel

Go to the CFO cell and move it to the left. Then go to the CIO cell and move it to the left. This is what the pivot table now looks like.

Advanced Excel

Review. Build a pivot table, with centered columns, showing “Home State” in the rows and “Title” in the columns. Have the cells of the table show the count of employees by Home State.

Advanced Excel

So far the exercises have asked for “counts” of employees. It is also possible to do other mathematical calculations such as sums, (such as sums of money), averages, etc.

Recall the field list dialog box from slide 35. Go to “Count of Title” in the bottom right section and left click the down arrow. In the pop up box, look at the last entry “Value Field Settings.” Left click that, and you will see several math calculations which are possible.

Advanced Excel

Produce a report of all employees, by gender, and the total salaries paid to that gender (do not account for the fact that there are more females than males – just the total salaries).

After completing the field list dialog box, and clicking on the Value Field Settings and clicking on “Sum” the field list dialog box should look like this.

Advanced Excel

The pivot table should look like this.

If an abbreviated form of the table is wanted, giving just the grand totals, build the field list dialog box, but do not put anything in the “Column Labels” section.

Advanced Excel

Review. Build a pivot table having the Job Series in the rows, and total salary paid in the columns.

Advanced Excel

The last section of the table is the upper left section, Report Filter.”

Suppose the requirement included the need to filter by gender. Drag “Title” into the upper left section.

The pivot table report will change in format as on the next slide.

Advanced Excel

Notice the top line “Title” and “(All)” above the report. From the down arrow one can filter for gender.

Suppose we needed to filter by female. Click the down arrow from the top line and select “Ms.”

Notice that the values in the “Sum of Salary in $1000” have changed to reflect only female salaries.

Advanced Excel

Review. From the Basic Data Set, build a pivot table showing the employee degrees in the rows, and the average salary per degree in the columns, with the cells providing the total average per degree type. Arrange the report so that “Master’s” is in the first row, “Bachelor’s” is in the second row, and “High School” is in the third row.

Advanced Excel

Review. From the Basic Data Set, build a pivot table showing gender in the rows, “Rank” in the columns, and average salary in the cells. Put in a filter and filter by “Degree.” Show in the report only Master’s degree employees.

Advanced Excel

EXCEL’S “SCENARIO MANAGER”

The purpose of this set of slides is to introduce you to Excel’s Scenario Manager feature. You will be needing this material for several of the case studies, and it would be good to be familiar with it for work.

Advanced Excel

A company is forecasting sales and gross income. It is looking at the possibility of three monthly sales levels forecasts.

Optimistic: sales of 3500 items sold.

Realistic: sales of 3000 items sold.

Pessimistic: sales of 2500 items sold.

The company is also considering increasing its staffing by one person. The company is currently paying $12,000 per month in salaries, and adding another person will increase the salary cost to $15,000.

There are therefore six possible scenarios for which the company would like to calculate gross income:

Optimistic, $12,000 per month Optimistic, $15,000 per month

Realistic, $12,000 per month Realistic, $15,000 per month

Pessimistic, $12,000 per month Pessimistic, $15,000 per month

Advanced Excel

With Excel , one can produce the following table very quickly showing the results of the six scenario options.

Advanced Excel

Open Excel, and enter in the following table exactly as shown.

Advanced Excel

Now perform the following click sequence:

Data/What-If Analysis/Scenario Manager.

The Scenario Manager dialog box should appear and be basically blank.

Advanced Excel

Start to populate this dialog box. Start by clicking “Add.” This next box will appear. Populate the “Scenario name:” with the first scenario, “Optimistic, Current Employee Level” and in the “Changing Cells” block click on the cells in green on the spreadsheet, G6 and G14 (representing the optimistic option and current employee salary level). Click “OK.”

Advanced Excel

Enter in the value for the optimistic, which Excel will automatically calculate in G6, as 3500; this is from spreadsheet cell C6. Enter in the salary value for current employee level, which is in G14, as 12000.

Since there are five more scenarios to go, click on “Add.”

Advanced Excel

For the next scenario, the left figure below shows the dialog box as it first appears. Type in to the “Scenario name:” Optimistic, Hire New Employee” and then click on “OK.”

Advanced Excel

For the next scenario, type into the “Scenario name:” Optimistic, Hire New Employee” and then click on “OK.” When this dialog box appears, change the value for G6 to 3500, which is the optimistic sales value (see spreadsheet cell C7). Change the value for G14 to 15,000. Then again click on “Add” for the next scenario.

Advanced Excel

For the next scenario, the left figure below shows the dialog box as it first appears. Type in to the “Scenario name:” Realistic, Current Employee Level” and then click on “OK.”

Advanced Excel

When this dialog box appears, change the value for G6 to 3000, which is the realistic sales value (see spreadsheet cell C7), and put in 12,000 for G14 (current employee salary level). Then again click on “OK” for the next scenario

Advanced Excel

Continue on until each of the six scenarios is populated. After the last one is populated, then click on “OK.” The Scenario Manager dialog box should appear as this does (except for the location of the blue highlighted area). If so, then click on “Summary.” If not, “Edit” as necessary.

Advanced Excel

After clicking on “Summary,” ensure that the “Result cells:” block has H22 in it. This is the result cell of the calculations in the spreadsheet when each option is entered into the spreadsheet. Then click on “OK.”

dvanced Excel

Excel now builds the Scenario Summary table, part of which is shown below.

If you are going to use this as a presentation graphic, you may want to edit it. I put in the names of the cells in the “Changing Cells:” and “Result Cells” blocks, centered the columns, and decreased the width of the columns while using the Excel ribbon’s “Home/Wrap Text” feature. One editing of the final table is on the next slide, although there are many ways to edit should you so choose.

Advanced Excel

This table can now be copied and pasted into an Excel graphic or a PowerPoint graphic (or a Word file).

Advanced Excel

Ensure that you understand the review questions and can answer conceptually identical questions for the Quiz You will be using this material for many of the case studies.