Learn to load, validate, and present data in Excel.

Handling Data

Because Excel is used to visually explore datasets and present those datasets for others, we often want to change the way tables are displayed to improve human readability.

We've loaded a table of data in the first slide in the learning environment. This table is perfectly accurate, but it isn't very easily readable. We want to present tables that provide insight, not tables that provide headaches!

We can solve this problem using Excel's formatting tools. These tools change the way data is displayed without changing any of the underlying data to maintain data integrity. Play through the slideshow in the learning environment to see how we can reformat this data using Excel. What do you think of the end result? 
	
We've emphasized that formatting doesn't change the value of a cell. Let's look at an example of why this is important to remember.

In the Excel table below, the cell `B3` is adding the values in `B1` and `B2`.

|   | A    | B   |
|---|------|-----|
| 1 |      | 1.1 |
| 2 |      | 1.4 |
| 3 | sum: | 2.5 |

If we format these numbers to have no decimal places, the table will be displayed like this:

|   | A    | B |
|---|------|---|
| 1 |      | 1 |
| 2 |      | 1 |
| 3 | sum: | 3 |

It seems as if Excel is calculating `1+1=3`! But this is just a consequence of the rounding, the underlying summation is still `1.1+1.4 = 2.5`.

When you're ready to practice formatting for yourself, get started on the next set of instructions!


Formatting Data

Even the most advanced analytics techniques are only as good as the data being used and the clarity with which results are presented. So handling your data properly is one of the most important skills you can learn in data analysis.

In this lesson, you'll learn how to prepare for analysis by

* importing external datasets from CSV files
* cleaning and validating data prior to analysis

You'll also learn how to present your analysis by

* formatting tables for human readability
* protecting data when sharing spreadsheets with others

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

Introduction to Handling Data

Okay, so we've explored a dataset and have some preliminary results to share with a team member. But our team member doesn't use Excel much, and we don't want them to accidentally overwrite a crucial formula. 
	
Excel provides built-in tools for protecting different aspects of a spreadsheet. It is important to note that none of these will stop a malicious actor from accessing or altering data. All anyone has to do to circumvent protected sheets and cells is to open the file in another program like Google Sheets.

However, protected and hidden sheets are still useful for preventing accidental data overwrites by ourselves or by others, controlling input to a spreadsheet, and cleaning up a spreadsheet for presentation.

Three common options for protecting and hiding data in Excel are

- Protecting an entire sheet from editing.
- Unprotecting specific cells to allow user input or data updates.
- Hiding sheets to keep raw data and background calculations out of the way.

We've placed a slideshow in the Learning Environment illustrating how to do each of these in Excel.

### Best practices for protecting and hiding ranges

Protected and hidden ranges can be very helpful. They can also be very frustrating. Pretty much every Excel user has a story of hours lost hunting for hidden data in a large spreadsheet. We recommend the following practices for avoiding these frustrations:

- Give hidden sheets meaningful names. Nobody wants to spend hours hunting for data hidden on `Sheet45321`.
- Highlight any unlocked cells in an otherwise protected sheet. Highlighting unlocked cells will both warn users that those cells can be overwritten and will also direct them to the cells they are supposed to change. For example, this is why we included "input your answer here" messages in the exercise spreadsheets for this course!
- Establish norms for spreadsheet hygiene with your team. If everyone follows the same conventions for protecting data, it will be easy to open and use each other's spreadsheets!


Protecting Data

After importing a data file, we're often eager to get started with exploratory data analysis. Unfortunately, raw data is often messy data. If we don't take some time to inspect and clean a dataset, we may get meaningless or misleading results.

For example, let's take a look at this table of monthly sales:

![An Excel table with Month and Sales columns. The month column has entries: jan, jan, february, february, april, march, mar, and january. The sales column has entries: 1000, 100, 50, 30, 30, 22, 1000, 500](https://static-assets.codecademy.com/Courses/Spreadsheets/learn-excel-for-data-analysis/handling-data/clean-text/table.png)

Suppose we want to know the sales associated with `january`. If we just dive in and filter the table on `month equals january`, we'll get a value of 500.

![The same Excel table, now filtered to only show the single row with month january and sales 500](https://static-assets.codecademy.com/Courses/Spreadsheets/learn-excel-for-data-analysis/handling-data/clean-text/clean-text-0.png)

But there are several other rows of the original table that ought to be associated with `january`! Unfortunately, Excel can't tell that `jan` and `january` really mean the same thing. Even harder to spot is the fact that one of the rows labeled `jan   ` has three spaces at the end. We can't see this extra whitespace easily, but Excel will treat it as a different category.

In a small dataset, these inconsistencies can be edited by hand. For larger datasets, Excel provides text cleaning functions that we can write once and then drag down a column like any other formula. You can even nest these functions (like in the last slide to the right), though nesting is outside the scope of this course.

We've placed a slideshow demonstrating common text-cleaning functions in the learning environment. When you're ready to try these out yourself, move on to the next set of instructions!

Cleaning Text Data

Many datasets are stored in formats other than Excel's **XLSX** format. One of the most common storage formats used across the data science world is the **CSV** or **comma-separated values** format. 

A CSV file is a text file containing data where the columns of data are separated by commas. Here's a sample of what a CSV file might look like if you open it up in a text editor:

```
month,sales,costs
Jan,1000,500
Feb,2000,800
```

Here's what this same CSV file looks like interpreted as a table:

| month | sales | cost |
|-------|-------|------|
| Jan   | 1000  | 500  |
| Feb   | 2000  | 800  |

### Other Delimiters

CSV files typically use commas as **delimiters**, the symbol that indicates where one column ends and another begins. However, in some cases this doesn't work very well. For example, in some countries the `,` is used as a decimal marker. In these countries, using a `,` as a delimiter will cause problems, since Excel can't tell the difference between `,` as a decimal and `,` as a delimiter. In cases like this, it's better to use another symbol, like the semi-colon `;`, as a delimiter. Even with a different delimiter, the format is still called CSV.

Excel provides an import tool that lets us select which symbol is being used as a delimiter. We've placed a slideshow illustrating this process in the learning environment. Note that some of the buttons and menus might look slightly different depending on your operating system and version of Excel.

When you're ready to practice this yourself, get started on the instructions below!


Importing Data

Before moving on to analysis, we also need to inspect and validate numeric data. There's no one rule for what valid numeric data looks like, since the definition of "invalid" data depends on what we expect "valid" data to look like. For example, an entry of `-500` might be suspicious in a column of Celsius temperatures but reasonable in a column measuring the sea floor relative to sea level.
	
While there is no one rule for cleaning data, there are some common questions that can help guide our inspections:

1. Are there any blank or missing data points?
2. What range of numbers (or dates) are we expecting?
3. Are there any data points that don't meet those expectations?

We've already learned the tools we need to answer these questions! For example:

* **missing data points**: use the `Filter` menu and select `(Blanks)`
* **unexpected values**: use sorting or functions to inspect minimum and maximum values or trends over time

Of course, identifying invalid or messy data is only the first half of the process. Once we identify these datapoints we have to decide what to do with them. There is a whole science to handling missing data, so be sure to check out our course on [Handling Missing Data](https://www.codecademy.com/learn/handling-missing-data) before working with missing data.

For this course, imagine that you were asked to review the datasets to the right. You've opened them in Excel and are inspecting the data. What would you do in each case? Think critically about if you can (or should) use the data and what types of analysis would be valid, and what wouldn't. 

Flip the cards to read our answer.

When you're ready to practice inspecting data for missing or suspect entries, move on to the next set of instructions!

Inspecting Numeric Data

Congratulations! You're now ready to analyze data from start to finish in Excel. You've learned how to prepare for analysis by:

* importing raw data into Excel from CSVs
* cleaning inconsistent text data
* inspecting numeric data for missing or potentially invalid entries

After performing your analysis, you've learned how to

* make tables human-readable with custom formatting
* protect data from being accidentally overwritten
* clean up the presentation of a file by hiding unneeded sheets
