INVENTORY LIST STEPS FOR SHEET 1

 

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

 

SHEET 1:

 

 

Step by Step Guide for SHEET I:

 

  1. Create a new Excel spreadsheet.

         Press Start / Program / Microsoft Excel (and you may need to first select Microsoft Office).

         Choose round Microsoft start button / New / Workbook to create an empty Excel spreadsheet.

         Choose office button / "save as" and then choose Excel 97/2003 format.

         Enter your name as the spreadsheet name and save.

 

  1. Set the sheet label to "Simple List"

         Double click on the word "sheet1" at the bottom of the screen.

         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 Home tab, cells pane, format choice and then format cells option. 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 outline box and then the inside box, 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 Alignment tab and click "wrap text" so the words don't bleed into other cells.

         Then, Choose the fill 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.

        


         Your spreadsheet should now look like:

 

 

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 Home tab, cells pane, format choice and then format cells option. Then choose the number tab 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 home tab / editing pane / fill (or down arrow) command.

 

 

 

  1. Make the column sizes what you want and wrap all the cells

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

         Choose Home tab, cells pane, format choice and then format cells option.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.

         Choose the data tab and then choose the sort command. Press the down arrow by the "sort by" box and choose the "category" label or column C. To then sort by item number within the category, click the "add level" button and then down arrow next to the second "sort by" box and choose either "item number" or Column 1. 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

 

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

         Place your cursor below the last quantity. On the formula tab, press the auto-sum button (which looks like a Greek E) and then press Enter. You will now have the total.

         Highlight that total cell

         Choose the home tab, cells pane, format command, format cells option.

         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:

 

 

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.)

 

 

 

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

 

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.

 

17.     Format the page header and footer.

         On the page layout tab, find the page setup pane. Right after the words "page setup" is a little square dialog box. Click that box.

         Click the Header/Footer tab.

         Click on custom header.

         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.

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

 

 

 

 

  1. Set the column headings:

         Click the sheet tab (in page setup) and then click on the box after "Rows to repeat at top". Click on the row number 1 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.

 

 

  1. Set the Margins to 7 inches from the 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 view tab/ freeze pane command.

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

 

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

 

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

 

Your spreadsheet should look like this at the top (with your own items):

 

 

 

 

And like this at the bottom: