Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
All articles PIVOT TABLE These Pivot Table Basics will Get You Started

These Pivot Table Basics will Get You Started

What is a Pivot Table?

A Pivot Table is a table that we can use to analyze, summarize, and calculate data that enables us to visualize trends, comparisons, and patterns in our data in a condensed manner. We can use a Pivot Table to organize a large amount of data in Microsoft Excel.

Figure 1: How to Create a Pivot Table

What can we do with a Pivot Table?

  • We can set up a Pivot Table in less than a minute
  • We can format our Pivot Table as we like it
  • We can add data to our Pivot Table by refreshing
  • We can set up filters in the Pivot table to have access to only the values we want, etc.

 

 

How to Create a Pivot Table

  • We will use the data below to create a Pivot Table

Figure 2: Setting up the Data

Creating a Data Table

Before we create the Pivot Table, we will put the values in figure 2 into a table.

  • We will click on anywhere on the data, click on the Insert tab, and click on Table as shown in figure 3

Figure 3- Putting the data in a Table

  • We will click on OK on the dialog box that appears once the table range is selected

Figure 4- Create Table Dialog Box

Figure 5- Created Table

  • We will click on the box below Table Name under File in Figure 5 and name the Table as Sales_Data
  • We will press enter after inserting the name
  • We can add some designs to the table by going to Design and make a choice

Figure 6- Selecting a Table design

Figure 7- Applying Table Design

  • The drop-down arrows on Row 1 of the Table can be used to filter data
  • Now, we will create a Pivot Table with the Data

Creating the Pivot Table

  • We will click on anywhere on the table
  • We will click on the Insert tab and click on Pivot Table as shown in figure 3

Figure 8- Create Pivot Table Dialog Box

  • We will click on New worksheet
  • We will click OK

Figure 9- Created Pivot Table and Selected Pivot Table Fields

  • We can drag any of the fields to the “Drag fields between areas below section” to adjust how our table will look

Formatting the Pivot Table

We can format our Pivot Table by refreshing our Pivot Table whenever we add new data to the data table, applying number formatting (e.g. with Currency), and using the filter options

Refreshing the Pivot Table

We can expand the Range of the Table by taking our cursor to the edge as shown in figure 10 and drag down

Figure 10a: Expanding the Range of the Data Table

Figure 10b: Expanding the Range of the Data Table

  • We will insert new values into the empty cells

Figure 11: Values inserted into the Expanded Range of the Data Table

  • Our Pivot Table doesn’t automatically update as we add these values
  • We will click on the Pivot Table, right-click, and click on Refresh

Figure 12: Refreshed Pivot Table with New Data

  • We have condensed the details of Sam White so that we will have a full view of the Pivot table. Double-clicking on Sam White will result in the details displaying

Number Formatting

  • We can format the Sum of Sales Amount in figure 12 to have a particular currency like the United States Dollar ($)
  • We will click on any cell of the Sum of Sales Amount Column
  • We will right-click and click on Number format

Figure 13: Format Cells Dialog Box

 

  • We will click on Currency and set the decimal places to 0. We will also set the symbol as $
  • We will click OK

 

Figure 14: Formatted Cells with Currency ($)

Grouping Pivot Table Data

We can group the Data in the Pivot Table to know Sum of Sales Amount and the Sum of Quantity within every 7 days.

  • We will click on any of the dates
  • We will right-click and click on group

Figure 15- Grouping Dialog Box

 

  • We will click on Days and set number of days to 7
  • We will click OK

 

Figure 16- Pivot Table grouped by Days

Instant Connection to an Expert through our Excelchat Service

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc