CONTENTS

1) The Excel Window: Basic Elements……………………………… 2

2) The Excel Worksheet……………………………………………… 5

2.1 Some Useful Navigation Keys………………………………. 5
2.2 Data Entry: Input, Modification and Saving………………… 8
2.3 Importing and Exporting File..………………………………. 13

3) Basic Excel Operations …………………………………………….. 18

3.1 Using Excel Functions………………………………………. 18
3.2 User Defined Functions……………………………………… 20
3.3 Making Graphs………………………………………………. 21
3.4 Curve Fitting Using Excel’s Trendlines……………………… 22

4) Advanced Excel Operations…………………………………………. 24

4.1 Matrix Operations: Solving System of Linear Equations…….. 24
4.2 Numerical Methods: Finite Difference and Integration………. 29
4.3 Excel Macros………………………………………………….. 35

Page | 1
1) The Excel Window: Basic Elements

a) Microsoft Office Button

b) Quick Access Toolbar

c) Title Bar

Page | 2
d) The Ribbon

e) Name Box

f) Formula Bar

Page | 3
g) Status Bar

Page | 4
2) The Excel Worksheet

2.1 Some useful Navigation Keys

The Down Arrow Key

 Press the down arrow key several times. Note that the cursor moves downward one
cell at a time.

The Up Arrow Key

 Press the up arrow key several times. Note that the cursor moves upward one cell at a
time.

The Tab Key

1. Move to cell A1.

2. Press the Tab key several times. Note that the cursor moves to the right one cell at a
time.

The Shift+Tab Keys

 Hold down the Shift key and then press Tab. Note that the cursor moves to the left
one cell at a time.

The Right and Left Arrow Keys

1. Press the right arrow key several times. Note that the cursor moves to the right.
2. Press the left arrow key several times. Note that the cursor moves to the left.

Page Up and Page Down

1. Press the Page Down key. Note that the cursor moves down one page.
2. Press the Page Up key. Note that the cursor moves up one page.

The Ctrl-Home Key

1. Move the cursor to column J.

2. Stay in column J and move the cursor to row 20.
3. Hold down the Ctrl key while you press the Home key. Excel moves to cell A1.

Page | 5
Go to -- F5

 The F5 function key is the "Go To" key. If you press the F5 key, you are prompted
for the cell to which you wish to go. Enter the cell address, and the cursor jumps to
that cell.

1. Press F5. The Go To dialog box opens.

2. Type J3 in the Reference field.
3. Press Enter. Excel moves to cell J3.

Go to -- Ctrl+G

 You can also use Ctrl+G to go to a specific cell.

1. Hold down the Ctrl key while you press "g" (Ctrl+g). The Go To dialog box
opens.
2. Type C4 in the Reference field.
3. Press Enter. Excel moves to cell C4.

Anchor to Copy -- F8

1. Go to cell A1.
2. Press the F8 key. This anchors the cursor.

Page | 6
3. Note that "Extend Selection" appears on the Status bar in the lower-left corner of the
window. You are in the Extend mode.
4. Click in cell E7. Excel highlights cells A1 to E7.
5. Press Esc and click anywhere on the worksheet to clear the highlighting.

Page | 7
2.2 Data Entry: Input, Modification and Saving

Input Data

1. Place the cursor in cell A1.

2. Type John Jordan. Do not press Enter at this time.

Delete Data

The Backspace key erases one character at a time.

1. Press the Backspace key until Jordan is erased.

2. Press Enter. The name "John" appears in cell A1.

Edit a Cell

Change "John" to "Jones."

1. Move to cell A1.

Page | 8
 2. Press F2.
3. Use the Backspace key to delete the "n" and the "h."
4. Type nes.
5. Press Enter.

Alternate Method: Editing a Cell by Using the Formula Bar

You can also edit the cell by using the Formula bar. You change "Jones" to "Joker" in the
following exercise.

1. Move the cursor to cell A1.

2. Click in the formula area of the Formula bar.

3. Use the backspace key to erase the "s," "e," and "n."
4. Type ker.
5. Press Enter.

Page | 9
Alternate Method: Edit a Cell by Double-Clicking in the Cell

You can change "Joker" to "Johnson" as follows:

1. Move to cell A1.

2. Double-click in cell A1.
3. Press the End key. Your cursor is now at the end of your text.

3. Use the Backspace key to erase "r," "e," and "k."

4. Type hnson.
5. Press Enter.

Change a Cell Entry

Typing in a cell replaces the old cell entry with the new information you type.

Page | 10
 1. Move the cursor to cell A1.
2. Type Cathy.
3. Press Enter. The name "Cathy" replaces "Johnson."

Wrap Text

1. Move to cell A2.

2. Type Text too long to fit.
3. Press Enter.

Page | 11
 4. Return to cell A2.
5. Choose the Home tab.
6. Click the Wrap Text button . Excel wraps the text in the cell.

Delete a Cell Entry

1. Select cells A1 to A2.

2. Press the Delete key.

Save a File

1. Click the Office button. A menu appears.

2. Click Save. The Save As dialog box appears.
3. Go to the directory in which you want to save your file.
4. Type Lesson1 in the File Name field.
5. Click Save. Excel saves your file.

Close Excel

1. Click the Office button. A menu appears.

2. Click Close. Excel closes.

Page | 12
2.3 Importing and Exporting File

Notepad File

Importing

1. Click the Office button. A menu appears.

2. Click Open.

Page | 13
3. Go to the directory where you want to import the file from.
4. Choose ‘All Files’.
5. Select the data file to import.
6. Click Open.

7. Choose the file type that best describes your data.

8. Choose the row to start import.
9. Click Next.

Page | 14
10. Choose the delimiters your data contains.
11. Preview data.
12. Click Next.

13. Choose column data format.

14. Select column to format and preview data.
15. Click Finish.

Page | 15
Exporting

16. Click on cell C4. Type Speed.

17. Click on cell C5. Type (miles/hr)
18. Click on Office button. A menu appears.
19. Choose Save As. Click on Other Formats.

Page | 16
20. Go to the directory in which you want to save your file.
21. Enter file name (data_new.dat).
22. Click Save.

Page | 17
3) Basic Excel Operations

3.1 Using Excel Functions

Use the file data_new.dat created in the previous section to convert the speed from
kilometer per hour (km/hr) to miles per hour (miles/hr) using the following simple
formula:
(miles/hr) = (km/hr) / ConvFactor, where the Factor, ConvFactor = 1.6

1. Open the file ‘data_new.dat’ in excel.

2. Click on cell A2. Type ‘Factor=’.
3. Click on cell B2. Type ‘1.6’.
4. Click on Name Box. Type ‘ConvFactor’ and press Enter.

5. Click on cell C7. Type ‘=B7/ConvFactor’.

Page | 18
6. Press Enter. The value in miles/hr appears in cell C7.

7. Select cells C7 to C16.

8. Click on Fill from the Ribbon.

9. Click the Down option.

10. The values in miles/hr are calculated and displayed for each km/hr entry.

Page | 19
3.2 User Defined Functions

Continue to use the file data_new.dat modified in the previous section to calculate
the acceleration from kilometer per hour (km/hr) speed using the following simple
formula:
Acceleration (km/hr2) = (v-u)/Δt

1. Click on cell D3. Type ‘Acceleration’.

2. Click on cell D4. Type ‘(km/hr2)’
3. Click on cell D8.
4. Type ‘=(B8-B7)/(A8-A7)’ in the Formula Bar. Press Enter.
5. Select cells C8 to C16. Click on Fill from the Ribbon and select the Down option.

6. The Acceleration for each speed entry is calculated and displayed.

Page | 20
3.3 Making Graphs

Use the Speed data from data_new.dat to make a graph of speed versus time.

1. Select cells A3 to B16.

2. Click Insert on the Ribbon.
3. Click on the Scatter Charts options.
4. Choose ‘Scatter with only Markers’

5. The Scatter Chart appears next to the data.

Page | 21
3.4 Curve Fitting Using Excel’s Trendlines

Use the Scatter chart to insert a Trendline.

1. Point the cursor on a data point on the Scatter Chart and Right Click.
2. Click on the ‘Add Trendline…’ option.

3. The ‘Format Trendline’ dialog box appears.

Page | 22
4. Choose the Trend/Regression Type.
5. Check the Display Equation on Chart and Display R-squared value on Chart
buttons.
6. The Trendline and R-squared value appear on the Scatter Chart.

Page | 23
4) Advanced Excel Operations

4.1 Matrix Operations: Solving System of Linear Equations

Addition

1. Input Matrix A.
2. Select cells B4 to C6.
3. Click on Name Box. Type the name of the selected Matrix ‘A’ and Enter.

4. Input Matrix B.
5. Select cells B8 to C10.
6. Click on Name box. Type the name of the selected Matrix ‘B’ and Enter.

Page | 24
7. Click on cell A12. Type ‘[A] + [B]’.
8. Click on cell B12 and select cells B12 to C14.
9. Type ‘=A+B’, which appears in cell B12.

10. Press Ctrl+Shift+Enter to get the result of the Matrix addition A + B

Page | 25
Multiplication

1. Input Matrix A and name it A in the Name Box as done in previous section.
2. Input Matrix B and name it B in the Name Box as done in previous section.
3. Click on cell B10 and select the area in which the product Matrix will reside.
4. Type ‘=MMULT(A,B)’ in cell B10.

5. Press Ctrl+Shift+Enter to get the result of Matrix Multiplication [A][B].

Page | 26
Solving a Linear System of Equations

Consider the following linear system of equations:

