Inventory control. How to keep inventory records in excel

Most small and medium-sized commercial organizations solve the problem of accounting for material values ​​(goods or products) using Microsoft Excel. In this article we will look at an example on this topic from real practice. The completed automation of the Microsoft Excel workbook allows you to reduce the number of routine actions in office work, as well as to ensure control of possible user errors.

Let's consider a fairly standard situation when our organization is engaged in trade - the purchase of goods and their subsequent sale. In this case, it is necessary to keep accurate records of the nomenclature (quantity of goods, prices of receipt and sale). Let's start our development with a sheet called Nomenclature(fig. 2.16). This sheet plays the role of a register or reference book of goods. Each line contains information about a specific item of the item (product name, receipt price, sales price, and quantity on hand). The goal of software development is to create control sheets that will allow you to adjust the quantity of goods when they are received or shipped, as well as to make changes in the price of goods. In general, an automated workbook should provide a user-friendly interface for managers. This interface will provide two sheets - Admission and Shipment.

Directly working with the sheet shown in fig. 2.16 is deprecated. Of course, the manager, when goods are received or sold, can directly enter data into it. For example, when a new batch of “Comfort” walls arrives in the amount of 5 pieces at a new price of 25,770 rubles, you can simply adjust both the number of units in the warehouse and the price of receipt. However, this is not very convenient and is a "springboard" for possible errors on the part of the user. It is more convenient to create a control sheet (Fig. 2.17) with elements that provide a convenient interface. And the leaf itself Nomenclature we will hide it further (this feature is easily implemented using the Microsoft Excel application service), so that it will not be directly accessible to the user. Of course, after that, you can enter protection on the structure of the book (with a password). As a result, users can work with the sheet Nomenclature only using the interface implemented on other sheets.

When goods arrive that are already in stock, but at a new price, you should decide on the recalculation of the receipt price. For definiteness, let us assume that in such a case, the price of the last delivery is set as the price of goods receipt.

Let's move on to a detailed examination of the sheet shown in Fig. 2.17, which allows the manager to enter a new receipt of an existing product in a convenient form, as well as add new items of the nomenclature.

The left half of the Receipt sheet is intended for new receipts of goods that are already available in the infobase (on the sheet Nomenclature). To do this, the "Combo box" control (located in the area of ​​cell A3) is filled with the names of the goods present on the sheet Nomenclature... This action is performed when the workbook is opened (the text of the procedure required for this is shown in Listing 2.21). The code assumes that the property value Name combo box selected Spk.

"Listing 2.21. Procedure for Opening Private Sub Workbook_Open ()" Clearing the Worksheets List. Spk.Clear "Counting the number of items in the infobase N = 0 While Worksheets. Cells (N + 2, 1) .Value<>"" N = N + 1 Wend "Populating the list For i = 1 To N Worksheets. Spk.AddItem Worksheets (3) .Cells (i + 1, 1) .Value Next Worksheets. Spk.ListIndex = -1 End Sub

When the user selects the name of the product in the combo box, then in cell C5 on the sheet Admission the price is entered from the corresponding line of the sheet Nomenclature books. To do this, the procedure performed when you click on the "Combo Box" element should be formatted as shown in Listing 2.22. There is also nothing fundamentally new in it in comparison with the previously considered examples.

"Listing 2.22. Procedure for handling a click on a Private Sub combo box Spk_Click () Range (" C5 "). Value = Worksheets (3) .Cells (Spk.ListIndex + 2, 2) .Value Range (" C6 "). Value = "" End Sub

Now the user must enter the number of units of the new receipt of the specified product in cell C6. To increase the security against accidental actions, we have placed another field for the password on the sheet. This is a simple text box. Clicking on a button Submit will allow you to update the price and quantity of goods on the list Nomenclature only if the password is correct. Let's set the following properties for the text box:

• Name - Pass;
• PasswordChar - *.

