Productivity

How to Create a Pivot Table for Data Analysis in Microsoft Excel

Gavin Phillips Updated 24-06-2020

Microsoft Excel’s pivot tables are a powerful tool you can use to compare large datasets.

Advertisement

You can use the pivot table as an interactive data summarization tool to automatically condense large datasets into a separate, concise table. You can use it to create an informative summary of a large dataset or make regional comparisons between brand sales.

Pivot tables do not have to be intimidating—far from it—and we are here to help you unlock one of the real hidden power tools of Excel.

What Is a Pivot Table?

A pivot table is a method for arranging large datasets into easier to manage statistics. You can use the pivot table feature to “pivot” your data to uncover new trends and links between data.

The primary method for data entry in Excel is a regular flat table, like so:

microsoft excel flat table example

Advertisement

The flat table contains columns and rows containing data. You can discern some basic trends through the data, especially with a small dataset.

However, if you have a dataset containing thousands or even millions of entries, you cannot understand the data at a glance. In that instance, you can create a pivot table to sort and link the data using the categories available (typically taken from the data range names).

One of the best things about a pivot table is the speed with which you can rearrange data. You don’t have to understand complicated comparison formulas, and you don’t need to become an Excel pro to work with a pivot table. Better still, you can reset your pivot table whenever you want and begin afresh.

How to Create a Pivot Table in Excel

Microsoft Excel has an excellent integrated pivot table option. You should note that Excel is far from the only spreadsheet program that uses pivot tables. Microsoft Office alternatives such as Libre Office and Google’s G-Suite all have support for pivot tables in their spreadsheet programs.

Advertisement

Anyway, back to pivot tables in Excel. The following tutorial uses Microsoft Excel for Office 365. Furthermore, I’m using a generic generated dataset of UK names, addresses, and so on. If you would like to use the same practice data, the file is available for download below.

To create a pivot table, head to the Insert tab and select PivotTable. Select the table or data range you want to include in the pivot table. In the case of the example, the entire data range forms the selection.

microsoft excel pivot table range

As you can see, there are options to connect an external data source (i.e., MS Access), or place the pivot table within the existing worksheet. For the latter option, be sure to check that your new pivot table will not obscure or break your existing data (though you can always hit CTRL + Z to undo if something terrible does happen!).

Advertisement

Sorting Pivot Table Data

Once you hit OK, your pivot table will open in a new worksheet. The worksheet begins blank. On the right, you will see the PivotTable Fields panel. This panel contains the data ranges from the example dataset, such as names, addresses, sales, and so on.

microsoft excel pivot table blank

From here, you have two options for adding data to your pivot table:

  1. Drag and Drop: The pivot table fields listed on the right-hand side can be dragged into the four areas below (Filters, Columns, Rows, and Values). You can select specific data items that you wish to cross-reference against any number of differentials.
  2. Add to Report: Clicking individual dataset fields will add them directly to the waiting report table. Use this method to rapidly build, analyze, collate, and modify the available data.

While both methods deliver similar outcomes, Add to Report allows you to simply select your preferred data field and marvel in wonder as your report table transforms. As a bonus, each time we click a new data field, it is automatically added to the area Excel deems correct, while reminding us that we cannot add a data’ field with more than 16384 items to the column area.’

Advertisement

Selecting Pivot Table Data Ranges

The pivot table example we are using is basic. It contains a few sample data points that are easy to understand. For this example, we’re going to look at regional sales of the various products sold.

From the PivotTable Fields list, select County, Sales Volume, Sales Total, and Product. The example pivot table should now look like this:

microsoft excel pivot table filters

You can now glance at your pivot table data and analyze data for any trends. If you want to see an overview of each section, right-click and select Expand/Collapse > Collapse Entire Field.

Filtering Your Pivot Table Data

microsoft excel pivot table filter column options

Feel free at this point to play around with the different fields, learn which varieties of data produce concise correlation, which data fields need tweaking to reveal their secrets and to comprehend the value actions of filter settings:

  1. Hover over the desired data field, note the small black arrowhead to the right of the text
  2. Selecting the black arrowhead reveals a drop-down ‘Filter’ menu – extremely useful for isolating data within a single field, or comparisons between similar data ranges across multiple data sources.
  3. Select the County filter menu.
  4. By default, all data ranges are selected. Unselect all (by hitting select all), followed by reselecting a few different counties. This isolates the data for these countries, filling our pivot table with concise, comparative data we are able to begin to analyze for potential trends.

microsoft excel pivot table filtered groups

Modifying the filters of each available field will almost always directly alter the data presented in the pivot table and is a sure-fire method of unlocking the true potential of your data.

Use Pivot Tables to Uncover Data Trends

You can use a pivot table to work your data into new angles, helping you to uncover new trends and statistics for your work, business, or otherwise. Pivot tables work best when they have a large amount of data to work with, multiple data fields, and some duplicate data fields, too.

That’s not to say you cannot use them with a smaller data sample. The example data for this article was just 500 rows and 14 columns, three of which were in use to manipulate the data.

If you’re looking to up your Excel powers, check out these Excel formulas that do amazing things 3 Crazy Excel Formulas That Do Amazing Things Excel formulas have a powerful tool in conditional formatting. This article covers three ways to boost productivity with MS Excel. Read More !

Related topics: Data Analysis, Microsoft Excel, Microsoft Office 2019, Microsoft Office 365, Spreadsheet.

Affiliate Disclosure: By buying the products we recommend, you help keep the site alive. Read more.

