Use Excel to explore a dataset with formulas, sorting, and filtering tools.

Exploring Data

The raw data we get often isn't in precisely the form we would like to analyze. For example, our new vehicle data contains the number of new vehicles for each type of vehicle, but what if we wanted to see each type as a percent of total new vehicles instead?

We could calculate percentages one cell at a time using individual formulas, but this would result in a lot of time writing formulas instead of analyzing our data. Also, if we ever decided to change our analysis slightly, we'd be stuck updating each formula individually again. Fortunately, Excel lets us move, or drag, formulas while automatically updating cell references.

Let's take a look at what this means. Suppose the **cell** `B2` contains the **formula** `=A4`. If we move this formula from `B2` to `D1`, we moved the formula two columns to the right and one row up. Excel then updated the formula from `=A4` to `=C3` following the same "path": two columns to the right and one row up. A way to think of it is that Excel moved **everything** two steps over and one step up.

![a screenshot of an Excel table with columns A-D and rows 1-4. Cell B2 has the formula =A4. There are arrows demonstrating this formula being moved three columns over and one row up, so that cell D1 has formula C3.](https://static-assets.codecademy.com/Courses/Spreadsheets/learn-excel-for-data-analysis/exploring-data/narrative.svg)

Sometimes, we won't want the column or row to update. For example, let's take a look at calculating the percentages of the new vehicle data in the next table.

|   | A            | B                      | C       |
|---|--------------|------------------------|---------|
| 1 | Vehicle type | # of new vehicles | Percentage |
| 2 | car     | 1000                   |         |
| 3 | SUV       | 1500                   |         |
| 4 | truck       | 5000                   |         |
| 5 | TOTAL:       | 7500                   |         |

We can calculate the first percentage in `C` by dividing the number of new cars by the total number of new vehicles. In Excel, we would type the formula `= B2/B5` in the cell `C2`.

|   | A            | B                      | C       |
|---|--------------|------------------------|---------|
| 1 | Vehicle type | # of new vehicles | Percentage |
| 2 | car     | 1000                   |     =B2/B5    |
| 3 | SUV       | 1500                   |         |
| 4 | truck       | 5000                   |         |
| 5 | TOTAL:       | 7500                   |         |


What happens when we move this formula to the cell `C3`? The column stayed the same, and the row moved one cell down. Excel will update the references in the formula `=B2/B5` to follow the same path:

- `B2` becomes `B3`
- `B5` becomes `B6`

Now the cell `C3` will have the formula `=B3/B6`.

|   | A            | B                      | C       |
|---|--------------|------------------------|---------|
| 1 | Vehicle type | # of new vehicles | Percentage |
| 2 | car     | 1000                   |     =B2/B5    |
| 3 | SUV       | 1500                   |    =B3/B6     |
| 4 | truck       | 5000                   |         |
| 5 | TOTAL:       | 7500                   |         |

This isn't what we want to calculate! The formula `=B3/B6` takes the number of new SUVs in `B3` and divides it by whatever is in cell `B6`, not by the total which is in cell `B5`.

We want Excel to update the reference `B2` to `B3`, so that we're calculating the SUV percentage. But we don't want Excel to update the reference to `B5`, since the total stays the same no matter which vehicle type we're working with.

To stop Excel from updating this row, we need to place a `$` before the original row reference. The first formula in `C2` becomes `=B2/B$5`. When we move it down to `C3` it updates to `=B3/B$5`. This takes the number of new SUVs and divides it by the total, which is what we want to do!

|   | A            | B                      | C       |
|---|--------------|------------------------|---------|
| 1 | Vehicle type | # of new vehicles | Percentage |
| 2 | car     | 1000                   |     =B2/B$5    |
| 3 | SUV       | 1500                   |    =B3/B$5     |
| 4 | truck       | 5000                   |    |
| 5 | TOTAL:       | 7500                   |         |


Note that if we wanted to stop the column from updating instead of the row, we would need to put the `$` sign in front of the column letter in the formula instead of the row number.

### Errors and Best Practices

Forgetting to use a `$` when necessary sometimes results in an Excel error. These errors are displayed in the cell containing the reference.

The two most common errors are `#DIV/0!` and `#VALUE!`. Let's look at how these could occur in our example, where Excel updated the percentage formula to `=B3/B6`, pointing to a cell `B6` that isn't in our table.

The `#DIV/0!` error indicates that Excel is trying to divide by `0`. This could happen because the cell `B6` contains a `0` or, more likely, because the cell `B6` is empty. Excel assumes that empty cells are the same as `0`.

The `#VALUE!` error likely indicates that the cell `B6` contains some non-numeric value that Excel cannot use in the mathematical formula `=B3/B6`.

Even if no errors appear, our cell references might still be updating in a way we don't want. If there was a nonzero number in cell `B6`, Excel would calculate `=B3/B6` without an error. Unless we're careful, we could use the wrong number in our analysis without realizing it.

In general, it is always good practice to audit every formula we intend to move, looking at every cell referenced to ask:

1. Should the row of this reference update when moved up/down?
2. Should the column of this reference update when moved left/right?

The learning environment has a slideshow illustrating each step of creating and moving a formula in Excel. You can advance the slides using the controls at the bottom. Once you feel ready to do this yourself, move on to the next set of instructions!

Transform Data

In this lesson, you've learned how to explore datasets in Excel using three key methods:

* **formulas** to summarize and transform data
* **sorting** to organize data for visual inspection
* **filtering** to zoom in on data points with specified features

Specifically, you've learned how to

* use built-in Excel functions to find out the average, maximum, and sum of a set of numbers
* use draggable cell references and formulas to determine percents and baseline comparisons from raw data
* use sorting on multiple columns to gain insight into trends over time
* use filtering to answer questions about datapoints matching specific criteria

These are the essential skills you need to explore data in Excel and prepare for formal analysis. Congratulations on completing the lesson!

A spreadsheet interface surrounded by tables, charts, and functions.

Let's Review

Since Excel is often used to store and display data, we can scroll to explore, too. If the data is randomly arranged, it's hard to get any insight by just scrolling. But if the data is organized in a predictable way, we might be able to spot patterns that can guide our more formal analysis later on.
		
For example, look at the tornado data in the learning environment (from the [National Weather Service](https://www.spc.noaa.gov/wcm/)). Notice that it is unsorted and difficult to make sense of. Once it is sorted, however, we can see all tornado measurements for each state together. This makes it much easier to visually inspect.


To sort a table of data in Excel by one of its columns, select a cell containing data for that column and then choose an order for sorting from the Sort & Filter menu on the Home tab. 

For example, here's how we would sort this tornado data alphabetically by state:
![An image of the Sort menu in Excle](https://static-assets.codecademy.com/Courses/Spreadsheets/learn-excel-for-data-analysis/exploring-data/sort-by-state.png)

This method would result in a table starting with the tornados in Florida, since F comes before M and O alphabetically.

The option to sort `A to Z` or `Z to A` will only be displayed for text-based data. For numeric and time data, the options are `Sort Smallest to Largest`; or dates, `Sort Oldest to Newest` and vice versa. Sometimes, Excel might interpret the data type incorrectly and display the wrong option. We'll show you how to fix that in a later lesson.

Often, we want to sort by more than one column. For example, we might want to organize our tornado data so that all the tornados in each state are grouped together and only then sorted by date. The natural thing to try would be to sort by state and then sort by date. But, in case this wasn't already complicated enough, we actually need to use the reverse order.

This might seem unintuitive, but let's look at a small example to see why this happens. Consider the following table:

| Date      | State |
| ----------- | ----------- |
|   1/4/20    |FL       |
| 2/5/20| AL|
| 1/31/20    |FL       |
| 1/11/20| AL |
| 2/6/20 |FL |

If we sort newest-to-oldest by date, we'll get the table rearranged by date:

| Date      | State |
| ----------- | ----------- |
| 2/6/20 |FL |
| 2/5/20| AL|
| 1/31/20    |FL       |
| 1/11/20| AL |
|   1/4/20    |FL       |




When we sort by state next, AL will come before FL. But which of the two AL rows will be first? 

The way Excel deals with cells that have the same value is by maintaining the order they were already in. Since we've already sorted by date from newest to oldest, the `2/5/20` row for AL will come before the `1/11/20` row. Here's what the whole sorted table looks like:

| Date      | State |
| ----------- | ----------- |
| 2/5/20| AL|
| 1/11/20| AL |
| 2/6/20 |FL |
| 1/31/20    |FL       |
|   1/4/20    |FL       |

In the learning environment, we've shown what happens if you sort a portion of this tornado dataset by time, and then by date, and then by state. When you're ready to try this yourself, move on to the next set of instructions.


Four images to illustrate how sorting data works. The first one is unsorted tornado data, with columns for date, time, state, length, and width. The rows are in no apparent order. The second image is sorted by time from earliest to latest. The time column now starts with the earliest hour and minute in that column and ends with the largest. The third image takes the time-sorted table and sorts by date, newest to oldest. Now the data is organized so rows on the same day occur together. Within a day, the rows are still sorted by time. The last image sorts by state from A to Z. Now the rows are organized so all rows corresponding to the same state occur together. Within those sub-groups, they are organized by date and then by time.

Sorting Data

A schematic of Excel described from the bottom up in the exercise narrative text.

Let's start by taking a tour of the Excel interface! When you first open up Excel, the sheer number of features can be a little overwhelming. But once you understand the structure behind Excel's layout, you can find the features you need and put the flexibility of Excel to use. 

Excel is structured around two basic purposes: 

1. Storing data and analyses of data
2. Performing analyses of data

Let's walk through the schematic of Excel in the learning environment to the right. At the bottom of the schematic are tabs corresponding to three **sheets** named `Sheet1`, `Sheet2`, and `Sheet3`.

The actual sheets appear above the tabs with the sheet names. The sheets are where Excel stores data tables and the results of data analysis, like charts or calculations. The data analysis tools themselves are stored above the sheets.

The **formula bar** is directly above the sheets. This is where you can view and edit any calculations you are using in your analysis.

The area above the formula bar displays different analytics options depending on which tab is open. In the schematic, the **Home** tab is open and the **Insert** and **Data** tabs are closed. Each icon on a tab is connected to some analytics function like sorting a table or creating a chart.

Depending on your version of Excel, icons may appear differently, but they should always be in the same location. We'll give you directions in each exercise to find the tools you need! 


Introduction to Excel

Sorting is excellent for organizing data, but finding specific data in a sorted table can still take a lot of scrolling. To answer questions about data with specific features, we're better off using **filters**.
		
Filtering a table consists of comparing each row of data to a list of criteria. Only those rows that match the criteria are included in the filtered table. For example, we could filter the tornado dataset by the following criteria:
* state = AR
* date is before 5/1/20

Applying this filter would result in a table of only those tornados in Arkansas before May 1st.

Sorting and filtering are often used together. For example, if we're interested in the length of tornados in Arkansas before May, we could take our filtered dataset and sort by length to see the longest and shortest tornados.

Let's look at an example of taking a question about data and breaking it down into filters. For example, let's imagine we've been asked to find out when the 2020 tornado season started in California (CA).

To break this question down, we start by identifying which columns of the dataset are being restricted. Since we're asked to find out *when* the tornado season *started*, this question will require some manipulation of the Date column. Since we're asked specifically about California, we'll also need to work with the State column.

Since the other columns aren't mentioned in the question, we won't need to filter or sort by them at all.

For the date, we're not asked to find a specific date, just to find the *start*, which will be the first date. This means we will want to sort the Date column from oldest to newest (further in the past to most recent).

For the state, we're given a specific value we want that column to have. For a tornado to be in California, it has to have CA in the state column. 

To get the answer to this question we'll

* filter by state = CA
* sort Date from oldest to newest

The answer will be the date in the first row of this filtered and sorted table. We've put a walkthrough of applying these filters in the learning environment. Note that we're only showing a portion of the tornado data so you can see what happens. Follow along and see how the table changes with each step.

Filter Data

Excel lets us store, organize, and analyze our data all in the same place. This makes Excel a great one-stop-shop for exploring a dataset prior to performing a formal analysis. In this lesson, we'll cover three standard Excel methods for data exploration:
* calculating averages and other statistics using **formulas**
* organizing data using **sorting**
* zooming in on the data we care about by **filtering**

Each exercise in this lesson will come with an interactive Excel spreadsheet for you to download. The spreadsheet will give you instructions to practice what you've learned, provide hints, and give feedback on your answers.

Even old hands at Excel sometimes get stuck on seemingly "simple" tasks, so if you get stuck, please consult the solutions or ask a question in the forums, even if you think you "should" be able to do it. 

An Excel interface surrounded by illustrative tables of data, charts, and formulas.

Introduction to Exploring Data

Let's start by exploring numeric data! In the learning environment, we've loaded a dataset containing the number of new cars produced in the US by year (from the [Bureau of Transportation Statistics](https://www.bts.gov/content/productions-production-shares-and-production-weighted-fuel-economies-new-domestic-and)). Often, exploring numeric data starts with answering basic statistical questions about the data such as

* what is the largest number of new cars in a year?
* what is the smallest number of new cars in a year?
* what is the average number of new cars in a year?

Excel can help us answer all of these. But first, we'll have to know a little more about how cells in a spreadsheet connect to each other. The connections between cells are based on cell "addresses" or "references".


Cell addresses are made up of a  `column letter` followed by a `row number`. For example, the cell containing the number 10 in the following image is named B2 since it is in column B and row 2. The number 10 is the **content** or **value** of the cell.
![rows A through C and columns 1 through 3 of Excel with cell B2 highlighted and containing the number 10](https://static-assets.codecademy.com/Courses/Spreadsheets/learn-excel-for-data-analysis/exploring-data/cellname.png)

In addition to numbers, cells can also contain **formulas**, which are how we'll calculate our maximum, minimum, and average. A formula in Excel always starts with the `=` sign, so Excel knows that it should do some kind of computation. 

For example, since B2 contains the number 10, the formula `=2+B2` would produce the value `12`, since `2+10 = 12`. The cell name B2 gets replaced by its value when Excel does the computation.

We can also do subtraction, division, and multiplication in formulas using

* `-` for subtraction
* `/` for division
* `*` for multiplication

This last one might seem like an unusual symbol, but it is pretty commonly used for multiplication on computers.

Here's an example of using `*` for multiplication. What would you change if you wanted to multiply B2 by 3?

![rows A through C and columns 1 through 3 of Excel with cell B2 containing the number 10 and cell C2 containing the formula =2*B2](https://static-assets.codecademy.com/Courses/Spreadsheets/learn-excel-for-data-analysis/exploring-data/cellformulaformula.png)
![rows A through C and columns 1 through 3 of Excel with cell B2 containing the number 10 and cell C2 containing the number 20](https://static-assets.codecademy.com/Courses/Spreadsheets/learn-excel-for-data-analysis/exploring-data/cellformularesult.png)

Now, you might be thinking to yourself that you could multiply `2` by `10` without a spreadsheet. That's right! But the magic of Excel formulas is that they update automatically when data changes. If the cell `B2` was changed to contain the number `4`, the cell `C2` would automatically update to contain the number `8`, since `2*4 = 8`. These dynamic formulas are the real power of Excel since they let us automatically update analyses with new data.


### Ranges of Cells

To calculate an average we need to reference a whole collection of cells instead of a single cell at a time. To reference a range of data, we type the name of the first cell in the range (usually the top-left cell), a colon, and then the name of the last cell (usually the bottom-right cell).

For example, the range `B1:C4` includes the cells in Columns B-C and Rows 1-4.
![rows A through C and columns 1 through 8 of Excel with cell A1 containing the formula =B1:C4 and the cells B1-B4 and C1 through C4 highlighted](https://static-assets.codecademy.com/Courses/Spreadsheets/learn-excel-for-data-analysis/exploring-data/rangetable.png)

To calculate the maximum, minimum, and average of this range we use special Excel functions `=MAX()`, `=MIN()`, and `=AVERAGE()`, and we we put the range containing the data between the parentheses. 

In the example in the learning environment, the data is contained in the range `B2:B7`, so the full formulas for calculating these three statistics are

* `=MAX(B2:B7)`
* `=MIN(B2:B7)`
*  `=AVERAGE(B2:B7)`

To display the average in cell `B8`, we

1. Select cell `B8`
2. Type the formula into the cell
3. Select `return` or `enter`

You can watch the full process in the slideshow in the learning environment. When you are ready to practice this yourself, follow the next set of instructions!


