1. Hiding unused cells
At the end of your visualization exercise, your visualization was still showing periods which were not in use.
In this lesson, we'll go over techniques to hide the periods that were not used in order to clean up your schedule and visualizations.
2. Easier to subtract than add
When you create an amortization dashboard, you should create a schedule with more periods than you need, as it is impossible to create additional rows after the schedule has been set.
For example, if you think that the dashboard could be used for bi-weekly schedules for a 30 year loan, that would be 780 periods!
But, if you do not need all those periods, your visualizations will still show the unnecessary periods, and some of your formulas will show N/A.
3. Methods to hide unused cells
There are 2 methods which can be used to hide unused values.
The first is to create a second table, using the FILTER() formula. This is a simple, 1 cell formula, but it also means that you can't do advanced adjustments; which we'll discuss in the final chapter.
The second method is to use IFS() formulas to hide values which are not being used. This will require adding a large number of adjustments to your schedule, but provides maximum flexibility.
4. The FILTER formula
The FILTER() formula copies a table based on a specified condition.
First, take the schedule, which has all the formulas, from period 1 through the final period and move it at least 10 rows to the right. This table will not be used, except for the calculations.
Then, add the FILTER() formula in place of the calculation schedule. This should refer to all of the columns from the date column through the final column.
The filtering condition must be the opening balance column, filtering on balances greater than zero.
This will create a copy of the calculation table, hiding all rows which are zero or N/A.
5. Hiding cells with IFS
The other way to hide unused cells is with the IFS() formula.
There are 2 cases where a row may need to be hidden.
The first case is where the row is the first row after the final amortization period; that is, where the closing balance of the prior row is zero. Since there are some cases where there could be odd calculation issues, the formula needs to check if the cell is less than 1 cent.
The other case is where the prior row had already been suppressed - so the prior row is blank.
If neither condition is met, then the IFS() formula uses a condition that the prior closing balance is greater than zero - then use the formula that existed before the IFS() formula.
6. Tips with IFS formula
Modifying all the formulas would take a long time to do manually!
Fortunately, there are some ways to speed up the process of adjusting the formulas.
First, you can use the same column to check for blank values. Notice how column A was used to check for blanks in 2 different formulas.
Second, instead of using values for the period, replace with a ROW() formula. In the example, it was period 241 on row 244, so it uses the ROW() minus 3. This formula allows the period to be copied to the entire schedule.
Finally, you should be able to copy your formulas from period 1 to the bottom of your schedule.
7. Time to fix some schedules!
Now that you know how to fix the schedules, its time for you to fix schedules with FILTER() and IFS()!