Microsoft Excel 2007: Basics and Functions

• Microsoft Excel 2007 is a useful application in the Microsoft Office suite
• It is primarily used in business, but has a wide range of applications, including making calendars, preparing schedules, and planning budgets
• Microsoft Excel 2007 is useful with numerical data that needs to be calculated
• Microsoft Excel 2007 can store, organize, and present data in an easy-to-read manner
• Data is presented in tables of rows and columns

Lessons Overview

• Lesson 1 discusses the MS Excel 2007 environment and its basic concepts and explains how it is similar to MS Word 2007
• Lesson 2 teaches you how to launch MS Excel 2007 and how to create a workbook and also explains how to enter and delete data
• Lesson 3 discusses how to work with cell content
• Lesson 4 explains how to format cells and merge or combine them
• Lesson 5 is about formatting rows and columns

Review of Microsoft Excel 2007

• MS Word 2007 is a word processing application, while MS Excel 2007 is a spreadsheet application
• Spreadsheet applications are used for calculations, presenting, and organizing data
• Data is displayed in rows and columns
• Rows and columns make up the worksheet
• There are three worksheets by default when you open MS Excel: Sheet 1, Sheet 2, and Sheet 3
• A collection of worksheets is called a workbook
• The MS Excel file that you work on and save is the workbook
• You can also call a document in MS Excel a workbook, and saving a workbook means saving an MS Excel document
• Columns run vertically and rows run horizontally in MS Excel
• Column headings are labeled with letters, and row headings are labeled with numbers
• The rectangular box where a row and column meet is called a cell and this is where you enter data
• Each cell is identified by a unique name called cell address
• Cell addresses are composed of the column letter and row number combination
• The cell formed by column B and row 3 has the cell address B3
• Another name for the cell address is cell reference

Microsoft Excel 2007 Environment

• Like MS Word 2007, MS Excel 2007 contains the Microsoft Office button, the ribbon, and the Quick Access toolbar

Microsoft Office Button

• The Microsoft Office button appears at the top of the MS Excel window
• It performs many of the functions in the File menu of older Excel versions
• Clicking this button opens a menu to create new workbooks, open existing files, save files, and print files

Ribbon

• The MS Excel 2007 ribbon is the panel at the top of the workbook
• It provides access to various MS Excel 2007 tools and replaces traditional menus
• The MS Excel 2007 ribbon has seven tabs: Home, Insert, Page Layout, Formulas, Data, Review, and View
• Each tab is divided into groups of related tools
• Commonly used tools are displayed on the ribbon
• Click the launcher in each group to view additional tools within the group

Ribbon Tabs

• Home tab: Contains commonly used formatting tools like font size and the number group
• Insert tab: Contains tools for inserting pictures, symbols, and charts
• Page Layout tab: Contains formatting tools applied to the whole document, such as setting margins and applying themes
• Formulas tab: Contains tools used in calculations
• Data tab: Contains advanced tools for formatting data
• Review tab: Contains advanced tools for text proofing and protecting data
• View tab: Contains page viewing options such as zooming or viewing a worksheet in full screen

Quick Access Toolbar

• The Quick Access toolbar in MS Excel 2007 is a toolbar for commonly used commands that can be customized similarly to MS Word 2007
• The Quick Access toolbar is found beside the MS Office button
• More commands and tools can be added to the toolbar by clicking on the down arrow

Creating a New Workbook

• A document in MS Excel 2007 is also called a workbook
• When launching MS Excel 2007, a new workbook opens automatically
• You can double-click the MS Excel 2007 icon on the desktop or find it in the Microsoft Office folder via the Start button to launch

Creating a New Workbook if One is Open

• Click on the Microsoft Office button, a dropdown menu will appear
• Click New, then the New Workbook dialog box appears
• Click on Blank Workbook, and then Click Create to open a new workbook; alternately, double-click on the Blank Workbook option

Entering and Deleting Data

• Text: Consists of letters, numbers, special characters, and spaces, and a cell can hold up to 255 characters of text, and text entries are left-aligned by default
• Numerical values (numbers): Includes numerals 0 to 9 and the characters + (positive numbers), - (negative numbers), and . (decimals) and is used in calculations
• Formulas: Used to perform calculations involving numbers, operators, and other formulas

