Hands-On Material: Case 1

File: C3HOG_99.doc12/15/2018

HANDS ON EXERCISES

The material that follows creates the basic EXCEL charts. The file BOBCHART.XLS is required for the exercises. Most of the material can be carried out in the Computer Lab but Students are encouraged to work through material on their own whenever possible.

Obtain the file BOBCHART.XLS from Shareout.

Pre Chart Exercises (and review for HANDS ON Examination);

Select B1 and input =CELL(“filename”);
Some students wanted to know how to display today’s date so edit B6 to =TODAY()

Notice that B5 and B6 have different date formats so:

Click into cell B5;
Point to the format painter button (little paint brush) and click;
Point over cell B6 and click.

Dragging again:

Click into cell C11;
Center the label in the cell;
Add a bottom border;
Drag across to J11.

Click into cell C15;
Use point and click to enter the correct formula ;
Format to $.00;
Drag across to J15.

Three ways to input a sum function

a)Direct input of built in function:

Click into cell C28;
Input =SUM(
then use your mouse to select C17:C28 then )
Delete the formula.

b)Lets try a neat tool I use a lot to help with semi familiar built in functions:

Click into cell C28;
input =sum(
then {Ctrl}+A
finish the dialogue box to input the formula;
Delete the formula.

c)Or Auto sum

Click into cell C28;
Select Auto Sum, , to input the formula;
Format to $.00;
Drag across to column J.

One last formula and a useful currency format:

Click into cell C30;
Input the correct formula;
Format C30 as follows:

Right Mouse;

Format Cells;

Currency;

Select ($1,234.10) in red;

Drag across to cell J30.

Illustrating the {Ctrl} Key (for selection of non-contiguous ranges):

Select C15:J15;
Depress {Ctrl};
Select C28:J28;
Keep {Ctrl} depressed;
Select C30:J30;
Release {Ctrl};
Select the accounting border upper bar and double lower bar.
Save the file.

HO1: Chart Basics

Objective(s):

In many cases a well-designed Chart can enhance a worksheet. For example, it is Bob’s “gut feeling” that he’s getting near break even. In his mind’s eye he sees sales and costs converging over time. EXCEL can easily check this feeling by producing charts to help Bob quantify his gut reaction.

New Spreadsheet Jargon and Input

Line chartstraces out the historical path of the data. The key is that the x-axis is treated as equal increments of time (monthly, quarterly, yearly data);

Scatter graphsplot combinations of (x,y) points like engineering paper.

Contiguousmeans touching or adjoining

example/ in a worksheet cells C5:C7 are contiguous to cells D5:D7. On the other hand cells C5:C7 and E5:E7 are non- contiguous

Question: What range(s) are contiguous to cells C6:E6 and what range(s) are not contiguous to C6:E6?

{Ctrl}depress and hold down the control key

HO1.1 Preparing a Line Chart

1) Open the file BOBCHART.XLS.

2)Charts can be created on the existing worksheet or as a new sheet. In this exercise a new chart will be inserted as a separate sheet. The finished product will look like Figure HO1.1 on the next page.

Figure HO1.1 A Line Chart

Notice that the Chart does give the impression that revenue will soon catch up to expenses.

Building the chart with Chart Wizard:

It’s easier in my view to click on a blank cell before the wizard (if you are in the data range EXCEL assumes you want it all on the chart).

From the button select Chart Wizard
Step 1 of the Wizard select. from the Standard types Tab, Line;
Next;
At Step 2 of the Wizard choose the Series Tab

Add the Total Revenue series by pointing and clicking as illustrated

Add the Expense Series and check your rough display:

If your Chart looks correct Next;

Now the common problem Add the Category Axis to show both Year and Qtr. Notice: the entries are from C110:J11. Two rows!

Next;

Set the titles (notice you have many tabs here but we leave editing until later):

Next;
Place the Chart on its own sheet:

Finish;
Save the file.

You should now have a chart similar to Figure HO1.1. Ignore minor differences editing charts is the next topic.

