Notesale: Turn your study into money

Document Preview

Extracts from the notes are below, to see the PDF you'll receive please use the links above

---

IT Services

Microsoft Excel 2007™
Charts (Level 3)

Contents
Introduction
...
2
Creating a Chart
...
3
Area and Line Settings
...
5

The Chart Tools Format Tab
...
7
The Labels Group
...
8
The Background Group
...
9

The Chart Tools Design Tab
...
11
Combination Charts
...
12
Templates
...
13
Chart Layouts
...
14

Themes
...
15
Copying Charts from Excel into Word
...
15
Preserving Colours with Excel 2003
...
15

Introduction
Microsoft Excel's charting capabilities are excellent
...
This course is designed not just to show you some of the more obvious
features (such as Chart Type) but also some of the more obscure
...
Start up Microsoft Excel as usual (or press for a new workbook)
2
...
Drag the cell handle down to cell A12 – the cells should all show the number -1
4
...
In this first exercise you’ll see exactly how Excel
uses this data to do this:
1
...
Move to the Insert tab and note the different types of chart available in the Charts group
3
...
You’ll see later how to place charts on
their own separate sheets
...
All twelve values are plotted
b
...
The horizontal (x-) axis is labelled 1, 2, 3 … 12 (reflecting the row numbers) and is equally spaced
d
...
The vertical (y-) axis is labelled from -2 to 12 in steps of 2
You’ll see later how to change some of these settings
...
Type xxx into cell A1 (and press )
You’ll find that xxx is interpreted as another zero value (the first two column divisions are both empty) and
that the vertical axis is automatically redrawn as there are no longer any negative values
...
Press to select the data again then repeat steps 2 & 3
A second chart appears (placed over the old one) but this time:
a
...
The value in cell A1 is used both for a chart title and as a label in the legend on the right
c
...
Move the mouse cursor into the chart and then hold down the mouse button
7
...
Drag the old chart to the left, so you can compare the two, then it
Next, try deleting and adding some data:
9
...
Select cells A4 to A6 then right click and choose Insert…
11
...
Press to [Undo] the last command and remove the new values
13
...
Press to [Undo] the last command and remove the new values
This last exercise demonstrated that Excel knows which cells contain the cell range being plotted and that
only changes made within that range are reflected in the chart
...
Click on cell B1 and type yyy (press )
16
...
Drag through cells B1 to B11 then right click and Copy them
18
...
Another change is that the chart title has
disappeared (and will have to be added manually)
...
Repeat step 18 and you’ll find that a third column appears – a second set of yyy values
20
...
These
include:


The Chart Area and Plot Area



The Chart Title



The Legend



The Axes and Axis Titles



The Series (and Data Values)



The Gridlines

To see exactly where these elements are:
1
...
Click on the list arrow attached to the right of the [Chart Elements] button – the top one in the far left
Current Selection group
3
...


Area and Line Settings
The following instructions apply to both the Chart Area and Plot Area (and, indeed, to any area on a chart –
here, the coloured columns, for example)
...
Click on the [Chart Elements] button, select Chart Area, then click on the [Format Selection] button
below (you can also right click on an element to Format it) – a dialog box appears:

3

An equivalent dialog box appears for all the other elements too, with similar options from which you can
choose
...
If you use the Drawing Tools,
the instructions which follow should be very familiar to you (most are exactly the same)
...
Drag the dialog box to the left so you can see most or all of the chart
3
...
Click on Color and select a different Standard color – your can access More colors… if necessary
5
...
Change the fill option to Gradient fill – a different set of options appear
7
...
Change the fill option to Picture or texture fill – here you can use any clip or your own picture for the
chart area, or a pre-defined texture
9
...
Explore the Tiling options which let you move the texture around and rescale it
11
...
End this section by changing the fill option back to Automatic
Next, look at the Border Color and Style
...

13
...
Solid line is very similar to the Solid fill you saw earlier, and Gradient line matches Gradient fill
15
...
Click on Border Styles on the left of the Format Chart Area dialog box – the following options appear:

17
...
The Cap type:, Join type: and Arrow settings aren’t really applicable here, but try Rounded corners
The other settings (Shadow and 3-D Format) also don’t really apply here, so:
19
...
Press to [Undo] any changes you don’t like
4

Axis Settings
Next, look at the axis settings:
1
...

Starting with the Axis Options:
2
...
Under Interval between labels: set Specify interval unit: to 2 click on another option and the axis is now labelled 1, 3, 5 …
4
...
Vertical axis crosses: lets you move the vertical axis so that it
crosses at a value other than 0 – try At maximum category
6
...
Label distance from axis: moves the
labels closer to or further away from the axis
...
Major/Minor tick mark type: lets
you position the tick marks relative to the axis
...

7
...
Click on Alignment on the left of the Format Axis dialog box – here you can change the direction of the
labels to read up/down or, indeed, at any angle between these settings
9
...
Click on [Chart Elements] and choose Vertical (Value) Axis then on [Format Selection]
The options here are the same as before except for the Axis Options
...
To change the settings:
11
...
For Major unit: click on Fixed and set this to 2
13
...
Change Minor tick mark type: from None to Outside
Tick marks should now appear at values of 1, 3, 5 etc
15
...
Next, change the data value in B7 to 15 – the chart shows it as 10 (the maximum axis value)
17
...
Repeat step 11 setting Maximum back to Auto to cater for any larger values automatically
19
...
Click on the chart then move to the Format tab and click on [Chart Elements]
2
...
Set Series Overlap to Overlapped (100%) and the two columns are drawn on top of each other
4
...
Set Gap Width: to Large Gap (500%) and the columns become very thin
6
...
Set Series Overlap to 0% (you can also type in values) and the columns completely fill the axis
8
...
Set Gap Width: back to 150% and reset Plot Series On to Primary Axis
10
...
Use the key to move along the series to a particular point (you could also click on it
with the mouse) – the handles are now showing on just the one column
12
...
View the Fill options and change the colour of the column by choosing Solid fill and a Color
14
...
Click on [Chart Elements] and choose Vertical (Value) Axis Major Gridlines
2
...
Move to the Line Style options and change Dash type: to a dashed line
4
...
Press and the gridlines disappear - press to [Undo] the deletion
There are other elements, not present on this particular chart, which will be looked at later
...
The other groups are identical to
the Drawing Tools Format tab
...
You may prefer using these buttons instead:
6

1
...
Next, click on the list arrow at the foot of the scroll bar on the right of the [Style] (Abc) buttons
3
...
If you have chosen a style, press to [Undo] it
If you want to set your own style, you do so using the three buttons to the right:
5
...
Next, do the same with the [Shape Outline] button
7
...
If you have made any changes, press to [Undo] them
The buttons in the WordArt Styles group are all greyed out, as you aren’t using any WordArt here
...
The final group is Size:
9
...
Click on the group arrow on the right of the Size group to display the Size and Properties dialog box
11
...
Press to [Close] the Size and Properties dialog box
13
...


The Chart Tools Layout Tab

The Labels Group
1
...
The second group gives you
access to the various shapes and text boxes (if you want to annotate your chart)
...
Your current chart has no labels, so:
2
...
Type Test Title then press - your typing is now shown in the placeholder
Note that you can use the mouse to drag the title (and some other elements) to any position you like
...
Click on the [Chart Title] button again and choose More Title Options… – the Format Chart Title
dialog box appears (similar to those seen previously) but note that it has no font settings
5
...
Use the [Grow Font] and/or [Shrink Font] buttons to change the size of the font title

The buttons here can also be used to change the font, font colour, bold/italic etc
...
Click on the [Axis Title] button and choose Primary Horizontal Axis Title then Title Below Axis – an
Axis Title placeholder is added below the x-axis
2
...
Repeat step 1 but choose Primary Vertical Axis Title then Rotated Title
4
...

5
...
Click on the [Legend] button and note the preset alternative positions –Show Legend at Right is the
default and doesn’t need to be changed
2
...
Press to [Close] the dialog box then right click to change any Font settings
Excel lets you format not just the whole legend but individual entries within it:
4
...
Right click and choose Format Legend Entry… - the Format Legend Entry dialog box appears
6
...
Click on the [Fill Color] button and choose No Fill – the columns disappear from the chart
8
...
Click on the [Data Labels] button and choose Outside End – numbers appear above the top of each
column showing the data values in cells A2 to B11
Again, each set of labels can be positioned (or formatted) differently:
2
...
Change the Label Position to Inside End then press to [Close] the Format Data Labels dialog box
You can even position (or format) an individual label:
4
...
Now, repeat steps 2 and 3, changing the Label Position to Center
6
...
Press twice to undo the changes to the colour and position of the label
If you want to display the data in tabular form on the chart, add a Data Table:
1
...
Click on [Data Table] again but choose More Data Table Options… a dialog box appears
3
...
End by hiding the Data Table – click on the [Data Table] button and choose None

The Axes Group
The buttons in the Axes group control both the axes and gridlines:
1
...
Instead, have a look at the vertical axis settings:
2
...
Having noted the effect, press to undo the log scale
8

Next, have a look at what changes you can make to the gridlines:
1
...
Repeat step 1 but choose Primary Vertical Gridlines then Major Gridlines – vertical lines appear
3
...
The major and minor gridlines can be formatted separately by selecting them before issuing the
command
...


The Background Group
Most of the buttons in this group are currently greyed out
...
The only active button, [Plot Area], lets you clear the fill from the Plot Area
...
First, right click on the Chart Area, choose Format Chart Area… and a Solid fill – press
2
...
Press twice to [Undo] the changes

The Analysis Group
The buttons in the Analysis group let you add trendlines (best fit lines) and error bars to your chart
...
Click on the [Trendline] button and choose Linear Trendline
2
...
Here, the line fits perfectly as the data values increase
regularly by one each time
...
Don’t
worry if you know nothing about this – just be aware that regression works out the best fit, based on the data
...
Change the data value in cell A2 to 5 – note how the line moves to best fit the new value
4
...

Such lines can even be used for forecasting extra values:
5
...
Press to [Undo] the extra values
7
...
You can also forecast both backwards and
forwards and you can display the equation of the line:
8
...
Under Forecast, set Forward: to 4 periods
10
...
Also turn on Display R-squared value on chart
12
...

13
...
Again, these will be well-known to some users
but a complete mystery to others
...
Click on the [Error Bars] button and choose Error Bars with Standard Error – up and down bars are
added at the top of each column
By choosing Standard Error, you have equal-sized bars on each column (the Standard Error is a fixed value)
...
Repeat step 1 but choose Error Bars with Percentage – the bars are different sizes this time, each
being 5% of the data value (ie for 10 both the up and down bar is 0
...

3
...

Usually you want both Minus and Plus but you don’t have to
...
Choose the option Custom then on [Specify Value] – another
dialog box appears
5
...
Repeat step 5 for the Negative Error Value (deleting the
current setting) then press for [OK]
7
...
Now type the following data into cells C2 to C11:
1 1 2 2 1 3 2 0 1 0
You’ll find these values are used for the error bars
...
Here you can choose a different
sort of chart and can make use of built-in styles
...
First, click on the chart then on the Design tab to view the available buttons
2
...
Choose New Sheet and change the name from Chart1 to My Chart – press for [OK]
Your chart is moved to a separate sheet, filling the whole screen, which makes it much easier to see what’s
going on
...
This lets you
change the type if you selected the wrong type when you first created the chart
...
Click on the [Change Chart Type] button – the following dialog box appears:

2
...
Move to the Layout tab and click on the [Chart Elements] on the far left – note how the list has grown
4
...
Investigate some of the other options, if you like, then press to [Close] the dialog box
6
...
Click on the [Change Chart Type] button and choose [Pie] (the first in the Pie group) and click on
[OK] – you’ll find that a pie chart can only plot one data series at a time
8
...
Move the mouse cursor over any slice and, holding the mouse button down, drag the slice in as far as
you can – all the slices come together again
...
Click on any slice (to select that slice) and drag it outwards – each slice can be exploded individually
11
...
Right click on any of the bubbles and choose Format Data Series…
13
...


Combination Charts
Excel lets you combine certain chart types onto a single chart – for example you could have both a column
and a line
...
Move back to the data – click on Sheet 1
2
...
Choose [Stacked Line] (the second in the top row) - a chart appears, as before
4
...
Right click on the chart and Paste in a second set of xxx values – the line adds the new values to the old
11

6
...
Select [Clustered Column] (the first one in the Column group) then press for [OK]
You now have a combination chart
...
To prove this:
8
...
It has been simply numbered
1to 10, which essentially indicates the data row in the data set (ie data values in A2 and B2 are in row 1)
...
First, select your data – here select A1 to B11
2
...

Note the gap between the first two points and also how the points are plotted at their exact position on the
horizontal axis
...
Change the value in cell A11 to 11 – the last point moves further along the axis
4
...
Right click on the chart and Paste in a second set of y-values (these are plotted on top of the first)
6
...
Choose [Clustered Column], as before, then press for [OK]
You’ll find that the columns don’t all appear where you want them – they aren’t making use of the horizontal
axis
...

8
...
Right click on any of the new values and choose Format Data Series…
10
...
Right click on the new axis on the right and choose Format Axis…
12
...
Here, you don’t need the duplicate series so:
13
...
Move to the Layout tab, click on [Error Bars] and choose Error Bars with Standard Error –both
horizontal and vertical error bars appear
15
...
This lets you set up
the exact style of chart you want for a set of charts, which can then be used for all of them
...
Right click on the Chart Area of the current chart and Format Chart Area…
2
...
Right click on the Chart Title and change the [Font Colour] and [Font Size]
4
...
Give the template a name (eg My Template) then press for [Save] – note that is goes into
a special folder and that this is not retained on the IT Services lab PCs (it would be on your own)
12

6
...
Now, click on the [Change Chart Type] button, choose Templates then select My Template – press
for [OK] – your chart area and title should now match the Scatter chart

The Data Group
The two buttons in the Data group let you change the data being used for the chart
...
Click on the [Select Data] button – the following dialog box appears:

2
...
With the Select Data Source dialog box still open, click on [Edit] – another dialog box appears:

4
...
Note the change to the chart then [Cancel] the new X series
6
...
Click on cell B1 to set the Series name: to =Sheet1!$B$1 a
8
...
Now drag through cells B2 to B11 to set this to =Sheet1!$B$2:$B$11 - press for [OK]
10
...
Finally, click on [Switch Row/Column] – the graph goes crazy (press for [Undo])
The Switch Row/Column button does have its uses
...
By default, Excel expects your data to be in rows
...

13

1
...
Now click on the [More] arrow at the bottom of the Chart Layout scroll bar to see further layouts
3
...
Again, these are pre-defined styles which
help make colouring your chart simple and which give a very professional look:
1
...
Now click on the [More] arrow at the bottom of the Chart Styles scroll bar to see further styles
3
...
Click on the [Change Chart Type] button and select a [Pie] chart – press [Enter] for [OK]
If there is a particular style you like but would also like to keep your own formatting, you can:
5
...
Right click on the preferred [Chart Style] then choose Apply and Maintain Formatting – your Chart
Area and Title settings should be preserved but other elements will change to reflect the chosen style
7
...
To change the
colours:
1
...
Move to the Page Layout tab and click on the [Themes] button
3
...
Choose the theme you like best then click on the [Colors] button to change that theme’s colours
To see how the colours in the theme are defined:
5
...
Type my theme to replace Custom 11 as the Name:
7
...

8
...
Click on [Save] to save your theme
You’ve finished exploring charts now, so:
10
...
If you hadn’t done so already, make sure your chart in Excel is on a Chart Sheet
so that the resolution is at a maximum
...

The one disadvantage is that the chart is now a picture and cannot be activated from Word
...


Problems with Chart Colours in PowerPoint
Problems also may arise when you copy a chart into PowerPoint 2007
...
You either have to select the same colour scheme in PowerPoint or
explicitly set the colours in Excel (ie change them so they refer to actual colours not Accent 1, Accent 2 etc)
...
You then can’t
activate the chart from PowerPoint, but maybe you don’t want to
...
You can fix the colour conversion between the new and old versions via the setting Choose what colours
will be seen in previous versions of Excel on the Save tab in Excel Options (from the Office Button)
...


Pattern Fill
You may well want to use Pattern Fill to add shading to your charts (especially if you are printing them in
black and white)
...
Sadly, this feature was dropped from Excel 2007 but can be accessed via an Add-In
...

The Add-In can be downloaded from http://www
...
info/charts/patternfills
...


™ Trademark owned by Microsoft Corporation
...

Copyright © 2009: The University of Reading
Last Revised: August 2010
15



---

Title: Excel
Description: Microsoft Excel has the basic features of all spreadsheets,[4] using a grid of cells arranged in numbered rows and letter-named columns to organize data manipulations like arithmetic operations. It has a battery of supplied functions to answer statistical, engineering and financial needs. In addition, it can display data as line graphs, histograms and charts, and with a very limited three-dimensional graphical display. It allows sectioning of data to view its dependencies on various factors for different perspectives (using pivot tables and the scenario manager).[5] It has a programming aspect

Buy These NotesPreview