This will ensure that "asterisks" are displayed when typing a password (the word typed by the user will be hidden from prying eyes). Thus, we have come to writing a procedure for entering information about a new receipt (an increase in the number of specified goods and a possible change in price) on the third sheet. Listing 2.23 shows the procedure for clicking the button Submit... The combination 357 is considered here as a password.

"Listing 2.23. Handling a click on the Add Private Sub button CommandButton1_Click () If Pass.Text =" 327 "Then" Updating the receipt price Worksheets ("Nomenclature"). Cells (Spk.ListIndex + 2, 2) .Value = Range (" C5 "). Value" (! LANG: Correction of quantity Col = Range ("C6").Value Worksheets("Номенклатура").Cells(Spk.ListIndex + 2, 4).Value = _ Worksheets("Номенклатура").Cells(Spk.ListIndex + 2, 4).Value + Col MsgBox "Данные внесены" Pass.Text = "" Else MsgBox "Ошибка пароля! Данные не внесены" End If End Sub!}

Here, when entering the password 357, the procedure will add the quantity of goods entered by the user to the third sheet and adjust the price. After that, an informational message is displayed on the screen stating that the data has been entered, and then the password field is cleared. If the password is wrong, no changes to the sheet Nomenclature not produced.

Let us now turn to the examination of the right side (in contrast to the left, it is highlighted in a darker shade) Fig. 2.17. This fragment of the sheet must be used to enter a new product (indicating the quantity). Listing 2.24 shows a procedure that performs this functional action in this case.

"Listing 2.24. Handling a click on the button Add new item Private Sub CommandButton_Click ()" Counting the number of added items on the sheet Nomenclature N = 0 While Worksheets ("Nomen"). Cells (N + 2, 1) .Value<>"" N = N + 1 Wend "Check the password entered in the right text field If Pass2.Text =" 35791 "Then Worksheets (" Nomenclature "). Cells (N + 2, 1) .Value = Range (" G3 "). Value Worksheets. Cells (N + 2, 2) .Value = Range ("G4"). Value Worksheets. Cells (N + 2, 4) .Value = Range ("G5" ) .Value MsgBox "Data entered" Pass2.Text = "" Else MsgBox "Password error! No data entered "End If End Sub

On the right side of the sheet there are two controls - a button and a text box for a password. It would be possible to leave one password field for the left and for the right side of the sheet. However, the option suggested here enhances the development functionality. As a property value Name we chose Pass2... Let's formulate the order of user actions when working with the right side of the sheet:

• the name of the new product is entered in cell G3;
• cell G4 indicates the price;
• cell G5 is reserved for the number of units of goods;
• the user enters the password, and clicking on the Add new product button fixes (if the password is correct) the entered information on the sheet Nomenclature.

At the beginning of the procedure CommandButton2_Click the number of cells with information about goods on the sheet is counted (for this, N is used) Nomenclature... After that, information about the new product is written into the next free line of the sheet.

Now let's move on to another sheet - Shipment(fig. 2.18), which is used in the sale of goods. On the sheet Shipment there are three controls: a combo box (Name - Spk), a password field (Name - Pass3) and a button (Name - CommandButton1).

To fill out the list, you need to adjust the previously described procedure (Listing 2.21). Workbook_Open... It should now look like Listing 2.25.

"Listing 2.25. Procedure for opening workbook (option 2) Private Sub Workbook_Open () Worksheets. Spk.Clear Worksheets. Spk.Clear" Counting already available items N = 0 While Worksheets ( "nomen"). Cells (N + 2, 1) .Value<>"" N = N + 1 Wend "Populating the lists For i = 1 To N Worksheets. Spk.AddItem Worksheets (3) .Cells (i + 1, 1) .Value Worksheets. Spk .AddItem Worksheets (3) .Cells (i + 1, 1) .Value Next Worksheets. Spk.ListIndex = -1 Worksheets. Spk.ListIndex = -1 End Sub

