How to manage bills with Excel

Did I already pay the phone bill? When does that of electricity expire? Questions that at least once we all asked ourselves. We can keep everything in order thanks to Excel. First we prepare the sheet

Did I already pay the phone bill? When does that of electricity expire? Questions that at least once we all asked ourselves. We can keep everything in order thanks to Excel.

First we prepare the worksheet that will contain the prospect of our bills. We delete sheets3 (which is automatically created for each new work sheet) and rename sheets 1 in bills and sheet2 in statistics. In the first sheet we will insert the bills received, in the second a pivot table that will allow us to observe the trend of consumption in the various periods of the year.

Let's start with the sheet that we called bills. We have to prepare at least five columns: bills, period, amount, deadline, payment. In the first column we will store the type of bill (e.g. "Enel"); in the second the reference period ("Gen/Feb 2003"); in the third the amount; in the fourth the expiry date; in the fifth the one in which the payment actually took place. Simple, right?

We enter some example data. We can invent them, taking care to choose dates and plausible amounts, or we can fish in a dusty drawer to be sure of having a realistic picture of the situation. Although this phase is boring and requires a minimum of attention, it immediately allows us to experience the most advanced functions of our work sheet and to check that the formulas are correct.

Let's start complicating things slightly: now we would like Excel to report how many days they remain on the expiry. How can we calculate it? We insert a column called gas (days at the deadline) and insert the formula in the first cell: = $ d2-today () which provides exactly the number of days that are missing when the bill expires (or a negative number, if it has already expired, or zero if it expires during the day).

Copiamo la formula lungo tutta la colonna. Questa impostazione è poco leggibile perché non tiene conto delle bollette già pagate. Inseriamo una nuova colonna, Stato, con la formula =SE($E2””; “Pagata”; $E2). La ricerca delle bollette viene già notevolmente semplificata, ma ragionare in termini di numeri relativi è scomodo anche per chi ha una certa familiarità con l’algebra. Vediamo come migliorare ulteriormente.

Modifichiamo la terza parte dell’istruzione SE in modo che si comporti diversamente per i numeri positivi e negativi: =SE($E2””; “Pagata”; SE($F2

We wonder if we can make our scheme even clearer. Let's try to highlight the writings with conditional formatting. We select the first cell of the State Column and then go to the conditional format/ formatting menu. The conditional formatting dialog box allows you to insert three logical conditions to the plus and to associate different styles. This technique allows you to highlight particular situations.

Let's start with the simplest condition: the bills paid. We select from the first curtain "The value of the cell is", from the second "equal to" and we insert the word "paid" in the third. Then, by pressing the format button, we choose the style that best suits the case. The choice of style is made (almost) exactly as it would do from the format menu. Some options are absent (number, alignment, protection), but we will not need.

The edges, background and style of the character of the cells can be set from the cell format dialog box. In this case, a clear green, reassuring and legible should go. To see the effect of our modification we click OK: if the word "paid" the color of the text should change in the cell. A further step forward in representative clarity. Now let's take care of the two remaining conditions.

We open the conditional formatting window as from point 8. Searching on Add >> We insert another condition to manage the case in which the bill has not yet expired. This time we prefer to use a formula to identify the situation more clearly and then select "The formula is" and insert to the = $ f2> = 0 side and form with an orange, perhaps bold, which inspire concern.

Before closing the window we add another condition based on the formula = $ f2

Now in the column we will have our colored text suitably depending on the situations and it will be easy to notice anomalies at a glance. At this point it is no longer necessary for the gas column to be visible and we can hide it by clicking with the right button on the header and then choosing hide. It will continue to be used in formulas but will not occupy useful space. To restore it, just use the control. Discover.

To improve the functionality of the document we can use the sorting commands from the data menu. First we select our table (including heads). Then we use the data/ order command and choose gas with a decreasing order. By clicking on the OK button we would like our bills with closer deadline to be moved to the top of the table.

Unfortunately, however, they will be mixed with those already paid. To solve this problem we use the command again from the data menu and modify the sorting criterion: first we enter payment/growing, for second gas/growing. In this way the bills already paid will be found at the top of the list and below there will be, in order of arrears, the others. The patience needed to collect data is repaid.

For data analysis there is nothing better than a pivot table. We select data/ ratio pivot table and indicate the table in the bills sheet as a area of ​​origin and as a destination the statistical sheet. Then insert the type of bill into the column, in line the period and the amount at the center. The use of this tool requires a minimum of experience, but provides a series of very interesting and easily interchangeable perspectives.