Lesson 1 -- Barstow College

Lesson 1 starts with an overview of basic screen elements and navigation steps for a workbook with a worksheet and a chart sheet. Tasks include opening, editing, and printing Excel workbooks. You will be introduced to Excel functions using SUM and AVERAGE. They work with simple format commands as well as basic printing steps.

Excel is a spreadsheet program used to record numerical and financial data. For example, you can create financial statements, prepare budgets, manage inventory, and analyze cash flow quickly and easily by using Excel. It runs in a Windows environment. It is called a windows environment because each application (box shown on your screen) is considered a window.

In addition, you can use Excel as a planning tool to analyze and evaluate information by doing a "what if" situation. A "what if" situation can help you predicate what a change in one value will be if you change a another value.   For example, "What if we increase each employee's salary by $100 a month?" "How much will that decrease our company's profit?" " In other words, can we afford to pay our employees more?" What if we increase each employee's salary by only $50?" An Excel spreadsheet can do these calculations for you. It is a useful planning tool because the information it provides can help you make better decisions.

The book assumes that Excel is installed using the "Typical" installation and that it is running with default settings. To use Excel, the first thing you must do is open Excel on your computer. You can start Excel on your computer two different ways. One way is to click the Start button on the Windows task bar to display a list of options, next click the All Programs option to display more options, then click Microsoft Office to display the list of programs in Microsoft Office, and finally click the Microsoft Office Excel 2010 option to open Excel. A quicker method is to simply click double-click the Excel shortcut icon on the desktop, if one is available. Either method will work on any computer that has Excel installed correctly.

•

Navigating in a Workbook
PowerPoint Slides 1–13

Learning the terminology for a specific subject area is important to your understanding of that subject. Below I have summarized some key terms you need to know in order to understand the instructions in your textbook and to learn to use Excel. As you work through the exercise, these terms will be used repeatedly and become meaningful to you. It is important to know the difference between a workbook and worksheet.

A workbook is the document you create in Excel. Notice that Excel numbers each new workbook in sequence with the current session. Therefore, your workbook may not be "Book1." A new workbook has three blank worksheets by default, named Sheet1, Sheet2, and Sheet3. The number can be changed in Excel Options on the Personalize pane.

A worksheet (sometimes called a sheet) has a grid that defines rows and columns. Worksheets can be inserted or deleted from the workbook. A sheet is an individual worksheet inside a workbook, like sheets of paper in a book. The sheets are named Sheet 1, Sheet 2, etc. on tabs located close to the bottom of the Excel window.

A worksheet has gridlines that are vertical and horizontal lines which intersect to form individual cells in the worksheet area of your Excel window.

A cell is the intersection of a row and a column and where data is entered. A cell is the single box created where each column and row meet.

A column is the group of cells that run down a page and are identified by letters of the alphabet in the Column header. Therefore, A is the first column in a sheet, B is the second, and so forth.

A row contains the line of cells that run across a page and are identified by numbers in the Row header. Therefore, 1 is the first row in a sheet, 2 is the second, and so forth.

Each cell has a cell address (also called the cell reference) identified by the column and row where it is located, such as such as A1, A2, A3, and so forth.

The active cell contains the insertion point and has a thick, black border around it to indicate where data will be entered.

The Name box located on the left side of the bar above the Column header shows you the active cell address, such as A1, A2, A3, and so forth.

When the mouse pointer is touching a cell, it changes to a cell pointer that looks like a thick, white plus sign.

After Excel opens, you will see a blank workbook where you will begin creating your first spreadsheet. New workbooks are named Book1, Book2, and so on during each work session. Before you begin, verify that the Excel window is maximized (fills the entire screen) by clicking the tiny square icon in between the minus and X symbols in the upper-right corner of the window to enlarge the Excel window so that it fills the entire screen.