Next in line is the following procedure (Listing 2.26), which is performed when you click the combo box. As a result, to the corresponding cells of the sheet Shipment the number of units of the specified product in the warehouse will be entered, as well as its selling price (Fig. 2.19).

"Listing 2.26. Procedure for clicking the Private Sub combo box Spk_Click () Range (" E6 "). Value = Worksheets (" Nomenclature "). Cells (Spk.ListIndex + 2, 4) .Value Range (" E7 ") .Value = Worksheets (" Nomenclature "). Cells (Spk.ListIndex + 2, 3) .Value End Sub

Now the user must indicate (in cell E6) how many units are being shipped (for orientation and control, using the previous procedure, we entered the available quantity of goods in the warehouse). The procedure in Listing 2.27 (performed when you click the Ship button) allows you to adjust the number of items in stock in the database.

"Listing 2.27. Procedure to execute when the Upload button is clicked Private Sub CommandButton1_Click () If Pass3.Text =" 775 "Then ColPrais = Worksheets (" Nomenclature "). Cells (Spk.ListIndex + 2, 4) .Value Col = Range ("E6"). Value If Col> ColPrais Then MsgBox "There is no such quantity in stock" Exit Sub End If Worksheets ("Nomenclature"). Cells (Spk.ListIndex + 2, 3) .Value = Range ("E7") .Value ColPrais = ColPrais - Col Worksheets ("Nomenclature"). Cells (Spk.ListIndex + 2, 4) .Value = ColPrais MsgBox "Added information" Pass.Text = "" Spk_Click Else MsgBox "Password error!" End If End Sub

After making changes, the information on the screen will be updated - the user will see the already adjusted value (by calling the procedure Spk_Click which we have previously developed). Now, to provide the previously agreed functionality, you need to hide the sheet Nomenclature... Users in this case will use the developed sheets Admission and Shipment.

Accounting for goods requires the use of reliable and modern software that can fully provide all the necessary operations for maintaining statistics of the arrival and departure of goods to the warehouse.

Convenient cloud-based automation system for trade and warehouse accounting that replaced Excel!
Increase your efficiency and increase your profits!

In particular, MS Excel has a set of functions in order to make the accounting of goods more complete. Learn how to keep track of store items in Excel. Let's consider the main advantages and disadvantages of this program as a tool for accounting for goods.

Features of accounting of goods in Excel

Accounting for goods in Excel is carried out in special tables that allow you to select a number of separate groups of goods depending on their purpose, consumption and some other features. In particular, thanks to this software product, you can perform a number of analytical functions and sequential operations, including using formulas.

At the same time, it is important to be able to work in Excel in order to fully use all the functions necessary for accounting for the sale of goods and services, to structure and analyze data to solve various business problems.

Control of the movement of goods in the warehouse

Excel program allows you to control the movement of goods in the warehouse, their arrival and departure, through the use of special tables.

In particular, it is possible to form a table in the program with different columns, depending on the user's needs for the necessary resources and options that the program gives. In the table, it is possible to display data on the operations performed, including indicating the balance in the warehouse, both for a specific group of goods, and for the entire warehouse as a whole.

For each group of goods, a supplier is indicated so that, if necessary, you can quickly find out where this or that product or equipment came from in the warehouse. At the same time, all work is carried out sequentially, taking into account possible adjustments. In parallel with the layout of the table, it is also necessary to take into account the introduction and creation of additional documents, footnotes and attachments, for which free space must be allocated.

As a rule, detailed accounting of products available in the warehouse is due to the need to monitor the movement of inventories during trading and, in particular, the requirement of strict reporting, therefore, a qualified specialist who is fluent in MS Excel should deal with accounting.

Useful "tricks" for inventory control in Excel

The first thing that can be advised to work in the program is to use all the elements of cell formatting in tables. For example, when it is necessary to arrange the head of a table in two lines, it is important to first provide word wrap in the cell itself. This technique will help ensure clarity and a great appearance of the table.

