Working with EXCEL tutorial step by step


Télécharger Working with EXCEL tutorial step by step

Formation Excel en ligne par vidéo

205 leçons vidéos + 20 Livres PDF + 20 TP + Sous supervision + Certificat de réussite à la fin du cours



★★★★★★★★★★3.5 étoiles sur 5 basé sur 1 votes.
Votez ce document:

Télécharger aussi :


Introduction to Microsoft® Excel 2007

Lessons Length: 4 lessons 

 Lessons will be posted on Mondays and Thursdays. Access to lessons will be available until the end date of the class.

Start/end dates: Mar 10, 2008 - Apr 11, 2008

Lesson 1: Getting started with Microsoft Excel 2007 (completed)  Have you always wanted to learn Microsoft Excel but just haven't had the time? If you don't have any experience in Excel yet, that's not a problem. This lesson eases you into the software by covering Microsoft Excel spreadsheet basics. 

»                          Assignment: Open, edit, and save an Excel file (completed) 

»                       Quiz: Lesson 1, quiz 1 (score: 100%) 

»                  Message board 

Lesson 2: Microsoft Excel tutorial: creating your first worksheet (completed)  In this lesson, you'll learn more about entering data in Excel, including how to

edit existing data and simplify the entry of repeating data. You'll also find

 out how to select and manipulate ranges (groups) of cells, and move and copy data among them.  

»                        Assignment: Create and modify a workbook (completed) 

»                       Quiz: Lesson 2, quiz 1 (score: 100%) 

»       Message board  

Lesson 3: Microsoft Excel tutorial: Microsoft Excel formula and function basics (completed) 

Excel's real power comes from its ability to "crunch numbers." In this lesson,

 you'll get an introduction to Excel's numeric calculation capabilities, including simple math formulas and time-saving, built-in functions. 

»                       Assignment: Using formulas and functions (completed) 

»                       Quiz: Lesson 3, quiz 1 (score: 100%) 

»       Message board  

Lesson 4: Microsoft Excel tutorial: formatting basics in Excel (completed)  Once you know how to create a simple spreadsheet and add formulas and functions, it's time to spice it up with formatting. In this lesson, you'll learn  how to add life to your spreadsheets with text, cell and page formatting. 

»                      Assignment: Format a gradebook (completed) 

»                       Quiz: Lesson 4, quiz 1 (score: 100%) 

»                  Message board 

Instructor

Gaye Sodoma, a Texas resident for 37 years, is a senior mortgage loan officer for a Texas-based mortgage broker; she joined their Austin branch in 2003. She has been consistently

Gaye                                           praised as an exceptional loan officer and mentor by her peers as

Sodoma            well as by industry personnel such as underwriters and real estate agents. Gaye is constantly striving to exceed her customers' expectations and make their experience in obtaining a mortgage

                                                                                                                                    the easiest and most satisfying one possible. 


Welcome to Microsoft Excel 2007

Welcome to the course. This is a no-experience-required online training course, suitable for anyone who wants to learn about Microsoft Excel 2007 starting with the basics. In these four simple, free tutorials, you'll get plenty of training and tips on Microsoft Excel spreadsheets and pick up some insider techniques not easily found through Microsoft Excel support channels. 

This introductory course is one in a series of free online courses designed to help you learn Excel -- from the basics through advanced levels. 

What you'll learn

This course consists of four lessons, each one with a simple quiz and an assignment. The lessons are: 

•    In Lesson 1, "Introduction to Excel 2007," you'll become acquainted with Excel 2007 by taking a tour of its interface and learning Microsoft Excel terms. You'll get practice opening a saved file, entering and editing data, and printing. 

•    Lesson 2, "Creating Your First Worksheet," teaches you how to start a new workbook; move around in a worksheet; select, move, and copy data; and insert rows and columns. 

•    In Lesson 3, "Formula and Function Basics," you'll discover the power of Microsoft Excel formulas and functions and learn how to use them to calculate and summarize numeric data. 

•    Lesson 4, "Formatting Basics," shows you how to format the text and numbers in a worksheet and find out how to apply borders and shading to cells. You'll also learn how to set page formatting and printing options. 

Quizzes and assignments are for your learning experience only. Quizzes are computergraded, and you may retake a quiz as many times as you like. Assignments are optional; however, working through the assignments enhances your learning experience and helps solidify key concepts and techniques for using Microsoft Excel 2007. 

Throughout this course, we provide Flash examples. To view these examples, you need the Adobe Flash Player. Keep an eye out for notes with links that say "See how to ___" or something similar. Some of these files are very large (2 to 5 MB or so) and may take a while to appear or download if you have a slow connection. 

Lesson 1 overview:  This first lesson eases you into Excel 2007 by explaining what Excel is and what it's good for. You'll find out what's changed since Excel 2003 and learn how to handle files and do some basic editing. Even if you've never opened Excel before, you'll get up to speed quickly and be ready to tackle creating your own worksheets in the next lesson. Let's get started.


Why use Excel?

 

Excel is a spreadsheet program. A spreadsheet is a grid of rows and columns that helps organize, summarize, and calculate data. Spreadsheets are an everyday part of many professions, including accounting, statistical analysis, and project management. You can use Excel to create business forms, such as invoices and purchase orders, among many other useful documents.

In addition, many people use spreadsheets at home for their own personal pursuits. For example, suppose that you're considering buying a home and are trying to decide between two sets of loan terms. A spreadsheet can easily show you how much you'd pay per month under each scenario and how much money you'd need at closing. Figure 1-1 shows this example.

 

Figure 1-1: You can use spreadsheets for a variety of financial calculations.

Figure 1-2 shows a business form in Excel. Because Excel enables you to place borders (lines) around one or more sides of a cell, you can use it to create vertical and horizontal lines on a page. Such forms are more than just neat-looking in print; they can also contain formulas that calculate invoice or purchase order totals. Notice in Figure 1-2 that most of the gridlines between cells are hidden and that the column widths have been changed to adjust the spacing to make the form look more attractive.

Figure 1-2 was created with a template from the Forms > Business category in the New Workbook dialog box in Excel. This template was downloaded from Microsoft's template library online. You'll learn how to do that in Lesson 2.

   

Figure 1-2: You can use spreadsheets to create business forms.

Enlarge image

Understanding spreadsheet terminology

At the intersection of each row and column is a cell, into which you type information. Each cell has a unique name consisting of a column letter and a row number. For example, the top left cell is A1. A cell can contain any of the following:

A number (and any associated punctuation, such as decimal points, commas, and currency symbols)

