How to make a summary table in Excel

Pivot Tables are an interactive display of information that allows users to analyze groups and large amounts of data in a concise, tabular format for easier reporting and analysis. One of the advantages of this function in Excel is that it allows you to change, hide and display different categories in one file to provide an alternative representation of the data. In this article, you can read the instructions on how to make a spreadsheet in Excel.

Step 1 of 3: Setting from scratch

Start Microsoft Excel.

Click on the menu item "Data", then select the summary table (note: in Excel versions 2007/2010, click "Paste"> PivotTable).

Specify the location of the data that you intend to summarize. If this information is in the Excel list that is currently open, the program automatically selects a range of cells. Click "Next".

Make sure that the "New list" option is selected after clicking "Next" from the previous step. After you have done this, click "Done".

Step 2 of 3: How to create a table in Excel

If necessary, adjust the range in the text box. These attributes are located under the "Select a table" menu item. If the information source for the table is external, created in a separate program (for example, in Access), click the "Use external data sources" button. Then find the menu item "Select connections", and then click in the dialog box called "Existing connections".

Make a choice of a place to create a pivot table. By default, Excel mode creates it as a new sheet that is added to the workbook. Talking about how to make a table in Excel, that it appeared on the given sheet, you must click on the "Existing sheet" selection button, then specify the location of the first cell in the "Location" text box.

Click OK. Then you should add a clean new grid to the PivotTable, and then you need to display the "PivotTable Fields" task pane. This panel will be divided into two parts.

Open the menu item "Select fields to add to the report" and enter all field names in the original data.

Filling

How to make a spreadsheet in Excel? Continuing to follow the instructions, you should get a field on the screen divided into four work areas, in which to specify the filter, column labels, rows and values.

Assign the fields in the "Pivot Table Fields" task pane in the various parts of it. Drag the field name from the "Select fields to add to report" menu item to each of the four zones used.

Perform the settings as needed until your ideal results appear.

Step 3 of 3: Understanding the initial terms, or How to make the table in Excel easier

First of all, try to understand the key definitions. There are several terms that may, at first glance, seem confusing.

What is a filter? This area contains fields that allow you to search for data specified in the pivot table, filtering them on some basis. They act as labels for the report. For example, if you assign the Year field as a filter, you can display summaries of data in a table for individual periods of time or for all years.

Column labels - this area has fields that determine the location of the data shown in the columns of the pivot table.

String signatures - The specified area includes fields that determine the location of the data entered in the rows.

Values contain fields that determine what data is represented in the cells of the pivot table. In other words, this is the information that is summarized in the last column (compiled by default).

The field from the original data that you assign to the page (or filter) is displayed in the PivotTable report.

Understanding the above values, as well as some other settings, will allow you to better understand how to make a spreadsheet in Excel. Remember that in some versions of the program some menu items may be different.