The Quick Access Toolbar displays the most frequently used commands. The three commands that are displayed by default on the toolbar are Save, Undo, and Redo. The Quick Access Toolbar commands are available for all tabs on the Ribbon, and the commands are executed immediately. If you accidentally display an unwanted dialog box, you can click the Cancel button to close it. You can move the Quick Access Toolbar below the Ribbon, if you want. Simply click the Customize Quick Access Toolbar button the the Quick Access Toolbar.

When you move the mouse pointer over an element on the screen, such as a button or command, a screen tip will appear to identify the element. Enhanced ScreenTips include a link to a Help topic. Press the function key F1 for Help.

TIP: If screen tips, scroll bars, or other items do not display, click Excel Options. Then click Advanced and scroll to Display. Finally click the missing options to show them.

The Ribbon is a strip above the text area with tabs containing commands to help you complete word processing tasks. The Home tab is selected when Excel is started. Tabs display groups of related commands. You can click a Tab to change to a different group of commands. The number of commands for each tab varies. The Ribbon commands that appear shaded or lighter in color are currently not available. For example, the Cut command on the Home tab is gray unless text is selected.

You can access Ribbon commands using the mouse or from the keyboard. Press [Ctrl]+[F1] to minimize the Ribbon or to restore the Ribbon. When you minimize the Ribbon, you can simply click the tab to display the commands, but when you click in the text area, the commands go back into hiding. If you want to maximize the Excel window to display the Ribbon commands, again, you can simply repress [Ctrl]+[F1] to have them remain displayed. If the window is not maximized, all controls may not be available. Groups may collapse to a single button and an extra step may be required to perform an action.

The Ribbon adapts to a task. For example, the Insert tab is used to add pages, text, shapes, illustrations, or other objects to a document. The Home tab contains commands for editing and formatting a document. There are also Ribbon tabs that are hidden from view by default. For example, the Picture Tools tab displays when a picture or clip are object is selected in a document. If a picture is not selected, the tab does not display. The Drawing Tools tab will also display when a shape is selected.

Ribbon Access keys are not combination keys like the shortcut keystrokes. You must press the Alt key, release it, and then press an arrow key, Tab, or other keystroke. Practice various Key Tips to get comfortable selecting commands in the Quick Access Toolbar, within the File menu, and the Ribbon tabs.

You can press the [Alt] key to display Key Tips that show lettered or numbered squares on the Ribbon. The tips are the letters or numbers that you can press on the keyboard to access or execute commands, instead of clicking the item on the Ribbon.

Ribbon Access keys are not combination keys like the shortcut keystrokes. You must press the Alt key, release it, and then press an arrow key, Tab, or other keystroke. Practice various Key Tips to get comfortable selecting commands in the Quick Access Toolbar, within the File menu, and the Ribbon tabs.

The new user interface for Excel 2010 can be cumbersome if you have prior Office experience. You will become more comfortable with the new interface as you move through the lessons. If your screens differ somewhat from the text figures, the settings on your computer may be different. Below is a list with definitions of the screen elements:

·         Active cell—The cell outlined in a heavy black border, ready to accept data.

·         Column headings—Alphabetic characters across the top of the worksheet that identify columns.

·         Command tabs—A tab control that includes buttons, galleries, and tasks for working with data.

·         Formula bar—Displays contents of the active cell,
           where text, numbers, or formulas can be entered.
·         File command tab—Opens Backstage view with basic commands for working with a file.
·         Name box—A drop-down list that displays the address of the active cell.
           It can be used to move the pointer to a specific cell.

·         Quick Access toolbar—A toolbar with shortcut command buttons for common tasks.

·         Ribbon—Organizes and displays command tabs. Practice collapsing and
           expanding the ribbon and look at the commands on each tab.

·         Row headings—Numbers down the left side of the worksheet that identify the rows.

·         Scroll bars—Used to move different parts of the screen into view.

·         Title bar—Displays the program name and the name of the workbook.

