Excel Tips: Go To Special Blanks with CTRL + Enter
In our last post we discussed the Go To Special dialog box using the options Comments, Constants, Formulas or Visible Cells Only. Very handy tools (see August AVC Newsletter for the post). This month we are going to cover the Blanks option in Go To Special. This option can make very short work of transforming a hierarchy formatted table into a tabular table where all labels are displayed on all rows.
But first let’s discuss a keyboard shortcut we will use in the process: CTRL + Enter. You can enter the same data in selected cells with the key combination of CTRL + Enter (hold down the CTRL key while simultaneously pressing the Enter key), whereas pressing the Enter key alone will fill in only the active or current cell.
For example, let’s say you have a column of Expenses you want to increase by 10% each for overhead costs. Here’s our starting point:
Table 1 [List of Expenses by Commitment Item]
To insert formulas that add 10% in column C:
- Highlight the cells C2:C8
- While the cells are highlighted type the formula =B2*1.1
- Press the CTRL + Enter keys
Table 2 [Fill Cells using CTRL + Enter]
This keyboard shortcut can be used for text, constants or formulas, and it can be used for contiguous cells (select the first cell then hold down the Shift key while selecting the last cell in the contiguous range) or non-contiguous cells (select the first cell then hold down the CTRL key while selecting each noncontiguous cell). You can accomplish the same thing with Copy and Paste, but you might find this keyboard shortcut faster (particularly for non-contiguous cells).
Now back to the Blanks option in the Go To Special dialog box. Assume you have been provided an Excel worksheet formatted as a hierarchy table, like we see in a Pivot table that has been created with more than one column of labels. But you need a tabular table with all labels in all rows for your analysis. For example, here’s a hierarchy formatted table looking at the unit times and unit costs for processing a deposit or withdrawal at a bank:
Table 3 [Fill Cells using CTRL + Enter]
To add all labels to all rows and create a tabular table:
- Highlight the contiguous range A3:C17 (hold down the Shift key and click cell A3 then cell C17)
- In the Home ribbon under Find & Select click Go To Special, then select the Blanks option and hit OK
- Type the formula =C2 since after selecting the Blanks option and hitting OK you should be cell C3 (if Excel highlights cell A3 rather than C3, then type the formula =A2)
Table 4 [After Selecting Blanks in Go To Special and Typing =C2]
- Lastly, press CTRL + Enter
What we are doing here is pointing to the cell directly above the current cell, which will be the current cell’s label value if the cell is blank.
Table 5 [After Pressing CTRL + Enter]
Since we have formulas in the previously blank cells in range A3:C17, you should change those formulas to values to lock them down before you manipulate this table. Copy the range A3:C17 and then select Paste in the Home ribbon and then Paste Values. This will change all the formulas in the range to values, in this case the row labels.
Note that pivot tables by default will create a hierarchy formatted table if you have more than one column of labels (above we have three columns of labels: Activity Name, Channel and Product Name). You can avoid this by following these steps:
- Create the pivot table as you normally would selecting the labels desired
- In the Design ribbon select the Report Layout drop-down and then Show in Tabular Form
- The pivot table will now have a tabular table format where all labels are repeated in all rows
- If desired, remove the Subtotals by selecting the Subtotals drop-down in the Design ribbon and Do Not Show Subtotals
So we see another powerful use of the Go To Special dialog box combined with the fill all selected cells CTRL + Enter keyboard shortcut. Formula on, dudes…