3x1 +2x2 +4x3 = 5,
2x1 +5x2 +3x3 =17,
7x1 +2x2 +2x3 =11.
The above system of equations can be written in Matrix format as:

3 2 4 𝑥1 5
𝑥
2 5 3 2 = 17
7 2 2 𝑥3 11
Alternatively,
[Coeff][x] = [rhs]

324 𝑥1 5
𝑥
Where Coeff = 2 5 3 , x = 2 and rhs = 17
722 𝑥3 11
The objective here is to solve the system of equations in Excel to get the Matrix [x]:
[x] = [Coeff]-1 [rhs]
3 2 4 −1 5
= 253 17
722 11

1. Input Matrix [Coeff] and assign it the name ‘Coeff’ in the Name Box.
2. Input Matrix [rhs] and assign it the name ‘rhs’ in the Name Box.

3. Click on cell B7 and select cells B7 to D9 where the inverse matrix will reside.
4. Type ‘=MINVERSE(Coeff)’, which will appear in cell B7.
5. Press Ctrl+Shift+Enter to get the Inverse Martix and assign it the name CoeffInv.

Page | 27
6. Click on cell G7 and select cells G7 to G9 where the solution Matrix x will reside.
7. Type ‘=MMULT(CoeffInv,rhs)’, which will appear in cell G7.

8. Press Ctrl+Shift+Enter to get the solution Matrix x.

Page | 28
4.2 Numerical Methods
Finite Difference

A schematic of the experimental setup for determining the thermal conductivity of

metals is shown below.

The thermal conductivity, k, can be determined from Fourrier’s law:

𝑞 𝑑𝑇
= −𝑘
𝐴 𝑑𝑥
Where
q is the power applied to the heater and
A is the cross-sectional area of the rod.

A set of recorded data is shown below:

Rod diameter: 2 cm
Thermocouple Spacing: 5 cm
Power: -100 Watts

Thermocouple Temperature (K)

1 384
2 387
3 425
4 464
5 503

𝑑𝑇
The objective here is to calculate the derivative in Excel using finite difference.
𝑑𝑥

Page | 29
The thermal conductivity, k, can then be determined by rearranging Fourrier’s Law to
get the formula:
𝑞 𝑑𝑇
𝑘=− /
𝐴 𝑑𝑥

1. Input the given data in an Excel worksheet. Name the parameters in the Name Box.
2. Calculate the area, A, of the rod in cell D6 by typing ‘=PI()*d^2/4’. Press Enter.

𝑑𝑇
3. Calculate in cell E11 by typing ‘=(D12-D10)/(C12-C10)’. Press Enter.
𝑑𝑥

Page | 30
4. Calculate the value of k in cell F11 by typing ‘=-(q/A)/E11*100’. Press Enter.

5. Calculate the k value for other positions.

Page | 31
Numerical Integration

The objective here is to demonstrate the capability of Excel to carry out integration.
The following simple function will be used:
𝑦 = cos⁡
(𝑥)
In the interval 0 to 𝜋/2.
𝜋/2 π/2
0
ydx = 0
cos x dx
𝜋
= sin − sin⁡
(0)
2
= 1.

Trapezoid Rule of Integration:

Area of a trapezoid:
1
A1   ya  yb  xb  xa 
2
Value of Integral:

 /2 n
 cos( x)   Ai
0 i 1

 A1  A2  A3  A4

Page | 32
1. Input the cosine data set in an Excel worksheet as follows:

2. Find the area of the first trapezoid by typing ‘=0.5*(C5+C6)*(C6-C5)’ in cell D5.

Page | 33
3. Calculate the area of the other Trapezoids.

4. Add the trapezoid areas to approximate the area below the curve.

Page | 34
4.3 Excel Macros

Excel allows the user to record a set of commands (keystrokes) as a Macro to be

reused as needed. Macros can be written as a Visual Basic for Applications (VBA)
subprogram or can be recorded within Excel. Recording a Macro is demonstrated
below:

1. Read the data file ‘data_new.dat’ in Excel. Click on cell C7.

2. Click on the ‘View’ option of the Ribbon.

3. Click on the Macros Button.
4. Choose the Record Macro… option.

Page | 35
5. The Record Macro sub-window appears. Fill the different boxes.
6. Click OK to start recording the Macro.

7. Type ‘=B7/1.6’ in cell C7. Press Enter.

8. Click ‘Stop Recording’ to stop recording the Macro. A macro to convert km/hr to
miles/hr has been recorded. The shortcut has been set as Ctrl+c.

Page | 36
9. Click on cell C8 and press Ctrl+c. The next speed entry is converted to miles/hr.

10. Click on the next cell (C9) and press Ctrl+c to get the corresponding miles/hr
value.
11. Repeat step 10 for cells C10 to C16 to generate the remaining miles/hr values.

Page | 37