INVENTORY LIST STEPS FOR SHEET 1 Open Office Calc

Assignment: Create an inventory workbook with the following 3 sheets:

SHEET 1:

Step by Step Guide for SHEET I:

1. Create a new Open Office Calc spreadsheet.

·         Press Start / Program / Open Office / Calc

·         Choose File / New / Spreadsheet to create an empty spreadsheet.

·         Save it as an excel spreadsheet by choosing file / save as / change then "save as type" to Microsoft Excel 97/2000/XP and enter a filename. (When it says there could be problems, so you might not want to do this, say "yes" you do want to save as excel.)

1. Set the sheet label to "Simple List"

·         Right click on the word "sheet1" at the bottom of the screen.

·         Choose Rename sheet

·         Type "Simple List" and then enter.

1. Fill in the column headings:

Enter : Column Headings: Item#, Item, Category, Quantity, Unit Value, Total Value, % of Total Value of All Items, More than 1, Sentence about item. (You can also add any other columns you want to add, such as a comment at the end.)   - so that it looks like the following:  (Tip: copy the columns names to word, convert text to table at commas, then copy from word to excel.)

1. Enter items in rows 2 – 21.

·         Only fill in Item, Category, Quantity and Unit Value. Everything else will be calculated.

·         Item – Short item description.

·         Category - A category code you create to group your items. At least two items must have the same category, and you need at least 2 categories.

·         Quantity - only numbers (Add a comment or unit type column if you want to include a unit description.)

·         Unit Value - the value of only one unit.You can just guess.

1. Format the heading cells. Bold and italicize the words in the headings, and make the background color of the heading cells gray.  Also, draw a thick border around the headings.

·         Highlight all the heading cells in row 1, but do this by highlighting the individual cells, not the row. Choose Format / Cells from the menu. Choose the font tab. Choose a bold italic font style.

·         Then, Choose the border tab and click on the heavy line picture and then click on the little box that has a border around everything, so all the lines in the "text" box are filled. If you make a mistake and put a line where you don't want it, just click on the line again to make it disappear.

·         Then, Choose the backgrounds tab and click on any color you like to set the background color for the selected cells. If you make a mistake and put a background in the wrong cell, just choose the no color box.

Note: If you want to create a macro so that you can format the heading this way whenever you want: see excelMacros.htm

1. Format the values as currency.

·         Highlight the first value. Hold the shift key down and highlight the last values, so that all the values are highlighted. Choose format / cell / number and then choose currency

1. Format the quantities and headings to be centered.

·         Click on the number 1 to highlight the first row. Click on the center button.

·         Click on the letter D to highlight the quantity column. Click on the center button.

1. Insert the Item Numbers using edit / fill / series
• Enter 1 in A2 (the first item number)
• Highlight the first item number to the last item number. (Click item number and then hold the shift key down, scroll down to item 20 and click again to highlight all item numbers.)
• Choose Edit / Fill / Series and press Enter. (Note that the numbers increase by 1 because it steps by 1.)
• If you forget to enter the 1 in A2, it wont work.

1. Make the column sizes what you want and wrap all the cells
• Grab the bar between the B & C to drag it so that most item names fit.
• Repeat for the other sections. (You may repeat this again as you add more to the spreadsheet.)

·         Click on the box between A & 1 to highlight the entire spreadsheet.

·         Choose Format / Cells and then hit the alignment tab.

·         Hit the "wrap text" button until it is checked.

1. Sort by Category, and then Item Number

·         Highlight rows 2 - 21 by placing your cursor on the # 2 and then hold the shift key and click the number 21. You want all the rows selected, not just a few cells.

·         Data / Sort and then choose Category or column C in "Sort by" and Item# or column A in "Then By". When you press okay, you will see all your items in a different order, but the same quantities should be next to them. If it is wrong, immediately choose Edit / Undo and try again. Be sure the entire rows are highlighted before you sort.

1. Calculate the total value as quantity * unit value
• Type = and then then click on the quantity in that row, and then type * and then click on the unit value in that row, and then press <ENTER>.
• Right click on that cell and choose copy.
• Highlight that cell and then hit shift and highlight the last total value cell so that all the total value cells are highlighted.
• Right click and choose paste.
• The total value of each items should be calculated.

1. Total the quantity and total value columns with a double bar above them

·         Place your cursor below the last quantity. Press the sum button (which looks like a Greek S ∑ ) and then press Enter. (You will find the ∑ approximately right over the B label for column B.)