Entering Data in an Active Cell

• Click on a cell to select it, and the cell address appears in the name box
• Type the data in the active cell, and the text will appear in the cell and in the formula bar

Entering Data in the Formula Bar

• Click on the cell where you want to enter the data
• Place the text cursor in the formula bar
• Type the data in the formula bar
• Pressing Enter makes the cell below the active cell, and using the Tab key makes the cell to the right active
• Use the arrow keys to select cells

Deleting Data

• To delete the entire content of a cell, select the cell and press the Delete key or Backspace key
• To delete part of the data in a cell, double-click on the cell and position the text cursor on the part you want to delete
• The Delete key will delete characters to the right of the cursor, and the Backspace key will delete characters to the left of the cursor
• You can position the cursor in the formula bar and use the Delete or Backspace keys

To Delete a Word From Data

• Double-click on the cell where you entered data to place the text cursor in the formula bar
• Position the text cursor after the word in the formula bar using the mouse or arrow keys
• Use the Backspace key to delete the word

Working with Cell Content: Copying and Pasting Data

• You can copy and paste data to avoid retyping
• To copy and paste data:
• Click on the cell you want to copy
• In the Clipboard group of the Home tab, click the Copy button
• Select the cell on which you would like to copy the data
• In the Clipboard group, click on Paste

Cutting and Pasting Data

• Cut and paste transfers content, deleting it from the original cell
• You may also cut and paste data in a cell to another cell
• To cut and paste data:
• Click on the cell with the data that you want to transfer
• Click on the Cut button in the Clipboard group of the Home tab
• Click on the cell to which you would like to transfer the data
• Click on Paste in the Clipboard group

Copying or Moving a Range of Cells

• You can copy or transfer data from a range of cells to another location
• A range of cells refers to the parameters or boundaries that define a selected group of cells ( ex. the range A1:B4 includes all cells from columns A to B and within rows 1 to 4)
• The steps to copy or move a range of cells:
• Highlight the cell range you want to copy or move
• Click on the Copy button (to copy) or the Cut button (to move) the set of data
• Click on the cell to which you want your data to appear
• Click on the Paste button

Copying A Column

• Steps to copy a column
• Click on the column header of the column you want to copy
• Click on the Copy button
• Click on the first cell of the column where you want to copy the data
• Click on the Paste button in the Clipboard group

Copying A Row

• Steps to copy a row
• Click on the row header to highlight the entire row
• Click on the Copy button
• Click on the first cell of the row where you want to copy the data
• Click on the Paste button in the Clipboard group

Auto Fill

• Auto Fill is a tool for automatically filling a range of cells with data
• It allows you to copy or repeat a previously entered data, continue a series of data (months, days, numbers), etc
• The Fill Handle (the small square that appears at the lower right edge of the active cell) is used to perform the Auto Fill function

Using Auto Fill to Repeat Data

• Steps to repeat data in Auto Fill :
• Position your mouse cursor on the Fill Handle on the cell until it turns into a cross
• Click on the Fill Handle and drag it down until all the cells are filled with data

Using Auto Fill to Continue a Series of Data

• MS Excel 2007 can automatically continue a series of data from a single entry for entries such as months, days, and years
• To create a pattern, type 1 in A1 and 2 in A2 to establish the sequence of numbers, where the number in the active cell is one more than the number above it, and Highlight both cells
• Click on the Fill Handle and drag it down until the number series is complete

Formatting Cells

• Formatting does not alter the value of data in a cell; it only affects the way data are displayed, which then improves the appearance of the data
• You can add borders and colors to the cells or merge or combine cells for better presentation
• Text entered is automatically left-aligned, while values and dates entered are automatically right-aligned

Adding Borders and Colors to Cells

• Borders and colors help emphasize selected cells such as column headings and row titles
• Borders can be applied to a single cell or a range of cells
• Steps to add borders
• Click on the cells, rows, or columns you want to apply borders to
• In the Font group on the Home tab, click on the dropdown arrow beside the Borders button
• Under Borders, choose the border position and style that you want