| Here's some example problems to get you comfortable with basic spreadsheet skills. |
|
| Instructions: In the YELLOW highlighted cells, type in your answer! |
|
| Use the hints to the right verify your work with the recommended answers to the far right. |
|
| Exercise # |
|
| Data |
|
| HINTS |
ANSWERS |
| Basics Formulas: |
1 |
Add two numbers, 3+2=? |
5 |
|
|
| Type =3+2 and then the enter-key |
5 |
|
| 2 |
Subtract two numbers, 5-2=? |
3 |
|
|
| Type =5-2 and then the enter-key |
3 |
|
| 3 |
Multiply two numbers, 6x3=? |
18 |
|
|
| Type =6*3 and then the enter-key |
18 |
|
| 4 |
Divide two numbers, 8 divided by 4 is ? |
2 |
|
|
| Type =8/4 and then the enter-key |
2 |
|
| 5 |
Add the contents of the two cells to the right (5+3=?). |
8 |
5 |
3 |
| Type = and then click on the 5, then type the +, then click on the 3, then the enter-key |
8 |
|
| 6 |
Multiple the contents of the two cells to the right (2x3=?). |
4 |
2 |
3 |
| Type = and then click on the 2, then type *, then click on the 3, then the enter-key |
6 |
|
| 7 |
In the correct order using the cells to the right, perform 2+3*4=? |
14 |
2 |
3 |
4 |
No worries, just enter formula from left to right. Multiplication take precedence over addition so 3x4 happens first. |
14 |
|
| 8 |
In the correct order using the cells to the right, perform 5+4/2=? |
7 |
5 |
4 |
2 |
No worries, enter formulas left-to-right. Division takes precedence, so 4/2 happens first, then add the 5. |
7 |
|
| 9 |
In the correct order using the cells to the right, perform 5+4 and then divide by 2=? |
4.5 |
5 |
4 |
2 |
Oops, you must use parentheses to ensure the addition is done first, then the result (9) can be divided by 2. |
4.5 |
|
| 10 |
Inspect this formula for accuracy. Make sure it correctly performs 2+1=. |
3 |
2 |
1 |
| Double-click (not single-click) on the yellow cell that shows 3 now -- that a formula to compute 2+1. Excel will color-code the cells in the formula and on the screen. RED-to-RED, BLUE-to-BLUE. It's right! |
(must be live to see the colored boxes around the 2 and 1) |
|
| 11 |
Using the formula, sum the 3 cells to the right. |
6 |
1 |
2 |
3 |
Type =sum( and then click the 1-cell holding down the left button, drag across the 2 and then over the 3, let go of the left button. Type ) and enter-key. |
6 |
|
| 12 |
Using the formula, find the average of the 3 cells to the right. |
3 |
2 |
3 |
4 |
Type =average( and then highlight the 3 data cells, then hit enter-key. Note that you do NOT need to close the parentheses as Excel will fill it in. OK to always close it manually, too. |
3 |
|
| 13 |
Find the weighted average of the 3 values to the right (weights are below each number) |
6.2 |
5 |
6 |
7 |
Multiply each value by its weight, then add the next, then the next. |
6.2 |
|
|
|
|
| 10% |
60% |
30% |
|
| 14 |
Using the formula, find the weighted average of the values to the right (=sumproduct). |
6.2 |
5 |
6 |
7 |
Type =sumproduct( then highlight the values 5, 6,7, then type comma, then highlight the weights 10%, 60%, 30%). You must get the same answer as above, but it's much faster, especially when you have long lists of values. |
6.2 |
|
|
|
|
| 10% |
60% |
30% |
|
| 15 |
Find John's score on the 20-point test using the Lookup function. |
18 |
Abe |
John |
Zoe |
Type =lookup( and then the Value you want to find "John" -- or click on the cell that includes the name -- type comma, then highlight the cells with the names (find items), type comma, then highlight the cells with the scores (values to return to the answer). |
18 |
|
|
| John |
| 20 |
18 |
19 |
Note -- the "find items" must be sorted in alphabetical order or numerical order (lowest-to-highest). Otherwise, see the =VLOOKUP() or =HLOOKUP() function using 0=FALSE to get "exact match"). |
| Basic Formatting |
16 |
Turn this answer (8) to bold font. |
8 |
|
|
| Home menu bar has many font edits, just select the cell then high the B button on top. |
8 |
|
| 17 |
Make this answer in 14-point font. |
14 |
|
|
| Either using the format menu bar's drop down font size, or just hit the A-up button a couple of time! |
14 |
|
| 18 |
Turn this answer RED letters. |
Red |
|
|
| The A-color button is ready with RED, click it now or use the drop-down menu to see other colors. |
Red |
|
| 19 |
Change the background color green. |
Green |
|
|
| The yellow highlight is the default, so click on the cell and then select the fill paint can button to find a green that you like! |
Green |
|
| 20 |
Change this from centered-font to left font. |
Left |
|
|
| There are left-right options as well as up-down options. |
Left |
|
| 21 |
Make sure this text fits within the cell. |
All text fits here. |
|
|
| Use the Wrap Text button on the Home Bar, in the Alignment set of buttons. |
All text fits here. |
|
| 22 |
Format this into dollars and cents. |
$ 4.56 |
|
|
| Just use the button in the Home Bar, Number set that looks like a dollar sign! |
$ 4.56 |
|
| 23 |
Format this into percentage. |
23% |
|
|
| Just use the percentage sign button. |
23% |
|
| 24 |
Format this to look like it a whole number (e.g. 6). |
6 |
|
|
| Use the 000-left arrow button, or the 000-right arrow button to add or remove digits. Note - -this only changes the look of the number, it does NOT actually remove the digits for future math operations. |
6 |
|
| 25 |
Make this actually equal to the whole number (e.g. no decimal places for later math). |
6 |
|
|
| You must use the =ROUND formula. =round(cell,0). Whole numbers require zero digits after the decimal point. Consider experimenting with other rounding options with 1 digit, 2 digits or even -1 digits. |
6 |
|
| 26 |
Put a thick border around this cell. |
Box |
|
|
| Click on the cell, then then select the border that you want from the drop-down menu in Home -- Font. "Thick outside border" |
Box |
| Basic Commands |
27 |
Copy the cell down so John has 2 rows. |
John |
|
|
| Several ways -- copy the cell, then click on the new cell and then paste. |
John |
|
|
|
| John |
|
|
| Option #2 -- click on the cell, drag the dot on the lower-right down to where you want to stop copying. |
John |
|
| 28 |
Copy this formula down 3 rows. |
5 |
2 |
3 |
| Same as above -- notice that the reference cells move DOWN as you copy down. We call this RELATIVE locations -- ex: right one cell, then right two cells. This is the default method of copying in spreadsheets. |
5 |
|
|
|
| 7 |
3 |
4 |
|
| 7 |
|
|
|
| 9 |
4 |
5 |
|
| 9 |
|
|
|
| 11 |
5 |
6 |
|
| 11 |
|
| 29 |
Copy this formula BUT keep the first number in the same spot, with the second number relative moving down. |
5 |
2 |
3 |
| Here you need to include the $ in the formula whenever you want to FIX a reference row # or column letter, and not let it be relative. |
5 |
|
|
|
| 6 |
3 |
4 |
| Here's the formula for the first one, in cell D44. =$E$44 + F44 |
6 |
|
|
|
| 7 |
4 |
5 |
| You can also lock a cell reference by hitting the F4 key after selecting the cell. Hit F4 again, and the dollar signs will change -- keep hitting F4 to see what happens! |
7 |
|
|
|
| 8 |
5 |
6 |
|
| 8 |
|
| 30 |
Sort these names in alphabetical order. |
Abe |
|
|
| Highlight all three cells, then select the Sort & Filter button in the upper right "Editing" block. Select the "A-Z" option. Continue with the current selection, not expanded. Consider opening the "Custom sort…" for more options that you may need later. |
Abe |
|
|
|
| John |
|
|
|
| John |
|
|
|
| Zoe |
|
|
|
| Zoe |
|
| 31 |
Filter the data to the right to show only Part Number 123. |
| Part # |
Order Date |
Quantity |
Highlight the entire table (including the headings). Then, click on the Sort & Filter button in the upper right "Editing" block. Click on Filter, and then the down arrows to filter will be shown for all headings in your table. Since we need to filter by Part Number, click on that down arrow, and then click to have only the check mark by the "123". Click OK. Notice that the filtered rows will now have the row numbers on the left in BLUE, and a FILTER symbol will be shown by the heading that is filtered. Don't miss these reminders as there is now data that can't be seen until you UN-FILTER the table. |
Part # |
Order Date |
Quantity |
(note: filtered rows will disappear!) |
|
|
|
|
| 123 |
Jan 1 |
6 |
| 123 |
Jan 1 |
6 |
|
|
|
|
| 234 |
Jan 2 |
89 |
| 123 |
Jan 4 |
32 |
|
|
|
|
| 345 |
Jan 3 |
54 |
| 123 |
Jan 5 |
4 |
|
|
|
|
| 123 |
Jan 4 |
32 |
|
|
|
|
| 123 |
Jan 5 |
4 |
|
|
|
|
|
|
|
|
| Part # |
Order Date |
Quantity |
|
|
|
|
|
|
|
|
| 123 |
Jan 1 |
6 |
|
|
|
|
|
|
|
|
| 123 |
Jan 4 |
32 |
|
|
|
|
|
|
|
|
| 123 |
Jan 5 |
4 |
|
| 32 |
Above you computed the average of data provided. Now, find the Standard Deviation of a sample of data collected. This measures the "spread" or "deviation" of the data. |
1.5275252317 |
5 |
6 |
8 |
Type =StDev( and then highlight the 3 data cells, then hit enter-key. Note that you do NOT need to close the parentheses as Excel will fill it in. OK to always close it manually, too. Note, for a sample of data (normally business data), you could also use the newer "StDev.S" function. Use the "StDev.P" ONLY when you have the entire population of all data. |
1.5275252317 |
RVP2021
M1Excelprep--SOLUTIONlearningactivity1.xlsx
