13th of Feb 2017

CEB 201 CIVIL ENGINEERING SYSTEMS I

Telue Engineering Institute Ltd

TOPIC 1: MICROSOFT EXCEL 2010 - REVIEWING THE BASICS AND A LOOK AT SOME SPECIAL
FEATURES

TUTORIAL #1

Open a New Excel Workbook and Rename the Workbook “Practical Assignment 1” and Save
the Document on Your Desktop.

There are 3 Questions and Question 1 must be answered on Sheet 1 while Question 2 and 3
are to be answered on Sheet 2.

As you work through this tutorial, make sure you save your work.

Try to attempt the Challenge Tasks by yourself - use your Lecture Note, ask a friend or even
Google. If you still can’t figure it out come and see me.
Note that in Excel,

A1:A5 means cell A1 to cell A5

QUESTION 1 - SIMPLE FORMULAS

A.) WORKING WITH VALUES

o Go To Sheet 1 in your Workbook, and Rename the Worksheet (Go to the Sheet Tab titled
“Sheet 2” and double left click) as “Simple Formulas”.
o Switch to “Page Layout” View
o Select the cells ranging from A1 to I2 (start at cell A1, left click and drag down to cell A2,
then drag across to I2); to format the selected cells:
 Go to the Home Tab and under the Alignment Group, select “Center & Merge”
 Go to the Font Group and set the Font to type Times Romans, Font Size 14 and
Bold
o Type the following text into the formatted cells (A1 to I2): WORKING WITH VALUES
o Switch to “Normal” View
 13th of Feb 2017

o Start at cell B3 and complete the following calculation in Excel:

Cell Simple Formula
B3 =2+8+9
B4 = 10 - 5 -1
B5 =6+4-2
B6 =8-6+4

o Inserting a Row:
 Select Cell B3 and go to the Home Tab and under the Cells group select the “Insert
Cells” Drop Down Box and select the Insert Sheet Rows option
 Select the cell range A3 to D3 and go to Alignment Group and select “Center &
Merge”
 Type the following into the formatted cell range A3 to D3: “ADDITION &
SUBTRACTION”
 Select Cell E3 to I3 and go to Alignment Group and select “Center & Merge”
 Type the following into the formatted cell range A3 to D3: “MULTIPLICATION,
DIVISION, AND POWERS”
o Begin at cell G4 and complete the following calculations in Excel:

Cell Simple Formula

G4 =2*8/4
G5 = 10 * 5 /1
G6 = 12/ 4 ^2
G7 = 8/4^3
For Asterisk (*) the key combination is: Shift + 8
For Caret ( ^) the key combinations is: Shift + 6
For Slash (/), look for the question mark (?) key on the keyboard.
o Formatting A Cell Range:
 Select the cell range C10 to G10; go to the Alignment Group in the Home Tab and
select “Center & Merge”
 Under the Font Group, Select drop down arrow beside Underline (U) option and
select the Double Underline Option
 Type the Following Text in the formatted cells (C10:G10) “BRING IN THE BRACKETS”
o Go to E11 and type in the following calculations:
Cell Simple Formula
E11 = (2 * 8 )/ 4
E12 = (10 * 5) /1
E13 =( 12/ 4 )^2
E14 = (8/4)^3

See how adding brackets, changes the formula calculations?

o Copying a Format:
 Select the cell range C10:G10 (contains the text “BRING IN THE BRACKETS”), press
the shortcut key Ctrl+C to copy the contents of the cell (text plus format)
 Select cell C16 and press the shortcut key Ctrl+V to past the contents of the cell
 Press the Enter button
 13th of Feb 2017

 Edit the contents of the cell; with the cursor still in cell C16 type the following text:
“COMBINE ALL OPERATIONS”

o Go to Cell E17 and complete the following calculations:

Cell Simple Formula
E11 = (2 * 8 )/ 4 -2 ^ 2 + 1
E12 = (2 * 8 )/( 4 -2) ^ (2 + 1)
E13 = 2 * 8/ (4 -2) ^ 2 + 1
E14 = 2 * 8/ (4 -2^ 2 + 1)

The numbers and operations for all 4 formulas are exactly the same, the only change is the
position of the brackets in the formula - note, that the different placing of the brackets
results in 4 different answers being produced.
o EDITTING FORMULAS IN THE FORMULA BAR
o Go through all the formulas typed so far and change the value 2 to 1
 Select the cell containing the formula
 Go to the Formula Bar (refer to Lecture notes), edit the formula and press
ENTER
 Repeat for all other formulas
* SHOW FORMULAS ON WORKSHEET
o To display the formulas on the worksheet
 Go to the Formulas Tab and under the Formula Auditing Group, select
“Show Formulas” button

All your formulas should now be shown on the spreadsheet; this feature is very helpful when
you want to check your formulas for errors.

* FORMAT YOUR SPREADSHEET

Challenge: The result of displaying your formulas in your spreadsheet will bring up some
paging issues (some of your cell contents are spread over 2 different pages), your task is to
figure out how to solve this paging issues. (Hint: go to the Page Layout Tab)

B.) WORKING WITH CELL REFERENCES

o Switch to the “Page Layout” View

o Use the Vertical Scroll Bar (refer to Lecture notes) to navigate to the 2nd Page of Your
Worksheet.
o Copy and Paste Cell Contents of Heading
 Select the Heading “Working with Values” in cell range A1:I2 and copy (Ctrl+C)
 Go to cell A36 and paste (Ctrl+V)
 Edit the text to “Working With Cell References”
o Copy and Paste Cell Contents of Formulas and Text
 Select the cell range A3 to G20
 Copy the cell contents (Ctrl+C)
 Go Cell A36 and press ENTER
 13th of Feb 2017

o Deleting Contents of Cells

 Select the cell B37 to B40 and press the delete button
 Repeat the same for the following cell ranges:
 G37:G40
 E44:47
 E50:E53
o Entering Values and Using the AutoFill Feature
 Go to cell A37 and enter the value 1 and press ENTER
 Use the AutoFill Feature; go to the right side of cell A37 and Move to the bottom
right corner of the cell, where a bolded cross ( + ) should appear.
 Left click and drag to cell A46
 Left click the “Auto Fill Options” and select the Fill Series Option

You should now have values ranging from 1 to 10 located in cell A37 to A46.
You will now use this values to redo your calculations from Part A of Exercise 1.

o Use Cell References

 Use the values located in cell A37 to A46 to redo the formulas displayed in Page 1.
Refer back to Page 1 and look at the formula contents, go to Page 2 of the
worksheet and enter the same formula again; except, this time use the cell
references of the values present in A37 to A46.

After you have entered all the formulas ( remember to use cell references), you can check if your
formulas are correct.

o To Check:
 Go To the Formulas Tab and under the Formula Auditing Group select the “Show
Formulas” button to now display the results of the formulas
 The results of the formulas in Page 1 and Page 2 should be exactly the same! The
only difference is that Page 1 used values in the formulas and Page 2 used cell
references!
If not go back and check your cell references, operations or brackets.

* DEALING WITH PAGGING ISSUES

Ensure that all the contents of the cell are present in the on their respective pages and are
neat and presentable.
* SAVING AS A PDF
Use your Lecture notes, to save the spreadsheet as a PDF.
 13th of Feb 2017

QUESTION 2 - BUILT-IN FUNCTIONS

Go To Sheet 2 in your Workbook, and Rename the Worksheet (Go to the Sheet Tab titled “Sheet 2”
and double left click) as “Built-In Functions”.

The following are the test results of a Year 12 Biology Class:

85, 86, 79, 92, 65, 75, 85, 95, 72, 82, 77, 62, 90, 84, 73, 88, 93, 87, 93, 82.

Enter a single value into a cell, then use the cell reference of the values to complete the rest of the
calculations. USE CELL REFERENCES IN YOUR CALCULATIONS! (Example: 85 = A1, 86 = A2, 79 = A3….)

 Go Cell E3 to Calculate the Following:

Cell Name Formula
D3 Class Average =AVERAGE(A1:A20)
D4 Maximum Score =MAXIMUM(A1:A20)
D5 Minimum Score =MINIMUM(A1:A20)
D6 Mode =MODE(A1:A20)
D7 Count =COUNT(A1:A20)

o FIXING ERRORS

Challenge:

The formulas in cell D4 and D5 returns an error value. Use your Lecture notes to figure out how to
correct this error. (Hint: Formula Name)

o ENTER AND FORMAT TEXT

 Go to cell B3 and type the following text: CLASS AVERAGE
 Go to cell B4 and type the following text: MAXIMUM SCORE
 Go to cell B5 and type the following text: MINIMUM SCORE
 Go to cell B6 and type the following text : MODE
 Go to cell B7 and type the following text: NUMBER OF STUDENTS IN CLASS
 Select all cells (B3 to B7) and set the font type as Bookman Old Style, the font size as
11, and bold the text using the shortcut key “Ctrl+B”
o ALIGNING THE CELL CONTENTS
 Select cell B3 to C3; go to the Alignment Group and select “Centre & Merge”
 Do the same for cell B4 to C4, B5 to C5, B6 to C6
 Select cell B7 to C8; go the Alignment Group and select “Centre & Merge” and
“Wrap Text”
 Select cell D7 to D8; go to the Alignment Group and select “Centre & Merge”
 Select cell D3 to D6; go to the Alignment Group and select “Middle Align” and
“Centre”
 13th of Feb 2017

QUESTION 3 - CREATING CHARTS

o Rename the Worksheet

 Go to the Sheet Tabs located at the bottom of the window, right click on the current
sheet ( “Built-In Functions”) and select Rename
 Enter the following text: “Functions and Charts”
o Save your Work
 Press Ctrl+S to save your work so far
o Insert a Column
 Select cell A1, under the Home tab and the Cells Group, select the “Insert Cells”
Drop Down box and select the 3rd option “Insert Sheet Columns”
A new column should be inserted on the left side of the worksheet
o Using the AutoFill Feature and Adjust Column Widths
 In cell A1, type the text: Student 1
 Move to the bottom right corner of the cell, where a bolded cross ( + ) should
appear. Left Click and Drag all the way down to cell A20.
The cells should automatically fill with the following: “Student 2, Student 3,…”
If it copies the same text “Student 1” throughout the other cells, just go to the
AutoFill Options Drop Down Box ( it should appear at cell A20) and select the 2nd
Option Fill Series.
 Adjust the column width by going to the column heading titled “A” and placing your
cursor on the thin blue line in between “A” and “B”, left click and widen the column
so the rest of the text can appear.
o Insert a Line Chart
 Select the cell range A1:B20, go to the Insert Tab and under the Chart group select
the Line Chart Drop Down Box
 Select the first Chart Type

Once a Chart is inserted the Chart Contextual Tab which provides

more command buttons that are relevant to enhancing the design, layout and format of the chart
you’ve now created.

The Chart Tools Contextual Tab is only available when the Chart is selected!

o Using the Layout Tab:

To Add a Chart Title

 Select the Chart

 Go to the Layout Tab in the Chart Tools Contextual Tab and under the Labels group,
select the Chart Title Drop Down Box
 Select the Option Above Chart to insert a Chart Title directly above the Chart
 13th of Feb 2017

 Select the Default Text (“Chart Title) and replace with the following text: Year 12
Test Results

To Add Horizontal Axis (X-Axis) Label

 Still in the Labels Group, select the “Axis Title” drop down box and select the first
option “Primary Horizontal Axis Title”
 A drop down list is shown, select the 2nd option “Title Below Axis”
 Replace the Default Text (“Axis Title”) with the Text “Students”

To Add Vertical Axis (Y-Axis) Label

 Still in the Labels Group, select the “Axis Title” drop down box and select the second
option “Primary Vertical Axis Title”
 For this chart, from the drop down list shown, select the 2nd option “Rotated Title”
 Replace the Default Text (“Axis Title”) with the Text “Test Score”
o Use the Format Tab of the Chart Contextual Tab

To Add a Border Around Your Graph

 Select the Chart and go to the Format Tab of the Chart Contextual Tab
 Go the Shape Styles Group, select the option Orange, Accent 6

Click somewhere outside the graph to close the Chart Tools Contextual Tab.

o Insert a Column
 Go to Cell C1 and Insert a Column by clicking the Home Tab going to the Cells Group
and from the Insert Cells Drop Down box, select the 3rd Option
 Enter the following values, which are the results of the Year 12 Chemistry Test:

88, 92,78, 82, 63, 75, 95, 89, 52, 81, 74, 85, 92, 81, 72, 64, 90, 88, 83, 92

To Add New Data to an Already Exisiting Chart, Use the Tools Available in the Design Tab of the Chart
Tools Contextual Tab!

o Use the Design Tab

 Select the Chart, Go to the Design Tab and under the Data Group, select the button
Select Data to open the Select Data Source dialog box.
 If you select the button Switch Row/Column, data graphed in the x- and y-axis will
switch and the chart on your worksheet will change. Click the button again to return
back to the original chart.
 The independent values (the name of the Students) are graphed correctly in the x-
axis. The x-axis in Excel is called the Horizontal (Category) Axis Labels. Whenever you
 13th of Feb 2017

are graphing in Excel, always ensure that your independent values are entered into
this area.
 The dependent values (the results of the test) are the values that are different for
each datum - the value that changes. It is usually (the best option) graphed in the
vertical axis or the y axis. In Excel, this is called the Legend Entries (Series). Here we
see that we have one set of test results graphed - the Biology Test Results.
 To Change the Default Name “Series 1”, select the Edit button and the Edit Series
Dialog Box appears. In the Series Name Text Box, enter the text: “Biology Test
Results” and click the OK button.
 To Add another Data Set to the Graph, select the Add button and once again the
Edit Series box appears. Enter the following text: “Chemistry Test Results” into the
Series Name textbox
 To enter the values of the data set, go to the textbox Series Values and delete/clear
all contents
 Use your cursor and navigate through your worksheet, left click on cell C1 and drag
through to cell C20 and press the OK button.
 Check the Horizontal (Catergory) Axis Labels and edit by clicking the its Edit button.
Place cursor in the textbox titled “Axis Label Range” then navigate through your
worksheet area and left click on cell A1 and drag down to cell A20. Click OK.
 Click OK to exit the Select data Source Dialog Box.

To make the chart large so that all the datum appear, go to the right corner of the chart and a small
white double head arrow should appear. Left click and drag outwards to increase the chart area.

REMEMBER, PRACTICE MAKES PERFECT, SO HAVE ANOTHER GO AT THE TUTORIAL AGAIN -this time
see if you can complete the questions faster and more efficiently 