# data[].attributes.attachment_link.readable_text
Many articles have been written on why R is better than Excel for data analysis. In this post, I will summarize the reasons why R is advantageous in most data analysis circumstances, with a focus on fantasy football analysis. Although this post focuses on the benefits of using R for fantasy football analysis, the benefits of R extend to many other domains (e.g., finance, time series, machine learning, social sciences; for a list of different domains of R packages, see here).

What is R?

R is a software package and language for statistical analysis and graphics.

When/Why R is Better than Excel

1. Data Manipulation. R allows you to manipulate (e.g., subset, recode, merge) data quickly. Some R packages have been designed specifically for these purposes, e.g., plyr. Typically, a majority of the time spent on an analysis project is spent before the analysis-preparing the data. R is much more adept and efficient in data preparation than Excel. Fantasy data scraped from websites often require many steps in data processing to be ready for analysis, so R is ideal.

2. Easier Automation. R uses a scripting language rather than a GUI, so it's much easier to automate things in R than in Excel. This can save you loads of time, especially when you plan to re-run the same analysis multiple times (e.g., every new fantasy season).

3. Faster Computation. Because of the automation provided by R scripts, many operations are much faster to perform in R than Excel.

4. It Reads Any Type of Data. R can basically read any type of data (.txt, .csv, .dat, etc.). There are also R packages specifically designed to read JSON, SPSS, Excel, SAS, and STATA files. You can also scrape data from websites and execute SQL queries. Scraping websites can be useful for downloading fantasy projections from ESPN and other websites for data analysis.

5. Easier Project Organization. In Excel, projects are often organized in different tabs of the same file. This can make the Excel file slow, clunky, and difficult to navigate. It is easier to keep a project organized when dealing with R scripts because different tasks or sub-projects can be stored in separate files stored in the same folder and linked together in the same project with RStudio. For an example folder structure for R projects, see here.

6. It Supports Larger Data Sets. Excel has restrictions for how large your data can be. Even if if your data don't exceed this maximum size, Excel can become slow with large data sets (especially after you add tabs, formulas, and references). R supports larger data, and can support big data with packages such as Hadoop.

7. Reproducibility. R has features that make it much easier to reproduce the findings of your analysis, which is important for detecting errors. First, it's easy to add comments to your scripts to make it clear what you're doing. Commenting your code is crucial, and can serve as a translation for someone else looking at your code, or as a reminder of what you did 6 months ago! It is difficult to document steps you've done in Excel. Second, data and analysis are separated in R, allowing you to see the logical progression for data analysis in the R code. In Excel, however, data and formulas are together, and it can be difficult to follow the data analyst's train of logic. Third, you can use version control with git a) to track (and revert) changes you make over time and b) to share your scripts with others to collaborate on projects as a community. Having more people examining your work can help find and fix errors and make other improvemnts. Excel files are binary files, so you can't track changes to Excel files. The github site hosting the R scripts for this site is located here. Feel free to use the scripts and suggest improvements!

8. Accuracy. Researchers have shown that Excel and other spreadsheets show important inaccuracies for basic analyses like linear regression. R was specifically designed for statistical analysis, so it is more precise and accurate for data analysis.

9. Easier to Find and Fix Errors. Because R uses scripting rather than clicking, and allows comments and version control, one can see a history of the actions taken to achieve the result. This makes it easier to find and troubleshoot errors. In Excel, however, errors can be hidden in formulas in cells that can be difficult to find. Spreadsheet errors have led to widely-publicized mistakes, including disastrous financial losses, faulty government policies, and the wrong drugs being given to cancer patients. Humans make mistakes and mistakes in data analysis are inevitable, whether with spreadsheets or with R code. The bottom line is that it's easier to find and fix these mistakes in R than it is in Excel, making it more likely that you're getting an accurate result in R.

10. It's Free. Enough said.

11. It's Open Source. Unlike Excel and other proprietary software used for data analysis, R is not a black box. You can examine the code for any function or computation you perform. You can even modify and improve these functions by changing the code.

12. Advanced Statistics. R has many more (and more advanced) statistics capabilities than Excel does. They also tend to be faster and more flexible. Part of the advanced capabilities of R owes to the fact that R is open source and many users have contributed packages for performing specialized functions. For example, this fantasy football draft optimizer uses the Rglpk package to find your optimal starting lineup of players that maximizes the team's projected points while minimizing its downside risk.

13. State-of-the-Art Graphics. R has advanced graphics capabilities (see here for examples and code for how to create them). You can create beautiful graphics using the base R package, or with the lattice or ggplot packages. People like to digest and understand statistics visually, and R provides a better tool for creating pretty visualizations than Excel does.

