Accounting in excel format

Format numbers as currency

If you want to display numbers as monetary values, you must format those numbers as currency. To do this, you apply either the Currency or Accounting number format to the cells that you want to format. The number formatting options are available on the Home tab, in the Number group.

In this article

Format numbers as currency

You can display a number with the default currency symbol by selecting the cell or range of cells, and then clicking Accounting Number Format in the Number group on the Home tab. (If you want to apply the Currency format instead, select the cells, and press Ctrl+Shift+$.)

If you want more control over either format, or you want to change other aspects of formatting for your selection, you can follow these steps.

Select the cells you want to format

On the Home tab, click the Dialog Box Launcher next to Number.

Tip: You can also press Ctrl+1 to open the Format Cells dialog box.

In the Format Cells dialog box, in the Category list, click Currency or Accounting.

In the Symbol box, click the currency symbol that you want.

Note: If you want to display a monetary value without a currency symbol, you can click None.

In the Decimal places box, enter the number of decimal places that you want for the number. For example, to display $138,691 instead of $138,690.63 in the cell, enter 0 in the Decimal places box.

As you make changes, watch the number in the Sample box. It shows you how changing the decimal places will affect the display of a number.

In the Negative numbers box, select the display style you want to use for negative numbers. If you don’t want the existing options for displaying negative numbers, you can create your own number format. For more information about creating custom formats, see Create or delete a custom number format.

Note: The Negative numbers box is not available for the Accounting number format. That’s because it is standard accounting practice to show negative numbers in parentheses.

To close the Format Cells dialog box, click OK.

If Excel displays ##### in a cell after you apply currency formatting to your data, the cell probably isn’t wide enough to display the data. To expand the column width, double-click the right boundary of the column that contains the cells with the ##### error. This automatically resizes the column to fit the number. You can also drag the right boundary until the columns are the size that you want.

Remove currency formatting

If you want to remove the currency formatting, you can follow these steps to reset the number format.

Select the cells that have currency formatting.

On the Home tab, in the Number group, click General.

Cells that are formatted with the General format do not have a specific number format.

What’s the difference between the Currency and Accounting formats?

Both the Currency and Accounting formats are used to display monetary values. The difference between the two formats is explained in the following table.

When you apply the Currency format to a number, the currency symbol appears right next to the first digit in the cell. You can specify the number of decimal places that you want to use, whether you want to use a thousands separator, and how you want to display negative numbers.

Tip: To quickly apply the Currency format, select the cell or range of cells that you want to format, and then press Ctrl+Shift+$.

Like the Currency format, the Accounting format is used for monetary values. But, this format aligns the currency symbols and decimal points of numbers in a column. In addition, the Accounting format displays zeros as dashes and negative numbers in parentheses. Like the Currency format, you can specify how many decimal places you want and whether to use a thousands separator. You can’t change the default display of negative numbers unless you create a custom number format.

Tip: To quickly apply the Accounting format, select the cell or range of cells that you want to format. On the Home tab, in the Number group, click Accounting Number Format . If you want to show a currency symbol other than the default, click the arrow next to the Accounting Number Format button and then select another currency symbol.

Create a workbook template with specific currency formatting settings

If you often use currency formatting in your workbooks, you can save time by creating a workbook that includes specific currency formatting settings, and then saving that workbook as a template. You can then use this template to create other workbooks.

Create a workbook.

Select the worksheet or worksheets for which you want to change the default number formatting.

How to select worksheets

Click the sheet tab.

If you don’t see the tab that you want, click the tab scrolling buttons to display the tab, and then click the tab.

Two or more adjacent sheets

Click the tab for the first sheet. Then hold down Shift while you click the tab for the last sheet that you want to select.

Two or more nonadjacent sheets

Click the tab for the first sheet. Then hold down Ctrl while you click the tabs of the other sheets that you want to select.

All sheets in a workbook

Right-click a sheet tab, and then click Select All Sheets on the shortcut menu.

Tip When multiple worksheets are selected, [Group] appears in the title bar at the top of the worksheet. To cancel a selection of multiple worksheets in a workbook, click any unselected worksheet. If no unselected sheet is visible, right-click the tab of a selected sheet, and then click Ungroup Sheets.

Select the specific cells or columns you want to format, and then apply currency formatting to them.

Make any other customizations that you like to the workbook, and then do the following to save it as a template:

Save the workbook as a template

If you’re saving a workbook to a template for the first time, start by setting the default personal templates location :

Click File, and then click Options.

Click Save, and then under Save workbooks, enter the path to the personal templates location in the Default personal templates location box.

This path is typically: C:\Users\Public Documents\My Templates.

Once this option is set, all custom templates you save to the My Templates folder automatically appear under Personal on the New page ( File > New).

Click File, and then click Export.

Under Export, click Change File Type.

In the Workbook File Types box, double-click Template.

