Select a cell within the list you wish to convert to a table. On the Insert tab, in the Tables group, click the Table command. In the Create Table dialog box, verify that Excel has correctly guessed the correct data range, check My table has headers if your table does have headers, and click OK. Sometimes you need a PivotTable for data that is stored outside of Excel, like in a database. In this case, you connect to the external data source first, and then use a PivotTable to summarize, analyze, explore, and present that data. Use an external data source to create a PivotTable. Cross tabulation is a method to quantitatively analyze the relationship between multiple variables. Also known as contingency tables or cross tabs, cross tabulation groups variables to understand the correlation between different variables. It also shows how correlations change from one variable grouping to another. Jun 21, 2016 Select the entire table as the range then hit Enter/Return key. Tips: You can first click cell A1 then hold down Control-Shift-Right Arrow to select everything to the right then Control-Shift-Down Arrow to select everything to the bottom of the table. Click Add button then Next. To begin making your crosstab, from the main menu of Excel, choose INSERT and click the PivotChart button. (Figure 2) Figure 2 Once clicked, the PivotChart dialog box will open.

A table without a header row can cause confusion and reduce efficiency in tracking and managing data because we always have to wonder what each of the values are referring to. Fortunately, Excel offers several ways to make a header row that will help us become more efficient and effective in presenting and handling data on a spreadsheet.

Figure 1. Final result: How to make a header row

Suppose we have below data.

Figure 2. Sample table with no header row

How to create a header row

• Apply formatting
• Format as table
• Freeze rows
• Add print title

Apply formatting

We can make the first row as header by changing the format of the first row of data in order to make its appearance distinct from the other cells. We can apply the following format:

• Bold text
• Fill the cell with color: Blue, Accent 5, Lighter 60%
• Bottom double border

Figure 3. Output: New format reflected

Now we can easily identify the first row as the header row.

Format as table with header row

In order to format our data as a table, we follow these steps:

How To Create A Cross Tab Table In Excel For Mac 2016 Free

• Select the cells we want to format
• Click Home tab > Format as Table > Table Style Light 9

Figure 4. Format As Table in menu

• Click OK. The Format As Table dialog box will appear. Verify the range B2:F7 for our data set and ensure that the checkbox is ticked for My table has headers.

Figure 5. Format As Table dialog box

We have successfully formatted our data as a table with a header row, and Excel automatically adds filter arrows that can be used to sort or filter the values per column.

Figure 6. Output: Format as table

How To Create A Crosstab In Excel 2016

Freeze header row

When navigating through a list or data that consists of many rows, it is wise to freeze the first row of data such that we can always see the labels even when we have scrolled so far down the worksheet.

In order to freeze the first row so that it is always visible on screen, we follow these steps:

• Click the second row of data, or the row just below our header row
• Click View tab > Freeze Panes > Freeze Panes

Figure 7. Freeze Panes in menu

After freezing, the header row will always be present on-screen even when we scroll down to the last row in the worksheet.

Figure 8. Output: Freeze header row

Add print title

How To Create A Cross Tab Table In Excel For Mac 2016 Free

For long lists or large data sets that are more than one page, it is very helpful to have a header row so that our data is properly labelled in every page that we print or preview. In order to add a print title, we follow these steps:

• Click Page Layout tab > Print Titles
• In the Page Setup dialog box, Sheet tab, enter row 2 as $2:$2 in Rows to repeat at top or click the icon and click row 2.

Figure 9. Add print title option in Page Setup

• Click OK.

Pivot Table In Excel

This method ensures that the selected header row (row 2) is repeatedly displayed on every page as we print or preview the worksheet.

Figure 10. Preview of page 1 with header row

How To Create A Cross Tab Table In Excel For Mac 2016 Version

Figure 11. Preview of page 2 with header row

Instant Connection to an Excel Expert

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our liveExcelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.