14. It Runs on Many Platforms. You can use R on Windows, Mac, Linux, and Unix.

15. Anyone (Including You) Can Contribute Packages to the Community to Improve its Functionality. In the chance there isn't an R package that does what you need to do, you can write a function to perform the task and can contribute it as a package to the community for others to use and improve. The number of R packages contributed to the community is increasing at a rapid rate. Chances are, if there's an analysis you need to do, an R package exists to do it.

For these reasons,

1. R is considered the top language for data science. Also see here.

2. R usage is increasing exponentially.

3. The number of R packages is increasing exponentially. Also see here.

4. More data scientists use R than any other software for predictive modeling competitions (including 50% of winners) on Kaggle.

5. An increasing number of jobs require experience with R.

6. R skills attract the highest IT salaries.

7. Many companies use R.

8. Microsoft (the owner of Excel) bought Revolution Analytics (the leading business software implementation of R for data analysis).

When to Use Excel

1. Data Entry. I don't necessarily recommend Excel for data entry, but it does have some nice features including data validation. Data validation ensures that the entered data conform to user-specified requirements (e.g., must be integer between 0-5), and can reduce data entry errors. The problem with saving data in Excel (.xls/.xlsx) format is that these formats don't cooperate well with other software, so if you plan to do any data manipulation or analysis with other software (e.g., R), it gets tricky. You might be able to save your data as comma-separated values (.csv), a more widely used format that can be read by most other data software. In most cases, however, using a simple text editor or relational database management software is best for data entry.

2. Basic Calculations. Excel can be faster for very simple calculations (e.g., descriptive statistics) or data manipulation (e.g., basic sort, filter, pivot table). Anything beyond simple calculations and data manipulation, however, will likely be better in R.

3. Viewing/Scrolling Data at a Glance. Excel can be handy for a quick visual overview of your data. Eyeballing and scrolling through the rows and columns gives you a hands-on way to quickly explore your data visually. There are ways to see your data in R, as well, but a spreadsheet-like view of your data is not presented by default.

4. Data Presentation and Summarization. Excel can be helpful for presenting data that need to be summarized in an aesthetically-pleasing tabular form. For more advanced graphical summaries, however, R is likely better.

How to Download R

You can download R here: http://cran.r-project.org/. I strongly recommend the RStudio text editor for working with R scripts: http://www.rstudio.com/ide/download/.

How to Get Data from Excel into R

Although there are R packages for importing Excel data directly (see here, here, and here), it's generally best to export the Excel data to a .csv file and then import the .csv file in R using the read.csv() function. Here's the code for importing a .csv file in R and storing it in an object called 'mydata':

mydata <- read.csv("C:/excelData.csv")

Where Can I Learn How to Use R?

1. Use this intro to R: http://www.statmethods.net/

2. Here's a guide to R for Excel users (and another one).

3. Check out these resources for getting started with R: http://www.ats.ucla.edu/stat/r/

4. Watch these videos from Coursera: http://blog.revolutionanalytics.com/2012/12/coursera-videos.html

5. Post to the R mailing list or forums if you have questions

6. Read other blogs on R-bloggers

7. Read this site!

Summary

In summary, R has many benefits over Excel and other data analysis software. If you're using Excel for data analysis, give R a try. You'll be thankful you did.

Examples Using R for Analyzing NFL Data
Win Your Fantasy Football Draft with This Draft Optimizer Expected Points by Position Rank in Fantasy Football WR Gold Mining Who Has the Best Fantasy Football Projections: ESPN, CBS, NFL.com, or FantasyPros? Turning Advanced Statistics Into Fantasy Football Analysis New York Times Uses R to Investigate NFL Draft Picks Every NFL Punt Since 2002
Further Reading
http://www.michaelmilton.net/2010/01/26/when-to-use-excel-when-to-use-r/ http://www.burns-stat.com/first-step-towards-r-spreadsheets/ http://www.burns-stat.com/spreadsheet-r-vector/ http://www.burns-stat.com/documents/tutorials/spreadsheet-addiction/ http://blog.revolutionanalytics.com/2013/10/how-to-switch-from-spreadsheets-to-r-for-data-analysis.html http://blog.revolutionanalytics.com/2013/04/more-reasons-not-to-use-excel-for-modeling.html http://blog.revolutionanalytics.com/2013/02/did-an-excel-error-bring-down-the-london-whale.html http://robjhyndman.com/hyndsight/rvsexcel/ http://andrewgelman.com/2013/04/17/data-problems-coding-errors-what-can-be-done/ http://andrewgelman.com/2013/04/17/excel-bashing/ http://r-dir.com/blog/2013/11/r-vs-excel-for-data-analysis.html http://www.quantumforest.com/2013/12/excel-fanaticism-and-r/ http://christophergandrud.blogspot.com/2013/04/reinhart-rogoff-everyone-makes-coding.html http://r4stats.com/articles/popularity/
- Isaac Petersen

