01 Introduction to Microsoft Excel.pdf

Introduction to Microsoft Excel Zeren Lucky L. Cabanayan STAT2100 –1st Semester, 2023-2024 CENTRAL LUZON STATE UNIVERSITY DEPARTMENT of STATISTICS Learning Outcomes At the end of this lesson, you would be able to 1. Explore t...

Introduction to Microsoft Excel Zeren Lucky L. Cabanayan STAT2100 –1st Semester, 2023-2024 CENTRAL LUZON STATE UNIVERSITY DEPARTMENT of STATISTICS Learning Outcomes At the end of this lesson, you would be able to 1. Explore the different parts of Microsoft Excel 2. Navigate the workbook and set-up data 3. Familiarize with the different data types in Excel 4. Engage with the different functions in Excel 5. Install data analysis in Excel Introduction to Microsoft Excel | 2  DEPARTMENT of STATISTICS Background and Functions of Excel Microsoft Excel is a spreadsheet application which features calculation, graphing tools, pivot tables and a macro programming language called VBA (Visual Basic for Applications). Excel can be used for data entry, manipulation and presentation but it also offers a suite of statistical analysis functions and other tools that can be used to run descriptive statistics and to perform several different and useful inferential statistical tests that are widely used in business and management research It provides all of the standard spreadsheet functionality, which makes it useful for other analysis and data manipulation tasks, including generating graphical and other presentation formats. Excel can be helpful when preparing data for analysis in those packages. Introduction to Microsoft Excel | 3  DEPARTMENT of STATISTICS Background and Functions of Excel Before using Excel, you should be able to: Identify the appropriate analysis to use for different research objectives Correctly interpret the output of the analysis given by Excel Because Excel cannot think, it ONLY computes what you command them to do. Introduction to Microsoft Excel | 4  DEPARTMENT of STATISTICS Title Bar Parts of Excel Quick Access Toolbar Excel Help Function File menu/ Tab Ribbon Active Cell Formula Bar name Select All Button Column Headings Worksheet Window Active Cell Vertical Scroll Bar Row Headings Horizontal Scroll Bar Sheet Tab Scrolling Button Sheet Tabs View Zoom Shortcuts Control Status Bar Introduction to Microsoft Excel | 5  DEPARTMENT of STATISTICS -located at the upper left corner of the screen wherein you can place the Parts of Excel - requires internet most commonly used commands Quick Access Toolbar Excel Help Function connection to provide here. It is customizable. answers to inquiries File menu/ Tab -used to open Formula Bar -enter and edit values, new or saved workbooks, formulas, and text save, print or close workbooks or -consists of rows and columns Worksheet Window manage Excel that intersect to form cells options -it provides information for the active worksheet. Switch views options or zoom in/out. Status Bar Introduction to Microsoft Excel | 6  DEPARTMENT of STATISTICS Exploring Excel Worksheet Navigating the Workbook Workbook - Core document of Excel. It can hold any number of worksheets/ spreadsheets. Introduction to Microsoft Excel | 7  DEPARTMENT of STATISTICS Exploring Microsoft Excel At any one time, only one sheet is active in a workbook. Each spreadsheet is gridded into columns and rows. Columns are denoted using capital Roman letters, and rows are denoted by numbers. ACTIVE CELL NAME – referenced by their column and row. ACTIVE CELL – intersection of rows and column Introduction to Microsoft Excel | 8  DEPARTMENT of STATISTICS Exploring Microsoft Excel Creating a New Workbook File Menu > New > Blank workbook Selecting Cells Edit individual cells by clicking them. Click a cell and drag the mouse pointer to select range of cells. An entire row or column may also be selected by clicking the number or letter heading of that alignment. To select non-adjacent cells, hold down the CTRL key, and then click the cells that you want. Introduction to Microsoft Excel | 9  DEPARTMENT of STATISTICS Exploring Microsoft Excel Adding data manually Double click on the cell you want to edit Notice that whatever you type appears in both the cell and the cell editor at the top. When you are finished typing, press ENTER. Introduction to Microsoft Excel | 10  DEPARTMENT of STATISTICS Exploring Microsoft Excel Adding a new worksheet Option 1: Introduction to Microsoft Excel | 11  DEPARTMENT of STATISTICS Exploring Microsoft Excel Adding a new worksheet Option 2: Introduction to Microsoft Excel | 12  DEPARTMENT of STATISTICS Exploring Microsoft Excel Renaming worksheet Double click the Name tab; when the default name is selected, type the new name. Right click Name tab > Rename Note: Worksheets can also be color-coded. Introduction to Microsoft Excel | 13  DEPARTMENT of STATISTICS Exploring Microsoft Excel Navigating To change the active cells, use the arrow keys or use the mouse to click a new cell. Embedding chart and pictures To create a new chart in Excel, Insert > Charts group, choose the preferred chart To insert another file such as clip art, picture or a scanned, Insert > Illustrations group > Picture/Clipart Introduction to Microsoft Excel | 14  DEPARTMENT of STATISTICS Exploring Microsoft Excel Setting Up Data Inserting a new column or row To insert a column/ row, click the header where you want to add a new column/row. On the header, right click and in the drop-down context menu, select Insert. You may also use the Insert button to add new columns found in the Home tab, Cells group. Note: When you insert a column or row, content found in columns will be moved to the right, and content in rows will be moved down. Introduction to Microsoft Excel | 15  DEPARTMENT of STATISTICS Exploring Microsoft Excel Formatting the cell To formatting of a cell refers to both the way it is styled (stylistic formatting) and the way it functions (numeric formatting). Formatting includes display characteristics such as font, size, alignment, style, color, as well as the type of data that the cell contains. For instance, a cell can be formatted to treat any data entered as a monetary amount and display only whole dollar amounts. Stylistic formatting options make your worksheet more attractive and easier to read. Introduction to Microsoft Excel | 16  DEPARTMENT of STATISTICS Preparing and manipulating data in Excel Data Types The data type manages how Excel will display and interpret data in the cells. For instance, you may choose percentages as a fraction, decimal or whole number. It is important that Excel interprets your data correctly since Excel’s functions depend on the type of data being manipulated. After you type numbers in a cell, you can change the format in which they are displayed. Introduction to Microsoft Excel | 17  DEPARTMENT of STATISTICS Most commonly data types in Excel are: Number: cells that contain only numerals, commas and decimal points that can be used in numerical calculations. Click the cell that contains the numbers that you want to format. On the Home tab, in the Number group, point to General, and then click the format you want. When you click on More Number Formats on the category list, the Format cells dialog box opens (similar to that in the older versions). To add the format cells icon to your Quick Access Toolbar, right click on the More Number formats and choose the Add to Quick Access Toolbar. Percentage: multiplies the cell value by 100 and displays the value with a %. Text: cells that contain letters, numbers, spaces, or any other keyboard character. Introduction to Microsoft Excel | 18  DEPARTMENT of STATISTICS Freeze Panes This is used to keep titles in sight when you scroll down a page. To freeze panes, first “split” the worksheet so that you can look at two areas independently. On the View tab, in the Window group, select Split. A thick grey bar should appear on the sheet. To make a split, drag the grey bar to a position next to or below the title row. When you have positioned the bar where you would like it, select Freeze Panes from the Window group of the View tab. The thick grey bar will be replaced by a thin black line and the row/column will stay in place. Introduction to Microsoft Excel | 19  DEPARTMENT of STATISTICS Formulas and Functions real power of an Excel spreadsheet uses standard mathematical symbols to operate on cell addresses and/or numbers can be a combination of values (numbers or cell references) and mathematical operators into an algebraic expression Introduction to Microsoft Excel | 20  DEPARTMENT of STATISTICS Mathematical operators Symbol Use + Addition - Subtraction * Multiplication / Division % Percentage ^ Exponentiation Note: Parentheses and decimal points may be used where needed. Introduction to Microsoft Excel | 21  DEPARTMENT of STATISTICS Constructing a formula Excel requires that EVERY formula begin with an equal sign (=). If you just type without this symbol, Excel treats the entry as text. To start entering a formula in a cell, click in that cell and then type the formula. Type Enter or Tab to move to the next cell when you have finished entering the formula. Formulas containing numbers will produce results that will not change. e.g. =200*7 always produces the result 1400. However a formula that contains cell references produces a result that may change depending on the data in the cell. e.g., the formula =C2 + D2 will produce a result based on the data in cells C2 and D2. Introduction to Microsoft Excel | 22  DEPARTMENT of STATISTICS Constructing a formula To view the formulas that are contained in your worksheet, press CTRL and ‘ (the apostrophe key) All the formulas follow the standard mathematical order of operations when calculating the results. If a part of the formula is in parenthesis, then that part will be calculated first. After expressions in the parenthesis, Excel will calculate your formula using the Math operators in the following order: Multiplication, Division, Addition and Subtraction. Introduction to Microsoft Excel | 23  DEPARTMENT of STATISTICS Sample Formulas Formulas can be a combination of built-in functions and your own formulas. Some sample formulas are: = 100*20 multiplies 100 x 20, returning 2000 and only uses literal values: = C3+D3 adds the value in cells C3 and D3 = (C4 + D4) /2 adds the value in cell C4 and D4 and then divides the result by 2 = (D4 - C4) / A2 subtracts the value in the cell C4 from the value in cell D4 and then divides the result by the value in cell A2 = SUM (A1:A10) uses the SUM function to add the values in the range A1:A10 (A1 to A10) = D5^2 uses the caret (^) and POWER function to calculate the power of the value = POWER(D5,2) in cell D5 compares the value in cell E1 and value in cell F1. If the values in the two = 𝐸1 = 𝐹1 cells are identical, then the formula returns TRUE otherwise, it returns FALSE. Introduction to Microsoft Excel | 24  DEPARTMENT of STATISTICS Copy formulas into additional cells Copy formulas into additional cells by selecting the formula, then paste into the desired cell. Relative referencing –cells referenced in a formula are based on their relative location to the cell with the formula. The cell references will change depending on where you copy it. For example, if cell G2 contains the formula =C2+D2, and you copy this to G3, the resulting formula will be =C3+D3. Absolute referencing–no matter where you copy and paste your formula, your formula will always have to point exactly the same cell or cells. To do this you add $which is placed in front of the row and column references. For example, if you wanted to add cells A1 and B1 using an absolute reference, your formula would be =$A$1 + $B$1 Introduction to Microsoft Excel | 25 DEPARTMENT of STATISTICS Round values to remove decimal places To reduce decimal places click on the Decrease Decimal button. Each click of this button shrinks the result one decimal place. Be cautious with this tool though as numbers will be rounded. So, for instance, an 85.5 will be rounded to 86. Introduction to Microsoft Excel | 26  DEPARTMENT of STATISTICS Inserting a function A function is a predefined/prewritten formula that takes a value or values, performs an operation on a range of cells you select, and returns a value or values. Functions are used to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations. Excel refers to each range of cells in the function as an argument. For complex calculations, it might be necessary to use as many as 3 to 4 arguments. Introduction to Microsoft Excel | 27  DEPARTMENT of STATISTICS Inserting a function (Option 1) 1. Select the cell where you would like the function score to appear. 2. From the Formulas tab, choose Insert Function. 3. Or, you could also click on the Function button, found just before the formula bar to use any of Excel’s preset functions Introduction to Microsoft Excel | 28  DEPARTMENT of STATISTICS Inserting a function (Option 1) 4. The Insert Function dialog box appears. Introduction to Microsoft Excel | 29  DEPARTMENT of STATISTICS Inserting a function (Option 1) 5. Under Select a function, choose from the range of functions available. 6. For example, choose the SUM function and click OK. 7. The Function Arguments dialog box as shown below appears. Introduction to Microsoft Excel | 30  DEPARTMENT of STATISTICS Inserting a function (Option 1) 8. Under Number 1 type the range of cells that you want to use or you can also drag the mouse to select the range of cells to be included as the function’s arguments and click OK. 9. The formula result is displayed on the window for you as a preview. Introduction to Microsoft Excel | 31  DEPARTMENT of STATISTICS Inserting a function (Option 2) 1. Select the cell where you would like the function score to appear. 2. Click Shift + F3 3. The Insert Function dialog box appears. Type a brief description of what you want a function to do, and then choose Go. A list of functions likely to fit your needs and based on your description will display in the Select a function box. Or select a category From the drop-down list, do one of the following: Select Most Recently Used. Functions you have inserted in the recent past will display in alphabetical order in the Select a function box. Select a function category. Functions in that category will display in alphabetical order in the Select a function box. Select All. Every function will display in alphabetical order in the Select a function box. Introduction to Microsoft Excel | 32  DEPARTMENT of STATISTICS Built-in function Excel has a variety of built-in functions that can be accessed using the Formula Wizard. 1. Click in the cell where you want the result of the formula to be placed. Now click on the = sign in the formula bar. 2. Click on the drop –down arrow to the left of the formula bar to select the function you wish to use. 3. Click on the More Functions…option at the bottom of the list to display a window showing all the available functions. 4. When you have selected the function, the Insert Function dialog box opens to help you complete the arguments after the function so that Excel calculates the right result. 5. When each function is selected a short description of the function and the type of arguments to be used is displayed in the dialog box. Introduction to Microsoft Excel | 33  DEPARTMENT of STATISTICS Mathematical function Function What it does SUM Adds its arguments SUMIF Adds the cells specified by one or many given criteria SUMPRODUCT Returns the sums of the products of two arrays SUBTOTAL Returns a subtotal of a filtered list or database TRUNC Truncates a number to an integer ROUND Rounds a number to a specified number of digits Introduction to Microsoft Excel | 34  DEPARTMENT of STATISTICS Mathematical function Function What it does ROUNDUP Round a number up, away from zero INT Rounds a number down to the nearest integer ABS Returns the absolute value of a number MOD Returns the remainder from division SQRT Returns a positive square root POWER Returns the result of a number raised to a power Introduction to Microsoft Excel | 35  DEPARTMENT of STATISTICS Basic statistical built-in functions Function What it Does AVERAGE(range) Returns the average of its arguments AVERAGEA(range) Returns the average of its arguments, including numbers, text, and logical values MEDIAN(range) Returns the number in the middle of a range of data MODE(range) Returns the most frequently occurring or repetitive value in a range of data Introduction to Microsoft Excel | 36  DEPARTMENT of STATISTICS Basic statistical built-in functions Function What it Does COUNT(range) Counts how many numbers are in a range of data COUNTA(range) Counts how many values are in a range of data MAX(range) Returns the maximum value of a range MIN(range) Returns the minimum value of a range LARGE(range, n) Returns the k-th largest value in a data set SMALL(range, n) Returns the k-th smallest value in a data set Introduction to Microsoft Excel | 37  DEPARTMENT of STATISTICS Pivot Tables Excel’s very useful and powerful feature can be used to summarize, analyze, explore and present your data with ease Introduction to Microsoft Excel | 38  DEPARTMENT of STATISTICS PivotTable report is especially designed for: Querying large amounts of data Sub-totaling and aggregating numeric data, i.e., summarizing data by categories and subcategories, and creating custom calculations and formulas. Expanding and collapsing levels of data to focus your results, and drilling down to details from the summary data for areas of interest to you. Transposing data –moving rows to columns or columns to rows (or “pivoting”) to see different summaries of the source of data. Filtering, sorting, grouping, and conditionally formatting the most useful and interesting subset of data to enable you to focus on the information that you want, without having to write any formulas. Presenting concise, attractive, and annotated online or printed reports. Introduction to Microsoft Excel | 39  DEPARTMENT of STATISTICS Create a PivotTable from worksheet data When you create a PivotTable report from worksheet data, that data becomes source data for the PivotTable report. 1. Select the range of cells that contains the data along with column headings. 2. On the Insert tab, in the Tables group, click PivotTable. Introduction to Microsoft Excel | 40 DEPARTMENT of STATISTICS 3. The Create PivotTable dialog box is open. Introduction to Microsoft Excel | 41  DEPARTMENT of STATISTICS 4. Under Choose the data that you want to analyze, make sure that Select a table or range is selected, and then in the Table/Range box, make sure the range of cells that you want to use is listed. 5. Under Choose where you want the PivotTable report to be placed, choose either the New Worksheet or Existing Worksheet and click OK. Introduction to Microsoft Excel | 42  DEPARTMENT of STATISTICS 6. An empty PivotTable report is added on the specified worksheet along with the PivotTable Field List from which you can select the fields that you would like to add to create a layout and customize the PivotTable report. Introduction to Microsoft Excel | 43  DEPARTMENT of STATISTICS 7. To place any field in the default area of the layout section, select the check box next to the field name in the field section. By default, nonnumeric fields are added to the Values area , while date/time hierarchies are added to the Column Labels area. Introduction to Microsoft Excel | 44  DEPARTMENT of STATISTICS Create a PivotTable from worksheet data 8. To place a field in a specific area of the layout section, you can also right click the field name in the field section, and then select Add to Report Filter, Add to Column Label, Add to Row Label, or Add to Values. 9. You could also drag a field to the area that you want by clicking and holding the field name in the field section, and then dragging it to an area in the layout section. Introduction to Microsoft Excel | 45  DEPARTMENT of STATISTICS ExceI Statistical Functions Excel Statistical Functions You can access the statistics functions in Excel via Formulas > More Functions > Statistical which opens up a menu of available function Introduction to Microsoft Excel | 46  DEPARTMENT of STATISTICS Example of Statistical Functions Function name Description AVERAGE Returns the arithmetic mean (average) of the given numbers CHISQ.DIST.RT Returns the right-tailed probability for the chi squared distribution CHISQ.TEST Returns the p-value for the chi-squared test of association CONFIDENCE.T Returns the margin of error for a confidence interval for the mean COUNT Counts the number of cells in a range that contain numbers COUNTIF Counts the number of cells in a range that meet a given condition COUNTA Counts the number of cells in a range that are not empty KURT Returns the kurtosis of a dataset MAX Returns the maximum value of the given numbers Introduction to Microsoft Excel | 47  DEPARTMENT of STATISTICS Example of Statistical Functions Function name Description MEDIAN Returns the median of the given numbers MIN Returns the minimum value of the given numbers MODE.SNGL Returns the mode of the given numbers PEARSON Returns the Pearson correlation coefficient (r) of two variables SKEW Returns the skewness of a dataset STDEV.P Returns the stdev of the given numbers, based on the population STDEV.S Returns the stdev of the given numbers, based on a sample VAR.P Returns the variance of the given numbers, based on the population VAR.S Returns the variance of the given numbers, based on a sample Introduction to Microsoft Excel | 48  DEPARTMENT of STATISTICS Example of Statistical Functions Selected range Result Cell selected as destination for the function output Introduction to Microsoft Excel | 49  DEPARTMENT of STATISTICS Installing data analysis in Excel Statistical Analysis Microsoft Excel has numerous Add-in features that support statistical analysis. Statistical data analysis in Excel is not recommended for analyzing datasets with a large sample size or a large number of variables, performing advanced statistical analyses, or for projects in which a number of procedures need to be performed. Introduction to Microsoft Excel | 50  DEPARTMENT of STATISTICS Disadvantages: Missing values are handled inconsistently, and sometimes incorrectly. Data has to be organized differently according to the analysis you wish to perform. Most analyses can only be done on one column at a time. This makes it inconvenient to do the same analysis on many columns. There is no log or record of how an analysis was accomplished. It also lacks many important features for advanced analyses. Introduction to Microsoft Excel | 51  DEPARTMENT of STATISTICS Installing data analysis in Excel 1. Open Excel 2. Click File Tab Introduction to Microsoft Excel | 52  DEPARTMENT of STATISTICS Installing data analysis in Excel 3. Click Options, and then click Add-Ins Introduction to Microsoft Excel | 53  DEPARTMENT of STATISTICS Installing data analysis in Excel 4. In View and Manage Box, select Analysis ToolPak, then click Go. Introduction to Microsoft Excel | 54  DEPARTMENT of STATISTICS Installing data analysis in Excel 5. In the Add-Ins Available Box, select Analysis ToolPak check box and click OK. (If ToolPak Is Not Listed, Click Browse to Locate It.) Introduction to Microsoft Excel | 55  DEPARTMENT of STATISTICS Setting up data for Analysis Typically, there are two options for getting your data into Excel: 1. Import the data in a suitable format. 2. Enter the data manually. If your going to enter your data manually, use a single worksheet to hold all the data in your dataset and set up the worksheet with variables (questions) as the columns and the cases (respondents) as the rows. Introduction to Microsoft Excel | 56 DEPARTMENT of STATISTICS Preparing and manipulating data in Excel Sample Formulas Formulas can be a combination of built-in functions and your own formulas. Some sample formulas are: =200*7 multiplies 200 x 7, returning 1400 and only uses literal values: =B2 +C2 adds the value in cells B2 and C2 subtracts the value in the cell B3 from the value in cell B2 and =(B2 –B3) * B4 then multiplies the result by the value in cell B4 =SUM(C1:C12) uses the SUM function to add the values in the range C1:C12 compares the value in cell A1 and value in cell B1. If the values in =A1 = B1 the two cells are identical, then the formula returns TRUE otherwise, it returns FALSE. Introduction to Microsoft Excel | 57  DEPARTMENT of STATISTICS Exploring Microsoft Excel Worksheet -consists of rows and columns that intersect to form cells File Menu/File Tab -used to open new or saved workbooks, save, print or close workbooks or manage Excel options Quick Access Toolbar -located at the upper left corner of the screen wherein you can place the most commonly used commands here. It is customizable. Formula Bar -enter and edit values, formulas, and text Status Bar -it provides information for the active worksheet. Switch views options or zoom in/out. Excel Help Function - requires internet connection to provide answers to inquiries Introduction to Microsoft Excel | 58