Sample table of income and expenses. Excel template for home accounting. Protecting data in a cell from changes

October 28, 2013 at 01:08 pm

Excel template for home accounting

Finance in IT

When the need arose to keep track of the income and expenses of the family budget three years ago, I tried a lot of specialized programs. Each one had some flaws, shortcomings, and even design flaws. After a long and unsuccessful search for what I needed, it was decided to organize what I needed based on an Excel template. Its functionality allows you to cover most of the basic requirements for maintaining home accounting, and, if necessary, build visual graphs and add your own analysis modules.

This template does not claim to cover 100% of the entire task, but can serve as a good basis for those who decide to go this route.

The only thing I would like to warn you right away is that working with this template requires a large desktop space, so a 22” or larger monitor is desirable. Because the file was designed with ease of use and no scrolling in mind. This allows you to fit data for an entire year on one sheet.

The content is intuitive, but, nevertheless, let’s quickly go over the main points.

When opening a file, the workspace is divided into three large parts. The top part is for maintaining all income. In other words, these are the financial volumes that we can manage. The bottom, largest one is for recording all expenses. They are divided into main subgroups for ease of analysis. On the right is a block for auto-summing results; the more the table is filled, the more informative its data is.

Each type of income or expense is on the lines. The columns break down the input fields by month. For example, let's take a block of data with income.

What can we hide, many receive “gray” or even “black” salaries. Someone can boast of being “white”. For others, the bulk of their income may come from part-time jobs. Therefore, for a more objective analysis of your sources of income, four main points have been highlighted. It doesn’t matter whether one cell will be filled in later or all at once - the correct amount will still be calculated in the “total” field.

I tried to break the costs into groups that would be universal and suitable for most people who started using this file. How successful this was is for you to judge. In any case, adding the required line with an individual expense item will not take much time. For example, I don’t smoke myself, but those who are hooked on this habit and want to get rid of it, and at the same time understand how much is spent on it, can add the “Cigarettes” expense item. Basic knowledge of Excel is quite enough for this, and I won’t touch on it now.

As above, all expenses are summed up by month in the final line - this is the total amount that we spend every month to no one knows where. Thanks to the detailed division into groups, you can easily track your own trends. For example, during the winter months my food expenses decrease by about 30%, but the craving for buying all sorts of unnecessary nonsense increases.

Even lower is a line called “remainder”. It is calculated as the difference between all income for the month and all expenses. It is from this that you can judge how much money you can put aside, for example, for a deposit. Or how much is missing, if the balance goes negative.

Well, that's basically it. Yes, I forgot to explain the difference between the fields “average (month)” and “average (year)” in the right summary block. The first, “monthly average,” calculates the average values only for those months in which there were expenses. For example, you bought educational courses three times in a year (in January, March and September). Then the formula will divide the total by three and place it in the cell. This allows you to more accurately estimate your monthly expenses. Well, the second, “average for the year,” always divides the final value by 12, which more accurately reflects the annual dependence. The greater the difference between them, the more irregular these expenses are. And so on.

You can download the file

A family is a state in miniature: it has a head, an adviser, “ subsidized population", income and expense items. Planning, distribution and sequestration ( familiar words?) family budget is an important task. How to save and save without going on a starvation diet? — Create a table for recording funds received by the family and review the structure of payments.

Money– one of the greatest instruments created by man. They can buy freedom, experience, entertainment and everything that makes life more comfortable. But they can be squandered, spent in an unknown direction and senselessly squandered.

Legendary American actor of the early twentieth century Will Rogers said:

“Too many people spend money on things they don’t need to impress people they don’t like.”

Has your income been less than your expenses over the past few months? Yes? Then you are not alone, but in a big company. The problem is that this is not a very good company. Debts, loans, penalties and late payments are growing like a snowball... it's time to jump out of the sinking boat!

Why do you need to keep a family budget?

“Money is just a tool. They will take you where you want, but will not replace you as a driver,” Russian-born writer who emigrated to the States, Ayn Rand learned from her own experience the need to plan and budget her own finances.

Unconvincing? Here three good reasons start planning your family budget:

