The purpose of this exercise is to learn how to produce an excel chart and adapt its presentation to given format request. While following the training session, the employee creates a chart which must be used as a validation document. Attention: while creating the chart, the employee must choose different colors for the chart than those used in the training session.

Requirements

Before you start, make sure you followed the training session: http://switzernet.com/2/support/100813-excel-chart-training/

Training session

The final result that must be reached is shown on the image below.

Save the following Excel file in your PC: [xls].

Save the data in the Excel file and click on icon in order to construct a chart:

Choose Column -> Stacked Column ->click on Next

For Data Range write =Rates!$C$1:$F$55 , click on next.

Go to Axes tab, and remove the checkbox for “Value (Y) axes”

Go to Gridlines tab and select the checkbox for Category (X) axis Major gridlines

Go to Legend and select the checkbox for “Show legend”, and select option “Top”.

Go to Data Labels and select the checkbox for “Value”.

Click on Finish.

You will get following chart.

Increase the size of the chart by clicking on the right top corner of the chart and moving your mouse.

Format Plot Area

Double click on grey area, and format the Plot Area.

Format Category Axis Major Gridlines

Double-click on any vertical black line, select Custom, and for color select Ice blue, click OK

Format Value Axis Major Gridlines

Double-click on any horizontal black line, select Custom, and for color select Ice blue, click OK.

Format Category Axis

Double click on any provider name, go to Font, select size 10, and click OK.

Format Data series for rates to mobiles

Double click on any brown area (but not on the number).

(1) For border choose Custom, Color=Gold, Weight = dashed

(2) For area click on fill Effects, select “two colors”. Color 1 = Light Yellow. Color 2 = Gold. Sharing styles = Horizontal, for Variant select the bottom left corner one.

Click OK (for fill effects only)

(3)Go to Options, choose Overlap =100, Gap width = 8

Click OK.

Format Data series for rates to landlines

Double click on any blue area (but not number).

(1) For border, choose Custom, Color=Bright Green, Weight = dashed

(2) For area, click on fill Effects, select “two colors”. Color 1 = Light Green. Color 2 = Bright Green. Sharing styles = Horizontal, for Variant chose the bottom left corner one.

Click OK (for fill effects)

Click OK (for format data series)

Format Data Labels for rates to mobiles

Double click on any value of mobile rates.

On Font tab select Size =9, Color=brown

On Alignment tab for Orientation write 90 degrees

Format Data Labels (for landlines)

Double click on any value of local rates.

On Font tab select Size =9 , Color=brown

On Alignment tab for Orientation write 90 degrees

Format the legend

(1) Move the legend from top to left top corner.

Double click on legend

(2) In Pattern tab for border choose “None”, for area choose “None)

(3) In Font tab for Size choose “10”

Click OK

As the legend area is now free, increase the plot area:

Adding text boxes

Add 5 text boxes:

Click on “Add text box “ from the drawing panel:

Textbox 1 - in English : Rates of all Swiss operators to Antigua/Barbuda mobile and proper (cts/min)

Color=brown, size =12

Textbox 2 - in French : Rates of all Swiss operators to Antigua/Barbuda mobile and proper (cts/min)

Color=indigo, size =12

Textbox 3 - source: www.comparis.ch call ~ appel: 1m31s date: 2011-04-17

Color=automatic, size=11, text alignment, horizontal = right

Textbox 4 - Swiss phone operators (V=VOIP, F=Fixed/Landline)

Color=brown, size =10

Textbox 5 - Opérateurs téléphoniques en Suisse (V=Téléphonie par Internet, F=operateur fixe)

Color=indigo, size =10

The destination and the date must be changed accordingly

The result will look like on the image below.

Making adjustments

Although we are giving setting to group parameters, we are always able to make changes on separate values, or colors or text.

In this example the mobile rate for first 2 operators is not available (N/A) and the word N/A is mixed with the landline rates. We need to click on the value and move it up:

Format Data series for fixed networks

For fixed networks the data point are must be one colored, without effect.

Double click on every fixnet data point one-by-one and in Patter tab for Area choose color light yellow

Format Data series for landlines

For fixed networks the data point are must be one colored, without effect.

Double click on every fixnet data point one-by-one and in Patterns tab for Area choose color light yellow

Changing format for Switzernet’s price

We need to change the data series format for Switzernet’s price, to make it more viewable.

For landlines choose Color1= Pale Blue, Color2 =Torquoise, and the Variants of gradient the left bottom one

Fore mobiles choose Color1= Rose, Color2 =Lite Yellow, and the Variants of gradient the left bottom one

The final result is shown on the picture below.

Validation

To validate this training session, click on the chart, then copy it (Ctrl+C). Open “Paint” software and copy the image in a new file (Ctrl+V). Save it in PNG format. Name it in the following format: yymmdd-name-chart-2.png. Where yymmdd is the current date, name is your family name. This png file must be uploaded on the training session web site, according to the guidelines.

Note: when the task has been assigned, you received a set of colors to be used during creation of the chart (instead of red and blue used in the training session above). Please make sure you used the colors that you received by email.

Références

Training: Excel Chart: http://switzernet.com/2/support/100813-excel-chart-training/

Training sessions: http://www.unappel.ch/2/support/100722-training-employees/i/

* * *