The program also allows you to insert special fonts. Their use is appropriate when it is necessary to highlight a number of products or categories depending on certain parameters and values.

In general, the most important groups of goods must be selected by means of appropriate operations on a cell, or on a line. You can also format columns in which various groups of goods can be located, which are supposed to be processed using tables.

A modern solution for simple accounting - the online program Klass365

Unfortunately, Excel also has a number of shortcomings, sometimes drastically affecting the work with goods in the warehouse. These can be safely attributed to: too complex a set of operations, which will require a certain skill and qualifications, as well as too much time spent, sometimes simply unbearable in the face of time pressure.

Moreover, imagine how catastrophic it would be to lose data in the course of an accidental error, which occurs quite often when working with Excel. In this regard, it is necessary to find a worthy, and most importantly a free specialized program for maintaining all the necessary accounting.

The online program for accounting for goods in the warehouse Klass365 allows you to automate warehouse and trade accounting, work with online stores, a client base and at the same time not waste time on implementation and training of personnel. The simple and well-thought-out interface of the program allows you to master it in no more than 15 minutes.

• Warehouse accounting: acceptance, write-off, revaluation, etc.
• Automatic statement of accounting and accompanying documentation
• Work with an unlimited number of warehouses
• Storage time control
• Inventory planning based on sales rates
• Reports for any period and area of ​​work in 1 click

It is not difficult to guess that accounting in Excel spreadsheets is a thing of the past and a more convenient and functional program comes to replace it - the automated business management system Klass365. The free online solution allows you to work in any convenient place, without being tied to a work computer. Use modern solutions for your business and get stunning results!

The program is designed for material accounting of various types of resources. The implementation of accounting in value terms is possible. Many storage locations are supported, consumption for production and write-off to scrap. Reporting forms allow you to track the movement of resources and receive operational information for each point of accounting.

Introduction

The program can be used as the simplest inventory control system for the storage locations of resources at enterprises using a small range of goods, raw materials and materials. Accounting is carried out in natural units. Cost accounting in automatic mode is not implemented, but there is a possibility of manual input of indicators in monetary units.

The initial data for generating reports are three types of tables:

• Parameters
• Directories
• Magazines

Moreover, the reports themselves are also Excel tables.

The openness of the spreadsheet interface provides maximum flexibility in calculations. The user has access to all the functions of a powerful computing device and the Excel interface. The peculiarities of the program implementation presuppose independent customization of logs and reports for specific user tasks.

Changes in version 2.6

• Since version 2.6, the old XLS workbook format is not supported. The program file is saved in the format XLSX / XLSM and works in Excel since 2007 ( 2007-2016 ). Program in format XLS are only available in version 2.0, and can only be purchased on special request.
• Updated the look of workbooks to reflect the design of the latest versions of Microsoft Office.
• Second summary report for storage locations.
• The default view of the first report has been converted to allow copying and pasting of the balances at the beginning of a new period.
• Drop-down list for searching dictionary elements by the first letters of a word. It is activated by double clicking on the cell.
• Red highlighting of journal items not found in directories.

Changes in version 2.0

• Accounting points and counterparties are divided according to different directories.
• The limitation on the number of suppliers and buyers has been removed.
• Added resource groups for filtering in reports.
• Initial balances are filled out in a separate journal.
• Removed cost indicators in magazines, added an average price indicator in reference books.
• New report on storage locations in the form of an Excel pivot table.

Opportunities and Limitations

Excel spreadsheets have internal restrictions on the amount of information processed. The program is not intended to replace large accounting systems for hundreds of thousands of items. But in most SMBs, the sheer volume and complexity of the data is available for processing in Excel.

The main file does not contain a large number of calculations; for reports, special algorithms are used, which also do not have formula links with the original data. Therefore, large amounts of data provide normal performance even in Excel automatic calculation mode. Switching to manual calculation mode is recommended only on outdated computers.