Calculating a family budget will help you figure out long-term goals and work in a given direction. If you drift aimlessly, throwing money at every attractive item, how will you be able to save and go on a long-awaited vacation, buy a car or make a down payment on a mortgage?
Family budget expenses table sheds light on spontaneous spending and forces you to reconsider your purchasing habits. Do you really need 50 pairs of black high heels? Planning a family budget forces you to set priorities and refocus on achieving your goals.
Illness, divorce or job loss can lead to a serious financial crisis. Emergencies happen at the most inopportune times. This is why everyone needs an emergency fund. The structure of the family budget necessarily includes the column “ saving“- a financial cushion that will help you stay afloat for three to six months.

How to properly distribute the family budget

A few rules of thumb for family budget planning that we will present here can serve as a rough guide for making decisions. Everyone's situation is different and constantly changing, but the basic principles are a good starting point.

Rule 50/20/30

Elizabeth and Amelia Warren, authors of the book " All Your Worth: The Ultimate Lifetime Money Plan" (in translation " Your Whole Wealth: A Master Money Plan for Life") describe a simple but effective way to create a budget.

Instead of breaking down a family's expenses into 20 different categories, they recommend dividing the budget structure into three main components:

50% of income should cover basic expenses, such as paying housing, taxes and buying groceries;
30% – optional expenses: entertainment, going to a cafe, cinema, etc.;
20% goes to pay off loans and debts, and is also set aside as a reserve.

80/20 rule

Step 2: determine the income and expenses of the family budget

It's time to look at the structure of the family budget. Start by making a list of all sources of income: wages, alimony, pensions, part-time jobs and other options for bringing money into the family.

Expenses include everything you spend money on.

Divide your expenses into fixed and variable payments. Fill in the fields for variable and fixed expenses in the table for maintaining a family budget, based on your own experience. Detailed instructions for working with the excel file are in the next chapter.

When distributing the budget, it is necessary to take into account the size of the family, living conditions and the desires of all members of the “unit of society”. A short list of categories is already included in the example table. Consider the categories of expenses that will be needed to further detail the structure.

Income structure

As a rule, the income column includes:

salary of the head of the family (indicated “husband”);
salary of the general adviser (“wife”);
interest on deposits;
pension;
social benefits;
part-time jobs (private lessons, for example).

Expense column

Expenses are divided into constant, that is, unchangeable: fixed tax payments; home, car and health insurance; constant amounts for Internet and TV. This also includes those 10–20% that need to be set aside for unforeseen cases and “rainy days.”

Variable expenses column:

products;
medical service;
spending on a car;
cloth;
payment for gas, electricity, water;
personal expenses of spouses (entered and planned separately);
seasonal spending on gifts;
contributions to school and kindergarten;
entertainment;
expenses for children.

Depending on your desire, you can supplement, specify the list or shorten it by enlarging and combining expense items.

Step 3: Track your spending throughout the month

It is unlikely that you will be able to draw up a family budget table right away; you need to find out where the money goes and in what proportions. This will take one to two months. In a ready-made excel spreadsheet that you can download for free, start adding expenses, gradually adjusting the categories " for yourself».

Below you will find detailed explanations for this document, since this Excel includes several interrelated tables.

The purpose of this step is to get a clear picture of your financial situation, clearly see the cost structure and, at the next stage, adjust the budget.

Step 4: Separate Needs from Wants

When people start recording their spending, they discover that a lot of money " flies away"for completely unnecessary things. Impulse, unplanned expenses seriously hit your pocket if your income level is not so high that a couple or two thousand go unnoticed.

Refuse to purchase until you are sure that the item is absolutely necessary for you. Wait a few weeks. If it turns out that you really cannot live without the desired item, then it is indeed a necessary expenditure.

A little advice: Put your credit and debit cards aside. Use cash to learn how to save. It is psychologically easier to part with virtual amounts than to count out pieces of paper.

How to properly plan a family budget in a table

Now you know what is really happening with your money.

Look at the categories of expenses you want to cut and make your own plan using a free excel spreadsheet.

