Posted: October 31st, 2023

Create two worksheets within the same Excel file

We have already learned the differences between various devices. Now, it is time to compare
them to determine which device is best for us. You will do this using MS Excel. Your Excel
spreadsheet must include the following:
1. Create two worksheets within the same Excel file:
o Rename the first worksheet with your last name. Apply a tab color.
o Create a second sheet. Rename it “Source.” Apply a different tab color.
2. TITLE: In cell A1 type your name, CGS 1000, the 5 digit section number, and “Excel
Project.” (example: Marcie Yordy – CGS 1000-12345 – Excel Project). Merge and Center
from A1:N1. Apply formatting other than Excel default. Apply a shade behind it.
3. You will create a series of 6 tables. The first spans cells A3:N7. Type the following:
o In A3 type “Device Comparison Table.” Merge and Center from A3:N3. Apply
formatting other than Excel default.
o From B4:N4 type the following COLUMN titles:
▪ Make
▪ Model
▪ Operating System
▪ CPU Type
▪ Storage
▪ RAM
▪ Monitor Size
▪ Touch Screen
▪ Rating
▪ Full Price
▪ Current Price
▪ Discount
▪ Trend
o Set each column’s width to “15.”
o Apply Wrap Text
o Bold each column name
o Apply a shade to the column headings
o In cells A5-A7 type the following ROW titles:
▪ Desktop
▪ Laptop
▪ Tablet
4. Add borders to the table
5. Fill in the table’s data. Do a web search for a Desktop, Laptop, and Tablet of your
choosing.
o Add the URLs for where you pulled the information to the Sources sheet.
6. Create a table for Input Device #1:
7. In A9 type, “Input Device #1.” Merge and Center from A9:D9. Apply formatting other
than Excel default.
o In B10, C10, and D10, type the following COLUMN titles:
▪ Product
▪ Rating
▪ Price
o In A11, A12, and A13 type the following ROW titles
▪ Desktop
▪ Laptop
▪ Tablet
8. Fill in the table’s data. Do a web search for an input device that is compatible with your
selected systems.
o Add the URLs for each product to the Sources sheet.
9. Repeat Steps 7 and 8 to create the table for the second Input Device, and both Output
Devices.
o Input Device #2 – table spans from F9:I13
o Output Device #1 – table spans from A18:D22
o Output Device #2 – table spans from F18:I22
10. Create a Total Costs table:
11. In K9 type, “Total Costs.” Merge and Center from K9:O9. Apply formatting other than
Excel default.
o In L10, M10, N10, and O10, type the following COLUMN titles:
▪ Current Price
▪ Input 1 & Output 1
▪ Input 2 & Output 2
▪ Sum of Current Price, Inputs & Outputs
o In K11:K16 type the following ROW titles:
▪ Desktop
▪ Laptop
▪ Tablet
▪ Minimum
▪ Maximum
▪ Average
▪ Add shading to the Minimum, Maximum, and Average rows
12. Once the tables are created and populated with data, it is time to add our formulas
13. In M5:M7 create a Subtraction Formula to subtract the Current Price from the Full Price
14. In N5:N7 add Sparklines to show the trend in price.
o Show the High Point and Low Point
15. In cells L11:L13 use Absolute Cell References to populate the Current Price of each
computer
16. In cells M11:M13 use the Addition Formula to add the total of Input 1 to Input 2 for
each computer
17. Repeat step 16 to add the total of Output 1 to Output 2 for each computer in N11:N13
18. In cell O11, use the SUM Formula to add L11:N:11
19. Repeat step 18 to create the SUM for O12 and O13
20. In cell L14, use the Minimum Formula to find the minimum price of cells L11:L13
o Repeat step 20 to find the Minimums for M11:M13, N11:N13, O11:13
21. In cell L15, use the Maximum Formula to find the maximum price of cells L11:L13
o Repeat step 20 to find the Maximums for M11:M13, N11:N13, O11:13
22. In cell L16, use the Average Formula to find the average price of cells L11:L13
o Repeat step 20 to find the Averages for M11:M13, N11:N13, O11:13
23. Create a Text Box in cell K18
o In this box, list what you would purchase and write your rationale for why chose
to purchase those items
o Make the box as big as needed to contain your rationale
o Add an outline color to the text box.
24. Save your workbook and submit to the Dropbox.