In this post, you will learn everything you need to know about adding accounting format to numeric values in Google Sheets.

There are two data types available in Google spreadsheets, text and numeric data. Each data type has different formatting option.

There are many different number formatting options available in spreadsheets. This is so because numbers can be used to represent many things.

Dates, percentages, and monetary values are all numbers, but they are represented differently based on their format. Number formatting allows you to present numbers appropriately. Using the right number format also determines the way computations will be carried out on them.

Accounting format is one such number formatting available in spreadsheets. It is appropriate for use when working with monetary values that involve accounting calculations.

The accounting format is different because the way numbers are represented in accounting calculations is different from how they’re represented in other mathematical calculations.

For instance, in mathematics, negative numbers are written with a dash in front of them. In accounting, they’re encased in parentheses.

Other features of the accounting format include the alignment of currency symbols, decimal points, and the display of zeros as dashes.

There are many different ways you can apply accounting format to your numbers. This post will show you all the different ways you can add accounting format to your numbers in Google Sheets.

Get your copy of the example workbook and follow along!

Add Accounting Format from the Format Menu

This method involves using the spreadsheet’s built-in accounting format. It’s a straightforward easy-to-use method for adding accounting format.

Follow these steps to use the accounting format in Google Sheets.

1. Select the cells containing the values you want to format.
2. Go to the menu bar and click on the Format menu.
3. Click on Number from the dropdown options.
4. Select Accounting from the submenu list of options.

As seen above, the values have been transformed into accounting style numbers.

This is the perfect format for your running total columns or other financial calculations.

You will notice several features of the accounting number format.

• The decimals are aligned the same across each cell.
• The zero value is replaced with a dash.
• The negative value is enclosed in parenthesis.
• The dollar symbol has been added to all of the values.
• The dollar symbol is left aligned and the number is right aligned.

Surely this is better than looking at raw numbers without this extra detail! Right? It makes comparing numbers much easier.

Now, when you do any computation on these values using formulas, your results will be displayed in this exact format.

But there’s more. You’re not bound or restricted to this style. In other words, you can change some of the details in this formatting style.

Let’s say you want to change the currency symbol.

Follow the steps you did when choosing the accounting format option. However, this time, select the Custom currency option from the menu.

This will open the Custom currencies dialogue box.

You can type the name or symbol of your preferred currency into the search bar. As you make these changes, the preview box beside the search bar will show you how your changes will appear.

Clicking on the preview box also gives you options regarding the position of the symbol and what type of symbol for the selected currency you want to use.

You also have the option of using the name or symbol. So you could show GBP instead of the £ symbol.

When you’re done with these tweaks, click on Apply.

As you see, using this solution will alter some of the accounting format features.

In fact, the only similarity is the alignment of the decimal point. Negative numbers are not enclosed in parenthesis, zeros are not replaced with dashes, and the currency symbol is not left-aligned.

If you want to change the default currency while keeping the accounting format features, one alternative will be to change the default locale of your spreadsheet to the locale of the country’s currency that you want to use.

From the File menu, select Settings.

In the dialogue box that pops up, change the Locale to your preferred locale. This will change the default currency symbol.

When you’re done, click on Save settings. You would have changed the currency symbol and also kept the accounting format.

You should note that any change you make here will affect your entire spreadsheet. This means other formatting details such as dates and functions will change according to the new locale.

Another thing you can change is the number of decimal places. You can do this by using the Increase decimal places and Decrease decimal places commands from the toolbar.

To do this, simply select the column where you want to add more decimal places and click on the Increase decimal places icon on the toolbar.

If you want to increase the decimal places by 3, you will click on the Increase decimal places icon three times. The number of decimal places you want is the number of times you click the icon.

The same thing also goes when using the Decrease decimal places icon.

Being able to make these changes is awesome, but you can create these formats and apply these changes at once, and use the created format anytime. This is made possible through the Custom Number Format feature.

Here’s how to use the Custom Number Format feature.

Add Accounting Format with Custom Number Format

The Custom number format is a feature that allows you to define the parameters of your number format.

This means you can define the number of decimal places and the currency you want to use. You can also define how negative numbers are displayed, and even add color.

