Music

Thursday, January 12, 2017

Week 12 - 14: Spread sheets

Spread sheets

INTRODUCTION OF MICROSOFT EXCEL


Microsoft Excel is an electronic spreadsheet. You can use it to organize your data into rows and columns. You can also use it to perform mathematical calculations quickly. This tutorial teaches Microsoft Excel basics. Although knowledge of how to navigate in a Windows environment is helpful, this tutorial was created for the computer novice.



Excel environment

Since we will be using Excel for statistical analysis, we provide a brief overview of the Excel environment with particular emphasis on the latest versions of Excel (Excel 2007, Excel 2010, Excel 2013 and Excel 2016) and the capabilities most useful for statistical analysis. Together with Excel’s online help facilities (accessible by pressing in Excel the F1 function key), this will provide you with sufficient background to carry out the analyses described in the remainder of this website.


Working with workbook and worksheet

Microsoft Excel consists of worksheets. Each worksheet contains columns and rows. The columns are lettered A to Z and then continuing with AA, AB, AC and so on; the rows are numbered 1 to 1,048,576. The number of columns and rows you can have in a worksheet is limited by your computer memory and your system resources.
The combination of a column coordinate and a row coordinate make up a cell address. For example, the cell located in the upper-left corner of the worksheet is cell A1, meaning column A, and row 1. Cell E10 is located under column E on row 10. You enter your data into the cells on the worksheet


Entering data (text, Number)

You have several options when you want to enter data manually in Excel. You can enter data in one cell, in several cells at the same time, or on more than one worksheet at once. The data that you enter can be numbers, text, dates, or times. You can format the data in a variety of ways. And, there are several settings that you can adjust to make data entry easier for you


Formatting (Font,number) and customizing data

Cell formatting The icons on the Home ribbon provide you with a variety of formatting options. To apply any of these, just select the cell or cells that you want to format, and then click the desired icon. Commonly used formatting attributes include: Font and size Bold, Italic, Underline Cell borders Background and Font color Alignment: Left, Centre or Right Merge text across multiple cells Wrap text within a cell Rotate angle of text Format number as Currency, Percentage or Decimal Increase or Decrease number of decimal places The Format Painter allows you to copy formatting attributes from one cell to a range of cells.

Editing spreadsheets

Enter the data

1. Click a cell, and then type data in that cell.
2. Press ENTER or TAB to move to the next cell.
Tip To enter data on a new line in a cell, enter a line break by pressing ALT+ENTER.
3. To enter a series of data, such as days, months, or progressive numbers, type the starting value in a cell, and then in the next cell type a value to establish a pattern.
For example, if you want the series 1, 2, 3, 4, 5..., type 1 and 2 in the first two cells.
Select the cells that contain the starting values, and then drag the fill handle across the range that you want to fill.
Tip To fill in increasing order, drag down or to the right. To fill in decreasing order, drag up or to the left.

Formulas and function

Creating / copying formulas

Microsoft Excel provide a really quick way to copy a formula down a column. You just do the following:

Enter a formula in the top cell.

Select the cell with the formula, and hover the mouse cursor over a small square at the lower right-hand corner of the cell, which is called the Fill handle. As you do this, the cursor will change to a thick black cross.
Hold and drag the fill handle down the column over the cells where you want to copy the formula.

Cell referencing

Excel is dynamic when it comes to cell addresses. If you have a cell with a formula that references a different cell’s address and you copy the formula from the first cell to another cell, Excel updates the cell reference inside the formula. Try an example:
1. In cell B2, enter 100.
2. In cell C2, enter =B2*2.
3 . Press Enter.
4. Cell C2 now returns the value 200.
5. If C2 is not the active cell, click it once.
6. Press Ctrl+C, or click the Copy button on the Home tab.
7. Click cell C3.
8. Press Ctrl+V, or click the Paste button on the Home tab

Using insert function button

1. Select the cell into which you want to enter the formula.
2. Select the Insert Function button in the Formula Bar
3. Enter a description of what you want to do and press [Return].
4. Select the desired function in the Select a function list.
5. Select OK.
6. Click the Collapse Dialog button at the right of the first argument edit box.
7. Select the range you want to use in the calculation.
8. Release the mouse button.
9. Click the Expand Dialog button.
10. Repeat steps 6 to 9 for any additional arguments you need to select.
10. Select OK

