To analyze data in Excel, clean the raw data, turn it into an Excel Table, summarize it with formulas and PivotTables, chart the summaries, and check the results before you share them. The built-in Analyze Data button on the Home tab can give you a fast first look. The steps below cover the full path from a messy export to a report you can trust.
Most bad Excel analysis starts with bad input. One author who teaches Excel for reporting calls dirty data the number-one reason analysis goes wrong and says to clean it before you touch a PivotTable or formula. Microsoft's guidance points the same way: use tabular data organized in columns, with a single row of unique, non-blank column labels.
A practical cleaning checklist, based on that same six-step routine:
When the data is clean, click any cell in it and press Ctrl+T. An Excel Table expands automatically when you add rows, and every PivotTable or formula that points to it picks up the new data.
Before you calculate anything, look at the data. Sort by amount, date or category. Filter to one region or one month. Add conditional formatting to highlight the top 10 values or anything below a target.
This step answers a question that sounds simple: what does this dataset contain? You will often find an outlier that is a typo, a category spelled two ways or a date range that is shorter than you assumed. Finding those now is cheaper than finding them after a report goes out.
The University of Minnesota's hiring-data exercise is a good model of this stage. In a project built on New York City job postings, students create derived columns such as Midpoint Salary, Salary Range Width and Days Until Closing. They then sort, filter and apply conditional formatting and IF before building any summary. The author's point is that each calculation shapes what you are able to notice in the data.
Formulas give you specific answers in specific cells. These are the ones analysts use most.
| Question | Formula to learn |
| Total for one category | SUMIF |
| Total for several conditions | SUMIFS |
| How many rows meet a rule | COUNTIF, COUNTIFS |
| Average by category | AVERAGEIFS |
| Pull a value from another table | XLOOKUP, or INDEX with MATCH |
| Handle errors cleanly | IFERROR |
| Work with dates | DATE, TEXT |
Here is a worked example. Say column C holds the amount, column B the category and column A the date. To total fuel spending in January 2018, Vertex42 shows a formula like this:
=SUMIFS(amount_range, category_range, "Fuel", date_range, ">=1/1/2018", date_range, "<=1/31/2018")
The Vertex42 guide describes SUMIFS as especially useful in budgets and account registers for summarizing expenses by category between two dates. In your own file, replace the range names with real references. If the data is in a Table, use structured names such as Sales[Amount] so the formula grows with the data.
Practice matters here more than reading. Typing a SUMIFS with the wrong range order and seeing it fail teaches you more than watching someone else get it right. Our Excel practice exercises let you enter formulas and get instant feedback in an Excel-like environment, with nothing to install.
A PivotTable is the fastest way to answer "how much by what?" Microsoft describes it as a tool to calculate, summarize and analyze data that shows comparisons, patterns and trends.
The workflow:
Slicers are clickable filters. Select the PivotTable, choose Insert, Slicer, and pick a field. For date fields, a Timeline lets you filter by month, quarter or year. To make one slicer control several PivotTables, use the Slicer tab, then Report Connections, and check each PivotTable. The iTechGuides walkthrough notes that the PivotTables must share the same underlying Table, range or compatible Data Model, and that you should test every chart after connecting.
If you are asking where the Analyze Data option is, it sits on the Home tab. Per Microsoft's documentation, you select a cell in your data range and click the button. A task pane opens with visual summaries, and you can type a question in plain language to get a table, chart or PivotTable that you can insert into the workbook. Insight types include Rank, Trend, Outlier and Majority.
Two practical limits from the same page: the feature is available to Microsoft 365 subscribers in a set list of languages, and it does not support datasets over 1.5 million cells. If you do not see the button, your version of Excel may not include it. Everything in Steps 3 and 4 works in any modern desktop version.
Use Analyze Data as a way to generate ideas, then rebuild the useful ones as PivotTables you control. That way you know exactly what each number means.
When you need descriptive statistics, correlations, t-tests or regression, the Analysis ToolPak is the built-in option. Microsoft explains that it is an add-in you load from File, Options, Add-Ins: choose Excel Add-ins in the Manage box, click Go, and check Analysis ToolPak. After that, a Data Analysis button appears on the Data tab. You provide the input range and parameters, and it writes the results to an output table.
One formatting rule trips people up in regression. OpenStax's business statistics text explains that the X variables must be in a single contiguous block. If your Y column sits between two X columns, rearrange the columns first.
A chart should answer one question. Build it from a PivotTable (a PivotChart) so it updates with the data and responds to your slicers.
A clean dashboard layout, following the method in the research:
If you rebuild the same report every week from a CSV export, stop cleaning by hand. Microsoft explains that Power Query connects to multiple data sources and lets you shape and transform data in the Query Editor. The typical flow is Data, Get Data, fix column types, remove blanks, standardize text, then Close & Load to a Table or the Data Model. Next week you click Refresh instead of repeating every step. Microsoft also offers a tutorial on importing data and building a Data Model for combining related tables.
Once you can build reusable analysis workbooks, some people go further and package them as products. If that interests you, this guide on selling spreadsheet templates online covers the US side of it.
1. Salary comparison. Take a list of job postings. Add a Midpoint Salary column (average of minimum and maximum) and a Range Width column. Build a PivotTable of average midpoint by career level. This mirrors the Minnesota project.
2. Monthly spending by category. Convert a bank export to a Table. Use SUMIFS for each category and month, then check the total against a PivotTable of the same data. If the two disagree, you have found a data problem.
3. Sales by region and month. Build a PivotTable with Region in Rows, Month in Columns and Sales in Values. Add a slicer for Product. Then chart it.
If you work with accounting data, our 5-day accounting spreadsheet course applies the same ideas to ledgers and reports.
Excel will calculate whatever you give it. These checks catch problems before your manager does.
> We built LogicExcel around one belief: nobody learns Excel analysis by watching someone else click. Video tutorials feel productive, but when you open your own spreadsheet, the muscle memory is not there.
>
> That is why every exercise on our platform asks you to type the formula yourself and gives instant feedback in an Excel-like environment. When you make a mistake, we explain it, because the error is where the learning happens. There is nothing to install, so the only thing between you and practice is opening the page.
>
> For analysts, managers and career changers, the goal is not to memorize a menu. It is to look at a business question, know which tool answers it, and trust the number you get. Short, repeated practice on SUMIFS, lookups, PivotTables and charts builds that confidence faster than any long course.
>
> Thomas Houza
Reading about SUMIFS is easy. Writing it correctly under time pressure is a skill. LogicExcel is a free platform for interactive Excel and SQL practice, with guides and tutorials on formulas, charts, pivot tables, conditional formatting and data validation.
Start with the 95 Excel exercises, move through the Excel I to IV lessons when you want a structured path, or try the free Excel test for interviews and assessments if you are preparing for a job screen. If you want a credential-focused route, see our Microsoft Excel certification prep. The Excel test and the first two lessons are free; everything else comes with All-Access. You can also browse the full site at LogicExcel.
It is on the Home tab, at the right end of the ribbon. Select any cell inside your data range and click Analyze Data to open the task pane. If you cannot see it, your Excel version may not include the feature, which Microsoft lists as available to Microsoft 365 subscribers.
For most business questions, PivotTables are the best starting point because they total, count and average by any category in seconds. Add SUMIFS and COUNTIFS when you need a result in a specific cell. Use the Analysis ToolPak when you need statistical tests such as regression or t-tests.
No. Sorting, filtering, formulas, PivotTables and charts cover most reporting work without it. The ToolPak is for statistical analysis. You turn it on under File, Options, Add-Ins, and the Data Analysis button then appears on the Data tab.
Work on realistic datasets and type the formulas yourself. Start with SUMIFS, COUNTIFS and lookups, then build PivotTables from a sample file. LogicExcel offers interactive exercises with instant feedback, the first two lessons free, so you can check each answer as you go.
Guides people read next.
SQL data types explained: numeric, text, date and time, Boolean and binary types, with examples, MySQL, SQL Server, Oracle and PostgreSQL differences, and how to choose.
15 min
Clean messy Excel data with Power Query: fix types, trim text, remove errors, split columns and merge queries, with repeatable steps you build once.
16 min
Unlock your Excel potential with the best excel sample data for practice. Discover free datasets across various industries to enhance your skills.
10 min