Excel select:Pivot table – selecting the table or table elements

Excel select: YOUR OBJECTIVE

You created a pivot table that summarizes your company’s sales. To make it easier to read and understand your pivot table, you want use color formatting to emphasize certain parts of your table. In fact, you can format your customer groups, sales regions, total order values, i.e., certain rows, columns or totals. But before you can format pivot table areas, you must select them first.

This learning module “ Excel select”shows you how to easily select pivot-table elements.

Excel select: HOW DOES IT WORK?

Excel: Select various table elements

You can select individual pivot-table elements or the entire pivot table. Even though Excel provides buttons in the Ribbon that help you select specific pivot-table elements, a pivot-table can also be managed like any other Excel table. That is, you can select any pivot-table cell or range of cells in the usual fashion, using just your mouse. The following paragraphs show you the pivot-table-specific methods for selecting a pivot table or its various elements:

Excel: Select the entire pivot table

To select an entire pivot table, go first to the pivot-table Options tab under the PivotTables Tools context tab. Then, in the Actions group, click the  button. In the selection list that appears, click the Entire PivotTable command.

Excel select:CAUTION

Remember that the PivotTable Tools context tabs appear in the Ribbon only when the pivot table has been activated. Activate the pivot table by clicking once anywhere on it.

Excel: Select all items belonging to a particular field

Move your mouse pointer toward the top edge of the column label you want to select. It will change into a black arrow . If you now click your left mouse button once, all items in that field, including the field label and the column total, will be selected.

Similarly, if you want to select all items associated with a row label, move your mouse pointer toward the left edge of any row label that interests you until it changes into a black arrow  . When you click your mouse button now, all items in the row, including the label and the row total, will be selected.

Excel: Selectall row or column labels and values

You can easily select all labels and values, or just the labels, or just the values. When values are selected using this method, the subtotals and grand totals are included in the selection. To select the entire tpivot table, go first to the pivot-table Options tab under the PivotTables Tools context tab. There, in the Actions group, click the  button. Now, in the selection list that appears, click the  command.

Doing that also enables the first three menu items, which now can be used.

Choosing the  option, selects all labels, values and totals; choosing the option, selects only the data, including all totals; and choosing the  option, selects all row and column labels.

Excel: Select all occurrences of a field

In the following example, the Customer Group field (in the source table: A, B and C) was brought into the Row Labels area. Additionally, the Sales Areas field was also brought into the Row Labels area, subordinated to the Customer Group. That is why you see, vertically, each customer group label (A, B, C) followed by sales areas: East, Midwest and North. The cities field, indicating where the customer is located, was brought into the Column Labels area, which explains why the several columns are headed by city names. Now, because some customers in a particular group are also in a certain region, a region may appear several times under a customer group. In the pivot table below, for example, the Midwest sales area appears three times, i.e., once under each customer group:

If you want to select all elements associated with the Midwest sales area in all three customer groups, move the mouse pointer toward the left of any one of the three occurrences of the Midwest label. When the black arrow appears, click the left mouse button. All identical occurrences of that label (the entire row) will be selected in all customer groups at one time.

Excel selecting: HINT

You can, of course, select all occurrences of identical fields among the columns in the same way.

Excel: Select just one occurrence of a field with multiple occurrences

If you want to select only one occurrence of a field, though it may appear multiple times, proceed first as described above under Selecting all occurrences of a field section. Move your mouse pointer just toward the left of the one occurrence that interests you. But this time, when the black arrow appears, double-click the mouse button.

In our current example, only the Midwest sales area under customer group A has been selected:

Excel selecting: HINT

You can, of course, use this same technique for the pivot table columns.

Excel: Select multiple fields

Now, you want to select several columns in a pivot table.

Begin by selecting one column, as described in the Selecting all items belonging to a particular field section: i.e., move your mouse pointer toward the upper edge of a column label until it changes to a black arrow . Then click your left mouse button. All items in the column will be selected.

Now, i you then want to select an adjacent row (or rows), press the left- or right-arrow keys –  or  - while holding down the [Shift] key. Alternatively, you can select multiple adjacent columns by positioning the mouse pointer to the farthest column of interest until the black arrow appears and then, while holding holding down the [Shift] key, click your mouse button.

To select multiple adjacent or non-adjacent columns, select a column (as above) and then, while holding down the [Ctrl] key, point just above any additional column until the usual black arrow appears and click once; As long as you continue to press [Ctrl], you can in this way select as many additional columns as you like.

To select several adjacent or non-adjacent rows (field label, data and total), proceed in a fashion similar to the methods for columns.

Excel: Selecttotals

To select pivot-table totals, whether in row or column, position your mouse pointer, just as for other fields. When you move your mouse pointer above the column total or to the left of the row total, the mouse pointer changes to the now familiar black arrow. A single mouse click will correspondingly select entire the totals row or column.

Please don’t lose sight of the fact that we were selecting various pivot-table areas in order to format them. Once an area or areas have been selected, you can go ahead and apply a fill color or any other suitable format.

Did you like it? Then share it or sign up!

You can share this article with your friends or sign up for our newsletter for further information.

Leave a comment

Connect with Facebook


Kein Banner zum Anzeigen