The Custom number format gives you a lot of flexibility over the display of each character in your numeric values. It will save you the time spent on altering pre-set accounting format repeatedly as you can do it all at once with the Custom number format.

You can access the Custom number format option the same way you accessed the accounting format.

1. Select the cells to format.
2. Go to the Format menu.
3. Select Number from the options.
4. Select Custom number format from the submenu options.

You can also access the Custom number format by using the More formats icon in the toolbar menu.

On selecting the Custom number format option, the subsequent dialogue box you see has a Format Editor box where you can input the format you want using special characters.

Immediately below this box is a Format preview where the effect of the entered format on numbers is displayed. It shows you how your defined format will appear when applied.

The last box provides a list of pre-set formats from which you can choose.

You will have noticed that format instruction is given using specific symbols. Whether you want to define your own format, or you want to choose from the list, you should understand what each symbol means and how it affects the display of numbers.

First, you should know that the Custom number format allows you to specify formatting for four categories of values: positive, negative, zero, and text values.

When writing your formatting instruction, use a semi-colon (;) to separate formatting for each value in the order shown above.

1. Positive: This section is where you define formatting rules for positive values.
2. Negative: This part is where you specify formatting rules for negative values.
3. Zero: This section is where you define the formatting rules for zero values.
4. Text: This section is where you can define formatting rules for when the cell contains a text value instead of a number.

You don’t have to use all these rules, you’ll only use the first three rules if you want an accounting format.

Understanding Custom Number Format Symbols

Zero “0”

This is used to specify the maximum number of digits you want to be displayed.

It forces the display of insignificant zeros when there are fewer digits than specified in the format rule. You can also use it to display preceding zeros.

Pound Sign “#”

The pound sign also works like the zero symbol. However, it only displays relevant digits. So, if your value has fewer numbers than specified in the format, the pound sign won’t display anything.

Comma or Thousand Separator “,”

The comma is used for applying a thousand separators to your format. It works for numbers in the thousand and million range.

You can also create abbreviated numbers by adding letters in double quote marks after the thousand separators. If you don’t add formatting after the comma, the thousand values will be removed.

Period “.”

The period symbol is used to add a decimal point to your values. Adding a period to your format rule will cause the decimal point to display even if there are no values after the decimal point.

Parenthesis “( )”

Parenthesis can be used to encase numbers. These are typically used to format negative numbers as seen in the pre-set accounting format to display a number like -200 as (200).

Question Mark “?”

This is used for aligning values correctly even when the digits have different lengths.

Bracket “[ ]”

These are used to specify colors for values.

Currency “$”, “€”, “£”, “¥”

You can use this in your format rule to add currency symbols when you’re working with monetary values.

Percentage “%”

The percentage % symbol is used to format numbers as percentages. It multiplies the value by 100 and adds % behind the result.

Underscore “_”

The underscore “_” is used for adding spaces just like the question mark symbol.

It doesn’t align values, it uses the size of the preceding character to determine the size of the space it will add. You can use it to specify how many spaces you want.

Forward Slash “/”

The forward slash symbol “/” is used for formatting decimal numbers as fractions.

Asterisk “*”

When you add an asterisk to your format rule, it repeats the character that comes after until it fills the cell width.

The space that follows the asterisk is repeated until it fills the cell.

Accounting Custom Number Format

With the understanding of the formatting symbols and rules, we can go ahead to change our sample data into accounting format using the Custom number format.

$* #,##.00? ; [red]$* (#,##.00)? ;$* -?

First, you will select the cells with the data you want to format, then go to the Custom number format option. In the format editor, copy and paste the above format rule and click on Apply afterward.

This should get your cells formatted as in the image above.

Although all of the currency symbols are not perfectly aligned, there’s very little difference between this and the pre-set accounting format.

The ability to add color even makes this better than what you’d get when you use the pre-set accounting format.

With the Custom number format, you don’t have to change decimal places manually or alter the locale setting of your spreadsheet just to change the currency symbol.

And the color and bracket provide more options for formatting numbers of certain values.

Add Accounting Format with the TEXT Function

