.
...
The next step is adding references to the data sheet. This example references the "SUM" formulas on the data sheet. These sums are added to a percent change equation. This will result in a template resembling the following: 
Note the formula in the formula bar, "Sheet2" is the data sheet.
Also, the error message is because there currently isn't any data, so Excel is throwing a 'Divide by 0' error. This will not be in the output file because the file will contain data.
| Info | ||
|---|---|---|
| ||
In this example, the sum rows alternate on the data sheet. If you stretch a formula, you'll have to update the references to skip every other row. |
1. Select C4 on Sheet1.
2. Start to type the formula by entering =(
3. Click over to Sheet2, Row 2 and select the second SUM cell. (B2 in this example)
4. Go back to Sheet1 and add a minus sign for =(Sheet2!B2-
5. Click over to Sheet2, Row 2 again and select the first SUM cell.
6. Continue selecting cells into formulas to end up with this: The formula we are using shows percent change. It is set up like this: "=(Sheet2!B2-Sheet2!A2)/(Sheet2!A2)
7. Drag this formula Horizontally only because of the alternating rows.
8. Repeat with rows 4 and 6
"
Number Formats
The value returned by the percent change equation should be displayed as a percentage. The table cells all have to be formatted.
...
5. Now select B2:E2 and apply a bottom border in dark blue.
The final template should look like this:
Conditional Formatting
ExcelTemplate will persist conditional formatting in a template. In this tutorial, conditional formatting is applied to the "Other" table. It sets negative numbers to be red and bold.
1. On the "Home" tab in Excel, click on "Conditional Formatting"
2. Select "New Rule..."
3. In this tutorial the condition type is "Format only cells that contain..." The rule is "Cell value less than 0"
4. Click on "Format..." Set the text to be dark red. Set the typeface to be bold.
5. Click OK to save the rule. 
Adding an ExcelWriter Reference in Visual Studio
...