You spent hours creating an impressive table of loan payment calculations. Different loan amounts are across the top of the table, and a variety of terms and interest rates are at the left side. At a glance, you can see the monthly payment for any combination of variables. Sweet!
Then, your boss breaks your magical spell of awesomeness, by asking you to include the total payments for each combination. Sure, you could copy that sheet, and tweak the formulas, or add more columns, but then the workbook is
- double the size, and
- twice the maintenance.
Use a CheckBox
Thanks to Dave Peterson, there’s a new tutorial and sample file on the Contextures website – Excel Formula CheckBox. Instead of duplicating your work, and creating multiple sheets, you can solve the problem with a simple checkbox.
A checkbox at the top of the worksheet is linked to cell C1. If the box is checked, C1 is TRUE, and if it’s not checked, C1 is FALSE.
The loan payment formulas are modified, to include a reference to cell C1. The the box is checked, the monthly payment is multiplied by the total number of payments. The loan payment table shows the total amount to be repaid, instead of the monthly payment.
Other Uses for CheckBox Formulas
Of course, this technique isn’t limited to loan payment tables. You can use a checkbox selector in other workbooks too — for example, let users specify if tax should be included, or check the box if they want to see prices converted to US dollars.
Do you have any other ideas for changing the formula results with a checkbox?
Download the Sample File
For the detailed instructions, please visit the Contextures website – Excel Formula CheckBox. You can download the sample file there too.