Skip to Main Content

Cleaning & Analyzing Data in Excel

This guide complements the Cleaning & Analyzing Data in Excel workshop

What are tables?

Tables are Excel constructions that allow you to leverage the relationships of tabular data.

To create a table based on your data, simply select a cell within the data range and visit the HOME tab on the menu ribbon. Select FORMAT AS TABLE and choose your preferred format.


Once you have selected a style, you will be asked to specify if your data has headers (column names).


When data is formatted in tables, you are able to sort based on columns, remove duplicates, and summarize with PivotTables.


What are Pivot Tables?

Pivot tables are interactive data structures in Excel that allow you to investigate variables by pivoting them against one another. This allows you to see cross-sections of data that may not be apparent on first glance of the data.

Creating Pivot Tables

To create pivot table, select a cell within your data and visit the INSERT tab in the top ribbon. 


To create a table from scratch, select PIVOT TABLE. This will bring you to a window asking (1) what range represents your data and (2) where to store the pivot table. Double check that your range is correct. Best practice is to store the table on a new worksheet.


Once you have selected OK, you will be brought to a new page to build your pivot table. On the right side of the screen, you will be able to interact with pivot table elements. 


FILTERS are used to limit the data displayed in the pivot table based on some condition from one or more of your data columns.

By placing a column in the COLUMNS section, you will create columns for each of the unique values.

By placing a column in the ROWS section, you will create row for each of the unique values.

By placing a column in VALUES, you will input a calculation based on the values represented in the column. 


Creative Commons License
This work is licensed under a Creative Commons Attribution-NonCommercial 4.0 International License.