Considerations for Formatting Numbers in Excel: Fixed vs. Floating Dollar Signs, Currency, and Percent

Excel’s strength lies in its handling of numbers and the presentation of them. Discuss some of the considerations to keep in mind when formatting numbers. In the discussion, including the terms fixed dollar sign, floating dollar sign, currency, and percent.

ANS: Excel displays numbers using the Accounting Number Format with a dollar sign to the left of the number, inserts a comma every three positions to the left of the decimal point, and displays numbers to the nearest cent (hundredths place).

Clicking the Accounting Number Format button on the Ribbon assigns the desired accounting number format. When you use the Accounting Number Format button to assign the accounting number format, Excel displays a fixed dollar sign to the far left in the cell, often with spaces between it and the first digit. The Accounting Number Format button assigns a fixed dollar sign to the numbers. To assign a floating dollar sign that appears immediately to the left of the first digit with no spaces, use the Currency Style in the Format Cells dialog box. The Comma Style format is used to instruct Excel to display numbers with commas and no dollar signs. The Comma Style format, which can be assigned to a range of cells by clicking the Comma Style button on the Ribbon, inserts a comma every three positions to the left of the decimal point and causes numbers to be displayed to the nearest hundredths. You can choose from 12 categories of formats. Once you select a category, you can select the number of decimal places, whether or not a dollar sign should be displayed, and how negative numbers should appear. Selecting the appropriate negative numbers format is important because doing so adds a space to the right of the number in order to align the numbers in the worksheet on the decimal points. Some of the available negative number formats do not align the numbers in the worksheet on the decimal points. The Percent Style button instructs Excel to display a value as a percentage, determined by multiplying the cell entry by 100, rounding the result to the nearest percent, and adding a percent sign.

Order custom essay Considerations for Formatting Numbers in Excel: Fixed vs. Floating Dollar Signs, Currency, and Percent with free plagiarism report

450+ experts on 30 subjects Starting from 3 hours delivery

Get Essay Help

TOP: Critical Thinking 

Discuss how to use conditional formatting, which is the feature of applying conditional operators to a cell, range, worksheet, or workbook.

ANS: Excel allows applied formatting to appear only when the value in a cell meets conditions specified by the user.

This type of formatting is called conditional formatting. Any format can be conditionally assigned to a cell, a range of cells, a worksheet, or an entire workbook. If the value of the cell changes and no longer meets the specified condition, Excel suppresses the conditional formatting. Usually, conditional formatting is applied to a range of cells that contains values to highlight, if conditions warrant. For example, instructing Excel to change the color of the background of a cell if the value in the cell meets a condition, such as being less than 0. A condition, which is made up of two values and a relational operator, is true or false for each cell in the range. If the condition is true, then Excel applies the formatting. If the condition is false, then Excel suppresses the formatting. What makes conditional formatting so powerful is that the cell’s appearance can change as you enter new values in the worksheet. Conditional formatting (relational) operators include cell values that are: between or not between two numbers; equal to or not equal to a number; greater than or less than a number; greater than or equal to a number; and less than or equal to a number.

TOP: Critical Thinking

Discuss the relationship between debugging and displaying or printing the version of the formula of a worksheet. Also, describe the difference between the formulas version and the values version.

ANS: The values version of the worksheet shows the results of the formulas entered, rather than the actual formulas. Excel also can display and print the version of the formula of the worksheet, which shows the actual formulas entered, rather than the resulting values. Toggling between the values version and formulas version is possible by holding down the CTRL key while pressing the ACCENT MARK (`) key. The formulas version is useful for debugging a worksheet. Debugging is the process of finding and correcting errors in the worksheet. Viewing and printing the formulas version instead of the values version makes it easier to see any mistakes in the formulas. When changing from the values version to the version of the formula, Excel increases the width of the columns so the formulas and text do not overflow to adjacent cells on the right. The formulas version of the worksheet usually is significantly wider than the values version. To fit the wide printout on one page, set the orientation to landscape, and the Fit to option in the Page sheet in the Page Setup dialog box.

TOP: Critical Thinking CASE

Critical Thinking Questions Case 1 Leo, owner of Leo’s Bread Company, has recently acquired Excel 2007. He would like to set up a workbook to track sales of different types of bread to determine which types are the most popular among customers. He is particularly interested in sales of rye bread, onion rolls, and bagels. Leo uses the Accounting Number Format with the sales numbers he enters into the cells. After Leo enters the sales numbers into the cells, he decides he does not want the fixed dollar sign to appear on the far left of the cell, separated by white spaces from the actual value. He would prefer that the dollar sign appear immediately to the left of the first digit of the number.

To format the location of the dollar sign, which category on the Number tab in the Format Cells dialog box would he select?

• a. General
• b. Currency
• c. Number
• d. Accounting

The worksheets still have the default names assigned to them automatically by Excel. How would Leo rename the worksheets to Rye Bread, Onion Rolls, and Bagels?

• a. In cell A1, press ALT+ENTER keys and type the new name.
• b. Select cell A1 of the worksheet and type the new name.
• c. Double-click the worksheet tab, type the new name and press the ENTER key.
• d. Click the top center cell on the worksheet and type the new name.

She wants to improve her performance and decides to keep track of data for six months to determine if the time of day or year affects performance numbers. On a single worksheet, she labels the columns B through E as follows:

• (B) Start Time
• (C) End Time
• (D) Total Time
• (E) Distance

The rows are labeled:

1. Date
2. Maximum
3.  Average
4. Minimum.

After three months, Anna wants to know the longest total time she has run, as listed in the Total Time column. By looking at the values in the column, she finds the longest total time to be 54 minutes. Instead of manually entering that value in cell D3 (Maximum Total Time), Anna decides to use the MAX function to determine the value rather than manually entering the 54 minutes. Why did Anna prefer to use the MAX function?

• a. She believes that 54 minutes is her best running time.
• b. She believes that 54 minutes is not her best running time.
• c. The MAX function recalculates the highest value each time a new value is entered into the worksheet.
• d. The MAX function provides a constant value that will not change for this cell when a new value is entered into the worksheet.

When she sets up the worksheet, Anna wants to know the average distance she runs. What should she do in cell E4 to determine the average distance?

• a. Enter the text =av and then select the AVERAGE function from the AutoComplete list; then enter the cell range.
• b. Enter the text =function and then select the AVERAGE function from the AutoFunction list; then enter the cell range.
• c. Enter the text E2: E9 and then enter the cell range.
• d. Enter the text E2: E9/9 and then enter the cell range.

Don't let plagiarism ruin your grade

Run a free check or have your essay done for you

Check my essay Hire Writer