Lesson Five PowerPoint

Slide 1: 

Lesson 2 Creating a Workbook

Lesson Objectives : 

Lesson Objectives Enter labels. Change the document theme. Select cell ranges. Modify column width and row height. Enter values and dates. Save a workbook. Enter basic formulas.

Entering Labels : 

Entering Labels Data that begins with a letter is recognized as a label. Labels are aligned at the left edge of the cell and are not used in calculations. Data appears in the active cell and the formula bar as it is being keyed. To start over for an entry, press . To edit the entry, press .

Entering a Label : 

Entering a Label

Completing a Cell Entry : 

Completing a Cell Entry

Changing the Document Theme : 

Changing the Document Theme A document theme includes 2 fonts, 12 colors, and special effects. The default document theme for workbooks is Office. It uses Calibri as the Body font and Cambria as the Headings font. You can change the document theme to any theme in the Gallery. Live Preview displays the new colors before they are applied. You are not limited to theme fonts and colors. You can choose any font/color available.

Using Theme Fonts : 

Using Theme Fonts

The Document Themes Gallery : 

The Document Themes Gallery

Using the Format Painter : 

Using the Format Painter The Format Painter is available in all Office applications. You can quickly copy cell formats from one cell to another. Format being copied Cell to be changed

Selecting Cell Ranges : 

Selecting Cell Ranges A range is a group of cells that forms a rectangle. The range address is the upper-left cell address, a colon, and the lower-right cell address.

Selecting Cell Ranges with the Mouse : 

Selecting Cell Ranges with the Mouse Drag across adjacent cells to select a range. Click the first cell in the range. Hold down [Shift] and click the last cell in the range. Click a column heading letter to select a column or click a row heading number to select a row. Drag across adjacent column heading letters or row heading numbers to select multiple columns or rows. Click the Select All button to select every cell on the worksheet.

Selecting Cell Ranges with Keyboard Shortcuts : 

Selecting Cell Ranges with Keyboard Shortcuts

Modifying Column Width and Row Height : 

Modifying Column Width and Row Height Columns are 8.43 spaces (64 pixels) wide with the Office document theme. Rows are 15.00 points (20 pixels) high. A point measures 1/72 inch. A ScreenTip helps you size columns/rows when you drag a border. You can also use the Column Width and Row Height dialog boxes.

Modifying Column Width and Row Height : 

Modifying Column Width and Row Height Resizing a Column Resizing a Row

Entering Values and Dates : 

Entering Values and Dates Excel assumes an entry that starts with a number or an arithmetic symbol is a value. A value is right-aligned in the cell and is included in calculations. Values are formatted in General style as a default. Excel recognizes dates if they are keyed in a typical date style. Dates have special formats and can be used in date arithmetic.

Using Number and Date Formats : 

Using Number and Date Formats Pressing [Ctrl]+[1]. Right-clicking the cell or range and choosing Format Cells from the shortcut menu. On the Home tab in the Cells group, clicking the Format button and then choosing Format Cells. Some number formats are available as buttons on the Ribbon. Many more formats are available in the Format Cells dialog box. You can open the Format Cells dialog box by:

The Format Cells Dialog Box, Number Tab : 

The Format Cells Dialog Box, Number Tab

The Format Cells Dialog Box, Font Tab : 

The Format Cells Dialog Box, Font Tab Changing the font color

Renaming a Worksheet TabChanging the Tab Color : 

Renaming a Worksheet TabChanging the Tab Color You can rename a worksheet with a descriptive name that is 31 or fewer characters. You can choose a color for the worksheet tab.

Saving a Workbook : 

Saving a Workbook To save a workbook, give it a descriptive filename. Try to keep filenames as short as possible, although they can be up to 255 characters. You can use uppercase or lowercase letters in a filename. Windows does not distinguish between them. Do not use these characters in a filename \ ? : * “ < > | Excel filenames are followed by a period and a four-letter extension, XLSX.

Entering Basic Formulas : 

Entering Basic Formulas A formula is an equation that performs a calculation and displays the results in the cell. The formula is visible in the formula bar. A formula is keyed into a cell, or it can be built by pointing. Formulas begin with an = sign and use arithmetic operators.

Arithmetic Operators : 

Arithmetic Operators

Keying a Formula : 

Keying a Formula When you key a letter after the = sign, you’ll see the Formula AutoComplete list. Ignore it when it does not suit what you are doing.

Building a Formula by Pointing : 

Building a Formula by Pointing Start with the = sign, point to the first cell and click. Then key the arithmetic operator and point/click the next cell. Press [Enter] to finish.

Copying Formulas : 

Copying Formulas Formulas are copied using regular Copy/Paste procedures. Excel automatically adjusts the copied formula to its new location on the sheet. Paste Options button

Using Built-in Functions : 

Using Built-in Functions Common business and personal calculations are built-in and known as functions. They start with an = sign like any formula.

Function Library Group, Formulas Tab : 

Function Library Group, Formulas Tab

Using AutoCalculate : 

Using AutoCalculate AutoCalculate displays basic calculation results in the status bar for a selected range of cells. Right-click the status bar to access it.

Summary : 

Summary In a blank workbook, you can key values, labels, dates, or formulas. Excel recognizes data by the first character you key in the cell. Labels are aligned at the left edge of a cell. If they are longer than the column width, they spill into the next column if it is empty. Otherwise, they appear cut off on the screen. To complete a cell entry, press [Enter], [Tab], or any arrow key or click another cell. You can also click the Enter button in the formula bar.

Summary : 

Summary New workbooks use the Office document theme. The default font is 11-point Calibri. You can change the font, the font size, the color, and the style. Use the Format Painter to copy formats from one cell to other cells. Many commands require that you first select a range of cells. You select a range of cells by using the mouse or keyboard shortcuts. The default row height matches the default font size in the document theme. The row height adjusts if you choose a larger font.

Summary : 

Summary Common formats, such as Accounting, can be applied to cells from the Number group on the Home tab. Many other formats are available in the Format Cells dialog box. It’s a good idea to change the default worksheet tab name to a more descriptive name. You can also change the worksheet tab color for visual cues. You must save a new workbook to keep your work. For a new workbook, you can use the Save or the Save As command.

Summary : 

Summary To create a formula in a cell, you can key it or you can construct it by pointing to the cells used in the formula. All formulas begin with the = symbol. When you copy a formula, Excel adjusts it to match the row or column where the copy is located. Excel has functions for common calculations such as Sum, Average, Maximum, Minimum, and Count. You can see results for common functions without keying a formula if you use AutoCalculate.