This tutorial guides the reader to use Microsoft Excel Web App. However, the steps are also applicable to the Desktop version of Microsoft Excel. In this tutorial, we will be creating an Excel document called 'Research-Assessment'.
A) CREATING NEW DOCUMENT
1) If you are using Desktop version of Microsoft Excel, e.g Microsoft Excel 2010, you may skip this step. Browse the url http://skydrive.live.com. You need to log in using your outlook.com or hotmail.com credential in order to access this page.
Click Create/Excel workbook.
2) Type the name of the workbook, Research-Assessment.
3) The workbook main page is displayed.
The page consists of few parts.
3.1) The top most part contains:
a) The document name.
b) The application name.
c) The application window control.
d) The user log control.
3.2) The second part is contains tabs and their associated command buttons.
Microsoft calls this as 'Ribbon'.
When a new document is created, the application initially displays HOME tab.
HOME tab provides command buttons that you need while editing a workbook.
Command buttons are grouped by sections, i.e Clipboard, Font, Alignment, Number, Tab, Cells, Formulas, Data and Web.
3.3) The next part is the workbook panel.
By default, the first sheet is called Sheet1.
3.4) The bottom most part shows the status of the document.
If you click the Drop Down button besides the word "1 Person Editing", you would see your username.
B) COPY-PASTE DATA
4)
4.1) Download a text file from https://sites.google.com/site/notaskydrive/home/beginner/research-assessment.txt?attredirects=0&d=1
4.1) If you open the text file using a Notepad program, it looks like below.
4.2 Highlight the texts and copy them ([Ctrl]+[C]).
4.3) Switch to your Research-Assessment workbook, click the Cell A1 and paste ([Ctrl]+[V]).
Notice that on the Row 1, the texts and the numbers go to separate cells.
4.3) Actually the texts have been separated by a special character called Tab.
If you use a right arrow to move along the texts, you will realize that you just need to press one right-arrow key in order to move from the end of one text to the beginning of another text.
C) COLUMN SIZE
5) Column can be resized to display texts that otherwise would have been hidden from view.
5.1) Hover the mouse pointer to the Column Header border until the pointer icon changes into the split symbol like below. Resize the column until the whole text for "D-CONCLUSION/ RECOMMENDATIONS/ FUTURE STUDY" is visible within the column border.
5.2) Notice that some texts size are longer than the resized column width.
5.3) Click the cell. Go to Alignment Section. Click Wrap Text.
5.4) As a result the texts that exceed the size of the column are brought down to a new line within the same cell.
5.5) Repeat the steps for all cells in the first column that have texts size exceeding the column size.
D) CELL BORDER
6)
6.1) Highlight the cells as shown below. Go to Font Section. Click the Drop Down button for the Border icon and choose All Borders.
6.2) As a result, you get the following look:
6.3) We will add a special border effect to the column to the right of the cells that have got borders.
6.4) Highlight the cells as shown below. Choose Outside Borders.
6.5) As a result these group of cells have got a border surrounding them, but not within their individual cell boundary.
6.6) Repeat the same steps to other cell groups.
E) FORMULA
7) Up to this point, the workbook is ready for use. Ideally, the assessor will type"x" in the cell to denote the marks to be awarded to the student corresponding to the assessment criteria. These marks need to be computed to produce the total mark for the assessment section.
7.1) Refer to the above screenshot, click Cell D10. Then Click the formula textbox.
7.2) Cell D10 should check the cells on top of it; multiply the number of cells containing 'x' with the mark value, i.e 0.858 (Cell D1).
7.3) The task in 7.2 can be expressed in Excel Formula as follows:
=COUNTIF(D3:D9,"x") * D1
7.4) Type the formula in the Formula Box and observe the outcome of it.
7.5) How about the following scenario? We need to copy the Cell D10 and paste it to B10, C10, E10, and F10 to achieve the same outcome.
7.6) By default, Excel will guess that you want to copy the formula and therefore, the formula for the cells have been adjusted according to the relative position of the cells, i.e. for column F, the range is from F3 to F9 and the Cell containing mark value is F1.
7.7) Next, we need to perform horizontal sum of the cells. Click Cell G10 and type the formula:
=SUM(B10:F10)
7.8) As a result, we get the sum of marks for the Assessment Section A.
7.8) Finally, we will add the grand total cell to add the total marks of each section. Click the Cell G36 add type the formula:
=SUM(G1:G34)
7.9) Alternatively, you may also use the following formula:
= =SUM(G10, G18, G27, G34)
7.10) The formula in Step 7.8 is easier than Step 7.9. However, if we want to precisely select the Section Total cells, then the formula in Step 7.9 is more accurate.
F) CELL COLOURS
8) Colours help to increase readability of workbook as well as adding aesthetic look to it.
8.1) Highlight the cell and assign colours as below.
8.2) Colours also help you to communicate to those who will use the workbook. For e.g., you can tell the user that:
a) Yellow cells are the section title.
b) Light Orange cells are cells to be filled by the user.
c) Light Green cells are the auto-computed cells.
G) SHARING
9) Next, you can start sharing with the user of the document.
No comments:
Post a Comment