One not so well-kept secret is that Excel likes patterns and tries to help you when it believes it finds one. For example, you can write January in a cell, select the cell and then grab and drag down the right corner of the cell then Excel continues with the monthly names.
This works great on numbers, dates and the like! I use the same feature later on to copy formulas from one cell to the next in rows or columns.

For further examples, I have filled some headlines around the months. We will generate budget per month for 4 regions.

Before we start filling out the table we just created, I create a separate table for the different regions and find that we should allocate our budget per number of employees within each region. This can be something else of course, number of computers, desks, trucks, percentage of pay or something completely different. For the example, I just want the regions not to have an equal share of the budget.
To create the percentage column, you see the formula to the right. The important thing is the dollar signs in the “Total” formula. By using $ in front of 4 and 7, we lock the formula when it is copied so that it does not change. L4 that stands first has no dollar sign and therefore changes to L5 when I copy the formula one line down.

Now we take the region’s share of the budget and add it to the table by the formula below.

We make up how our budget is spread out like this every month of the year. The only important thing now is that the sum will be 100%.

In the cell where January and Region 1 meet, we write the following formula to see how much of the region 1’s budget will be covered in January.

The problem now is that when we copy the formula to the next line, the formula just changes line. We now take 8% of the January budget instead of the full-year budget!

This is solved through dollar signs. By putting a dollar sign in front of C we say to Excel that the formula must not leave the column C, however, it may change row as there is no dollar sign before the row number.
For the second part of the equation, we put the dollar sign in front of the row number instead of the column letter. In this way, the formula changes column, but not row.

This time when we copy the formula, the calculation stops right where we want it!

And it works in both directions. When we go aside, the percentage does not change column but, on the other hand, it makes the total budget for the region.

There are 48 cells in the matrix below. Everyone counts correctly just by putting dollar signs in the formula and we get the right formula in the right cell in a matter of seconds!

Now we calculate the sum of all regions’ budgets per month.
We only use the sum function and merge all rows. Copy the formula downwards so that the references in the formula follow the line by row.

Now, for some strange reason, let us assume that we definitely want SEK 50,000,000 in the April budget. There are 2,000,000 missing there. And we want to get there by increasing the total budget, not changing a few percentage points per month.
This is a typical job for Golad seek that you will find under the Data tab!
Mark the cell to be changed, in our case SEK 48,000,000. Fill in the desired value, in our case SEK 50,000,000. Then select the cell that Excel should change to achieve the result.

When we press OK, Excel calculates the new value based on how the cells are connected. It does not matter that we ourselves at this point hardly remember how the cells count in relation to each other.

We make a control column! It is of the utmost importance that we know which months regions 2 and 4 sums are less than SEK 7,500,000 and where region 1 also ends at exactly SEK 7. We can achieve this with the formula “AND”.
In the formula below, which is separated by a semicolon for the arguments, we first say that Region 2 and 4 are lower than SEK 7,500,000, then we say that the sign to the far right in region 1 is “7”. If both of these arguments are true, the cell will say “True” and if any of them do not match it is instead “False”.

=AND(E21+G21<7500000;RIGHT(D21;1)=”7″)

Since that text is really boring, we can change it by inserting the previous formula in an IF statement. Copy the previous formula and insert it as the logical check in the IF statement. Then we just fill in what we want to show if the arguments are true and false.

=IF(AND(E21+G21<7500000;RIGHT(D21;1)=”7″);”Sanningsenligt”;”Lögnaktigt”)

I hope some of these features where news to you and that using the will save you some valuable time this year!