Using nested functions

A nested function uses a function as one of the arguments. Excel allows you to nest up to 64 levels of functions. Users typically create nested functions as part of a conditional formula. For example, IF(AVERAGE(B2:B10)>100,SUM(C2:G10),0). The AVERAGE and SUM functions are nested within the IF function. The structure of the IF function is IF(condition test, if true, if false). You can use the AND, OR, NOT, and IF functions to create conditional formulas. When you create a nested formula, it can be difficult to understand how Excel performs the calculations. You can use the Evaluate Formula dialog box to help you evaluate parts of a nested formula one step at a time


Using SUM/COUNT/MIN/AVERAGE/RANK

  • example of sum


  • example of min



  • example of average 

  • example of Rank

  • if statement and nested if

Working with charts

A chart is a tool you can use in Excel to communicate data graphically. Charts allow your audience to see the meaning behind the numbers, and they make showing comparisons andtrends much easier. In this lesson, you'll learn how to insert charts and modify them so they communicate information effectively.


Sorting and querying data

This example teaches you how to import data from a Microsoft Access database by using the Microsoft Query Wizard. With Microsoft Query, you can select the columns of data that you want and import only that data into Excel.

1. On the Data tab, click From Other Sources, From Microsoft Query.

The 'Choose Data Source" dialog box appears.
2. Select MS Access Database* and check 'Use the Query Wizard to create/edit queries'.
3. Click OK.
4. Select the database and click OK.
This Access database consists of multiple tables. You can select the table and columns you want to include in your query.
5. Select Customers and click the > symbol.
6. Click Next.
To only import a specified set of records, filter the data. 7. Click City from the 'Column to filter' list and only include rows where City equals New York
8. Click Next.
You can sort your data if you want (we don't do it here).
9. Click Next.
10. Click Finish to return the data to Microsoft Excel.
11. Select how you want to view this data, where you want to put it, and click OK.
Result:
Note: when your Access data changes, you can click Refresh to update the data in Excel.

Freeze pane Select the View tab from the toolbar at the top of the screen and click on the Freeze Panes button in the Window group. Then click on the Freeze Top Row option in the popup menu.


Data validation 

Linking worksheets:

Importing and Exporting data


export to word The simplest way to display Excel data in a Word document is to use Copy/Paste

Open the destination Word document.
In the source Excel spreadsheet, select the data you want to copy then hit CTRL-C.
In the destination Word document, place the cursor where you want the data, then hit CTRL-V.

The default paste will use the Keep Source Formatting (A) paste option. This preserves any formatting you have done in Excel and pastes the data into Word as a table using that same formatting. As you can see, you may need to clean up your table after the paste to make it look correct in the new document.
To change the paste option, click the Ctrl dropdown option in the bottom right corner of your new table after pasting and select a new option. Other Paste options include: Use Destination Styles (B) – This will paste the data into Word as a table and adapt the display elements into the same formatting as the Word document. Use this to make your fonts and colors consistent in the destination without having to edit in Excel beforehand.

Copy as Picture (C) – This will paste the data range as a Word image object. You will be able to resize and edit the image as you would any other picture, but you will not be able to edit the data. The paste will use the original Excel formatting to generate the picture.

Import a word table

Open a new or existing document in Microsoft Word.

Click the "Insert" tab > Locate the "Tables" group.

Select the "Table" icon > Choose the "Insert Table..." option.

Set the "Number of columns," "Number of rows," and "AutoFit behavior" to your desired specifications > Click [OK].

Open the Excel file and use your mouse to select the data you wish to import.
Right-click on the range of cells you have highlighted and select "Copy."
Switch back to Word and highlight the table cells where you want to import the Excel data.
Right-click on the Word table and click the option you want under "Paste Options."


Import a text fileThere are two ways to import data from a text file by using Microsoft Excel: You can open the text file in Excel, or you can import the text file as an external data range. To export data from Excel to a text file, use the Save As command.
There are two commonly used text file formats:

Delimited text files (.txt), in which the TAB character (ASCII character code 009) typically separates each field of text.
Comma separated values text files (.csv), in which the comma character (,) typically separates each field of text.You can change the separator character that is used in both delimited and .csv text files. This may be necessary to make sure that the import or export operation works the way that you want it to.
Notes: You can import or export up to 1,048,576 rows and 16,384 columns.

0 comments:

Post a Comment