How To Create Crosstabulation Table In Excel
Cross tabulation is a method to quantitatively analyze the relationship between multiple variables.
Also known equally contingency tables or cantankerous tabs, cross tabulation groups variables to understand the correlation between different variables. It as well shows how correlations change from ane variable grouping to another. It is usually used in statistical analysis to find patterns, trends, and probabilities within raw information.
When yous can use cross tabulation
Cross tabulation is usually performed on categorical data — data that can be divided into mutually exclusive groups.
An instance of categorical data is the region of sales for a product. Typically, region can be divided into categories such as geographic area (Northward, South, Northeast, Due west, etc) or state (Andhra Pradesh, Rajasthan, Bihar, etc). The of import thing to recollect about categorical data is that a categorical data point cannot belong to more than i category.
Cross tabulations are used to examine relationships inside data that may not be readily apparent. Cantankerous tabulation is specially useful for studying marketplace research or survey responses. Cross tabulation of categorical data can be done with through tools such as SPSS, SAS, and Microsoft Excel.
An example of cross tabulation
"No other tool in Excel gives you the flexibility and analytical power of a pivot table."
Bill Jalen
One simple way to exercise cross tabulations is Microsoft Excel'southward pin table feature. Pivot tables are a great manner to search for patterns as they help in easily grouping raw data.
Consider the below sample data set in Excel. It displays details near commercial transactions for four product categories. Let's use this data gear up to show cross tabulation in action.
![cross tabulation](https://sp-ao.shortpixel.ai/client/q_lossless,ret_img,w_1006/https://humansofdata.atlan.com/wp-content/uploads/2016/01/CT1.png)
This data tin exist converted to pin table format past selecting the entire table and inserting a pivot table in the Excel file. The table can correlate different variables row-wise, column-wise, or value-wise in either table format or nautical chart format.
![cross tabulation](https://sp-ao.shortpixel.ai/client/q_lossless,ret_img,w_292/https://humansofdata.atlan.com/wp-content/uploads/2016/01/CT2.png)
Let's use cross tabulation to cheque the relation betwixt the blazon of payment method (i.e. visa, MasterCard, PayPal, etc) and the production category with respect to the region of sales. We can select these three categories in the pin table.
![cross tabulation](https://sp-ao.shortpixel.ai/client/q_lossless,ret_img,w_326/https://humansofdata.atlan.com/wp-content/uploads/2016/01/CT3.png)
Then the results appear in a pin table:
![cross tabulation](https://sp-ao.shortpixel.ai/client/q_lossless,ret_img,w_900/https://humansofdata.atlan.com/wp-content/uploads/2016/01/CT4.png)
It is now clear that the highest sales were done for P1 using Master Card. Therefore, we tin conclude that the MasterCard payment method and product P1 category is the most assisting combination.
Similarly, we tin employ cross tabulation and observe the relation between the production category and the payment method blazon with regard to the number of transactions.
This tin can be done by group the payment method, product category, and units sold:
![cross tabulation](https://sp-ao.shortpixel.ai/client/q_lossless,ret_img,w_321/https://humansofdata.atlan.com/wp-content/uploads/2016/01/CT5.png)
By default, Excel'southward pivot table aggregates values every bit a sum. Summing the units volition requite united states the total number of units sold. Since we want to compare the number of transactions instead of the number of units sold, we need to change the Value Field Setting from Sum to Count for Units.
![cross tabulation](https://sp-ao.shortpixel.ai/client/q_lossless,ret_img,w_600/https://humansofdata.atlan.com/wp-content/uploads/2016/01/CT6.png)
The results of this pivot table mapping is equally shown below. This is a cantankerous tabulation analysis of 3 variables — it analyses the correlation between the payment method and payment category according to the number of transactions.
![cross tabulation](https://sp-ao.shortpixel.ai/client/q_lossless,ret_img,w_899/https://humansofdata.atlan.com/wp-content/uploads/2016/01/CT7.png)
For all regions, we tin can find that the highest selling category of products was P1 and the highest number of transactions was done using Master Card. We tin likewise see the preferred payment method in each of the production categories. For example, American Limited is the preferred carte for P2 products.
The benefits of cross tabulation
Now that we are clear virtually how to apply cross tabulation, let's take a brief look at the benefits of using cross tabulation.
Eliminates defoliation while interpreting data
Raw data can exist difficult to translate. Even for small data sets, it is all too easy to derive wrong results past just looking at the data. Cross tabulation offers a simple method of group variables, which minimizes the potential for confusion or error by providing articulate results.
Helps in deriving innumerable insights
As we observed in our example, cross tabulation can help us derive great insights from raw information. These insights are not easy to meet when the raw data is formatted as a table. Since cross tabulation clearly maps out relations between categorical variables, researchers can gain ameliorate and deeper insights — insights that otherwise would have been overlooked or would accept taken a lot of time to decode from more complicated forms of statistical analysis.
Offers data points to chart out a course of action
Cross tabulation makes it easier to interpret data, which is beneficial for researchers who have express knowledge of statistical analysis. With cross tabulation, people do not need statistical programming to correlate chiselled variables. The clarity offered by cross tabulation helps professionals evaluate their current work and chart out future strategies.
Decision
Many studies suggest that cross tabulation is one of the most preferred methods of analysing market place research or survey data. In fact, Qualtrics estimates that cross-tabulation analysis and single variable frequency analysis together account for more than than 90% of all inquiry analyses. And so go ahead and utilise cross tabulation! Information technology'due south invaluable for uncovering hidden relationships in your raw data.
To come across cross tabulation in activeness, bank check out the video version of this blog.
Photo by Mika Baumeister on Unsplash
Source: https://humansofdata.atlan.com/2016/01/cross-tabulation-how-why/
Posted by: flinnithersell.blogspot.com
0 Response to "How To Create Crosstabulation Table In Excel"
Post a Comment