How to Create Pivot Tables in Excel 2016

Edited 2 weeks ago by ExtremeHow Editorial Team

Microsoft OfficeExcelPivot TablesReportingSpreadsheetWindowsMacProductivityChartCalculation

Pivot tables are one of the most powerful features in Excel 2016. They allow you to effectively summarize data and find patterns that are difficult to see in the raw data format. In this guide, we will explore the steps required to create a pivot table in Excel 2016, offer tips and tricks for optimal use, and cover some advanced features. This topic is going to be quite detailed, intended to be comprehensive for both new and familiar users of Excel.

Understanding pivot tables

A pivot table is a data processing tool available in Excel that allows you to summarize, analyze, explore, and present your data. It enables grouping data from a large table into a more consumable form. With pivot tables, you can turn long data sheets into concise reports in just a few clicks, revealing important information and trends.

When to use pivot tables

You may find pivot tables particularly useful in the following situations:

• When you need to get a quick summary of your data.
• If you want to see comparison between data items.
• When you’re tasked with creating a report for your dataset.
• Uncovering trends and patterns.

Preparing your data

Before creating a pivot table, it's important to make sure your data is ready for analysis. Here's how you can prepare your data:

• Organize data into columns: Make sure your data is organized in a tabular structure, where each column has a heading. Each column will represent a different category of data.
• Continuous data range: The data should not have any blank spaces or empty rows. Continuous range ensures that the pivot table captures all the available data.
• Remove subtotals: If your data already has subtotal rows, remove them. Pivot tables will calculate their own subtotals, and you won't want to repeat.
• Data type consistency: Each column should have a consistent data type. For example, dates should be in date format, numbers should be formatted as numbers, and so on.

Creating a pivot table in Excel 2016

Now that your data is ready, we can begin the process of creating a pivot table. Follow these steps:

1. Select your data: Click anywhere inside your data range (clicking inside the data usually automatically selects the correct range, but if you need more precision, it's a good idea to select the entire data set).
2. Insert a pivot table: Go to the "Insert" tab on the Excel ribbon and select "Pivot Table." A dialog box will appear, asking you if you want to create a pivot table from the data you selected.
3. Select the data source: Make sure "Select a table or range" is selected and verify that the correct data range is listed in the "Table/Range" box. Next, decide where you want to place the pivot table report. You can choose to place it in a new worksheet or an existing worksheet.
4. Create a pivot table: Click "OK," and Excel creates a blank pivot table report in your chosen location. On the right, a pivot table field list will appear with your column headings as available fields in the data.

Building your pivot table

Once you have a blank pivot table, you can begin building your table using the available fields.

1. Select fields: Drag and drop different fields into the "Rows," "Columns," "Values," and "Filters" boxes to organize how you analyze your data.
• Rows: Fields placed here will be displayed as rows in the pivot table.
• Columns: Fields added here will appear as column headers in the pivot table.
• Values: This is where you enter the fields you want to calculate or summarize, such as a sum or average.
• Filter: Provides an interactive way to filter the data in your pivot table, allowing users to choose what to display in the pivot table.
2. Customize it: Once the data is organized in a pivot table, you may want to customize it by sorting and formatting it further.

Formatting and customizing a pivot table

To make your pivot table more presentable and easier to read, you can use various formatting and optimization techniques:

• Change the layout: Experiment with report layouts under the "Design" tab. You can switch from compact to outline or tabular form.
• Show values as: Instead of just the sum, you may want to display the values as a percentage of the total or as a difference from another field. This option can be found by right-clicking on a value in your pivot table and selecting "Show values as."
• Conditional formatting: Use colors to highlight specific results. You can apply conditional formatting by selecting a cell and navigating to the "Home" tab to access the "Conditional Formatting" options.
• Value field settings: Right-click in your pivot table and select “Value field settings” to access more options, such as summarizing data by count, maximum, minimum, or average, among others.

Create a calculated field or item

In some scenarios, you may want to add your own calculations or derived data to your pivot table. This can be achieved with calculated fields or items:

1. Access to calculated fields: Go to the "Analysis" tab, and in the "Calculations" group, click "Fields, Items, and Sets," then select "Calculated Fields" or "Calculated Items," depending on your needs.
2. Insert a formula: In the "Insert calculated field" dialog box, name your field and enter the required formula. Formulas can take advantage of data fields and standard Excel calculations.
3. Finalize: Click OK, and the calculated field or item will be added to your pivot table.

Advanced features and options

Excel 2016 offers several advanced features when working with pivot tables, which can be helpful in performing in-depth data analysis:

• Data grouping: If you have a list of dates or numbers in a row or column field, you can group them by month, year, or interval. Right-click one of the date entries, select "Grouping" and choose the grouping criteria you want.
• Slicers: Slicers are visual filters for your pivot table. You can insert them by clicking "Insert Slicer" from the "Analysis" tab after selecting your pivot table.
• Timeline: When dealing with date fields, adding a timeline filter makes it easier to navigate to individual months or years. Insert a timeline from the "Analysis" tab.
• Refreshing data: If your raw data changes, you will need to refresh your pivot table. Click on your table and under the "Analysis" tab, press the "Refresh" button.

Best practices for using pivot tables

To get the most out of pivot tables, consider the following best practices:

1. Keep your data clean: Make sure your data is accurate and up-to-date before using pivot tables.
2. Use descriptive headings: This makes it easier to understand at a glance what the fields represent.
3. Limit the amount of data: Large datasets can be unwieldy, so try to include only the data you need for analysis.
4. Experiment with different layouts: Different problems or analyses may benefit from different pivot table layouts, so don't hesitate to reorder the fields.
5. Document your analysis: Provide explanations or annotations for your pivot tables if needed, especially when sharing reports.

Conclusion

Pivot tables in Excel 2016 are a robust and flexible way to analyze and efficiently present data. With the ability to summarize large datasets into digestible reports, pivot tables aid in understanding complex data. By learning how to create and manipulate pivot tables, you'll be able to extract meaningful insights and patterns from your data. Whether organizing fields, using calculated items, or taking advantage of advanced tools like slicers, you'll find pivot tables extremely useful for your data analysis tasks. With practice and exploration, pivot tables can become an invaluable asset in your Excel toolkit.

  Share This Article

If you find anything wrong with the article content, you can request an update