Pinterest Stumbleupon Whatsapp
Ads by Google

The pivot table is one of the single most powerful tools in the Excel 2013 repertoire 5 Microsoft Word & Excel Skills You Must Have to Succeed at the Office 5 Microsoft Word & Excel Skills You Must Have to Succeed at the Office "Get that report to me, ASAP!" A core set of Microsoft Excel and Word tricks can make this task less stressful and help you whiz up a stylish report in no time. Read More . It is frequently used for large data analysis.

You can use the pivot table as an interactive data summarisation 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.

To demonstrate the power of pivot tables, we will be using the UK Government dataset 2013 UK Fleet Landings by ICES Rectangle which can be downloaded via the linked page, if you want to play along at home.

Summarise All Your Data – Small Vs. Large Datasets

How you organise and group your data can make vital differences in uncovering trends What's Trending? Find Out With These Real-Time Data Websites What's Trending? Find Out With These Real-Time Data Websites It's amazing how quickly information can spread from person to person these days thanks to the Internet and social media. Is it possible to keep up? The short answer is yes -- with a catch. Read More . In a small sample, as in our example below, it is easy to spot trends:

  • Directors are paid more
  • Creative Directors have lowest average wage
  • Post-grads earn, on average, £9,000 more than individuals with a college education

Example Excel 2013 Worksheet

Ads by Google

 

Scaling up to a government dataset demands a different approach. What appears to be a colossal list of raw, unlinked data can actually hold the key to business success or failure. Pivot tables are suited to larger datasets and to work really well, they need to hit a few basic criteria:

  • Numeric Information: Your subtotals require this data.
  • Duplicates: At least one column with duplicate data ranges. Duplicate data ranges allow us to more effectively filter and analyse our information, providing numerical data ranges for the duplicate sources.

Import Raw Data Into a Pivot Table

Luckily for us, Excel 2013 contains the most up-to-date pivot table tool, now even offering up suggested pivot tables across the full spectrum of your available dataset. However, where the presets do not fit the bill, you can always manually select the pivot table wizard to get you rolling:

Pivot Table Insert

Clicking ‘Pivot Table’ should then bring you to this menu:

Create Pivot Table Options

Stick with the existing settings for now, though 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 Oops, I Did It Again: 7 Keyboard Shortcuts Users Keep Hitting By Mistake Oops, I Did It Again: 7 Keyboard Shortcuts Users Keep Hitting By Mistake It happens even to the most experienced of users: the accidental keyboard shortcuts. No matter how often you use your computer, weird things can start happening when you’re going too fast, when you lean on... Read More if something terrible does happen!).

So, clicking OK should present you with an entirely new worksheet for your brand spanking new pivot table. Excited yet? You should be: this is where the data magic happens!

Pivot Table Excel 2013

 

If you are still with me, and I truly hope you are, your page should look like what is shown in the screenshot above, or something very similar if you chose a different Worksheet. Excel has very kindly sorted our Pivot Table fields so as we can work in two ways:

  1. Drag and Drop: The pivot table fields listed on the right-hand side can be dragged into the 4 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 (PivotTable2 in screenshot above). This method can be used to rapidly build, analyse, collate and modify the available data.

Whilst 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 is transformed. As a bonus, each time we click a new data field it is automatically added to the area Excel deems correct, whilst reminding us that we cannot add a data ‘field with more than 16384 items to the column area.’ Thanks, Excel, always looking out for the business person, aren’t you?

Pick & Compare Data Fields

Now, click Vessel Nationality, Species Name, Landed Weight (tonnes) and Value (£). Your data subtotals should now look like this:

pivot row examples

Whilst your pivot table fields should match this:

pivotrowexample2

We can now rapidly glance at our custom pivot table data fields and analyse the available data to ascertain any trends. As a result of our actions, we now know that England fishes the most Bass throughout the UK, though the fish itself is worth more in Wales. Insight gained!

Filtering Your Data

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 Vessel Nationality filter menu.
  4. By default, all data ranges are selected. Unselect all (by hitting select all), followed by reselecting England and Scotland. This isolates the data for these countries, filling our pivot table with concise, comparative data we are able to begin to analyse for potential trends.

Pivot Table Filters

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 Do Visionary Web Research Studies Using Deep Web Data & Excel Web Queries Do Visionary Web Research Studies Using Deep Web Data & Excel Web Queries What would you say if I told you that you have the tools at your disposal to do ground-breaking, Earth-shattering research? Well, you do, and I'll show you how. Read More .

Size Matters

For this example, we used a small proportion of a relatively large dataset. Pivot tables work best when they have several thousand individual records, multiple data fields and a range of duplicate numerical figures. That is not to say their relevance is strictly associated with large datasets but, when dealing with a massive amount of raw data, nothing can beat the pivot table in ease of use, data filtering or its concise summarising facilities.

Has this ‘How To’ helped you, or have we missed something? We’d like to hear your pivot table experiences below!

  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

  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.

Leave a Reply

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