Many people don't like the word " budget”, because they believe that these are restrictions, deprivations and lack of entertainment. Relax, a personalized spending plan will allow you to live within your means, avoid stress and sleep better rather than worrying about how to get out of debt.

“An annual income of £20 and an annual expenditure of £19.06 leads to happiness. An income of 20 pounds and an expense of 20.6 leads to suffering,” Charles Dickens’ brilliant note reveals the basic law of planning.

Enter your prepared family budget into the table

You have set goals, determined income and expenses, decided how much you will save monthly for emergencies andlearned the difference between needs and wants. Take another look at the budget sheet in the spreadsheet and fill in the blank columns.

A budget is not a static figure fixed once and for all. If necessary, you can always adjust it. For example, you planned to spend 15 thousand monthly on groceries, but after a couple of months you noticed that you only spend 14 thousand. Make additions to the table - redirect the saved amount to the “savings” column.

How to plan a budget with irregular income

Not everyone has a permanent job with regular paychecks. This doesn't mean you can't create a budget; but this means you have to plan in more detail.

One strategy is to calculate the average income over the past few years and focus on this figure.
Second way- determine a stable salary from your own income - what you will live on, and save the excess into an insurance account. In lean months, the account balance will decrease by exactly the missing amount. But your “salary” will remain the same.
Third planning option– maintain two budget tables in parallel: for “ good" And " bad» months. It's a little more complicated, but nothing is impossible. The danger that awaits you along this path: people spend and take out loans, waiting for income from the best months. If the “black streak” drags on a little, the credit funnel will eat up both current and future income.

Below you will find solutions on how to distribute the family budget according to the table.

After we have decided on the main goals, let's try to distribute the family budget for the month, indicate current income and expenses in the table, in order to manage funds wisely, to be able to save for main goals, without missing out on current and everyday needs.

Open the second sheet " Budget"and fill in the fields of monthly income, annual income, and the program will calculate the results itself, example:

In columns " variable expenses" And " fixed costs» Enter estimated numbers. Add new items where " other", in place of unnecessary names, enter your own:

Now go to the tab of the month from which you decided to start saving and planning family expenses. On the left you will find columns in which you need to record the date of purchase, select a category from the drop-down list and make a note.

Additional notes are very convenient to refresh your memory if necessary and clarify exactly what the money was spent on.

Simply delete the data entered in the table as an example and enter your own:

To account for expenses and income by month, we suggest looking at the table on the third sheet in our Excel " This year", this table is filled in automatically based on your expenses and income, sums up and gives an idea of your progress:

And on the right there will be a separate table that will automatically summarize all expenses for the year:

Nothing complicated. Even if you have never tried to master working with Excel tables, selecting the desired cell and entering the numbers is all that is required.

Poll: How old are you?

I dedicated my blog to the topic of saving and planning. But it turned out that in this process many people are confused not so much by general as by specific technical issues. So today I will write about how to budget and save money in Excel.

It is easiest to manage a family budget if you live alone. After all, all financial receipts and expenses are controlled by you and only you! In a large happy family, counting common and personal money is much more difficult.

The main thing is that the idea of “accounting and saving” is supported by all family members (including children and elderly relatives). We all get together for dinner, voice a joint plan, discuss details and... build the future step by step!

By the way, children are the easiest to motivate. It’s enough just to give boring daily accounting a game form.

We count expenses

At a general family council, we come up with names for the main expense items. The first thing that comes to mind: “Food”, “Utilities”, “Communication” (telephone, internet), “Entertainment”, “Clothing”, “Transport” (public/taxi/personal car expenses), “School/children garden/institute”, “Pets”, “Health”, “Beauty”, “Loans”, etc. In each family, the list of such items will be individual.

It’s also not worth splitting up expenses too much – a dozen categories are quite enough. In a computer program "Home accounting" I noticed that food costs are divided into “Bread”, “Dairy products”, “Meat and sausages”, “Vegetables and fruits” and others. I don't see much point in this. Especially if the accounting is done manually. After all, food is food in Africa too.