Text (including any combination of letters, numbers, and

symbols that aren't number-related) A formula, which is a math equation

Formulas always begin with an equal sign, and can include math operators (such as + or -), references to cells, and numbers. For example, =B1+B2 is a formula.

 A function, which is a named equation that shortcuts an otherwise complex operation

For example, the AVERAGE function adds up all the values in a range and then divides it by the number of entries in the range, to calculate their average. You could do that with a regular formula; however, it would be complicated to write. The AVERAGE function makes it much easier.

You'll learn how to enter text and numbers in Lessons 1 and 2 of this course, and then you'll learn about formulas and functions in Lesson 3.

Each file in Excel is a workbook. A workbook consists of one or more tabbed sheets called worksheets. (Worksheet is Microsoft's term for "spreadsheet.") Figure 1-1 shows three tabbed sheets at the bottom: Sheet1, Sheet2, and Sheet3.

Next, find out how to start Excel 2007 and take a tour of the interface.

Touring the Excel interface

Now that you know a little about Excel, let's get started using it. To start Excel, select Start > All Programs > Microsoft Office > Microsoft Office Excel 2007.

Excel opens with a blank workbook. You can use that workbook, or you can open an existing one from disk. If you add data to the blank workbook, it remains open; if you open another workbook without doing anything to it, it disappears.

Learning the Excel interface

Figure 1-3 points out the main features of the Excel window. Because Excel 2007 is so different from earlier versions, even veteran users of the older versions may need a little assistance in finding their way around. The following list describes each of the features:

You can point at any button on any toolbar, tab, or ribbon to see its name and a brief explanation of what it does. One good way to explore the interface is to move your mouse pointer around the screen and read the ScreenTips (explanations) that appear.

 

Figure 1-3: Parts of the Excel 2007 screen.

Enlarge image

Ribbon: This is the multi-tabbed toolbar system that contains buttons and other controls for issuing commands. Microsoft Office Button: Opens the only real "menu" in Excel 2007 -- the Office menu -- which contains commands for saving, opening, and printing files.

Tabs: There are two meanings for this word. A tab is an individual page of the Ribbon; however, it's also the word you click to display that page. For example, you click the word Data to display the Data tab.

 Quick Access Toolbar: This is a highly customizable portion of the interface; you can place shortcuts to your favorite buttons and commands here. By default, it contains Save, Undo, and Redo buttons.

Insert Function button: You can click this button to get help creating functions, as you'll see in Lesson 3.

Formula bar: This is where the contents of a cell appear and where you can type or edit those contents.

Column headers: Each column's letter is a header for that column. You can click the column letter to select the entire column.

Microsoft Office Excel Help button: You can click this

button to open the Excel Help system, from which you can look up any topic.

Scroll bars: You can use these to scroll within the active worksheet.

Zoom controls: These buttons, and this slider, control the magnification at which you view the active worksheet. Insert Worksheet tab: You can click this tab to insert another worksheet.

Worksheet tabs: You can click one of these tabs to switch between worksheets.

Row headers: Each row's number is a header for that row. You can click the row number to select the entire row.

Everything covered in this course is also in the Excel Help system, so if you forget anything you've learned here, you can use the Help system to remember. In Excel, just click the Help button, and you're there.

The next section covers new features and functionality in Excel 2007.

What's new in Excel 2007?

Excel 2007 is very different from earlier versions. For those of you who are upgrading, let's take a quick look at a couple of the new features in the interface.

Excel 2007 is part of the Microsoft Office 2007 suite of applications, which includes Word, Excel, PowerPoint, Access, and Outlook. These programs all have similar interfaces.

The Ribbon

The Ribbon replaces the menu system from earlier versions of Excel. Instead of drop-down menus, you now have tabbed pages (a.k.a. tabs) of toolbars. Many of the buttons on the Ribbon are similar to the buttons on the toolbars in earlier versions of Excel.

Each Ribbon tab has named sections, called groups. For example, in Figure 1-4, you see the Home tab, which contains groups including Clipboard, Font, Alignment, Number, Styles, Cells, and Editing.

Some of the groups have icons in their lower-right corners. These are dialog box launchers. They open dialog boxes containing more options for the settings in that group than the Ribbon provides.

 

Figure 1-4: The Ribbon takes the place of menus and toolbars.

Enlarge image

Groups expand or collapse based on the width of the Excel window. When the Excel window is restored down (smaller than full size), some groups become single buttons that drop down into a palette of buttons when you click them. Figure 1-5 shows the same tab as Figure 1-4, but at a narrower window size and with the Alignment group's button active (selected) so that the hidden buttons open as a drop-down palette.

 

Figure 1-5: When the Ribbon is less than full size, groups are collapsed.

Enlarge image

The Microsoft Office Button

The only drop-down menu remaining in Excel 2007 is the Office menu. To open it, click the Microsoft Office Button, which is the large, round button in the upper-left corner of the Excel screen. The Office menu contains many of the same commands that were on the Office menu contains many of the same commands that were on the File menu in earlier versions, including Save, Save As, Print, and Close.

The Office menu has two columns. Commands appear in the left column. When you point at an arrow in the left column, additional commands appear in the right column. For example, as shown in Figure 1-6, if you point to the arrow next to Save As in the left column, various saving options appear for selection in the right column.

 

Figure 1-6: The Office menu replaces the File menu from earlier versions of Excel.

Take a tour of the Excel 2007 interface and learn basic skills. (1.8 MB file)

Many other differences can be found in Excel 2007. You'll discover some of them as you proceed through the course. The next section addresses how to work with data files in this course.

Working with data files for this course

Throughout this course, example data files are provided for you to work with. The first set of skills you need to master is downloading these files, saving them to your hard disk, and then opening them in Excel 2007.

If your browser is set to block pop-ups and downloads, an information bar may appear at the top of the Web page when you try to download a data file for this course. Click it, and then click the option to download the content to override the block. If your browser still blocks the content, try changing its security settings. You might even need to try a different browser.

Here's the basic procedure for downloading a data file for this course:

1.   Click the hyperlink. To practice, download the file.

2.   If asked whether you want to Save or Open the file, click Save.

If a page appears with a bunch of gibberish, close that window and then use the alternate method that follows.

3.   In the Save As dialog box, navigate to the folder in which you want to save it and then click Save.

If that doesn't work, here's an alternate method:

1.   Right-click the hyperlink, and then select Save Target As.

2.   In the Save As dialog box, ensure that the file is identified as an Excel file. If it displays an .htm extension or as an HTML (Hypertext Markup Language) file, that's a problem. If necessary, change the file type to All Files and then change the extension to .xls.

3.   Click Save.

4.   In the Download complete dialog box, click Open to open the file in Excel, or open it manually later as described in the next section.

This course uses data files in the Excel 97-2003 format, so that students who don't have Excel 2007 can use them, too. Excel 2007 opens such files with no problems. The data files you create from scratch yourself as you proceed through the course will probably be in Excel 2007 format. Some minor advantages are provided when working in Excel 2007 format, such as the ability to use some of the new formatting features. You'll learn about them later when they become relevant to the lesson.

Opening a saved data file

After saving a data file to your hard disk, you need to open it in Excel.

If a dialog box opens after the download that contains an Open button, you can click that button to open the file in Excel without further ado. That might or might not happen, depending on your version of Windows and how it is set up.

If that doesn't occur, or if you want to open the data file later, you can use the Open command, as follows:

1.   Click the Microsoft Office Button.

2.   Select Open.

3.   In the Open dialog box, navigate to the location where you stored the data file.

stored the data file.

4.   Highlight the data file you want to open.

5.   Click the Open button.

The Open dialog box looks very different depending on the Windows version you have. (Yes, that's the Windows version, not just the Excel version.) If you use Microsoft Windows XP, for example, dialog boxes in Excel 2007 look pretty much like they did in earlier versions of Excel, and like they do in other programs. See Figure 1-7 as an example.

 

Figure 1-7: In Windows XP, the Open dialog box looks similar to that in earlier Excel versions.

Enlarge image

If you use Windows Vista, however, the Open dialog box looks more like the Windows Vista file management interface, as shown in Figure 1-8.

 

Figure 1-8: In Windows Vista, the Open dialog box looks like the Vista file management interface.

Enlarge image

In either case, you navigate to the folder containing the file to open, select the file you want, and click the Open button.

Next, you'll learn how to enter and edit data in a worksheet.

Entering and editing data

Now that you know how to open files, let's take a quick look at how to type data into a worksheet. You'll work with the file you downloaded in the previous section. If you didn't download it yet, download it now by clicking here.

Selecting a cell

Before you can enter or edit data in a cell, you need to select it. Here are some ways:

Click the cell.

Use the arrow keys on the keyboard to move the cell selector (the thick outline that indicates the active cell) onto the desired cell.

If the desired cell is to the right of the cell selector's current position, press the Tab key on your keyboard to move it to the right. Or, if the desired cell is to the left, press Shift+Tab to move it to the left.

If you change your mind about entering or editing data in the cell, press Esc before you move the cell selector away from that cell. This returns it to its previous value. If you don't realize the mistake until after moving away from the cell, press Ctrl+Z to undo the last action. Alternatively, click the Undo button on the Quick Access Toolbar.

Entering data in a blank cell

If a cell is blank, you can just select it and then type. Pressing Enter moves the cell selector to the next cell below; pressing Tab moves the cell selector to the next cell to the right.

To try this out with the file, do the following:

1.   Select cell A26.

2.   Type your last name.

3.   Press Tab. Cell B26 should now be selected.

4.   Type 12.

5.   Press Tab. Cell C26 should now be selected. Notice that the number in C26 is filled in automatically, because C26 contains a formula that relies on a value from B26. When B26 was empty, C26 showed 0; now it shows 1.032258065, and cell D26 shows 100%.

6.   Press Enter. Cell A27 should now be selected. Notice that Excel was smart enough to realize that you just entered a series of data that began in column A, so when you pressed Enter, it took you all the way back to column A on the next row.

Editing existing data in a cell

To edit data, select the cell and then do one of the following:

Double-click inside the cell to move the insertion point into the cell and then edit the text there.

Click in the formula bar and edit the text there.

Type new content. The old content will be replaced.

1.   Select cell A1, and then type Employee. The new text replaces the previous text. Press Enter.

2.   Select cell D1. Click in the formula bar at the end of the word Percent and type age, changing the word to Percentage. Press Tab to move to the cell to the right.

See how to enter data in a worksheet. (.5 MB file)


Printing your work

After you create or edit a worksheet, you might want to print a hard copy of it. You can print in two ways:

Print using the Print dialog box. Here, you can specify the range, the number of copies, the printer to use, and more. Print using Quick Print. This sends one copy of the active worksheet to the default printer.

To print using the Print dialog box, follow these steps (try it with if you like):

1.   (Optional) If you want to print only a certain range of cells, select them. (You'll learn how to do this in Lesson 2; for now, you can drag across a range to select it, if desired.)

2.   Click the Microsoft Office Button, and then click Print. The Print dialog box opens, as shown in Figure 1-9.

 

Figure 1-9: Print dialog box.

3.   In the Name box, confirm that your desired printer's name appears. If it doesn't, open the drop-down list and select it.

4.   In the Number of copies text box, confirm that the desired number of copies appears. If it doesn't, use the up or down increment arrow to the right of that box to increment the number up or down.

5.   In the Print range section, All is selected by default. Leave this alone unless you want a certain page range, in which case you should select Pages and then enter the page range.

6.   In the Print what section, check the option for what you want to print: Selection (applicable only if you made a selection in Step 1), Active sheet(s), or Entire workbook.

7.   Click OK to print.

Next, learn how to save and close a workbook.

Saving and closing a workbook

After making changes to a workbook, you can save those changes to disk. If the workbook has already been saved once -- or opened from an existing file -- you don't need to re-specify a name or location. You can just issue the Save command. If the workbook has never been saved, you're prompted for a file name, type, and location.

To save changes to an already-existing file, do any of the following:

Click the Microsoft Office Button, and then select Save.

Click the Save button on the Quick Access Toolbar. Press Ctrl+S.

To save a file that hasn't previously been saved, or to save an existing file under a different name, type, or location:

1.   Click the Microsoft Office Button, and then select Save As. The Save As dialog box opens.

2.   Type the desired file name in the File name text box.

3.   (Optional) Navigate to a different folder location, if desired.

4.   (Optional) Change the file type in the Save as type drop-down list, if desired.

5.   Click Save.

The Save As dialog box opens, as shown in Figure 1-10.

If you're running Excel 2007 in an operating system other than Windows Vista, your Save As dialog box will look a bit different; however the options work the same.

 

Figure 1-10: The Save As dialog box in Windows Vista.

Enlarge image

To practice saving, save the file under a different name, as follows:

1.   With open in Excel, click the Microsoft Office Button and then select Save As.

2.   In the Save as type list, select Excel Workbook (*.xlsx).

3.   In the File name text box, change the name to Practice07.

4.   Click Save.

By selecting the Excel Workbook (*.xlsx) option before saving the workbook, you changed the file to the Excel 2007 format, which is workbook, you changed the file to the Excel 2007 format, which is XML-based. This file now has an .xlsx extension.

If you don't see the .xlsx extension, you need to change an option in the Folder Options dialog box in your operating system to make the extensions viewable. In Windows Vista, click Start, and then click Computer. In the Computer window, select Tools > Folder Options from the menu bar. Click the View tab. Uncheck the Hide extensions for known file types checkbox, and then click OK. Close the Computer window and return to Excel 2007. Click the

Microsoft Office Button, and then click Save As. Notice in the Save As dialog box that you can now see the file extensions. Click Cancel to close the Save As dialog box. The steps are similar for Windows XP -- just open My Computer rather than Computer.

Moving on

In this first lesson, you learned about Excel 2007's interface, and you saw how to open and save data files and enter and edit text in cells. In Lesson 2, you'll continue exploring the Excel 2007 interface and building Excel files. You'll learn how to move around in a worksheet, change the view, move and copy data, and more. Before you move on to Lesson 2, complete the assignment and take the quiz, which are designed to help you get the most out of this course.


Assignments are designed to help you apply the information learned in the lessons.

Open, edit, and save an Excel file

For this assignment:

1.  Download and save it on your hard disk, in any location that's convenient for you.

Excel 2007 defaults to the Documents or My Documents folder, so if you save your files there, you won't have to worry about changing the save or open location.

2.  Open in Microsoft Excel 2007.

3.  Save the file in Excel 2007 format with the name .

4.  Edit the text in cell A1 to read Gross Receipts Summary.

5.  Change the value in cell D5 to 18.

6.  Change the value in cell G12 to 28.

7.  Change the value in cell J18 to 25.

8.  In cell A19, type # nights @ $155.

9.  In cell A18, change the text to read # nights @ $95.

10.   In cell A17, select the cell and press the Delete key, clearing that cell's contents.

11.   In cell K11, type December, and then press Enter.

12.   In cell K12, type 10, and then press Enter.

13.   In cell K13, type 1, and then press Enter.

14.   In cell K17, type December, and then press Enter.

15.   In cell K18, type 16, and then press Enter.

16.   In cell K19, type 2, and then press Enter.

17.   Save your work by clicking the Save button on the Quick Access Toolbar.

18.   (Optional) Print one copy of the worksheet on your default printer.

19.   Check your work against the solution, and then close the workbook and Excel 2007.


Quiz: Lesson 1, quiz 1

Quizzes are designed to give you a chance to test your knowledge.

Please feel free to take this quiz again. We will update your score. 

 

1.    You're using Microsoft Internet Explorer and having trouble downloading a data file for this course by clicking the hyperlink. An alternate method is to right-click the hyperlink, and then select ___________. 

A.        Download

B.        Save Target As

C.        Target Location

D.        Save Image

2.    Which of the following are examples of a formula? (Check all that apply.) 

A.        =B1+B2

B.       =B1+15-A6

C.       Grand Total

D.       437.222

3.    What's the Ribbon in Excel 2007? 

A.       Where the contents of the cell appear, and where you can type or edit the contents

B.       The buttons and slider that control the magnification at which you view the active worksheet

C.       The small, customizable toolbar located just to the right of the Office button

D.       The tabbed toolbar system that contains buttons and other controls for issuing commands

Quiz: Lesson 1, quiz 1

 

Quizzes are designed to give you a chance to test your knowledge.

Please feel free to take this quiz again. We will update your score.

Quiz Results

1. You're using Microsoft Internet Explorer and having trouble downloading a data file for this course by clicking the hyperlink. An alternate method is to right-click the hyperlink, and then select ___________.

A.         Download 

B.         Save Target As 

C.         Target Location 

D.         Save Image 

Correct answer(s): B  Explanation:

The correct command for forcing a Save As dialog box to open to download a file is Save Target As in Internet Explorer. If you use a different browser, the command may be different.

apply.) 

A.         =B1+B2 

B.         =B1+15-A6

C.         Grand Total 

D.         437.222 

Correct answer(s): AB 

Explanation:

A formula is a math calculation of some type. It may involve constants (such as specific numbers), math operator characters like + or -, and cell references.

A.        Where the contents of the cell appear, and where you can type or edit the contents 

B.        The buttons and slider that control the magnification at which you view the active worksheet 

C.        The small, customizable toolbar located just to the right of the Office button 

D.        The tabbed toolbar system that contains buttons and other controls for issuing commands 

Correct answer(s): D  Explanation:

The Ribbon is the multi-tabbed toolbar across the top of the Excel 2007 window. It replaces both the toolbars and the menu bar from the previous versions of Excel.

 4. To open a saved file, click the _________ button and then click Open. 

A.

                                                                                       Microsoft Office 

B.

                                                                            File 

C.

                                                                              Library 

D.

                                                   Data  

Correct answer(s): A  Explanation:

The Microsoft Office Button enables access to the Open, Save, Print, and other file management commands. It takes the place of the File menu in

previous versions of Excel.

5. Which key can you press to accept text you just entered into a cell and then move to the adjacent cell to the right? (Check all that apply.) 

A.         Tab 

B.         Esc 

C.         Right-arrow key 

D.         F1 

Correct answer(s): AC  Explanation:

You can move to another cell and accept the entry by pressing Tab or the right-arrow key.

You got 5 correct out of 5 questions.

Your Score: 100%

4.    To open a saved file, click the _________ button and then click Open. 

A.        Microsoft Office

B.        File

C.        Library

D.        Data

5.    Which key can you press to accept text you just entered into a cell and then move to the adjacent cell to the right? (Check all that apply.) 

A.        Tab

B.        Esc

C.       Right-arrow key

D.        F1


Starting a new workbook

In Lesson 1, you were introduced to Excel 2007 and you learned how to work with data files. Now you're ready to create your own workbooks from scratch and learn more about moving around in a worksheet; selecting cells; and inserting and deleting rows, columns, and cells.

As you learned in Lesson 1, Excel 2007 starts a new workbook each time you open the program. You can use that workbook as-is, saving it with whatever file name you like. This default workbook is based on a generic workbook template that contains three worksheet tabs (Sheet1, Sheet2, and Sheet3) and no special formatting or content.

To start another new workbook, click the Microsoft Office Button, and then select New. The New Workbook dialog box opens. Highlight the Blank Workbook template, and then click Create to create another workbook just like the default one.

 

Figure 2-1: The New Workbook dialog box can be used to start a new blank workbook.

Enlarge image

To quickly start a new blank workbook, press Ctrl+N. This keystroke combination bypasses the dialog box.

Alternatively, you can select one of the categories on the Templates list, and then select a template. Templates are samples that contain extra formatting, sample content, more or fewer worksheets, or any combination of those things; templates are designed to jump-start specific types of projects, such as business forms, calendars, reports, or schedules. For example, in Figure 2-2, the Budgets category is selected, and you can select from several list templates. Pick the one you want, and then click Download (or Create). The button is labeled "Download" if the template is located on the Internet and "Create" if it's located on your own hard disk.

   

Figure 2-2: The New Workbook dialog box can be used to browse templates at Microsoft Office Online.

Enlarge image

Try starting a new workbook:

1.   Click the Microsoft Office Button, and then select New.

2.   Select the Budgets category in the left pane.

3.   Highlight the Personal budget template, and then click Download.

4.   If a Microsoft Office Genuine Advantage dialog box opens, click Continue.

A new workbook starts in Excel.

Now create a new blank workbook using two different methods:

1.   Click the Microsoft Office Button, and then select New.

2.   Highlight the Blank Workbook template, and then click Create. A new blank workbook is created.

If you find yourself frequently needing to create a new blank workbook, place a shortcut button that does that on the Quick Access Toolbar. To do so: Click the Microsoft Office Button, and then click Excel Options. Select Customize in the left pane. In the left list of options, select New, click Add >> to move it to the list on the right, and then click OK.

3.   Close all open workbooks. To do so, click the Microsoft Office Button, and then select Close. Repeat this until all workbooks close. If you're prompted to save the changes you made to the Personal budget file, click No.

4.   Press Ctrl+N to create a new blank workbook.

See how to start a new workbook using templates. (1.5 MB file)

Now that you understand various methods for creating a new worksheet, you're ready to learn how to use Excel's navigational tools to move around a worksheet.

Moving around in a worksheet

Each spreadsheet is much larger than can be displayed on the screen at once. This gives you plenty of room to create very large tables of data or databases.

Row numbering increases as you scroll downward; the column letters run from A to Z from left to right and then start over with AA through AZ, then BA through BZ, and so on.

As you learned in Lesson 1, the worksheet window has vertical and horizontal scroll bars, and they provide the simplest method of moving around the sheet. They work just like the scroll bars in other applications. Figure 2-3 shows the parts of an Excel scroll bar.

 

Figure 2-3: Use the scroll bars to move around in the worksheet.

Here's how 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 scroll box.

To scroll quickly, drag the scroll box.

The scroll box changes size depending on how much of the sheet is currently not displayed; the smaller the scroll box, the more undisplayed content. In Figure 2-3, for example, approximately one-half of the vertical size of the worksheet wouldn't be displayed, because the scroll box takes up approximately one-half of the scroll bar overall.

Using the scroll bar doesn't change which cell is active; it changes only your view of the sheet. (Remember, the active cell is the one with the cell selector around it -- that thick outline that moves when you use the arrow keys or click in a cell.) It's possible for the active cell to not be visible onscreen at the moment because you've scrolled away from it.

You can press Ctrl+Backspace to bring the active cell into view if you lose track of it.

In Lesson 1, you learned that you can change the active cell by clicking a cell or by using the arrow keys. Here's a more complete list of the keystrokes and resulting movement of the cell selector:

 Arrows: One cell in the direction of the arrow

Enter: Beginning of next row (or beginning of data range in next row)

Tab: One cell to the right

Shift+Tab: One cell to the left

Home: Beginning of current row

Ctrl+Home: Beginning of the worksheet

Ctrl+End: Bottommost, rightmost nonblank cell in sheet

Page Down: Down one screen

Page Up: Up one screen

Alt+Page Down: Right one screen

Alt+Page Up: Left one screen

Now that you've picked up some techniques for navigating worksheets, it's time to find out how to select ranges. That's covered in the next section.

Selecting ranges

A range is a group of one or more cells. If you select more than one cell at a time, you can then perform actions on the group of them at once, such as applying formatting or clearing the contents. A range can even be an entire worksheet.

A range is referenced by the upper left and lower right cells. For example, the range of cells B1, B2, C1, and C2 would be referred to as B1:C2.

To select a range

 With the mouse: Drag across the desired cells with the left mouse button held down. Be careful when you're positioning the mouse over the first cell (before pressing the mouse button). Position the pointer over the center of the cell, and not over an edge.

If you drag while the pointer is over the edge of the cell, Excel interprets the selection as a move operation and whatever is in the cell(s) is dragged to a different spot.

 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 (where the letter is). To select an entire row, click the row header (where the number is). 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.

To practice range selection, follow these steps in any open workbook:

1.  Click column B's letter to select that column.

2.  Shift+click column D's letter. Columns B, C, and D should all be selected.

3.  Release the Shift key.

4.  Ctrl+click column G's letter. Now B, C, D, and G are all selected.

5.  Release the Ctrl key.

6.  Click row 2's number. The columns are deselected, and row 2 is now selected.

7.  Click in cell B4. The row is no longer selected, because you weren't holding down Shift or Ctrl. Now only B4 is selected.

8.  Press and hold the Shift key while pressing the down arrow key two times. Now B4 through B6 are selected. This range is called B4:B6.

9.  Still pressing the Shift key, press the right arrow two times. Now the range B4:D6 is selected.

10.   Release the Shift key, and then press and hold the Ctrl key.

11.   Drag across cells B9, C9, and D9. Those cells are selected in addition to B4:D6.

12.   Release the Ctrl key.

13.   Press Ctrl+A. This is a shortcut for selecting the entire sheet.

14.   Click in any cell -- or press the Esc key -- to undo the selection.

15.   Click the square containing a gray triangle at the upper intersection of the column letters and the row numbers. The entire sheet is selected again.

See how to select cells in a range. (.4 MB file)

If you use a laptop computer with a touchpad, you might need to spend additional time practicing range selection due to the sensitive nature of the touchpad. After you've mastered the technique, move on to the next section, which covers moving between cells and copying data.


Moving and copying data between cells

As you're constructing a spreadsheet, you might realize that you haven't placed some text in the correct spot. You can easily move content between cells either with drag-and-drop or cut-and-paste techniques.

To move content by dragging with the mouse, select the cell or range, and then drag the selection by its border to the new location.

Hold down Ctrl to copy rather than move.

Try it out:

1.   In a new workbook, type some text in cells A1, A2, B1, and B2.

2.   Select the range A1:B2.

3.   Position the mouse pointer over the border of the range, anywhere except in the lower-right corner. The mouse pointer appears as a four-headed arrow with a white pointer arrow overlaid on it.

4.   Press and hold the left mouse button, drag until the selection appears over D1:E2, and then release the mouse button to drop.

5.   With D1:E2 still selected, position the pointer over the border of the range.

6.   Press and hold the Ctrl key and the left mouse button, and then drag the selection back to A1:B2. A copy appears there, and the original stays in D1:E2.

Moving content also moves any formatting associated with that content. For example, if you have a heading formatted in a large font, and you move it to another cell, it remains formatted with that same font.

You can also move or copy using cut-and-paste with the Windows Clipboard. Use the Cut, Copy, and Paste buttons in the Clipboard group on the Home tab, or use the equivalent shortcut keys -Ctrl+X for Cut, Ctrl+C for Copy, and Ctrl+V for Paste.



Try it out:

1.   In the same worksheet you used in the previous steps, select D1:E2.

2.   Press Ctrl+X or select Cut on the Home tab to cut.

3.   Click cell D4 to select it.

4.   Press Ctrl+V or select Paste on the Home tab to paste.

5.   With D4:E5 still selected, press Ctrl+C or click Copy on the Home tab to copy.

6.   Select cell A4.

7.   Press Ctrl+V or select Paste to paste the data. See how to move and copy ranges of cells. (.6 MB file)

Setting the zoom level

The zoom is the magnification at which you're viewing the worksheet cells. It has no effect on the size of the Ribbon or other onscreen elements. At a higher zoom level (higher number), cells appear larger; at a lower zoom level, they're smaller; and you can see more of them at once.

You can change the zoom in several ways. The easiest is to drag the Zoom slider, which is in the bottom right corner of the Excel window, as shown in Figure 2-4. Drag to the left to decrease the zoom or to the right to increase it.

 

Figure 2-4: Drag the Zoom slider to change the magnification.

Zoom controls are also on the View tab:

The Zoom button opens a Zoom dialog box in which you can choose a specific percentage.

The 100% button resets the zoom to 100 percent.

The Zoom to Selection button zooms in so that the selected range is as large as possible while still fitting in the window.

Try out some of the Zoom options by doing the following:

1.   Drag the Zoom slider to the right, to 200%.

2.   Drag the Zoom slider to the left, to 50%.

3.   On the View tab, click the 100% button.

4.   Select cells A1:G11, and then click the Zoom to Selection button.

5.   Click the Zoom button. The Zoom dialog box opens.

6.   Click the 100% option, and then click OK.

Next, learn how to use the Auto Fill feature and the fill handle.

Using Auto Fill and the fill handle

As you're moving or copying with drag-and-drop, you must avoid the lower-right corner of the selected range, because it has a special use. Notice the little black square in that corner in Figure 2-5. That's the fill handle. You can drag it to fill adjacent cells with the same value as the current cell (or an incremental value). This feature is called Auto Fill.

 

Figure 2-5: The fill handle is the black square in the lower-right corner of the selected range.

The best way to understand this feature is to see it in action. Try it now:

1.   If there's anything on the current sheet, delete it. The easiest way is to press Ctrl+A to select all data and then press Delete to clear all cell content.

2.   Type your name in cell A1.

3.   Drag the fill handle down to cell A6. Your name is repeated in the range A2:A6.

Notice that with text, the same value is repeated in every cell. It also does this with some numbers, as in the following:

1.   Type 100 in cell B1.

2.   Drag the fill handle down to B6. The number 100 is repeated in cells in the range B2:B6.

However, in other cases, Auto Fill increments a number rather than repeating it:

1.   Type 1 in cell C1.

2.   Type 2 in cell C2.

3.   Select C1:C2.

4.   Drag the fill handle (in the bottom corner of C2) down to C6. Excel fills in the numbers 3, 4, 5, and 6.

This also works in other increments:

1.   Type 100 in cell D1.

2.   Type 150 in cell D2.

3.   Select D1:D2.

4.   Drag the fill handle down to D6. Excel fills in 200, 250, 300, and 350.

Custom lists are fill series that Excel always attempts to Auto Fill in their prescribed increments. Try the following to see the custom list for the days of the week, for example:

1.   Type Monday in cell E1.

2.   Drag the fill handle down to E6. Excel fills in the rest of the days of the week.

You can edit and create your own custom lists, although this procedure is beyond the scope of this course. To access the controls for doing so, click the Microsoft Office Button and then click Excel Options. Select Popular, and then select Edit Custom Lists.

See how to use Auto Fill and the fill handle. (1 MB file)

The next section shows you how to resize rows and columns. Read on.

Resizing rows and columns

If you type long entries into cells, the content overruns into the next column. If nothing is in that other column, no problem. If something is in the other column, the overflow cuts off. Figure 2-6 shows what happens with long entries in both cases. B3 and B4 both contain the same entry, "Sales Totals for July," but C3 contains nothing, whereas C4 contains "$1,000." If you want all the text in B4 to show, you need to widen the B column.

 

Figure 2-6: Example of what happens when content overruns a cell width.

You can manually drag the divider between two column headings to the left or right to adjust the width of the left column.

You can also AutoFit a column width, adjusting it so that its widest entry exactly fits. Following are a couple of ways to do that:

Double-click between two column headings.

On the Home tab, in the Cells group, click Format, and then select AutoFit Column Width.

To adjust the column width of all columns based on the text, select the entire worksheet (Ctrl+A) and then use the AutoFit feature.

You can also specify an exact width in number of characters. Column width is measured in characters of the default font. The default width is 8.43 characters. To specify exact width:

1.   Right-click a selected column, and then select Column Width. Alternately, on the Home tab, in the Cells group, select Format > Column Width. The Column Width dialog box opens.

2.   In the Column width text box, enter a number of characters.

3.   Click OK.

You can also adjust the row heights, although this isn't quite as important because row height adjusts automatically to accommodate the largest font used in that row. To adjust the row height, drag between the row numbers, the same as with columns. You also can use the AutoFit Row Height command on the Format button's menu (Home tab, Cells group).

If you set a cell (or multiple cells) to Word Wrap, and the cell isn't wide enough to accommodate all the text, the row height adjusts automatically to allow for multiple rows of text within the cell. To set Word Wrap, select the cell, and then 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.

Now that you know how to resize rows and columns, learn how to insert and delete rows and columns, which is covered in the next section.


Inserting and deleting rows and columns

If you don't plan your worksheet layout correctly, you might end up with too many or too few rows or columns in a certain area. You can always move data around in the sheet to help with this, but sometimes it's easier to simply insert or remove columns or rows.

To insert a column or row:

1.   If you want to insert multiple rows or multiple columns at once, select the same number of rows or columns that you want to insert. The new ones are placed to the right, or above, the ones you select.

If you want to insert only one row or column, you can click in any cell in the row or column adjacent to where the new one should go. You don't have to select the entire row or the entire column.

2.   On the Home tab, in the Cells group, select Insert, and then select what you want to insert (for example, Insert Sheet Rows or Insert Sheet Columns), as shown in Figure 2-7.

 

Figure 2-7: The Insert menu enables you to insert rows or columns, in addition to cells and an entire sheet.

To delete a column or row (or multiple columns or rows):

1.   Select the rows(s) or column(s) to delete.

2.   On the Home tab, in the Cells group, select Delete, and then select what you want to delete (for example, Delete Sheet Rows or Delete Sheet Columns).

You can also delete individual cells, but that's a slightly trickier matter, to be discussed in the next section.

Deleting versus clearing a cell

Many beginners get confused about clearing versus deleting in Excel, so let's look at this concept briefly. It may be helpful to think of an Excel worksheet as a stack of empty cardboard boxes, each one with its open side facing you. You can put something into a cell or take something out. When you take something out of a cell, it's called clearing its content. The cell itself remains in the "stack," but it's now empty.

Clearing content

You can clear a cell's content in several ways:

Press Delete on the keyboard.

Right-click the cell, and then select Clear Contents. On the Home tab, in the Editing group, select Clear > Clear Contents, as shown in Figure 2-8.

Clearing a cell's content doesn't clear its formatting. You can clear formatting from the Clear button's menu; select Clear Formats; or to clear both contents and formats at once, select Clear All.

 

Figure 2-8: To clear content or formatting from a cell, use the Clear menu.

Deleting cells

In contrast, deleting the cell removes the cell itself from the stack and makes the surrounding cells shift. Think about what happens when you pull a box out of a stack of boxes -- the boxes above it fall down one position, right? It's the same thing with Excel cells, except it's reverse-gravity (cells fall up rather than down), and you have the choice of making the remaining cells shift up or to the left.

To solidify your understanding of these concepts, try the following experiment:

1.   In a blank worksheet, enter the data shown in Figure 2-9.

 

Figure 2-9: Enter this data into a blank sheet to practice deleting cells.

2.   Select cell A1, and then press Ctrl+B to bold the entry.

(You'll learn more about formatting in Lesson 4.) 3. Press the Delete key to clear the A1 cell content.

4.  Type your first name in cell A1, and then press Enter. Notice that it's bold. The formatting wasn't removed when you cleared the content.

5.  Click in cell A1.

6.  On the Home tab, select Clear > Clear Formats. The name is still there, but it's no longer bold.

7.  On the Home tab, select Clear > Clear All.

8.  On the Home tab, select Delete > Delete Cells. The Delete dialog box opens.

9.  Select Shift cells up, and then click OK. The names in column A have all moved up one row.

10.   Select B1 and then repeat steps 8 and 9 to delete B1 so that the last names shift up to once again align with the proper first names.

11.   Close the workbook without saving your changes to it.

If you get confused when working with Excel 2007, remember that the key to understanding the difference between clearing a cell and deleting a cell is to visualize the cell as a box or container. Then you can decide whether you want to empty that box or remove it entirely.

Moving on

In this lesson, you learned how to create a new worksheet and how to enter and edit data into it, including how to automate the entry of repeated data. You also learned how to resize, insert, and delete rows and columns. Before you leave, take the quiz and complete the assignment. In Lesson 3, you'll get started with formulas and functions, the real "meat and potatoes" of the Excel application. With formulas and functions, you can set up cells that automatically calculate numeric values based on input from other cells and that automatically change their values when the numbers change.


Assignments are designed to help you apply the information learned in the lessons.

Create and modify a workbook

To practice using a template and inserting and deleting rows, do the following:

1.   Start a new workbook based on the Personal budget template available from Microsoft Office Online (in the Budgets category). Save the file as (in Microsoft Excel 2007 format).

2.   Go through the list of budget categories in column A and delete any rows that don't match your needs for a budget. Use the skills you learned in the lesson for moving around in a worksheet.

3.   Go through the list of budget categories again, adding new rows as needed for other budget items that you want to track that were not already on the sheet.

4.   Save the workbook and close it. You might want to come back to this workbook later and use it for your own personal budgeting; you won't need it again in this course.

5.   Start a new blank workbook, and then save it as .

6.   Type the data shown in Figure 2-10. Here are some suggestions:

Widen the columns, as needed, for everything to fit in its cells.

Use Auto Fill to fill in the other month names after typing January in cell B3.

 

Figure 2-10: Enter this data.

Enlarge image

7.   Insert a new row between Thomas Byrd and Nelson Brown, with the following data:

Salesperson: Kelly Porter

January: 20

February: 25

March: 30

April: 32

May: 26

June: 21

8.   Save your work, close the file, and then close Excel 2007.


Quiz: Lesson 2, quiz 1

Quizzes are designed to give you a chance to test your knowledge. 

 

1.    Which shortcut key combination starts a new blank workbook without opening the New Workbook dialog box? 

A.        Ctrl+C B. Ctrl+N

C.       Ctrl+P

D.       Ctrl+X

2.    If you want to move the selected range with the mouse, where do you position the mouse pointer over the range before starting to drag? 

A.        Over the border, but not over the bottom-right corner

B.        On the bottom-right corner of the border

C.        In the center of the range (not on any border)

D.        Anywhere on any part of the border

3.    On which tab does the Zoom to Selection feature appear? 

A.        Page Layout

B.        Formulas

C.        Review

D.        View

4.    Suppose that you type Monday in cell A1, select the cell, and then drag the fill handle to cell A2. What will appear in cell A2? 

A.        Tuesday

B.        Monday

C.        Sunday

Quiz: Lesson 2, quiz 1

Quizzes are designed to give you a chance to test your knowledge.

Please feel free to take this quiz again. We will update your score.

 Quiz Results

1. Which shortcut key combination starts a new blank workbook without opening the New Workbook dialog box? 

A.

            Ctrl+C 

B.

            Ctrl+N 

C.

            Ctrl+P 

D.

Ctrl+X  

Correct answer(s): B  Explanation:

Ctrl+N is the shortcut for starting a new blank workbook. Ctrl+C copies, Ctrl+P prints, and Ctrl+X cuts.

2. If you want to move the selected range with the mouse, where do you position the mouse pointer over the range before starting to drag? 

A.

            Over the border, but not over the bottom-right corner 

B.

            On the bottom-right corner of the border 

C.

            In the center of the range (not on any border) 

            D. Anywhere on any part of the border 

Correct answer(s): A  Explanation:

You must position the pointer over the edge of the selection, but not over the bottom-right corner, because that's where the fill handle is.

A.

                               Page Layout 

B.

                              Formulas 

C.

                              Review 

D.

                              View 

Correct answer(s): D  Explanation:

Zoom to Selection appears on the View tab.

4. Suppose that you type Monday in cell A1, select the cell, and then drag the fill handle to cell A2. What will appear in cell A2? 

A.

                               Tuesday 

B.

                              Monday 

C.

                               Sunday 

D.

                                #ERROR# 

                                                  Correct answer(s): A 

Explanation:

Tuesday will appear in cell A2 because the days of the week are set up as a custom list in Microsoft Excel 2007. Custom lists automatically fill consecutive values in adjacent cells.

5. Which of these are ways of changing the width of column C? (Check all that apply.) 

A.

            Drag the divider between the headings for columns B and C. 

B.

            Drag the divider between the headings for columns C and D. 

C.

            Select column C, and then right-click it and select Column Width. 

D.

            Double-click cell C1. 

E. Select column C, and then on the Home tab in the Cells group,

 select Format > Column Width. 

Correct answer(s): BCE Explanation:

To change the width of column C, work with the divider between columns C and D or select column C and issue commands for changing the width.

You got 5 correct out of 5 questions.

Your Score: 100%

D. #ERROR#

5. Which of these are ways of changing the width of column C? (Check all that apply.) 

A.       Drag the divider between the headings for columns B and C.

B.       Drag the divider between the headings for columns C and D.

C.       Select column C, and then right-click it and select Column Width.

D.       Double-click cell C1.

E.       Select column C, and then on the Home tab in the Cells group, select Format > Column Width.


Understanding formulas

In the previous two lessons, you learned the basics of the Microsoft Excel interface, along with data entry and editing and file handling. You learned how to create worksheets that contain text and numbers in individual cells and how to move, resize, insert, and delete rows and columns.

But if that's all Microsoft Excel were good for, a spreadsheet would be little more than a glorified table. Data entry and editing are just the tip of Excel's iceberg; its real power comes from its ability to "crunch numbers" with formulas and functions. In this lesson, you'll learn how to create formulas and functions and how to use them to summarize and calculate numeric data. 

What's a formula? 

A formula is an equation that performs some type of operation and issues a result. In Excel, formulas always begin with an equal sign. Here are some formula examples: 

•    =2+6: This formula is strictly math. If you place this formula in a cell, the cell displays 8. 

•    =A1+6: Same as the preceding, but this time you're adding 6 to whichever value is in cell A1 and displaying the result in the cell into which you enter this formula. This formula does not change A1's contents. 

•    =A1+A2: Same thing again, but you're adding the contents of cell A1 to the contents of cell A2. 

•    =A1+A2-A3: In this example, multiple cells are referenced. 

Understanding math operators 

Here are the symbols you can use in formulas to indicate mathematical operations: 

•    +: Addition 

•    -: Subtraction 

•    *: Multiplication 

•    /: Division 

•    ^: Exponentiation 


Exponentiation means to multiply the number by itself. For example, 2 to the 4th power is 2 x 2 x 2 x 2 and would be written 2^4 in Excel. 

To try a basic formula, do the following: 

1.   In a new worksheet, type 6 in cell A1 and 7 in cell A2. 

2.   In cell A3, type =A1+A2, and then press Enter. 

3.   Select cell A3. Notice that it displays 13 in the cell itself, and in the formula bar, the original formula you entered appears. 

Figure 3-1: Cell A3 shows the result of the formula; the formula bar shows the formula itself. 

4.   Click in the formula bar to move the insertion point there and edit the formula to read as follows: =A1+A2+5. Then press Enter. The value now appears as 18. 

5.   Change the value in cell A1 to 4. The value in A3 changes to 16. 

In the next section, you'll learn about order of operations and grouping, which is useful when you have more than two cell references or fixed numbers in a formula.  

Understanding order of operations and grouping

The math operators in Excel have an order of operation, just like in regular math. The order of operation is the order in which they're processed when multiple operators appear in the same formula. Here are the rules that determine the order: 

1.   Any operations that are in parentheses, from left to right 

2.   Exponentiation (^) 

3.   Multiplication (*) and division (/) 

4.   Addition (+) and subtraction (-) 

Parentheses override everything and go first. So, if you need to execute an operation out of the normal order, you place it in parentheses. 

For example, suppose you have this formula: 

=5+16/4^2

The order of operation looks like this: 

1.   The exponentiation (4^2 equals 16) 

2.   The division (16/16 equals 1) 

3.   The addition (5+1 equals 6) 

The result of the formula is 6. 

If you wanted the addition to occur first, you could put parentheses around the addition portion of the equation, like this: 

=(5+16)/4^2

This time, the order of operation looks like this: 

1.   The part in parentheses (5+16 equals 21) 

2.   The exponentiation (4^2 equals 16) 

3.   The division (21/16 equals 1.3125) 

The result of this formula is 1.3125. 

If you wanted to perform the exponentiation last, you could add another set

of parentheses, like this: 

=((5+16)/4)^2

Now the order is this: 

1.   The inner parentheses (5+16 equals 21) 

2.   The outer parentheses (21/4 equals 5.25) 

3.   The exponentiation (5.25^2 equals 27.5625) 

The result of this formula is 27.5625. 

If you left off the inner set of parentheses and wrote it like this: 

=(5+16/4)^2

The order would be as follows: 

1.   The division, because it's the highest-ranking operation inside the parentheses (16/4 equals 4) 

2.   The rest of the parenthetical operations (5+4 equals 9) 

3.   The exponent (9^2 equals 81) 

The result of this formula is 81. 

The next section provides an opportunity to practice working with formulas. 

Looking at some formula examples

Now let's try some formula examples that refer to cells and use math operations. For this exercise, enter the following values in cells in a blank worksheet: 

•    A1: 12 

•    A2: 6 

•    A3: 4 

•    A4: 9 

Then create the following formulas, to produce the results shown in Figure 32: 

1.   In cell A5, create a formula that adds A1+A2+A3+A4. 

2.   In cell A6, create a formula that adds A1+A2, and then multiplies the result by the sum of A3+A4. 

3.   In cell A7, create a formula that adds A1+A2+A3+A4 and then divides the result by 4. 

4.   In cell A8, create a formula that first calculates A4^A3 and then adds A1 to it. 

Figure 3-2: A sample worksheet after entering the formulas specified in the preceding steps. 

If your formulas didn't produce the results shown in Figure 3-2, check them by showing the formulas, which is covered in the next section. 

Showing formulas 

By default, you see the formula results in the cells, but sometimes it can be helpful to see the formulas there instead. For example, you might want to check your work after entering the formulas in the preceding section. 

To toggle between the two views, press Ctrl+` (grave accent). This isn't an apostrophe; it's the accent symbol that's immediately above the Tab key on most keyboards. Figure 3-3 shows the same formulas as Figure 3-2, but with the formulas displayed in this manner. Press Ctrl+` again to switch back. 

Figure 3-3: The worksheet from Figure 3-2, but with formulas displayed. 

See how to use formulas. (.6 MB file) 

In the next section, you'll learn about functions and how they improve upon the basics provided by formulas. 

Understanding functions

Sometimes the number of entries in a formula can make it awkward to type and edit. For example, suppose that you wanted to sum the values in a range of 10 cells, like this: 

=A1+A2+A3+A4+A5+A6+A7+A8+A9+A10

Now, that's bad enough, but suppose it's a 100-cell range, or 1,000, or 10,000. That's too much typing! 

It gets even more complicated when you start dealing with other operations besides the standard five that you learned about earlier in this lesson (addition, subtraction, multiplication, division, and exponentiation). For example, suppose that you want to find the average of the ten cells' values. You would need to sum them and then divide by 10, like this: 

=(A1+A2+A3+A4+A5+A6+A7+A8+A9+A10)/10

You have to put parentheses around the part that should be done first if it's outside of the normal order of operations, as it is in the preceding formula. 

For situations like these, functions are invaluable. A function stands for a type of mathematical or logical calculation. Each function has one or more arguments, which are like instructions that tell the function how to execute. The arguments go in parentheses after the function's name. The most common argument is the range of cells to which the function should apply. So, for example, to average the range A1 through A10: 

=AVERAGE(A1:A10)

Functions have a couple of important benefits over regular formulas: 

•    They help you perform math calculations more simply for operations that involve more than just the basic operators, such as averaging. Some functions even offer capabilities that pure math cannot accomplish, such as finding the minimum or maximum value in a range. 

•    You can refer to a range of cells by indicating the first (top left) and last (bottom right) cells, as in A1:A4, rather than typing. 

The latter dramatically cuts down on the amount of typing you need to do. In addition, if you insert more rows and columns in the specified range later, the reference to the range in the function refers to them automatically. In contrast, if you manually type each cell's name into a formula and then you insert more cells, the formula isn't automatically updated. In the next section, you'll explore some common Excel functions and practice using a few of them. 

Identifying some common functions

Here are some of the most commonly used functions: 

•    =SUM: Adds a range of numbers. 

•    =AVERAGE: Averages a range of numbers. 

•    =COUNT: Counts how many numbers are in the range. 

•    =MIN: Reports the lowest number in the range. 

•    =MAX: Reports the highest number in the range. 

•    =TODAY: Shows today's date. 

•    =NOW: Shows the current date and time. 

All of these functions require a single argument (consisting of the range to be acted upon) except for the last two, which do not take any arguments. You just enter a blank set of parentheses after them, like this: 

=TODAY()

Practicing with functions 

To try out some of the functions you just learned about, do the following: 

1.   Download the file , and then open it in Excel. 

2.   In cell D5, enter =SUM(B5:C5). That's one way of entering a function's range -- by manually typing it in. 

3.   In cell B11, enter =SUM( and then drag across the range B5 through B10. The range is filled in automatically. Press Enter to finish the formula. (Excel adds the closing parenthesis for you automatically.) 

4.   In cell G4, enter =COUNT(A5:A10). Hmm, the result is 0. Any guesses as to why? It's because those cells contain text, and the COUNT function counts only numeric values. 

5.   Modify the function in G4 to read =COUNT(B5:B10). Now it should show a result of 6. 

6.   In B2, enter =TODAY( ). If a series of pound signs (#) appear in the cell, adjust the cell's width to display the contents. 

7.   Enter the appropriate functions into G5 through G10 on your own. Compare the results to Figure 3-4. 

Figure 3-4: The completed worksheet. 

Enlarge image 

If your answers are different, press Ctrl+` and check your work against the worksheet shown in Figure 3-5. 

Figure 3-5: The completed worksheet with functions displayed. 

Enlarge image 

Understanding multi-argument functions

Some functions take more than one argument. When this is the case, you should enter them in a single set of parentheses, separated by commas. One example is the =NPER function, which helps you determine how many payments you'll need to make on a loan in order to pay it off. It has three required arguments: 

•    Rate: The interest rate charged per period (for example, per month) 

•    Pmt: The amount of payment you'll make in each period 

•    Pv: The present value of the loan -- in other words, how much you owe 

You enter multiple arguments within a single set of parentheses, separated by commas. So, suppose that you have an interest rate of 6 percent a year (that's 0.5 percent per month). You'll make $200 payments each month, and the starting value of the loan is $10,000. Here's how you would write the function: 

=NPER(0.005,200,-10000)

The reason you use a negative -10000 is that the money is being borrowed, so you're starting with a negative present value -- that is, you're "in the hole" by that much. 

Try that one now in any empty cell of any open worksheet. If you enter it

correctly, the answer should be 57.680136. 

The next section covers the Insert Function feature, which is a handy way to use functions without having to memorize the syntax for each one. 

Using the insert function feature

Nobody can be expected to memorize all the arguments and syntax for the hundreds of functions that Excel provides. Large reference manuals catalog all their details, and a large portion of Excel's online Help system is devoted to function syntax and examples. Fortunately, though, Excel makes it easy to use the right arguments in a function by providing an Insert Function feature. 

The Insert Function feature is helpful not only when you need help with a function's arguments, but also when you've forgotten the name of the function you want. Suppose that you want to find the absolute value of the number in cell A1. You might guess at the function name, but it's easier to just look it up. 

To try it out, display the Example2 tab in the file you downloaded earlier in the lesson. Then do the following: 

1.   Click in cell B6. 

2.   Click the Insert Function button (the fx button) on the formula bar or click the Insert Function button on the Formulas tab. The Insert Function dialog box opens. 

3.   Click in the Search for a function text box, and then type any keywords that represent the function you're seeking. For our example, enter absolute value. 

4.   Press Enter or click Go. Functions matching those keywords appear, in order of the closeness of the match, as shown in Figure 3-6. 

If ABS (absolute value) and several other functions don't appear under Select a function, make sure the Or select a category drop-down list is set to All. 

Figure 3-6: Using the Insert Function feature enables you to select the desired function based on name or type. 

Enlarge image 

5.   Click ABS and read the description. 

6.   Click OK. The Function Arguments dialog box appears. This particular function has only one argument: the range of cells. 

7.   Enter B5 in the text box provided, as shown in Figure 3-7. 

If you're not sure of the range, you can click the Browse button to the right of the text box to minimize the Function Arguments dialog box, and then drag to select the desired cell or range. 

Figure 3-7: Enter arguments for the chosen function. 

Enlarge image 

Required arguments appear in bold; optional ones appear in normal weight text.

8.   After all arguments have been entered (at least the required ones), click OK. The dialog box closes, and the function is entered into the cell. Cell B6 should now show a value of 13, and if you select B6 and look in the Formula Bar, it should show =ABS(B5) there. 

Next, learn to use the buttons and menus on the Formulas tab to insert functions. 

Working with the Formulas tab

The Formulas tab contains buttons for working with formulas and functions. On it, the Function Library group provides drop-down lists for the most popular categories of functions, as shown in Figure 3-8. You can use these lists as an alternative to using the Insert Function dialog box covered in the previous section. When you select a function from one of these lists, the Function Arguments dialog box opens, just as it would have if you'd chosen the function from the Insert Function dialog box. 

If you click the AutoSum button, it inserts the SUM function. This button also has a drop-down list, from which you can choose from among several common functions (AVERAGE, COUNT, MIN, and MAX). 

Figure 3-8: Select functions by category from the Formulas tab. 

Enlarge image 

See how to use functions. (2.3 MB file) 

When you move or copy a formula or function that contains cell references in it, the references shift. You'll learn about that in the next section. 

Copying formulas and functions

When you copy a formula or function, you usually don't want an exact copy of it, right? Instead, you want the copy to refer to its surrounding cells in the same way that the original did. 

For example, take a look at Figure 3-9, the Quarterly Results sheet from the earlier example. The function in D5 is =SUM(B5:C5). You might like to copy this formula to D6, D7, D8, D9, and D10; however, you would want each copy to refer to the appropriate row number, not row 5. 

Figure 3-9: Copying the content of D5 to D6:D10 results in relative copies in each cell. 

Excel knows that you usually want cell references to shift, so it uses relative references by default. A relative reference changes when you move it to another cell. For example, if the function =SUM(B5:C5) appears in cell D5, what it is really saying is "sum the values in the two cells immediately to my left." Copying that function to cell D6 would result in =SUM(B6:C6). 

Excel also offers absolute referencing. With an absolute reference, the cell name(s) don't change when you move or copy the formula or function. To experiment with this, place a dollar sign before the row and before the column to fix them in place. For example, a relative reference to A1 would be =A1; an absolute reference to A1 would be $A$1. 

To try this for yourself, do the following: 

1.   In , display the Example1 tab. 

2.   Click cell D5, which should already contain the formula =SUM(B5:C5). If it does not, type that formula there. 

3.   Drag the fill handle (small black square in the bottom right corner) from cell D5 down to D10. 

4.   Examine the contents of D6, D7, D8, D9, and D10 in turn by clicking a cell, and then looking in the Formula Bar. Notice that the cell references are different in each one. 

5.   Copy the formula from B11 into C11 using copy and paste. Then check the formula in C11 and notice that it is different from the one in B11, also due to relative references. 

Common formula errors

Here are some of the most common mistakes people make when entering formulas and functions: 

•    Not putting in all the required arguments: If a function is expecting more arguments than you have entered, and you get the dialog box shown in 3-10, be sure you've placed commas between the arguments and that you haven't overlooked any. 

Figure 3-10: This error results from improper syntax in a function. 

Enlarge image 

•    Circular references: If you refer to the cell's own address in a function, you create a circular error, which is like an endless loop. Suppose that you enter =A1+1 into cell A1. You'll get an error message like the one shown in Figure 3-11. If you click OK at this message, a Help window appears to help you find the problem. 

Figure 3-11: This message indicates a circular reference 

Enlarge image 

•    Text in an argument: Most functions require numeric arguments. If you enter text as an argument, for example, =SUM(text), the word #NAME? appears in the cell. This happens because Excel allows you to name ranges of cells using text, so technically =SUM(text) isn't an invalid function. It is invalid only if there's no range that has been assigned the name "text." 

•    Hash marks (###) in a cell: This happens when the cell isn't wide enough to display its value. Widen the column to fix this. 

If you receive an error when copying a formula, don't panic; it happens to everyone. Use the skills you learned earlier in this chapter to display the formulas and then check them for the common errors discussed here. 

Moving on 

In this lesson, you learned how to create and edit formulas and functions. There's a lot more to know about these powerful tools; however, this lesson provided all the basics you'll need for simple worksheets. In Lesson 4, you'll learn how to format a worksheet. So far, we have been focusing on the nuts and bolts -- the hard stuff -- so formatting will be a fun and refreshing change. Before logging off, do the assignment and quiz for this lesson. 


Assignments are designed to help you apply the information learned in the lessons.

Using formulas and functions  For this assignment: 

1.         Download , and then open it in Microsoft Excel. 

2.         In cell C11, enter a function that averages C4:C10. 

3.         Copy the function from C11 to D11:I11. Adjust any column widths to display cell contents, if necessary. 

4.         In cell C12, enter a function that finds the minimum value in C4:C10. 

5.         Copy the function from C12 to D12:I12. 

6.         In cell C13, enter a function that finds the maximum value in C4:C10. 

7.         Copy the function from C13 to D13:I13. 

8.         In cell C14, enter a function that finds the standard deviation in C4:C10. 

9.         Copy the function from C14 to D14:I14. 

10.        In cell J4, enter a function that averages C4:I4. 

11.        Copy the function from J4 to J5:J14. 

12.        Save the file, check your work against the solution, close the file, and then close Excel. 

You have completed this assignment.

Check your work against Figure 3-12. If yours contains any mistakes, download , and then check the formulas there against your own. 

Figure 3-12: These results should appear if you entered the functions correctly. 

Enlarge image 


Grade Book

Name

               

Allen

Joyce

100

88

74

99

80

79

92

 

Baird

Tom

84

82

79

88

82

81

94

 

Chase

Brian

79

87

89

72

67

83

90

 

Davis

Todd

66

74

79

89

88

62

83

 

Ege

Nancy

99

94

92

88

75

90

97

 

Foreston

George

71

84

73

74

88

84

70

 

Gaines

Pat

55

61

0



60

51

70

57

 
 

Average

               
 

Lowest

               
 

Highest

               
 

Std. Dev.

               
                   

Quiz: Lesson 3, quiz 1  

Quizzes are designed to give you a chance to test your knowledge. 

 

1.    Which of the following is an example of a function? 

A.        =B1+B2

B.        =SUM(B1:B2)

C.        =AVG[B1:B2]

D.        #NAME?

2.    Given the order of operations, which is the answer Microsoft Excel 2007 provides to the formula =56+16/4^2*2? 

A.        58

B.        9

C.        0.28125

D.        648

3.    Which function displays today's date and the current time? 

A.        =TIME()

B.        =DATE()

C.        =TODAY()

D.        =NOW()

4.    With a function that takes multiple arguments, how do you separate one argument from another within the parentheses? 

A.        Semicolon

B.        Comma

C.        Colon

D.        Period

Quiz: Lesson 3, quiz 1

Quizzes are designed to give you a chance to test your knowledge.

Please feel free to take this quiz again. We will update your score.

 Quiz Results

 1. Which of the following is an example of a function? 

A.

            =B1+B2 

B.

            =SUM(B1:B2) 

C.

            =AVG[B1:B2] 

D.

#NAME?  

Correct answer(s): B  Explanation:

A function begins with an equal sign and the function name, followed by arguments in parentheses. =B1+B2 is a formula but not a function. =AVG[B1:B2] is not a function because it's AVERAGE, not AVG, and functions use parentheses, not square brackets. #NAME? is an error message that appears in a cell, not a function.

2. Given the order of operations, which is the answer Microsoft Excel 2007 provides to the formula =56+16/4^2*2? 

A.

            58 

B.

            9 

            C. 0.28125 

D.

                              648 

Correct answer(s): A  Explanation:

Excel should display 58 as the result of the formula =56+16/4^2*2.

 3. Which function displays today's date and the current time? 

A.

                               =TIME() 

B.

                               =DATE() 

C.

                               =TODAY() 

D.

                               =NOW() 

Correct answer(s): D  Explanation:

The function =NOW() displays the current date and time. The function =TODAY() displays only the date.

4. With a function that takes multiple arguments, how do you separate one argument from another within the parentheses? 

 

Correct answer(s): B  Explanation:

Use a comma to separate arguments.

 5. On which tab is the Function Library group? 

A.

                               Functions 

B.

                               Reference 

C.

                              Data 

D.

                              Formulas 

Correct answer(s): D 

Explanation:                                                                                 

The Function Library group is on the Formulas tab.

You got 5 correct out of 5 questions.

Your Score: 100%

5. On which tab is the Function Library group? 

A. Functions B. Reference

C.       Data

D.       Formulas

 

Formatting text in cells

Welcome back for the last lesson in this course. In this lesson, you'll learn how to dress up those dull worksheets with various types of text and cell formatting, including borders, shading, number formats, font, size, and color changes, and more. 

Applying basic text formatting 

You might already be familiar with basic text formatting techniques from using programs such as Microsoft Word or PowerPoint. You can use the buttons on the Home tab, in the Font group, to apply a certain font, size, color, and other text attributes, as shown in Figure 4-1. The buttons are described as follows: 

Figure 4-1: The Font group contains buttons and lists for applying text formatting. 

•    Font: Open the drop-down list and select a font. 

The top two entries on the Font list, for the Heading and Body, work with the formatting themes you'll learn about in the next section. 

•    Font Size: Open the drop-down list and select a font size or click the Increase Font Size or Decrease Font Size button. 

•    Attributes: Use any of these buttons to apply bold, italic, or underline. 

•    Font Color: Click here to apply text color or open the button's menu to change the color. This process is described in more detail in the next section. 

The other two buttons in the Font group, the Borders button and the Fill button, are discussed later in this lesson. 

You can also click the dialog box launcher, in the lower-right corner of the Font group, to open the Format Cells dialog box with the Font tab displayed, as shown in Figure 4-2. From here, you can select the same formatting as on the Ribbon, but with a few extra options available, such as Strikethrough, Superscript, and Subscript. 

Figure 4-2: The dialog box launcher in the Font group opens the Format Cells dialog box. 

Enlarge image 

Try it out now by doing the following: 

1.   Download the file, and then open it in Excel. Save the file as (Excel 2007 format). 

2.   Select cell A1, and then change the font size to 24 point. 

3.   Apply the Bold and Italic attributes to cell A1. 

4.   Click the Decrease Font Size button once to decrease the size. It decreases by 2 points, to 22 pt. 

The Increase Font Size and Decrease Font Size buttons don't always change one point at a time; they change to the next size on the Font Size list. If you open the Font Size list, you'll notice that it jumps from 22 to 24; that's why clicking the button once in step 4 resulted in a 2-point change. If you wanted 23-point text, you could manually type the number 23 in the Font Size text box above the list. 

5.   Change the font size in the cell range B4:D4 and in cell A11 to 12 point bold. 

6.   Select cells A2 and F2, and then apply the Times New Roman font.  

7.   Save your work. Keep the file open for later use in the lesson. 

Next up, you'll learn how to apply color to text. 

Applying text color

The Font Color button in the Font group opens a palette of colors, as shown in Figure 4-3. You'll use this same type of palette for virtually all color selection in Excel (and in other Office 2007 applications), so it pays to learn about it now. 

Figure 4-3: The palette for selecting a color from the Font Color button. 

The top row contains a single color: Automatic. This is the default. It shows text in black when on a light-colored background and in white when on a darkcolored background. 

The next row is the Theme Colors. These are not fixed colors, but rather color placeholders. Each of the 10 slots is filled by a certain color; you can change which colors fill those slots by selecting a different color theme, which you'll learn about in the next section of the lesson. So, for example, suppose you apply the rightmost theme color (orange, in Figure 4-3) to some text, and then you change to a different color theme that defines color #10 as red. Any text that you've formatted with that color changes from orange to red. 

The five rows immediately beneath the theme colors are tints and shades of those theme colors. These are varying degrees of intensity -- various modification filters -- applied to the theme colors. When the theme colors change, these color swatches change too. For example, in Figure 4-3, the lightest tint of color #10 (orange) is shown as pale orange. If you were to apply a theme that defined color #10 as red, that lightest tint of the color would change to a pale pink. 

The Standard Colors are fixed colors. These colors don't change, regardless of the color theme applied. You would use these whenever you wanted to make sure that text remained the exact color you chose for it. 

If you have already selected any standard/fixed colors in this workbook, swatches for them appear in the Recent Colors section, for easy reuse. If you haven't, this section does not appear. 

The More Colors button opens a dialog box from which you can select additional standard (fixed) colors. You can select from a palette of additional color swatches (General tab) or specify a color by its RGB or CMYK numbers (Custom tab, shown in Figure 4-4). 

Figure 4-4: You can define additional fixed colors from the Colors dialog box. 

Try it out now on the file you saved earlier in the lesson: 

1.   Select cell A1, open the Font Color button's menu in the Font group, and then apply the fourth theme color from the left (Dark Blue, Text 2). 

2.   Select A2:G2, and then apply the third tint down from the fourth color theme color (Dark Blue, Text 2, Lighter 40%). 

3.   Select the cell range F4:F10, and then apply the Red standard color, which is second from the left in the Standard Colors section of the list.

4.   Select the cell range G4:G10. Open the Font Color button's menu, select More Colors, and then click the Custom tab in the Colors dialog box. Make sure the Color model is set to RGB, and then enter the following values: Red: 208, Green: 40, and Blue: 0. Click OK. 

5.   Save your work. Leave the file open for use later in the lesson. Check your work against Figure 4-5 to make sure you've done everything correctly so far. 

Figure 4-5: Check your work on the practice file. 

Enlarge image 

Next, you'll learn how to use formatting themes, a new feature in Excel 2007. 

Applying formatting themes

Formatting themes are sets of fonts, colors, and effects that you can apply to an entire worksheet to quickly change its look. These formatting themes are the same as in Word and PowerPoint 2007. Let's take a look at how they work. 

Formatting themes apply three things to the worksheet: 

•    They change the font placeholder, so that any text formatted using the font placeholder changes to a different font. 

•    They change the color placeholders, so that any objects with color applied using one of the theme colors change to the new colors. 

•    They change the object effects, so that any objects with formatting effect applied using one of the theme effects change to the new effects. 

Effects are for graphical objects, which are not covered in this course, so for this lesson, ignore effect options. 

You can apply an overall theme that changes all three of those things at once, or you can apply individual color themes, font themes, and effect themes to change only one. 

How formatting themes affect fonts 

In the preceding section, you learned a bit about theme colors versus fixed colors and how color choices are affected by the color theme. Now, let's look at how themes affect fonts. 

As you learned at the beginning of this lesson, when you apply a font choice to a cell, you can do any of the following: 

•    Apply any of the fonts on the Font list, in which case, the font stays fixed no matter which theme is applied. You did this, for example, in the practice file to the text in cell A2. 

•    Apply the Headings font placeholder, so it gets whichever font is currently assigned as the Heading font. 

•    Apply the Body font placeholder, so it gets whichever font is currently assigned to the Body font. 

You can tell which font has been assigned to the Headings and Body placeholders by opening the Font drop-down list and seeing the fonts listed at the top of the list, as shown in Figure 4-6. 

Figure 4-6: The theme fonts appear at the top. 

Applying a formatting theme 

If you use the Headings and Body placeholders rather than fixed fonts, you can change the worksheet's formatting with a theme. To apply a theme, select one from the Page Layout tab from the Themes group as shown in Figure 4-7. You can apply an overall theme with the Themes button's dropdown list, or you can apply only a Colors, Fonts, or Effects theme by using one of those individual drop-down lists. 

 Figure 4-7: Apply a formatting theme. 

Now let's try it. Use the practice file that should already be open from earlier in the lesson: 

1.   Select cell A1. Open the Font drop-down list, and then select the Headings font (Cambria) in the Themes Fonts section. 

2.   On the Page Layout tab, select Themes, and then point to several of the themes without clicking on them. Behind the open menu you can see how the worksheet changes to preview the various themes. 

3.   Select Median as the theme choice. Notice that not only have the fonts changed, but also the colors of the text in A1, A2, and F2 have changed. The text in cell range F4:G10 has not changed because it's formatted with standard/fixed colors. Notice that the font in A2 didn't change because it was assigned as a fixed font earlier. 

4.   In the Themes group, open the Colors list, and then select Opulent. Notice that the colors in A1, A2, and F2 change again, but there's no font change. 

5.   Open the Fonts list, and then select Office Classic. Notice that the fonts change, but no color change occurs. 

6.   Open the Effects list, and then select Apex. Nothing changes because no graphic objects are in the worksheet. 

7.   Save your work and leave the file open for future use in the lesson. 

See how to apply text formatting. (2.2 MB file) 

In the next section, you'll learn how to set the alignment for cell content. 

Aligning cell content

When you enter text in a cell, it aligns to the bottom left automatically. When you enter numbers, they align to the bottom right. These settings are just the defaults, however, and you're free to make changes. 

When a cell is at its default height, it's exactly the height needed to hold its contents. Vertical alignment becomes an issue only if you resize the row height so it's taller than it needs to be, or when a cell contains fewer lines than other cells in that same row. 

The easiest way to change cell alignment is with the buttons on the Home tab, in the Alignment group, as shown in Figure 4-8. Select the cell(s), and then click the desired alignment button. Separate sets of buttons are provided for vertical and horizontal alignment. 

Figure 4-8: Select an alignment. 

Several other useful alignment buttons are in the Alignment group as well, pointed out in Figure 4-9: 

•    Orientation: Opens a drop-down list of various text orientations. You can use this to make text run up-and-down or at an angle, for example.

•    Wrap Text: Toggles on/off the text wrapping feature, which allows text to wrap to additional lines and increase the cell height if it won't fit. 

•    Decrease Indent and Increase Indent: These buttons shift the text's starting point in the cell. 

•    Merge & Center: This button does two things: it merges the selected cells, and its sets the alignment within the new merged cell to horizontal center. 

 Figure 4-9: Other alignment group buttons. 

For more alignment options, click the dialog box launcher in the Alignment group (the small icon in the lower-right corner of the group) to open the Alignment tab in the Format Cells dialog box. Additional options for horizontal and vertical alignment are here, as are several options not available on the

Ribbon. Two notable alignment options are: 

•    Fill: Repeats the entered character(s) to fill the entire cell. For example, if you enter X in the cell and then apply the Horizontal Fill command, the cell fills with XXXXXXX. 

•    Justify: Aligns text to both the right and left in all lines except the last line in the cell by inserting spaces between words as needed. Most often only one line is in a cell, so Justify looks just like Left alignment. However, when you enter more text than fits the cell, and then use Wrap Text, you'll see Justify in action. 

Now let's try it. Use the practice file you have been working with throughout this lesson: 

1.   Select the cell range A2:A11, and then apply horizontal right alignment.

2.   Select the cell range A1:G1, and then click the Merge & Center button.

3.   Select the cell range B4:D4, and then apply horizontal center alignment.

4.   Increase the row height for row 1 to 40 point. (Hint: Use the Format button in the Cells group on the Home tab.) 

5.   Set the vertical alignment for row 1 to Center. 

6.   Save your work. Leave the file open for later work in this lesson. 

Next, you'll learn about cell borders and shading. 

Applying borders and shading

It's important to understand the difference between gridlines and borders in Excel. The dividers between rows and columns in Excel are gridlines. They appear as soft grey lines onscreen by default, and they do not print. When you apply formatting to a gridline, you create a border. Borders do print and can be any color, thickness, and style you like. 

Gridlines can be turned on/off for both onscreen viewing and printing. To control these, use the check boxes in the Sheet Options group on the Page Layout tab. 

Applying cell borders 

The Home tab's Font group has a Border button that provides an easy way to apply a default-style border. Select the cells, and then open the Border button's menu, as shown in Figure 4-10, and select the button that best represents the border(s) you want. 

Figure 4-10: The Border button's menu enables you to apply borders to certain sides of the selected area. 

To try your hand at applying borders, follow these steps using the practice file you've been working with throughout this lesson:

1.   Select the cell range A4:D11. 

2.   Click the arrow on the Borders button, opening its menu, and then select All Borders. 

3.   Select the cell range A10:D10. 

4.   Click the arrow on the Borders button, and then select Double Bottom

Border. 

5.   Select the cell range A4:D4. 

6.   Click the arrow on the Borders button, and then select Thick Bottom Border. 

7.   Save your work and leave the file open for later work in this lesson. 

You can also apply borders via the Format Cells dialog box, as shown in Figure 4-11. This method gives you more precise control over the border style and over the sides to which it's applied. To access it, click the dialog box launcher for the Font group (Home tab) and then click the Border tab. 

Figure 4-11: Use the Border tab to more precisely control the borders. 

Enlarge image 

The best way to see how these border controls work is to try them out. Use the same practice file as before and follow these steps: 

1.         Select the cell range F4:G10, and then click the dialog box launcher in the Font group. The Format Cells dialog box opens. 

2.         Click the Border tab. 

3.         In the Style area, select the third choice on the left side (the dotted line). 

4.         Open the Color list, and then select the Red color swatch under Standard Color. 

5.         In the Border area, select each of the three vertical line buttons at the bottom of the sample. Dotted red lines appear vertically on the sample area. 

6.         In the Style area, select the thick solid line (sixth down on the right side). 

7.         In the Border area, select the top and bottom horizontal lines. 

8.         Click OK. 

Some of the lines might look bright blue at this point; that's just because they're selected, and when things are selected, they appear in their opposite color. Click away from the selection to see how it really looks. 

9.         If the text appears to hang into the E column, widen column F as needed. The result should resemble Figure 4-12. 

Figure 4-12: The statistics after applying custom borders. 

10.        Save your work and leave the file open for later use in the lesson.

Applying shading 

Shading is the colored background (if any) behind a cell. By default, a cell has no shading, but you can add any of a variety of solid or patterned backgrounds. 

For simple, no-frills shading, the easiest way is to use the Fill Color button on the Home tab (Font group) toolbar. It looks like a paint can. Click the arrow to its right to open its palette of colors and pick a color, just like you did earlier with the font color. The same rules apply for standard colors, theme colors, tints/shades, and so on. 

If you plan to save the file in Excel 97-2003 format, some of the tints/shades might not work properly. Try to stick with standard colors if you think you'll be distributing your work in a format other than Excel 2007's native format. 

See how to apply borders and shading. (1.3 MB file) 

Next you'll learn about number formats. 

Using number formats

The term number format in Excel has a very specific meaning; it doesn't just mean text formatting like bold or italic. It refers to formatting that affects the reader's understanding of the number's context, such as using a dollar sign, percentage sign, or number of decimal places. 

The Number group on the Home tab, shown in Figure 4-13, contains some buttons for the most popular number formatting options: 

Figure 4-13: The Number group on the Home tab enables you to format numbers. 

Enlarge image 

•    Number Format: Select a number format from this drop-down list

(which currently displays "Currency"). You can also select More Number Formats at the bottom of the drop-down list to view a dialog box with more options. 

•    Accounting Number Format: Click this button to quickly set up a number as currency or open the button's menu to select a nationality (currency

symbol other than $). 

•    Percent Style: Click this button to change the number into a percentage.

•    Comma Style: Click this button to insert commas into long numbers (more than 3 digits to the left of the decimal point). 

•    Increase Decimal and Decrease Decimal: These buttons change the number of decimal places shown for the number. 

When you apply a format to a number that decreases its precision, the original value isn't lost. Excel still uses the original value for any calculations in which the cell is involved. For example, if you set the number of decimal places to 0 for two cells both containing 2.49, they each display as 2. However, if you create a formula that adds them, the result is 4.98. If you then format the result with 0 decimal places, the result appears as 5. So in this case, it would appear that 2+2=5. Be careful that you don't inadvertently create confusion for your readers with this sort of formatting. 

If you select More Number Formats from the Number Format list or click the dialog box launcher for the Number group, the Format Cells dialog box opens with the Number tab displayed. From here, you can fine-tune the settings for the chosen number format. The following table lists the number formats and the types of options you can set for each. 

Category 

Purpose/Usage 

Options

You Can

Set 

General 

The default. This is, effectively, "no format." 

None 

Number 

General numbers that don't fall into any of the other categories. 

Decimal places Use of comma Negative number indicator 

Currency 

General currency values. The dollar sign is flush up against the leftmost digit. 

Decimal places Currency symbol Negative

   

number indicator 

Accounting

Currency values for accounting. Same as Currency, except the dollar sign left-aligns in the cell. 

Decimal places Currency symbol 

Date 

Dates (day, month, year), such as January 1, 2006. 

Type

(format)

Locale 

Time 

Times (hours, minutes, seconds), such as 12:34:42 A.M. 

Type

(format)

Locale 

Percentage

 Percentage values, such as 25% or 120%. 

Decimal places 

Fraction 

Fractional values, such as 2/3 or 21/25. 

Type 

Scientific 

Large numbers. A scientific format consists of the number reduced to a single digit, a decimal point, and then one or more digits, followed by the letter "E" and then the number of places the decimal point should be moved to the right to create the original number. For example, 201,000 would be 2.01E05. 

Decimal places 

Text 

Numbers that should be treated as text, such as identification numbers in an inventory. 

None 

Special 

Special-purpose templates for Zip codes, phone numbers, and Social Security numbers. 

Type 

Custom 

Codes that let you define your own number formats. 

Type 

Table 4-1: Number format categories. 

In the next section, you'll learn how to format entire pages and set up a worksheet for printing. 

Setting page formatting and printing options

So far, we've been working with individual cell formats. Now let's look at the worksheet as a whole. You can adjust margins, create headers and footers, repeat certain rows/columns on each page, and much more. 

Enough useful page-wide settings exist to create an entire lesson, but this section summarizes them for you. You'll want to spend more time with them on your own. All of these settings are located on the Page Layout tab. 

Page setup 

The Page Setup group on the Page Layout tab, shown in Figure 4-14, provides menus for changing various document-wide settings: 

 Figure 4-14: Page Setup group. 

•    Margins: Select Normal,Wide, or Narrow presets or select Custom Margins to enter your own values. 

•    Orientation: Select Portrait or Landscape. 

•    Size: Select a paper size; this setting determines where Excel shows page breaks in Print Preview. 

•    Print Area: Select a range of cells, and then select Print Area,Set Print Area to print only a certain part of the worksheet. Use Print Area,Clear Print Area to reset the worksheet so everything prints. 

•    Breaks: Set or remove hard page breaks here. 

•    Background: Specify a graphic to use as a background behind the cells. 

•    Print Titles: Click this button to open the Sheet tab of the Page Setup dialog box, in which you can select certain rows and/or columns to repeat on every page of a printout. 

After you've tried the Page Setup group, familiarize yourself with the Scale to Fit group in the next section. 

Scale to fit 

In this group, you can set Excel to automatically shrink a printout's font to print a document on a specified number of pages, or to print at a certain percentage of the original font size, as shown in Figure 4-15. 

Figure 4-15: Scale to Fit group. 

This section discussed just a few of the page-wide settings available in the Page Setup and Scale to Fit groups, accessible from the Page Layout tab. Practice with these options and others on your own to increase your knowledge of Excel. 

Moving on 

In Lesson 4 you learned how to use font and number formats, text color, and cell alignment to customize the look of your Excel worksheets. You also learned to apply formatting themes, borders, and shading to your spreadsheets and how to use many of the options available to create effective, useful documents 

Congratulations! You've finished the course. Complete the assignment and quiz before you go, then give yourself a congratulatory pat on the back. Now you're ready to tackle your own Excel projects with confidence. 

Assignments are designed to help you apply the information learned in the lessons.

Format a gradebook  For this assignment: 

1.         Download and open in Microsoft Excel 2007. 

2.         Format the title in cell A1 using the Headings font placeholder (Cambria, by default), 24-point, and bold. Use Merge & Center to merge cells in the cell range A1:J1, and then center the title. 

3.         Format the column titles in row 3 using the Headings font placeholder (Cambria), 12-point, and bold. 

4.         Click the Orientation button to rotate the text up in C3:J3. 

5.         In the following ranges, set the number of decimal places to 1 using the Number format: C11:H11, C14:H14, I11:I14, J4:J14.  

6.         Adjust the widths for columns C through J to be exactly 5 characters. 

7.         Apply pale gray shading to the cell range B11:J14. 

8.         Apply a thin dark green border on all sides of all cells in the range A4:J10. Use a theme color, not a standard color. 

9.         Apply a thin dark blue dotted border on all sides of all cells in the range B11:J14. Use a theme color, not a standard color. 

10.        Apply a thick dark blue solid border on the top only of the cells in the range B11:J11. Use a theme color, not a standard color. 

11.        Switch to the Urban theme. 

12.        Save your work. 

You have completed this assignment.

Check your work against Figure 4-16. You may also download

, and then check the formatting there

against your worksheet. 

Figure 4-16: These results should appear if you applied the formatting correctlyEnlarge image

 

Quiz: Lesson 4, quiz 1

Quizzes are designed to give you a chance to test your knowledge. 

 

1.    What happens when you apply a different color theme to a worksheet that uses standard colors? 

A.        The standard colors change to those defined by the theme.

B.        The standard colors don't change.

C.        Only the Windows colors change; the others remain the same.

D.        Only the primary colors change; the others remain the same.

2.    Which feature makes text tilt up or down at an angle? 

A.        Vertical alignment

B.        Horizontal alignment

C.        Orientation

D.        Diagonal

3.    In which dialog box do you set up custom borders for cells and ranges? 

A.        Borders and Shading dialog box

B.        Format Cells dialog box

C.        Worksheet dialog box

D.        Format Object dialog box

4.    Which of the following is not one of Microsoft Excel 2007's number format categories? 

A.        Scientific

B.        Number

C.        General

D.        Standard

Quiz: Lesson 4, quiz 1

Quizzes are designed to give you a chance to test your knowledge.

Please feel free to take this quiz again. We will update your score.

Quiz Results

1. What happens when you apply a different color theme to a worksheet that uses standard colors? 

A.

            The standard colors change to those defined by the theme. 

B.

            The standard colors don't change. 

C.

            Only the Windows colors change; the others remain the same. 

D.

Only the primary colors change; the others remain the same.  

Correct answer(s): B  Explanation:

Standard colors, also called fixed colors, are not affected by a color theme change.

A.

            Vertical alignment 

B.

            Horizontal alignment 

C.

            Orientation 

            D. Diagonal 

Correct answer(s): C  Explanation:

Text tilts according to its orientation.

 3. In which dialog box do you set up custom borders for cells and ranges? 

A.

                                   Borders and Shading dialog box 

B.

                                  Format Cells dialog box 

C.

                                 Worksheet dialog box 

D.

                                  Format Object dialog box 

Correct answer(s): B  Explanation:

Use the Format Cells dialog box, on the Borders tab.

4.    Which of the following is not one of Microsoft Excel 2007's number format categories? 

A.

                               Scientific 

B.

                              Number 

C.

                              General 

D.

                               Standard 

Correct answer(s): D  Explanation:

All of the choices are number format types except Standard.

5.    On which tab is the Page Setup group, where you can define margins, orientation, paper size, and print area? 

A.

            Page Layout 

B.

            Review 

C.

            View 

D.

Home  

Correct answer(s): A  Explanation:

Page Setup is on the Page Layout tab.

You got 5 correct out of 5 questions.

Your Score: 100%

5. On which tab is the Page Setup group, where you can define margins, orientation, paper size, and print area? 

A.        Page Layout

B.        Review

C.        View

D.        Home


 



9