V automatic calculation mode Excel we recommend the following parameters when working on high-performance computer:

• Number of resources (nomenclature) - up to 2000
• Number of metering points - up to 200
• The number of operations (lines) in each journal - up to 10,000
• File size (xlsx) - up to 10Mb

V manual Excel calculations the limitations are mainly related to the capabilities of the computer. For normal operation of reports, we recommend the following restrictions:

• Number of resources (nomenclature) - up to 5000
• Number of metering points - up to 500
• The number of operations (lines) in each journal - up to 30,000
• File size (xlsx) - up to 25Mb

In the case of using additional calculations (full openness of the program allows you to customize any additional calculations), the performance limits may be more stringent.

To reduce the data, you can create a new file at any time and transfer directories and initial balances there for all accounting points.

Installation and launch

The structure of the file is described on the initial page of the program - "Main".

As already noted, the program consists of parameter tables, reference books, logs and reports. You can navigate to these objects through hyperlinks on the "Home" page. You can also use standard ways to navigate Excel sheets and cells.

The easiest way to return to the page with the content is by pressing a special button in the header of any worksheet of the program.

The page navigation options are described on the service sheet. Preset and in the hidden third column of the Home sheet.

Parameters

The program contains several general parameters that control the performance characteristics:

All parameters can be changed at the discretion of the user.

Directories

Reference books are used for calculations and facilitate data entry into journals (through selection from lists). Data entry into directories is carried out as information becomes available.

Buyers and Suppliers

Lists of accounting counterparties. It is allowed to add lines down without restrictions.

List of storage locations for resources participating in the accounting system. These can be separate storage bins, storage areas, production areas or premises. It is allowed to add lines down without restrictions.

Groups and Resources

Two interconnected reference books for describing resources of all kinds stored at metering points. Resources include: finished products, semi-finished products, raw materials and materials. Grouping is used by the convenience of filtering reports. It is allowed to add lines down without restrictions.

Table fields (all are available for editing):

Operation logs

All warehouse transactions required for generating reports are entered into special tables, divided by type of operation.

Stock data at the start of accounting. The date is defined in Parameters.

Coming from suppliers

Receipts of resources from suppliers are entered into the table. The sequence by dates or order numbers is not important.

Table fields:

date

The table contains information about the consumption of raw materials and materials in the production process. The sequence by dates or order numbers is not important. This table is not required for the sales organization.

Table fields:

In addition to the described fields, the table contains several special service columns.

To delete a record, just clear the field date standard Excel tools. It is also possible to delete entire lines.

The table contains information on the production of finished products and semi-finished products. The consistency of dates or order numbers is not important. This table is not required for the sales organization.

Table fields:

In addition to the described fields, the table contains several special service columns.

To delete a record, just clear the field date standard Excel tools. It is also possible to delete entire lines.

Transactions for transferring resources between internal warehouses are entered into the table. The sequence by dates or order numbers is not important. The initial balances stored in warehouses as of the start date of accounting are also entered here.

Table fields:

Currently, the number of various reports that are prepared by all divisions of organizations is growing steadily. Very often, enterprises automate reporting based on various software products (SAP, 1C, Intalev, etc.). But even in those organizations where the system is "automated", they cannot do without the use of Excel. Since the software products used for automation have general principles for constructing reports, and each enterprise is unique, it is difficult to unify all reporting in specialized software products, and sometimes it is impossible to do this. And that's where Excel spreadsheets come in. They allow you to refine reports, present information obtained when unloading from the same 1C, in the required form, and sometimes generally serve as the main program for maintaining management and other reporting.

Excel is one of the most popular among the basic Microsoft Office suite. The sales manager, using tables, conducts systematic work with clients. The secretary keeps a record of all local regulations, correspondence or prepares diagrams for a production meeting. The economist processes the primary information and calculates the performance indicators of the company. Spreadsheets are the most popular software used by people in a variety of services and industries.