·         Sheet tabs—Indicators at the bottom of the worksheet to identify sheets in the workbook.

·         Status bar—Displays information about the current task and mode of operation.

·         Tab scrolling buttons—Navigation buttons to scroll through worksheet tabs.

·         View Switcher—Buttons on status bar to switch to Normal, Page Layout, or Page Break view.

·         Zoom controls—Slider and buttons to change magnification size.

A new workbook has three blank worksheets by default, named Sheet1, Sheet2, and Sheet3. The number can be changed in Excel Options on the General pane. Worksheets can be inserted or deleted from the workbook.

A worksheet has a grid that defines rows and columns. A cell is the intersection of a row and a column and where data is entered.

When you are doing a lot of data entry, keyboard shortcuts are provided to keep you from having to go back and forth between the mouse and the keyboard. The keyboard shortcuts provide the alternative method of pressing keys on the keyboard to initiate commands, instead of clicking buttons with the mouse. Therefore, you can press [Ctrl]+[Home] by holding down the Ctrl key on the keyboard, and while holding it down, press the Home key on the keyboard to move to the beginning of the worksheet cell A1. Keyboard shortcuts to navigate through a workbook are:

• [Ctrl]+[Home]—Move to the beginning of the worksheet, cell A1
• [Ctrl]+[End]—Move to the last-used cell on the worksheet
• [Home]—Move to the beginning of the current row
• [Page Up]—Move up one screen
• [Page Down]—Move down one screen
• [Alt]+[Page Up]—Move one screen to the left
• [Alt]+[Page Down]—Move one screen to the right
• [Up Arrow], [Down Arrow], [Left Arrow], [Right Arrow]—Move one cell up, down, left, or right
• [Ctrl]+[Arrow]—Move to the edge of a group of cells with data
• [Ctrl]+[G] or [F5]—Open the Go To dialog box
• Click—Move to the cell that is clicked
• [Tab]—Move to the next cell in a left-to-right sequence
• [Shift]+[Tab]—Move to the previous cell in a right-to-left sequence
• [Ctrl]+[Backspace]—Move to the active cell when it has scrolled out of view
• [Ctrl]+[Page Up]—Move to the previous worksheet
• [Ctrl]+[Page Down]—Move to the next worksheet

While scrolling through a worksheet, the location of the active cell does not change. The Zoom size controls how much of the worksheet is seen on the screen. The Zoom size can be set to see more or less on screen so that scrolling is not needed.

Collapse and expand the ribbon and look at the commands on each tab. Look at the Dialog Box Launchers in various groups. Remember that each worksheet is created in a workbook.

To get comfortable with the Excel spreadsheet, you will spend some time moving around the worksheet screen. While scrolling through a worksheet, the location of the active cell does not change. The Zoom size controls how much of the worksheet is seen on the screen. The Zoom size can be set to see more or less on screen so that scrolling is not needed. The size of the screen, the display settings, the data, and its formatting affect how many column/rows appear at once. Therefore, these types of changes may make a difference between your screens and the text illustrations. When you try the Go To command, Excel capitalizes cell references in commands. You can save time by keying lowercase letters for all cell references.

A workbook can be closed by clicking the Close button, or clicking the File tab and choosing Close, or by pressing [Ctrl]+[W] or [Ctrl]+[F4].

Editing Data in a Worksheet

PowerPoint Slides 14-22

To access a workbook that is saved, you can open it to display it on the screen. A copy of the file is put in the computer’s temporary memory for you to work on, and the original copy remains saved on your disk.

You open a file by first clicking the Microsoft Office Button (often called File or Office menu), and then you click the Open command to display the Open dialog box. The Open dialog box has search tools that you can use to help you locate the file you want. Later you may want to experiment with the search features to find a file that you cannot remember where you saved. It is important and will save you time, if you know where and how to access files.
Filenames in the Open dialog box show a ScreenTip when you hover over the name. If a workbook opens in a 100 percent size, and you cannot see all you want, you can try a smaller Zoom size.