In the File name box, type the name you want to use for the template.

Click Save, and then close the template.

Create a workbook based on the template

Click File, and then click New.

Double-click the template you just created.

Excel creates a new workbook that is based on your template.

Источник

Top Excel Templates for Accounting

December 29, 2015

In this article, you’ll find the most comprehensive list of free, downloadable accounting templates for a variety of use cases.

Accounting Journal Template

An accounting journal is an accounting worksheet that allows you to track each of the steps of the accounting process, side by side. This accounting journal template includes each step with sections for their debits and credits, and pre-built formulas to calculate the total balances for each column.

We’ve also included links to similar accounting templates in Smartsheet, a spreadsheet-inspired work management tool that makes accounting processes even easier and more collaborative than Excel.

Accounts Payable Template

When making large purchases for items like inventory, supplies, or equipment it may be necessary to do so on credit, which could result in multiple monthly payments made to different vendors or suppliers, due on different dates. Using this accounts payable template will help to keep track of what you owe to each party, and will provide a quick look at the total outstanding balances and due dates.

Accounts Receivable Template

Every company should have a process in place to manage the outstanding balances owed to them. Using this accounts receivable template will help streamline the process by providing a place for you to track the amounts due to your company and help prioritize collection efforts.

Bill To Invoice Template

For any company providing goods or services, using an invoice that looks professional and can be customized to fit your needs, is important. This simple bill template will help you get started quickly. Add your company details and payee information, provide an itemized list of the description, quantity, and price of each item you are charging for, and include directions on how your customer may remit payment.

Bill of Lading Template

A bill of lading is a document detailing how goods are being shipped from a seller to a recipient. It includes details about the items being shipped, the quantity of items included in the shipment, and the destination address. Use a bill of lading template to ensure you complete this document for each shipping transaction. This template includes a signature section that should be signed by you, then the shipping company, and finally the recipient, so that if the shipment is lost, the signature detail will help identify at what point it was lost and who was liable.

Billing Statement Template

A billing statement is helpful if you receive regular bi-monthly or monthly payments from your customers. Use this billing statement template to track customer invoices, account details, and billing status, all in one location. Additionally, this template looks professional and is customizable to match your needs.

Cash Flow Statement Template

A cash flow statement is important to provide a good picture of the inflow and outflow of cash within your company. It shows where the money came from (cash receipts) and where the money went to (cash paid). Use a cash flow statement template, in conjunction with your balance sheet and income statement, to provide a comprehensive look into the financial status of your company. This cash flow template includes two additional worksheets to track month-to-month and year-to-year cash flow.

Cash Flow Forecast Template

Creating a cash flow forecast can be helpful for managing your business’ finances. It enables you to estimate how much money your business will make and spend at any given point, and will allow you to take the appropriate steps to ensure that your cash outflow is not more than your inflow. Use a simple cash flow forecast template to get started quickly. Be sure you include all income including revenue and investments, and account for all expenses including fixed costs.

Expense Report Template

A simple expense report is helpful to keep track of business expenses for an individual, department, project, or company, and provides a quick way to document and track expense details. You can require that your team submit monthly expense reports or as the expenses are accrued. Use this expense report template to quickly input specific expense details and obtain approvals as needed.

Income Statement Template

An income statement, or profit and loss statement, provides a look into the financial performance of a company over a period of time. The statement provides a summary of the company’s revenue and expenses, along with the net income. Use this income statement template to create a single-step statement that groups all revenue and expenses, and is helpful for businesses of all sizes.

Payment Schedule Template

You will likely have multiple bills to pay in a month, to different companies and on different dates. It is important to have a way to track when specific bills are due, the amount that is due, and to whom. Use a simple payment schedule template to track these details. This payment schedule template will help you remember when each bill is due and be able to budget accordingly.

Simple Balance Sheet Template

A simple balance sheet template provides a quick snapshot of a company’s financial position, at a given moment. Use this balance sheet template to summarize the company’s assets, liabilities, and equity, and give investors an idea of the health of the company.

Travel Itinerary Template

Whenever you or your team are scheduled for business trips, it’s helpful to have a travel itinerary that lists the details for transportation, lodging, car rentals, meetings and more. Use a simple business travel itinerary template to keep all of these details in one location, and be able to share the details with important stakeholders.

Save Hours of Manual Work with Smartsheet

Empower your people to go above and beyond with a flexible platform designed to match the needs of your team — and adapt as those needs change.

The Smartsheet platform makes it easy to plan, capture, manage, and report on work from anywhere, helping your team be more effective and get more done. Report on key metrics and get real-time visibility into work as it happens with roll-up reports, dashboards, and automated workflows built to keep your team connected and informed.

When teams have clarity into the work getting done, there’s no telling how much more they can accomplish in the same amount of time. Try Smartsheet for free, today.

Источник