Microsoft Excel logo on a green background

No matter what you use Excel for, whether business financials or a personal budget, the tool’s data analysis features can help you make sense of your data. Here are several Excel features for data analysis and how they can help.

Quick Analysis for Helpful Tools

When you aren’t quite sure of the best way to display your data or if you’re a new Excel user, the Quick Analysis feature is essential. With it, you simply select your data and view various analysis tools provided by Excel.

RELATED: How to Use Excel's "Quick Analysis" to Visualize Data

Select the data you want to analyze. You’ll see a small button appear in the bottom corner of the selected cells. Click this Quick Analysis button, and you’ll see several options to review.

Quick Analysis button in Excel

Choose “Formatting” to look through ways to use conditional formatting. You can also select “Charts” to see the graphs Excel recommends for the data, “Totals” for calculations using functions and formulas, “Tables” to create a table or pivot table, and “Sparklines” to insert tiny charts for your data.

Quick Analysis tools

After you pick a tool, hover your cursor over the options to see previews. For example, if you select Formatting, hover your cursor over Data Bars, Color Scale, Icon Set, or another option to see how your data would look.

Quick Analysis tool preview

Simply choose the tool you want and you’re in business.

Analyze Data for Asking Questions

Another helpful built-in feature in Excel is the Analyze Data tool. With it, you can ask questions about your data and see suggested questions and answers. You can also quickly insert items like charts and tables.

Select your data, go to the Home tab, and click “Analyze Data” in the Analysis section of the ribbon.

Analyze Data on the Home tab

You’ll see a sidebar open on the right. At the top, pop a question into the search box. Alternatively, you can choose a question in the Not Sure What to Ask section or scroll through the sidebar for recommendations.

Analyze Data sidebar

If you see a table or chart in the list you want to use, select “Insert Chart” or “Insert PivotTable” to add it to your sheet with a click.

Analyze Data sidebar with insert options

Charts and Graphs for Visual Analysis

As mentioned above, charts make great visual analysis tools. Luckily, Excel offers many types of graphs and charts, each with robust customization options.

RELATED: How to Choose a Chart to Fit Your Data in Microsoft Excel

Select your data and go to the Insert tab. You can choose “Recommended Charts” in the Charts section of the ribbon to see which graph Excel believes fits your data best.

Recommended Charts on the Insert tab

You can also pick “All Charts” in the Recommended Charts window or choose a specific chart type in that same section of the ribbon if you know which kind of visual you want.

All Charts in the Recommended Charts window

When you pick a chart type, you’ll see it appear in your sheet with your data already inserted. From there, you can select the graph and use the Chart Design tab, Format Chart Area sidebar, and chart buttons (Windows only) to customize the chart and the data in it.

Chart Design tab and customization buttons on Windows

For more, check out our how-tos for making graphs in Excel. We can help you create a pie chartwaterfall chartfunnel chart, combo chart, and more.

Sort and Filter for Easier Viewing

When you want to analyze particular data in your Excel sheet, the sort and filter options help you accomplish this.

You may have columns of data that you want to sort alphabetically, numerically, by color, or using a particular value.

Select the data, go to the Home tab, and open the Sort & Filter drop-down menu. You can choose a quick sort option or “Custom Sort” to pick a certain value.

Sort options in Excel

Along with sorting the data, you can use filters to see only the data you need at the time. Select the data, open the same Sort & Filter menu, and pick “Filter.”

Filter in Excel

You’ll see filter buttons at the top of each column. Select a button to filter that column by color or number. You can also use the checkboxes at the bottom of the pop-up window.

Filter options in Excel

To clear a filter when you finish, select the filter button and choose “Clear Filter.” To turn off filtering altogether, return to the Sort & Filter menu on the Home tab and deselect “Filter.”

Functions for Creating Formulas

Excel’s functions are fantastic tools for creating formulas to manipulate, change, convert, combine, split, and perform many more actions with your data. When it comes to analyzing data, here are just a handful of functions that can come in handy.

IF and IFS

The IF and IFS functions are invaluable in Excel. You can perform a test and return a true or false result based on criteria. IFS lets you use multiple conditions. IF and IFS are also useful when you combine them with other functions.


The COUNTIF and COUNTIFS functions count the number of cells containing data that meets certain criteria. COUNTIFS lets you use multiple conditions.


The SUMIF and SUMIFS math functions add values in cells based on criteria. SUMIFS lets you use multiple conditions.


The XLOOKUP, VLOOKUP, and HLOOKUP functions help you locate specific data in your sheet. Use XLOOKUP to find data in any direction, VLOOKUP to find data vertically, or HLOOKUP to find data horizontally. XLOOKUP is the most versatile of the three and is an extremely helpful function.


With the UNIQUE lookup function, you can obtain a list of only the unique values from your data set.

Conditional Formatting for Spotting Data Fast

Conditional formatting is a favorite feature for sure. Once you set it up, you can spot specific data quickly, making data analysis go faster.

Select your data, go to the Home tab, and click the Conditional Formatting drop-down menu. You’ll see several ways to format your data, such as highlighting cells greater or less than a specific value or showing the top or bottom 10 items.

Conditional Formatting highlight options

You can also use conditional formatting to find duplicate data, insert color scales for things like heat maps, create data bars for color indicators, and use icon sets for handy visuals like shapes and arrows.

Additionally, you can create a custom rule, apply more than one rule at a time, and clear rules you no longer want.

Pivot Tables for Complex Data

One of the most powerful Excel tools for data analysis is the pivot table. With it, you can arrange, group, summarize, and calculate data using an interactive table.

RELATED: How to Use Pivot Tables to Analyze Excel Data

Select the data you want to add to a pivot table and head to the Insert tab. Similar to charts, you can review Excel’s suggestions by selecting “Recommended PivotTables” in the Tables section of the ribbon. Alternatively, you can create one from scratch by clicking the PivotTable button in that same section.

Insert pivot table options in Excel

You’ll then see a placeholder added to your workbook for the pivot table. On the right, use the PivotTable Fields sidebar to customize the contents of the table.

Use the checkboxes to choose which data to include and then the areas below to apply filters and designate the rows and columns. You can also use the PivotTable Analyze tab.

Pivot table customization sidebar

Because pivot tables can be a little intimidating when you get started, check out our complete tutorial for creating a pivot table in Excel.

Hopefully, one or more of these Excel data analysis features will help you with your next review or evaluation task.

Profile Photo for Sandy Writtenhouse Sandy Writtenhouse
With her B.S. in Information Technology, Sandy worked for many years in the IT industry as a Project Manager, Department Manager, and PMO Lead. She learned how technology can enrich both professional and personal lives by using the right tools. And, she has shared those suggestions and how-tos on many websites over time. With thousands of articles under her belt, Sandy strives to help others use technology to their advantage.
Read Full Bio »