Excel Forms and Arrays

Using Tools to Create Forms

Practice with Array Formulas.

 

Learning Objectives

Understand how to Google Forms.

Understand how to use the Form Tool in Excel.

Understand the basics of Array Formulas.

 

Use Google Forms to Collect and Analyze Data

Google Forms are a convenient and easy tool you can use to conduct surveys.

Google Forms Video     Time = 14:58

Google Forms Task

Create a Google Form

1.      Ask for the first and last names of the respondents  (2 questions)

2.      Ask at least one multiple-choice question

3.      Ask at least one Yes/No or True/False question

4.      Ask at least one question that requires a sentence or paragraph response

5.      Include at least one graphic.

6.      Send the form to several people and get at least three responses.

 

Use the Excel Forms Tool to Manage Data

Forms are a user-friendly method of maintaining a data table. The Forms icon is not included in the ribbon, but you can add it to the Quick Access Toolbar, aka the QAT.  The QAT is found on the top left corner of your monitor, either above or below the ribbon. It’s at the top left of this picture.  The toolbar is showing seven icons (one of them, Redo, is grayed out).

This is a picture of what the quick access toolabar looks like.

Forms Task One - Add the Forms Tool to your Quick Access Toolbar.

This video shows how. Form Tool   You only need to watch the first 1:30.

7.       Right-click on the down arrow next to the Quick Access toolbar. Select “More Commands” from the bottom of the list.

8.      A dialog box opens. In the “Choose Commands From” box, select All Commands.

9.      Find the “Form” icon in the list. Click on the icon. Click Add. The icon will now appear in the list of icons on the right hand side of the dialog box.

10.   We want it to be the first icon in the list. Use the up arrow key to move it to the top of the list. Click OK. The icon will now be on your Quick Access Toolbar

 

Forms Task Two – Add the Forms Tool to the ribbon

This video shows how Excel User Forms   1:02

11.  Add the Tool to the data tab in the ribbon.

 

Forms Task Three – Use the Forms Tool.

12.  Download and save this file http://bus91L.altervista.org/Data/2013Data.xlsx

13.  Select the Students worksheet. This data table includes one row of labels and 300 rows of data. It is 11 columns wide

14.  Click on the cell A2 in the data table on the Students worksheet. Press the ALT key. You will see the icons on the Quick Access toolbar are all numbered. The Form icon that you just added should have the smallest number (1). Type the number 1 to select it. The Forms Tool dialog box will open. 

Add a Record to the Table

15.  Click New.

16.  In the ID# field type 401 and press Tab. Use your name and personal data to fill out the remaining fields. Use the Tab key to move between fields. Expenses will be $50, $70, and $320 for the expense categories.

17.  Click Close to add the record to the data table. You should see the new record at the bottom of your rows of data.

Find and Edit a Record in the Table

Click on any cell in the data table and click on the Form icon on the quick access toolbar.

18.  Click on Criteria.

19.  Enter 106 for the ID#. Press Enter.

20.  Change the student’s class to Senior.

21.  Click Close to add the changes to the record.

Remove a Record from the Table

22.  Click on any cell in the data table and click on the Form icon on the quick access toolbar.

23.  Click on Criteria.

24.  Enter 401 for the ID#. Press Enter.

25.  Select Delete and click Close to remove the record.

 

Array Formulas

An "Array" function allows you to perform mathematical operations on many cells (ranges) instead of just one at a time. They are also known as “Control Shift Enter” or “CSE” formulas because those are the keystrokes used to create array formulas.

 

To briefly explain what an array function does, it performs a set of calculations that would normally be done in a row or column in the worksheet, but does not show them anywhere in the worksheet. The result of the calculation is stored somewhere in the computer memory. This is the key concept to take away from these videos.

 

ARRAY ONE

Introduction to Arrays

https://www.youtube.com/watch?v=C7bA1jdn_Fo     Time = 3:35

 

ARRAY TWO

Introduction to Array Formulas The TRANSPOSE function. This video is not closed-captioned.

https://www.youtube.com/watch?v=BZ1sNKJ2cjY     Time - 3:30    

 

