Excel for Beginners- Week 2 & 3.pdf
Transcript
ONLINE INSTITUTE OF HIGHER LERANING EXCEL 2019 FOR BEGINNERS WEEK 2 This week you’ll learn: 1. Understanding the types of data, you can use 2. Entering text and values into your worksheets 3. Ent...
ONLINE INSTITUTE OF HIGHER LERANING EXCEL 2019 FOR BEGINNERS WEEK 2 This week you’ll learn: 1. Understanding the types of data, you can use 2. Entering text and values into your worksheets 3. Entering dates and times into your worksheets 4. Modifying and editing information 5. Using built-in and custom number formats 6. Understanding Excel worksheet basics 7. Controlling your views 8. Manipulating the rows and columns Exploring Data Types An Excel workbook file can hold any number of worksheets, and each worksheet is made up of more than 17 billion cells. A cell can hold any of four basic types of data. A numeric value Text A formula An error A worksheet can also hold charts, diagrams, pictures, buttons, and other objects. These objects aren’t contained in cells. Instead, they reside on the worksheet’s drawing layer, which is an invisible layer on top of each worksheet. Error values are discussed throughout Part II, “Working with Formulas and Functions.” Numeric values Numeric values represent a quantity of some type: sales amounts, number of employees, atomic weights, test scores, and so on. Values also can be dates (Feb 26, 2019) or times (such as 3:24 AM). Text entries Most worksheets also include text in some of the cells. Text can serve as data (for example, a list of employee names), labels for values, headings for columns, or instructions about the worksheet. Text is often used to clarify what the values in a worksheet mean or where the numbers came from. Text that begins with a number is still considered text. For example, if you type 12 Employees into a cell, Excel considers the entry to be text rather than a numeric value. Consequently, you can’t use this cell for numeric calculations. If you need to indicate that the number 12 refers to employees, enter 12 into a cell and then type Employees into the cell to the right. Formulas Formulas are what make a spreadsheet a spreadsheet. Excel enables you to enter flexible formulas that use the values (or even text) in cells to calculate a result. When you enter a formula into a cell, the formula’s result appears in the cell. If you change any of the cells used by a formula, the formula recalculates and shows the new result. Formulas can be simple mathematical expressions, or they can use some of the powerful functions that are built into Excel. Figure 2.1 shows an Excel worksheet set up to calculate a monthly loan payment. The worksheet contains values, text, and formulas. The cells in column A contain text. Column B contains four values and two formulas. The formulas are in cells B6 and B10. Column D, for reference, shows the actual contents of the cells in column B. Entering Text and Values into Your Worksheets If you’ve ever worked in a Windows application, you’ll find that entering data into worksheet cells is simple and intuitive. And while there are differences in how Excel stores and displays the different data types, for the most part it just works. Entering numbers To enter a numeric value into a cell, select the appropriate cell, type the value, and then press Enter, Tab, or one of the arrow navigation keys. The value is displayed in the cell and appears in the Formula bar when the cell is selected. You can include decimal points and currency symbols when entering values, along with plus signs, minus signs, percent signs, and commas (to separate thousands). If you precede a value with a minus sign or enclose it in parentheses, Excel considers it to be a negative number. Entering text Entering text into a cell is just as easy as entering a value: activate the cell, type the text, and then press Enter or a navigation key. A cell can contain a maximum of about 32,000 characters—more than enough to store a typical chapter in this book. Even though a cell can hold a huge number of characters, you’ll find that it’s not actually possible to display all of these characters. What happens when you enter text that’s longer than its column’s current width? If the cells to the immediate right are blank, Excel displays the text in its entirety, appearing to spill the entry into adjacent cells. If an adjacent cell isn’t blank, Excel displays as much of the text as possible. (The full text is contained in the cell; it’s just not displayed.) If you need to display a long text string in a cell that’s adjacent to a nonblank cell, you have a few choices. Edit your text to make it shorter. Increase the width of the column (drag the border in the column letter display). Use a smaller font. Wrap the text within the cell so that it occupies more than one line. Choose Home ➪ Alignment ➪ Wrap Text to toggle wrapping on and off for the selected cell or range. Using Enter mode The left side of Excel’s status bar normally displays “Ready,” indicating that Excel is ready for you to enter or edit the worksheet. If you start typing numbers or text in a cell, the status bar changes to display “Enter” to indicate you’re in Enter mode. The most common modes for Excel to be in are Ready, Enter, and Edit. See “Modifying Cell Contents” later in this chapter for more information about Edit mode. In Enter mode, you are actively entering something into a cell. As you type, the text shows in the cell and in the Formula bar. You haven’t actually changed the contents of the cell until you leave Enter mode, which commits the value to the cell. To leave Enter mode, you can press Enter, Tab, or just about any navigation key on your keyboard (like PageUp or Home). The value you typed is committed to the cell, and the status bar changes back to say “Ready.” You can also leave Enter mode by pressing the Esc key. Pressing Esc ignores your changes and returns the cell to its previous value. Entering Dates and Times into Your Worksheets Excel treats dates and times as special types of numeric values. Dates and times are values that are formatted so that they appear as dates or times. If you work with dates and times, you need to understand Excel’s date and time system. Entering date values Excel handles dates by using a serial number system. The earliest date that Excel understands is January 1, 1900. This date has a serial number of 1. January 2, 1900, has a serial number of 2, and so on. This system makes it easy to deal with dates in formulas. For example, you can enter a formula to calculate the number of days between two dates. Most of the time, you don’t have to be concerned with Excel’s serial number date system. You can simply enter a date in a common date format, and Excel takes care of the details behind the scenes. For example, if you need to enter June 1, 2019, you can enter the date by typing June 1, 2019 (or use any of several different date formats). Excel interprets your entry and stores the value 43617, which is the serial number for that date. Entering time values When you work with times, you extend Excel’s date serial number system to include decimals. In other words, Excel works with times by using fractional days. For example, the date serial number for June 1, 2019, is 43617. Noon on June 1, 2019 (halfway through the day), is represented internally as 43617.5 because the time fraction is added to the date serial number to get the full date/time serial number. Again, you normally don’t have to be concerned with these serial numbers or fractional serial numbers for times. Just enter the time into a cell in a recognized format. In this case, type June 1, 2019 12:00. Modifying Cell Contents After you enter a value or text into a cell, you can modify it in several ways. Delete the cell’s contents. Replace the cell’s contents with something else. Edit the cell’s contents. Deleting the contents of a cell To delete the contents of a cell, just click the cell and press the Delete key. To delete more than one cell, select all of the cells that you want to delete and then press Delete. Pressing Delete removes the cell’s contents but doesn’t remove any formatting (such as bold, italic, or a different number format) that you may have applied to the cell. For more control over what gets deleted, you can choose Home ➪ Editing ➪ Clear. This command’s drop-down list has six choices. Clear All Clears everything from the cell—its contents, formatting, and cell comment (if it has one). Clear Formats Clears only the formatting and leaves the value, text, or formula. Clear Contents Clears only the cell’s contents and leaves the formatting. This has the same effect as pressing Delete. Clear Comments Clears the comment (if one exists) attached to the cell. Clear Hyperlinks Removes hyperlinks contained in the selected cells. The text and formatting remain, so the cell still looks like it has a hyperlink, but it no longer functions as a hyperlink. Remove Hyperlinks Removes hyperlinks in the selected cells, including the cell formatting. Replacing the contents of a cell To replace the contents of a cell with something else, just activate the cell and type your new entry, which replaces the previous contents. Any formatting applied to the cell remains in place and is applied to the new content. You can also replace cell contents by dragging and dropping or by copying and pasting data from another cell. In both cases, the cell formatting will be replaced by the format of the new data. To avoid pasting formatting, choose Home ➪ Clipboard ➪ Paste ➪ Values (V), or Home ➪ Clipboard ➪ Paste ➪ Formulas (F). Editing the contents of a cell If the cell contains only a few characters, replacing its contents by typing new data usually is easiest. However, if the cell contains lengthy text or a complex formula and you need to make only a slight modification, you probably want to edit the cell rather than re-enter information. When you want to edit the contents of a cell, you can use one of the following ways to enter Edit mode: Double-click the cell to edit the cell contents directly in the cell. Select the cell and press F2 to edit the cell contents directly in the cell. Select the cell that you want to edit and then click inside the Formula bar to edit the cell contents in the Formula bar. You can use whichever method you prefer. Some people find editing directly in the cell easier; others prefer to use the Formula bar to edit a cell. All of these methods cause Excel to go into Edit mode. (The word Edit appears at the left side of the status bar at the bottom of the window.) When Excel is in Edit mode, the Formula bar enables two icons: Cancel (the X) and Enter (the check mark). The figure below shows these two icons. Clicking the Cancel icon cancels editing without changing the cell’s contents. (Pressing Esc has the same effect.) Clicking the Enter icon completes the editing and enters the modified contents into the cell. (Pressing Enter has the same effect, except that clicking the Enter icon doesn’t change the active cell.) When you begin editing a cell, the insertion point appears as a vertical bar, and you can perform the following tasks: Add new characters at the location of the insertion point. Move the insertion point by doing one of the following: o Using the navigation keys to move within the cell o Pressing Home to move the insertion point to the beginning of the cell o Pressing End to move the insertion point to the end of the cell o Select multiple characters. Press Shift while you use the navigation keys. o Select characters while you’re editing a cell. Use the mouse. Just click and drag the mouse pointer over the characters that you want to select. o Delete a character to the left of the insertion point. The Backspace key deletes the selected text or the character to the left of the insertion point if no characters are selected. o Delete a character to the right of the insertion point. The Delete key also deletes the selected text. If no text is selected, it deletes the character to the right of the insertion point. Learning some handy data-entry techniques You can simplify the process of entering information into your Excel worksheets and make your work go quite a bit faster by using a number of useful tricks, which are described in the following sections. Automatically moving the selection after entering data By default, Excel automatically selects the next cell down when you press the Enter key after entering data into a cell. To change this setting, choose File ➪ Options and click the Advanced. The check box that controls this behavior is labeled After pressing Enter, move selection. If you enable this option, you can choose the direction in which the selection moves (down, left, up, or right). Selecting a range of input cells before entering data When a range of cells is selected, Excel automatically selects the next cell in the range when you press Enter, even if you disabled the After pressing Enter, move selection option. If the selection consists of multiple rows, Excel moves down the column; when it reaches the end of the selection in the column, it moves to the first selected cell in the next column. To skip a cell, just press Enter without entering anything. To go backward, press Shift+Enter. If you prefer to enter the data by rows rather than by columns, press Tab rather than Enter. Excel continues to cycle through the selected range until you select a cell outside the range. Any of the navigation keys, like the arrow keys or the Home key, will change the selected range. If you want to navigate within the selected range, you have to stick to Enter and Tab. Using Ctrl+Enter to place information into multiple cells simultaneously If you need to enter the same data into multiple cells, Excel offers a handy shortcut. Select all of the cells that you want to contain the data; enter the value, text, or formula; and then press Ctrl+Enter. The same information is inserted into each cell in the selection. Changing modes You can press F2 to change between Enter mode and Edit mode. For example, if you’re typing a long sentence in Enter mode and you realize that you spelled a word wrong, you can press F2 to change to Edit mode. In Edit mode, you can move through the sentence with your arrow keys to fix the misspelled word. You can also use the Ctrl+arrow keys to move one word at a time instead of one character at a time. You can continue to enter text in Edit mode or return to Enter mode by pressing F2 again, after which the navigation keys can be used to move to a different cell. Entering decimal points automatically If you need to enter lots of numbers with a fixed number of decimal places, Excel has a useful tool that works like some old adding machines. Access the Excel Options dialog box and click the Advanced tab. Select the Automatically Insert a Decimal Point check box and make sure that the Places box is set for the correct number of decimal places for the data you need to enter. When this option is set, Excel supplies the decimal points for you automatically. For example, if you specify two decimal places, entering 12345 into a cell is interpreted as 123.45. To restore things to normal, just clear the Automatically Insert a Decimal Point check box in the Excel Options dialog box. Changing this setting doesn’t affect any values that you already entered. Using AutoFill to enter a series of values The Excel AutoFill feature makes inserting a series of values or text items in a range of cells easy. It uses the fill handle (the small box at the lower right of the active cell). You can drag the fill handle to copy the cell or automatically complete a series. Enter 1 into cell A1, and enter 3 into cell A2. Then select both cells and drag down the fill handle to create a linear series of odd numbers. The figure also shows an icon that, when clicked, displays some additional AutoFill options. This icon appears only if the Show Paste Options button when content is pasted option is selected in the Advanced tab of the Excel Options dialog box. Excel uses the cells’ data to guess the pattern. If you start with 1 and 2, it will guess you want each cell to go up by 1. If, as in the previous example, you start with 1 and 3, it guesses that you want the increment to be 2. Excel does a good job of guessing date patterns too. If you start with 1/31/2019 and 2/28/2019, it will fill the last day of the successive months. Using AutoComplete to automate data entry The Excel AutoComplete feature makes entering the same text into multiple cells easy. With AutoComplete, you type the first few letters of a text entry into a cell, and Excel automatically completes the entry based on other entries that you already made in the column. Besides reducing typing, this feature ensures that your entries are spelled correctly and are consistent. Here’s how it works: Suppose you’re entering product information into a column. One of your products is named Widgets. The first time you enter Widgets into a cell, Excel remembers it. Later, when you start typing Widgets in that same column, Excel recognizes it by the first few letters and finishes typing it for you. Just press Enter, and you’re done. To override the suggestion, just keep typing. AutoComplete also changes the case of letters for you automatically. If you start entering widgets (with a lowercase w) in the second entry, Excel makes the w uppercase to be consistent with the previous entry in the column. Keep in mind that AutoComplete works only within a contiguous column of cells. If you have a blank row, for example, AutoComplete identifies only the cell contents below the blank row. Sometimes, Excel will use AutoComplete to try to finish a word when you don’t want it to do so. If you type canister in a cell and then below it type the shorter word can, Excel will attempt to AutoComplete the entry to canister. When you want to type a word that starts with the same letters as an AutoComplete entry but is shorter, simply press the Delete key when you’ve reached the end of the word and then press Enter or a navigation key. If you find the AutoComplete feature distracting, you can turn it off by using the Advanced tab of the Excel Options dialog box. Remove the check mark from the Enable AutoComplete for Cell Values box. Forcing text to appear on a new line within a cell If you have lengthy text in a cell, you can force Excel to display it in multiple lines within the cell: press Alt+Enter to start a new line in a cell. When you add a line break, Excel automatically changes the cell’s format to Wrap Text. But unlike normal text wrap, your manual line break forces Excel to break the text at a specific place within the text, which gives you more precise control over the appearance of the text than if you rely on automatic text wrapping. Using AutoCorrect for shorthand data entry You can use the AutoCorrect feature to create shortcuts for commonly used words or phrases. For example, if you work for a company named Consolidated Data Processing Corporation, you can create an AutoCorrect entry for an abbreviation, such as cdp. Then, whenever you type cdp and take an action to trigger AutoCorrect (such as typing a space, pressing Enter, or selecting a different cell), Excel automatically changes the text to Consolidated Data Processing Corporation. Excel includes quite a few built-in AutoCorrect terms (mostly to correct common misspellings), and you can add your own. To set up your custom AutoCorrect entries, access the Excel Options dialog box (choose File ➪ Options) and click the Proofing tab. Then click the AutoCorrect Options button to display the AutoCorrect dialog box. In the dialog box, click the AutoCorrect tab, check the Replace Text as You Type option, and then enter your custom entries. You can set up as many custom entries as you like. Just be careful not to use an abbreviation that might appear normally in your text. Entering numbers with fractions Most of the time, you’ll want non integer values to be displayed with decimal points. But Excel can also display values with fractions. To enter a fractional value into a cell, leave a space between the whole number and the fraction. For example, to enter 6 7/8, enter 6 7/8 and then press Enter. When you select the cell, 6.875 appears in the Formula bar, and the cell entry appears as a fraction. If you have a fraction only (for example, 1/8), you must enter a zero first, like this—0 1/8—or Excel will likely assume that you’re entering a date. When you select the cell and look at the Formula bar, you see 0.125. In the cell, you see 1/8. Using a form for data entry Many people use Excel to manage lists in which the information is arranged in rows. Excel offers a simple way to work with this type of data through the use of a data entry form that Excel can create automatically. This data form works with either a normal range of data or a range that has been designated as a table. (Choose Insert ➪ Tables ➪ Table.) The figure below shows an example. Unfortunately, the command to access the data form is not on the Ribbon. To use the data form, you must add it to your Quick Access toolbar or add it to the Ribbon. Here’s how to add this command to your Quick Access toolbar: 1. Right-click the Quick Access toolbar and choose Customize Quick Access Toolbar. The Quick Access Toolbar panel of the Excel Options dialog box appears. 2. In the Choose Commands From drop-down list, choose Commands Not in the Ribbon. 3. In the list box on the left, select Form. 4. Click the Add button to add the selected command to your Quick Access toolbar. 5. Click OK to close the Excel Options dialog box. After you perform these steps, a new icon appears on your Quick Access toolbar. To use a data entry form, follow these steps: 1. Arrange your data so that Excel can recognize it as a table by entering headings for the columns into the first row of your data entry range. 2. Select any cell in the table, and click the Form button on your Quick Access toolbar. Excel displays a dialog box customized to your data (refer to Figure 2.7). 3. Fill in the information. Press Tab to move between the text boxes. If a cell contains a formula, the formula result appears as text (not as an edit box). In other words, you can’t modify formulas using the data entry form. 4. When you complete the data form, click the New button. Excel enters the data into a row in the worksheet and clears the dialog box for the next row of data. You can also use the form to edit existing data. Entering the current date or time into a cell If you need to date-stamp or time-stamp your worksheet, Excel provides two shortcut keys that do this task for you: Current date: Ctrl+; (semicolon) Current time: Ctrl+Shift+; (semicolon) To enter both the date and time, press Ctrl+;, type a space, and then press Ctrl+Shift+;. The date and time are from the system time in your computer. If the date or time isn’t correct in Excel, use the Windows Settings to make the adjustment. When you use either of these shortcuts to enter a date or time into your worksheet, Excel enters a static value into the worksheet. In other words, the date or time entered doesn’t change when the worksheet is recalculated. In most cases, this setup is probably what you want, but you should be aware of this limitation. If you want the date or time display to update, use one of these formulas: =TODAY() =NOW() Applying Number Formatting Applying number formatting changes the appearance of values contained in cells. Excel provides a variety of number formatting options. In the following sections, you will see how to use many of Excel’s formatting options to improve the appearance and readability of your worksheets quickly. Values that you enter into cells normally are unformatted. In other words, they simply consist of a string of numerals. Typically, you want to format the numbers so that they’re easier to read or are more consistent in terms of the number of decimal places shown. The figure shows a worksheet that has two columns of values. The first column consists of unformatted values. The cells in the second column are formatted to make the values easier to read. The third column describes the type of formatting applied. Using automatic number formatting Excel is able to perform some formatting for you automatically. For example, if you enter 12.2% into a cell, Excel knows that you want to use a percentage format and applies it for you automatically. If you use commas to separate thousands (such as 123,456), Excel applies comma formatting for you. And if you precede your value with a dollar sign, the cell is formatted for currency (assuming that the dollar sign is your system currency symbol). Anything you enter that can possibly be construed as a date will be treated as such. And depending on how you enter it, Excel will choose a date format to match. If you enter 1/31/2020, Excel will interpret that as a date and format the cell as 1/31/2020 (just as it was entered). If you enter Jan 31, 2020, Excel will the format it as 31-Jan-20 (if you omit the comma, Excel won’t recognize it as a date). The less obvious example of entering 1- 31 causes Excel to display 31-Jan. If you need to enter 1-31 in a cell and it’s not supposed to be a date, type an apostrophe (‘) first. Formatting numbers by using the Ribbon The Home ➪ Number group in the Ribbon contains controls that let you quickly apply common number formats. The Number Format drop-down list contains 11 common number formats (see Figure below). Additional options in the Home ➪ Number group include an Accounting Number Format drop-down list (to select a currency format), a Percent Style button, and a Comma Style button. The group also contains a button to increase the number of decimal places and another to decrease the number of decimal places. When you select one of these controls, the active cell takes on the specified number format. You also can select a range of cells (or even entire rows or columns) before clicking these buttons. If you select more than one cell, Excel applies the number format to all of the selected cells. Using shortcut keys to format numbers Another way to apply number formatting is to use shortcut keys. The table below summarizes the shortcut-key combinations that you can use to apply common number formatting to the selected cells or range. Notice that these Ctrl+Shift characters are located together, in the lower left of your keyboard. Key Combination Formatting Applied Ctrl+Shift+~ General number format (that is, unformatted values) Ctrl+Shift+$ Currency format with two decimal places (negative numbers appear in red and inside parentheses) Ctrl+Shift+% Percentage format, with no decimal places Ctrl+Shift+^ Scientific notation number format, with two decimal places Ctrl+Shift+# Date format with the day, month, and year Ctrl+Shift+@ Time format with the hour, minute, and AM or PM Ctrl+Shift+! Two decimal places, thousands separator, and a hyphen for negative values Formatting numbers by using the Format Cells dialog box In most cases, the number formats that are accessible from the Number group on the Home tab are just fine. Sometimes, however, you want more control over how your values appear. Excel offers a great deal of control over number formats through the use of the Format Cells dialog box, as shown in the figure below. For formatting numbers, you need to use the Number tab. You can bring up the Format Cells dialog box in several ways. Start by selecting the cell or cells that you want to format and then do one of the following: Choose Home ➪ Number and click the small dialog box launcher icon (in the lower-right corner of the Number group). Choose Home ➪ Number, click the Number Format drop-down list, and choose More Number Formats from the drop-down list. Right-click the cell, and choose Format Cells from the shortcut menu. Press Ctrl+1. The Number tab of the Format Cells dialog box displays 12 categories of number formats. When you select a category from the list box, the right side of the tab changes to display options appropriate to that category. The Number category has three options that you can control: the number of decimal places displayed, whether to use a thousands separator, and how you want negative numbers displayed. The Negative Numbers list box has four choices (two of which display negative values in red), and the choices change depending on the number of decimal places and whether you choose to separate thousands. The top of the tab displays a sample of how the active cell will appear with the selected number format (visible only if a cell with a value is selected). After you make your choices, click OK to apply the number format to all of the selected cells. The following are the number format categories, along with some general comments: General The default format; it displays numbers as integers, as decimals, or in scientific notation if the value is too wide to fit in the cell. Number Enables you to specify the number of decimal places, whether to use a comma to separate thousands, and how to display negative numbers (with a minus sign, in red, in parentheses, or in red and in parentheses). Currency Enables you to specify the number of decimal places, choose a currency symbol, and specify how to display negative numbers (with a minus sign, in red, in parentheses, or in red and in parentheses). This format always uses a comma to separate thousands. Accounting Differs from the Currency format in that the currency symbols always align vertically. Date Enables you to choose from several different date formats. Time Enables you to choose from several different time formats. Percentage Enables you to choose the number of decimal places and always displays a percent sign. Fraction Enables you to choose from among nine fraction formats. Scientific Displays numbers in exponential notation (with an E): 2.00E+05 = 200,000; 2.05E+05 = 205,000. You can choose the number of decimal places to display to the left of E. The second example can be read as “2.05 times 10 to the fifth.” Text When applied to a value, causes Excel to treat the value as text (even if it looks like a number). This feature is useful for such items as part numbers and credit card numbers. Special Contains additional number formats. In the U.S. version of Excel, the additional number formats are Zip Code, Zip Code +4, Phone Number, and Social Security Number. Custom Enables you to define custom number formats that aren’t included in any other category. NOTE: If a cell displays a series of hash marks (such as #########), it usually means that the column isn’t wide enough to display the value in the number format that you selected. Either make the column wider or change the number format. Hashmarks also indicate a negative time value or an invalid date (that is, a date prior to January 1, 1900). Adding your own custom number formats Sometimes you may want to display numerical values in a format that isn’t included in any of the other categories. If so, the answer is to create your own custom format. Basic custom number formats contain four sections separated by semicolons. Those four sections determine how a number will be formatted if it is a positive value, negative value, a zero, or text. Learning the Fundamentals of Excel Worksheets In Excel, each file is called a workbook, and each workbook can contain one or more worksheets. You may find it helpful to think of an Excel workbook as a binder and worksheets as pages in the binder. As with a binder, you can view a particular sheet, add new sheets, remove sheets, rearrange sheets, and copy sheets. A workbook can hold any number of sheets, and these sheets can be either worksheets (sheets consisting of rows and columns) or chart sheets (sheets that hold a single chart). A worksheet is what people usually think of when they think of a spreadsheet. The following sections describe the operations that you can perform with windows and worksheets. Working with Excel windows Each Excel workbook file that you open is displayed in a window. A window is the operating system’s container for that workbook. You can open as many Excel workbooks as necessary at the same time. Each Excel window has five icons at the right side of its title bar. From left to right, they are Account, Ribbon Display Options, Minimize, Maximize (or Restore Down), and Close. An Excel window can be in one of the following states: Maximized Fills the entire screen. To maximize a window, click its Maximize button. Minimized Hidden but still open. To minimize a window, clicks its Minimize button. Restored Visible but smaller than the whole screen. To restore a maximized window, click its Restore Down button. To restore a minimized window, click its icon in the Windows taskbar. A window in this state can be resized and moved. If you work with more than one workbook simultaneously (which is quite common), you need to know how to move, resize, close, and switch among the workbook windows. Moving and resizing windows To move a window, click and drag its title bar with your mouse. If it’s maximized, it will change to a restored state. If it’s already in a restored state, it will maintain its current size. To resize a window, click and drag any of its borders until it’s the size that you want it to be. When you position the mouse pointer on a window’s border, the mouse pointer changes to a double arrow, which lets you know that you can now click and drag to resize the window. To resize a window horizontally and vertically at the same time, click and drag any of its corners. If you want all of your workbook windows to be visible (that is, not obscured by another window), you can move and resize the windows manually, or you can let Excel do it for you. Choosing View ➪ Window ➪ Arrange All displays the Arrange Windows dialog box, as shown in the figure below. This dialog box has four window arrangement options. Just select the one that you want and click OK. Windows that are minimized aren’t affected by this command. Switching among windows At any given time, one (and only one) workbook window is the active window. The active window accepts your input, and it is the window on which your commands work. The active window appears at the top of the stack of windows. To work in a workbook in a different window, you need to make that window active. You can make a different window the active window in several ways. Click another window if it’s visible. The window you click moves to the top and becomes the active window. This method isn’t possible if the current window is maximized. Press Ctrl+Tab to cycle through all open windows until the window that you want to work with appears on top as the active window. Pressing Shift+Ctrl+Tab cycles through the windows in the opposite direction. Choose View ➪ Window ➪ Switch Windows, and select the window that you want from the drop-down list (the active window has a check mark next to it). This menu can display as many as nine windows. If you have more than nine workbook windows open, choose More Windows (which appears below the nine window names). Click the corresponding Excel icon in the Windows taskbar. You might be one of the many people who prefer to do most work with maximized workbook windows, which enables you to see more cells and eliminates the distraction of other workbook windows getting in the way. At times, however, viewing multiple windows is preferred. For example, displaying two windows is more efficient if you need to compare information in two workbooks or if you need to copy data from one workbook to another. Closing windows If you have multiple windows open, you may want to close those windows that you no longer need. Excel offers several ways to close the active window. Choose File ➪ Close. Click the Close button (the X icon) on the right side of the workbook window’s title bar. Press Alt+F4. Press Ctrl+W. When you close a workbook window, Excel checks whether you have made any changes since the last time you saved the file. If you have made changes, Excel prompts you to save the file before it closes the window. If you haven’t, the window closes without a prompt from Excel. Sometimes you will be prompted to save a workbook even if you’ve made no changes to it. This occurs if your workbook contains any volatile functions. Volatile functions recalculate every time the workbook recalculates. For example, if a cell contains =NOW(), you will be prompted to save the workbook because the NOW function updated the cell with the current date and time. Activating a worksheet At any given time, one workbook is the active workbook, and one sheet is the active sheet in the active workbook. To activate a different sheet, just click its sheet tab, which is located at the bottom of the workbook window. You also can use the following shortcut keys to activate a different sheet: Ctrl+PgUp activates the previous sheet, if one exists. Ctrl+PgDn activates the next sheet, if one exists. If your workbook has many sheets, all of its tabs may not be visible. Use the tab scrolling controls (see Figure below) to scroll the sheet tabs. Clicking the scrolling controls scrolls one tab at a time, and Ctrl+clicking scrolls to the first or last sheet. The sheet tabs share space with the worksheet’s horizontal scrollbar. You also can drag the tab split control (to the left of the horizontal scrollbar) to display more or fewer tabs. Dragging the tab split control simultaneously changes the number of visible tabs and the size of the horizontal scrollbar. Adding a new worksheet to your workbook Worksheets can be an excellent organizational tool. Instead of placing everything on a single worksheet, you can use additional worksheets in a workbook to separate various workbook elements logically. For example, if you have several products whose sales you track individually, you may want to assign each product to its own worksheet and then use another worksheet to consolidate your results. Here are four ways to add a new worksheet to a workbook: Click the New Sheet control, which is the plus sign icon located to the right of the last visible sheet tab. A new sheet is added after the active sheet. Press Shift+F11. A new sheet is added before the active sheet. From the Ribbon, choose Home ➪ Cells ➪ Insert ➪ Insert Sheet. A new sheet is added before the active sheet. Right-click a sheet tab, choose Insert from the shortcut menu, and select the General tab of the Insert dialog box that appears. Then select the Worksheet icon and click OK. A new sheet is added before the active sheet. Deleting a worksheet you no longer need If you no longer need a worksheet or if you want to get rid of an empty worksheet in a workbook, you can delete it in either of two ways. Right-click its sheet tab and choose Delete from the shortcut menu. Activate the unwanted worksheet and choose Home ➪ Cells ➪ Delete ➪ Delete Sheet. If the worksheet is not empty, Excel asks you to confirm that you want to delete the sheet. Changing the name of a worksheet The default names that Excel uses for worksheets—Sheet1, Sheet2, and so on—are generic and nondescriptive. To make it easier to locate data in a multisheet workbook, you’ll want to make the sheet names more descriptive. These are three ways to change a sheet’s name: From the Ribbon, choose Home ➪ Cells ➪ Format ➪ Rename Sheet. Double-click the sheet tab. Right-click the sheet tab and choose Rename Sheet. Excel highlights the name on the sheet tab so that you can edit the name or replace it with a new name. While editing a sheet name, all of the normal text selection techniques work, such as Home, End, arrow keys, and Shift+arrow keys. Press Enter when you’re finished editing and the focus will be back on the active cell. Sheet names can contain as many as 31 characters, and spaces are allowed. However, you can’t use the following characters in sheet names: : Colon / Slash \ Backslash [] Square brackets ? Question mark * Asterisk Keep in mind that a longer worksheet name results in a wider tab, which takes up more space on-screen. Therefore, if you use lengthy sheet names, you won’t be able to see as many sheet tabs without scrolling the tab list. Changing a sheet tab color Excel allows you to change the background color of your worksheet tabs. For example, you may prefer to color-code the sheet tabs to make identifying the worksheet’s contents easier. To change the color of a sheet tab, choose Home ➪ Cells ➪ Format ➪ Tab Color, or right click the tab and choose Tab Color from the shortcut menu. Then select the color from the color palette. You can’t change the text color, but Excel will choose a contrasting color to make the text visible. For example, if you make a sheet tab black, Excel will display white text. If you change a sheet tab’s color, the tab shows a gradient from that color to white when the sheet is active. When a different sheet is active, the whole tab appears in the selected color. Rearranging your worksheets You may want to rearrange the order of worksheets in a workbook. If you have a separate worksheet for each sales region, for example, arranging the worksheets in alphabetical order might be helpful. You can also move a worksheet from one workbook to another and create copies of worksheets, either in the same workbook or in a different workbook. You can move a worksheet in the following ways: Right-click the sheet tab and choose Move or Copy to display the Move or Copy dialog box. Use this dialog box to specify the location for the sheet. From the Ribbon, choose Home ➪ Cells ➪ Format ➪ Move or Copy Sheet. This shows the same dialog box as the previous method. Click the worksheet tab and drag it to its desired location. When you drag, the mouse pointer changes to a small sheet icon, and a small arrow indicates where the sheet will be placed when you release the mouse button. To move a worksheet to a different workbook by dragging, both workbooks must be visible. Copying the worksheet is similar to moving it. If you use one of the options that shows the Move or Copy dialog box, select the Create a copy check box. To drag and create a copy, hold down the Ctrl key while you drag the worksheet tab. The mouse pointer will change to a small sheet icon with a plus sign on it. If you move or copy a worksheet to a workbook that already has a sheet with the same name, Excel changes the name to make it unique. For example, Sheet1 becomes Sheet1 (2). You probably want to rename the copied sheet to give it a more meaningful name. Hiding and unhiding a worksheet In some situations, you may want to hide one or more worksheets. Hiding a sheet may be useful if you don’t want others to see it or if you just want to get it out of the way. When a sheet is hidden, its sheet tab is also hidden. You can’t hide all of the sheets in a workbook; at least one sheet must remain visible. To hide a worksheet, choose Home ➪ Cells ➪ Format ➪ Hide & Unhide ➪ Hide Sheet, or right-click its sheet tab and choose Hide. The active worksheet (or selected worksheets) will be hidden from view. To unhide a hidden worksheet, choose Home ➪ Cells ➪ Format ➪ Hide & Unhide ➪ Unhide Sheet, or right-click any sheet tab and choose Unhide. Excel opens the Unhide dialog box, which lists all hidden sheets. Choose the sheet that you want to redisplay, and click OK. You can’t select multiple sheets from this dialog box, so you need to repeat the command for each sheet that you want to unhide. When you unhide a sheet, it appears in its previous position among the sheet tabs. Preventing Sheet Actions To prevent others from unhiding hidden sheets, inserting new sheets, renaming sheets, copying sheets, or deleting sheets, protect the workbook’s structure. 1. Choose Review ➪ Protect ➪ Protect Workbook. 2. In the Protect Structure and Windows dialog box, select the Structure option. 3. Provide a password (optional) and click OK. After performing these steps, several commands will no longer be available from the Ribbon or when you right-click a sheet tab: Insert, Delete Sheet, Rename Sheet, Move or Copy Sheet, Tab Color, Hide Sheet, and Unhide Sheet. Be aware, however, that this is a weak security measure. Cracking this particular protection feature is relatively easy. Controlling the Worksheet View As you add more information to a worksheet, you may find that navigating and locating what you want becomes more difficult. Excel includes a few options that enable you to view your sheet, and sometimes multiple sheets, more efficiently. This section discusses a few additional worksheet options at your disposal. Zooming in or out for a better view Normally, everything you see onscreen is displayed at 100%. You can change the zoom percentage from 10% (very tiny) to 400% (huge). Using a small zoom percentage can help you get a bird’s-eye view of your worksheet to see how it’s laid out. Zooming in is useful if you have trouble deciphering tiny type. Zooming doesn’t change the font size specified for the cells, so it has no effect on printed output. You can change the zoom factor of the active worksheet window by using any of these three methods: Use the Zoom slider located on the right side of the status bar. Click and drag the slider, and your screen transforms instantly. Press Ctrl and use the wheel button on your mouse to zoom in or out. Choose View ➪ Zoom ➪ Zoom, which displays a dialog box with some zoom options. Also in the Zoom Ribbon group is a 100% button to return to 100% zoom quickly and a Zoom to Selection button to change the zoom so that whatever cells you have selected take up the whole window (but still limited to the 10–400% zoom range). Zooming affects only the active worksheet window, so you can use different zoom factors for different worksheets. Viewing a worksheet in multiple windows Sometimes, you may want to view two different parts of a worksheet simultaneously— perhaps to make referencing a distant cell in a formula easier. Or you may want to examine more than one sheet in the same workbook simultaneously. You can accomplish either of these actions by opening a new view to the workbook, using one or more additional windows. To create and display a new view of the active workbook, choose View ➪ Window ➪ New Window. Excel displays a new window for the active workbook. In this case, each window shows a different worksheet in the workbook. Notice the text in the windows’ title bars: climate data.xlsx - 1 and climate data.xlsx -2. To help you keep track of the windows, Excel appends a hyphen and a number to each window. A single workbook can have as many views (that is, separate windows) as you want. Each window is independent. In other words, scrolling to a new location in one window doesn’t cause scrolling in the other window(s). However, if you make changes to the worksheet shown in a particular window, those changes are also made in all views of that worksheet. You can close these additional windows when you no longer need them. For example, clicking the Close button on the active window’s title bar closes the active window but doesn’t close the other windows for the workbook. If you have unsaved changes, Excel will prompt you to save only when you close the last window. Comparing sheets side by side In some situations, you may want to compare two worksheets that are in different windows. The View Side by Side feature makes this task a bit easier. First, make sure that the two sheets are displayed in separate windows. (The sheets can be in the same workbook or in different workbooks.) If you want to compare two sheets in the same workbook, choose View ➪ Window ➪ New Window to create a new window for the active workbook. Activate the first window; then choose View ➪ Window ➪ View Side by Side. If more than two windows are open, you see a dialog box that lets you select the window for the comparison. The two windows are tiled to fill the entire screen. When using the Compare Side by Side feature, scrolling in one of the windows also scrolls the other window. If you don’t want this simultaneous scrolling, choose View ➪ Window ➪Synchronous Scrolling (which is a toggle). If you have rearranged or moved the windows, choose View ➪ Window ➪ Reset Window Position to restore the windows to the initial side-by-side arrangement. To turn off the side-by-side viewing, choose View ➪ Window ➪ View Side by Side again. Keep in mind that this feature is for manual comparison only. Unfortunately, Excel doesn’t provide a way to identify the differences between two sheets automatically. Splitting the worksheet window into panes If you prefer not to clutter your screen with additional windows, Excel provides another option for viewing multiple parts of the same worksheet. Choosing View ➪ Window ➪ Split splits the active worksheet into two or four separate panes. The split occurs at the location of the active cell. If the active cell pointer is in row 1 or column A, this command results in a two-pane split; otherwise, it gives you four panes. You can use the mouse to drag the individual panes to resize them. Figure below shows a worksheet split into four panes. Notice that row numbers aren’t continuous. The top panes show rows 9 through 14, and the bottom panes show rows 107 through 121. In other words, splitting panes enables you to display in a single window widely separated areas of a worksheet. To remove the split panes, choose View ➪ Window ➪ Split again (or double-click the split bar). Keeping the titles in view by freezing panes If you set up a worksheet with column headings or descriptive text in the first column, this identifying information won’t be visible when you scroll down or to the right. Excel provides a handy solution to this problem: freezing panes. Freezing panes keeps the column or row headings visible while you’re scrolling through the worksheet. To freeze panes, start by moving the active cell to the cell below the row that you want to remain visible while you scroll vertically and to the right of the column that you want to remain visible while you scroll horizontally. Then choose View ➪ Window ➪ Freeze Panes and select the Freeze Panes option from the drop-down list. Excel inserts dark lines to indicate the frozen rows and columns. The frozen rows and columns remain visible while you scroll throughout the worksheet. To remove the frozen panes, choose View ➪ Window ➪ Freeze Panes, and select the Unfreeze Panes option from the drop- down list. Figure below shows a worksheet with frozen panes. In this case, rows 4:7 and column A are frozen in place. (Cell B8 was the active cell when the View ➪ Window ➪ Freeze Panes command was used.) This technique allows you to scroll down and to the right to locate some information while keeping the column titles and the column A entries visible. Most of the time you’ll want to freeze either the first row or the first column. The View ➪ Window ➪ Freeze Panes drop-down list has two additional options: Freeze Top Row and Freeze First Column. Using these commands eliminates the need to position the active cell before freezing panes. Monitoring cells with a Watch Window In some situations, you may want to monitor the value in a particular cell as you work. As you scroll throughout the worksheet, that cell may disappear from view. A feature known as a Watch Window can help. A Watch Window displays the value of any number of cells in a handy window that’s always visible. To display the Watch Window, choose Formulas ➪ Formula Auditing ➪ Watch Window. The Watch Window is actually a task pane, and you can dock it to the side of the window or drag it and make it float over the worksheet. To add a cell to watch, click Add Watch and specify the cell that you want to watch. The Watch Window displays the value in that cell. You can add any number of cells to the Watch Window. The figure below shows the Watch Window monitoring four cells in different worksheets. Working with Rows and Columns This section discusses worksheet operations that involve complete rows and columns (rather than individual cells). Every worksheet has exactly 1,048,576 rows and 16,384 columns, and these values can’t be changed. Inserting rows and columns Although the number of rows and columns in a worksheet is fixed, you can still insert and delete rows and columns if you need to make room for additional information. These operations don’t change the number of rows or columns. Instead, inserting a new row moves down the other rows to accommodate the new row. The last row is simply removed from the worksheet if it’s empty. Inserting a new column shifts the columns to the right, and the last column is removed if it’s empty. To insert a new row or rows, use either of these methods: Select an entire row or multiple rows by clicking the row numbers in the worksheet border. Right-click and choose Insert from the shortcut menu. Move the active cell to the row that you want to insert and then choose Home ➪ Cells ➪ Insert ➪ Insert Sheet Rows. If you select multiple cells in the column, Excel inserts additional rows that correspond to the number of cells selected in the column and moves the rows below the insertion down. To insert a new column or columns, use either of these methods: Select an entire column or columns by clicking the column letters in the worksheet border. Right-click and choose Insert from the shortcut menu. Move the active cell to the column that you want to insert and then choose Home ➪ Cells ➪ Insert ➪ Insert Sheet Columns. If you select multiple cells in the row, Excel inserts additional columns that correspond to the number of cells selected in the row. You can also insert cells rather than just rows or columns. Select the range into which you want to add new cells and then choose Home ➪ Cells ➪ Insert ➪ Insert Cells (or right-click the selection and choose Insert). To insert cells, you must shift the existing cells to the right or down. Therefore, Excel displays the Insert dialog box shown in the figure below so that you can specify the direction in which you want to shift the cells. Notice that this dialog box also enables you to insert entire rows or columns. You can insert partial rows or columns by using the Insert dialog box. Deleting rows and columns You may also want to delete rows or columns in a worksheet. For example, your sheet may contain old data that is no longer needed, or you may want to remove empty rows or columns. To delete a row or rows, use either of these methods: Select an entire row or multiple rows by clicking the row numbers in the worksheet border. Right-click and choose Delete from the shortcut menu. Move the active cell to the row that you want to delete and then choose Home ➪ Cells ➪ Delete ➪ Delete Sheet Rows. If you select multiple cells in the column, Excel deletes all rows in the selection. Deleting columns works in a similar way. If you discover that you accidentally deleted a row or column, select Undo from the Quick Access toolbar (or press Ctrl+Z) to undo the action. Changing column widths and row heights Often, you’ll want to change the width of a column or the height of a row. For example, you can make columns narrower to show more information on a printed page. Or you may want to increase row height to create a “double-spaced” effect. Excel provides several ways to change the widths of columns and the height of rows. Changing column widths Column width is measured in terms of the number of characters of a monospaced font that will fit into the cell’s width. By default, each column’s width is 8.43 units, which equates to64 pixels (px). Before you change the column width, you can select multiple columns so that the width will be the same for all selected columns. To select multiple columns, either click and drag in the column border or press Ctrl while you select individual columns. To select all columns, click the button where the row and column headers intersect. You can change column widths by using any of the following techniques: Drag the right-column border with the mouse until the column is the desired width. Choose Home ➪ Cells ➪ Format ➪ Column Width and enter a value in the Column Width dialog box. Choose Home ➪ Cells ➪ Format ➪ AutoFit Column Width to adjust the width of the selected column so that the widest entry in the column fits. Instead of selecting an entire column, you can just select cells in the column, and the column is adjusted based on the widest entry in your selection. Double-click the right border of a column header to set the column width automatically to the widest entry in the column. Changing row heights Row height is measured in points (a standard unit of measurement in the printing trade— 72 pts is equal to 1 inch). The default row height using the default font is 15 pts, or 20 pixels (px). The default row height can vary, depending on the font defined in the Normal style. In addition, Excel automatically adjusts row heights to accommodate the tallest font in the row. So, if you change the font size of a cell to 20 pts, for example, Excel makes the row taller so that the entire text is visible. You can set the row height manually, however, by using any of the following techniques. As with columns, you can select multiple rows: Drag the lower row border with the mouse until the row is the desired height. Choose Home ➪ Cells ➪ Format ➪ Row Height and enter a value (in points) in the Row Height dialog box. Double-click the bottom border of a row to set the row height automatically to the tallest entry in the row. You can also choose Home ➪ Cells ➪ Format ➪ AutoFit Row Height for this task. Changing the row height is useful for spacing out rows and is almost always preferable to inserting empty rows between lines of data. Hiding rows and columns In some cases, you may want to hide particular rows or columns. Hiding rows and columns may be useful if you don’t want users to see particular information or if you need to print a report that summarizes the information in the worksheet without showing all the details. To hide rows in your worksheet, select the row or rows that you want to hide by clicking in the row header on the left. Then right-click and choose Hide from the shortcut menu. Or, you can use the commands on the Home ➪ Cells ➪ Format ➪ Hide & Unhide menu. To hide columns, use the same technique, but start by selecting columns rather than rows. A hidden row is actually a row with its height set to zero. Similarly, a hidden column has a column width of zero. When you use the navigation keys to move the active cell, cells in hidden rows or columns are skipped. In other words, you can’t use the navigation keys to move to a cell in a hidden row or column. Notice, however, that Excel displays a narrow column heading for hidden columns and a narrow row heading for hidden rows. You can click and drag the column heading to make the column wider—and make it visible again. For a hidden row, click and drag the small row heading to make the row visible. Another way to unhide a row or column is to choose Home ➪ Editing ➪ Find & Select ➪ Go To (or use one of its two shortcut keys: F5 or Ctrl+G) to select a cell in a hidden row or column. For example, if column A is hidden, you can press F5 and go to cell A1 (or any other cell in column A) to move the active cell to the hidden column. Then you can choose Home ➪ Cells ➪ Format ➪ Hide & Unhide ➪ Unhide Columns.