Looking ahead, I’ll share one more trick. Keeping track of personal (pocket) expenses is very difficult - both technically and morally. Therefore, you can enter the section “Petya/Vasya/Masha’s personal money”. In this column we record the amount that is given to Petya/Vasya/Masha for pocket expenses (once a week or once a month).

And they can spend it at their own discretion: buy candy bars and chips or save for roller skates. The most important thing is that the amount issued in hand is already displayed in Excel.

By the way, everyone needs pocket money: schoolchildren, students, housewives, and the head of the family. It’s like with personal space and time - some piece should belong only to you.

Yes, and don’t forget to highlight such an expense item as “Force Majeure”. This includes everything that goes beyond the daily budget: car or apartment repairs, seaside vacation, medications in case of a serious illness, and a trip to grandma’s anniversary.

Well, then we give each family member a tiny notebook. And throughout the day, Petya/Vasya/Masha records expenses by category. In the evening we all got together and transferred the expenses to a common table on the computer. In terms of time, “reconciling debits with credits” takes a maximum of ten minutes.

What will cost accounting give?

You accumulate specific statistics by month, which can be adjusted and used as a guide for the future
You can clearly see where the money is going and for what
The results of the first months allow you to effectively optimize costs

By the way, if you already know your “weak points” that would be worth saving on without any tables and records, I advise you to highlight them in a separate category for clarity. My friend, for example, kept records under the item “Cigarettes”. According to his records, it turned out that over the course of a year his bad habit cost him about $600. He eventually quit smoking.

We count income

For obvious reasons, income items will be much smaller than expenses. For example: “Petya’s salary”, “Masha’s salary”, “Irina’s pension”, “Roma’s scholarship”.

If there are side sources of income, it is better to consider them separately. For example: “Interest on deposits”, “Renting out a garage”, “Part-time work”.

And just in case, we create one more income item: “Other” (lottery winnings or money in an envelope for a birthday).

What will income accounting give?

Additional control: the total amounts of income and expenses for the month should agree to within a couple of hundred rubles
The growth and decline of income by year and month will be clearly visible
Dry numbers will highlight, like a spotlight, “holes” in the budget that need to be “patched” in advance

Classic example: New Year holidays, vacations, birthdays. During these months, as a rule, all savings are spent “to zero”, and the family gets into debt and loans.

How to correctly transfer home accounting to Excel?

The easiest way is to place all income and expense items in the first column, and dates horizontally (or vice versa).

You can create separate Excel sheets for each month in one document, but in different tabs (“January 2015”, “February 2015”, etc.). For clarity, you can transfer the monthly totals to Excel, and keep daily records in a regular paper weekly journal.

The main thing is that it does not take up too much of your time. Oh, and by the way, at the end of each month it is worth holding “family meetings” - with a debriefing and drawing up a plan for the future. By the way, here you can download a template for Excel.

Finally, I will say that I used to keep a budget in Excel myself, but it turned out to be inconvenient.

Now I use, which allow me to do this better and spend much less time entering data. In addition, they can build charts and analytics based on historical data, which makes it easier to control their funds. In general, I recommend it!

In what form do you manage your family budget? Subscribe to updates and share the best posts with your friends!

Drawing up a family budget will help you keep track of income and expenses, and this will finally allow you to understand where the money is constantly going. Creating a table of income and expenses of the family budget is actually not as difficult as it may seem at first glance.

It is preferable to start work with approximate deadlines, oral analysis and assessment of the situation. You can say that your budget has been drawn up correctly if you are not focused only on reducing expenses, but are also looking for options on how to generate additional income.

Why is this necessary?

Accounting for material flows can be kept electronically, using a Microsoft Excel spreadsheet, or the old-fashioned way - keep a notebook where you enter all the information.

Both options can be called an effective method of controlling costs, and they will also allow you to create an approximate list of costs that will have to be incurred.

You can sum up your results weekly, every month, or make a plan for several months at once. Which option is right for your family is up to you to decide. Such a diagram, which is in front of your eyes, will allow you to find out exactly what and when the money was spent.

This way you will see the expenses that are really necessary and those that could have been avoided (impulse purchases). This is a great opportunity to start saving, which is why it is so important to know how to create a family budget spreadsheet correctly.

