Appendix E – Excel 2016 Troubleshooting
|
You will see the yellowish Create Relationships box appear sometimes but creating a relationship is not the answer. Frequently, this yellow box can be cleared simply by relocating a recent field addition. If you recently added a field with numerical data, Excel automatically placed the field in the Values quadrant and either summed or counted it as well. Try moving the field over to the Rows quadrant. There’s a good chance that will clear the yellow create relationship box and you can continue on with your work (FIGURE 1).
|
 FIGURE 1
|
|
Here’s an example. I recently added the CustomerID field by checking its box in the Field List. Since the data is numerical, Excel automatically placed it in the VALUES quadrant and summed it. My pivot table data ballooned from 186 rows to 118,444 rows (FIGURE 2).
|
 FIGURE 2
|
|
The fix to this issue was moving the Sum of CustomerID field over to the ROWS quadrant (FIGURE 3).
|
 FIGURE 3
|
|
Excel has an interesting way of keeping track of dates. It uses what is known as the “1900 date system”, which means Excel begins tracking each consecutive day as a running number total, beginning with January 1, 1900 as day 1. Microsoft Excel for Macintosh uses the 1904 date system, so that system begins tracking each consecutive day as a running total, with January 1, 1904 as day 1.
The problem is that your date field shows only large numbers, as shown in FIGURE 4. |
 FIGURE 4
|
|
The fix is simple. Highlight the Date column, right-button mouse click the highlighted column, select Format Cells on the mouse shortcut menu. On the Format Cells dialog box, select Date for the category, and choose which date type you prefer in the section (FIGURE 5).
|
 FIGURE 5
|