Sorting

• If you want to see information about the employees that work for you, is that very easy on this worksheet?
• Right now that is kind of difficult.  We want to sort the data in our table by region so it is easy to see all the North region employees in one group.
• To show students some different options with sorting, first select all the data in a column and click the sort A to Z (or the Z to A) button.  Excel will give you a warning and ask if you want to expand the selection.  If you just click inside the column you want to sort by excel will automatically detect where the rest of your data is.  No warning will pop up.
• Sort by region
• Then by name
• Students usually ask if it is possible to sort by last name.  It is if you split the first and last name.  We can split data very easily using the “Text to Columns” tool under the data tab.  Insert a column to the left of Column B.  Select all the data to be split then select text to columns.  Delimit by a space and click Finish.
• Show how to sort by multiple levels.
• Click the filter button to add a filter.  If all the data in the column is highlighted, the filter will only be applied to that column. If a cell within the table is selected filters will be added to all the columns.
• Filtering is great because instead of just organizing the data to find what you want easily, it takes out everything that is not applicable.  Click on the filter button on the region column.  Uncheck everything except North.  Now you have all your North Employees.  You can also sort from the filter drop down.
• Show advanced filtering options.
• Show subtotal options (if you formatted the data as a table, you must convert it back to a range).

Pivot Tables

Solution Based Training: You are the owner of an online DVD business.  This is a record of all your DVDs their ranking, number of votes, Rating, Sales, etc.  Your job is to get useful information out of this worksheet.  Ask students if they can tell you how many DVDs were sold in the first 6 months of the year by looking at the worksheet.  When they can’t do it you can very easily introduce Pivot Tables.

After showing them how pivot tables work, give them several scenarios to figure out on their own.

For additional practice download the pivot table – manufacturer activity file. You can find it under extras > Excel data analysis extras > pivot table – manufacturer.xlsx

Go to the “Database Functions” Sheet

Excel Sporting Goods Spreadsheet. We have a large database and have set up this criteria box.  We want to enter a database function to be able to, for example, enter state UT, manufacturer name NIKE, product type HAT, search and sum the sales amount.

*Ask about function experience of students*

We want to have the total of sales amount, so we’ll want to use a SUM function. The DSUM function:

• Click in “Sales Amount” cell
• FUNCTION BUILDER:

1. FORMULAS > FUNCTION LIBRARY > INSERT FUNCTION

• Search for DSUM, click OK

1. Database: range of cells that makes up the list or database

–          Select Entire table including headings!!

1. Field: either the label of the column in double quotation marks or a number that represents the column’s position in the list

–          Select “Sales Amount” column. Either reference cell K2, or type “Sales Amount”, or column number 11

1. Criteria: the range of cells that contains the conditions you specify. The range includes a column label and one cell below the label for a condition.

–          This is the Search Bar of criteria you have created.

–          Select M2:P3

• Now as you change the Criteria, it changes the cells amount.

1. Changing State, Manufacturer Name, Product type, etc.
2. It’s almost like a filtering function… very useful for Databases.

** NOTE: remember if you are going to continually be adding data, you will need to correct your function to contain the range of data in the database ***

Change Sales amount function to be DAVERAGE instead of DSUM

Change the function to DCOUNT. “D” stands for Database.

Regular SUM, AVG, and COUNT Functions cannot filter the data and simultaneously SUM, AVG, or COUNT.

Goal Seek

Goal Seek allows you to set what you want the result to be. For example if you want to make 1,000,000 dollars net income, we can set a different value to change in order to meet our goal.  Excel tells you what you need to do to accomplish your goals.

Another example is calculating a final grade.  You have a 90% in a class right now but you need a 94% to get an A.  What grade do you need to get on the final to get an A?

Set Cell – The cell you want the change to happen to

To Value – The value you want Set Cell to be

By Changing Cell – This is the cell you want excel to change to tell you how to accomplish your goal.

Scenario Manager

The Scenario Manager is a tool that allows you to answer some what-if questions.  Using the scenario manager you can obtain the results of different scenarios.  While it is similar to Goal Seek and the Solver, the advantage is that you can easily see the results for all the different scenarios at once and it will be generated automatically.  For our activity we will find the best, worst and most likely scenario for our sales. You can have as many Scenarios as you would like and they can be saved for later.

Solver

The solver tool is all about optimization.  We tell the solver the constraints and it will give us the optimal solution.  In our example, our goal is to get as many people to see our add as possible by staying within our budget and some other contraints.  When we put all those constraints into the solver it will tell us how many ads to place in which magazines.

Solver

• DATA > ANALYSIS > SOLVER

It should be blank, but if it is not, click “Reset All”

Once you use Solver, it saves your work.

• Set Objective: What is it? What are we trying to get? 800 million views.

Reference the cell by clicking on cell (G9)

• To: We have the option to maximize or minimize. Based on our budget we want to maximize our budget to get the most views possible.

Click on Max

• By Changing Variable Cells: What cells will we be changing to get our 800 million views? What do we have control over? Number of Ads placed.

Select the whole column of “Number of Ads Placed” (D3:D8)

• Subject to the Constraints: here we add our constraints:

Click Add: What will one of our constraints be?

–          Cell Reference: Total Magazine 3 + Magazine 4 (E10) <= Constraint: (G13)

Click Add to add more constraints: What is another constraint?

–          Cell Reference:  total audience views (G9) >= Constraint: (G14) ** greater or equal to **

Click Add: Constraint of total Advertising Budget

–          Cell Reference: E9 <= Constraint: (G12)

Click Add: Constraint of Percent of Total

–          Cell Reference: Percent of Total, select all cells (F3:F8) <= Constraint: (G15)

Click Add: Constraint of number of Ads placed per publication

–          Cell Reference: (D3:D8) >= Constraint: (G16)

• Check “Make Unconstrained variables Non-Negative” to make sure number don’t go negative
• Click Solve
• Solver Results:    

We have maximized our budget of 12 million

We have reached our target audience of 800 million

There is one quirky thing that in our number of Ads Placed it specifies a decimal. We cannot place .98 of an ad. So… Check “return to Solver parameters Dialog”

• Click Add: Cell References (D3:D8) “Number of Ads Placed” drop down arrow (int) we want it an integer.
• Click Ok
• Click Solve

** If it still does not change the adds to integers, then go to the solver, options, and uncheck the Ignore integer constraints **