Each family is special, so the required expenses and income level will be different. For example, some people need to pay the cost of rent, while others have two children, so their expenses will double. There may be many options, but the standard table for accounting expenses and income includes the following items:

the amount of total family income;
how much taxes need to be paid;
payment for housing, utilities, and, if necessary, car maintenance;
if there are loans, what is the size of the monthly payment and interest;
purchasing food;
expenses for medications, purchase of household chemicals;
unexpected expenses.

Expense items can be very different, but potential expenses should be described in as much detail as possible. Therefore, even before you start drawing up a plan, you need to try to break them all down into categories, and then only adjust the numbers monthly; the expenses and income themselves usually remain unchanged.

What is the best way to share expenses?

Create 3 large sections (regular, one-time and main expenses). Constants typically include:

Internet and telephone payments;
loans and insurance;
communal payments.

Unlike other items, these amounts cannot be adjusted and they cannot be avoided. You can also include other fixed expenses in your family budget, which will be determined by yourself as soon as you try to keep such a table for a couple of months.

purchasing food;
entertainment events, which will be discussed in the entertainment section;
purchase of cosmetics and household chemicals;
payment for transport travel or expenses for servicing a personal car;
expenses are not medications.

The amounts for each expense item may vary, because some groups of goods do not need to be purchased monthly. It is very important here to calculate the burden on the budget, because if, for example, your car breaks down and you spend all your money on it in a month, then you will not have any cash left for other expenses.

Distribute the load evenly and buy winter tires, etc. in the summer, spending as much as you can on it.

Now all that remains is to create a category for one-time expenses and all kinds of entertainment. This includes purchasing shoes and clothing, equipment and furniture, as well as vacations and various trips. If you properly control your finances, you can save money and not allow yourself to give up your favorite entertainment. From time to time, be sure to do something that brings you positive emotions, otherwise life will seem very boring.

It would also be a good idea to keep a small amount of reserve ( approximately 10% of your family income) in case of unforeseen situations. Be prepared that a small part of the budget will still be spent on various little things and you won’t be able to keep track of these 500-700 rubles.

They can also be divided into separate groups, depending on what is the source of profit (part-time work, interest on deposits, dividends, etc.). In an ordinary family there will be few such articles. Therefore, we simply indicate the income of the wife and husband.

Include “Other sources” in the table, where you enter birthday gifts, winning prizes, profits from the sale of any goods, etc. This way you can track changes in your family budget.

A family budget spending spreadsheet in Excel is a great way to keep track of your budget and, if necessary, take any measures to cut costs.

Regularly compare your family's monthly income with expenses and adjust the latter as necessary.
Make an approximate list of expenses for the year, in which you enter all fixed payments, taxes and mandatory expenses. This way (let the figure be approximate) you will not have to urgently cut back on all your desires at the end of the year and save even on the most necessary things if suddenly for some reason your income becomes less. Try not to go beyond the established limits.
In a notebook, write down all the money you spent every day. Even little things like a cup of tea or coffee.
Follow a simple rule: if you can’t pay for your planned purchases by the end of the month, then you don’t need to make them.

An income and expenses table is a great tool that will allow you to buy only the essentials and save enough money to buy a car or apartment.

Check out bank offers

Cashback card in Rosbank

Apply for a card

More about the map

Cashback up to 7% - on selected categories;
Cashback 1% - on all purchases;
Bonuses, discounts on goods and services from VISA;;
Internet banking – free;
Mobile banking – free;
Up to 4 different currencies on 1 card.

Card from PromsvyazBank

Apply for a card

More about the map

Up to 5% cashback;
Cash withdrawal without commission at partner ATMs;
Internet banking – free;
Mobile banking is free.

Card from Home Credit Bank

Apply for a card

More about the map

Up to 10% cashback with partners;
Up to 7% per annum on account balance;
Withdrawing funds from ATMs without commission (up to 5 times a month);
Apple Pay, Google Pay and Samsung Pay technology;
Free Internet banking;
Free mobile banking.

More about the map

