Microsoft Office 2016: The Complete Guide (2015)

FORMAT NUMBERS AS CURRENCY IN EXCEL 2016

Numbers that should be shown as monetary values must be formatted as currency. This can be achieved by using either the Currency or Accounting format for your data, to the cells that you want to change. These options can be found by going to the Home tab and selecting the options from the Number group.

Format numbers as currency

Go to the Home tab and select the Number group. After highlighting the cell or number of cells and selecting this option you can show a number with the default currency symbol. You can show a number with the original currency sign by selecting the cell or series of cells, and then clicking Accounting Number Format. The currency format can be selected an another option by pressing the Ctrl+Shift+$.

How to change other features of formatting

Select the areas that you want to edit

Go to the Home tab, and select the Dialog Box Launcher which is located next to the Number option.

TIP Ctrl+1 is a shorter method to access the Format Cells dialog box.

Go to the Format Cells dialog box and under the Category option select Currency or Accounting.

Select the currency symbol of your choice by going to the Symbol box.

You should note that in order to show the monetary value without the currency symbol, you must select the option, None.

Go to the Decimal places box, to input how many decimal places should be included in the number.

For example, if you want to have a figure rounded off, go to the Decimal places box and enter a 0. That indicates that you do not wish to have any decimal places in the number. The digit in the Sample box will change as you apply the changes. It displays how the changes you have made will affect the display of a number.

To display the style for numbers that are negative, go to the Negative numbers box, and select your option. You can also create your own number format if the existing options are not suited to your needs.

It is important to note that in the Accounting number format, the Negative numbers box will not appear. This is so because it is a default setting in accounting practices for negative numbers to be displayed in parentheses.

Select the option OK to close the dialog box, Format Cells.

If the cell is too narrow to display the data after you have applied the relevant currency formatting, it will show #####. To remove this, simply make your cells wider by highlighting the right boundary of the column with the error to automatically fit the contents in the cell. You may also drag the corner of the cell until the column is the suitable size.

Remove currency formatting

Highlight the cells that contain the currency formatting.

Go to the Home tab and under the Number group, select General from the list of available options.

How to identify the difference between Currency and Accounting Formats

You may have noticed that Excel has both the Currency and Accounting as options to display monetary values. Though both have similarities, it is important to know when to use them and how they differ.

Currency -When you choose the Currency format for a number, the currency symbol comes up right beside the first value in the number. You can choose how many decimal places you want to use and whether the number should be separated by a thousand separators. You also have the option to select how negative numbers are displayed.

A quick tip to note is that a shorter way to apply the Currency format, is to simply choose the cell or series of cells that you want to format, and then press Ctrl+Shift+$.

AccountingSimilarly to the Currency format, the Accounting format is used for monetary values. However, with this format the currency symbol and decimal points are aligned in a column. Additionally, zeros are shown as dashes when the Accounting format is applied. As earlier stated this format displays negative numbers in parentheses. Similarly, the Currency format, you can choose how many decimal places you want to use and whether the number should be separated by a thousand separators. A negative display of numbers cannot be changed unless you go in and create an original number format.

TIP It should be noted that you can do this in a more condensed step by selecting the cell or series of cells that you wish to have formatted and then go to the Home tab and select the Number group and click the option, Accounting Number Format. If you want to show a currency symbol other than the original one, click the arrow next to the Accounting Number Format button, and then select another currency symbol.

How to create a workbook template with explicit currency formatting settings?

You may opt to create a workbook with specific currency formatting options, if you frequently use currency formatting in your workbook. By doing so, you can save the workbook as a template, which can then be used when creating other workbooks.

How to generate a workbook template?

Generate your workbook.

Then choose the spreadsheets for which you want to change the original formatting.

To select one sheet only select the sheet tab. Select the tab scrolling keys to display a tab if you do not see the tab that you want.

To select more than two sheets that are next to each other select the tab for the original sheet, and then hold down the Shift key while you select the tab for the last sheet that you want to choose.

To select more than two sheets that are not next to each other select the tab for the first sheet, and then hold down the key Ctrl while you select the tabs of the other sheets that you want to select.

To select all the sheets in a workbook Right-click a sheet tab, and then select, Select All Sheets.

Tip When numerous worksheets are selected, the word GROUP appears in the heading bar at the top of the worksheet. To stop a selection of multiple worksheets in a workbook, choose a sheet that has been unselected. If you are unable to find an unselected sheet, simply go on the tab of a selected sheet and select the option Ungroup Sheets.

Apply the currency formats to the relevant sheets or columns.

Apply additional customizations that you may desire to your workbook and then save it as a template.

How to set the default personal templates location?

Select File, and then choose Options.

Go to Save, and then under Save workbooks option, go to the Default personal templates location box and input the link to the path to the personal templates location. The link generally appears in this format: C:\Users\Private Documents\My Templates.

Then select OK.

How to save the workbook as a template?

Select the option File, and then choose the Export option.

A list of options will appear under Export; from which you choose Change File Type.

Double-Click on the Template Option in the Workbook File Types box.

Go to the File name box, and insert the name that you want to use as the label for the template.

Select Save, and then close the template.

Create a workbook based on the template

Select File, and then choose New.

Next select the option Personal.

Select the template that you have just made.

Excel will generate new workbook with features and formatting from your template.