Excel – Data Tools

This lesson introduces shortcuts for extracting information from large sets of data.

Instructions:

Read through the set of learning objectives. This is what you should know when you finish the lesson.

Watch the YouTube videos before starting the lesson.

Complete every step in the assignment, in the order given. Do not skip steps.

When you have done all of the steps, fill out the Check for Understanding (CFU) form.

A completed CFU for every lesson, in-class and homework, is due to be turned in during your next class. 

The CFU forms are not to be submitted by email.

Where there are differences between software versions, the instructions for Excel 2010 are in Black, for the Mac are in green and for Excel 2013 are in blue.

Summary of Key Points:

Apply, modify and remove subtotals.

Create a subtotals report using the visible cells method.

Create a subtotals report using the AVERAGEIF function.

Permanently replace formulas and functions with their values.

Locate and remove duplicate records.

Use advanced filters.

Videos:

Visible Cells

Advanced Filters

More Data Tools

 

Background

Microsoft Office includes a full database application called Access. Access can be used for tasks that require complex data management capabilities. Excel has database tools suitable for smaller and simpler tasks. Learning these tools will prepare you to understand more complex databases. 

Databases store information in tables. A data table starts with one row of labels across the top. The labels are called field names and the columns are called fields. Below the first row there are multiple rows of data. The rows of data are called records. A data table should not include any empty cells.

Let’s look at a data table. Download and save this file http://bus91L.altervista.org/Data/2013Data.xlsx

 

Select the Students worksheet. This data table includes one row of labels and 300 rows of data. It is 11 columns wide. To help Excel determine which cells are included in the database table, you should surround it with empty cells. In our worksheet all of the cells in the row beneath the table (row 302) and the column to the right of the table (column L) are empty cells.

Because the data table is isolated you do not have to mouse over the complete table to work with it. You only have to click in one cell in the table. This comes in handy when you have hundreds or thousands of records.

Add Subtotals

Subtotals can be used to perform mathematical analysis on the records that share a characteristic, for example students from the same city, or students with the same major. We will find a sum and an average. You start by sorting the data so all students with the desired characteristic are grouped together.

1.         Click in cell D3 and sort the data in the table in alphabetical order by City, using the A-Z icon [ALT  A  S  A].  If you get a pop up message asking if you want to expand the selection, you have made a mistake. You selected the column label D at the top of the column instead of clicking on a cell that is included in the table. If you continue you will only be sorting the data in column D. This will ruin the set of records. If you do it correctly the data in row 2 should be Jose Bauer, an International Business student from Campbell.

2.         Click in any cell in the data range.

Click on the Subtotals icon [ALT  A  B] on the Data tab.

In the MAC click on the Data menu and select Subtotals.

You should see this dialog box:

In the dialog box, “At each change in” select City. The entry in the “At each change in” box tells Excel which column to look in. Every time it finds a change in value it inserts a subtotal. If you don’t sort the data before creating your subtotals, you will be getting multiple subtotals for each city.

3.         Set the “Use Function” to Sum.

4.         For the “Add subtotal to” boxes select the Gas, Phone and Food fields.  

5.         Select “Summary below data.” Click OK.

Your report will now include subtotals below each city, and a set of vertical lines of the left. The lines on the left are used to hide and display subsets of the data.  

6.         Scroll down to see the some of the subtotals. Click on the minus signs next to the Campbell, Fremont and Gilroy Subtotals to hide the records for those three cities.

7.         Click on the plus signs (on the left of each subtotal) to redisplay the records for the three cities.

8.         A picture of a sprreadsheet with subtotals turned on.Notice the vertical lines on the left have the numbers 1, 2 and 3 above them. Clicking on the 1 will hide everything but the grand total. Try it. The entry in cell i2 might show railroad tracks ####. This just means the column is too narrow to show the total.

9.         Clicking on the two will hide everything except for the subtotals and grand total. Try it. Cell K317 shows 1E+05. This is another method Excel uses to show numbers that are too wide to display in the column.

10.     Clicking on the 3 shows all of the records. Do that now. 

Now we will remove the subtotals and show the average GPA.

11.     Click somewhere in the data.

12.     Click on the Subtotals icon [ALT  A  B] on the Data tab.

13.     In the dialog box, click on “Remove All”

Project - Create a report showing Average GPA by Major

14.     Click in cell F3 and sort the data in the table in alphabetical order by Major, using the A-Z icon [ALT  A  S  A].

15.     Click in any cell in the data range.

16.     Click on the Subtotals icon [ALT  A  B] on the Data tab.

17.     In the dialog box, “At each change in” select Major.

18.     Set the “Use Function” to Average.

19.     For the “Add subtotal to” boxes select the GPA field. 

20.     Click OK

21.     Click on the level 2 option (see the picture in step 8) to hide the individual records (rows) and just show the subtotals.

At this point we want to save the report, showing just the subtotals. Let’s try to copy them into another worksheet. We will see that it doesn’t work.

22.     Click on the Insert Worksheet icon, [ALT  H  I  S] it’s the bottom-right tab below the worksheets.

23.     Right click on the worksheet tab and rename it as GPA.

24.     Select the Students Worksheet.

25.     Hide columns A to E, Column G, and columns I to K. To hide a column select the letter name for the column, right click and choose Hide. A keyboard shortcut to hide columns is Ctrl + 0. You can also hide a column by clicking in the letter row and dragging it to the right, or by using an icon [ALT  H  O] on the Home tab of the ribbon.

26.     At this point you should just be seeing two columns and twelve rows of data, F1:H312. 

27.     Copy the range F1:H312. Click on the tab of the GPA worksheet. Press Enter.

Did you get what you expected? No. You are seeing the contents of columns F and H in the students data, which you wanted to see, but you are also seeing the contents of column G, which was supposed to be hidden. Data in hidden columns is included when we use the standard copy and paste commands. But there is a way to copy and paste just the visible (unhidden) cells.

Use Visible Cells

Watch this video on ignoring hidden cells: Visible Cells

Let’s try again, using the trick we just learned from the video.

28.     Delete the contents of the GPA worksheet. Click on cell A1.

29.     Select the Students worksheet and select the columns F and H. A keyboard shortcut to select a column is to click in any cell in the column and then press CTRL + the spacebar.

30.     On the Home section of the ribbon, click on the Find and Select icon [ALT  H  F  D]. Choose Go to Special. Choose Visible cells only. Click Ok.

For the MAC, from the EDIT menu, choose Find and then choose “Go To”. A dialog box opens. Click on Special. Choose Visible cells only. Click Ok.

31.     Press Ctrl + C to copy the cells. Click on the tab of the GPA worksheet. Click in cell A1. Press Enter.

32.     Use the Format icon on the Home tab [ALT  H  O] to  resize the columns and rows so the data is clearly visible.

The numerical values in the GPA column are the result of formulas. We can replace functions and formulas with the numbers they generate. The tool to do that is a “Paste Special” option called “Values.”

33.     Select the entire GPA worksheet and copy its contents. Ctrl + A is the keyboard shortcut to select all. Press Ctrl + C to copy.

34.     Right-click and choose Paste Special. Select the second option, Paste Values. In Excel 2013 the keyboard shortcut is either [ALT  H  V  V] or [ALT  H  V  A].  Press Enter. This converts any formulas into their numerical values. We had formulas in column B. Those formulas are not replaced with numbers.

35.      Switch into the formulas view of the worksheet by pressing Ctrl + the ~ character. This character is above the Tab key.

You’ll see that Excel converted the formulas into their number values. We did this so that when we remove the subtotals from the Students worksheet the results of our formulas will not be lost.

36.     Switch back into the regular view of the worksheet by pressing Ctrl + the ~ character again.

37.     Return to the Students worksheet. Click anywhere in the subtotals. Click on the Subtotals icon [ALT  A  B]  and select Remove all. Click on Ok.

Only two columns of data will be showing, the rest of the data is in hidden columns.

Did you remove the subtotals in the previous step? If not, you will have problems later on.  If you skipped that instruction go back and do it now.

38.     Select the entire worksheet by pressing Ctrl + A. On the Home section of the ribbon click on the Format icon [ALT  H  O]. Mouse over the Hide and Unhide options and choose Unhide columns. (Another way to unhide these columns is to put your cursor to the left of the letter F at the top of the worksheet. Your cursor should change into a two-headed arrow. Click and drag to the right. ) Click anywhere in the data.