There are functions available in Google spreadsheets that you can also use to add accounting format to numbers. They also use the formatting rules as found in the Custom number format option.

One of these functions is the TEXT function.

The TEXT function is used to convert numeric values to text using formatting rules.

TEXT ( number, format )

TEXT takes two arguments: number and format. Both arguments are required.

• number: This is the number to be converted. It takes values, cell references, ranges, and named ranges.
• format: This allows you to specify the formatting rules you want for the output display. It can use most of the symbols and formatting rules from the Custom number format option. Characters in the format argument must be enclosed in double-quotes characters (“).

The format argument shares some formatting rule similarities with the Custom number format.

You can identify formatting for positive, negative, zero values, and text in the same order just as it is in Custom number format. Symbols such as #, 0, * follow the same rules as well.

However, the asterisk (*) works a little differently in TEXT. The asterisk repeats the character that follows the number of times the character appears.

That is a slight difference from how it works in a Custom number format where it repeats the character that follows until it fills the cell width.

= TEXT ( A1, "$ * #,##.00;$ * (#,##.00);$ * - " )

To format these cells using TEXT, use the above syntax.

See that as you start to type into the format argument, you get a preview showing how the rule will make the number appear. You can use this as a guide to know if you’re on the right track.

When you’re done, your results should appear as shown above. Now, your numbers are perfectly formatted in the accounting style.

A drawback to using the TEXT function to format numbers is that it changes your numbers to text.

The spreadsheet reads all these new numbers as text. So, you can’t do any meaningful calculations with these numbers.

As seen in the above example, the sum of both numbers gives different results. Values in the first image are formatted as numbers while those generated with the TEXT function are formatted as text.

That is one way the Custom number format option is better because the values remain formatted as numbers, so calculations aren’t affected.

Add Accounting Format with the QUERY Function

Another function you can use to format numbers is the QUERY function.

The QUERY function is perhaps the most powerful function in Google Sheets. QUERY brings SQL queries into Google Sheets.

You can query data as it’s done using a database programming language. It supports the use of SQL-like arithmetic functions, aggregate functions, and clauses.

There are a lot of things QUERY can do in spreadsheets, but we will stick with using it to change numbers to accounting format.

QUERY ( data, query, [header] )

QUERY function takes three arguments, one of which is optional.

• data: This is used to tell the function of the dataset that is about to be queried. The argument works with cells, named ranges, and cell ranges.
• query: This tells the function what you want from the data. The query argument are entered in double-quotes.
• header: This argument is optional. It allows you to use numbers to specify how many rows the function should use as headers. The argument takes numeric values. When left blank, QUERY will use its best guess to identify the column headers in the data.

When using the QUERY function to format numbers, you must make sure your values are formatted as numbers. This will allow the QUERY function to identify the values and apply the formatting rules accordingly.

To add number formatting, use the following steps.

1. Select the cells on which you want to add the number format.
2. Go to the menu bar and select Format.
3. From the dropdown list, select Number.
4. Choose Number from the next dropdown menu.

The numbers will be formatted as above after you choose Number.

When you’re done with this, you can go ahead to use the QUERY function to add an accounting format.

= QUERY ( A1:A5, "format A '$* 0.00;$* (0.##);$* -'" )

Select a blank cell, and add the above formula.

When you hit Enter, all the blank cells are populated. This is because the query was carried out in column A.

Notice how the formatting rules are also similar to the ones from the Custom number format.

Additionally, QUERY produces results that are exactly the same as what you get when you use the accounting format option from the Format menu.

Using QUERY to add an accounting format is a much superior alternative to the TEXT function for one reason. The results are formatted as numbers and can therefore be used in further calculations!

Conclusions

It is easy for you to change your numbers to accounting format.

For less elaborate accounting format needs, the accounting format from the Format menu will do just fine.

With the other methods, you have total control over how your values appear. They also give you the ability to add some extra detail to your format when you understand the formatting symbols and rules.

Once you learn how the formatting rules work, you can tweak them as necessary to get the exact formatting you need.

Which method of applying accounting format do you like the most? What other methods do you know of? Let us know in the comments below!