TIP: You can click the headings in the Open dialog box to sort files, and you can open the Preview Pane by clicking the down arrow beside Organize, clicking Layout, and then clicking Preview Pane to display it. You can protect files by clicking the read-only attribute in the Properties dialog box.

A more simple method for giving the command to open a file is to use the keyboard shortcut [Ctrl]+[O] by holding down the Ctrl key, and while holding it down, press the O key to display the Open dialog box. An Open button can be added to the Quick Access toolbar. Use whichever method works best for you.

You may want to practice changing the View in the Open dialog box to see which one you like best. I always use the Details view. Use the one that you like best.

You can change the location to another folder or drive to find the Student Data files you downloaded. In the Open dialog box, you can click the down-pointing triangle button near the top of the box to drop down a list of locations available. After you have selected the location where you saved your Student Data files, you can locate the file that you want to open, and either open the file by clicking it once and then clicking the Open button, or by simply double-clicking the file name to just open it.

TIP: The Quick Access Toolbar can be customized to include an Open button by right-clicking it to display the menu option to Customize.

If you recently opened a file and want to reopen it, click the File tab, and then you can choose a file name listed in the Recent Documents. Recent Documents link to previously opened document. Since files listed in the Recent Documents section on computers used by many other people, it may include documents that are saved on someone else’s flash drive, and the files listed may not always link to your storage medium. Recall that the Recent Documents feature can display up to 17 files. The Push Pin symbol to the right of the file names in the list of Recent Documents is used to pin a document to the list. Click the Push Pin to lock the file to the list of Recent Document. The file name appears each time you click the Microsoft Office Button. It will remain available until you click the Push Pin symbol to unlock the file name.

Spreadsheets created in earlier versions of Excel do not support Excel 2010 features. If you open a file created in an earlier version of Excel, you will notice a Compatibility Mode notation in the Title bar, and you can the Run Compatibility Checker feature from the Microsoft Office button’s Excel Options to diagnose problems. Documents created in earlier versions of Excel can be converted to Office Excel 2007 by clicking the Microsoft Office Button, and then clicking Convert. Nest click OK in the Microsoft Office Excel dialog box, and finally save the document. The Compatibility Checker lists elements in your spreadsheet that aren't supported or will behave differently in Excel 97-2003 format. Some of these features will be permanently changed and won't be converted to Microsoft Office Excel 2010 elements, even if you later convert the document to Office Excel 2010 format. In the Compatibility Checker, you can review a summary of elements that behave differently in previous versions of Excel and then either click Continue to save the document in Excel 97-2003 format or click Cancel.

A cell contains text, numbers, or a formula. A formula calculates an arithmetic result.

To replace cell contents, make it the active cell, key the new data, and press [Enter] or click the Enter button in the formula bar. To clear cell contents, make the cell active and press [Delete].

The Undo command reverses the last task completed in the worksheet. The Redo command reverses the action of the Undo command. Remember that Undo and Redo are similar in all Office applications.

As you work through the lesson, be sure you understand how a simple series can be completed by Excel, and how to use the Mini toolbar as a quick way to make changes to selected text. You will use these features a lot!

Working with Columns and Rows

PowerPoint Slides 23-26

Some of your monitors or settings may be different from what is shown in the text. When you do the exercises, try to reach the sizes stated in the exercises as closely as possible, but most do not need to be exact.

Column widths and row heights are based on the default font size. Column width is measured in character spaces and pixels. Row heights are measured in points and pixels. One inch equals 72 points. A pixel does not have a set width or height, because it depends on the monitor and its resolution.

Microsoft Help explains that at times, a cell might display #####. This can occur when the cell contains a number or a date and the width of its column cannot display all the characters that its format requires. For example, suppose a cell with the Date format "mm/dd/yyyy" contains 12/31/2007. However, the column is only wide enough to display six characters. The cell will display #####. To see the entire contents of the cell with its current format, you must increase the width of the column.

