If you want to get a grip on your expenses, it is a good idea to keep track of your fixed costs and variable costs. You do that of course in Excel. In this article you will learn how to automatically discolor amounts, so you can immediately see how you are doing. This works through the conditional formatting option in Excel.
We’ll get started with the Conditional Formatting feature. Numbers or cells are given a color depending on the condition you specify. As an example, we have put together a number of fixed costs. All descriptions are in column A. Column B contains the amounts to be paid in January. In column C, copy the caption from the image below.
Column D shows the amounts you have to pay in February, column F is for the amounts in March and so on. Most amounts have to be paid every month, others only three times a year or even once a year. You write down the amounts in the columns of all months.
To indicate that an amount was paid in January (in column B), enter something next to it in column C: you type an x, or OK or the word paid or you enter the date on which it was paid; does not matter. If you have paid an amount from the February column, you write down your designation in column E and so on.
Many people find Excel complicated, while it is so useful to have mastered this software. So take a look at the course Excel in practice from our Tech Academy. Or go for the Excel course bundle in practiceincluding practice book!
Format formula
Then we make sure that the relevant amount turns red as soon as you have typed something next to it. You may be familiar with Conditional Formatting in Excel. This allows you to discolor a cell, depending on the number it contains. For example, if the number exceeds 10, the cell turns yellow and that number becomes bold.
Now we don’t want the cell to discolour if the number in the same cell changes, but if something is typed in the cell next to it. You set that condition by means of a formula.
Select cells B3 through B30 and click in the tab Start on Conditional formatting. The menu that opens gives the obvious settings, but we’re going for an advanced option. Choose at the bottom of that menu New rule; in the window that appears, choose Use a formula to determine which cells are formatted, on which an input box appears. In it you enter the formula: = C3> 0.
Is to do, right? Note that in this formula, the row number (the 3 of C3) is equal to the number of the first row you selected, the 3 of B3 in this case.
In the same window, click Format; the window Format cells opens. Take the tab Font style, because with this you determine the color of the amount. Select with the drop-down list below Color in front of red. To ensure that the amount also becomes bold, click below Drawing style on Fat. Close the windows by clicking OK three times.
As soon as you type something next to the amount in column C (an x, OK, a date or a word), the amount itself becomes red and bold.
The nice thing is that this also works for negative amounts, so for amounts you receive, such as reversal of a collection. Because the formula = C3> 0 does not mean that there must be a positive amount, but merely means: if there is anything in column C (a letter or a word also counts as “greater than zero”). Whereupon the cell next to it will discolour in column B.
To adjust this conditional formatting, click one of the cells you just set up (in column B), click Conditional formatting, Manage rules and Edit rule.
Discolor cell
You may not find it useful to make the amount paid red and bold and you would prefer the cell with that amount to turn gray. Then take the same steps as before; after entering the formula, click Format, but click in the window Format cells on the tab Padding and choose a shade of gray from the color palette. Then the cell with the amount will turn gray as soon as you type something next to the amount in column C.
The same formula is applied to all cells
You have the formula = C3> 0 entered for the whole series B3 to B30. Want to see how that works? Click on cell B3 again, click in the ribbon Start on Conditional formatting and click in the menu Manage rules. In the right part of the window you will see Formula: = C3> 0; that is the condition, if-then, but without IF before it. below Format you see the example of the layout. You can read below on the left Apply to: which cells this rule applies to: = $ B $ 3: $ B $ 30 in our case. Imagine the dollar signs, then it says B3 through B30 (this is a so-called absolute reference).
Close this window and click a cell a few rows below, for example, B5. Click again Conditional formatting and on Manage rules. You will see exactly the same formula in the settings window! You would expect to be here = C5> 0, as the row number usually moves when you copy down a formula. This also happens with conditional formatting, but you don’t see it here. You only see the formula as it applies to the first cell in this series.
As long as row number 3 of C3 is equal to row number 3 of B3 under Applies to, you can be sure that in the background for the color of B5 the formula will be applied = C5> 0. That is also going well, but it is not visible in this window.
Copy formatting
We’ve set up conditional formatting for the first month, January. For the following months it will be easy if you can copy this format. Do you know the trick with the make-up brush? That’s the button at the top left of the ribbon Start, below Cut and Copy. It’s called Copy / paste formatting and that is exactly what this button does. For example, if a cell is yellow and bold, and you want to give another cell this format, click the yellow and bold cell, click the Copy / Paste Format button, and click another cell; the formatting of the first cell is then transferred to the other cell.
This button can also be done with a whole column: click on the letter of a certain column, click on Copy / paste formatting and click on the letter of another column; all attributes from the first column are transferred to the other column. And if you don’t click on that one other column, but drag over more column letters, all the attributes of the original column are transferred to those other columns.
This is also possible for two columns that are next to each other. If you just click on column B, click the button Edges and choose from the menu the Left edge. Click on column C, click on the button Edges and choose from the menu the Right edge. This makes it clear that these two columns belong together, in the month of January. We will copy the formatting of these borders, plus the conditional formatting, using the Format Painter brush.
Select columns B and C (click on column letter B and drag aside so that B and C are selected). Click the Copy / Paste Format button, drag over columns D to Y and release; all formatting of columns B and C is transferred to the other months of the year.
Impressive code
If you want to see that this conditional formatting has ended up in the columns with the amounts, click on a cell with an amount, for example on F6. click on Conditional formatting and on Manage rules. In the settings window you see below Apply to an impressive code. Those are cells 3 through 30 of columns B, D, F, and so on, of exactly the twelve months.
Bee Formula see you still = C3> 0. This formula is therefore also applied horizontally, on the columns alternately, and automatically adjusted. In column D, Excel looks for something in column E, in column F it checks whether something is next to it in column G, and so on.
Variable cost
Fixed costs can be predicted reasonably, but variable costs make this more difficult. And now that we are working on the household booklet, we also want to take it with us. With the variable costs, we do not want to know whether an amount has been paid, but whether that amount was higher or lower than budgeted. For this we set up a budget for twelve months, we enter the actual amounts in a separate table and we compare the two. Next, we use color conditional formatting to indicate whether the amount was higher or lower than expected.
The setup looks like the image below.
Columns Q through AB contain the months with the amounts you expect. Columns B through M contain the same months and there are the actual amounts. Column N adds the amounts of each item, in N3 there is the formula = SUM (B3: M3).
Column O calculates the average per month; the formula for this is in O3: = N3 / 12. Copy these two cells down. Also copy N3 to AC3, copy O3 to AD3 and copy them down there, so that you can also see the totals of each item with the average per month in the budget.
Compare amounts
You hold in the left half, below REALLY, your actual expenses. If an amount is actually lower than budgeted, it is a stroke of luck. We want the cell to turn green with this amount. An amount that in reality is higher than was budgeted is a setback, that cell must turn red. We set up the conditional formatting for the entire table in one fell swoop. Select cells B3 through O30, start selecting in B3 and drag down. In the Name box, to the left of the formula bar, you should B3 see. This is important, because we will then draw up the formula, as it applies to B3, and then apply it behind the scenes to all selected cells.
click on Conditional formatting and choose New rule; a window will appear. Choose Using a formula… and in the box enter the formula: = B3
Click in this window Format, in the next window, take the tab Padding and choose light green. Close the windows with a click on OK. The same area is still selected? Then click again Conditional formatting, New rule. Choose again Using a formula… and in the box enter the formula: = B3> Q3. click on Format, take the tab again Padding and choose one light color red. Close these windows.
We are going to test: type in B3 an amount smaller than Q3: the cell with this amount turns green. In B3, type an amount greater than Q3: the cell turns red. And if the amount in B3 is equal to Q3, the cell remains white. This effect applies to the entire table ACTUAL. You can enter an amount under each month; that is immediately compared to what you had budgeted and the cell changes color.
Green amounts
Here and there you see a green cell with no amount. For example, if you had budgeted 50 euros for the bus in July, but you did not spend anything on it in July, that cell will turn green, because nothing is smaller than 50. Do you want empty cells to remain white and only turn green when you actually get there? entered a lower amount, please adjust the formula.
Click on one of the cells of the table REALLY (you don’t need to select the whole table), click in the tab Start on Vconditional formatting and on Manage rules. In the window that appears, double-click the line with the green area (or click Edit line); the formula box appears. Change this formula to: = AND (B3> 0; B3
The AND function says here: the amount in B3 must be greater than zero AND that amount must be less than Q3. Only then will the cell turn green. That gives a calmer picture.
If you want to read more about the countless possibilities of Conditional formatting, read the colorful “Expert Handbook Data Visualization in Excel”. Or “Excel for professionals”, both written by this author.
.