Back to lesson
1. The following PivotTable is configured with Category on rows, Week on columns and Units on Values. You’d like to change the PivotTable so that it displays with no decimal places. How should you modify it to make that happen?
Right click one of the values > Value Field Settings > Number Format > Select the number format you’d like to use.
Select all values in the body of the PivotTable > go to the Home tab > click the “Decrease Decimal” button.
Right click one of the values > Format Cells > Number > Select the number format you’d like to use.
2. This PivotTable looks like it has holes in it. How would we fix that and plug the empty cells with 0?
Right click one of the values > Value Field Settings > Number Format > Select a number format that displays zeros properly.
Type a 0 in all the empty cells.
Right click the PivotTable > PivotTable Options > check the box next to “For empty cells show” and enter a 0.
3. The PivotTable below shows an excerpt from a PivotTable including cells in the Values area which are numeric, blank and errors. What will happen if we change the PivotTable options as follows: For error values show: <blank> For empty cells show: 0
Cells H10, I10, H11, I11, I12 and I16 would all show as blanks.
Cells H10, I10, H11, I11, I12 and I16 would all show as zero.
Cells H10, H11 and I12 would show as zero. Cells I10, I11 and I16 would show as blanks.
Back to the top
© 2017 GoSkills Ltd.
Skills for career advancement