1.    Click the cell for which you want to change the column width.
2.    On the Home tab, in the Cells group, click Format.


3.    Under Cell Size, do one of the following:
         a. To fit all text in the cell, click AutoFit Column Width.
         b. To specify a larger column width, click Column Width, and type the width that you want in the Column width box.

Note:   As an alternative to increasing the width of a column, you can change the format of that column or even an individual cell. For example, you could change the date format so that a date is displayed as only the month and day ("mm/dd" format), such as 12/31, or represent a number in a Scientific (exponential) format, such as 4E+08.

AutoFit sizes a column to its widest entry and a row to its tallest font. Any width or height can be changed by:

·        Dragging a column or row border to the new size
·        Double-clicking a column’s right border or a row’s bottom border to AutoFit
·        Using Row Height or Column Width from the Cells group on the Home tab

If you are using AutoFit for a column, you should note long labels. Excel sizes a column to accommodate the longest label, but the bulk of the column data may not require that width. If data is edited in a column that has been AutoFit, it is not AutoFit, again, automatically. Additionally, row heights may be resized automatically to fit a larger font.
Inserted or deleted rows and columns extend across the entire worksheet. This can result in changes that are not visible unless the worksheet is scrolled and checked.

If a row is inserted within a formula range, the formula is adjusted to show the new range. If a formula includes a deleted row, the formula is adjusted to show the new range.

Multiple columns/rows can be inserted by dragging across the same number of headings as desired. For example, if you want to insert two columns before column D, drag across the headings for column D:E, right-click, and choose Insert.

When a column is deleted, remaining columns move left to fill the space. Formulas are adjusted if the deleted column was within the formula range. Empty columns or rows can be hidden. Although a row or column is hidden, its values are used in calculations. When data spans several screens, it can be problematic to see related columns or rows at the same time. Data can be kept in view by freezing rows, columns, or both.

Saving Workbook Files

PowerPoint Slides 27-28

Work must be saved if it might be used at another time. The Save or Save As command can be used to save and name the workbook. An existing workbook can be saved with the same name with the Save command. A filename identifies the workbook. Windows is case-aware but not case-sensitive, recognizing uppercase and lowercase characters but not distinguishing between them. Filenames are followed by a period and a four-letter extension, xlsx. The extension identifies the type of file and is assigned automatically by most software programs. If a different filename is preferred for an existing workbook, save it using the Save As command. Always check the Save In location when saving a workbook.

Documents are best organized in folders. Develop a file management scheme for use in class.

Files are best organized in folders, whether it is on a disk, a network, or other drive. A folder is a location on a disk, network, or other drive. Folders are organized like a tree. The top level is a letter such as A, B, or C to represent the drive or other storage device. Under each drive letter, create folders to help organize and manage work.

Throughout the text, you are told to save your files using your initials and the exercise number. Please always do so.

The Save as PDF and XPS add-ins must be installed for you to complete some of the exercises. If you do not have the options available on your computer, download the XPS add-in at the following website:

http://www.microsoft.com/downloads/details.aspx?familyid=4D951911-3E7E-4AE6-B059-A2E79ED87041&displaylang=en

An XPS file is a document format that allows one to view the data without the application used to create it. It is a Microsoft format, similar to Adobe’s PDF format. If you can choose PDF or XPS from the Save As submenu, the Publish as PDF pr XPS dialog box opens. This dialog box is different from the Save As dialog box, but it does include the same information. If you cannot save the file as an XPS file, simply resave it without changing the file type.

