ווי צו שאַפֿן אַ פּשוט דרייפּונקט טיש אין עקססעל?

This part of the tutorial details how to create a PivotTable in Excel. This article was written for Excel 2007 (as well as later versions). Instructions for earlier versions of Excel can be found in a separate article: How to create a PivotTable in Excel 2003?

As an example, consider the following table, which contains sales data for a company for the first quarter of 2016:

To begin with, let’s create a very simple pivot table that will show the total sales of each of the sellers according to the table above. To do this, do the following:

1. Select any cell from the data range or the entire range to be used in the pivot table.Attention: If you select one cell from a data range, Excel will automatically detect and select the entire data range for the PivotTable. In order for Excel to select a range correctly, the following conditions must be met:
   • Each column in the data range must have its own unique name;
   • The data must not contain empty lines.
2. Clicking the button קיצער טיש (דרייווינג טאַבלע) אין אָפּטיילונג טישן (Tables) tab אַרייַנלייגן (ינסערט) עקססעל מעניו ריבאַנז.
3. א דיאַלאָג קעסטל וועט דערשייַנען אויף דעם עקראַן. שאַפֿן אַ פּיוואָטטאַבלע (שאַפֿן פּיוואָטטאַבלע) ווי געוויזן אין די פיגור אונטן.Make sure the selected range matches the range of cells that should be used to create the PivotTable. Here you can also specify where the created pivot table should be inserted. You can select an existing sheet to insert a pivot table on it, or option – צו אַ נייַ בלאַט (New worksheet). click OK.
4. An empty pivot table will appear, as well as a panel דרייפּונקט טיש פעלדער (Pivot Table Field List) with multiple data fields. Note that these are the headers from the original datasheet.
5. אין פּאַנאַלז דרייפּונקט טיש פעלדער (Pivot Table Field List):
   • שלעפּן און קאַפּ סאַלעס רעפּ. צו דער געגנט ראָוז (רודערן לאַבעלס);
   • שלעפּן און קאַפּ סומע в די וואַלועס (Values);
   • We check: in די וואַלועס (Values) must be a value Amount field Amount (Sum of Amount), а не Amount by field Amount (Count of Amount).

   In this example, the column סומע contains numeric values, so the area Σ וואַלועס (Σ Values) will be selected by default Amount field Amount (Sum of Amount). If in a column סומע will contain non-numeric or empty values, then the default pivot table can be selected Amount by field Amount (Count of Amount). If this happens, then you can change the quantity to the amount as follows:

   • אין די Σ וואַלועס (Σ Values) click on Amount by field Amount (Count of Amount) and select the option ווערט פעלד אָפּציעס (Value Field Settings);
   • אויף די אַוואַנסירטע קוויטל אָפּעראַציע (Summarise Values ​​By) choose an operation סאַכאַקל (Sum);
   • דריק דא OK.

The PivotTable will be populated with the sales totals for each salesperson, as shown in the image above.

If you want to display sales volumes in monetary units, you must format the cells that contain these values. The easiest way to do this is to highlight the cells whose format you want to customize and select the format געלטיק (Currency) section נומער (נומער) קוויטל היים (Home) Excel menu ribbons (as shown below).

As a result, the pivot table will look like this:

• pivot table before number format setting
• pivot table after setting currency format

Please note that the default currency format depends on the system settings.

Recommended PivotTables in Latest Versions of Excel

In recent versions of Excel (Excel 2013 or later), on the אַרייַנלייגן (Insert) button present רעקאַמענדיד דרייפּונקט טישן (Recommended Pivot Tables). Based on the selected source data, this tool suggests possible pivot table formats. Examples can be viewed on the Microsoft Office website.