This report took a lot of steps to build. Is there a quicker, easier way? Yes there is. We can use the AVERAGEIF function.

39.     Click on the Students Worksheet tab. Click on cell F1.

40.     Press the Shift key, the End key and the down-arrow key. The down-arrow key is on the bottom row of your keyboard, below the End key. The range F1:F301 should be selected. If you don’t have a down arrow on your laptop, try pressing Fn with the Page Down.

41.     Another way to select this range of data is to type F1:F301 into the Name box.

Press Ctrl + C to copy the list of majors. Click on cell M1 and press Enter to paste the list into the M1:M301 range. Widen the column to show all of the majors - click the Format icon [ALT  H  0] on the home section of the ribbon and choose AutoFit Column Width.

Remove Duplicate Records

42.     Now we want to create a short list in column M showing each Major. Click in the name box and type in M2:M301 to select that range of cells. Press Enter.

43.     On the Data tab of the ribbon, click on the Remove Duplicates icon [ALT  A  M].

In the dialog box, underneath the word Columns, make sure the word Majors is checked (If other columns are listed, they should NOT be checked) and then Click Ok.

A message will appear telling you 290 records were removed and only 10 were kept. Click Ok.

You should see a list of ten majors in column M.

We are going to use this list as an index to the contents of the data in the A1:K301 range when we create IF functions. 

You could also use the Remove Duplicates tool to create a list to use for data validation.

Enter the AverageIF Function

44.     Click on cell N1 and type the word Average and press Enter.

45.     Click on cell N2. Type =AVERAGEIF(F$2:F$400,M2,H$2:H$400) and press Enter. If you don’t want to type this, you can copy and paste the formula, but you will need to use Paste Special [ALT  H  V  S] and select Text.

46.     Click on cell N2. Right click and choose Format cell. Select the Number format and set the number of decimal places to 2.

47.     Click on cell N2. Mouse over the lower right had corner of the cell until you see the small black cross, the AutoFill handle. Double click. Your function should be copied down into cells N3:N11.

M

N

Major

Average

Marketing

2.86

Finance

2.95

Undeclared

2.93

Management

2.93

International Business

2.91

Accounting

2.88

Human Resource

2.99

Information Systems

2.89

Hospitality Mgmt

2.85

Corporate Finance

3.02

Convert Functions into Numbers

The functions in the range N2:N11 will automatically recalculate their values whenever the data in the A1:K301 range changes.  Suppose we don’t want that to happen and we want to keep the numbers that are showing right now. We can copy the formulas and permanently replace them with their current values by using the “Paste Special” option called “Values”. Paste Values basically takes a picture of the values displayed in the cells.

48.     Click on the column letter N and press Ctrl + C to copy the contents of the entire column.

49.     Right-click and choose Paste Special. In Excel 2013 the keyboard shortcut is either [ALT  H  V  V] You can also use  [ALT  H  V  A].  Select the second option, Paste Values. Press Enter to paste the numbers into column N.

Use Advanced Filters

Watch this video on Advanced Filters

An advanced filter can be used to simultaneously select data from multiple fields in a range or table. You set up the advanced filter by creating a filters list of items you want to include in your output. The top row of the filters list includes the field names. Below the field names you enter the criteria (desired characteristics) for Excel to use when it searches the data. Only rows of data that match the criteria will appear in your output area.

If criteria values are entered into the same row in the filters list, only records that match all of the criteria will be displayed. This is called an AND filter. If the criteria values are entered in different rows in the range/table, records that match the criteria in any of the rows will be displayed. This is called an OR filter.

We will make two filtered lists. The AND list will show students who both live in San Jose and spend over $400 a month on food. The OR list will show students who either live in San Jose or spend over $400.

Set up the range/table of criteria for the AND list.

50.     Type City in cell M13.

Type =”San Jose” in cell M14. Include the quotation marks.

Type Food in cell N13.

Type >400 in cell N14.

 

M

N

13

City

Food

14

=”San Jose”

>400

15

 

 

Note that the words City and Food must be IDENTICAL to your column headings in D1 and K1. If they are not identical you get just a single row of labels in your output, with no records selected.

When you create and run the advanced filter you will see a list showing only the students who BOTH live in San Jose AND spent over $400 on food.

 