ARRAY THREE

You can use Array formulas to multiply one range by another range and find the sum. Note that the array formula used inside the function is similar to a regular formula, except that the individual cell references are replaced with ranges. =(C5*D5) becomes = {C5:C6 * D5:F6}. The curly braces tell you that it is an array formula.

https://www.youtube.com/watch?v=CHLUP4g43B4     Time = 3:38

 

ARRAY FOUR

This video shows how an array function performs a set of calculations, using the MAX function.

https://www.youtube.com/watch?v=gmxFcXXlEAg     Time  = 3:07

 

ARRAY FIVE

Using Arrays inside formulas. This video uses the LARGE function.

Note: To create an array inside a formula, you can’t just type in the curly brackets { } when you are creating the formula. You also have to use the Ctrl+Shift+Enter keyboard combination when you enter the completed formula. Both are required.

https://www.youtube.com/watch?v=FnV7bKh3JfM     Time = 2:35

 

Arrays Task One: 

26.  Open the workbook 2010Data.xlsx. http://bus91L.altervista.org/Data/2013Data.xlsx

27.  Select the Sales worksheet.  Select the range K2:N9.

28.  Type =TRANSPOSE(A5:H8) and press CTRL + Shift+ Enter. This function should take the contents of the 8x4 A5:H8 range and paste it into the 4X8 K2:N9 range, with the row and column values transposed.

29.  Select the range K2:N9. Change the format of the numbers to show zero decimal places.

30.  Widen the columns so the column headers fit within each column.

31.  Center the cell contents.

32.  Look in the formula bar. You will see ={TRANSPOSE(A5:H8)} The { and } symbols show you this is an array formula.

33.  Click in cell M5 and press the delete key. You will get an error message. You can’t delete the contents of individual cells within an array. Clear the error message.

34.  How do you see which cells are contained in an array> Click in any cell in the K2:N9 range.

35.  Press the F5 function key. (Note: for some laptops you must press both the Fn key and the F5 key.) The Go To dialog box opens.

36.  Click on Special. The Go To Special dialog box opens.

37.  Select the Current Array option and click Ok. The dialog box closes. Your array range will be selected.

 

Arrays Task Two:

Modify the worksheet you created in the Inventory Lesson to eliminate some of the columns and replace them with Array Formulas.

When you enter the array formulas remember to use CTRL+Shift+Enter. You do not need to type in the brackets { and }.That is done automatically.

 

A

B

C

D

E

F

G

H

I

J

K

L

3

Item

Units

Stockout

Order

Starting

Purchases

Ending

Sold

Cost

Price

Margin

Gross Profit

4

Beer 1

Can

100

144

340

0

49

=E4+F4-G4

.75

3.75

=J4 – i4

=H4 * K4

5

Beer 2

Bottle

100

144

160

144

26

=E5+F5-G5

.85

4.25

=J5 – i5

=H5* K5

6

Soda

Can

100

144

80

288

52

52=E6+F6-G6

.25

2.00

=J6 – i6

=H6 * K6

7

Juice

Bottle

100

144

100

144

60

=E7+F7-G7

.20

2.00

=J7 – i7

=H7 * K7

8

Wine

Bottle

100

144

60

288

33

=E8+F8-G8

1.50

4.50

=J8 – i8

=H8 * K8

38.  Enter an array formula in cells H4:H8 ={(E4:E8)+(F4:F8)-(G4:G8)}

39.  Enter an array formula in cells K4:K8 ={J4:J8-i4:i8}

40.  Enter an array formula in cells L4:L8  ={H4:H8 8 K4:K8}

41.  Type the word Total in cell K10

42.  In cell L10, enter the Function =SUMPROFIT(H4:H8,K4:K8))  This is not technically an array formula, it’s a function, so you don’t need to use the Ctrl+Shift+Enter keystroke combination when you enter it.

Instructions for the Check for Understanding (CFU) form.

A CFU form is to 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 the “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 Forms and Arrays

Y/N I understand how to Google Forms.

Y/N I understand how to use the Form Tool in Excel.

Y/N I understand the basics of Array Formulas.