How to Group Rows and Cells in Excel - and Outline them too

 

excel outline format

Dec 24,  · An Outline allows you to switch your focus between summary information for a category and the details that make up that summary. It is easy to create an Outline - using Auto Outline - . Hide an outline (Excel ) On the Home tab, in the Cells group, click Format, point to Hide & Unhide, and then click Unhide Rows or Unhide Columns. Create a summary report with a chart. Imagine that you want to create a summary report of your data that only displays totals accompanied by a chart of those totals. In general, you can do the. How To Group Rows And Cells in Excel – And Outline Them Too!. Written by co-founder Kasper Langmann, Microsoft Office Specialist.. Spreadsheets often have different levels of data. And those levels can be difficult to parse when you’re trying to read them.


Excel QuickTip: Change Excel’s Outline Settings


After creating a PivotTable and adding the fields that you want to analyze, you may want to enhance the report layout and format to make the data easier to read and scan for details.

To change the layout of a PivotTable, you can change the PivotTable form and the way that fields, columns, rows, subtotals, empty cells and lines are displayed. To change the format of the PivotTable, you can apply a predefined style, banded rows, and conditional formatting.

Note that the procedures in this topic mention both Analyze and Options tabs together wherever applicable. To make substantial layout changes to a PivotTable or its various fields, you can use one of three forms:.

Row labels take up less space in compact form, which leaves more room for numeric data. Expand and Collapse buttons are displayed so that you can display or hide details in compact form.

Compact form is saves space and makes the PivotTable more readable and is therefore specified as the default layout form for PivotTables. On the Design tab, in the Layout group, click Report Layoutand then do one of the following:. To keep excel outline format data from spreading horizontally off of the screen and to help minimize scrolling, click Show in Compact Form. In compact excel outline format, fields are contained in one column and indented to show the nested column relationship.

To see all data in a traditional table format and to easily copy cells to another worksheet, click Show in Tabular Form. To show field excel outline format in excel outline format form, click Show item labels in outline form. To display or hide labels from the next field in the same column in compact form, click Show item labels in outline formand then select Display labels from the next field in the same column compact form.

To show field items in table-like form, click Show item labels in tabular form. To get the final layout results excel outline format you want, you can add, rearrange, and remove fields by using the PivotTable Field List. If you don't see the fields that you want to use in the PivotTable Field List, you may need to refresh the PivotTable excel outline format display any new fields, calculated fields, measures, calculated measures, or dimensions that you have added since the last operation.

On the Options tab, in the Data group, click Excel outline format. Select the check box next to each field name in the field section. The field is placed in a default area of the layout section, but you can rearrange the fields if you want. Click and hold a field name, and then drag the field between the field section and an area in the layout section. In a PivotTable that is based on data in an Excel worksheet or external data from a non-OLAP source data, you may want to add the same field more than once to the Values area so that you can display different calculations by using the Show Values As feature.

For example, you may want to compare calculations side-by-side, excel outline format, such as gross and net profit margins, minimum and maximum sales, or customer counts and percentage of total customers. For more information, see Show different calculations in PivotTable value fields. Click and hold a excel outline format name in the field section, and then drag the field to the Values area in the layout section.

In each copied field, change the summary function or custom calculation the way you want, excel outline format. When excel outline format add two or more fields to the Values area, whether they are copies of the same field or different fields, the Field List automatically adds a Values Column label to the Values area.

You can use this field to move the field positions up and down within the Values area. You can add a field only once to either the Report FilterRow Labelsor Column Labels areas, whether the data type is numeric or non-numeric. Another way to add the same field to the Values area is by using a formula also called a calculated column that uses that same field in the formula.

You can rearrange existing fields or reposition those fields by using one of the four areas at the bottom of the layout section:. Use to display fields as rows on the side of the report. A row lower in position is nested within another row immediately above it, excel outline format.

Use to display fields as columns at the top of the report. A column lower in position is nested within another column immediately above it. To rearrange fields, click the field name in one of the areas, and then select one of the following commands:, excel outline format.

Value Field SettingsField Settings. For more information about each setting, click the Help button at the top of the dialog box. You can also click and hold a field name, and then drag the field between the field and layout sections, and between the different areas.

Click and hold a field name in the layout section, and then drag it outside the PivotTable Field List. To further refine the layout of a PivotTable, you can make changes that affect the layout of columns, rows, and subtotals, such as displaying subtotals above rows or turning column headers off.

You can also rearrange individual items within a row or column. To switch between showing and hiding field headers, on the Analyze or Options tab, in the Show group, click Field Headers.

To display subtotals above the subtotaled rows, select the Display subtotals at the top of each group check box. This option is selected by default. To display subtotals below the subtotaled rows, clear the Display subtotals at the top of each group check box. In the PivotTable, right-click the row or column label or the item in a label, point to Moveand then use one of the commands on the Move menu to move the item to another location.

Select the row or column label item that you want to move, and then point to the bottom border of the cell. When the pointer becomes a four-headed pointer, excel outline format, drag the item to a new position.