Whatsapp Pinterest

Leave a Reply

Your email address will not be published. Required fields are marked *

  1. Abul kalam
    November 20, 2016 at 4:16 pm

    This post is very useful to remotely connected any PC desktop around in world. Team Viewer is very powerful remote administrator software for maintenance your partner PC or transfer any data via internet.
    So thanks for sharing… by us-uk-software.com

    • Gavin Phillips
      December 13, 2016 at 12:03 pm

      Hey Abul,

      I think your comment may have jumped onto the next article somehow. That never ending scroll strikes again. Thanks for reading and commenting though!

  2. Abul kalam
    November 20, 2016 at 4:15 pm

    This post is very useful to remotely connected any PC desktop around in world. Team Viewer is very powerful remote administrator software for maintenance your partner PC or transfer any data via internet.
    So thanks for sharing…

  3. Chris Curry
    September 5, 2016 at 3:21 pm

    Thank you for posting this helpful guide. Your comment that although England fished more Bass, it was worth more in Wales had me really confused. Looking at the data, the value of the Bass was $5,001,337.439 while in Wales it was $524,354.1619. To me, it cleary says the value was greater by almost tenfold in England. I thought you might mean that the Value/Tonne was greater in Wales than England however, this did not check either as the Value/Tonne was $6,581.906 £/tonne for Wales and $7,253.253 £/Tonne in England, with Scotland having the highest value of $7,253.253 £/Tonne.
    Please correct me if I'm wrong or misunderstood your meaning.
    Thanks again for the tutorial.
    Cheers!

  4. Emmanuel
    March 11, 2015 at 8:39 pm

    According to the data, it is said that Bass is worth more in Wales. i particularly don't agree with that though i do agree that it is fished more in England.
    Anyway, is there actually a way to compare the values and the quantity being produced using the pivot tables, especially when huge values are involved, and cannot be compared merely by observation?

  5. Adrian
    October 17, 2014 at 12:04 pm

    I've been using Excel since 1997 but I have never used the Pivot Table feature since I have always found them intimidating. They don't seem that difficult to use though.

    Thanks for the tutorial.

    • Gavin
      October 20, 2014 at 4:16 pm

      Glad it helped. I found them quite overwhelming for sometime, but the latest iteration of Excel has made it a dream. Good luck!

  6. Coco
    October 16, 2014 at 12:19 am

    In probably just being dumb, but why do you want duplicate data?

    • Gavin
      October 20, 2014 at 4:30 pm

      Good question. I wrote:

      'Duplicate data ranges allow us to more effectively filter and analyse our information'

      The duplicate ranges form similarities in our data, allowing us, through the use of the pivot table, to contrast our data with single entries. For instance, in our tutorial we use the UK Fleet Landings dataset. Within this data there are thousands upon thousands of duplicate entries for trawl size, rectangle type, catch type, length group and many more.

      Using our pivot table, we can filter out (or include) specific data ranges. Don't want data concerning 'Gear Category: Demersal trawl/seine,' then filter all of those duplicate entries out (you can actually try this in the tutorial using 'Filter your Data' section).

      It doesn't mean they disappear forever, but you don't want to consider that data range in your analysis. Duplicate ranges provide the pivot table with a method of breaking down our data and presenting it in manageable, quantifiable chunks that we can digest with our morning tea.

    • Coco
      October 20, 2014 at 5:31 pm

      Thanks for your reply. Now let me see if I get this, when you talk about duplicate ranges are we talking about 2 different tables that point to the same data, are we talking about linked types of data, or are we talking about unlinked data that is simply the same.

      Let me try and explain. For different tables it would work out that for ship A, on x date caught Y, and that information was recorded in the ships log (table 1) and officially (table 2) - we have access to both tables, and although they record exactly the same event (duplication), we're error checking, or something like that.

      Or, and this is what I think you mean, we're talking about linked data. In linked data, ship A is RED, and caught X on date Y. If we want to know how much was caught on date Y by RED ships, ship A would be duplicated as it is linked to RED and date Y. I hope that makes sense.

      Or, and I don't see why this would be an issue, are we talking about multiple instances of X under the caught header?

      Man, this is difficult to explain. I'm new to this, so my terminology will be highly suspect. Last thing, why not just use a DB program? Is it not more straightforward to query the data?

    • Gavin
      October 27, 2014 at 10:54 am

      Hi Coco,

      We are talking about data within a single table that is the same, but unlinked. The pivot table, in this case, acts as the link, drawing on the duplicate data to allow us to further filter our information.

      Your second explanation fits the bill, though your third also fits explanation is also part of it. They work together. Multiple instances of Bass caught in the UK can be filtered down into Bass caught in England, Scotland, Wales etc. Bass caught in England can be further filtered into catch size, rectangle size/type etc.

      You will get it - it does take time. Try experimenting with other datasets:

      http://data.gov.uk/data/search

      Has thousands of open-source datasets. The US government also provides a massive online repository of public data. You'll want either CSV or XLS for Excel compatibility. If you chose a few different datasets, you can throw them into Excel one at a time and see what works with a pivot table, what types of data facilitate your analysis and what trends you can uncover.

      Regarding your last question - 'why not use a DB program?' - this is a good question, and one that I will be answering in a separate article coming quite soon!

      Hope that has helped you, Coco.

    • Coco
      October 28, 2014 at 6:02 pm

      Thanks for your reply, Gavin.

      I think we're on the same page now, and I'm looking forward to your DB article.