My name is Isaac and I'm an assistant professor with a Ph.D. in Clinical Psychology. Why am I writing about fantasy football and data analysis? Because fantasy football involves the intersection of two things I love: sports and statistics. With this site, I hope to demonstrate the relevance of statistics for choosing the best team in fantasy football.

# data[].attributes.attachment_link.title
Why R is Better Than Excel for Fantasy Football (and most other) Data Analysis | Fantasy Football Analytics

# data[].attributes.attachment_link.description
Many articles have been written on why R is better than Excel for data analysis. In this post, I will summarize the reasons why R is advantageous in most data analysis circumstances, with a focus on fantasy football analysis.

# data[].attributes.attachment_link.readable_text
Data analytics are increasingly important components of decision-making in any business. Whether you're a part of a marketing team that needs to generate visuals to highlight industry trends, or you're looking to generate financial statements, you will need an analytics program to help you develop your reports and effectively communicate your findings.

Both R and Excel are excellent data analytics tools, but they each have distinct functionality.

Excel is a well-known software program included in the Microsoft Office Suite. Used to create spreadsheets, execute calculations, produce charts, and perform statistical analysis, Excel is used by many professionals across a variety of industries.

R is a free, open-source programming language and software environment that's frequently used in big data analysis and statistical computing. R has many advanced functions and capabilities.

Read on to learn more about their unique features and which is the better tool to help you solve your current data analysis problems.

Download Our Free Guide to Breaking Into Analytics

A guide to what you need to know, from the industry's most popular positions to today's sought-after data skills.

Differences Between R and Excel

When choosing between R and Excel, it's important to understand how either software can get you the results you need. Here are some key differences between R and Excel to help you decide which makes the most sense to use.

1. Ease of Use
Learning the Software
Most people have likely already learned at least a few basic tips in Microsoft Excel. That's one substantial benefit of using Excel-the initial learning curve is quite minimal, and most analysis can be done via point-and-click on the top panel. Once a user imports their data into the program, it's not exceedingly hard to make basic graphs and charts.

R is a programming language, however, meaning the initial learning curve is steeper. It will take most at least a few weeks to familiarize themselves with the interface and master the various functions. Luckily, using R can quickly become second-nature with practice.
Replicating Analysis
R, while less user-friendly with a more intimidating user interface, has the capability to reproduce analyses repeatedly and with very different datasets. This can be incredibly helpful for large projects with multiple data sets, as you'll keep everything consistent and clean, without having to rewrite the script each time.

Since Excel's user interface is point-and-click, you'll need to rely on memory and repetition frequently. You cannot import codes and scripts as you would with R, so you'll have to "reinvent the wheel" to perform the same analysis across different data sets. This is not detrimental if you are doing basic statistics, but it may become time-consuming with more complicated analyses.

2. Visualization

When deciding between R and Excel, ask yourself, "How detailed do my visualizations need to be in order to achieve my goal(s)?" In Excel, for example, you can quickly highlight a group of cells and make a simple chart for PowerPoint. If you need a more comprehensive graph, however, R may be your best bet. R can produce incredibly attractive, detailed visuals that can help stakeholders understand your findings.

It all comes down to what you need your graphics to do. If you're just looking to cobble together a quick-and-dirty presentation to visualize data for your coworkers, then making simple straightforward charts in Excel will suffice. For those planning to publish large amounts of complicated data to various stakeholders, spending the time in R to create impressive interactive visual representations will likely be worth your while.

3. Price, Community, and Customization

Excel costs at least $70 a year as part of Office 365, which also includes Outlook, Word, PowerPoint, and other software. You can download free add-ins for Excel also, including options to improve visualization, among others. Since Excel is part of Microsoft, you also gain access to their community of forums and technical help professionals.

R, however, is free to download. As R is open-source and open to the public, it has gathered a vibrant community, with many forums, websites, and Reddit boards devoted to sharing resources and tips. If there's something you want to do in R, someone is out there to help.

R's open-source nature, engaged community, and free price tag are welcoming, but Excel's price is affordable and the community is also strong.

4. Statistical Analysis

When comparing R and Excel, it's important to define the level of information you are looking for. If you simply want to run statistics and arithmetic quickly, Excel might be the better choice, since it's an easy point-and-click way to run numbers.

Pivot tables in Excel have been gaining traction the past few years, and it's one of the program's more useful features for stats. You can easily compute large amounts of data, define variables, and easily choose what rows and columns you want to compare and gather reports from. It's fairly easy to make a pivot table, and they provide powerful benefits. Excel's spreadsheets have a finite number of rows and columns, however, so you'll be unable to analyze massive datasets that can be handled with R.

