#BASIC DATA ANALYSIS IN EXCEL HOW TO#

It essentially allows you to use a selected range as a lookup table and return a “looked up” result to a cell. XLOOKUP is like a combination of VLOOKUP and HLOOKUP, since it can go either vertically or horizontally to lookup a value from a range. It will tell us how many unique values remain. The duplicates will be dropped from the dataset. Make sure the My data has headers checkbox is marked if the column names are not displaying.Ĭlick OK. The editor allows us to select columns that should be included when deleting the duplicates. Highlight the dataset in Excel and click Remove Duplicates. The Remove Duplicates feature is available under Data > Data Tools > Remove Duplicates. Using conditional formatting rules, you can highlight the duplicate data to review it before deleting it.

#BASIC DATA ANALYSIS IN EXCEL HOW TO#

Remove duplicatesĭata is often messy, so it is important that you know how to remove duplicates. It is possible to have multiple rules affecting the spreadsheet. įrom the manager we are able to create new rules or edit existing ones. If we decide we want to adjust the conditional formatting rule we just created, follow path Conditional Formatting > Conditional Formatting Rules Manager. The cells in the column with a value greater than 1987 will turn light red. The editor will pop up:Įnter 1987 and click OK. Select the Year_Birth column and click Conditional Formatting > Highlight Cells Rules > Greater Than. Let’s say we want to highlight all Year_Birth values greater than 1987 in the dataset. It is useful for highlighting outliers, duplicates, or patterns in data. Apply the rules to one cell or multiple cells in the same worksheet. Conditional formatting allows you to highlight or hide cells based on a rule you specify.

Thinking about it, I probably use conditional formatting more than any other feature in Excel. It only takes a few clicks to aggregate data and visualize it in Excel, which is why it is such a widely-used tool. You can add additional dimensions or filters by dragging new fields into the corresponding box. The pivot table and chart should populate. If the data type were numeric, the aggregation defaults to Sum. Since the Marital_Status data is a string, the Values aggregation should default to Count. Then drag Marital_Status from the field list into the Values box. In the PivotChart Fields, drag Marital_Status from the field list into the Axis (Categories) box. Click OK and the pivot table will generate. The selected range will appear in the Table/Range field by default. The Create PivotTable editor will pop up. To create a pivot chart and pivot table, first select the range of data you want to include then click Insert > PivotChart > PivotChart & PivotTable. Using a pivot table makes that super simple, because it transforms the data and aggregates it for us. Let’s say we wanted to count the records grouped by Marital_Status. We can create a chart along with the table using the PivotChart feature under Insert.

Pivot tables provide a simple approach to reformatting columns and rows, transforming them into groupings, statistics, or summaries. Pivot charts visualize the data expressed in a pivot table, giving us insight at a glance. One of my favorite features in Excel is pivot charts and, as a close follow-up feature, pivot tables. Here’s our very own senior data scientist, Tom, explaining these functions in a video: I am using the Customer Personality Analysis dataset from Kaggle in these examples. In order to show the power of data analysis in Excel, we need some data to play around with and graph. You can click any of the features to skip ahead to them. In this article, we’ll go over the 10 features and functions for using data analysis in Excel I think every analyst needs to know: One of the reasons Excel is so popular is because it is jam-packed with features and functions that can be used to clean, aggregate, pivot, and graph data. I personally use Excel almost every day because it can sometimes lead to quicker results than spinning up Python or loading the data into a database. Today, Microsoft’s ubiquitous spreadsheet program Excel has over 750 million users and is used in some of the largest companies worldwide. Spreadsheets are still in style! The use of electronic spreadsheets date back to 1979 and are still one of the most popular ways to review and manipulate data.

Basic data analysis in excel