Field List & Areas | MyExcelOnline

When working with Pivot Tables, one of the most crucial concepts is understanding how the Field List and its different Areas work. Being able to utilize this correctly will spell the difference on how effective you can perform your analysis with Pivot Tables!

Let us go over the different sections:

  • Fields
  • Rows
  • Columns
  • Values
  • Report Filter

Exercise Workbook:

DOWNLOAD EXCEL WORKBOOK


Here is our initial data table:

Field List & Areas | MyExcelOnline

STEP 1: Now the first part is the Fields section, which represents the column headings of our data source:

field list

STEP 2: The Row labels show the unique fields on the left-hand side of our Pivot Table. Drag PRODUCTS to Rows to see how this looks like.

Field List & Areas | MyExcelOnline

Now you can see our Products are listed on the Pivot Table.

Field List & Areas | MyExcelOnline

STEP 3: The Column labels show the trend of your data. For example, this could be periods, trends, time, month, years.

Let us drag the SALES YEAR to the Columns

Field List & Areas | MyExcelOnline

Now you can see the years 2012 – 2014 as the columns.

Field List & Areas | MyExcelOnline

STEP 4: Values are fields that you want to calculate or quantify. Examples are sum (for sales), count (for number of units), average (for prices), and maximum/minimum (for values).

Let us drag SALES to Values

Field List & Areas | MyExcelOnline

You will get the Sum of Sales values here for each Product-Year combination.

Field List & Areas | MyExcelOnline

STEP 5: The last one is our Report Filters. These are optional fields that you want to drill down on. Examples are regions, periods, business units, or staff.

Drag both SALES REGION and SALES PERSON to the Filters area

Field List & Areas | MyExcelOnline

Click on any filter and you will be able to filter your Pivot Table data according to your selection.

Field List & Areas | MyExcelOnline

 

HELPFUL RESOURCE:

Make sure to download our FREE PDF on the 333 Excel keyboard Shortcuts here:

Microsoft Excel Training

You can learn more about how to use Excel by viewing our FREE Excel webinar training on FormulasPivot TablesPower Query, and Macros & VBA!

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Share on LinkedIn