Data tin be organized in various ways. To take advantage of Tableau Desktop, Tableau recommends that you connect to information that is formatted for analysis. Specifically, data that is:

  • equally granular equally possible rather than aggregated (such as daily weather data rather than monthly averages)

  • organized like a database table (rather than a column-oriented table such as a crosstab)

  • stripped of inapplicable information (anything that's not the data and its headers)

When data is structured for analysis, it's much easier to inquire and answer questions. Tableau can aggregate raw data to the desired level, rather than being restricted by the aggregations already present in the data. Groups and hierarchies can be created as needed, and calculations tin can be performed in the flow of analysis.

Tableau Desktop has basic cleaning options and the Information Interpreter. Tableau Prep may be necessary for more than complex formatting issues.

The following Tableau Desktop-specific sections highlight and provide suggestions for resolving some common formatting or issues that tin can make analyzing your data difficult.

Pivot information in crosstab format

When data is formatted as in crosstab format, the tabular array is column oriented. In a column oriented table, the variables are stored as column headers. However, Tableau Desktop is optimized for row oriented data. In a row-oriented tabular array, the variables are stored in the row values.

For example, suppose you have column-oriented table, which shows math, science, and history scores for grade school students.

Tableau Desktop is optimized to connect to row-oriented tables, where math, science, and history values are organized nether a cavalcade called "Subject" and the scores for each educatee are organized under a column called "Score." You tin can pivot the columns in the rows, past manually editing your Excel data. Alternatively, connect to your Excel information from Desktop and and then utilize the pin pick. For more than information about the pivot option, see Pin Data from Columns to Rows.

Remove pre-aggregated data

Data can often come pre-aggregated. That is, data can contain sums, averages, medians, etc. A common of case of pre-aggregated information comes in the form of subtotals and grand totals. Subtotals and grand totals data are computed from the raw data, but is non part of raw data itself.

For example, suppose you have a table that contains a row of subtotals information.

In this instance, pre-aggregated data needs to be removed. To use subtotals and grand totals in your analysis, manually remove this type of information from your table. Then, connect to yous Excel information from Desktop and calculate subtotals and totals using the totals option. For more data, see Show Totals in a Visualization. Alternatively, connect to your Excel data from Desktop, plow on Data Interpreter, and and so use the totals option. For more than information, meet Make clean Information from Excel, CSV, PDF, and Google Sheets with Data Interpreter.

Remove or exclude introductory text

Excel data that is delivered equally a report can contain titles or blocks of introductory text. Because Desktop expects either column headers or row values in the showtime row of a table, this information can cause problems during your analysis.

For example, suppose you lot have a table that contains a report title and appointment.

In this example, the title and engagement data needs to be removed. To apply a title and date for a report, do one of the following:

  • Manually remove this data from your Excel data. Then connect to your Excel data from Desktop and add a written report title using the championship option. For more information, see Format Titles, Captions, Tooltips, and Legends.
  • Connect to your Excel data from Desktop, turn on Information Interpreter, and so use the title option. For more information, see Clean Data from Excel, CSV, PDF, and Google Sheets with Data Interpreter.
  • If yous cannot remove this information from your Excel information, create a named range and connect to the named range from Desktop. For more data, see Excel.

In general, Tableau Desktop expects only the offset row in your Excel data to contain cavalcade headers. Data that contains multiple layers of column headers can cause problems during your analysis.

For example, suppose yous accept a table that contains 1 major header and multiple sub-headers.

In this example, the hierarchy of headers must be flattened or removed. To do this, you lot tin manually create a new column for each header in the hierarchy directly in your Excel data. Alternatively, connect to your Excel data from Tableau Desktop and then plow on Data Interpreter. Verify that your headers are flattened correctly. For more information about Data Interpreter, see Make clean Data from Excel, CSV, PDF, and Google Sheets with Data Interpreter.

Brand sure there are no bare cells

If you create new columns for your hierarchical headers, make certain that each jail cell in the new columns contains values.

While y'all might echo the same value for each row, it'southward important that each row contains the data that associates it with the information that was stored in the hierarchical header. You must manually remove bare cells from your Excel data.

Remove blank rows

Brand sure that there are no blank rows in your information. To fix bare rows, y'all must remove the bare rows from your Excel information.

Make sure that in that location are no missing column headers. To fix missing headers, you must manually add the missing headers directly to your Excel data.