5 Excel Data Analysis Tools to Know in 2022

With so many data analytics tools out there, why use Excel? The answer is simple: Excel is the world’s most widely used spreadsheet program. It’s also easy to use and completely free.

But beyond that, it’s a powerful tool that can analyze data, save significant amounts of time and prevent monotony and errors.

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

Let’s get started!

1. Use Power Query to Gather Data

Before you can analyze data in Excel—or anywhere—you need to gather it.

Luckily, in Excel, there are several options to get and organize data. With Power Query, you can gather data from different sources, including databases, CSVs, Xl or XML files. (Also read: 7 Reasons Why You Need a Database Management System.)

Once you get the data from these sources, you can shape them to suit your needs. Power Query also allows you to consolidate the data from different files into a single worksheet.

For instance, if you have 100 different Excel spreadsheets, you can consolidate them into a single workbook, which saves you time.

You can bring data from other applications into Excel as well. For example, you can get data from Salesforce or Google Analytics into Excel, then manipulate that data however you like.

2. Clean Your Data With Auto Filter

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

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

Clean the Formatting

Sometimes, you’ll have data that appears out of place. In that case, you can use Excel’s “Auto Filter” to find the values you need.

For example, let’s say you have a list of names with birthdates next to each. Typically, you’d format the birthdate as a number, but for this example we’ll pretend the birthdates are formatted “YYYY-MM-DD” instead.

Here’s how to clean the formatting in Excel:

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

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

Remove Duplicate Rows in Excel

In some cases, you’ll 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.
3. Tick the “Unique” box.
4. Click “OK.” The “Remove Duplicates” dialog box will appear.
5. Check all the options.
6. Click “OK.”

This will allow you to remove all duplicate rows.

Remove Empty Rows in Excel

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

1. Open the “Data” tab.
2. Select “Remove Empty Rows” from the drop-down.
3. Tick the “Unique” box.
4. Click “OK.”

3. Explore Your Data Using Power Pivot

The most powerful way to explore your data is by using 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, like:

Create a Table

1. Open the “Data” tab.
2. Select “Insert.”
3. Click “New.” Once you do that, 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. Deselect “Selected Items.”
8. Click the “OK” button. A new table will be created and you can drag columns and rows to arrange the table’s structure.
9. Once you have your table set up, click “OK” to close the window.
10. Go to “File” > “Close & Load”. This will load your newly created table.

Create Charts and PivotTables

After you’ve created a table, you can create charts and PivotTables to analyze your data. Here’s how:

Pie Charts

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

PivotTables

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

4. Use Power Maps to Add Advanced Analytic Capabilities (Optional)

At this point, you’ve explored your data and have created charts and PivotTables. Now, it’s time to add some advanced analytic capabilities. (Also read: 9 Reasons to Go for 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, pan and link different data together.

To create a power map:

1. Go to “Insert” → “Map” → “New Map.” Once you do that, 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 Edit the Map Style

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

Here, you can edit attributes such as the map type, map title, center, zoom, pan, rotation, size, color, transparency, opacity, background, border, lighting, shadow and direction.

5. Share Your Excel Report to 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 “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 analytics capabilities. Together, these functions help improve decision-making, increase productivity and drive business success. (Also read: Why Network Analytics are Vital for the New Economy.)