HO1.2: Chart Editing

The Chart, Figure HO1.1, is a little messy. Consider the following problems:

a)the gray scale background may be hard to read or make the Chart awkward to print;

b)the Y-axis values are busy ($.00) and not scaled from 0;

c)Grid lines are not needed on this Chart;

d)the Legend is off to the side;

e)the Chart Title could be centered more “nicely”;

Note: Before starting find the Undo Button on the toolbar. If at any step you accidentally remove the wrong item use the Undo Button to correct your error.

1) Eliminate the gray scale.

Point over Plot Area (not on a data line);
Click Right Mouse;
Format Plot Area (note you could choose clear instead);
Set Border to None;
Set Area to None;
OK

2) Eliminate the grid.

Point over the (Value Axis Major) Gridlines;
Click Right Mouse;
Clear;

3) Clean Up the Y-axis.

Point onto the Y-axis line;
Click Right Mouse;
Format Axes;
Select Number tab;
Select Category Number set to use Comma separator and display 0 decimals;
Select Scale tab;
Set Minimum input 0;
OK

4) Adjust the location of the Legend.

Click on the legend text box;
Drag over and above the X-axis as illustrated in Figure HO1.2.

2)Edit the titles a bit

Click on the Chart Title text box;
Edit the title to read Revenue And Expenses with 1994/1995 as in Figure HO1.2;
Click on the X Axis Title and edit to Dollars;
Click on the Y Axis Title and edit as illustrated in Figure HO1.2.

5) Format the Data Series a little

Point onto the Revenue series;
Click Right Mouse;
Format Data Series;
Select Patterns tab;
Select Style and set to dotted;
Select Weight and set to heaviest;
Select Smoothed;
OK

Figure HO1.2 The Edited Chart

Save the file.

HO1.3: Good Questions on Chart Editing

To look at a couple of student questions that arise out of this exercsie make a copy of the Chart sheet:

Point the mouse over the tab name Line;

Hold own {Ctrl} and drag right (you should see a little sheet with a cross on it);

Release to create a copy called Line[2].

The material that follows uses the sheet Line[2].

Student Question #1: If I want a different type of Chart, do I have to redo everything?

Answer:

No. It’s very easy to change the Chart Type:

Select Line[2];
Select the Chart Wizard button;
Chart type: Column;
Chart sub type: 3D (the last panel);
Finish.

(Note: An alternative method is point the mouse to Chart Area then use right mouse)

When you are finished playing around set the Chart back to the original Line chart selection.

Student Question #2: How do I more series to the chart?

Answer:

There are several ways to achieve this easily. The most common is:

From the Main Menu:

Chart

Add Data

Select Sheet1 and select:

OK.

Student Question #3: I have legends I want to I edit, can I do this?

Answer:Yes. But not the way you will probably want to try. Logic says select the legend text box and then edit. Try to edit the legend Net Income to read Profit. Frustrating isn’t it!

Do you see how to make the change?

Select Sheet1;
Edit cell B30 to Profit;
Check the Legend.

That’s the importance of the information in the Dialogue Box above. Always read the information in Dialogue Boxes as it is intended to help you do things professionally.

Student Question #4: The legends say Series 1 how do I edit this?

Answer:A very common problem and a variation on Question #2. Let’s demonstrate using the common problem:

First simulate the error on the Chart:

Select Chart Wizard;
Next;
Now edit the Series tab by eliminating deleting the series name for Revenue as illustrated:

Finish;
Observe the legend. It reads Series 1. EXCEL has no other Name it can use. Select the Chart Wizard and fix the problem

Student Question #5: Can I move my Charts around?

Answer:Yes. In the next section the Chart will be put on the Sheet containing the data. However it is easy to move the chart on and off of sheets using the Location feature.

To illustrate:

Select the Chart Area;
Right Mouse;
Location;
Set the box to:

The Chart probably looks funny, not to worry.
For now use Location again to put the Chart on its own sheet called Profit;
Save the file or delete the excess sheets and Save as you wish.