You should create a new folder for each of your lesson files, such as Lesson 1, Lesson 2, and so forth to keep them organized. From this point in the course, you should create a new folder for each lesson where you can save your files. It is important to develop good file management habits. You can create these folders on your own hard disk or on a USB thumb drive, if you are using someone else’s computer. If you are using your own personal computer, you can save your documents on Drive C in an Excel Class subfolder in the Documents folder or wherever you would prefer to save them. Recall that you can change the location to another folder or drive. In the Save As dialog box, you can click the down-pointing triangle button near the top of the box to drop down a list of locations available. After you have selected the location where you will save your work, you can click the new Folder button to create and name your new folder.

The Save As dialog box is where you will enter the name of the file name that you want saved as. If it is a new spreadsheet, you must give it a filename before it is saved. A file is where a document or data (information) is saved on a disk. The file name is the name used to find a document or the data stored in a file at a later time. If the file name you use is descriptive of the document saved in the file, it is easier for you to remember the contents of a file when you see the file name. Therefore, use a name that is unique and relevant. A filename can be 255 characters long. You can use hyphens, underscores, and numbers in a filename. Following is a list of symbols that you cannot use as part of a filename:

        backslash \                            question mark ?
        forward slash /                       colon :
        greater than sign >                 quotation mark "
        less than sign <                      semicolon ;
        pipe symbol |                         asterisk *

An unnamed spreadsheet in the window is not saved and is only in the temporary memory of the computer. It could be lost if the power to the computer is turned off before it is saved. You save a file to store it on a disk for later use. The Save command is used to save a new file or to save an existing file with the same name. It is important that you understand the difference between Save and Save As. Remember that the Save button on the toolbar will not allow you to assign a new file name to a previously saved file. Be aware that the toolbar Save button overwrites the currently saved file with the revised copy of any document saved on your disk. Use the Save As command to name and save a file for first time or to save an existing file with a different name. Use the Save command to update an existing document. Save often and frequently!

TIP: To assign a new file name to a previously saved file and not overwrite the original file, you must use the Save As command from the File menu. When you see the words "Copy of " in a filename, it is probably a read-only file. To resave the edited file, you must use a new name.

Printing Excel Files

PowerPoint Slides 29-31

You can print a copy of a document on paper to have a copy to read, file, or to give to other people. You can print a spreadsheet before or after you save it. It is a always a good idea to save before printing to reduce the chances of losing your work. A document printed on paper is called a hard copy. For this class, since you will be e-mailing your assignments to me, you can save paper and ink by not printing your documents, if you want.

To print an Excel file:

• Click the File command tab and choose Print
• Press [Ctrl]+[P]
• Click the Quick Print button on the Quick Access toolbar

The Quick Print button prints the active sheet with default settings, but other methods will open the Print dialog box so that options can be changed. You may want to add the Quick Print button to the Quick Access toolbar by clicking the Customize Quick Access Toolbar arrow, and choosing Quick Print.. This button can be added to the Quick Access toolbar. Other print methods open Backstage View and/or the Print dialog box so that options can be changed.

Student Learning Outcomes and Objectives

Getting Started

Navigating in a Workbook
PowerPoint Slides 1–13

Editing Data in a Worksheet

PowerPoint Slides 14-22

Working with Columns and Rows

PowerPoint Slides 23-26

Saving Workbook Files

Printing Excel Files

Getting Acquainted with Excel Functions

Using Alignment, Borders, and Fill

Getting Acquainted with an Excel Chart

Click for LESSON 1 Assignments

Student Learning Outcomes and Objectives

Getting Started

Navigating in a Workbook PowerPoint Slides 1–13

Editing Data in a Worksheet PowerPoint Slides 14-22

Working with Columns and Rows

PowerPoint Slides 23-26

Saving Workbook Files

Printing Excel Files

Getting Acquainted with Excel Functions

Using Alignment, Borders, and Fill

Getting Acquainted with an Excel Chart

Click for LESSON 1 Assignments

Navigating in a Workbook
PowerPoint Slides 1–13

Editing Data in a Worksheet

PowerPoint Slides 14-22