NGN110_rec1_Excel10 (1).pdf

Introduction to MS Excel 2010 & Basic Exercises NGN110 1 Excel10 2 Basic Concepts (I) A spreadsheet is an application that manipulates numerical data that are arranged in rows and columns of cells. Columns are labelled with letters and rows are labelled with numbers. Cells can contain: – – – – Numbe...

Introduction to MS Excel 2010 & Basic Exercises NGN110 1 Excel10 2 Basic Concepts (I) A spreadsheet is an application that manipulates numerical data that are arranged in rows and columns of cells. Columns are labelled with letters and rows are labelled with numbers. Cells can contain: – – – – Numbers Text Dates Formulas e.g. = 5*A2 + B1^2 3 Basic Concepts (II) Formulas are calculated automatically when changes happen to cells. Each Excel file (called a workbook) can contain a number of worksheets (default is 3). Each cell is uniquely identified by the column and the row it belongs too. e.g. – A1: top left corner cell – C3 An alternative exists in the form of assigning unique names to particular cells of interest. 4 Navigating Through Cells Using the arrow keys. Using the mouse pointer Ctrl + Home : top left of the worksheet Ctrl+End : bottom-right of the worksheet Ctrl+→: rightmost column, same row Ctrl+←: leftmost column, same row Ctrl+↑: top row, same column Ctrl+↓: bottom row, same column 5 Data Entry Entering data in a cell requires: – Cell selection (navigate to it) – Type the data or formula that will reside in it. – Cells display the results of the calculations if any. To cancel changes press Esc if they are not applied yet. Otherwise use the Undo button. 6 Modifying a Worksheet Adding a row/column – Use the Insert button from the Home Cells group after you select the cell where the new row/column will be inserted 7 Modifying a Worksheet (II) Resizing a row/column – By selecting the lower/right border of the corresponding label and “dragging” – By double-clicking on the lower/right border of the corresponding label (auto-fit) – By right-clicking the corresponding label and selecting the appropriate menu item. 8 Modifying a Worksheet (III) Selecting Groups of Cells: – Using the mouse – Using the Shift+arrow keys Selects the whole worksheet Non-continuous cell ranges can be selected by pressing the Ctrl key while clicking with the mouse. Left-clicking on the label of a row or a column selects all its cells. 9 Formatting Cells The visual appearance of cells can be modified through: – The Font, Alignment, Number and Styles groups of the Home Tab. – The context sensitive menu (right-click). 10 Format Cells Dialog For comprehensively changing many visual attributes of a cell(s) the Format Cells dialog is the most appropriate. 11 Formulas Formulas start with an '=' sign Functions can be entered: – Manually, if you know the names and parameters used. Example: =sin(2) – Via the Formulas tab, Function Library group – Via the fx button on the Formula Bar. 12 Functions Excel provides a wealth of functions for calculations related to: – – – – Statistics Date/Time Mathematics & Trigonometry Engineering Functions can reduce the effort needed by the user. Example: =A1+A2+A3+A4+A5 =sum(A1:A5) versus 13 Simple Expressions - Example What is the difference if references are used? 14 Simplified Cell Reference Entry 15 Useful Functions Hint: essential documentation is available at the press of a key (F1). If Excel detects a possible problem in a function, it will display a hint: 16 Charts Charts provide a convenient and visually attractive medium for summarizing data. To create a chart: – Enter the data in a worksheet – Select the cells that contain the data – Select the graph type you desire from the Charts group of the Insert tab. 17 An Example The chart below was generated after selecting the cells (including the header cells) and clicking on the “Scatter” chart button. 18 Chart Tools 19 Modifying a Chart Moving: click to select the graph and Drag. Data range specification: use the “Select Data” button in the Data group of the Design Tab: New cell selection Use if series are stored in rows 20 Modifying a Chart (II) Chart type change: right-click on the graph and select the “Change Chart Type” : Common pitfall: scatter or line chart? 21 Modifying a Chart (III) Design Tab: chart Type, Data, Layouts, Style 22 Modifying a Chart (IV) Layout Tab: axes, labels, Axes, background, etc. Format tab: shape, word style and Arrange 23 Problem 1: Statistics Below find the grades for the first Exam in CHE 206: 83.5 52 71.5 46 60 43.5 35.5 82 50 49.5 55 80 46.5 33 46.5 43 73 59.5 37 59.5 Find the :1) Average 2) Median 3) Mode 4) Maximum 5) Minimum. 24 Problem 2: Plots Plot the following points on a scatter plot and find the slope: X 1.5 2.3 3.7 6.3 8.3 9.8 11 12.3 Y 2.475 3.795 6.105 10.395 13.695 16.17 18.15 20.295 25 Problem 3: Spread Sheet Find Y, for the following x= 0 to x=10: (Step every 0.5 point) Y= 4.3*X^2+2.1*X+1.4*X^0.5+12.3 26 Problem 4: Bar Chart Draw a bar chart to represent the countries below and their corresponding populations: Country USA UK Egypt China Russia UAE Population (million) 300 60 75 1300 150 4.5 27