10 Essential Excel Features For Data Analysts (and How to Use Them)

You cannot go wrong learning spreadsheets

Eric Kleppen

--

This tutorial was originally written for CareerFoundry. Check them out if you’re interested in becoming a UX designer, UI designer, web developer, or data analyst from scratch!

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. Today, Microsoft’s ubiquitous spreadsheet program Excel has over 750 million users and is used in some of the largest companies worldwide. 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.

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. 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:

  1. Pivot tables and pivot charts
  2. Conditional formatting
  3. Remove duplicates
  4. XLOOKUP
  5. IFERROR
  6. MATCH
  7. COUNTBLANK
  8. DAYS and NETWORKDAYS
  9. RANK
  10. SUMPRODUCT

Before we get started…

In order to show the power of data analysis in Excel, we need some data to play around with and graph. I am using the Customer Personality Analysis dataset from Kaggle in these examples. You can download it to follow along.

1. Pivot tables and pivot charts

One of my favorite features in Excel is pivot charts and, as a close follow-up feature, pivot tables. Pivot charts visualize the data expressed in a pivot table, giving us insight at a glance. Pivot tables provide a simple approach to reformatting columns and rows, transforming them into groupings, statistics, or summaries. We can…

--

--