Excel HW1, Ch' 5 and More Charts

1
Excel HW1, Ch. 5 and More Charts

• Sean Banerjee
• sean.banerjee_at_mail.wvu.edu

1
2

• Reminders
• TAIT 6/7 is due on June 3
• TAIT 8 is due on June 6
• TAIT 9 is due on June 10
• TAIT 10 is due on June 12
• Homework 2 is due on Monday June 4th
• Exam 1 (Excel) is on June 6
• pre-test is the password for the pre-test
• post-test is the password for the post-test
• If you get less than 85 on the pre-test you
  must do the training and the post-test, otherwise
  move on to the next lesson
• Note please do not use the TAIT email, I dont
  get those emails.

3

• Homework 2 covers the following topics
• Importing data (csv files)
• General formatting
• Conditional formatting
• Working with multiple work sheets
• Charts and trendlines
• Critical thinking questions

4

• Importing a .csv file
• Go to DATA IMPORT EXTERNAL DATA IMPORT DATA
• Browse to the location where your file is
• Select the correct file
• Make sure delimited is checked in Step 1
• Make sure Comma is checked in Step 2 (uncheck
  Tab)
• Hit Finish
• Specify the correct cell

5

• Homework 1 Discussion
• Importing data remember to right click and
  save the files
• Converting to range if it did not convert to
  range you had the wrong version of Excel
• Rank function not too many issues there
• Sum function not too many issues there
• Conditional formatting - first one was easy,
  second one had to be greater than 0.1 or less
  than -0.1
• General formatting not too many issues there
• Critical thinking questions could have been
  done better

6

• Charts Worksheet References Example
• Say we have data from 1990 2006 of gas prices
  in one sheet
• In another sheet we have distance traveled
  annually and the fuel economy
• First we can calculate the total gallons of gas
  purchased
• We can then calculate the total annual cost
• We can create a chart based on the data we have
• What trendline should we pick? Why?

7

• Beyond worksheet references
• Can we reference external Excel files? Yes
• How? Same way as referencing a different
  worksheet. Instead of Atlanta!B3 it will say
  Atlanta.xlsSheet1!B3
• Example well use yesterdays Atlanta.xls,
  Boston.xls and Chicago.xls files

8

• Beyond worksheet references
• Create a new blank file called Summary.xls
• Open all three files at the same time, you can
  do this by holding down Ctrl
• Set the window so that its tiled
• Now create the function by hitting and then
  selecting the data you want
• Issues and limitations with this method? If you
  delete one of the files youve just lost all the
  data. But, you can change a single file and it
  automatically updates the rest of them.

9

• Next class
• Excel Chapters 6 and 7
• Please start on the homework its due in a little
  more than 48 hrs.