One of the rules for working with an Excel database and preparing it for a PivotTable is that the raw list of worksheet data must contain a header (individual column headings) row and that row can only be one row deep.

NOT this!

PivotTable Rule 1 - 1

 

Below is a workbook with titles on one row but  that makes the columns too wide:

PivotTable Rule 1 - 2

Below the title row is set to wrap text and the columns, when sized to fit, are narrower but do not break where I want:

PivotTable Rule 1 - 3

Use Alt + Enter to split the titles into multiple lines within the cell.  It this example, click before 1st and Press ALT + Enter.  Repeat for 2nd, 3rd, 4th, and Year:

 PivotTable Rule 1 - 4

Now for the Issue of using ALT + Enter in a Field title:

I am creating a PivotTable with the above data (not the ideal format/layout but it will work) and want to add a Calculated Field that gives me the Commission for the Total Year based on a 25% commission rate.

The field titles have all been split by using Alt + Enter.  Notice in the Formula: for the Insert Calculated Field dialog box (below) that I only see =‘Sales.  The remaining part of the field name is out of sight.

PivotTable Alt + Enter Issue in Calculated Field

Solutions:

  • At the end of the field you see in the Formula section, press and hold the right arrow until it stops moving.  It MIGHT show a small flashing dot where the field name ends on the line below what you can see.  At that point, I could type the balance of the formula BUT I would have to TRUST that I entered the correct information because I will not see it.
  • Cancel the Insert Calculated Field process and return to the data and remove the Alt + Enter by expanding the Formula Bar, click at the start of the 2nd line and press Backspace.  The Field title is now on one line.  (See below)  You will need to Refresh the PivotTable and, perhaps, add the field back to the PivotTable.

PivotTable Alt + Enter Issue in Calculated Field cleanup1     PivotTable Alt + Enter Issue in Calculated Field cleanup2

 Issue Solved:

PivotTable Alt + Enter Issue in Calculated Field cleanup3

 

 

Thank you to DB, DH, LW, & NW for pointing out this issue during our PivotTable class.