Create and run the advanced filter

51.     Click somewhere in the data range, A1:K301. If any columns are hidden you can unhide them by selecting the entire worksheet (Ctrl + A is the shortcut), going to the Home tab and selecting the format cells icon. [ALT H O U L]. Note: If you can’t unhide column A, type A1 into the name box and press Enter.

52.     On the Data Tab, click on the Advanced icon [ALT  A  Q] in the Filter group.

53.     In the dialog box, click on the radio button next to “Copy to Another Location.”

54.     The “List range:” box should already show the range containing the data records.

55.     In the “Criteria range:” box, click on the cell-selector icon. Click and drag over cells M13:N14.  (Don’t click on individual cells.) 

56.     In the “Copy to:” box, click on the cell-selector icon. Click on cell O16. This cell is the upper left hand corner of the area where your output will show up.

57.     Press Enter. This will create a list of 37 records that match the criteria. If you look in the city field you will see San Jose in every record. If you look in the food field you will see a number greater than 400 in every record.

If all you see is a row of labels in the O16: Y15 range Excel was unable to find the words San Jose in the A1:K301 data range, or the word City (M13) or Food (N13) do not exactly match the words in the date range.  To make sure they all match, copy and paste the San Jose, Food, and City labels from the data range into the labels you used in the filter list. Click on cell P2 and click in the formula bar. Change San Jose to =”San Jose” and re-run the advanced filter.

Help with extracting records  

Filters Copy Paste Data Extract    2:20

Set up the range/table of criteria for the OR list.

58.     Delete the contents of the range O16:Y16. A shortcut method is to click on any cell in the range and then press CTRL + * followed by pressing the delete key. You can use the CTRL + * shortcut to select any range of data as long as it is surrounded by empty cells.

59.     Delete the contents of cell N14. Type >400 in cell N15. When you create and run the advanced filter you will see a list showing any of the students who EITHER live in San Jose OR spent over $400 on food. Everything on the first row underneath your column headers is treated as an AND requirement. Everything on the second row underneath the column headers is treated as an OR requirement.

 

P

Q

1

City

Food

2

=”San Jose”

 

 3

 

>400

Create and run the advanced filter

60.     Click somewhere in the data range A1:K301.

61.     On the Data Tab, click on the Advanced icon [ALT  A  Q] in the Filter group.

62.     In the dialog box, click on the radio button next to “Copy to Another Location.”

63.     The “List range:” box should already show the range containing the data records.

64.     In the “Criteria range:” box, click on the cell-selector icon. Click and drag over the M13:N15 range.  (Don’t click on individual cells.). Press Enter.

65.     In the “Copy to:” box, click on the cell-selector icon.

66.     Click on cell O16. Press Enter. This will create a list if records that match the criteria. You should see 169 records.

67.     Click on cell Y17 in the Food column of the records then click on the A-Z sort icon [ALT  A  S  A]. The first 53 records are students who live in San Jose. The remaining records are students who spent over $400.

 

 

More Tools

“Goal Seek” and “What If” are additional data tools. The Break Even worksheet has examples of how to use them. You can learn more from this video More Data Tools

The Subtotal Function gives you other filtering options. Subtotal() Function   5:31

 

Review – Fine Points

 

 

Instructions for the Check for Understanding (CFU) form.

A CFU form is turned in for every assignment.

Starting with the line for your name, copy and paste the CFU form into a Word document.

Fill in your name, date, section number

Circle (or indicate by highlighting) a Y or N response for each statement. I do not subtract points for No answers.

Below the list of Y/N statements, insert ONE screenshot of any part of your work. I just need to see something to prove that you did the assignment. I don’t need to see all of the assignment.  Resize the screenshot, if necessary, so both the form and graphic can fit on one page.

A copy of the CFU is due to be turned in during the next class session.  I do not accept CFU’s through email. 

 

Name _______________________________      Date___________       Section _______

Check for Understanding – Excel Data Tools

Y/N I can apply, modify and remove subtotals.

Y/N I can create a subtotals report using the visible cells method.

Y/N I can create a subtotals report using the AVERAGEIF function.

Y/N I can permanently replace formulas and functions with their values.

Y/N I can locate and remove duplicate records.

Y/N I can use advanced filters.