5 Excel Data Analysis Tools You Need to Know in 2022

With so many data analysis tools, why use Excel? The answer is simple: Excel is the most used spreadsheet in the world. It is also easy to use and completely free.

But beyond that, it is a powerful tool that can analyze data, save a lot of time, and avoid monotony and errors.

This five-part tutorial will show you how to use Excel’s business intelligence tools such as Power Query, Power Pivot, and Power Maps to analyze data. (Also read: What skills are required to get a job in data analysis?)

Let’s start!

1. Use Power Query to collect data

Before you can analyze data in Excel, or anywhere, you need to put it together.

Fortunately, in Excel there are several options for obtaining and organizing data. With Power Query, you can collect data from different sources, including databases, CSV, Xl, or XML files. (Also read: 7 Reasons You Need a Database Management System.)

Once you get the data from these sources, you can shape it according to your needs. Power Query also lets you consolidate data from different files into a single spreadsheet.

For example, if you have 100 different Excel spreadsheets, you can consolidate them into one workbook, which saves you time.

You can also import data from other applications into Excel. For example, you can get data from Salesforce or Google Analytics into Excel and then manipulate that data as you see fit.

2. Clean your data with the automatic filter

Once you’ve gathered your data, the next step is to clean it up.

There are many ways to clean your data, such as:

Clean formatting

Sometimes you will have data that seems irrelevant. In this case, you can use Excel’s “AutoFilter” to find the values ​​you need.

For example, suppose you have a list of names with birthdates next to each. Typically, you’ll format the date of birth as a number, but for this example, we’ll assume that dates of birth are formatted “YYYY-MM-DD” instead.

Here’s how to clean up formatting in Excel:

  1. Open the “Data” tab.
  2. Select “AutoFilter”.
  3. From the drop-down menu, select “Custom” and enter this formula: =LEFT(A2,4). This formula will extract the “4” digits from the date of birth.
  4. Select “AutoFilter” again (you can just press “Ctrl+F”). This time, choose “Date” as the category and “Custom” as the criterion.
  5. Enter this formula: =RIGHT(A2, 2). This will extract the “2” digits from the date of birth.

This formula will filter out all birthdates that don’t have four or two characters.

Remove duplicate rows in Excel

In some cases, you will have duplicate rows in your list. Here’s how to remove them:

  1. Open the “Data” tab.
  2. Select “Remove Duplicates” from the drop-down menu.
  3. Check the “Single” box.
  4. Click OK.” The “Remove Duplicates” dialog box will appear.
  5. Check all options.
  6. Click OK.”

This will allow you to remove all duplicate rows.

Delete Blank Rows in Excel

You may have blank rows in your Excel worksheet. Here’s how to get rid of it:

  1. Open the “Data” tab.
  2. Select “Remove blank rows” from the drop-down menu.
  3. Check the “Single” box.
  4. Click OK.”

3. Explore your data using Power Pivot

The most powerful way to explore your data is to use Excel’s “Power Pivot” tool.

With Power Pivot, you can analyze your data by creating tables. A Power Pivot table allows you to visualize your data in different ways, such as:

Create a table

  1. Open the “Data” tab.
  2. Select “Insert”.
  3. Click “New”. Once done, a new Power Pivot window will appear.
  4. Click on “Table”.
  5. Click “Add New”. This will open another window.
  6. Enter a name for your table.
  7. Uncheck “Selected Items”.
  8. Click the “OK” button. A new table will be created and you can drag columns and rows to arrange the table structure.
  9. Once you have configured your table, click “OK” to close the window.
  10. Go to “File” > “Close and load”. This will load your newly created table.

Create charts and pivot tables

After creating a table, you can create charts and pivot tables to analyze your data. Here’s how:

Pie charts

  1. Go to “Insert” → “Chart” → “Chart Types” → “Circle Chart”. A pie chart will be created.

PivotTables

  1. Go to “Insert” → “Pivot Table”.
  2. Select “Source” → “Get Data” → “From Table” → “Next” → “Select Table” → “OK”. This will create a pivot table.
  3. To create another PivotTable, select “Source” → “Get Data” → “From Table” (this time, select the desired table) → “Next” → “Select Table” → “OK”. This will create another pivot table.

4. Use Power Maps to add advanced analytics capabilities (optional)

At this point, you have explored your data and created charts and pivot tables. Now is the time to add advanced analytical capabilities. (Also read: 9 Reasons to Take a Data Science Course.)

You can do this with Excel’s “Power Maps” tool.

Power Maps allows you to visualize your data in 3D: you can rotate, zoom, move and link different data together.

To create a power map:

  1. Go to “Insert” → “Map” → “New Map”. Once you have done this, a new window will appear. You can drag the “Map” section to the right in this window.
  2. Select the “Data” section and drag it to the right.
  3. Select “Add” → “New Source” → “From Database”. This will open a new window.
  4. Choose a database and click “OK”.
  5. Select “Add” → “New Data Source” → “From File” → “Next” → “Choose File” → “Next” → “Choose File” → “Next” → “Choose File” → “Next “. This will open a new window.
  6. Select a file and click “OK”.
  7. In the “Data” section, select the “Fields” section. Drag the “Fields” section to the right and select the “Country” field.
  8. Still in the “Fields” section, select the “Region” field. In the right panel, click “OK”.

This will create a map. You can change the “Legend” to “Map”.

How to change the map style

  1. Click on “Style”. This will open a new window.

Here you can change attributes like map type, map title, center, zoom, pan, rotate, size, color, transparency, opacity, background , border, lighting, shadow and direction.

5. Share your Excel report on SharePoint

Finally, you can share (or “publish”) your report and share it with others. Here’s how:

  1. Go to “File” → “Publish”. From there, you can publish your report in different formats. For example, you can publish your report as a PDF, Microsoft Word, or Excel document.

You can also share your report on your SharePoint site.

To do this, click on “Other web locations”. (This will open a new window). From there, you can choose which site you want to publish your report on.

Finally, you can save your report to your local computer. To do this, click “Save”.

Summary

Data analysis (especially with Excel) is incredibly powerful.

With Excel, you can clean and explore data and add advanced data analysis features. Together, these functions help improve decision-making, increase productivity and drive business success. (Also read: Why network analysis is vital for the new economy.)