Basic spreadsheet commands

1. Basic spreadsheet commands

2. (note on powerpoint) • These powerpoint slides were made using the following menu options: • View -- normal • Insert -- new slide • Format -- slide format, and choosing the one that allows a title and notes

3. Before you sit at the computer • 1. Make sure you have a task that motivates you to persist in learning Excel (or an equivalent spreadsheet program). • 2. Design your spreadsheet on paper, including the kinds of calculations you need to do. • 3. Arrange assistance from someone more advanced in using Excel. • 4. Arrange convenient access to a computer with Excel installed.

4. Keyboard shortcuts • There are keyboard shortcuts for commonly used commands in the menus. On a windows machine these involve holding down the Control key in combination with those below. On a MAC, the Command (apple) key with the others, e.g., • C for copy • X for cut • V for paste in what you have copied or cut • P for print • S for save (do this often)

5. Additional shortcuts Control or Command (apple) plus • I for insert blank cell(s) or row(s) or column(s) • K for delete the selected cell(s) or row(s) or column(s) • Z to undo your last command

6. A grade sheet exercise • Type the students names in columns A & B from row 3 downwards • List your assignments in row 2 from column 3 to the right If you plan 10 assignments then in cell M3 type =sum(c3:l3), which will add up the grades for that student • Now select cell M3 by clicking on it, copy it into the computer’s memory using the copy command, and paste it into cells M4 to the end of the students.

7. Bells & whistles 1 • Convert grades in col. M to % in col. N • Typing the maximum grade possible in M2 • In N3 type =m3/m$2*100 • Copy and paste this formula into the cells below N3 • The formula in cell N4 will read m4/m$2*100, and so on

8. Bells & whistles 2 • To round off to nearest number, select the cells you wish to round off • Pull down the format menu, and (depending on version of excel being used) look for number or cells then number • Choose the option with no decimal places

9. Making s’sheets understandable • Include notes or comments that explain your spreadsheet. These can be hidden behind a cell using the insert comment or note menu option. • When trying to decipher someone else’s spreadsheet formulas, write out on paper what they do in words.

10. Other standard formulas • E.g., average, standard deviation, maximum, minimum • Explore under the Sigma or f symbol on the tool bar. If your version doesn’t have these, look for functions • E.g., max (c3:l3) would supply the highest grade that student got

11. IF formulas • IF formulas allow you to do useful tricks. E.g., in O4 you might type IF (N4<50, “Fail”, “Pass”) which means if grade (as percentage) is less than 50 the cell O4 will show the word Fail; otherwise it will show Pass. Try it and see.

12. Exploration • If there’s something else you’d like to do, either • 1. Ask a person with more experience to show you (and add in some other tricks if you have time to take them in), or • 2. Explore the different items in the menus and tool bars (sometimes the help menu can help you, but asking a person is better)

13. More complex spreadsheets • Complexity in spreadsheets is less a matter of learning Excel commands than of what you want to calculate. Plan this on paper before you open the software (which I should have had you do in the thermostat exercise).

14. (More complex powerpoint presentations) would explain everything in the preceding show using visual images, not simply text, and allow animations such as this on this slide (created using Menu option: Slide show, animation, fly in). • But more time is needed to compose such a presentation.