06. excel charts
DESCRIPTION
06. Excel Charts. File -> Open -> 06b-datastart.xlsx. Click on column header A to select the Date column. While holding Ctrl on your keyboard, click on column header E and F. Click on Insert -> Line -> 2-D Line. Displays a line graph of the indices both starting at 1,000. - PowerPoint PPT PresentationTRANSCRIPT
06. Excel Charts
File -> Open -> 06b-datastart.xlsx
Click on column header A to select the Date column
While holding Ctrl on your keyboard, click on column header E and F
Click on Insert -> Line -> 2-D Line
Displays a line graph of the indices both starting at 1,000
Right-Click with mouse on left axis and click on Format Axis
Choose Axis Options -> Major Unit Fixed 20000
Choose Number -> 0 Decimal places
Tick box for Use 1000 Separator
Click Close
Right-Click with mouse on X axisand click on Format Axis
Click on Number and change Format Code to yyyy
Click Add and then Close
Right-Click on Legend and click Format Legend…
Untick box which saysShow the legend without overlapping the chart
Select Fill -> Solid Fill -> White
Select Border Color -> Solid Line -> Black
Click Close
Click on Legend and drag to empty space on top left
Right-Click on Plot Area and select Format Plot Area …
Select Fill -> Solid Fill -> White
Select Border Color -> Solid Line -> Black
Click Close
Right-click on Chart Area and select Format Chart Area …
Click Fill -> No Fill
Click Border Color -> No Line
Click Close
Now have a neatly formatted chart that can be copied to Word or Powerpoint
Custom Charts
Click on Chart
Click Design -> Save as Template
Save as CustomLineChart
Click Save
Click on Chart and press DEL on keyboard to delete
Click on column header A to select the Date column
While holding Ctrl on your keyboard, click on column header E and F
Click on Insert -> Line -> All Chart Types
Click on Templates and your custom chart
Click OK
Produces a chart using your template
Click on chart and press DEL on keyboard
Challenge:Create a scatter graph of returns
We often want to find how the daily changes in one company are related to changes in the S&P500
Type AppleReturn in H1
Type SPReturn in I1
In H3 type=(B3-B2)/B2
In I3 type=(C3-C2)/C2
Select cells H3 and I3 and press Ctrl+c on your keyboard
Paste formula down remainder of dataset
Click on column headers H and I
Click on Format -> Format Cells…
Click on Percentage -> 2 Decimal Places
Click OK
Click on column header A, then hold Ctrl on your keyboard and select columns H and I
Click on Insert -> Scatter
Click on Chart
Click on Layout -> Horizontal (Value) Axis
Click on Format Selection
Click on Axis Labels: Low
Click Close
Choose Layout -> Series “AppleReturn”
Click Format Selection
Choose Marker Options -> Built-in -> Size 3
Click Close
Choose Layout -> Series “SPReturn”
Click Format Selection
Click Marker Options -> Built-in -> Size 3
Click Close
File -> Save As -> 06c-datacompleted.xlsx
Challenge
• Format the rest of the graph to make it neater– Date format on axis– Legend– Plot area– Chart area
• Save as CustomScatterGraph