The following illustration shows how to move a row item by dragging. To automatically fit the PivotTable columns to the size of the widest text or number value, select the Autofit column widths on update check box. To keep the current PivotTable column width, clear the Autofit column widths on update check box. You might want to move a column field to the row excel outline format area or a row field to the column labels area to optimize the layout and readability of the PivotTable, excel outline format.

When you move a column to a row or a row to a column, you are transposing the vertical or horizontal orientation of the field. This operation is also called "pivoting" a row or column.

Drag a row or column field to a different area. The following illustration shows how to move a column field to the row labels area. You can merge cells for row and column items in order to center the items horizontally and vertically, or to unmerge cells in order to left-justify items in the outer row and column fields at the top of the item group.

On the Options tab, in the PivotTable group, click Options. There may be times when your PivotTable data contains blank cells, blank lines, or errors, and you want to change the way they are displayed. To change the error display, select the For error values show check box. In the box, type the value that you want to display instead of errors. To display errors as blank cells, delete any characters in the box.

To change the display of empty cells, select the For excel outline format cells show check box, and then type the value that you want to display in empty cells in the text box. To display zeros, clear the check box. On the Display tab, under Displaydo one or more of the following:. To show items with no data on rows, select or clear the Show items with no data on rows check box to display or hide row items that have no values. To show items with no data on columns, select or clear the Show items excel outline format no data on columns check box to display or hide column items that have no values.

To display item labels when no fields are in the values area, select or clear the Display item labels when no fields are in the values area check box to display or hide item labels when there are no fields in the value area.

You can choose from a wide variety of PivotTable styles in the gallery. In addition, you can control the banding behavior of a report. Changing the number format of excel outline format field is a quick way to apply a consistent format throughout a report. You can also add or remove banding alternating a darker and lighter background of rows and columns. Banding can make it easier to read and scan data.

You can quickly change the look and format of a PivotTable by using one of numerous predefined PivotTable styles or quick styles. On the Design tab, in the PivotTable Styles group, do any of the following:. To see all of the available styles, click the More button at the bottom of the scroll bar.

To alternate each row with a lighter and darker color format, click Banded Rows. To alternate each column with a lighter and darker color format, click Banded Columns, excel outline format. On the Design tab, in the PivotTable Styles group, click the More button at the bottom of the scroll bar to see all of the available styles, and then click Clear at the bottom of the gallery.

Use a conditional format to help you visually explore and analyze data, excel outline format, detect critical issues, and identify patterns and trends. Conditional formatting helps you answer specific questions about your data.

There are important differences to understand when you use conditional formatting on a PivotTable:. If you change the layout excel outline format the PivotTable by filtering, hiding levels, excel outline format, collapsing and expanding levels, or moving a field, the conditional format is maintained as long as the fields in the underlying data are not removed.

The scope of the conditional format for fields in the Values area can be based on the data hierarchy and is determined by all the visible children the next lower level in a hierarchy of a parent the next higher level in a hierarchy on rows for one or more columns, or columns for one or more rows.

There are three methods for scoping the conditional format of fields in the Values area: by selection, by corresponding field, and by value field.

For more information, see Apply conditional formatting. The Field Settings dialog box displays labels and report filters; the Values Field Settings dialog excel outline format displays values.

In the Format Cells dialog box, in the Category list, click the number format that you want to excel outline format. Select the options that excel outline format prefer, and then click OK twice. To enable or disable number formatting, such as currency, dates, and times, select or clear the Number Format check box, excel outline format.

To enable or disable font styles, such as bold, italics, underline, and strikethrough, select or clear the Font Style check box. To save the PivotTable layout and format so that it is used each time that you perform an operation on the PivotTable, excel outline format the Preserve cell formatting on update check box, excel outline format.

To discard the PivotTable layout and format and resort to the default layout and format each time that you perform an operation on the PivotTable, clear the Preserve cell formatting on update check box, excel outline format. In Excel for the web, you can apply sorting and filtering to a PivotTable, excel outline format, and move its fields and change value field settings in the Field List, but you can't change its data layout.

You have to use the desktop version in order to be able to change the layout. See Design the layout and format of a PivotTable. You can always ask an expert in the Excel Tech Communityget support in the Answers communityor suggest a new feature or improvement on Excel User Voice. Excel outline format your Office skills, excel outline format.

 

Design the layout and format of a PivotTable - Excel

 

excel outline format

 

Dec 24,  · An Outline allows you to switch your focus between summary information for a category and the details that make up that summary. It is easy to create an Outline - using Auto Outline - . Outline Excel Data in Microsoft Excel – Instructions: A picture of an Excel worksheet with an auto outline applied. To outline Excel data by applying an outline to a selected cell range, first select the Author: Joseph Brownell. Hide an outline (Excel ) On the Home tab, in the Cells group, click Format, point to Hide & Unhide, and then click Unhide Rows or Unhide Columns. Create a summary report with a chart. Imagine that you want to create a summary report of your data that only displays totals accompanied by a chart of those totals. In general, you can do the.