1 / 73

What is Excel ? Spreadsheet Terminology Opening a new Workbook Excel 2007’s Interface

Excel Introduction Outline. What is Excel ? Spreadsheet Terminology Opening a new Workbook Excel 2007’s Interface Downloading & Saving Templates Selecting Cells Entering and Editing Data Saving Workbook Changes & Closing a Workbook Moving around a Worksheet Selecting Ranges

tosca
Download Presentation

What is Excel ? Spreadsheet Terminology Opening a new Workbook Excel 2007’s Interface

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Excel Introduction Outline • What is Excel? • Spreadsheet Terminology • Opening a new Workbook • Excel 2007’s Interface • Downloading & Saving Templates • Selecting Cells • Entering and Editing Data • Saving Workbook Changes & Closing a Workbook • Moving around a Worksheet • Selecting Ranges • Selecting, Moving, and Copying Data • Fill Handle and Auto Fill • Resizing Columns and Rows • Word Wrapping • Inserting Columns and Rows • Deleting Columns and Rows • Clearing Cells • Formulas and Functions • Copying Formulas and Functions (Relative versus Absolute Referencing) • TextFormatting • Aligning Cell Contents • Cell Borders and Shading • Number Formatting • Page Formatting /Printing Excel Introduction

  2. What is Excel and why use it? Excel is a spreadsheet application program. Spreadsheet characteristics: • Organized as a large 2 dimensionaltable of columns and rows • Columns are labeled with alphabetic letter combinations • Rows are labeled with numbers • The column label followed by the row label identifies the address of each cell • Excel 2007 provides approximately 1 Million cells • Text and numeric data is entered into cells • Text and numeric data can be sorted, summarized, and processed with mathematical and logical operations. Spreadsheets have broad application in both the business and home environments. Typical business uses include accounting, statistical analysis, and project management. Business forms are easily created (e.g., invoices, purchase orders, etc.) Excel Introduction

  3. Spreadsheet terminology A spreadsheet is a matrix of columns and rows. The columns are labeled with sequential alphabetic letters (A, B, … Z, AA, AB, …AZ, BA, BB…BZ, etc.) and the rows are labeled with sequential numbers (1, 2, …). At the intersection of each column and row is a cell, into which you enter data. Each cell has a unique address or cell reference, which consists of its column letter and row number. For example, the top left cell is A1. A cell can contain any of the following: • A number (with or without associated decimal points, commas, currency symbols, percentage symbol, etc.) • Text (including any combination of letters, numbers, and symbols that aren't number-related) • A formula • a mathematical equation • always begins with an equal sign • can include math operators, cell references, numbers, parenthesis • + Addition • - Subtraction • * Multiplication • / Division • ^ Exponential • example: =D4/3+D5+(D6+D7)*2 • A function • A formula that includes a predefined shortcut Name that performs a more complex mathematical or logical calculation • Example: =AVERAGE(B2:B100) returns the average value of the numbers in cells B2 through B100 by summing the values in the range of cells from B1 through B100 and dividing by the number of entries in that range of cells • Excel provides a library of functions to perform mathematical, statistical, financial, logical and other calculations Excel Introduction

  4. Practice: Open a new Excel Workbook Select Start > All Programs > Microsoft Office > Microsoft Office Excel 2007 This opens a new Excel workbook as illustrated below. A workbook consists of one or more tabbed sheets called worksheets (Microsoft's term for spreadsheet). Observe the three tabbed sheets at the bottom labeled Sheet1, Sheet2, and Sheet3. Sheet Tabs Excel Introduction

  5. Excel 2007 Window Microsoft Office Button Quick Access Toolbar Tabs Groups Ribbon Column Headers Function Bar Insert Function Button Cells Cells are outlined by light gray lines that divide rows and columns. Row Headers Sheet Tabs Insert New Worksheet Tab Scroll Bars Zoom Control Status Bar Excel Introduction

  6. Microsoft Office Button: Opens the only drop down menu in Excel 2007 – contains commands for opening and saving workbooks, printing worksheets, and other controls. Quick Access Toolbar: This is a user customized toolbar to place shortcuts to your favorite commands. By default, it contains Save, Undo, and Redo buttons. Ribbon: This is the multi-tabbed toolbar system that contains buttons and other controls for issuing commands. Tabs/Groups/Commands: Commands with similar attributes are grouped under distinctive Groups that are organized under Tabs . Click on a Tab to display the associated Groups of Commands. Insert Function button: You click this button to get help creating functions Formula bar: Displays contents of the selected cell. You can type and edit the cell’s contents here. Column headers: Labeled with consecutive alphabetic letters. Click a column letter to select the entire column. Row headers: Labeled with consecutive numbers. Click a row number to select the entire row. Scroll bars: Scroll within the active worksheet. Zoom controls: Change the magnification at which you view the active worksheet. Worksheet tabs: You can click one of these tabs to switch between worksheets Insert New Worksheet Tab: Click to insert a new worksheet. . Excel Introduction

  7. Microsoft Office Button: clicking displays Excel 2007’s only drop down menu. The following are the primary buttons you’ll use: • New: click to open a new workbook • Open: click to open an existing (already saved) workbook • Save: click to save updates to an existing (previously saved) workbook • Save As: click to save an existing workbook but with a different name, or save an existing workbook to a different location (Storage Device or folder), or save as a different file format other than the default .xlsx extension, etc. • Print: click to print a worksheet • Note: • Clicking directly on any Command button invokes Excel’s default operation • Command buttons with arrows to the right provide additional options other than the default operation: • flyover to view additional options in the right column • click on any option in the right column Excel Introduction

  8. Quick Access Toolbar: This is a user customized toolbar to place shortcuts to your favorite commands. The following are default buttons: • Save • Undo • Redo My optional buttons: Print Preview, Print, Open, New, Spell Check Excel Introduction

  9. Ribbon: Each Ribbon Tabhas named sections called groups. In this example, the Home tabis selected, which contains the Clipboard, Font, Alignment, Number, Styles, Cells, and Editing groups. Some of the groups have a Dialog box launcher icon in their lower-right corner, which opens a dialog box containing more options for the control settings in that group. Note: Group details automatically expand or collapse based on the width of the Excel window. Excel Introduction

  10. Example: Clicking on the Dialog box launcher in the corner of the Alignment Group under the Home tab, opens the Alignment Tab in the Format Cells dialog box for more options. Excel Introduction

  11. Download a Templateentitled Expense report Click on Microsoft Office Button select New > The New Workbook dialog box opens Select Expense reports under the Microsoft Office Online window pane Select Expense report in middle window pane Click Download Excel Introduction

  12. The template Expense report1 workbook opens on your desktop Excel Introduction

  13. Save the Workbook as Expense report1.xlsx Attach your Flash Drive and save the Workbook as Expense report1.xlsx on your Flash Drive Click the Microsoft Office Button and select Save As The Save As dialog box pops-up Select My Computer > all storage devices attached to your computer will display in the right window pane Double click your Flash Drive (note: your Flash Drive Label and Drive Letter will be different) Navigate to any desired folder location on your Flash Drive and then click Save Verify the File name is correct. Otherwise, retype the name as shown here. Excel Introduction

  14. All Roads Lead To Rome Pick one or two Roads you like the most and stick with it! With almost every topic presented in this lesson, there are multiple methods available to accomplish each specific Excel task. In most cases, I have presented all the available methods. As I present this material, I will focus on one or two methods and only mention the other methods in passing (in most instances). Pick one or two Methods you like the most and stick with it! Excel Introduction

  15. Selecting a cell • Before entering or editing data in a cell, you first select it. Selection Options: • Click the cell (a thick outline highlights the selected cell) • Press the arrow keys on the keyboard to move the cell selector • Press the Tab key on your keyboard to move the cell selector to the right • Press Shift+Tab to move the cell selector to the left • Press the Home key to move to the leftmost cell in the same row • Press the Ctrl+Home key to move to cell A1 • Press the Ctrl+End key to move to the highest cell address used • Press Ctrl+Backspaceto bring the active cell into view if you have scrolled away from the worksheet area displaying the active cell • Entering Data • After selecting a cell, data can also be edited directly in the cell or in the Function Bar. • Aborting Data Entry • As you are typing, if you change your mind about entering or editing data in the cell, press Esc before pressing Enter or moving to a different cell. Once moving away from a cell, press Ctrl+z or click the Undo Button on the Quick Access Toolbar to undo the last action. • Editing Data • You can change the contents of a cell directly in the cell by double clicking in the cell or by selecting the cell and clicking in the data displayed in the Function Bar. Excel Introduction

  16. Practice Entering and Editing Data • In cell C4, type Attend Tea Party Conference • In cell C7, type your name • In cells B11 through K12, type the entries as shown in this example (do not enter the $ signs) • Select cell L29 and change the text to Balance Due Click on any of the following cells E26:L26, cells M11:M25, cell M27, and cell M29 and observe that the formula of the selected cell displays in the Function Bar. Also observe how Excel automatically recalculates the entire worksheet and returns updated values after each expense item is entered. Excel Introduction

  17. Save Your Workbook Changes • To save changes to a previously saved file, choose from the following: • Click the Microsoft Office Button, and then click Save • Click the Save buttonon the Quick Access Toolbar • Press Ctrl+s • Close Workbook Expense report1.xlsx • To close an open workbook, do any of the following: • Click the Microsoft Office Button, and then click Close • Click the Close X iconin the upper right hand corner of the Excel Window • Right click on the Title Bar and click Close • Press Alt+F4 • If changes have been made since the last time you saved the workbook, Excel will prompt you to save the changes as follows: • Note that only the active workbook is closed. If other Excel workbooks are open, Excel does not close but now displays another open workbook. In this example, a new workbook with the default name Book1 is still open. Excel Introduction

  18. Moving around a worksheet Each blank spreadsheet is much larger than can be displayed on the screen at once (Excel 2007 provides approximately 1 Million blank cells). With very large tables of data, you may not be able to display the entire used worksheet area at the same time. The simplest method of viewing the non-visible worksheet area is using the horizontal and vertical scroll bars. • To use a scroll bar: • To scroll a little bit at a time, click a scroll arrow at one end or the other of a scroll bar. • To scroll one screen at a time, click above or below the vertical scroll box or to the right or left of the horizontal scroll bar. • To scroll quickly, drag the scroll box. • Alternative method using keyboard: • Page Down: Down one screen • Page Up: Up one screen • Alt+Page Down: Right one screen • Alt+Page Up: Left one screen Vertical Scroll Bar Scroll Boxes Horizontal Scroll Bar Excel Introduction

  19. Selecting ranges • A range is a group of cells. By selecting a range, you can then perform an action on the entire group of cells with a single operation, such as applying formatting or clearing the contents. A range normally extends from two cells, one or more rows of cells, one or more columns of cells, or large blocks of cells. • A range is referenced by the upper left and lower right cells separated with a colon. For example, the range of cells A1 through G10 would be referred to as A1:G10. • To select a range: • With the mouse: Drag across the desired cells. Be careful to position the mouse pointer over the center of the cell, and not over an edge or corner. • With the keyboard: Select the first cell, and then hold down the Shift key while you press the arrow keys to expand the selection area. • To select a nonrectangular or noncontiguous range, select the first portion of the range (that is, the first rectangular piece), and then hold down the Ctrl key while you select additional cells/ranges with the mouse. • To select an entire column, click the column header . To select an entire row, click the row header. You can click one row or column and then drag to select additional columns, or hold down Ctrl as you click on the headers for noncontiguous rows and/or columns. Excel Introduction

  20. Practice selecting ranges In Book1, Sheet1: Select column B by clicking on the column B header. Select contiguous columns B through G by pressing and holding the Shift key and then click on column G. Release the Shift key. Select column A by clicking on the column A header, which also deselects any prior selection Select non-contiguous columns A, E, G and K by pressing and holding the Ctrl key and then click on columns E, G, and K. Release the Ctrl key. Select row 4 by clicking on row 4’s header. The columns are deselected, and row 4 is now selected. Select cell C2 which also deselects the prior selection. Select the range C2:F10 by pressing and holding Shift key by dragging the mouse across cells C2 to F10. Release the Shift key. In addition to selecting range C2:F10, also select the non-contiguous range H2:K10 by pressing and holding the Ctrl key and dragging the mouse across cells H2 to K10. Release the Ctrl key. Select the entire spreadsheet by pressing Ctrl+a. (Remember that Ctrl+a is a universal shortcut for selecting all.) Undo the selection by clicking in any cell or by pressing the Esc key An alternate method for selecting the entire spreadsheet is to press the square containing a gray triangle at the intersection of the column and the row headers in the upper left corner. Excel Introduction

  21. Moving Data • You will find the need to move data from one cell to another or to move a range of cell contents from one range into another. • You can easily move cell contents between cells either with Drag-and-Drop or Cut-and-Paste methods. • Drag-and-Drop: • Select the cell or range, position the mouse over any edge (the mouse cursor changes to a four headed arrow) and then drag the selection by its border to the new location. • Cut-and-Paste using the keyboard: • Select the cell or range • Press Ctrl+x(cuts selection to Clipboard) • Select the upper left cell in the new range to move to • Press Ctrl+v(pastes from Clipboard) • Cut-and-Paste using Right Click: • Select the cell or range • Right click and select Cut (cuts selection to Clipboard) • Select the upper left cell in the new range to move to • Right click and select Paste (pastes from Clipboard) • Cut-and-Paste using the Clipboard group under the Home tab • Select the cell or range • Click the scissors cut icon in the Clipboard group under the Home tab (cuts selection to Clipboard) • Select the upper left cell in the new range to move to • Click the Paste icon in the Clipboard group in the Home tab (pastes from Clipboard) Excel Introduction

  22. Copying Data • You will find the need to copy data from one cell to another or to copy a range of cell contents from one range into another. • You can easily copy cell contents between cells either with Drag-and-Drop or Copy-and-Paste methods. • Drag-and-Drop: • Select the cell or range, position the mouse over any edge (the mouse cursor changes to a four headed arrow), press and hold the Ctrl key and then drag the selection by its border to the new location. • Copy-and-Paste using the keyboard: • Select the cell or range • Press Ctrl+c(copies selection to Clipboard) • Select the upper left cell in the new range to copy to • Press Ctrl+v(pastes from Clipboard) • Copy-and-Paste using Right Click: • Select the cell or range • Right click and select Copy (copies selection to Clipboard) • Select the upper left cell in the new range to copy to • Right click and select Paste (pastes from Clipboard) • Copy-and-Paste using the Clipboard group under the Home tab • Select the cell or range • Click the scissors copy icon in the Clipboard group under the Home tab (copies selection to Clipboard) • Select the upper left cell in the new range to copy to • Click the Paste icon in the Clipboard group in the Home tab (pastes from Clipboard) Excel Introduction

  23. Fill Handle and Auto Fill for copying data The black square in the lower-right corner of a selected cell or range is called the Fill Handle • Copy a cell’s content or the contents of a range of cells by positioning the mouse over the Fill Handle and then dragging across the cell(s) you want to copy to. • In one operation, you can either copy across columns or across rows, but not both. • Auto Fill: • Auto Fill is a special case of using the Fill Handle. When using the Fill Handle, Excel automatically recognizes common incremental steps in the data and assumes you want to continue the same sequence. Examples: • Date data: dates, names of days, names of months, etc. • two or more cells with consistent increments in numbers (e.g., 1,2; 25,50; 100, 200; etc.) Fill Handle Excel Introduction

  24. Practice Moving data • In a Book1, Sheet1, type data in cells A1 through B2 as follows: • A1: your first name • B1: your last name • A2: 100 • B2: 125 • Move the range A1:B2 with the drag-and-drop method: • Select the range A1:B2. • Position the mouse pointer over any border of the range except in the lower-right corner (The mouse cursor changes to a four headed arrow.) • Move the selection by dragging the selection to D4:E5 Excel Introduction

  25. Practice Moving data (continued) • Undo the last operation by clicking the Undo button on the Quick Access Toolbar • Move the range A1:B2 with the keyboard cut-and-paste method: • Select the range A1:B2. • Cut the selection to the clipboard by pressing Ctrl+x • Select cell D4 (the upper most left cell in range D4:E5) • Copy the selection on the clipboard to D4:E5 by pressing Ctrl+v • Undo the last operation by pressing the universal undo shortcut Ctrl+zkey • Move the range A1:B2 with the right click cut-and-paste method: • Select the range A1:B2. • Cut the selection to the clipboard by right clicking and selecting Cut • Select cell D4 (the upper most left cell in range D4:E5) • Copy the selection on the clipboard to D4:E5 by right clicking and selecting Paste • At home, you may also want to try moving the range with the Clipboard group under the Home Tab Excel Introduction

  26. Practice Copying Data • Copy the range D4:E5 with the drag-and-drop method: • Select the range D4:E5 • Position the mouse over any edge (the mouse cursor changes to a four headed arrow), press and hold the Ctrl keyand then drag the copy of the selection by its border to cell range A1:B2. • Undo the last operation (use your preferred method) • Copy the range D4:E5 with the keyboard copy-and-paste method: • Select the range D4:E5 • Copy the selection to the clipboard by pressing Ctrl+c • Select cell A1 (the upper most left cell in range A1:B2) • Paste the selection on the clipboard to A1:B2 by pressing Ctrl+v • Undo the last operation (use your preferred method) • Copy the range D4:E5 with the right click cut-and-paste method: • Select the range D4:E5. • Copy the selection to the clipboard by right clicking and selecting Copy • Select cell A1 (the upper most left cell in range A1:B2) • Paste the selection on the clipboard to A1:B2 by and selecting Paste Excel Introduction

  27. Practice Copying using the Fill Handle and Auto Fill Copy cell B1 into cells B1 through M1 using the Fill Handle: Select cell B1 Position the mouse over the Fill Handle (the mouse cursor changes to a cross) Drag the mouse to cell M1 (your last name is copied to each cell in the range B1:M1) Copy cell A2 into cells A3 through A10 using the Fill Handle: Select cell A2 Position the mouse over the Fill Handle (the mouse cursor changes to a cross) Drag to cell A10 (the number 100 is copied to each cell in the range A3:A10) Excel Introduction

  28. Practice Copying using Auto Fill • Use Auto Fill to continue the sequence 100, 125 into cells C2 through M2: • Select cell range A2:B2 • Position the mouse over the Fill Handle (the mouse cursor changes to a cross) • Drag to cell M2 (the sequence of incrementing the number by 25 is continued to cell M2) • Select Sheet 2 of Practice.xlsx and use Auto Fill to create the sequence January through December in cells B1 throughM1: • Select cell B1 and type January, press Enter • Select cell B1 and position the mouse over the Fill Handle (the mouse cursor changes to a cross) • Drag to cell M1 (the sequence of incrementing the names of the months continues to column M) Excel Introduction

  29. Practice Copying using Auto Fill (continued) • Use Auto Fill to create the sequence Monday through Sunday in cells A2 through A8: • Select cell A2 and type Monday, then press Enter • Select cell A2 and position the mouse over the Fill Handle (the mouse cursor changes to a cross) • Drag to cell A8 (the sequence of incrementing the days of the week continues to row 8) Excel Introduction

  30. Resizing Columns • When text exceeds the width of a cell, the text overflows the cell. If the adjacent column’s cell is empty, the text continues into the adjacent cell. If data is present or is later entered in the adjacent cell, then the display of overflow portion of the contents of the first column truncates (the truncated portion is retained but does not display). • Observe that cells A4 and M1 overflow but continue to display in the adjacent cells since the adjacent cells are empty. • Observe that cells J1 and L1 overflow but do not continue to display in the adjacent cells since the adjacent cells contain data. • You can resize any columns to increase their width to prevent overflowing into the adjacent cells. • Note: when a number (including dates and time) is entered in a cell and the column width is too narrow to display the entire content, the entire cell will display hash marks (####). You must resize the column to display the number. • Save your workbook as Practice.xlxs Excel Introduction

  31. Resizing columns (continued) • You resize a single column width using one of four methods: • On the column header, position the cursor on the right border of the column to resize (the cursor will change to a cross with right and left arrows on the cross’s horizontal line) and drag the border left to make the column width smaller and drag the border right to make the column width larger. • On the column header, position the cursor on the right border of the column to resize (the cursor will change to a cross with right and left arrows on the cross’s horizontal line) and double click. The column width will adjust so that the widest cell entry in the column exactly fits. • Select the column to resize, then on the Home tab, in the Cells group, select Format > AutoFit the Column Width . The column width will adjust so that its widest entry exactly fits. • You can also specify an exact width as a number. The default column width 8.43. To specify exact width: • Right-click a selected column, and then select Column Width... Enter the desired column width in the Dialog Box and click OK. • Alternately, on the Home tab, in the Cells group, select Format > Column Width. Enter the desired column width in the Dialog Box and click OK. • You can also resize one or more ranges of columns by selecting the desired range(s) and using then using the same options as when resizing a single column. Excel Introduction

  32. Word Wrap • An alternative to resizing columns to display text is to set a cell or range of cells to Wrap text. When set, if the cell text overflows the width of the cell, the row height adjusts to provide for two or more rows of text to exactly fit the entire text content. • Wrap text is set with one of two methods: • Select the cell or range of cells • Right click and select Format Cells… The Format Cells Dialog Box pops-up • On the Alignment tab, check the Wrap text checkbox, and then click OK. • Select the cell or range of cells • On the Home tab, in the Cells group, select Format > Format Cells... On the Alignment tab, check the Wrap text checkbox, and then click OK. • Note: Wrapping text only works with text and not numbers. Excel Introduction

  33. Resizing rows • You resize row heights with similar methods as resizing columns, although this isn't quite as important because row height adjusts automatically when entering data to accommodate the largest font used in that row. • You resize a row height using one of four methods: • On the row header, position the cursor on the bottom border of the row to resize (the cursor will change to a cross with up and down arrows on the cross’s vertical line) and drag the border up to make the row width smaller and drag the border down to make the row width larger. • On the row header, position the cursor on the bottom border of the row to resize (the cursor will change to a cross with up and down arrows on the cross’s vertical line) and double click. The row width will adjust so that the tallest cell entry in the row exactly fits. • Select the row, then on the Home tab, in the Cells group, select Format > AutoFit Row Height . • You can also specify an exact height as a number. The default row height is 15. To specify exact height: • Right-click a selected row, and then select Row Height... Enter the desired row height in the Dialog Box and click OK. • Alternately, on the Home tab, in the Cells group, select Format > Row Height... Enter the desired row height in the Dialog Box and click OK. • You can also resize one or more ranges of rows by selecting the desired range(s) and using then using the same options as when resizing a single row. Excel Introduction

  34. Inserting Columns or Rows • You will often need to insert one or more columns or rows. • Insert one or more columns or rows with one of several methods: • Right click method • Select one or more columns or rows where you want the new column(s) or row(s) to be inserted. • Right click and select Insert. • Ribbon method • Select one or more columns or rows where you want the new column(s) or row(s) to be inserted. • On the Home tab, in the Cells group, select Insert. • Alternatively, if you want to insert only one column or row, you don’t have to select the entire column or row but you can click in any cell in the column or row. • Using the Right click method, the Insert Dialog Box pops-up • Select Entire column or Entire row and click OK • Using the Ribbon method, a drop down menu appears • Select Insert Sheet Columns or Insert Sheet Rows Excel Introduction

  35. Deleting Columns or Rows • Delete one or more columns or rows with one of several methods: • Right click method • Select the column(s) or row(s) to delete. • Right click and select Delete. • The selected column(s) or row(s) will be removed. • Ribbon method • Select the column(s) or row(s) to delete. • On the Home tab, in the Cells group, click Delete. • The selected column(s) or row(s) will be removed. • Alternatively, if you want to delete only one column or row, you don’t have to select the entire column or row but you can click in any cell in the column or row’s deletion point. • Using the Right click method, the Delete Dialog Box pops-up • Select Entire column or Entire row and click OK • Using the Ribbon method, a drop down menu appears • Select Delete Sheet Columns or Delete Sheet Rows Excel Introduction

  36. Clearing Cells • Clearing a cell or range of cells is a different operation than deleting a cell or range of cells: • Deleting a cell removes the cell or range of cells from the spreadsheet . • Clearing a cell or range of cells’ removes the contents of the cell(s) but retains the respective cell(s) in the spreadsheet. • You can clear a cell or range of cells in several ways: • Select the cell(s) and press Delete on the keyboard. • Select the cell(s), Right click and then select Clear Contents. • Select the cell(s), on the Home tab, in the Editing group, select the Clear icon, then select Clear Contents. • Clearing a cell's content doesn't clear its formatting. To clear formatting: • Select cell(s) • On the Home tab, in the Editing group, select the Clear icon, then select Clear Formats. • To simultaneously clear both a cell(s) contents and formatting: • Select cell(s) • On the Home tab, in the Editing group, select the Clear icon, then select Clear All. Excel Introduction

  37. Formulas • Excel's intrinsic value is the capability to "crunch numbers" using formulas and functions. • A Formula is a mathematical equation that returns a result. • always begins with an equal sign • can include math operators, cell references, numbers, parenthesis • + Addition • - Subtraction • * Multiplication • / Division • ^ Exponential • Practice: • In a new worksheet, type 2 in cell A1 and 3 in cell A2. • In cell A3, type =A1+A2 and press Enter. • In cell A4, type =A1*A2 and press Enter • Select cell A3 and observe the cell A3’s formula displays in the Function Bar. • Select cell A4 and observe the cell A3’s formula displays in the Function Bar. • Observe that cell A3 returns 5 (2+3) and cell A3 returns 6 (2*3) • Select cell A1 and type 5 and press Enter • Observe that cell A3 now returns 8 (5+3) and cell A4 now returns 15 (5*3) Excel Introduction

  38. Order of Mathematical Operations and Grouping • When multiple math operators are used in the same formula, each operation is processed in a specific order as follows: • Any operations that are in parentheses, from left to right • Exponentiation (^) • Multiplication (*) and division (/) • Addition (+) and subtraction (-) • Each of following examples returns a different result: • =4+4/4^2 (returns 4.25) • =(4+4/4)^2 (returns 25) • =(4+4)/4^2 (returns .5) • =((4+4)/4)^2 (returns 4) Note: I recommend always using parentheses to avoid unintended consequences. • Practice: • In a new worksheet • Enter 1 in cell A1 • Enter 2 in cell A2 • Select A1:A2 and Auto Fill down through cell A6 (drag the Fill Handle to cell A6) • Select cell A7 and enter a formula to summarize cells A1 through A6 (=A1+A2+A3+A4+A5+A6) • Cell A7 returns 21 Excel Introduction

  39. Functions • A Function is a shortcut syntax of a formula to perform a more complex mathematical or logical calculation. • Example: If you wanted to sum the contents of cells A1 through A1000, it would be totally impractical to type the formula = A1+A2+A3+A4+…………………………………………………………………+A1000. To simplify this operation, Excel provides a function call SUM. In this example, one would simply type =SUM(A1:A1000) to accomplish the same results. • A Function has these characteristics: • Begins with a predefined name • Always includes right and left parenthesis () • Most require one or more arguments which are included inside the parenthesis and separated by commas • One or more Functions may be imbedded within a cell’s formula. Example: • = A1+ROUNDUP(SUM(B1:B100),0) • Excel provides an extensive library of functions, but here’ a very small sample of commonly used functions: • =SUM returns the sum of the numbers in a range • =AVERAGE returns the average of the numbers in a range • =COUNT returns the count of the number of cells containing numbers in a range • =MIN returns the lowest number in a range • =MAX returns the highest number in the range • =TODAY returns today's date • =NOW returns the current date and time • =IF tests a specified condition and returns one result if the condition is True and another result if the condition is False Excel Introduction

  40. Practice using Functions Select Sheet 1, Practice.xlsx In cell N2, type the function =sum(a2:m2) and press Enter > cell N2 should now return 3250 Note: When typing alphabetic letters in formulas or functions, you can enter lower case letters . Excel will automatically change to capitalized letters Excel Introduction

  41. Using the insert function feature Since it’s impractical to memorize the syntax and arguments for the hundreds of Excel functions, Excel provides an Insert Function feature to assist you in finding the function’s syntax and arguments. Insert Function • Practice Using Insert Function • In cell A11, use Excel’s Insert Function to locate and insert the Sum function. • Select cell A11 and click on the Insert Function button in the Function Bar or on the Formulas tab, under the Function Library group, click on Insert Function. • Type Sum in Search for a function: box and click Go Excel Introduction

  42. Practice Using Insert Function (continued) • The Insert Function dialog box now displays all functions staring with Sum • Select Sum and click OK • The Function Arguments dialog box pops-up • In the Number 1 box, type the range b5:d5 and click OK Excel Introduction

  43. Finding and inserting functions using the assist drop down menu: By either knowing or assuming you know the desired function’s syntax, start by typing = followed by the functions presumed syntax. Excel will display all functions that match your input. Select the function you intended and read the associated message to verify its purpose. Once you’ve determined that you’ve selected the intended function, double click the desired function in the list. Then either type the required arguments or drag the mouse over the required arguments and press Enter. In this example, select cell F5 and type =sum Double click the SUM function Drag the mouse cursor over cells D5 and E5 (this enters D5:E5 to the SUM function Press Enter Excel Introduction

  44. Copying Formulas and Functions Using Relative References When copying a formula or function to another row or column, Excel’s default mode is to copy the formula or function by adjusting the formulas’ argument references to reflect the row or column being copied to. This is referred to relative referencing. • Practice: • In cell B3, type 101 • In cell B4 type 102 • Select cells B3:B4 and Auto Fill down through cell B10 Excel Introduction

  45. Copying Formulas and Functions Using Relative References (continued) • Select cells A3:B10 and Auto Fill across column M • Observe that row 3 increments by 1, row 4 increments by 2, row 5 increments by 5, etc……. • In cell A11, enter function to sum cell A2 through A10 (returns 900) • Use the Fill Handle to copy cell A11 across the columns to cell M1 • Use the Fill Handle to copy cell N2 down through the rows to cell N11 Excel Introduction

  46. Copying Formulas and Functions Using Relative References (continued) • Press Ctrl+`(accent) to display all formulas and functions in their respective cells • Observe that each column on row 11 sums the respective columns cells using Relative Referencing • Observe that each row in column N sums the respective rows cells using Relative Referencing • Press Ctrl+`(accent) to display values Excel Introduction

  47. Copying Formulas and Functions Using Absolute References • Using Absolute Reference, the cell reference(s) don't change when you move or copy a formula or function. • A dollar sign $ is place in front of a column and/or row reference to lock the respective reference when moving or copying the cell. • Example: • A1 is a relative reference to cell A1 • $A$1 is an absolute reference to A1, which locks both the column (A) and the row (1) when copied or moved • A$1 is an absolute reference to A1 which does not lock the column but locks the row when copied or moved • $A1 is an absolute reference to A1 which lock s the column but does not lock the row when copied or moved • Note: • When creating an Absolute Reference, after entering the reference (assume it is A1 for this example) you can toggle the F4 key to toggle between A1 > $A$1 > A$1 > $A1 > A1……. Excel Introduction

  48. Copying Formulas and Functions Using Absolute References (continued) • Practice copying using Absolute References using Sheet 1, Practice.xlsx: • In cell P1, enter .0625 (an assumed tax rate) • In cell A12, enter the formula =A11*$P1 (lock s the column when copying) • Use the Fill Handle and copy cell A12 through cell N12 • In cell O2, enter the formula =N2*P$1 (locks the row when copying) • Use the Fill Handle and copy cell O2 through cell O11 Excel Introduction

  49. Copying Formulas and Functions Using Absolute References (continued) • Press Ctrl+`(accent) to display all formulas and functions in their respective cells • Observe that each column on row 12 locks column P using Absolute Referencing • Observe that each row in column O locks row 1 using Absolute Referencing • Note: In this example, I would normally use $P$1 when copying formulas across the columns and rows. However, there are many cases when you will only want to lock only the column and allow the row to change and visa-versa. • Press Ctrl+1(accent) to display values Excel Introduction

  50. Text Formatting Excel 2007 provides the same basic text formatting controls as Microsoft Word 2007 and Microsoft PowerPoint 2007. The Font group under the Home tab provides the necessary controls to select the font, size, color, shading, and borders for your text and cells as follows: Font Size Increase Font Size Decrease Font Size Font Bold Font Color Italic Format Cells dialog box launcher (see Note) Underline Borders Fill Color Note: You can also launch the Format Cells dialog box by right clicking on a cell or range of cells Excel Introduction

More Related