·         Highlight that cell

·         Choose format / cells

·         Choose borders

·         Hit the double line tool

·                     Click on the line above the box so it looks like this:

·

·         Click OK

·         Copy that cell to the one under the last total value.

13.     Calculate the average and minimum for quantity, unit value and total value:

• Below the items, skip one line and type Average and on the next line type Minimum to label the numbers you will calculate.
• Calculate the average quantity by placing your cursor in the cell under total quantity.
• Insert / function / statistical / AVERAGE
• Click Next
• Click on the up arrow spreadsheet box
• Highlight the first quantity through the last one and press ENTER twice.
• Calculate the minimum quantity by placing your cursor in the cell under average quantity.
• Insert / function / statistical / MINIMUM
• Click Next
• Click on the up arrow spreadsheet box
• Highlight the first quantity through the last one and press ENTER twice.
• Copy these functions to the unit value and total value columns by highlighting the two cells you just created and right click
• Choose copy
• Highlight the four cells under unit value and total value and right click and press paste.
• It should now look like this:

14.     Under "More than 1", use an if statement to show "yes" if the quantity is more than one and "no" otherwise. (Do not just type in yes and no into the cells, and be sure at least one turns out to be yes and another no.)

• Click on the first cell under "More than 1".
• Insert / Function / Logical and double click IF
• The logical test is "Is the quantity more than 1?" This translates to is D2>1. Type "D2>1" in the logical test. (Click on the up arrow spreadsheet box so you can click on the D2 instead of typing it in.)
• Value if true = "Yes" (don't forget the quotes)
• Value if false = "No" (don't forget the quotes)
• Press <enter> twice.
• Copy this first cell to all the other rows with items.
• Change the quantity of an item to see the yes and no values change automatically.

15.     Under "Sentence about item", create a concatenation of "I have " + quantity + " of " + long description.

• Click on the first cell under "Sentence about item"
• type = "I have " &
• click on the quantity
• type &
• type " of  " &
• click on the item name
• press <enter>
• Copy this first cell to all the other rows with items.
• example: ="I Have " & D2 & " of " & B2
• Change the quantity of an item to see the yes and no values change automatically.

16.     Calculate the % of the total value of all items by dividing the total value for the row by the total value for all items.  Use one formula for all the rows by properly setting the \$ in the formula.

• Click on the first cell under "% of total value"
• type = (to tell Excel this will be a formula)
• click on the total value of that item (to put the excel address of the total cell into the formula you are working with)
• type / (to divide)
• click on the total value of ALL items (to divide by the total of all items)
• press <enter> (You should now have = F2/F<your last row>, which is the total value of one item divided by the total value of all items)
• in the formula bar, change the total value cell to have \$ before the row and before the column. (You do this to make the total of all items remain the same when you copy the formula down.)
• press <enter>
• format / cell / number / percentage & ok
• Copy this first cell to all the other rows with items.
• example: F2/\$F\$18

17.     Format the page header and footer.

·         Choose Format / Page and then the header tab

·         Click on the edit button

·         Enter "INVENTORY LIST FOR " and  your name in the middle.

·         Click on the right box and then click on the date picture (which looks like a calendar)

·         It will look like:

·         Accept it with OK.

·         Choose Format / Page and then the footer tab

·         Click on the edit button

·         Below the footer, just down arrow to choose the page 1 of ? format.

·         Click the format / print ranges and then click edit. Define the column headings by clicking on the box after "Rows to repeat at top".  Click on a heading cell (such as the category word) and then hit enter. Your Rows to Repeat at top box will then look like:

1. Set the page to print only one page wide, so you don't have to tape two pages together.
• Click the format menu/ page option and then the sheet tab
• Down arrow under "scaling mode" to choose "fit print ranges to width/height"
• choose 1 page wide by 99 high

1. Set the Margins to 7 inches from the bottom
• Choose format / page and then click the page tab
• Type 7 next to bottom

1. Accept the entire page setup by choosing OK until you see your spreadsheet again.

1. Make the Heading Cells ("Item", etc.) stay put when you scroll.

·         Click the row number 2 to highlight the entire row 2.

·         Choose windows / freeze.

·         Scroll down to be sure that row one does not move.

1. Print Preview by choosing File / Print Preview. It should print on 2 pages and have column headings at the top of each.

2. Go to the Excel inventory sheet 1 submission item in the assignment section of moodle.