Creating Cross-Tabulations in Excel: Steps & Examples

Instructor: Scott Tuning

Scott has been a faculty member in higher education for over 10 years. He holds an MBA in Management, an MA in counseling, and an M.Div. in Academic Biblical Studies.

Cross-tabs are an excellent option for presenting data because they provide a succinct but complete picture of the material being addressed. This lesson will explore cross-tabs and the steps for creating them using Microsoft Excel.

How Bad Will It Be When They Leave?

Imagine for a moment that you are the owner of a small business that supplies parts and equipment to the oil and gas industry. Your business deals chiefly in products that are specific to one industry and your sales records demonstrate that your revenue is quite healthy. However, you are always aware that your overall customer base is minimal. Not only is it a relatively small customer group, but the number of oil and gas companies in the area is fixed. No new companies will spring up to replace any companies that leave the area.

Consequently, the loss of a single customer could have a detrimental impact on your business. Three years from now, the leases to drill for oil and gas on public lands will expire, and it is not yet clear whether the federal government intends to renew them. In preparation for the change, you want to gather some data and analyze it for the purpose of assessing the potential loss of revenue.

Show Me Everything, But Don't Use More Than One Page

A crosstab is an excellent choice for analyzing this data because it allows for the succinct, yet complete, presentation of complex results. A crosstab is a spreadsheet, but instead of row upon row of individual sales data, the crosstab keeps totals and averages. Your company's sales records are depicted in Figure 1. Even with a cursory glance, you can see that there is quite a bit of data, but most of the sales are from a relatively small number of companies. The list does you very little good in this state, so you are going to create a crosstab report in order to make the data easier to digest and act upon.


Figure 1
Fig 1


You can easily see that you have some widely varied data. A few of your sales are less than $10 but several are over $60,000. Ultimately, you want to know which customers bring your company the most revenue and how much revenue you would lose if the customer packed up and left the area. To see and digest that data, you want a crosstab that lists each customer only once and only the total amount of their gross sales.

If You Build It, the Data Will Come

To begin making your crosstab, from the main menu of Excel, choose INSERT and click the PivotChart button. (Figure 2)


Figure 2
Figure 2


Once clicked, the PivotChart dialog box will open. Select the data that should be used in the crosstab, and select where the crosstab should be placed. (Figure 3).


Figure 3
Figure 3


You can experiment with the individual parameters of the crosstab, but for this specific question, we have selected both columns, ''Customer Number'' and ''Sale Amount''. This gives us a crosstab like the one shown in Figure 4.


Figure 4
Figure 4


To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create an account
Support