MARISSA JONES ONLY!!!!

Round a Value with a Function

Learning Outcomes

· Use Formula AutoComplete to insert a function

· Copy an edited formula

The more you explore features and tools in Excel, the more ways you’ll find to simplify your work and convey information more efficiently. For example, cells containing financial data are often easier to read if they contain fewer decimal places than those that appear by default. You can round a value or formula result to a specific number of decimal places by using the ROUND function.  In your worksheet, you’d like to round the cells showing the 20% rise in expenses to show fewer digits; after all, it’s not important to show cents in the projections, only whole dollars. You want Excel to round the calculated value to the nearest integer. You decide to edit cell B14 so it includes the ROUND function, and then copy the edited formula into the other formulas in this row.

Steps

1. 1

Click cell B14, then click to the right of = in the formula bar

You want to position the function at the beginning of the formula, before any values or arguments.

Quick Tip

In the Insert Function dialog box, the ROUND function is in the Math & Trig category.

2. 2

Type RO

Formula AutoComplete displays a list of functions beginning with RO beneath the formula bar.

3. 3

Double-click ROUND in the functions list

The new function and an opening parenthesis are added to the formula, as shown in  Figure 2-19 . A few additional modifications are needed to complete your edit of the formula. You need to indicate the number of decimal places to which the function should round numbers, and you also need to add a closing parenthesis around the set of arguments that comes after the ROUND function.

Figure 2-19ROUND Function Added to an Existing Formula

Trouble

If you have too many or too few parentheses, the extraneous parenthesis is displayed in red, or a warning dialog box opens with a suggested solution to the error.

4. 4

Press [END], type ,0), then click the Enter button  on the formula bar

The comma separates the arguments within the formula, and 0 indicates that you don’t want any decimal places to appear in the calculated value. When you complete the edit, the parentheses at either end of the formula briefly become bold, indicating that the formula has the correct number of open and closed parentheses and is balanced.

5. 5

Drag the fill handle from cell B14 to cell E14

The formula in cell B14 is copied to the range C14:E14. All the values are rounded to display no decimal places. Compare your worksheet to  Figure 2-20 .

Figure 2-20Completed Worksheet

6. 6

Scroll down so row 25 is visible, click cell A25, type your name, then click 

7. 7

Save your work, preview the worksheet in the Print place in Backstage view, then submit your work to your Instructor as directed

8. 8

Exit Excel

Using Auto Fill options

When you use the fill handle to copy cells, the Auto Fill Options button appears. Auto Fill options differ depending on what you are copying. If you had selected cells containing a series (such as “Monday” and “Tuesday”) and then used the fill handle, you would see options for continuing the series (such as “Wednesday” and “Thursday”) or for simply pasting the copied cells. Clicking the Auto Fill Options button opens a list that lets you choose from the following options: Copy Cells, Fill Series (if applicable), Fill Formatting Only, Fill Without Formatting, or Flash Fill. Choosing Copy Cells means that the cell’s contents and its formatting will be copied. The Fill Formatting Only option copies only the formatting attributes, but not cell contents. The Fill Without Formatting option copies the cell contents, but no formatting attributes. Copy Cells is the default option when using the fill handle to copy a cell, so if you want to copy the cell’s contents and its formatting, you can ignore the Auto Fill Options button. The Flash Fill option allows you to create customized fill ranges on the fly, such as 2, 4, 6, 8, 10, by entering at least two values in a pattern: Excel automatically senses the pattern.

Creating a New Workbook Using a Template

Excel templates are predesigned workbook files intended to save time when you create common documents such as balance sheets, budgets, or time cards. Templates contain labels, values, formulas, and formatting, so all you have to do is customize them with your own information. Excel comes with many templates, and you can also create your own or find additional templates on the web. Unlike a typical workbook, which has the file extension .xlsx, a template has the extension .xltx. To create a workbook using a template, click the File tab, then click New on the navigation bar. The New place in Backstage view displays thumbnails of some of the many templates available. The Blank workbook template is selected by default and is used to create a blank workbook with no content or special formatting. To select a different template, click one of the selections in the New place, view the preview, then click Create.  Figure 2-21  shows an example. (Your available templates may differ.) When you click Create, a new workbook is created based on the template; when you save the new file in the default format, it has the regular .xlsx extension. To save a workbook of your own as a template, open the Save As dialog box, click the Save as type list arrow, then change the file type to Excel Template.

Figure 2-21Previewing the Budget Planner Template