Adjusting Worksheet Layout and Data
This is a shorter section, you should use the spare time to get caught up.
- Inserting and deleting rows and columns
- He discusses how to insert rows and columns.
- And how to insert cells as well.
- If you do this properly, the formulas will adjust properly
- But do pay attention when you do!
- Hiding and unhiding rows and columns
- This is useful.
- I plan my grade book for the entire semester
- And hide columns that we have not yet used.
- And the ones that we are no longer using.
- Moving, copying, and inserting data
- He discusses how to move data within a worksheet.
- Cut and Paste
- Grab and move the box. (Drag and drop)
- Copy and paste
- He gives us several techniques to accomplish this.
- Finding and replacing data.
- Finally he discusses Find/Replace
- A big difference here is you can select a range to decrease the search space.
- The ability to narrow a search becomes more important as the size of data sets increases.
An illuminating exercise.
- Start excel.
- We will insert a table from a web page.
- Click on the DATA tab.
- Click on the Get External Data dropdown
-
- Select From Web
-
- Paste http://eartheasy.com/live_led_bulbs_comparison.html in the address bar and hit go.
- Scroll down to Comparing the top LED light bulbs for household use chart.
- Click on the yellow arrow.
- Then click on Insert at the bottom of with dialog box.
-
- This will pop up an Import Data dialog box, select OK
-
- After a moment, it should insert a table like this
-
- Clean up the table
- In mine, rows 8, 10, 12 and 14 did not import properly. Just delete them.
- Column H needs to be just numbers, so use find/replace to eliminate the "> " at the beginning and " hrs" of each field
- We want to apply conditional formatting to column F, so replace yes with 1 and no with 0
- Column G is of no use to us so delete it.
- Move Column F to between column B and C
- Move the entire table down so it starts at row 8.
-
- Remove the word "Dimmable" form column A
- Replace "LED Bulb" with "Bulb Comparison" in A8.
- Apply some formatting to the table.
- Make it look like this
-
- You may need to "Manage Rule", "Edit Rule" in conditional formatting to remove the numbers in the Dimmable Column.
- Insert an incandescent bulb for comparison between FEIT Warm White and FEIT Globe
- Bulb: Incandescent
- Price: $1.25
- Dimmable: 1
- Watts: 60
- Lumens: 800
- Incandescent Equivalent Watts: 60
- Lifespan: 800
-
- Build an assumption table in cells A2:B5
- Label the following
- A2 as Assumptions, merge this with B2
- A3 as Electricity (Per kWh)
- A4 as Period (Years)
- A5 as Total Hours
-
- We need to compute the value for B3,
- This is available at http://www.northwesternrec.com/single-phase-service-residential/
- Build the following table in cells A23:B25
-
- Compute B3 as the sum of B23:B25
- Put a 10 in cell B4
- For B5, multiply the value in B4 by 365*24
- Place a border around the entire table.
-
- Finally, hide rows 23:26
- Now we will estimate the cost of using one of these bulbs for the time period
- Add the following labels, formatted as shown
- H8: Bulbs Needed
- I8: Cost of Bulbs
- J9: kWh Used
- K8: Price of kWh
- L8: Total Cost
-
- Calculate The bulbs needed
- This is the number of hours we need (B5) divided by the lifespan of a bulb (Column G)
- But we only want integer amounts, we can't buy 1/2 a bulb.
- So we will use the roundup function
- This will round up (1.1 becomes 2)
- It takes a number to round
- And the number of decimal places to round to.
- In this case that number will be B5/G9
- And we will round to 0 decimal places.
- =roundup(B$5/G9,0)
- Calculate the price of the bulbs needed in column I.
- Number of bulbs (H) times price per bulb (B)
- Calculate the kWh needed in column J
- Hours (B5) times Watts (column D), but divide this by 1000 (convert from watt hours to kWh)
- Calculate the price of electricity in column K
- kWh (in column j) times Price per kWh (in B3)
- Finally in column L calculate the total cost
- Price for bulbs (column I) plus price of electricity (col K)
-
- Finish by copying all formulas (H9:L9) down to complete the table. (H10:L20)
- You can now change cell B4 to see what happens if you use the bulbs longer (say 20 years)
- This is my final worksheet:
-