The program is especially useful for employees of financial and economic departments and accounting, if it is entrusted with the functions of planning and maintaining management accounting. Excel program for an accountant provides the ability to consolidate data, allowing you to consolidate information from several files into one. Allows you to select the information you want by creating separate tables or by hiding the data in the original table. Allows you to organize the available information according to the required criteria and summarize the results.

One way or another, accounting in Excel will become much easier if you master a few useful tricks that can speed up your work with data. Find out how excel can be useful for an accountant in the examples we have prepared. The following exercises in Excel for the work of accountants will be not only useful, but simply necessary.

Fundamental Excel Tool

In addition to direct responsibilities, the accountant can add functions for preparing commercial proposals, calculating contractual prices, and so on. To perform calculations, it is necessary to apply various coefficients and corrections, as well as convert prices. And most importantly, perform all actions quickly and without prejudice to basic responsibilities.

Excel for the accountant will help in preparing the main document, which can be performed on the main sheet, and additional calculations on separate ones.

So, when recalculating the rate, in one cell you can specify the price, in the second the currency rate, and in the third set the conversion formula (= first cell * second cell), then press Enter and get the price in rubles. In the first sheet in the desired cell, you can put “=”, go to the second sheet and indicate the third cell with the total. Press Enter again and get the result.

If it is necessary to carry out such calculations for a large number of points, where only the price changes, then you can use a fundamental Excel technique - autocomplete formulas, or broaching.

The ability to draw formulas is one of the basic functions of the program. It automates the process of calculating data in a table, without repeatedly writing the same formula. You can broach formulas as follows.

In the formula bar, we put equal and the reference to the cell from the table with the initial data (= A3). After that, we just get a duplicate value from the table. When you drag this cell, you will get a copy of the table with data, which will change accordingly with the change in information in the original table. This is an example of stretching cells without fixing ranges.

You can pin a link so that it remains unchanged when you drag in full, row, or column. Freezing is done in the formula bar using the $ sign. This sign is placed in front of the part of the coordinates in the link that needs to be fixed:
$ before letter - fixing by column - $ C1
$ before a number - fixing by line - С $ 1
$ before letter and number - full fixation of the cell - $ С $ 1.

Counting calendar days

Excel can become an indispensable assistant even in such simple actions as counting calendar days. An accountant needs to know exactly how many days were in a particular period in order to calculate interest, interest, penalties, loans, and the like.

If this is a short period of time, then it is easy to calculate it using the calendar, but this format is rather inconvenient for performing constant calculations. In such situations, Excel comes to the aid of the accountant.

To perform calculations, you need to select three free cells in the table. In one you need to write down the start date, in the second end, and the third should be left blank to get the results.

Next, select the third cell and click “Insert Function”, you can find it by the ¶x icon. After clicking, the "Function Wizard" window will pop up. From the list “Category” select “Date and time”, and from the list “Function” - “DAYS 360” and press the button OK. In the window that appears, you need to insert the values ​​of the start and end dates. To do this, you just need to click on the cells of the table with these dates, and in the “Method” line, put a unit and click OK. If the total value is displayed in a non-numeric format, you need to check the cell format: right-click and select “Cell Format” from the menu, set “Number Format” and click OK.

You can also count days in this way: in the third cell, type = 360 DAYS (B1; B2; 1). In parentheses, you must specify the coordinates of the first two cells with dates, and for the method, set the value of one. When calculating interest for weeks, the resulting number of days can be divided by 7.

You can also add or subtract any number of days to the date. To do this, you need to write the date in one cell, place the equal sign in the second, then click on the cell with the date and type “+” or “-” and the required number of days.

Sorting data

A very handy function that allows you to place data in ascending / descending order. You can also sort the data to sort the records by date.

To perform this action, you must select the area that requires sorting. Then you can click the "Sort in Ascending" button in the top row of the "Data" menu, you will find it by the "AY" sign. Your data will be placed from smallest to largest in the first selected column.

