Make Expense Control Sheet in Excel  Step by Step Guide ▷ 2020

Both for a company and in the family economy it is important to maintain a control over money. Since, if we lose sight of this, it can imply a future headache. Regrettably, it is impossible to do all these calculations mentally. Doing it in notebooks and by hand is archaic and impractical these days.

The professionals who perform these tasks for companies, they use specialized software. They are usually quite expensive and difficult to handle.

Luckily, we have Microsoft Excel that allows us to create spreadsheets Y organize in a simple document all our income and expenses of money.

What are the best Excel functions and formulas to make an income and expense control sheet?

What are the best Excel functions and formulas to make an income and expense control sheet?

Although it sounds complicated, the truth is that income, expenses and balances are calculated with two basic operations: addition and subtraction. However, we are talking about a great number of variables. This can complicate things a bit if we do not organize the categories well.

In this way it will be essential to distribute the tables in an orderly manner in the spaces available Y through the document sheets. The formulas will be reduced to those mentioned before and we will only use the paste special function.

To help you with this task and streamline your monthly budgets we offer you these simple ways to make your expense control table and monthly income.

Steps to create an expense and income control sheet in Microsoft Excel

There are two basic ways to access such a check sheet. With both methods you will reach the same result. The important thing will be that you try to load as much data as possible and thus take control more effectively.

Let’s see what they are:

With a template

The first is about use a predesigned document. There are loads of them within the same program. You just have to choose the one that best suits your needs.

To achieve this, follow these steps:

Steps to create an income and expense control sheet in Microsoft Excel with a template

  • Start Microsoft Excel. You can do it from the start menu if you don’t have a shortcut on the desktop.
  • On the welcome screen go to the button “New” found in the sidebar.
  • There you can see several examples of templates. However, the ones that interest us are in “Budgets”.
  • This option is located between the bar and the examples. Click there.

It is also possible to enter keywords to perform a rough search. It is important to mention that all these documents are obtained online, so an internet connection is essential to download them (not like that when working them).

Each template has a name that somehow describes its content. If this is not enough, press the right button on it and choose the option “Preview”. This will show a window with a screenshot of the content. You can too open the document and explore further.

Each of these designs incorporates its own formulas and functions. So you only have to fill in the corresponding cells and change some names.

Individually

If the above method does not convince you, you can make your own spreadsheet to control your expenses and income with very simple steps.

Taking into account that personal income generally come from one or two sources, the most important thing will be separate expenses into categories. Of course, this could vary in each particular case.

However, as an example, we recommend the following:

  • accommodation. Here you can group the fixed expenses of your home such as electricity, drinking water, gas, rent, telephone, etc.
  • Transport. If you move with public media, calculate monthly expenses. If instead you do it in a own vehicleTake into account insurance, maintenance, fuel, and everything competent in this category.
  • Food. Contemplate the grocery shopping and also the outings to restaurants.
  • Debts. Include in this group payments to Credit cards and the personal loans that you may have.
  • Taxes. Take into account those of the car, Public servicess and everyone you pay depending on the region where do you live.
  • Pets Although they are not always taken into account, our animals also represent a monthly expense. Keep in mind the food, vets, toys and the cleaning supplies that are used to maintain it.
  • Entertainment. They represent the outings to the movies, theaters, concerts, streaming services, sporting events and what you consider that falls into this group.

At first, you probably don’t have all the numbers needed to complete the cells. But it is a good opportunity to start collecting vouchers and organize your expenses. You can also use the last month’s expenses and get a rough notion.

It’s time to pass this information to software. You will see how the categories that we mention and their descriptions will help you streamline the design process.

The steps to follow are those:

  • Begins starting Microsoft Office.
  • On the main screen, create a “Blank book”.
  • In the first cell, put the title of our document. You can give it header format so that it stands out on the sheet.
  • Name cells A3, 4 and 5 with Income, Expenses and Balance respectively. You can color them with the paint icon in the section “Source”. If you want to have all the months in a single document, put their name in the second row.
  • Rename the blade like “Homepage” To do this, right-click on “Sheet 1” and choose “Rename”.
  • Creates a new leaf and name it as “Income”.

Income worksheet in Microsoft Excel

In it we will place all the money we get per month organized according to where they come from. For example, salaries, fixed terms, rent, etc. As before, write the months of the year.

Then:

  • At the end of the list we will place the total corresponding to each period. To do this enter “= SUM ()” taking into account that between the parentheses the cells you want to add must go. In the case of the image it would be “= SUM (B2: B4)” This tells the program to add the figures in column B, from row 2 to row 4.
  • Copy with “Ctrl + C” the result.
  • Again on the main page, in the income box corresponding to the month in question, we will do a glued special. This will cause any modifications made to the worksheet to “Income” is reflected in this cell.
  • To do this, press “Ctrl + Alt + V”. In the window that appears, choose “Paste links”.

Paste Special in Microsoft Excel

  • It’s time to create a expenses sheet. To do this, repeat the first steps.
  • In this section we will have to elaborate more on its components. You can guide you through the categories that we mention before.
  • At the end of each of them enter a subtotal, what will be the sum of all the figures in each group for each month.
  • At lower end places the total, which will be made up of the sum of all subtotals. To do this enter “= SUM ()” but this time, instead of dragging, you will select each of them by holding down the key “Ctrl”. In the example it remains “= SUM (B10; B19; B26; B33)”.

Individual sum in Microsoft Excel

  • Copy this last result and perform special paste in the corresponding cell from the main page.
  • Finally, in the “Balance” performs the subtraction between income and expenses. In the example the formula is “= B3-B4”. If you haven’t forgotten a transaction, this number should be similar to the one you have available.

If you have any questions, leave them in the comments, we will answer you as soon as possible, and it will also be of great help to more members of the community. Thank you! 😉

You may be interested:

Rate this post

Leave a Comment