R programming can do a tremendous amount of analysis, is great for identifying trends you might not have thought to look for, and it can even decide how reliable said statistics are. R allows you to clean and organize data, gives more visualization options, and if there's a topic you want to explore, then there's likely a way to do it in R. If you're looking to do anything beyond basic statistical analysis, such as regression, clustering, text mining, or time series analysis, R may be the better bet.

5. Careers

Aptitude with Excel and R are incredibly valuable competencies that are in-demand across a variety of industries. Countless jobs are looking for applicants with at least some Excel experience (pivot tables look really good on a resumé), but R has a higher earning potential and is more in-demand than Excel.

R is one of the most popular programming languages and is an industry-standard for data analytics and data science. If you want to enter either field, there's a good chance you'll have a competitive advantage by knowing R. Entry-level jobs for those focusing on R also tend to make a high salary, frequently starting off earning more than $75,000.

Countless job listings also require Excel competency. From administrative assistants, marketers, academics, and more, everyone is expected to use Excel to some degree, whereas 10 to 15 years ago it was optional. Having a good background in Excel is still attractive on a resumé and will help to land a career with a high earning potential, but there are not many jobs looking for Excel skills alone.

Using R and Excel

R and Excel are beneficial in different ways. Excel starts off easier to learn and is frequently cited as the go-to program for reporting, thanks to its speed and efficiency. R is designed to handle larger data sets, to be reproducible, and to create more detailed visualizations. It's not a question of choosing between R and Excel, but deciding which program to use for different needs.
To learn more about the latest data analytics trends and how to gain today's in-demand analytics skills, download our free guide below.

# data[].attributes.attachment_link.description
Data analytics are increasingly important components of decision-making in any business. Whether you're a part of a marketing team that needs to generate visuals to highlight industry trends, or you're looking to generate financial statements, you will need an analytics program to help you develop your reports and effectively communicate your findings.

# data[].attributes.attachment_link.readable_text
A Data Model allows you to integrate data from multiple tables, effectively building a relational data source inside an Excel workbook. Within Excel, Data Models are used transparently, providing tabular data used in PivotTables and PivotCharts. A Data Model is visualized as a collection of tables in a Field List, and most of the time, you'll never even know it's there.

Before you can start working with the Data Model, you need to get some data. For that we'll use the Get & Transform (Power Query) experience, so you might want to take a step back and watch a video, or follow our learning guide on Get & Transform and Power Pivot.

Where is Power Pivot?

Getting started

First, you need to get some data.

1. In Excel 2016, and Excel for Microsoft 365, use Data > Get & Transform Data > Get Data to import data from any number of external data sources, such as a text file, Excel workbook, website, Microsoft Access, SQL Server, or another relational database that contains multiple related tables.

In Excel 2013 and 2010, go to Power Query > Get External Data, and select your data source.

2. Excel prompts you to select a table. If you want to get multiple tables from the same data source, check the Enable selection of multiple tables option. When you select multiple tables, Excel automatically creates a Data Model for you.

3. Select one or more tables, then click Load.

If you need to edit the source data, you can choose the Edit option. For more details see: Introduction to the Query Editor (Power Query).

You now have a Data Model that contains all of the tables you imported, and they will be displayed in the PivotTable Field List.

Notes:

Models are created implicitly when you import two or more tables simultaneously in Excel.

Models are created explicitly when you use the Power Pivot add-in to import data. In the add-in, the model is represented in a tabbed layout similar to Excel, where each tab contains tabular data. See Get data using the Power Pivot add-in to learn the basics of data import using a SQL Server database.

A model can contain a single table. To create a model based on just one table, select the table and click Add to Data Model in Power Pivot. You might do this if you want to use Power Pivot features, such as filtered datasets, calculated columns, calculated fields, KPIs, and hierarchies.

Table relationships can be created automatically if you import related tables that have primary and foreign key relationships. Excel can usually use the imported relationship information as the basis for table relationships in the Data Model.

For tips on how to reduce the size of a data model, see Create a memory-efficient Data Model using Excel and Power Pivot.

For further exploration, see Tutorial: Import Data into Excel, and Create a Data Model.

Create Relationships between your tables

The next step is to create relationships between your tables, so you can pull data from any of them. Each table needs to have a primary key, or unique field identifier, like Student ID, or Class number. The easiest way is to drag and drop those fields to connect them in Power Pivot's Diagram View.

1. Go to Power Pivot > Manage.

2. On the Home tab, select Diagram View.

3. All of your imported tables will be displayed, and you might want to take some time to resize them depending on how many fields each one has.

