06. excel charts

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