How to Create a Pivot Table in Microsoft Excel


Microsoft Excel is one of the greatest tools at our disposal when it comes to analyzing huge data sets.  One of the more powerful tools I like to use within Excel are Pivot Tables.  

A Pivot Table allows you to take literally thousands of rows and hundreds of columns of data and almost instantly identify patterns, trends, and perform a robust analysis.  In this post, we’re going to show you How to Create a Pivot Table and how we use them right here on ChartYourTrade.


Getting Ready: Make Sure Your Data is Consistently Formatted

The first step to working with Pivot Tables is to make sure that your data is consistently formatted.  What this means is that all of the content you want to analyze in each column is formatted exactly the same.  For example, below we have 2 sets of screen shots, one with what we want our data to look like and one that needs to be cleaned up a bit.  The column I’d like you to focus on is the “Sector” Column (column D).  

Note how in the ‘Inconsistent Data’ screenshot under Sector we see both “Building” and “Buildings”.  If we were to run a pivot table on this data set to find out how many stocks are in the “Building” sector, we’d have a miscount because Excel would count up the number of stocks in the “Building” and “Buildings” separately.  

Make sure all of the data you wish to analyze is formatted consistently. 


Example of What We Want Our Data to Look Like & Results

Consistent Data
Consistent Data
Consistent Table
Consistent Results















 Example of a Data Set that Needs to be Cleaned Up & Results

Inconsistent Data
Inconsistent Data
Inconsistent Table
Inconsistent Results













Creating a Pivot Table

Now that our data set is consistent, we can create our pivot table.  In the screen shots below, we’ll walk you through the how to do it.


Step 1

First, select the “Data” tab which is highlighted by the small red rectangle in the screen shot below.  Next select the drop down next to the “pivot table and select “create manual pivot table” which is highlighted within the large red rectangle and red arrow.

Screen Shot 2016-01-20 at 5.12.17 PM



Step 2

After selecting “create manual pivot table” you’ll be presented with the following window asking you where you’d like to create your pivot table (new worksheet or existing worksheet).  I always prefer to create it in a new worksheet as I find this method cleaner, but please select the option you feel is best.  Click “OK”.

Screen Shot 2016-01-20 at 5.07.27 PM


Step 3

Next we are presented with the “Pivot Table Builder” (large black/gray box to the right) and this is where we get to select our inputs for the pivot table.  As you can see, so far we have selected our Row Labels to be “sectors” and we have already dragged and dropped “sector” again into our “values” box.  This time, Excel has defaulted to automatically count the number of stocks in each sector.  As you’ll see later on, we can edit this calculation to be something else.

*When analyzing our Universe week to week, its important to monitor the expansion and contraction of the number of stocks in each sector as it highlights strength and weakness.  Pivot Tables allows us to do this instantly!

We are also dragging in the “% Chg Cur Week” into our values.  We will eventually tell Excel to take the average of each sector to further highlight strength/weakness among the sectors.

Screen Shot 2016-01-20 at 5.07.55 PM


Step 4

Excel doesn’t always automatically give us the calculation or layout we want.  In the screen shot below we see a “sum of % chg cur week” and a “count of % change YTD” in the pivot table.  

To edit this, go to the “pivot table builder” (large black/gray box to the right) and click on the “i” in the values box of the value you wish to change.  In this example we’re starting with changing the “sum of % chg cur week”.  We are presented with a window that lets us edit the calculation.  We’ll select “average” and then click okay. 

Screen Shot 2016-01-20 at 5.08.24 PM

To help highlight positive and negative values, we’d like Excel to highlight negative values in red.  To do this, we go to the “PivotTable Field” and click “number”.

Once we select “number” we’ll be given some formatting options.  Here’s where it can get a little tricky.  We are working with percentages but we do not want to select the percentage format in this instance.  The reason is because the values in our original data set are already percentages and if we tell Excel to format our values into percentages, it will treat our current values as whole numbers.  Thus what should be 10% will turn into 1,000%.  For that reason, it is best to select the “Number” format.

As mentioned earlier, we also want to show negative values in red so we select that from the options below to the right and click okay.

Screen Shot 2016-01-20 at 5.08.38 PM


Step 5... WE’RE DONE!

That’s it, we’re done!  We are now ready to begin analyzing the information that the pivot table is giving us.  Here is an example of some of the points we can extract:

  • There are 153 stocks in our Universe
  • The largest sector is the Medical sector with 19 stocks in it
  • On average, stocks in the Universe rose +3.14% for the week and +35.39% year to date
  • The top performing sector with 10 stocks or greater is presently the Building Sector
  • There are plenty more points we can extract from this pivot table…how many can you find?

How to Create a Pivot Table in Microsoft Excel 1


Have a question?  We’d love to help!  Please leave a comment below…


Check out these other blog posts:


Here are more articles you may like

Claim Your Free Guide Today

Give us your email and we will give you the tools to change your life. 


Learn about Early Entry Points & much more...

© ChartYourTrade | Contact us:

Disclaimer: All communication from ChartYourTrade is general in nature and for educational and general informational purposes only. Under no circumstance should it be considered personalized investment advice. All our work is general in nature and not specific to any one person. All the information on this site and/or that originates from us, or any of our partners or affiliates, is for educational and informational purposes only and is NOT a recommendation to buy or sell anything. To avoid any conflicts of interest, we do not have a working relationship with any of the companies mentioned in our work. Furthermore, we may have a long, short, or no position in any, or all, of the names that appear in our work and they may change at any time without notice. Investing and trading in capital markets or using margin carries a high level of risk and may not be suitable for all investors. Leverage can work against you as well as for you. Before you decide to invest or trade in capital markets you should carefully consider your investment objectives, level of experience, and risk appetite, among other factors. The possibility exists that you could sustain a loss of some, all, or more of your initial investment and therefore you should not invest money that you cannot afford to lose. You should be aware of all the risks associated with capital markets, investing/trading, and seek specific investment advice from an independent financial advisor and other professionals. Remember all the information we provide is for educational and general informational purposes only and is subject to change without notice.

Charts and Data are courtesy of MarketSmith Incorporated. Join MarketSmith here.

Terms of Service