Excel tables for the accountant allow you to sort the data starting with the first selected column. If you selected cells from left to right, then the sequence will be performed in the leftmost column. If from right to left, then to the right.

If the data needs to be sorted by the middle column, then you can use the "Data" menu - the "Sort" item - "Range sort". In the "Sort by" section, you must select a column and sort type.

Working with long tables

Excel spreadsheets for an accountant are a multifunctional working tool that contains a lot of information for reporting and performing current calculations. When printing a table that does not fit on one sheet, you can place its “header” on each separate page, which will make it easier to find the necessary data. To do this, select the "File" menu - "Page settings" and the "Sheet" tab. We place the cursor on the "Through lines" or "Through columns" and in the table click on the lines that need to be placed on each sheet.

You can also use headers and footers to work with such documents. They mark the necessary data, such as the date, sheet numbers, the name of the originator, and so on. Headers and footers customization is available in “Page settings” - “Headers and footers”. There are ready-made markup options or the ability to add your own.

In addition to useful techniques for working in Excel, an accountant needs to master its hotkeys.

A set of Excel keyboard shortcuts you can't do without

Using these keyboard shortcuts in Excel will speed up your work and help you perform data analysis, graphing, and formatting tables.

• F4 - when entering a formula, adjusts the type of links (relative, fixed). Can be used to redo the last action.
• Shift + F2 - edit notes
• Ctrl +; - entering the current date (for some computers Ctrl + Shift + 4)
• Ctrl + ’- copy the values ​​of the cell located above the current one (for some computers, the combination Ctrl + Shift + 2 works)
• Alt + F8 - open the macro editor
• Alt + = - summarize a range of cells located above or to the left of the current cell
• Ctrl + Shift + 4 - defines the currency format of the cell
• Ctrl + Shift + 7 - set the outer border of the selected range of cells
• Ctrl + Shift + 0 - define the general format of the cell
• Ctrl + Shift + F - the combination opens the cell formatting dialog box
• Ctrl + Shift + L - enable / disable filter
• Ctrl + S - save the file (save as often as possible so as not to lose valuable data).

Using hotkeys and useful techniques in Excel will simplify and speed up your work only if you already have a sufficient knowledge of this program. If you want to improve your level and use spreadsheets more effectively to keep accounting in Excel, then you can download the book for free

Many companies use Microsoft Excel to keep track of department or company expenses. Currently Excel can be used on all PCs running Windows operating system. For this reason, you can keep track of your own expenses if this program is installed on your home PC. There are many templates for keeping track of expenses from Microsoft and other sites, the latest versions of Excel include a built-in template as a pre-installed one. Also in Excel, you can create your own dynamic table to keep track of expenses. In both cases, follow the instructions below.

Steps

Working with an Excel template

Select a preset template. The latest versions of the program include a template for keeping track of personal expenses, as well as templates for keeping track of business expenses. You can access these templates and track expenses using them.

• In Excel 2003, choose New from the File menu. In the On My Computer taskbar, select New Book to see the templates in a new dialog box.
• In Excel 2007, on the File menu, choose New. This will bring up the "New Book" dialog box. Select Installed Templates from the Templates menu in the left menu. Select Personal Monthly Budget from Installed Templates in the center menu and click Create.
• In Excel 2010, on the File menu, choose New. Select Sample Templates at the top of the Available Templates pane, then select Personal Monthly Budget from among all the sample templates and click Create.

1. Select a template online. If the predefined templates are not suitable for keeping track of your expenses, you can select a template over the Internet. You can download it from any website or by connecting via Excel to Microsoft Office Online.

   • For Excel 2003, you can select a suitable template from the Microsoft Office online library at https://templates.office.com/en-us. (You can also find templates there for later versions of Excel.)
   • In Excel 2007, select the Budgets tab under the Internet section of the New Workbook dialog box. To connect to the Office Online Template Library, you must have Internet enabled.
   • In Excel 2010, select Budgets from the Office.com section in the Available Templates pane. To connect, you must have the Internet turned on.

2. Enter the required information in the appropriate fields. The information depends on the specific dynamic table template you use.

   You can use the suggested name for the dynamic table or change the name. It will be enough to indicate your name and the current year in the file name.

   Create your own dynamic expense tracking table

   1. Open Excel.

      Enter the column headings. Suggested titles and order are Date, Expense Category, Note, Expense, Income, and Balance. Fill in these headings from cell A2 to G2. You may need to make the columns wider to fit the title or value.

      • Once you've prepared your dynamic table and column headings, you can use the Freeze Region feature to display the headings at all times as you scroll down through the values. The Freeze Region feature is on the View menu in Excel 2003 and earlier versions, and on the View menu, Window tab in Excel 2007 and 2010.

   2. Enter the information for the first type of expense in the cell in the third row.

      Enter the balance formula in cell G3. Due to the fact that you are entering a balance for the first time, it will be defined as the difference between expenses and income. How you write this depends on whether you want to display expenses or available funds.

      • If you want the spreadsheet to display expenses first, the balance formula should be = E3-F3, where E3 represents expenses and F3 represents income. With this setting, the formula will show expenses as a positive number, making it easier to understand the amount of expenses.
      • If you want the table to primarily display the funds available to you, the balance formula should be = F3-E3. With this setting, the table will display a positive balance if your income is higher than your expenses, and a negative balance in the opposite situation.

   3. Enter the information for the second type of expense in the cell in the fourth row.

      Enter the balance formula in cell G4. The second and subsequent values ​​will continue to display the balance, so you need to add the difference between expenses and income to the balance value of the previous record.

      • If you are looking at expenses first, the balance formula would be = G3 + (E4-F4), where G3 is the cell that shows the previous balance, E4 shows expenses, and F4 shows income.
      • If you are maintaining a spreadsheet to display cash, the balance formula would be = G3 + (F4-E4).
      • The parentheses around the cells that represent the difference between expenses and income are optional. They are used only to make the formula clearer.
      • If you want the balance to remain blank until a record is entered, you can use the IF value in the formula so that if no date has been entered, the cell will not display the value. The formula for such a record will be = IF (A4 = "", "", G3 + (E4-F4)), if the table is needed for keeping costs, and = IF (A4 = "", "", G3 + (F4-E4) ), if the table is needed to display cash (you can omit the brackets around the cell, which show expenses and income, but you need to use external brackets).

   4. Copy the balance of the formula to other cells in column G (balance column). Right-click in cell G3, select Copy. Then select the cells below the column. Right-click the selected cells and choose Paste to paste the formula into the selected cells (in Excel 2010, choose Paste or Paste Formula from the menu). The formula will automatically update the cells to show expenses, income, and dates (if applicable) from the current series and the balance from the series above the current series.

   5. Save your dynamic table. Give it a meaningful name, for example, "Expense Accounting.xls" or "Personal Budget.xls." As in the case with the name of templates, you can include your name and year in the file name (note, quotes are used only to show an example, you should not use them in the name. You do not need to specify the file extension either, Excel will do it itself) ...

      • Excel 2003 and older versions save dynamic tables in the old ".xls" format, while Excel 2007 and 2010 save tables in the newer XML-based ".xlsx" format, but you can read and save dynamic tables and in the old ".xls" as well. If you have multiple PCs and want to store a spreadsheet on each of them, use the older format if any of your PCs has Excel 2003 or older, or the newer format if at least Excel 2007 is installed on all computers.
   • Use AutoComplete in the expense and income categories to match the information you entered.
   • To distinguish paid bills from those expenses that are only planned, make the font bold, highlight text or cells with color.
   • To avoid accidentally changing a formula or column heading, you can protect cells from being changed. Select the cells that you may need to change in the future (date, category, expense, income, note) and unlock them. After that, you can lock the rest of the table from changes.