Looking for:
- Microsoft Project by Microsoft Official Academic Course |
Microsoft Project - PDF Drive - Account Options
Microsoft official academic course microsoft project 2016 pdf free
Lists with This Book. This book is not yet featured on Listopia. Add this book to your favorite list ». Community Reviews. Showing Average rating 3. Rating details. All Languages. More filters. Sort order. Start your review of Microsoft Project Nikolas rated it it was ok Oct 16, Seth rated it really liked it Mar 03, Brett Kerns rated it liked it Mar 24, Ryan rated it really liked it Dec 30, Ryan Howard marked it as to-read Jan 18, Scot McAtee added it Mar 10, Lisa Richard marked it as to-read Apr 23, Bruce Churchill added it Jan 13, Lia Estine marked it as to-read Sep 08, Denver marked it as to-read Sep 14, There are no discussion topics on this book yet.
Be the first to start one ». About Microsoft Official Academic Course. Microsoft Official Academic Course. Books by Microsoft Official Academic Course.
Need another excuse to treat yourself to a new book this week? We've got you covered with the buzziest new releases of the day. To create our Read more Trivia About Microsoft Project All entries are removed. The previous entry will be restored. You can edit a cell by double-clicking the cell and then typing the replacement text in the cell.
Or, you can click the cell and then click in the formula bar. Use the Home key on your keyboard to move the insertion point to the beginning of the cell, and use the End key to move the insertion point to the end of the cell.
You can add new characters at the location of the insertion point. To select multiple characters while in Edit mode, press Shift while you press the arrow keys.
You also can use the mouse to select characters while you are editing a cell. Just click and drag the mouse pointer over the characters that you want to select. This deletes what is in the cell rather than the cell itself. To erase the contents of more than one cell, select all the cells that you want to erase and on your keyboard, press Delete.
In cell A1, type 1 and then press Enter. Type 2 and then press Enter. Type 3 and then press Enter. Type 4 and then press Enter. Highlight cells A1 through A4 containing the numbers 1 through 4.
All the cells are erased. On the Quick Access Toolbar, click the Undo button to restore the cell entries. The value and format are placed into the cell. Click cell B5 and then press Delete. Type without the dollar sign and comma and then press Enter. Although the original entry is gone, the cell retains the previous format when you press Delete.
Click cell B5 and on the Home tab, in the Editing group, click Clear. Click Clear Formats. Cell B5 displays without the dollar sign and comma. Take Note Clear displays a number of options.
To remove both the entry and the format, choose Clear All. You have already en- tered basic text and numeric data in this lesson. In the following exercises, you enter dates, use Auto Fill to complete data in a series, and use the Flash Fill feature to speed data entry down a column.
The strength of Ex- cel is its capability to calculate and analyze numbers based on the numeric values you enter. Of course, if you enter the wrong numbers, you get the wrong calculations. For that reason, accurate data entry is crucial. Entering Dates Dates are often used in worksheets to track data over a specified period of time.
Like text, dates can be used as row and column headings. However, dates are considered serial numbers, which means that they are sequential and can be added, subtracted, and used in calculations. Dates can also be used in formulas and in developing graphs and charts. The way a date is initially displayed in a worksheet cell depends on the format in which you type the characters. In Excel , the de- fault date format uses four digits for the year.
Also by default, dates are right-justified in the cells. Working with Microsoft Excel 25 4. If the year displayed in the formula bar is not , click cell B7 and then press F2. Change the year to and then press Enter. In cell B9, type January 21, and then press Enter. If you enter a date in a different format than specified or had already entered something in the cell and deleted it, your worksheet might not reflect the results described.
The date formats in column B are not consistent see Figure You apply a consistent date format in the next section. Notice that the value changes but the formatting remains the same. Click the Undo button to return to the workbook shown in Figure Excel interprets two-digit years from 00 to 29 as the years to ; two-digit years from 30 to 99 are interpreted as to If you type January 28, , the date will display as Jan In the next section, you learn to apply a consistent format to a series of dates.
Take Note When you enter a date into a cell in a particular format, the cell is automatically formatted even if you delete the entry. Subsequent numbers entered in that cell will be converted to the date format of the original entry. To populate a new cell with data that exists in an adjacent cell, use the Auto Fill feature either through the command or the fill handle. The fill handle is a small green square in the lower-right corner of a selected cell or range of cells. A range is a group of adjacent cells that you select to perform operations on all of the selected cells.
When you refer to a range of cells, the first cell and last cell are separated by a colon for example, C4:H4. Click and drag the fill handle from cells that contain data to the cells you want to fill with that data, or have Excel automatically continue a series of numbers, numbers and text combinations, dates, or time periods, based on an established pattern. In this exercise, you use the Auto Fill command and fill handle to populate cells with data.
USE the workbook from the previous exercise or type the text in Figure Select the range C4:H4. January is in the first cell. On the Home tab, in the Editing group, click the Fill button. The Fill menu appears see Figure Figure Fill drop-down menu Fill button Fill options 3.
From the menu, click Right. The contents of C4 January are filled into all the cells. Click the Undo button. Select the range C9:C13 and then click the Fill button. Choose Down. The content of C9 is copied into the four additional cells. Click cell C4, point to the fill handle in the lower-right corner of the cell see Figure , and drag it to E4 and release. Click cell C5, point to the fill handle, and then drag it to C9 and release.
The Auto Fill Options button appears near the lower-right corner of the selected range see Figure All the numbers return to their previous values and are formatted with dollar signs and commas. Repeat Steps 8 and 9 for the range B5:B9. Working with Microsoft Excel 27 Figure You can fill numbers, formats, or other options. Auto Fill Options button Click cell A9, and then drag the fill handle down to A Click the Undo button to return the spreadsheet.
After you fill cells using the fill handle, the Auto Fill Options button appears so that you can choose how the selection is filled. In Excel, the default option is to copy the original content and formatting.
With Auto Fill, you can select how the content of the original cell appears in each cell in the filled range. Take Note When you type sufficient data for Excel to recognize a series, the fill handle will do the rest.
For example, to record daily sales, you might want to have consecutive columns labeled with the days of the week. If you type Monday in the first cell, you can fill in the rest of the days by dragging the fill handle from the Monday cell to complete the series. Excel recognizes January as the beginning of a natural series and completes the series as far as you take the fill handle.
By definition, a natural series is a formatted series of text or numbers that are in a normal sequence such as months, weekdays, numbers, or times. For example, a natural series of numbers could be 1, 2, 3, or , , , or a natural series of text could be Monday, Tuesday, Wednesday, or January, February, March. For different natural series, see Table With Flash Fill, you can quickly fill a column of data using an example that is based on existing data in adjacent columns.
As the following steps show, for example, you can almost instantly cre- ate columns for first and last names if the full name appears in another column. After you enter the initial item such as the first name in a column and start to type the second item in that col- umn, Excel displays a preview of entries formatted the same way in the rest of the column.
If the presented preview of the data is what you want, just press Enter to fill the column with the entries. OPEN 02 Customers from the data files for this lesson. Notice the customer list in column A, which includes the last name followed by a comma and then the first name.
You want to create separate columns for the first and last names. Select cell B2 in the First Name column. Type Alice and then press Enter. In cell B3, type Ai to begin the next first name, Aidan. Excel guesses that you want to enter the rest of the first names in column B and displays a preview of the results see Figure Figure Flash Fill showing possible list Type first name of for all First Names first person in B2 Type first two letters of second person in B3 and Excel automatically guesses that you want all first names in column B.
Press Enter to accept the suggestion. The remaining first names fill down the column. Notice that Excel also includes the middle initials for those names that include them. Select cell C2 in the Last Name column. Type Ciccu and then press Enter.
In cell C3, type De to begin the next last name, Delaney. Excel guesses that you want to enter the rest of the last names in column C and displays a preview of the results.
Working with Microsoft Excel 29 9. The remaining last names fill down the column. These processes are discussed as the exercises in this section continue. You can also copy specific contents or attri- butes from the cells. For example, you can copy the format only without copying the cell value, or copy the resulting value of a formula without copying the formula itself.
You can also copy the value from the original cell but retain the formatting of the destination cell. Just select the cell or range of cells you want to copy and hold down Ctrl while you point to the border of the selection. When the pointer becomes a copy pointer arrow with a plus , you can drag the cell or range of cells to the new location. As you drag, a scrolling ScreenTip identifies where the selection will be copied if you release the mouse button. In this exercise, you practice copying data with the mouse.
Open the 02 Customer Houses file. Select the range AA Press Ctrl and point to the right border of the selected range. The copy pointer is displayed. Troubleshooting Be sure to hold down the Ctrl key the entire time you are dragging a data series for copying with the mouse, or you will move the series instead of copying it.
With the copy pointer displayed, press and hold down the left mouse button and drag the selection to the right, until HH22 appears in the scrolling ScreenTip next to the selection. Release the mouse button and then release Ctrl. The data in AA22 also appears in HH Moving a Data Series with the Mouse Data can be moved from one location to another within a workbook in much the same way as copying. To move a data series, select the cell or range of cells and point to the border of the se- lection.
When data is moved, it replaces any existing data in the destination cells. In this exercise, you practice moving a data series from one range of cells to another. USE the 02 Customer Houses workbook from the previous exercise. Select EE Point to the right border of the selected range. The move pointer a white arrow with four smaller black arrows attached is displayed.
With the move pointer displayed, hold down the left mouse button and then drag the selection to the right, until II22 appears in the scrolling ScreenTip beside the selected range. Release the mouse button. In your worksheet, the destination cells are empty; therefore, you are not concerned with replacing existing data.
The data previously in EE22 is now in II Drag A1 to H Note that a dialog box warns you about replacing the contents of the destination cells. Click Cancel. Drag E1 to I Your worksheet should look like the one shown in Figure Take Note When you attempt to move a selection to a location that contains data, a caution dialog box opens. Do you want to replace it? You can click OK or cancel the operation. Copying and Pasting Data The Office Clipboard collects and stores up to 24 copied or cut items that are then available to be used in the active workbook, in other workbooks, and in other Microsoft Office programs.
Working with Microsoft Excel 31 You can paste insert selected items from the Clipboard to a new location in the worksheet. Cut moved data is removed from the worksheet but is still available for you to use in multiple locations.
If you copy multiple items and then click Paste, only the last item copied will be pasted. To access multiple items, you must open the Clipboard pane. In this exercise, you use commands in the Clipboard group and the Clipboard pane to copy and paste cell data. The Clipboard pane opens on the left side of the worksheet.
The most recently copied item is always added at the top of the list in this pane, and it is the item that will be copied when you click Paste or a shortcut command. Select A1:E22 and then press Delete. Select HI22 and in the Clipboard group, click the Copy button. The border around the selected range becomes a moving border. Select A1 and then click the Paste button. The moving border remains active around HI A copied range does not deactivate until you type new text, issue another command, double-click on another cell, or press Esc.
Select A20 and then click the down arrow on the Paste button. The Paste options menu appears see Figure Figure The Paste options menu 6. Under Paste Values, select the first option. Notice that the values in the range BB31 are no longer formatted. Select HI22 and then press Delete.
Click the Close button in the upper-right corner of the Clipboard pane to close it. Take Note If you point to the Paste options in either the shortcut menu or the Paste options in the Clipboard group, you will be able to preview your changes before actually implementing them.
The properties include items that you indirectly change such as file size and last edit date. The workbook properties also include items you directly change such as keywords. Assigning keywords also called tags to the document properties makes it easier to organize and find documents. You can also add more notes to your file for classification and document management.
Assigning Keywords If you work for Fabrikam, Inc. You can then search for and locate all files containing information about sales. You can assign more than one keyword to a document. Click File. The Backstage view displays current properties on the right side of the window. At the bottom of the right pane, click the Show All Properties link to display additional properties. Click the Categories field and type Revenue. Click the Company field and type Fabrikam, Inc..
Above the Size field, click the Properties drop-down arrow, and then click Advanced Properties. The Properties dialog box opens see Figure Click the Summary tab in the dialog box to see the properties you entered. Click the Statistics tab to see the date you modified the file. Click OK to close the Properties dialog box. Press Esc to return to the worksheet. Working with Microsoft Excel 33 After a file is saved, the Statistics tab records when the file was accessed and when it was modified.
It also identifies the person who last saved the file. After a workbook is saved, the Properties dialog box title bar displays the workbook name. Knowledge Assessment Multiple Choice Select the best response for the following statements.
Which of the following consists of details that describe or identify a file, including the author? Paste b. Document properties c. Copy d. Range 2. Which command is used to insert a cut or copied selection to a cell or range of cells? Range 3. If you want to use a workbook in another kind of document, you have the option to save using which of the following?
File format b. Worksheet c. File sheet d. File range 4. Which is a small green square in the lower-right corner of a selected cell or range that you can use to copy one cell to adjacent cells or to create a series?
Cell pointer b. Column marker c. Fill handle d. Formula bar 5. Which of the following can you drag or double-click to change the width of a column? Using the Delete key removes both text and formats from a cell. You can assign keywords so that others can search for your documents online. The formula bar is found at the bottom of the Excel window. Use the fill handle to create a natural series, such as the months of the year.
Workbooks can be saved as web pages, PDF files, and for use in previous versions of Excel. Click cell A1, type Fabrikam, Inc. Beginning in A4, type the following labels and values. If necessary, adjust the column widths to display all of the text in the columns. LEAVE the workbook open for the next project. Project Setting Document Properties and Assigning Keywords In this project, you will use Document Properties to assign properties to an existing workbook. Click Properties and then click Advanced Properties.
Click the Summary tab. In the Subject field, type Sales and then press Tab. In the Author field, type [your name] and then press Tab four times. In the Keywords field, type , options, priorities. CLOSE the file.
Each tab provides related options and settings to help you manage your Excel workbook files. This is your access to Backstage view. When you click the File tab to access Backstage view, you see the navigation pane on the left side of the window, with commands related to managing files refer to Figure If you want to return to your workbook from Backstage view, click the Return to document button in the upper left corner or press Esc.
You can use the Print settings to manipulate workbook elements such as margins, orientation, paper size, and so on. Many of these commands are also available from the Page Layout tab on the ribbon. Setting the Print Area You can use the Print options in Backstage view to print only a selected portion, or print area, of an Excel workbook.
In this exercise, you learn to select an area of a workbook for printing. You print just the list of items in the worksheet. On the worksheet, click cell A3, hold the mouse button, and then drag to cell A Your selected cell range should be highlighted in gray see Figure Figure Selecting a print area 2. Click the File tab and then click Print in the navigation pane. Under Settings, click the first button, labeled Print Active Sheets the name on the button changes if you make a different selection.
A drop-down menu of options displays for setting the print area. From the menu that appears, click Print Selection. You have now set the print area. Notice in the Print Preview pane on the right that you can see only the list of items to bring and not the text in rows 1 and 2 see Figure Click the Return to document button. You will not print at this time. Take Note Specifying a print area from the Backstage view does not save the setting with the workbook.
If you want to save the print area for later use, select the range for the print area, and then on the Page Layout tab, click Print Area, and then Set Print Area. Printing Selected Worksheets In this exercise, you learn to access the options for printing individual worksheets in a workbook. You can use these options to print the current worksheet only or to print multiple worksheets. This is a modified version of the potluck workbook you used previously.
In this case, there are three different worksheets for three different departments. Click each of the three worksheet tabs: HR, Operations, and Finance. Notice that the title in C1 shows the department name and there are a different number of items to bring to each potluck depending on the size of the department. Click the HR tab. In the Print Preview pane, the first worksheet displays.
Excel prints only the active sheets by default. Notice that the page number shows 1 out of 1, indicating that only one of the worksheets will print, and it will all fit on one page. Press Esc or click the Return to document button. While the HR worksheet is active, hold down Ctrl and click on the Finance tab. Now both the HR and Finance worksheets are selected.
Click the File tab and then click Print. Now in the Print Preview area, the bottom of the screen shows 1 of 2 with the HR worksheet preview. Click the right arrow to go to the second page and notice that the Finance worksheet previews see Figure Page 1 is the HR worksheet 7.
In the drop-down menu that appears, notice that you can select several printing options for your workbook or worksheet. Click the Return to document button to return to the workbook without printing.
CLOSE the workbook without saving. Printing an Entire Workbook In most scenarios in business, workbooks are composed of multiple worksheets. In this exercise, you use commands in Backstage view to print an entire workbook. With Excel open from the previous exercise, perform the following steps. Click the File tab and then click Open if it is not selected. Because you just used the 03 Contoso Potluck Depts workbook, it should be at the top of the Recent list in the right pane.
Click 03 Contoso Potluck Depts to open it. Notice that the complete workbook does not display in the Print Preview area. This is because you did not save the workbook after you selected multiple sheets in the previous exercise. Now in the Print Preview area, the bottom of the screen shows 1 of 3. This exercise prepares you to customize such options as page setup, scale, paper selection, and gridlines, all using the commands in Backstage view. Click the File tab and select Print see Figure Notice that the worksheet is small and it might be nice to have lines for people to write in on a printed page.
In the Settings area of the Print window, click the Margins drop-down arrow and then click Wide. The new margins will allow the worksheet to be hole-punched and put in a binder. Click the Scaling drop-down and see the choices below.
The scaling options ensure that all columns, rows, or the entire worksheet fit on one page. In this case, you want to make the text larger without changing the font.
Click Custom Scaling Options. The Page Setup dialog box opens. Make sure that the Page tab is selected and select Landscape so the page prints horizontally. Click the Sheet tab and in the Print section, select the Gridlines check box. Click OK to return to Backstage view. Notice that the bottom of the screen still says, 1 of 1, meaning that only one page will print and notice that Print Preview shows larger text with boxes around each cell.
Without printing the document, click the Return to document button and then click the HR-P2 worksheet. Change the Settings to print Landscape, the Scaling to Fit Sheet on One Page, and add gridlines based on the previous steps in this section.
Print Preview should look like Figure Default settings are pre-set options that determine how Excel will behave when performing an action. For example, a default printer is the one your documents always print to unless you change the setting.
By default, there is one worksheet in a workbook and the font is Calibri, 11 points—but you can change those settings too. Take Note After you customize the Quick Access Toolbar and the ribbon in the following exercises, you will follow steps to return Excel to the default settings. In this exercise, you customize the Quick Access Toolbar by adding commands for functions you use most frequently in Excel, and by organizing the command but- tons on the toolbar to best suit your working needs and style.
OPEN a blank workbook in Excel. Click the File tab to access Backstage view. In the navigation pane, click the Options tab. In the right pane, the list on the left includes the commands that you can add to the toolbar.
The list on the right shows the commands that are currently shown on the toolbar. In the list on the left, scroll down and click Format Painter see Figure , and then click the Add button in the center of the two lists to add the Format Painter to the Quick Access Toolbar.
Using the same process, move five more commands you use often to the Quick Access Toolbar. Notice that your Quick Access Toolbar now includes additional command buttons. Similarly, you can remove any command that you added to the toolbar. At any time, you can reset the toolbar to its default settings.
Click Yes to confirm and then click OK. Customizing the Ribbon As mentioned in Lesson 1, the ribbon is a visual interface that allows you to manipulate items on your worksheet. You can add a new ribbon tab, add a group on any ribbon tab, and add commands within a group. A group of commands on the default ribbon tabs are related in functionality. For example, on the Home tab, the Font group allows you to change the font, font size, add bold, italic, or underline, or change the color of the cell or font.
The Excel Options dialog box also offers selections for customizing the ribbon. You can add and remove commands, and you can change the location of ribbon commands to make accessing those you use most frequently more convenient.
In this exercise, you use the commands in the Excel Options dialog box to create a new tab and command group to contain your frequently used com- mands. Then you will reset the customizations to return to the default ribbon. Click the Options tab. In the Excel Options dialog box, click Customize Ribbon. The Customize the Ribbon options appear. By default, Popular Commands is selected in the Choose commands from drop-down box.
The list of Popular Commands appears in the list below the drop- down box. In the list of Popular Commands, click Format Painter. Note the Add button in the center of the dialog box is now active. You can use this method to display the current groups available on a ribbon tab.
Under the Customize the Ribbon options, click the New Tab button shown in Figure to insert a new blank tab into the Customize the Ribbon list. When you click New Tab, a New Group is automatically created inside the new tab. Click the New Tab Custom list item on the right to select it, and then click the Rename button. Under your new tab, click New Group Custom to select it.
Click the Rename button again. This time, the Rename dialog box allows you to select a symbol see Figure Select the hand symbol. You see the New Group renamed. In the command list on the left, click on a command of your choice, and then click the Add button. The command appears on your new ribbon tab. In the Choose commands from list, select All Commands and then add another command from this list.
In the Choose commands from list, select File Tab and then add another command. Your screen should look similar to Figure Click OK to close the Excel Options dialog box. When you exit, you see your tab named My New Tab on the ribbon.
Click the My New Tab tab. Right-click anywhere in the ribbon and then click Customize the Ribbon. Near the lower-right corner of the Excel Options dialog box, click the Reset button and then click Reset all customizations. This exercise familiarizes you with where the templates are located and how to select and use them. Selecting a Template from the New Tab Templates allow you to create professional workbooks in a fraction of the time it would take you to develop them from scratch.
Examples of these are budgets, loan models, invoices, calendars, and so on. Click the File tab and then click New. The New window displays a series of featured templates. You already used the Blank workbook template in this book. Scroll down if necessary and then click the Project Tracker icon. Click the Create button. Notice that there are two worksheets in this workbook: Project Tracker with the sample data you can change and Setup that allows you to input a list of categories and employees.
When you are finished looking at this template, click File and then click Close. If prompted, do not save changes. Look for and click the Academic calendar any year template and click the Create button.
Click on the year and use the up or down arrow to change to the current year if necessary. Click the month name and select the current month name from the drop- down list.
Scroll down the worksheet to see additional months displayed in the template. Notice that the Academic calendar any year template appears as the third item on your list of templates because it was recently used. Click in the Search for online templates box at the top of the window. Type Budget and then press Enter. Notice that Excel displays a downloaded list of templates as well as a Category list on the right so you can narrow the choices see Figure Take Note You can modify the calendar workbook with your name, company name, and favorite pictures to personalize your calendar and make it your own or use as a gift for others.
Which of the following is where you can save, select a template, change document properties, and close or exit Excel? Backstage b. Print c. Edit d. Windows 2. To change printer, layout, or margin settings, you click the File tab and use which of the following options?
Info b. Options c. Print d. Open 3. Which of the following can be customized for quicker access to your most commonly used commands? Print Preview b. Quick Access Toolbar c. Printer setup d. Workbook 4. When you modify the ribbon, which of the following is created? Which predesigned file already has a significant amount of formatting, text, and other features? Blank workbook b. Preset file c. Text file d. To access an Excel template, you can click the File tab and then click New.
If you have too many columns on a page, the only option to see them all on a printed page is to decrease the column width. In Excel, you can add your most commonly used commands to the Quick Access Toolbar. You can create a completely new ribbon tab as well as groups on that ribbon. Click the File tab, click New, and then search for the Movie list template. Add an additional movie to the list. Project Printing a Specific Area of a Worksheet In this project, you will print a selected area in the worksheet and then print a specific worksheet.
Select the range D5:L9. OPEN Backstage view. Click Print in the Backstage view navigation pane. In the Settings section of the Print window, click the Print Active Sheets drop-down arrow, and then click the Print Selection option in the drop-down menu. Click the Print button to print the selected area of the worksheet.
From Backstage view, click the Print tab to return to the Settings options, open the Print Selection drop-down menu and then click Print Active Sheets. Click the Print button to print the active worksheet. For example, you can create basic formulas for addition, subtraction, multiplication, and division using these methods.
However, as you have discovered in previous lessons, the user interface offers tools that make it easier to work with data. In this lesson, you use a few command groups on the Formulas tab to display formulas and name ranges to be used in formulas.
Name box Formula bar View and edit named ranges Define Name Create from Selection Show Formulas Figure The Formulas tab in Excel Use Figure as a reference throughout this lesson as you become familiar with some of the com- mand groups on the Formulas tab and use them to work with formulas. You learn about additional commands on the Formulas tab in the next lesson, which addresses functions. The formula is one of the essential elements of Excel, which enables you to add, subtract, multiply, and divide numbers.
When you enter a formula in a cell, the formula is stored internally and the results are displayed in the cell. You can view the underlying formula in the formula bar when the cell is active, when you double-click the cell to edit it, or by using the Formulas tab. You just entered a formula. Take Note Formulas should be typed without spaces, but if you type spaces, Excel eliminates them when you press Enter. Notice that the result of the formula displays in the cell, but the formula itself appears in the formula bar see Figure Figure Viewing a formula in the formula bar Results of the formula Formula displays display in the cell in the formula bar 4.
Double-click cell A1. The formula appears in both the active cell and the formula bar. You can edit the formula in this mode. The formula in cell A1 displays. Take Note While you are displaying formulas in the worksheet, you will not see the results of those formulas.
Click Show Formulas again to turn off formula display. A formula is an equation that performs calculations, such as addition, subtraction, multiplica- tion, and division, on values in a worksheet. In Excel, a value can be a number, a cell address, a date, text, or Boolean data, but is usually a number or cell address in terms of formulas. A formula consists of two elements: operands and calculation operators. Operands identify the values to be used in the calculation. An operand can be a constant value, or a variable such as a cell reference, a range of cells, or another formula.
A constant is a number or text value that is entered directly into a formula. A variable is a symbol or name that represents something else, which can be a cell address, a range of cells, and so on. Calculation operators specify the calculations to be performed. Excel treats it like an ordinary string of characters.
Using Basic Formulas 51 Excel uses four types of calculation operators: arithmetic, comparison, text concatenation, and reference. This lesson covers several arithmetic operators and the equal sign, which is a comparison operator. The arithmetic operators are listed in Table When you complete the formula and press Enter, the value displays in the cell and the formula displays in the formula bar if you select the cell. You can edit a formula in the cell or in the formula bar the same way you can edit any data entry.
When you click the Show Formulas button on the Formulas tab, all formulas in your worksheet display. Click the Show Formulas button again to toggle off display of formulas. Parentheses play an important role in con- trolling the order of operations. USE the worksheet from the previous exercise. Click cell A1 to make it the active cell.
Click in the formula bar. The result in A1 changes to Figure Parentheses added to the formula 6. Then 7 is added and 4 is subtracted.
You can use parentheses in a formula to override the standard order of operations. Excel per- forms calculations on formulas inside parentheses first. Parentheses inside of parentheses are called nested parentheses. Calculations are performed on formulas in the innermost set of paren- theses first, and from left to right if nested parentheses are at the same level.
Then, 4 is subtracted. Take Note While modifying a complex formula, if you decide to revert back to the original formula and start over, just press Esc.
Using a cell reference rather than the data displayed in a cell gives you more flex- ibility in your worksheet. If the data in a cell changes, any formulas that reference the cell change as well. For example, if cell E1 contains the number 12 but is later changed to 15, any formula that references cell E1 updates automatically.
The same principle applies to a cell that contains a formula and is referenced in another formula. Using Relative Cell References in a Formula A relative cell reference is one that adjusts the cell identifier automatically if you insert or delete columns or rows, or if you copy the formula to another cell.
Click cell B Click in the formula bar and replace with B3. Using Basic Formulas 53 Figure Entering a relative cell Entering a relative reference cell reference Cell B3 is highlighted and surrounded by a blue border. Take Note You can use either uppercase or lowercase when you type a cell reference in a formula. For ex- ample, it does not matter whether you type B4 or b4 in the formula you enter. Excel changes the reference to uppercase when you press Enter. The formula in cell B18 now uses a relative cell reference to cell B3.
Copy cell B18 to cell B The displayed result changes to The cell reference is adjusted relative to its position in the worksheet. An alternate way to use a cell reference is to click the cell being referenced while creating or modifying a formula.
With cell B21 still active, click in the formula bar and select B6. Click cell B3. Cell B3 becomes highlighted and surrounded by a blue dashed border, and cell B3 appears in the formula bar rather than cell B6. You use relative cell references when you want the reference to automatically adjust when you copy or fill the formula across rows or down columns in ranges of cells.
By default, new formulas in Excel use relative references. You can also reference a range of cells in a formula, which you learn about later in this lesson. Cell referencing raises another important point about Excel. Using Absolute Cell References in a Formula Sometimes you do not want a cell reference to change when you move or copy it. USE the worksheet you modified in the previous exercise.
The formula in cell B18 now uses an absolute cell reference to cell B3. The displayed result is , which matches B Copy cell B21 to cell C The displayed result is still Figure shows the formula for cell C Regardless of where you copy the formula in the worksheet, the formula still refers to cell B3. Figure An absolute cell reference An absolute cell reference contains two dollar signs.
Cells B21 and C21 show the results of the same absolute cell reference. SAVE the workbook. An absolute cell reference refers to a specific cell or range of cells regardless of where the formula is located in the worksheet. Absolute cell references include two dollar signs in the formu- la, preceding the column letter and row number. When you copy the formula to any other cell in the worksheet, the absolute reference will not adjust to the destination cells.
Using Basic Formulas 55 Using Mixed Cell References in a Formula You can also create a mixed reference in which a column or a row is absolute, and the other is relative. The formula in cell B21 now uses a mixed cell reference. The displayed result is , which is different from the result in B The dollar sign before the B in the formula is absolute, but the row number is relative. Figure A mixed cell reference A mixed cell reference contains one dollar sign.
Cell C22 shows the result of a mixed cell reference. Delete the contents of cell B21, cell C21, and cell C A mixed cell reference is a cell reference that uses an absolute column or row reference, but not both. In the exercise, the column portion of the cell reference is absolute and remains unchanged in the formula regardless of where the formula is copied.
The row portion of the formula is relative no dollar sign precedes the row number of 3 , so that part of the cell reference changes when the cell is copied. The column reference remained the same because that portion of the formula is absolute.
The row reference adjusted because it is relative. Howev- er, you can also refer to cells in another worksheet in the same workbook or to another workbook entirely.
References to cells located in a separate workbook are considered external references. Unless you specify another worksheet or workbook, Excel assumes your cell references are to cells in the current worksheet. Referring to Data in Another Worksheet An external reference refers to a cell or range in a worksheet in another Excel workbook, or to a defined name in another workbook.
You learn how to define range names later in this lesson. You might need to use this strategy, for example, to create a summary of data in one worksheet based on data in another worksheet. The basic principles for building these formulas are the same as those for building formulas referencing data within a worksheet.
Comments
Post a Comment