Excel Chapters 345

1
Excel Chapters 3/4/5

• Sean Banerjee
• sean.banerjee_at_mail.wvu.edu

1
1
2
Upcoming Deadlines

• Homework 1 is due on February 6
• MyITLab Lesson 2 is due on February 9
• Homework 2 is due on February 13

3
Cell References

• Absolute A1
• Relative A1
• Column Absolute A1
• Row Absolute A1

4
Agenda for today

• More formatting
• Working with multiple worksheets
• Conditional formatting
• Excel Charts
• Excel Chart Trendlines

5
Example file pricetrends.xlsx

• Rename Sheet2 to Gas Expenses
• Merge and center cells A1 F1
• Enter Gas Expenses 1990 2006 in cell A1
• Format cell A1 as Heading 1 Cell Style
• Select cell A2 and apply Table Style Medium 1.
• Note you have to edit the cell range!

6
Example file pricetrends.xlsx

• Select the option for Total Row and First Colum
• Total Row adds in a final row to calculate totals
• First Column shades the first column in a
  different style
• Make sure you convert to range by selecting
  Convert to Range
• Change cell A851 to Average
• Enter AVERAGE(B3B850) in cell B851. Autofill to
  cells F851

7
Example file pricetrends.xlsx

• In cell C3 enter a formula to calculate the fuel
  cost for Sheetz
• Step 1 enter (
• Step 2 select B3/
• Step 3 select the Gas Prices sheet
• Step 4 select cell C1
• Step 5 enter
• Step 6 select cell B3, hit F4 to convert it to
  an absolute reference. (Why did we do this?)
• Step 7 enter ) and hit the ENTER key

8
Example file pricetrends.xlsx

• When working with multiple sheets, be careful
  when selecting a different sheet as every
  keystroke is logged
• From the previous page, the formula we calculated
  is
• (B3/'Gas Prices'!C1)'Gas Prices'!B3
• What this indicates is we are dividing cell B3 by
  the contents in cell C1 in the Gas Prices sheet
  and then multiplying the results with cell B3 in
  the Gas Prices sheet.

9
Example file pricetrends.xlsx

• Autofill the formula from cell C3 to cells C4 to
  C850
• Enter a formula in cell D3 to calculate the Fuel
  Cost (Exxon)
• (B3/'Gas Prices'!C1)'Gas Prices'!C3
• Autofill the formula from cell D4 to cell D4 to
  D850
• Calculate the Savings in cell E3
• C3-D3

10
Example file pricetrends.xlsx

• Autofill the formula from cell E3 to cells E4 to
  E850
• Enter a formula in cell F3 to calculate the
  Savings in dollars
• E3/100
• Autofill the formula from cell F3 to cells F4 to
  F850

11
Example file pricetrends.xlsx

• We wish to conditionally format cells E3 to E851
  based on the following conditions
• If the savings are greater than 5 change the cell
  fill to red with white font color
• If the savings are less than -5 change the cell
  fill to green with white font color

12
Example file pricetrends.xlsx

• Select cells E3 to E850
• Click on Conditional Formatting and then New
  Rules
• Select the option for Format Only Cells that
  Contain
• Change between to less than and enter -5 for the
  value
• Click on Format, pick a red fill and white font
  color
• Click on Conditional Formatting and Manage Rules
• Click on New Rule
• Select the option for Format Only Cells that
  Contain
• Change between to greater than and enter 5 for
  the value

13
Example file pricetrends.xlsx

• A note on conditional formatting
• As you add rules they get saved
• If you create an incorrect rule, applying a new
  rule will only be a BandAid fix
• If you do create an improper rule, click on
  Conditional Formatting and select Manage Rules
  then delete each improper rule
• If you dont delete the incorrect rules, your
  conditional formats may not work right

14
Example file pricetrends.xlsx

• Now, we want to create a chart to show the
  expenses for both Sheetz and Exxon. Sadly, we
  cant chart it simply by selecting the cells.
• Select cells C2 to C850, then select Insert 2d
  Line Chart. Move the chart to a new sheet called
  Prices Chart
• Now Right click on the chart and hit Select Data
• Hit Add and specify Fuel Cost (Exxon) for the
  series name and select cells D3 to D850 for the
  values from the Gas Expenses sheet.
• You can add individual series using the method
  outlined above!
• NOTE this method is used primarily when dealing
  with charts that display date/time on the lower
  axis

15
Example file pricetrends.xlsx

• Our lower axis does not display the date, it
  simply shows numbers from 1 to 841
• Right click on the chart and again hit Select
  Data
• If you look to the right you will see Horizontal
  (Category) Axis Labels this is your lower axis
• Click on the Edit button
• Select cells A3 to A850 from the Gas Expenses
  sheet
• Click on Design and add in appropriate Axes
  Labels and Chart Titles
• Use Gas Expenses Exxon vs Sheets for the title
• Use Weeks as the lower axis label
• Use Weekly Expenses as the vertical axis label

16
Example file pricetrends.xlsx

• While charts are helpful for displaying
  information contained in our data, sometimes we
  want to forecast our data to the future or the
  past
• This is commonly called a trendline it is used
  in weather forecasting, stocks, etc
• Select the Fuel Cost (Exxon) line and right click
  and select Add Trendline
• Trendlines must not only fit our CURRENT data but
  also forecast a plausible future value. You can
  select the trendline (in this case polynomial)
  and specify a Forecast forward for 800 units

17
Next Class

• More Conditional Formats
• More Charts
• More Functions
• What if Analysis
• Goal Seek