4. Next, drag the primary key field from one table to the next. The following example is the Diagram View of our student tables:

We've created the following links:

tbl_Students | Student ID > tbl_Grades | Student ID

In other words, drag the Student ID field from the Students table to the Student ID field in the Grades table.

tbl_Semesters | Semester ID > tbl_Grades | Semester

tbl_Classes | Class Number > tbl_Grades | Class Number

Notes:

Field names don't need to be the same in order to create a relationship, but they do need to be the same data type.

The connectors in the Diagram View have a "1" on one side, and an "*" on the other. This means that there is a one-to-many relationship between the tables, and that determines how the data is used in your PivotTables. See: Relationships between tables in a Data Model to learn more.

The connectors only indicate that there is a relationship between tables. They won't actually show you which fields are linked to each other. To see the links, go to Power Pivot > Manage > Design > Relationships > Manage Relationships. In Excel, you can go to Data > Relationships.

Use a Data Model to create a PivotTable or PivotChart

An Excel workbook can contain only one Data Model, but that model contain multiple tables which can be used repeatedly throughout the workbook. You can add more tables to an existing Data Model at any time.

1. In Power Pivot, go to Manage.

2. On the Home tab, select PivotTable.

3. Select where you want the PivotTable to be placed: a new worksheet, or the current location.

4. Click OK, and Excel will add an empty PivotTable with the Field List pane displayed on the right.

Next, create a PivotTable, or create a Pivot Chart. If you've already created relationships between the tables, you can use any of their fields in the PivotTable. We've already created relationships in the Student Data Model sample workbook.

Add existing, unrelated data to a Data Model

Suppose you've imported or copied lots of data that you want to use in a model, but haven't added it to the Data Model. Pushing new data into a model is easier than you think.

1. Start by selecting any cell within the data that you want to add to the model. It can be any range of data, but data formatted as an Excel table is best.

2. Use one of these approaches to add your data:

3. Click Power Pivot > Add to Data Model.

4. Click Insert > PivotTable, and then check Add this data to the Data Model in the Create PivotTable dialog box.

The range or table is now added to the model as a linked table. To learn more about working with linked tables in a model, see Add Data by Using Excel Linked Tables in Power Pivot.

Adding data to a Power Pivot table

In Power Pivot, you cannot add a row to a table by directly typing in a new row like you can in an Excel worksheet. But you can add rows by copying and pasting, or updating the source data and refreshing the Power Pivot model.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers community.

See Also

Get & Transform and Power Pivot learning guides

Introduction to the Query Editor (Power Query)

Create a memory-efficient Data Model using Excel and Power Pivot

Tutorial: Import Data into Excel, and Create a Data Model

Find out which data sources are used in a workbook data model

Relationships between tables in a Data Model

# data[].attributes.attachment_link.description
A Data Model is a new approach for integrating data from multiple tables, effectively building a relational data source inside the Excel workbook. Within Excel, Data Models are used transparently, providing data used in PivotTables, PivotCharts, and Power View reports. You can view, manage, and extend the model using the Microsoft Office Power Pivot for Excel 2013 add-in.

# data[].attributes.attachment_link.title
Get more out of Find and Replace in Excel

# data[].attributes.attachment_link.description
Find and Replace in Excel helped me impress my boss and made me an Excel Whiz in office. Here are 4 tips that can do the same for you

# data[].attributes.attachment_link.title
Excel Crash Course - Data Cleaning in Excel - Microsoft Excel Tutorial

# data[].attributes.attachment_link.readable_text
In his day and age, our dependence on data is overwhelming. Thanks to our cellphones and laptop, a halo of data surrounds our life. Data is nothing but a piece of classified information.

Microsoft Excel is one of the most used data handling/analysis software. At the same time, one tiny mistake in analyzing data can cause headaches. Simple errors like spacing, value error, format, duplicates, etc. usually miss our eye.

Imagine the chaos when you handle large chunks of information. Keeping your Data clean and organized can take you miles ahead in your work ethics and efficiency.

Here's a list of Top 10 Super Neat Ways to Clean Data in Excel as follows.

1) Get Rid of Extra Spaces:

Extra Spaces are difficult to spot & correct. Multiple spaces may be easy, but trailing spaces are pretty tough. Trailing spaces are blank spaces at the end of the statement or word which are not followed by any other character.
Steps:

Consider data with four cells with different spacing errors.

Now select a column & type "TRIM("

Now select the cell you want to correct (in matters of spaces).

The cell will be corrected. If there are other erroneous cells sequentially aligned, drag the fixed cell till the point, you want to check & correct.

Here's an easy way to spot & eliminate such errors:

Syntax: TRIM(text)

This easy step can save you time!
Steps:

Select the entire Data (you want to treat)

Press F5 (on keyboard)

A dialogue box will appear > Select "Special"

Select "Blanks" & click "OK"

Now, all blank cells will be highlighted in pale grey color, out of which one cell would be white with a different border. That's the active cell, type the statement you want to replace in blank cells.

Hit "Ctrl+Enter"

2) Select & Treat all blank cells:

Blank cells are troublesome because they often create errors while creating reports. And, people usually want to replace such cells with 0, Not Available or something like that. But replacing each cell manually on a large data table would take hours. Luckily, there's an easy way to tackle this problem.

NOTE:
At the last step, if " Enter" only is pressed, then the value will be inserted only in the active cell. So remember to press " Ctrl+Enter."

Steps:

Select any blank cell & type 1

Select that cell & hit "Ctrl+C"

Now select your data set & go to Paste > Paste Special

In Paste Special, select "Multiply" option in the "Operation" category

Click "OK"

3) Convert Numbers Stored as Text into Numbers:

When we import data from files, other sources, databases, text, etc. During transit, data might get affected. Also, some have a habit of using an apostrophe before numerical values, which is considered as text in Excel. Such minor data conversion can drastically affect calculations.
A) Highlight Duplicates:

Select the data & go to Home > Conditional Formatting > Highlight Cell Rules > Duplicate Values

A dialogue box will appear (Duplicate Values), Select Duplicate & formatting color

Press OK

All duplicate values will be highlighted!

B) Delete Duplicates:

Select the data & go to DATA > Remove Duplicates

A dialogue box will appear (Remove Duplicates), tick columns whose duplicates need to be found.

Remember to have a click on "My data has headers" (if your Data has headers) or else column heads will be considered as data & duplication search will be applied on it too.

Click OK!

Suppose there are three values "70, '70, 80". When we compare 70 and 80 (70<80), the result is "TRUE." But when we compare "apostrophe 70 & 80" ('70<80), the problem starts. Here the result will be FALSE as the text will be rated higher than any number. To eliminate such errors, here's a trick.
A) Using Conditional Formatting:

Select the Data

Go to Home > Conditional Formatting > New Rule

Within New Rule, Select "Format only cells that contain."

In Rules, Select "Errors" & Click on "Format"

Select any color & click OK

Hit the final "OK" button

Here it multiples every single value to "1". And anything multiplied by 1 is the same number. But this trick also takes care of the apostrophe numerical.
B) Go to Special:

Select the Data

Press F5

Click on "Special"

A dialogue box appears (Go to Special), Select Formulas

Now you get four options in Formulas, deselect all options except "Errors"

Click OK! Now all errors are selected, you can delete them manually or replace a statement.

If you wish to replace, then type the statement at active cell & hit "CTRL+ENTER."

4) Remove Duplicates:
Steps:

Just type the formula you want to use, suppose "LOWER(" and select the cell whose case needs to be changed.

Hit "CTRL+ENTER."

The case has been changed & consistent

Drag down to do the same for other cells.

Similarly for UPPER() & PROPER()

Elimination of duplicate data is necessary for the creation of unique data & less usage of storage. In duplication, you can either highlight it or delete it.
Steps:

Select the Data

Go to Data> Text to Column

A dialogue box will appear (Convert Text to Columns Wizard - Step 1 of 3), select Delimited or Fixed Width as per your convenience.

Delimited is to be selected if the width isn't fixed, click "NEXT"

In Delimiters tick the option which separates your text in the cell. Suppose "Norwich Cathedral, Norwich, UK," here three values are separated by commas. So we will select "Comma" for this example. And, deselect rest options.

View the preview & click on "NEXT"

Select Column Data Format & destination cell address

Click "FINISH"

Duplicate values will be removed! Suppose you select 4 of 4 columns. Then that four columns rows should also match or else; they won't be considered duplicate.

Select the Data

Press "F7"

A dialogue box appears, which shows you the possible wrong word & it's the possible correct spelling. Click on "Change," if you agree with the suggestion.

Check & change till it says "Spell check complete. You're good to go!"

5) Highlight Errors:
A) Changing Cell References:

Press "CTRL+H" to open "Find and Replace"

Now in Replace > "Find What" (change the reference range too) "Replace With"

Suppose Find What: $B to Replace With: $C

Click on "Replace All"

Similarly finding & replacing using reference range we can clean the Data

B) Find & Change Specific Format:

Press "CTRL+H"

Select "Options"

Now go to "Format" of "Find What." Here you can specify the format or choose a format from the cell. Suppose you select a format.

Now it will show you the preview for "Find What."

Click on "Format" of "Replace With." Suppose we go for "Format..."

Now select format, example: Number, Alignment, Font, Border, Fill, Protection.

Suppose we select Color then select any color to fill the column header cell.

Click on Replace All

Instantly the format has been changed!

C) Removal of Line Breaks:

Press "CTRL+H"

Find and Replace dialogue box will appear, press "CTRL+J"

Go to the replace with box & type a single space

Click Replace All

All rows will be managed in one row within the same cell!

D) Removal of Parenthesis:

Select the Data

Press "CTRL+H"

Type (*) in "Find What" (This will consider all characters within parenthesis)

Leave the Replace With column empty & click Replace

Parenthesis characters are removed!

While creating reports or dashboards, you might face a few arithmetical errors (like divisional errors). Such errors are easy to spot if the Data is small. But for big data, it's complicated. So to get rid of such mistakes, you can go for two ways: Conditional Formatting or Go to Special.

All the cells with errors are highlighted & now are easy to spot.

6) Change Text to Lower/Upper/Proper Case:

While importing data, we often find names in irregular forms like a lower, upper case, or sometimes mixed. Such errors are not easy to eliminate manually. Here's a fingertip trick to bring back the consistency.

7) Parse Data Using Text to Column:

Sometimes the received Data has texts filled in one cell, only separated by punctuations. Usually, the addresses are cramped in one cell separated by a comma. To distinguish values in separate cells, we can use "Text to Column."

8) Spell Check:

Spelling mistakes are common in text files & PowerPoint. However, MS points out such errors by underlining it with colorful dashes. And, MS Excel doesn't have such feature. But you can use it below steps:

9) Delete all Formatting:

Suppose you want to clear all the formats, including highlights & borders. You can do this by selecting the data & go to HOME > Clear (in editing group) > Clear Formats. It will clear the formats & you get standard content without highlights or borders. Similarly, you can clear Content, Comments, Hyperlink, or entire data (using Clear All).

10) Use Find & Replace to Clean Data in Excel

Suppose we have a data where it is separated by line breaks (same cell but different rows). To remove these line breaks, follow the below steps:

Conclusion:

These were TOP 10 Super Neat Ways to clean data in Excel. We have discussed the elimination of different types of data errors in simple ways. I hope you liked it!

# data[].attributes.attachment_link.title
10 Quick Ways to Clean Data in Microsoft Excel Easily

# data[].attributes.attachment_link.description
In his day and age, our dependence on data is overwhelming. Thanks to our cellphones and laptop, a halo of data surrounds our life. Data is nothing but a piece of classified information. Microsoft Excel is one of the most used data handling/analysis software.

# data[].attributes.attachment_link.readable_text
Do you know what comes before data analysis? Data integrity. Well, it should anyway. Because if your data is rubbish, then your analysis will be.

Before you can do any analysis with a dataset, it should be correct, consistent, and complete. Otherwise, you could end up with a Kodak moment. Not the good kind, the $11m mistake kind .

Here are a few tips for dealing with a dirty dataset.

Removing Duplicates

One of the most common challenges with data is duplicates. Dealing with duplicates can be straightforward. But before we start, it's a good idea to take your dataset and make a copy. Deleting data is permanent, so it's always safer to keep a copy of what you started with. Now, if your dataset is full of duplicates rows, you can highlight the entire dataset and navigate to the Data tab in the Excel ribbon. There, you can click remove duplicates and, voila - Excel will delete duplicate values so that only the first remains. Many of us are familiar with that process, but we think you should take a step back and do some due diligence before removing duplicates.

Two rows may share many values but be separated by others. Take this example:

In a dataset containing membership contact details, two rows may contain the same email address, but the names in each row are different.

There are a couple of different scenarios that may explain this.

The names are of two family members who share an email address, but are of course two distinct people - two members.

It's one person who has input two different variations of their name-Bob and Robert, for example. Or perhaps a simple typo - Robert and Roebrt.

If we've selected the whole dataset, Excel doesn't see these rows as duplicates. So we could end up with excess data, giving false impressions about the number of members.

So how do we account for things like this? Well, one way is conditional formatting. We could highlight a column - let's say the 'email' column - then Navigate to the 'Home' tab on the ribbon, and click the 'Conditional Formatting' dropdown. Hover over 'Highlight Cell Rules', then click 'Duplicate Values'.

Now, select Data > Filter from the excel ribbon. Click the down arrow in the heading of the 'email' column, then Filter by colour, specifically the colour used to highlight duplicate values. You'll be left with the duplicate emails, and can spot check for similarities and differences between rows before choosing which duplicates need removing.

If text contains

Sometimes when we have a dataset, we want to look for something that links cells. An example might be displaying contacts with specific email domain extensions - '.co.uk' and '.org.uk' for example.

An easy, effective way to do this is with filtering. While sitting in your column heading, go to the Data tab of the Excel ribbon, and click the Filter button. There is an option for Text Filters - hover over it, and select the 'Contains...' option.

Make sure to pick the right option - here; we are looking for 'org.uk' OR 'co.uk'.

We selected contains from the dropdowns, and these were the results. There are other very useful options aside from 'contains' too, though. For example, if you wanted to filter certain results from a column of payments, you could use 'greater than or equal to' to find payments over a certain threshold.

If you want to search for values within a cell and return values, Dave Bruns over at ExcelJet has an excellent formula for just that .

MIN/MAX

Another extremely handy technique to use when cleaning data is to search for outliers in values using MIN/MAX. The classic mistake that Kodak fell victim to was adding too many zeros to a particular severance accrual record, resulting in an $11 million overstatement . It's almost impossible to spot such errors in a vast database with the naked eye, but with the MAX function, you can return the largest value in a dataset, thus catching errors that would have otherwise gone unnoticed.

On the other hand, you may want to ensure nobody has made an underpayment, which is where MIN comes in. Working in much the same way, MIN simply returns the smallest value in a search area.

In the example below, we were able to discover both a significant overpayment and a similarly serious underpayment by searching the payment column:

In cell E2, we input the formula =MAX(D:D)

In cell F2, we input the formula =MIN(D:D)

Splitting Text to Columns

Another popular technique used in data cleaning is the text to columns wizard. If your spreadsheet contains a column in which the cells contain lists of items, you may want to put each item into its own cell.

In the example below, we've added a column whose cells list hats owned by each person.

Highlight the area you'd like to split - in this case, the whole of Column D. Hop over to the Data tab in the Excel Ribbon once more, and click the Text to Columns button (as shown above).

As all of our list items are separated by commas inside the cell, that is what is called our delimiter. So, as below, choose 'Delimited', and click next.

Now, ensure the correct delimiter is selected. For us, it's a comma, so we check that box. If you're happy with the preview, click next again.

Usually, leaving the column data format as General is just fine. So now, you just need to click finish, and you are done.

Concatenate

If you require the opposite - as in, you are looking to combine various cells, you need the CONCATENATE function. It's pretty simple - if you'd like to combine the a bunch of cells on one row, like D2 to J2, you use the formula =CONCATENATE(D2,E2,F2,G2,H2,I2,J2). With a large dataset, you can use the autofill handle to fill the entire column:

Final thoughts

Using one or any combination of these tips should help to ensure your dataset is properly prepared to dig into. There are plenty of other tips that may be of interest, from freezing the top row of your sheet, to the IFERROR function, but there's plenty here to start.

Cleaning data is a vital skill. It's useful in many work situations and is a fundamental part of any data analysis project. But there's much more to it. To perform excellent data analysis, you must think like an analyst. You must understand the problem space and question you're trying to answer, clean and process your data, and have the tools and techniques to analyse it, drawing out inferences and insights that aren't visible in the raw data alone.

In our course - Analysing Data in Excel - we cover all the above and more. We've been training companies of all sizes and individuals since 2009, and we've condensed that experience into this short, impactful course.

Course syllabus
The course covers the fundamental skills, tools and functions to be competent at analysing data in Excel. Including:

Excel functions

Excel formulae

PivotTables, conditional formatting, timelines, and slicers

PivotCharts

Spreadsheet management/efficiency

Excel shortcuts/commands

Thinking like a data analyst

Learning outcomes

Produce a range of spreadsheets, including a cleaned data set, PivotTables, PivotCharts

Apply Excel formulae - such as concatenate, trim, and proper - to data sets to clean and analyse data

Calculate mathematical functions including averages, counts and sums

Identify which Excel functions and formulae are appropriate to perform mathematical functions, clean and interpret data, and answer business questions

Improve decision making, thinking efficiency and problem-solving in Excel

Apply numerical and visual formatting, including conditional formatting, slicers and timelines, to spreadsheets to produce clean and comprehensible workbooks

Improve spreadsheet efficiency and maintenance with shortcuts, navigation commands, and design principles

# data[].attributes.attachment_link.title
How to clean your data using Excel | Tips and tricks - FutureLearn

# data[].attributes.attachment_link.description
Do you know what comes before data analysis? Data integrity. Well, it should anyway. Because if your data is rubbish, then your analysis will be. Before you can do any analysis with a dataset, it should be correct, consistent, and complete. Otherwise, you could end up with a Kodak moment.

# data[].attributes.attachment_link.description
Misspelled words, stubborn trailing spaces, unwanted prefixes, improper cases, and nonprinting characters make a bad first impression. And that is not even a complete list of ways your data can get dirty.