Pivot tables are an interactive display of information that allows users to analyze groups and large amounts of data in a compressed, tabular form 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 table in Excel.
Step 1 of 3: Setting Up from the Ground Up
Launch Microsoft Excel.
Click the "Data" item in the menu, and then select the pivot table (note: in Excel 2007/2010, click Insert> Pivot Table).
Indicate 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 on “Next” from the previous step. Once you have done this, click Finish.
Stage 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 menu item "Select a table." 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. After that, find the menu item “Select Connections”, and then click in the dialog box called “Existing Connections”.
Select a location to create the pivot table. By default, Excel mode creates it as a new sheet to add to the workbook. Speaking about how to create a table in Excel to make it appear on a given sheet, you need to click on the "Existing sheet" selection button, then specify the location of the first cell in the "Location" text box.
Click the OK button. Then add a clean new grid for the pivot table, after which you will need to display the task list "List of Pivot Table Fields". 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 source data.
Filling out
How to make a table in Excel? Continuing to follow the instructions, you should receive on the screen a field divided into four working areas, in which you should specify a filter, column labels, rows and values.
Assign the fields in the "List of Pivot Table Fields" task pane in its various parts. Drag the field name from the menu item "Select fields to add to the report" in each of the four zones used.
Make adjustments as needed until your perfect results appear.
Stage 3 of 3: Understanding the original terms, or How to make the table in "Excel" easier
First of all, try to deal with key definitions. There are several terms that may, at first glance, seem obscure.
What is a filter? This area contains fields that allow you to search for the data specified in the pivot table, filtering them by any criterion. They act as labels for the report. For example, if you designate the Year field as a filter, you can display data summaries in a table for individual time periods or for all years.
Column labels - this area has fields that determine the location of the data in the columns of the pivot table.
Line signatures - the specified area includes fields that determine the location of the data entered in the lines.
The values contain fields that determine what data is presented in the cells of the pivot table. In other words, this is the information that is summarized in the last column (compiled by default).
A field from the source data that you assign to the page (or filter) is displayed in the pivot table report.
Understanding the above values, as well as some other settings, will allow you to better understand how to make a table in Excel. Remember that in different versions of the program, some menu items may differ.