Cashback up to 10% from filling stations at gas stations
Cashback up to 5% on bills in cafes and restaurants
Cashback up to 1% on all other purchases
Up to 6% per annum on balance
Card maintenance is free;
Free Internet banking;
Free mobile banking.

Card from Tinkoff Bank

Every person who cares about the financial well-being of his family analyzes income and expenses. If you don't know how much money you have, then you don't have it. Maintaining a budget allows you to always stay in the black, prevent unnecessary expenses, and realize your goals and dreams.

There are many ready-made programs for accounting income and expenses. But finding the best program that would fulfill all the needs of a particular family is difficult. After all, the requests are different. We suggest creating a family budget in Excel and adapting it to your needs.

Personal budget of income and expenses in Excel

The Microsoft Office software package includes several templates for solving certain problems. Open Excel - click the “File” button - “Create” - “Sample templates” - “Personal budget for the month” - OK.

If for some reason you do not have this template or you could not find it, you can download your personal budget for the month in Excel.

A simple template will open where you can enter planned and actual indicators. Distribute income and expenses by item, calculate amounts automatically.

We can adapt a ready-made family budget template to your needs:

add/delete articles;
change conditional formatting options;
line names;
fill colors, etc.

Before us is a summary statement of income and expenses. It may be convenient for some families. But we suggest detailing the tables.

Family budget spreadsheet in Excel

Let's say a family maintains a separate budget. It is important who brings how much into the house. It is necessary to take into account the monthly earnings of the husband and wife. Moreover, the money flows unevenly. One day - salary, a week later - advance payment. In a couple more days - interest on the deposit. Plus periodic part-time jobs.

To detail the income, we create an Excel table of family income and expenses on a separate sheet. Let's give it a name.

We designate the columns: “Date”, “Article”, “Amount”. Below - “Total”. Enter the formula to calculate the amount into this cell.

We return to the summary sheet. Select the cell above which we want to insert a line. Right mouse button – “Insert” - “Line” - OK. Sign:

Now we need to make sure that the total amount from the detailed report is automatically transferred to the summary. Select an empty cell where the numbers should be displayed. Enter "equals".

Let's go to the sheet with a detailed report. And click the total amount of income for the month. "Input":

We have attached the detailed report to the main summary sheet. Now you can make changes to the detail sheet as many times as needed throughout the month. The amounts in the summary will be recalculated automatically.

Budget expenditure breakdown

Money is spent almost every day: food, fuel, travel tickets are bought. To optimize family budget management, it is recommended to make expenses immediately. Spent it and write it down.

For convenience, we will create detail sheets for all expense items. Each one contains an Excel table of family budget expenses in detail. “Date” - “Expense item” - “Amount”. It is enough to do it once. And then copy and paste.

To give a name to the detail sheet, right-click on its designation. "Rename".

Don’t forget to write down the amount formula in the “Total” line.

Now let's link expense reports to the summary sheet. The joining principle is the same.

Try to highlight the cell with the total amount!

Protecting data in a cell from changes

Often expenses and income are introduced in a hurry. Because of this, you can make a mistake and enter an invalid value. This will result in incorrect summary data. And at the end of the month it is sometimes impossible to remember exactly where the inaccuracy was.

How to protect a cell from changes in Excel:

From entering incorrect values. Data – data check – parameters. Select a data type from the pop-up list. OK.
Prevent changing values in a cell. Review – protect the sheet. In the tab that opens, enter a password that will allow you to disable the protection. In the pop-up list, you can select what we prohibit and what we allow. In the example, we left the user the ability to select cells.
Selectively protect cells. The family budget can be used by several people at once. For each of them, you can organize access to a specific area of the sheet. Review – Allow ranges to change. Create. Enter the name of the range, the addresses of the cells (to which there will be access) and the access password.

To protect the entire workbook, on the Review tab, click the corresponding button.

Working with formulas in a personal finance spreadsheet

When you stretch a formula (“multiply” across the entire column) in a table with income and expenses, there is a danger of shifting the link. You should anchor the cell reference in the formula.

Press F4. A $ sign appears before the column name and row name:

Pressing F4 again will result in this type of link: C$17 (mixed absolute link). Only the row is fixed. The column can be moved. Click again – $C17 (the column is fixed). If you enter $C$17 (an absolute reference), the values are fixed relative to the row and column.

To remember the range, perform the same steps: select – F4.

The family budget disciplines. Helps develop financial behavior and avoid unnecessary spending. And Excel allows you to take into account the characteristics of a particular family.

2 methods: Working with an Excel template Creating your own dynamic expense table

Many companies use Microsoft Excel to track expenses for a department or the entire company. Currently, Excel can be used on all PCs running the 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 a large number of expense tracking templates from Microsoft and other sites, and recent versions of Excel include a built-in template as a preset. You can also create your own dynamic table in Excel to track expenses. In both cases, follow the instructions below.

Method 1 Working with an Excel template

Method 2 Create your own dynamic expense table

Open Excel.

Enter the name of the dynamic table in cell A1. The name should be meaningful, for example, “Personal Budget”, “Accounting for Personal Expenses” or something similar (no need to use quotes, they are here only to show an example).

Enter column headings.
Suggested names and order: Date, Expense Category, Note, Expense, Income, and Balance. Fill cells A2 to G2 with these headings. You may need to make the columns wider to accommodate the title or value.
- Once you have your dynamic table and column headers ready, you can use the Freeze Pane feature to permanently display the headers while you scroll down the values. The Freeze Pane feature is found on the View menu in Excel 2003 and earlier versions and on the View menu, Window tab in Excel 2007 and 2010.

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

Enter the balance formula in cell G3.
Since this is the first time you are entering a balance, 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 show expenses first, the balance sheet formula should be =E3-F3, where E3 shows expenses and F3 shows income. With this setting, the formula will show expenses as a positive number, making the amount of expenses easier to understand.
- If you want the table to primarily display the funds available to you, the balance formula should be =F3-E3. With this setup, the table will display a positive balance if your income is higher than your expenses, and a negative balance if the opposite situation occurs.

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

Enter the balance formula in cell G4.
The second and subsequent values will continue to show a balance, so you need to add the difference between expenses and income to the balance value of the previous entry.
- If you're looking at expenses first, the balance sheet 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 will be =G3+(F4-E4).
- Brackets around cells showing 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 an entry is entered, you can use an IF value in the formula so that if a date has not been entered, the cell will not display a value. The formula for such an entry will be =IF(A4="","",G3+(E4-F4)), if the table is needed to maintain expenses, and =IF(A4="","", G3+(F4-E4) ), if the table is needed to display cash (you don't have to use brackets around the cell that show expenses and income, but you need to use outer brackets).

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

Save the dynamic table.
- Excel 2003 and older versions save dynamic tables in the older ".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 the spreadsheet on each of them, use the older format if any of your PCs have Excel 2003 or older, or the new format if all computers have at least Excel 2007 installed.

Use AutoFill in the expense and income categories to ensure the information you enter matches.
To distinguish paid bills from those expenses that are just planned, make the font bold and highlight text or cells.
To prevent accidental changes to a formula or column header, cells can be protected from modification. Select cells that you may need to change later (date, category, expense, income, note) and unlock them. You can then lock the rest of the table from changes.

Article information

This page has been viewed 49,634 times.

Was this article helpful?

Excel Templates for Cost Control play an important role in accounting because they help keep abreast of financial status. These
Excel templates to download not only serve to control of enterprise finances, and for control personal expenses. Thus, thanks to the register of expenses and income in payslips, we can keep daily family budget accounting and enterprises. In addition, it serves as an add-on for calculating budgets in Excel.

How to control expenses in Excel

In order to control expenses in Excel, you need to record expenses and income on spreadsheets, and then take the difference between them using Excel functions. It allows you to see movement of cash in and out. In addition, we can calculate and plan subsequent actions depending on the financial situation, since with the help expense and income payslip we also have the opportunity to know whether we are managing our capital well and whether we need to change our course of action, since the difference can be positive or negative.

Cost control in Excel for download

There is no need to know how to use Excel in order to control expenses, since there are several options for sample calculation sheets for free download, including: