Download Study Guide for Spreadsheet Essentials | ABM 121 and more Study notes Agricultural engineering in PDF only on Docsity!
Study Guide
Spreadsheet Essentials
Introduction This study guide is intended to give basic background on the use of spreadsheets and provide a practical foundation for using spreadsheets in support of the business. In this study guide we will give an overview of why spreadsheets are so useful, a list of the essential things to know about spreadsheets, and do some activities to practice your spreadsheet skill. Spreadsheets have changed the world. If you are not already using one, you may be missing the boat.
Overview Successful business owners and managers use spreadsheets to answer specific questions and to make the business more profitable. Spreadsheets offer great flexibility in handling complex calculations that are difficult to do by hand. They have become invaluable tools in business for recording and manipulating business information.
The basic layout of a spreadsheet is like the rows and columns on a piece of ledger paper but the spreadsheet version extends much further down the page and to the right. Imagine a sheet of normal ledger paper the size of a blackboard, with hundreds of columns and thousands of rows, and you have an idea of the immensity of the spreadsheet environment. In recent years the most sophisticated spreadsheets, such as industry- leading Microsoft Excel, have expanded on the original ledger paper model and now include tab dividers at the bottom of the sheet to organize separate sheets into electronic workbooks.
Spreadsheets are more than an alternative to paper for recording information; they offer unique ways to manipulate and arrange information. This can range from something as simple as summing, averaging, or counting a column or row of numbers to making complex statistical or financial calculations like determining the payment amount for an amortized loan.
What if I don’t have the Excel Program? This information about spreadsheets is generic enough to apply to any sort of spreadsheet program. But it applies most easily to Microsoft Excel. Microsoft Works is an introductory program that may do everything that you need to it to. However, the more you work with spreadsheets, probably the more you will want to use a more advanced and professional program such as Excel (www.microsoft.com/office), or Corel Quattro (www.corel.com) or even Lotus 123 (www.lotus.com). If you don’t have Excel, 95% this information is going to be useful, but there may be some fine points that will not apply to the spreadsheet software you are using.
I have a Macintosh! There are not as many programs that will run a Mac, but Microsoft does have a version of Excel designed for Macs.
Essential Things to Know Spreadsheets are powerful and can be intimidating. We hope to provide a sound foundation for students to explore spreadsheets independently of their instructor. There are entire college courses devoted to learning spreadsheets. In this guided study we will concentrate on the most essential aspects of spreadsheets. These essential things to know about fall into five categories: terms, cell properties, survival commands, essential tools, and essential concepts.
A. Essential Spreadsheet Terms:
- The marker that shows your position in the spreadsheet is called the cursor and is moved by using the arrow keys and/or the mouse.
- The rows of a spreadsheet are identified by a unique number starting with the number 1.
- The columns of a spreadsheet are identified by a unique combination of one or more letters, starting with the letter A. So when the 27th^ column is designated, it will be AA, and the 28th^ will be AB.
- The intersection of one row and one column is called a cell and is identified by the column and row it occupies.
- The cell marked by the cursor is called the current cell.
- The location of the current or active cell is specified by its cell address , which indicates the column and row it occupies. For example, the current cell is C7, as indicated in Name Box.
- A range, block, or selection is a rectangular group of adjacent cells specified by the upper-left cell address and the lower right cell address separated by a colon. For example, the range C7:E12 specifies the 18 adjacent cells beginning in column C, row 7 and extending to column E and row 12.
- In a spreadsheet cell a word such as Cost: 45.00 is considered a label, because it starts with a letter. The 45.00 will not be available for use in a calculation. Labels are not used in numeric calculations.
- In a cell a word starting with a number, such as 4x4 Pickup, is considered a label.
- In a cell a number such as 5280 is considered a value , and can be used in calculations.
- In a cell an expression beginning with a = sign is interpreted as a formula and is evaluated by the spreadsheet, with the result of the formula being displayed. An example is =A5+B
- In a spreadsheet cell, the characters + - * / are the symbols for mathematical operators of plus, minus, multiplication, and division. The
b. Alignment – This attribute controls how a number or text is displayed inside the cell. Left, centered, or right-aligned; vertical or angle alignment, word wrapping, etc. c. Font – This section controls the font, size and color of the contents of the cell. d. Border – The border tab of the Format menu controls whether there is a border around the cell, and if so, what style of line, and how thick it is, and whether the border is on top, bottom, left, and right, or some combination. e. Patterns – The patterns tab of the Format menu controls whether there is a background color or pattern in the cell. Typically a crosshatch pattern might be placed in a cell that is not meant to have a number. f. Protection – The protection tab controls whether the cell is locked or hidden when protection for the sheet is turned on.
- The height and width of a cell can also be controlled, but are controlled for an entire row and column.
C. Four Survival Commands:
- The command to save a spreadsheet file is: File => Save As => and then type the name you want and select the folder to save it in. Then click on Save.
- The command to print a spreadsheet file is: File => Print. Select the number of copies and other options here before printing by clicking on Ok.
- The command to retrieve or open a spreadsheet file is: File => Open => and select the file to open.
- The command to exit or quit the spreadsheet program is: File => Exit.
D. Essential Tools:
- The cut tool (Edit => Cut) erases the selected range of cells, but holds a copy of it in memory (on the clipboard) to paste later.
- The copy tool (Edit => Copy) copies the selected range of cells to the clipboard leaving the original range as it was.
- The paste tool (Edit => Paste) replaces the selected range of cells with what was last placed on the clipboard by the cut or copy command.
- The undo (Edit => Undo) command reverses the last command or keystroke typed; continued clicking on undo will step further back in time. This command may not always work… if you have saved the file, and then try to undo a command, it will not work.
- The insert tool allows the user to: insert a row above the current cell; insert a column left of the current cell; and to insert another worksheet into the file.
- The delete command allows a person to delete a cell or range of cells without putting the contents on the clipboard.
- The edit command allows the user to edit the content of the cell by either highlighting the cell and then clicking on the formula bar, or pressing the F2 key. (Don’ t be confused by the Edit Menu at the top of the menu toolbar).
- The fill handle is used to fill several cells with new content, based on a pattern that you specify with a sample. For instance if you want columns labeled with the days of the week, start off by typing Monday in the first column. Then click and hold the fill handle, and drag to the right. The other cells will follow the pattern and should be listed to the right of the column.
E. Essential Concepts:
- Relative Cell Reference - When a formula in one cell is copied to another cell, the spreadsheet automatically compensates for the different location of the formula. The cells referenced by the formula change, according to the location the formula is now in, so that the formula is still correct. For example: if cell A3 containing the formula =A1+A2 is copied to B3, the formula in B3 will read =B1+B2 and is said to be the same formula, relative to its position on the spreadsheet.
- Absolute Cell Reference – At times the spreadsheet’ s treatment of cells references as relative when copied will result in incorrect formulas. There are times that you want a cell to always refer to a particular cell. This is called an absolute reference and can be achieve in a formula by preceding the row and/or column with the $ character. This can be done while editing the cell by pressing the F4 key and cycling through the possibilities. Here is an example of using absolute references in a formula: if cell A3 containing the formula =$A1+$A2 is copied to cell B3, it will still read =$A1+$A2. But if it is copied to B4, it will read =$A2+$A3. The reference for the column is absolute but the reference for the row is still relative.
Test your Knowledge Spreadsheet Essentials Test Your Knowledge
Name _____________________________________________
Date__________________________
Instructions: This test will help you determine what you know about spreadsheets. Underlined items indicate the content of a spreadsheet cell. Follow the instructions for each of the sections below. This assessment extends to a second page. Good luck.
Multiple-Choice: Read the statement and select the best answer by placing the letter beside the answer in the blank space provided. Each question is worth 10 points.
- ____ In a spreadsheet cell a number such as 3.1416 is considered to be a/an:
a) range b) formula c) label d) value e) None of the above
- ____ If a spreadsheet cell contained =(72-27)*2 what number would the cell display?
a) 36 b) 18 c) 90 d) 3888 e) None of the above
True/False: Indicate whether the statement is true or false by placing a T or F in the space by the statement. Each question is worth 10 points.
____ Clicking on Format => Cells => Border allows the user to change the background color of the spreadsheet cell.
____ The formula =C$35*$A5G is a valid formula (in other words, it will NOT result in an error message).
Fill-in-the-blank: Read the statement and write in the missing word. Each question is worth 10 points.
The ________________ command is used to duplicate the contents of one cell or range once or many times.
The intersection of one row and one column in a spreadsheet is called a __________________.
Matching: Below are two columns. One column has a statement and the second column has possible answers to the statement. Select the letter of the item in the second column that best fits the statement in the first column, and write the letter in the space provided. You will only use four of the 10 possible answers; none of the answers will be repeated. Each question is worth 10 points.
____ The toolbar with the common commands such as File =>, Edit =>, etc.
____ Command used to change the name of a file.
____ Contents of a spreadsheet cell that begins with a = character.
____ The command to move the contents of one or more cells to the clipboard.
a) formula b) value c) copy d) cut e) status bar f) menu bar g) title bar h) File => Save i) File => Save As j) File => Exit
