Looking for:
Excel Bible by John Walkenbach - Ebook | Scribd |
Excel Bible [PDF] [4iuvd65g0nf0]
Views 10, Downloads 5, File size 71MB. Aula For general microsott on our other products and services please contact our Customer Buble Department within the United States atoutside the United States at or fax Wiley publishes in a variety of print and 22016 formats and by print-on-demand. Some material included with standard print versions of this book may bble be included in e-books or in print-on-demand.
For more information about Wiley products, visit www. Microsoft and Excel are registered autodesk maya 2018 youtube free of Microsoft Corporation.
All other trademarks are жмите сюда property of their respective owners. He lives amid the saguaros, javelinas, rattlesnakes, bobcats, and Gila monsters in Southern Arizona, but the critters are mostly scared away by his clawhammer banjo playing. For more information, Google him. He started in data processing in in an insurance company. He ran into Visicalc in and has frer addicted to spreadsheets ever since.
SinceNiek has microosft self-employed—reviewing books, writing articles, and developing Excel-related software, such as a high-performance actuarial add-in. Your suggestions have helped make this edition the best one yet.
And a special thanks to behind-the-scenes folks who helped considerably: Karen Gill the copy как сообщается здесь who made it more readable microsoft excel 2016 bible free pdf free, Niek Otten the technical editor who made it more accurateand Adaobi Obi Tulton the project editor who made it all come together. The book contains everything that you need to know to learn the basics of Excel and microskft move on to more advanced topics at your own pace.
Is This Book for You? This book covers all bigle essential components of Excel привожу ссылку provides clear and practical examples that you can adapt to your own needs. My goal is to make that learning an enjoyable process. Software Versions This book was written for the desktop version of Excel for Windows.
The user interface changes introduced in Excel are so extensive that this book will be very confusing if you use an earlier version. Also, please note that this book is not applicable to Excel for Mac.
This book covers only the standard desktop version of Microsoft excel 2016 bible free pdf free In place of a menu system, Excel uses a context-sensitive Ribbon system.
The words along the top such as File, Insert, Page Layout, and so on are known as tabs. Click a tab, and the Ribbon displays the commands for the selected tab.
Each command has a name, which is usually displayed next to or below the icon. The commands are arranged in groups, and the group name appears at the bottom of the Ribbon. The convention I use is to indicate microsodt tab name, followed by microsoft excel 2016 bible free pdf free group name, followed by the command name. Lengthy input usually bigle on a separate line.
The right mouse button is used in Excel to pop up shortcut menus that are appropriate for whatever is currently selected. Dragging is often used жмите select a range of cells or to change the size of an object.
For Touchscreen Users Excel also works microsoft excel 2016 bible free pdf free touchscreen devices. If you happen to be using one mirosoft these devices, you probably already know the basic touch gestures. Quickly touching and releasing your finger on a button is the same as clicking it with a mouse. Touching twice in rapid succession is equivalent microslft double-clicking. Tap an item on the microsoft excel 2016 bible free pdf free menu to execute the command. Make sure you enable Приведенная ссылка mode from the Quick Access toolbar.
This command toggles between normal mode and Touch mode. How This Book Is Organized Notice that the book is divided into six main parts, followed by two appendixes. It starts with an /62379.txt to recording macros and VBA programming and then provides coverage of UserForms, events, and add-ins. The index is comprehensive, and each chapter typically focuses on a single нажмите чтобы увидеть больше topic.
Some readers, however, may ffee to follow the chapters in order. The URL is www. Please note that the URL is case sensitive, so use all lowercase letters. Other spreadsheet software is available, but Excel is by far the most popular and has been the world standard for many years. Keep in mind that this book deals only with the desktop version of Excel.
In the past, this tool excfl in the form of an add-in, and it worked only with the Pro versions of Excel. This feature, formerly known as Power Map, allows you mcirosoft create impressive data-driven maps. This is a rather specialized tool, and it is not covered in this book but you can see what is microsoft project professional 2016 used for free example in Chapter Excel is green. Understanding Workbooks and Worksheets You perform the work you do in Excel in a workbook.
You can have as many workbooks open as you need, and each one appears in its own window. By default, Excel workbooks use an. Beginning with Excelthat is no longer an option. Every workbook that you open has its own window.
Each cell can contain a value, a formula, or text. A worksheet also has an invisible draw layer, which holds charts, images, and diagrams. Each worksheet in жмите workbook is accessible by clicking the tab at the microsoft excel 2016 bible free pdf free of the workbook window.
In addition, a workbook can store chart sheets; a chart sheet displays a single chart and is accessible by pdd a tab. Figure 1. Collapse the Ribbon microsoft excel 2016 bible free pdf free Click this button to temporarily hide the Ribbon.
Click it again to make the Ribbon remain microsoft excel 2016 bible free pdf free. You can click a column pf to select an entire column of cells or drag a column border to change its width.
File button Click this button to open Backstage view, which contains one book capture pro free 12 options for working with your document including printing and setting Excel options. Tree bar When you fee information or formulas into a cell, it appears in this bar. Horizontal scrollbar Use this tool to scroll the sheet horizontally. The icon changes while your actions are being recorded.
Click again to stop recording. Name box This box displays the active cell address or the перейти на страницу of the selected cell, range, or object. New Sheet button Add a new worksheet by clicking the New Sheet button which is displayed after the last sheet tab. Page View buttons Click these buttons to change the way the worksheet is displayed.
/1876.txt Access toolbar This customizable toolbar holds commonly used commands. The Quick Access toolbar is always visible, regardless of which tab is selected. Ribbon This is the main location for Excel commands. Clicking an item in the tab list changes the Ribbon that is displayed. Tell me what you want to do Use this control to identify commands or have Excel issue a command automatically.
User name The name and associated image of the person logged in. Ribbon Display Options A drop-down control that offers three options related to microsoft office 2003 professional free the Ribbon. Row numbers Numbers range from 1 to 1, — one for each row in the worksheet. You can click a row number to select an entire row of cells. A workbook can have any number of sheets, and each sheet has its name displayed in a sheet tab. You imcrosoft also right-click to get a microsft of sheets.
It also shows pddf information about the range of cells selected. Right-click the status bar to change the information displayed. Tab list Use these commands to display a different Ribbon, similar to a menu.
Title bar This microsoft excel 2016 bible free pdf free the name of the program and the name of the current workbook. It also holds the Quick Pd toolbar on the left and some control buttons that you can use to modify the window on the right. Vertical scrollbar Use this to scroll the sheet vertically.
Window Close button Click this button to close the active workbook window. Window Minimize button Click this button to minimize the workbook window. The window displays as an icon in the Windows taskbar. Zoom control Use this to zoom your worksheet in and out. Every worksheet consists of rows numbered 1 through 1, and columns labeled A through Frse.
The intersection of a row and a column is a single fere, and each cell has a unique address made up of its mucrosoft letter and row number. For example, the address of the upper-left cell is A1. The boble of the cell at the lower right of a worksheet is XFD At any given time, one cell is the active 22016. The active cell is the cell that accepts keyboard input, and its contents can be edited.
Excel Books - PDF Drive
Introducing Excel ; Entering and editing worksheet data ; Essential worksheet operations ; Working with cells and ranges ; Introducing tables ; Worksheet formatting ; Understanding Excel files ; Using and creating templates ; Printing your work -- Working with formulas and functions. Introducing formulas and functions ; Creating formulas that manipulate text ; Working with dates and times ; Creating formulas that count and sum ; Creating formulas that look up values ; Creating formulas for financial applications ; Miscellaneous calculations ; Introducing array formulas ; Performing magic with array formulas -- Creating charts and graphics.
Getting started making charts ; Learning advanced charting ; Visualizing data using conditional formatting ; Creating sparkline graphics ; Enhancing your work with pictures and drawings -- Using advanced Excel features. Customizing the Excel user interface ; Using custom number formats ; Using data validation ; Creating and using worksheet outlines ; Linking and consolidating worksheets ; Excel and the internet ; Protecting your work ; Making your worksheets error free -- Analyzing data with Excel.
Introducing visual basic for applications ; Creating custom worksheet functions ; Creating userforms ; Using userform controls in a worksheet ; Working with Excel events ; VBA examples ; Creating custom Excel add-ins -- Appendixes.
Worksheet function reference ; Excel shortcut keys. There are no reviews yet. Be the first one to write a review. Books for People with Print Disabilities. Be aware, however, that this is a weak security measure. Cracking this particular protection feature is relatively easy. This section discusses a few additional worksheet options at your disposal. Zooming in is useful if you have trouble deciphering tiny type.
Excel contains separate options for changing the size of your printed output. Click and drag the slider, and your screen transforms instantly. Zooming affects only the active worksheet window, so you can use different zoom factors for different worksheets. Also, if you have a worksheet displayed in two different windows, you can set a different zoom factor for each of the windows. Viewing named ranges in this manner is useful for getting an overview of how a worksheet is laid out.
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. Excel displays a new window for the active workbook, similar to the one shown in Figure 3.
In this case, each window shows a different worksheet in the workbook. To help you keep track of the windows, Excel appends a colon and a number to each window. If you select the Windows of Active Workbook check box, only the windows of the active workbook are arranged. A single workbook can have as many views that is, separate windows as you want. Each window is independent.
However, if you make changes to the worksheet shown in a particular window, those changes are also made in all views of that worksheet. Multiple windows make copying or moving information from one worksheet to another easier. 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 more than two windows are open, you see a dialog box that lets you select the window for the comparison.
When using the Compare Side by Side feature, scrolling in one of the windows also scrolls the other window. Keep in mind that this feature is for manual comparison only.
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.
The split occurs at the location of the cell pointer. If the 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 3. The top pane shows rows 13 through 23, and the bottom pane shows rows through In other words, splitting panes enables you to display in a single window widely separated areas of a worksheet.
Excel provides a handy solution to this problem: freezing panes. To freeze panes, start by moving the cell pointer 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.
Excel inserts dark lines to indicate the frozen rows and columns. The frozen row and column remain visible while you scroll throughout the worksheet. In this case, rows and column A are frozen in place. 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.
Using these commands eliminates the need to position the cell pointer before freezing panes. When you scroll down, Excel displays the table column headings in place of the column letters. The table headings replace the column letters only when a cell within the table is selected. Monitoring cells with a Watch Window 3 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 Watch Window can help. 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. Double-click a cell in the Watch Window to immediately select that cell. This works only if the watched cell is in the active workbook. Working with Rows and Columns This section discusses worksheet operations that involve complete rows and columns rather than individual cells.
If you open a workbook that was created in a version of Excel prior to Excel , the workbook is opened in Compatibility Mode. These workbooks have 65, rows and columns.
If you would like to increase the number of rows and columns, save the workbook as an Excel. Instead, inserting a new row moves down the other rows to accommodate the new row. In either case, attempting to add a row or column displays the dialog box shown in Figure 3.
Right-click and choose Insert from the shortcut menu. 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.
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. To insert cells, you must shift the existing cells to the right or down. Therefore, Excel displays the Insert dialog box shown in Figure 3. Notice that this dialog box also enables you to insert entire rows or columns. 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. Right-click and choose Delete from the shortcut menu. If you select multiple cells in the column, Excel deletes all rows in the selection. Deleting columns works in a similar way.
For example, you can make columns narrower to show more information on a printed page. Excel provides several ways to change the widths of columns and the height of rows. Widen the column to solve the problem. 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.
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. This command displays a dialog box into which you enter the new default column width. If you enter a long number that displays as hash symbols , you need to change the column width manually. Changing row heights Row height is measured in points a standard unit of measurement in the printing trade — 72 pt is equal to 1 inch.
The default row height using the default font is 15 pt, or 20 px. 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 pt, for example, Excel makes the row taller so that the entire text is visible.
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.
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.
To hide columns, use the same technique, but start by selecting columns rather than rows. You must drag the border in the row or column heading. Drag the bottom border of a row upward or the right border of a column to the left. 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 cell pointer, cells in hidden rows or columns are skipped. 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 column visible. For example, if column A is hidden, you can press F5 and specify cell A1 or any other cell in column A to move the cell pointer to the hidden column.
Understanding how best to manipulate cells and ranges will save you time and effort. This chapter discusses a variety of techniques that are essential for Excel users.
Understanding Cells and Ranges A cell is a single element in a worksheet that can hold a value, some text, or a formula. For example, cell D9 is the cell in the fourth column and the ninth row.
A group of cells is called a range. You designate a range address by specifying its upper-left cell address and its lower-right cell address, separated by a colon. A1:B1 Two cells that occupy one row and two columns. A1:A cells in column A. A1:D4 16 cells four rows by four columns. C1:C An entire column of cells; this range also can be expressed as C:C.
A6:XFD6 An entire row of cells; this range also can be expressed as A1:XFD All cells in a worksheet. This range also can be expressed as either A:XFD or When you select a range, the cells appear highlighted. The exception is the active cell, which remains its normal color. Figure 4. Cell A5, the active cell, is selected but not highlighted. Then release the mouse button. If you drag to the end of the window, the worksheet will scroll.
Press F8 again to return the navigation keys to normal movement. When you finish making the selection, the Name box reverts to showing the address of the active cell.
Quick Analysis? When you select a range of data, Excel may display a Quick Analysis icon at the lower right of your selection. You can add conditional formatting, create a chart, add formulas, create a pivot table, and generate Sparkline graphics. The exact options vary, depending on the data in the range. For example, you may want to apply the same numeric format or the same alignment options to an entire row or column.
The column of the active cell or columns of the selected cells is highlighted. The row of the active cell or rows of the selected cells is highlighted. You can also click the area at the intersection of the row and column borders to select all cells. Selecting noncontiguous ranges Most of the time, the ranges that you select are contiguous — a single rectangle of cells.
Selecting noncontiguous ranges is also known as a multiple selection. If you want to apply the same formatting to cells in different areas of your worksheet, one approach is to make a multiple selection. When the appropriate cells or ranges are selected, the formatting that you select is applied to them all.
Three ranges are selected: A6:F6. AF14, and AF Then press and hold Ctrl as you click and drag the mouse to highlight additional cells or ranges. Separate each range address with a comma. Enter the range or cell address in the Reference box, and separate each range address with a comma. Click OK, and Excel selects the ranges. Selecting multisheet ranges In addition to two-dimensional ranges on a single worksheet, ranges can extend across multiple worksheets to be three-dimensional ranges.
Suppose that you have a workbook set up to track budgets. One approach is to use a separate worksheet for each department, making it easy to organize the data. You can click a sheet tab to view the information for a particular department. The workbook has four sheets: Totals, Operations, Marketing, and Manufacturing. The sheets are laid out identically. The only difference is the values. The Totals sheet contains formulas that compute the sum of the corresponding items in the three departmental worksheets.
This workbook, named budget. Assume that you want to apply formatting to the sheets — for example, make the column headings bold with background shading. A better technique is to select a multisheet range and format the cells in all the sheets simultaneously. The following is a step-by-step example of multisheet formatting using the workbook shown in Figure 4.
Activate the Totals worksheet by clicking its tab. Select the range B3:F3. Press Shift and click the Manufacturing sheet tab.
This step selects all worksheets between the active worksheet Totals and the sheet tab that you click — in essence, a three-dimensional range of cells see Figure 4.
Excel applies the formatting to the selected range across the selected sheets. Click one of the other sheet tabs. This step selects the sheet and cancels Group mode; [Group] is no longer displayed in the title bar.
When a workbook is in Group mode, any changes that you make to cells in one worksheet also apply to the corresponding cells in all the other grouped worksheets. You can use this to your advantage when you want to set up a group of identical worksheets because any labels, data, formatting, or formulas you enter are automatically added to the same cells in all the grouped worksheets.
In general, selecting a multisheet range is a simple two-step process: select the range in one sheet, and then select the worksheets to include in the range.
To select a group of contiguous worksheets, you can press Shift and click the sheet tab of the last worksheet that you want to include in the selection. To select individual worksheets, press Ctrl and click the sheet tab of each worksheet that you want to select.
If all the worksheets in a WOW! When you make the selection, the sheet tabs of the selected sheets display in bold with underlined text, and Excel displays [Group] in the title bar. To select all sheets in a workbook, right-click any sheet tab and choose Select All Sheets from the shortcut menu. After you make your choice in the dialog box, Excel selects the qualifying subset of cells in the current selection.
Often, this subset of cells is a multiple selection. If no cells qualify, Excel lets you know with the message No cells were found. If you bring up the Go to Special dialog box with only one cell selected, Excel bases its selection on the entire used area of the worksheet. Otherwise, the selection is based on the selected range. Some of the options are very useful. TABLE 4. Use the check boxes under the Formulas option to choose which types of nonformula cells to include. Formulas Selects cells that contain formulas.
Blanks Selects all empty cells. If a single cell is selected when the dialog box displays, this option selects the empty cells in the used area of the worksheet. Current Region Selects a rectangular range of cells around the active cell. This range is determined by surrounding blank rows and columns.
Current Array Selects the entire array. Objects Selects all embedded objects on the worksheet, including charts and graphics. Row Differences Analyzes the selection and selects cells that are different from other cells in each row. Column Differences Analyzes the selection and selects the cells that are different from other cells in each column. Precedents Selects cells that are referred to in the formulas in the active cell or selection limited to the active sheet.
You can select either direct precedents or precedents at any level. Dependents Selects cells with formulas that refer to the active cell or selection limited to the active sheet. You can select either direct dependents or dependents at any level. See Chapter 31 for more information. Last Cell Selects the bottom-right cell in the worksheet that contains data or formatting. For this option, the entire worksheet is examined, even if a range is selected when the dialog box displays.
Visible Cells Only Selects only visible cells in the selection. This option is useful when dealing with a filtered list or a table. The All option selects all such cells. The Same option 4 selects only the cells that have the same conditional formatting as the active cell. The Same option selects only the cells that have the same validation rules as the active cell. The placement of these suboptions can be misleading. For example, when you select Constants, the suboptions under Formulas become available to help you further refine the results.
Likewise, the suboptions under Dependents also apply to Precedents, and those under Data Validation also apply to Conditional Formats.
The Find and Replace dialog box is shown in Figure 4. The dialog box expands to display all the cells that match your search criteria. For example, Figure 4. You can click an item in the list, and the screen will scroll so that you can view the cell in context. The Find and Replace dialog box allows you to return to the worksheet without dismissing the dialog box.
The Find and Replace dialog box supports two wildcard characters:? Searching for 1? For example, searching for smith does not locate Smith. When using wildcard characters, an exact match is not required. Excel makes copying or moving ranges of cells easy. The source cell is copied to every cell in the destination range. The primary difference between copying and moving a range is the effect of the operation on the source range.
When you copy a range, the source range is unaffected. When you move a range, the contents are removed from the source range. When you copy a cell that contains a formula, the cell references in the copied formulas are changed automatically to be relative to their new destination.
Select the cell or range to copy the source range , and copy it to the Clipboard. To move the range instead of copying it, cut the range instead of copying it. Move the cell pointer to the range that will hold the copy the destination range , and paste the Clipboard contents. When you paste information, Excel overwrites any cells that get in the way without warning you. When you copy a cell or range, Excel surrounds the copied area with a thick-dashed border.
As long as that border remains visible, the copied information is available for pasting. If you press Esc to cancel the border, Excel removes the information from the Clipboard. Because copying or moving is used so often, Excel provides many different methods. I discuss each method in the following sections. Copying and moving are similar operations, so I point out only important differences between the two. You only need to activate the upper-left cell in the destination range.
Each time that you cut or copy information, Windows replaces the information previously stored on the Clipboard with the new information that you cut or copied. The Windows Clipboard can store data in a variety of formats. Because Windows manages information on the Clipboard, it can be pasted to other Windows applications, regardless of where it originated. Microsoft Office has its own Clipboard the Office Clipboard , which is available only in Office programs.
Whenever you cut or copy information in an Office program, such as Excel or Word, the program places the information on both the Windows Clipboard and the Office Clipboard. However, the program treats information on the Office Clipboard differently from the way it treats information on the Windows Clipboard. Instead of replacing information on the Office Clipboard, the program appends the information to the Office Clipboard.
With multiple items stored on the Clipboard, you can then paste the items either individually or as a group. For more control over how the pasted information appears, right-click the destination cell and use one of the paste icons in the shortcut menu see Figure 4. Instead of using Paste, you can just activate the destination cell and press Enter.
Click the list or press Ctrl , and you see the options shown in the figure here. These options enable you to specify how the data should be pasted, such as values only or formatting only. In this case, using the paste option buttons is an alternative to using options in the Paste Special dialog box.
Count me in the latter group. Copying or moving by using drag-and-drop Excel also enables you to copy or move a cell or range by dragging. Oddly, you do not get a warning if a drag-anddrop copy operation will overwrite existing cell contents. The mouse pointer is augmented with a small plus sign. Then drag the selection to its new location while you continue to press the Ctrl key. The original selection remains behind, and Excel makes a new copy when you release the mouse button.
Copying to adjacent cells Often, you need to copy a cell to an adjacent cell or range. You can use the same formula to add the values in the other columns. Rather than re-enter the formula, you can copy it to the adjacent cells. Excel provides additional options for copying to adjacent cells.
Excel copies the original selection to the cells that you highlight while dragging. Copying a range to other sheets You can use the copy procedures described previously to copy a cell or range to another worksheet, even if the worksheet is in a different workbook.
You must, of course, activate the other worksheet before you select the location to which you want to copy. Excel offers a quicker way to copy a cell or range and paste it to other worksheets in the same workbook: 1. Select the range to copy. Press Ctrl and click the sheet tabs for the worksheets to which you want to copy the information. A dialog box appears to ask you what you want to copy All, Contents, or Formats.
Make your choice and then click OK. Excel copies the selected range to the selected worksheets; the new copy occupies the same cells in the selected worksheets as the original occupies in the initial worksheet.
You can quickly overwrite lots of cells with this command and not even realize it. To make the Clipboard task pane open automatically, click the Options button near the bottom of the task pane and choose the Show Office Clipboard Automatically option.
Repeat this process, selecting the next cell or range that you want to copy. To paste an individual item, click it in the Clipboard task pane. The items are pasted, one after the other. The Paste All button is probably more useful in Word, for situations in which you copy text from various sources and then paste it all at once.
The Office Clipboard has a serious problem that limits its usefulness for Excel users: if you copy a range that contains formulas, the formulas are not transferred when you paste to a different range. Only the values are pasted. Pasting in special ways You may not always want to copy everything from the source range to the destination range. For example, you may want to copy only the formula results rather than the formulas themselves. Or you may want to copy the number formats from one range to another without overwriting any existing data or formulas.
Click the icon to use the selected paste option. Here, the information is copied from E4:G7 and is being pasted beginning at cell F11 using the Transpose option. Rows become columns, and columns become rows. Any formulas in the copied range are adjusted so that they work properly when transposed.
When clicked, it merges the copied conditional formatting with any conditional formatting in the destination range. The destination for the copy can be a new range or the original range. In the latter case, Excel replaces the original formulas with their current values. A Paste Options drop-down appears at the lower right of the pasted range. Click it or press Ctrl , and you see the paste option icons again. You can also right-click and choose Paste Special from the shortcut menu to display this dialog box.
This dialog box has several options, which I explain in the following list. Excel actually has several different Paste Special dialog boxes, each with different options. This section describes the Paste Special dialog box that appears when a range or cell has been copied.
For the Paste Special command to be available, you need to copy a cell or range. In addition, the Paste Special dialog box enables you to perform other operations, described in the following sections. Performing mathematical operations without formulas The option buttons in the Operation section of the Paste Special dialog box let you perform an arithmetic operation on values and formulas in the destination range.
For example, you can copy a range to another range and select the Multiply operation. Excel multiplies the corresponding values in the source range and the destination range and replaces the destination range with the new values. This feature also works with a single copied cell, pasted to a multicell range. Assume that you have a range of values, and you want to increase each value by 5 percent. Then select the range of values and bring up the Paste Special dialog box. If the destination range contains formulas, the formulas are also modified.
In many cases, this is not what you want. Transposing a range The Transpose option in the Paste Special dialog box changes the orientation of the copied range. Note that you can use this check box with the other options in the Paste Special dialog box. As a result, the destination range automatically reflects changes in the source range. For example, WOW! You can paste a cell or range name into a formula by using Formula Autocomplete.
See Chapter 10 for information on Formula Autocomplete. Creating range names in your workbooks Excel provides several methods you can use to create range names. Excel also uses a few names internally for its own use.
Using the Name box The fastest way to create a name is to use the Name box to the left of the Formula bar. Select the cell or range to name, click the Name box, and type the name. Press Enter to 98 WOW! If the name you type includes an invalid character, Excel displays an error message. Attempting to do so simply selects the range.
The Name box is a drop-down list and shows all names in the workbook. To choose a named cell or range, click the Name box and choose the name. The name appears in the Name box, and Excel selects the named cell or range in the worksheet. Using the New Name dialog box For more control over naming cells and ranges, use the New Name dialog box. Start by selecting the cell or range that you want to name.
Excel displays the New Name dialog box, shown in Figure 4. Note that this is a resizable dialog box. Click and drag a border to change the dimensions.
Use the Scope drop-down list to indicate the scope for the name. If you like, you can add a comment that describes the named range or cell. Click OK to add the name to your workbook and close the dialog box. Using the Create Names from Selection dialog box You may have a worksheet that contains text that you want to use for names for adjacent cells or ranges.
For example, you may want to use the text in column A to create names for the corresponding values in column B. Excel makes this task easy. These items can be individual cells or ranges of cells.
A multiple selection is allowed. Excel displays the Create Names from Selection dialog box, shown in Figure 4. Click OK, and Excel creates the names.
Using the data in Figure 4. If the text contained in a cell would result in an invalid name, Excel modifies the name to make it valid.
For example, if a cell contains the text Net Income which is invalid for a name because it contains a space , Excel converts the space to an underscore character.
If the upper-left cell of the selection contains text and you choose the Top Row and Left Column options, Excel uses that text for the name of the entire range, excluding the top row and left column. So, after Excel creates the names, take a minute to make sure that they refer to the correct ranges.
If Excel creates a name that is incorrect, you can delete or modify it by using the Name Manager described next. If your workbook has many names, you should know about the Name Manager, shown in Figure 4. You can also click a column heading to sort the information by the column. For example, you can view only the worksheet-level names.
You can change the name itself, modify the Refers To range, or edit the comment. If the name is used in a formula, deleting the name causes the formula to become invalid. It displays NAME?. However, deleting a name can be undone, so if you find that formulas return NAME?
If you delete the rows or columns that contain named cells or ranges, the names contain an invalid reference. If you use the name Interest in a formula, the formula displays REF. Such a feat is possible, but you need to look beyond the Name Manager.
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, or times such as a.
Excel can display values in many different formats. In the Applying Number Formatting section, later in this chapter, you see how different format options can affect the display of numeric values. You may be curious about the types of values that Excel can handle. In other words, how large can a number be? And how accurate are large numbers?
Excel's numbers are precise up to 15 digits. For example, if you enter a large value, such as ,,,,, 18 digits , Excel actually stores it with only 15 digits of precision. This digit number displays as ,,,,, This precision may seem quite limiting, but in practice, it rarely causes any problems. One situation in which the digit accuracy can cause a problem is when entering credit card numbers.
Most credit card numbers are 16 digits, but Excel can handle only 15 digits, so it substitutes a zero for the last credit card digit.
Even worse, you may not even realize that Excel made the card number invalid. The solution? Enter the credit card numbers as text. The easiest way is to preformat the cell as Text. Or you can precede the credit card number with an apostrophe.
Either method prevents Excel from interpreting the entry as a number. These numbers are expressed in scientific notation. For example, the largest positive number is 9. Keep in mind, though, that this number has only 15 digits of precision. 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 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. 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 B Column D, for reference, shows the actual contents of the cells in column B.
This workbook, named loan payment calculator. To enter a numeric value into a cell, move the cell pointer to the appropriate cell, type the value, and then press Enter 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, 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 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, 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 possible to actually display all these characters. If you type an exceptionally long text entry into a cell, the Formula bar may not show all the text. To display more of the text in the Formula bar, click the bottom of the Formula bar and drag down to increase the height see Figure 2.
Pressing this key combination toggles the height of the Formula bar to show either one row or the previous size. 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:. 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. 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.
Excel handles dates by using a serial number system. The earliest date that Excel understands is January 1, This date has a serial number of 1.
January 2, , 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, , you can enter the date by typing June 1, or use any of several different date formats. Excel interprets your entry and stores the value , which is the serial number for that date. The date examples in this book use the U.
English system. Your Windows regional settings will affect the way Excel interprets a date you've entered. For example, depending on your regional date settings, June 1, may be interpreted as text rather than a date. In such a case, you need to enter the date in a format that corresponds to your regional date settings — for example, 1 June, For more information about working with dates, see Chapter 12, Working with Dates and Times.
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, , is Noon on June 1, halfway through the day , is represented internally as 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, You can also modify a cell by changing its formatting.
However, formatting a cell affects only a cell's appearance. Formatting doesn't affect the cell's contents. Later sections in this chapter cover formatting. To delete the contents of a cell, just click the cell and press the Delete key.
To delete more than one cell, select all 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 five choices:. Clear All: Clears everything from the cell — its contents, its formatting, and its 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 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.
Clearing formats doesn't clear the background colors in a range that has been designated as a table unless you've replaced the table style background colors manually.
See Chapter 5, Introducing Tables, for more about tables. 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 pasting data from the Clipboard. In both cases, the cell formatting will be replaced by the format of the new data. 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 cell-edit mode:. 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. These settings affect how editing works. To access this dialog box, choose File Options.
If the Allow Editing Directly in Cells option isn't enabled, you can't edit a cell by double-clicking. In addition, pressing F2 allows you to edit the cell in the Formula bar not directly in the cell. All 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. 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. Open navigation menu. Close suggestions Search Search.
User Settings. Skip carousel. Carousel Previous. Carousel Next. What is Scribd? Explore Ebooks. Bestsellers Editors' Picks All Ebooks. Explore Audiobooks. Bestsellers Editors' Picks All audiobooks. Explore Magazines. Editors' Picks All magazines. Explore Podcasts All podcasts. Difficulty Beginner Intermediate Advanced. Explore Documents. Enjoy millions of ebooks, audiobooks, magazines, and more. Start your free days Read preview.
Publisher: Wiley. Released: Oct 20, ISBN: Format: Book. The complete guide to Excel , from Mr. Spreadsheet himself Whether you are just starting out or an Excel novice, the Excel Bible is your comprehensive, go-to guide for all your Excel needs. Create functional spreadsheets that work Master formulas, formatting, pivot tables, and more Get acquainted with Excel 's new features and tools Customize downloadable templates and worksheets Whether you need a walkthrough tutorial or an easy-to-navigate desk reference, the Excel Bible has you covered with complete coverage and clear expert guidance.
Enterprise Applications. About the author. Related to Excel Bible Titles in the series 8. Read More From John Walkenbach. Spreadsheet's Bookshelf by Peter G. Related Books. Learn Excel from Mr. Related Podcast Episodes.
How to recognize ML generated images. Excel gains OCR capabilities. He has an Excel blog over at www. The magic of spreadsheets: Devotees love spreadsheets for their directness. You click the formula that you want, edit it instantly and it updates, live.
But their use can also lead to problems, as the UK retailer Marks and Spencer recently discovered to its cost. Columnist Lisa Echeruo had no expertise in computer science when he set out to build the service, but he was a whiz at Excel. With a little help from Upwork and some paper maps, he built a business that was eventually acquired by Apple. Show Notes In , Andre founded Ping Identity with a vision of securing the Internet through identity.
He recently sold his company They range from the CTRL combinations shortcuts to the Function keys shortcuts as well as many other Excel keyboard shortcuts. In this She currently works as the director of community engineering at Glitch. In this episode of That Happened Fast Micah Shippee by OnEducation 65 min listen. MacVoices Michael E. You may have heard our conversation back on January 27, Related Articles.
Related categories Skip carousel. Here are just a few of the uses for Excel: Number crunching: Create budgets, tabulate expenses, analyze survey results, and perform just about any type of financial analysis you can think of. Creating charts: Create a variety of highly customizable charts. Organizing lists: Use the row-and-column layout to store lists efficiently. Text manipulation: Clean up and standardize text-based data. Accessing other data: Import data from a variety of sources.
The Backstage area which displays when you click the File tab has been reorganized. Understanding Workbooks and Worksheets You perform the work you do in Excel in a workbook. Note In previous versions of Excel, users could work with multiple workbooks in a single window. Screenshot of the Excel screen with labeled parts. Table 1. Image described by caption and surrounding text. Note Excel is also available for devices that use a touch interface.
Navigating with your keyboard Not surprisingly, you can use the standard navigational keys on your keyboard to move around a worksheet. Tip You can use the keyboard to scroll through the worksheet without changing the active cell by turning on Scroll Lock, which is useful if you need to view another area of your worksheet and then quickly return to your original location.
Tip If your mouse has a wheel, you can use it to scroll vertically. Using the Ribbon In Office , Microsoft made a dramatic change to the user interface. Ribbon tabs The commands available in the Ribbon vary, depending upon which tab is selected. Here's a quick overview of Excel's tabs: Home: You'll probably spend most of your time with the Home tab selected.
Note Although the File button shares space with the tabs, it's not actually a tab. Contextual tabs In addition to the standard tabs, Excel includes contextual tabs. Image described by surrounding text. Types of commands on the Ribbon When you hover your mouse pointer over a Ribbon command, you'll see a pop-up box that contains the command's name and a brief description.
You'll find several different styles of commands on the Ribbon: Simple buttons: Click the button, and it does its thing. Accessing the Ribbon by using your keyboard At first glance, you may think that the Ribbon is completely mouse centric.
Tip You don't need to hold down the Alt key while you type keytip letters. Tip Often, you'll want to repeat a particular command. The cursor points Protect Sheet option. Using Shortcut Menus In addition to the Ribbon, Excel features many shortcut menus, which you access by right-clicking just about anything within Excel.
Snipped image of Excel presenting six highlighted cells C2 to C7 with two shortcut menus: right-click menu of various options and menu with the elements of Font and Number panels. Customizing Your Quick Access Toolbar The Ribbon is fairly efficient, but many users prefer to have certain commands available at all times, without having to click a tab. See Chapter 24 for more information about customizing your Quick Access toolbar. Caution You can't reverse every action, however.
Working with Dialog Boxes Many Excel commands display a dialog box, which is simply a way of getting more information from you. Screenshot of Protect Sheet dialog box presenting selected commands: Protect worksheet and contents of locked cells command and Allow all users to select locked cells and select unlocked cells commands. Excel dialog boxes vary in the way they work. You'll find two types of dialog boxes: Typical dialog box: A modal dialog box takes the focus away from the spreadsheet. Navigating dialog boxes Navigating dialog boxes is generally very easy — you simply click the control you want to activate.
Tip When a control is selected, it appears with a dotted outline. Using tabbed dialog boxes Several Excel dialog boxes are tabbed dialog boxes; that is, they include notebook-like tabs, each of which is associated with a different panel. Screenshot of Format Cells dialog box with six tabs. Font tab is selected displaying font options. Calibri Body font, Regular font style, and font size of 11 are selected.
OK button at the bottom is highlighted. Using Task Panes Yet another user interface element is the task pane. Tip If you prefer to use your keyboard to work within a task pane, you may find that common dialog box keys such as Tab, Space, the arrow keys, and Alt key combinations don't seem to work. Getting started on your worksheet Start Excel and make sure that you have an empty workbook displayed.
Here's how to begin: Move the cell pointer to cell A1 the upper-left cell in the worksheet by using the navigation arrow keys. Filling in the month names In this step, you enter the month names in column A. Release the mouse button, and Excel automatically fills in the month names. Your worksheet should resemble the one shown in Figure 1.
Comments
Post a Comment