LABORATORY WORK 4
MANUAL SPREADSHEETS MICROSOFT EXCEL
Student: Tursingali N. E.
Group: 16-TPhK-1.
Teacher: Nursadykova R.K.
Ust-Kamenogorsk
MANUAL SPREADSHEETS MICROSOFT EXCEL
1 Excel Basics
Microsoft Excel is a spreadsheet application that is used for basic data organization, statistical analysis, graphing data as well as many other uses. In this lab, we will take a look at what makes up an Excel spreadsheet and the basic uses of it.
The interface
1. The File menu: This menu allows you to create, save, open and print spreadsheets.
2. Quick access toolbar: You can customize this toolbar to include all of the functions you use most, such as save and undo.
3. The ribbon: The ribbon contains all the office menus and toolbars. The ribbon is divided into tabs, each of which contains groups of controls.
4. Columns: Label 4 is one of the columns in the spreadsheet. Each column is labeled by the letter (or a string of letters) at the top of it.
5. Rows: Label 5 is one of the rows in the spreadsheet. Each row is labeled by the number to its left.
6. Cells: A cell is the intersection between a row and column. A cell is where most of the excel data is entered. A cells address is the row and column it is in, for instance, the boxed cell (label 6) is in column E and row 13; hence, its address is E13.
7. The worksheet toolbar: An Excel file is called a workbook. It consists of a number of spreadsheets (worksheets). This toolbar allows you to move between the different sheets in a workbook. It also allows you to create new worksheets, delete existing sheets, and rename sheets.
Exercise 1
1. Enter the data Sunday into cell A1 and Monday into cell B1.
2. Type in 17/08 into cell E8. 3. Type in 2 into cell I8 and 4 into cell I9.
Auto-complete
Your worksheet should now look like this:
Notice how Excel automatically detected that 17/08 was a date and converted it to 17Aug. We will discuss formatting data later on in this lab.
Now, we want to select both cells A1 and B1 together. To do this, click A1 and without releasing the mouse button, move the mouse over cell B1. Now there should be a rectangle around both cells as shown below.
To get excel to auto-complete this row, we now position the mouse cursor at the bottom-left corner of the rectangle. Make sure the cursor has changed into a + sign. Now hold down the cursor and drag it to the left till I1.
This is what your spreadsheet should look like when you release the mouse button:
Exercise 2
1. Auto-complete cells I8 and I9 all the way to I14.
2. Auto-complete cell E8 all the way to E12.
1.3 Formatting
Excel allows you to format your data so that it shows up in the way you need it to. Let us start with number formatting. Select cells I8 and I9. If you take a look at the Number group in the Home tab on the ribbon, you will notice that the current number format is General:
|
|
|
Selecting that drop down box shows you some of the available number formats, as shown below.
Select currency from the drop down menu. Now, you will notice that the two numbers have a $ sign preceding them, and have two decimal places. Let us change the currency symbol to a Euro. Select the Euro symbol from the currency format menu.
Exercise 3
Modify cells I8 and I9 by removing the 2 decimal places.
Exercise 4
Format cell E8 so that it looks like August 17, 2010.
Basic calculations
When working on a spreadsheet, you will almost definitely need to perform some calculations on the data you have. The first thing you need to remember about Excel calculations is that formulas always start with an = sign. Let us begin with a very simple calculation. Type =3+5 into cell A5 as shown below.
Press Enter. Excel automatically replaces the formula with the result of the equation.
Now let us calculate the sum of the numbers in I8 and I9. In cell J10, type =I8+I9. One other option is to type in =, then select cell I8. After that, type in + and then select I9.
Pressing Enter will give you the result of the calculation. Double-clicking on the cell with the formula allows you to edit the formula.
Excel has built-in functions that make your life easier. One of them is the SUM function. In cell J11, type =sum(. Now select both cells I8 and I9.
Pressing Enter gives you the same result as the plus operation we did in cell J10. Try changing the value in cell I8 and notice how the change is reflected in both formulas.
Exercise 5
1. Open Sheet 2 in your workbook.
2. In cells A1 and A2, type 1000 and 1500 respectively.
3. Use auto-complete to fill in cells A3 to A8.
4. Format the numbers so that they show the 1000 separator (1,000) and have one decimal place.
5. Calculate the following values for cells A1 to A8 using built-in Excel functions: a) Sum b) Maximum
c) Minimum
d) Average
6. Enter the number 5000 into cell A9 and modify all the above formulas to include it.
7. Calculate the sum of the Maximum and Minimum.
Task for lab 4 (Microsoft Excel)
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
|
