Chart Border Part 3 Ism3011 Usf Information Syste

By /

Chart Border Part 3 Ism3011 Usf Information Syste

Required files and instructions attached. Check final results against this video: https://www.youtube.com/watch?v=2i2-mBWF6rI&feature=youtu.be

Part 1 – Create / Download

  • Create a blank workbook.Name it using your Last name followed by your initials and _ 2EX (underscore then 2EX). For Example: WarnerBL_2EX .
  • Copy/paste the data from Ex2-Bicycles.docx , into the 2nd worksheet in your workbook.Name the tab Bicycles.
  • Adjust the column widths so that none of the data is wrapped onto 2 lines.
  • Sort the data (do not sort or remove the title/heading rows) by Bike ID.Check the sort to be sure all is correct.No additional data/formulas should be added to the Bicycles worksheet.
  • Using the data in the Bicycles worksheet, create 3 pivot worksheets
  • Create 3 worksheets and name their tabs Filter1, Filter2 and Filter 3.
  • Copy the Bicycle worksheet data into each one of the filter worksheets.
  • Filter 1 – display bicycles in the ‘children’ category with selling prices greater than $100.00
  • Filter 2 – display bicycles with names that start with ‘classic’ and ‘tea’.Add a textbox that explains how the status bar can be used to find the average cost and average selling prices of the displayed bicycles.Note the averages for these 2 fields for the bicycles displayed.
  • Filter 3 – show some interesting analytics.Add a textbox to the worksheet to explain what you are showing.
  • **Use the IFERROR function and if a user enters a Bike ID that doesn’t exist, display ‘Code Not Found’ for the title and blanks for the rest of the cells below,
  • **Protect the LookUp worksheet so that the only change a user can make is to enter a different Bike ID. They shouldn’t be able to change any other cells in the worksheet.Don’t use a password, just leave that blank.Don’t guess how to do this, if you don’t know – watch the Tips on it.Test it when you’re done to be sure we can open the worksheet and enter a new Bike ID and be sure we can’t change any other cells in the worksheet.
  • Your worksheets should be in the following order: LookUp, Bicycles, your 3 pivots and then your 3 filter worksheets.Delete any other (blank) worksheets.
  • Once a user enters a new Bike ID in the LookUp worksheet, all of the data and chart should automatically change.If any information is changed in Bicycle worksheet, the changes should be automatically reflected in Look Up worksheet.
  • Check your worksheet and be sure there are no errors or error symbols in your finished worksheet.If you don’t have this feature come into the lab to do this step.

Part 2 – Set up your 1st worksheet

Name the tab for the first worksheet, Look Up.Below is a sample of how I set up my worksheet.Use your own color scheme for your project – but include borders and shading as shown – be sure to include all of the components, as described below.

Component #1 – Title

  • Include a title with your name and any other information you think is appropriate.Merge and center it across all columns with data.
  • Below the title put the current date.Be sure you use the appropriate formula so that whenever your spreadsheet is opened, the current date is displayed (will change as the date changes).Again use the merge and center feature.
  • Add a colored border to the title and date rows (not black/ blue) & be sure the border is visible on all 4 sides (put a blank row above the title and a blank column to the left of the title so the whole border can be seen).Include a background color and font color (besides black/ blue).
  • Add an area to enter a Bike ID.Try to make it obvious to the user that this is the data entry area.Use placement, borders, and/or background colors to distinguish it from the rest of the spreadsheet.
  • Include an arrow in this section; make it a color other than black.Use the SHAPE feature in Excel to create the arrow
  • Use the VLOOKUP function/formula and search the Bicycle worksheet for the Bike ID that the user entered in Component 2.
  • Display all information for the Bike ID selected – use the same layout as in the example above.
  • Understand how relative and absolute cell references work and use them correctly in your VLOOKUP formulas(there is a TIP demonstrating this).
  • **Be sure you have a valid code in the Bike ID input area before creating your VLOOKUP formulas – or your formulas will display error messages.
  • Calculate the Markup % on the bike displayed (how much the price has been marked up from the cost – in % form).
  • Calculate and display the YTD (Year to Date) Bikes Sold (all four regions combined) – you may want to do this formula after you display the sales for each region (below).
  • Calculate and display the % of Sales Goal already met.Use conditional formatting on this cell so that if the % of Sales Goal Met is greater than 100%, a bright color font is used.
  • Using an IF statement, calculate and display the number of bicycles that need to be sold to meet the sales goal.Display a 0 if the sales goal has been met.
  • Using another IF statement, display a message if the sales goal has been met.Use a bright colored font for this message. If the sales goal has not been met, do not display anything.
  • Display the sales for each region (see example above).
  • Display the % of YTD Bike Sold for each region (see example above).This should show how much of the total YTD bike sales each region has contributed (in a % form).
  • Display the Lookup information & calculations in the same order as the example above.
  • Create the column chart displayed above:

Component #2 – Bike ID Input Area

.Component #3 – Lookup Information

Component #4 – Calculations

Component #5 – Graph/Chart

  • Select only the data needed for the chart (don’t select all data & delete items from the chart). Do not display any additional fields.
  • Display the data values above each column
  • All text should be a color besides black or dark blue (title, axis, data values)
  • The title should include the Bike Name and should change each time new bicycle information is displayed.
  • If an axis label changes in the spreadsheet, the chart should change as well.
  • Place the chart on your Lookup worksheet.
  • Use a 2-color gradient to format the columns and a background texture (not background color or image) on the chart.
  • Use rounded edges and a color (not dark blue/black) for the chart border

Part 3 – Pivot worksheets

  • The first should be a pivot table showing each category and the average Selling Price.Format the table so that your numbers have a dollar sign, two decimal places. Add a title and format it so it looks nice.Name the tab Pivot 1.
  • The second should be a pivot table and chart showing bicycles sold for each region (north, south, east and west) by category.The numbers should have commas and no decimal places and the chart should include data labels. Add a title and format it so it looks nice. Name this tab Pivot 2.
  • Add one more Pivot worksheet that shows some interesting analytics.Add a textbox to the worksheet to explain what you are showing.Include a title and nice formatting.Name this tab Pivot 3.

Part 4 – Filtering


Part 5 – Finishing Up

Check your formulas, be sure they are correct and make sense.For example, if you are subtracting 2 numbers don’t use the SUM formulas (sum is for adding).Excel may figure out what you mean, but we want the formulas to be used correctly (show that you understand how to use them).