Data analysis in Excel starts in the Data tab on the ribbon, where you'll find sorting, filtering, and pivot tables — the three tools that handle most real work
Excel's data analysis tools are not hidden in menus or buried in settings. They sit in plain sight on the Data tab at the top of the screen, right next to Home, Insert, and Formulas. If you have a spreadsheet open with actual data in it — rows and columns with numbers, dates, or text — click the Data tab and you will see buttons for Sort, Filter, Subtotals, and Pivot Table. These four buttons do the work that makes data analysis worth doing: they let you reorganize information without retyping it, find patterns without reading every cell, and summarize thousands of rows into a single table.
The reason these tools matter is that they work on the data you already have. You do not need to learn formulas or create new columns. You select your data, click a button, and Excel rearranges or summarizes it for you. A pivot table, for example, can take a year of daily sales records and show you total sales by product and by month in seconds. Sorting and filtering let you answer questions like "which customers spent the most" or "which dates had zero orders" without writing anything.
Key Takeaways
- The Data tab holds Sort, Filter, Subtotals, and Pivot Table — the four tools that handle most data analysis work in Excel.
- Sorting and filtering work on the data you already have without changing the original spreadsheet, so you can experiment without risk.
- A pivot table summarizes large datasets by grouping rows and columns the way you choose, turning hundreds of records into a readable summary.
- Before using any data analysis tool, select the entire data range including headers, or Excel will not know where your data ends.
How to sort and filter your data
Sorting and filtering are the fastest way to answer straightforward questions about your data. Sorting rearranges rows so the data you care about appears first. Filtering hides rows that do not match what you are looking for, so you see only the data that matters.
To sort: select all your data including the header row (the row with column names), then click Data > Sort. A dialog box opens. Choose which column to sort by — if you want sales from highest to lowest, pick the Sales column and choose "Largest to Smallest". Click OK. Excel rearranges the entire dataset so the rows stay together; if you sort by sales amount, the customer name, date, and product in each row move with it. To sort by more than one column — for example, by product first, then by date within each product — use the "Add Level" button in the same dialog.
To filter: select your data and click Data > Filter. Excel adds dropdown arrows to each column header. Click the arrow in any column — say, the Product column — and you see a list of every unique value in that column. Uncheck the products you do not want to see, and Excel hides those rows. The rows are not deleted; they are just hidden until you remove the filter. You can filter multiple columns at once. If you filter Product to show only "Widget" and Region to show only "North", you see only Widget sales in the North region.
What a pivot table does and when to use one
A pivot table is a summary table that groups your data the way you choose. Instead of looking at thousands of individual sales records, a pivot table can show you total sales by product, or by month, or by both at once. It is the tool to reach for when you have a large dataset and need to see patterns or totals.
To create a pivot table: select your data including headers, then click Data > Pivot Table (or Insert > Pivot Table, depending on your Excel version). Excel opens a dialog asking where you want the pivot table to appear — usually a new sheet is cleaner. Click OK. A new window appears with four boxes: Filters, Columns, Rows, and Values. Drag column headers from your original data into these boxes. If you drag "Product" into Rows and "Sales Amount" into Values, the pivot table shows each product with its total sales. If you also drag "Month" into Columns, you get a table with products down the left side and months across the top, with sales amounts filling the grid. You can rearrange these boxes as many times as you want; the pivot table updates when ready.
Pivot tables are especially useful when you have date data. If you drag a date column into Rows, Excel automatically groups dates by month or year, so you can see sales trends over time without manually creating date ranges. You can also add filters to a pivot table — drag a column into the Filters box — so you can show only certain products or regions without rebuilding the whole table.
Using Subtotals to add running totals to sorted data
The Subtotals tool is less common than pivot tables, but it is useful when you want to keep your data in its original layout and just add summary rows. Subtotals inserts rows that calculate sums, averages, or counts at each change in a column you specify.
To use Subtotals: first, sort your data by the column you want to group by — if you want subtotals for each product, sort by Product. Then select all your data and click Data > Subtotals. A dialog opens. Choose the column to group by (Product), choose what calculation you want (Sum, Average, Count, etc.), and choose which columns to calculate on (usually your numeric columns like Sales Amount). Click OK. Excel inserts new rows showing the subtotal for each group, and a grand total at the bottom. You can collapse the view to show only the subtotals, hiding the detail rows, by clicking the outline buttons on the left side of the spreadsheet.
The Data Analysis ToolPak for statistical functions
Excel also has an add-in called the Data Analysis ToolPak that provides statistical tools like regression, correlation, and histograms. This is not visible by default; you have to turn it on. It is useful if you need to do statistical analysis — for example, testing whether two datasets are significantly different, or finding the relationship between two variables.
To enable it: click File > Options > Add-ins. At the bottom, find the dropdown that says "Manage" and select "Excel Add-ins", then click Go. A dialog opens. Check the box next to "Analysis ToolPak" and click OK. Now when you click the Data tab, you will see a new button called "Data Analysis". Click it to see options like Descriptive Statistics, Regression, T-Test, and others. Each opens a dialog where you select your data and choose parameters. These tools are more technical than Sort or Pivot Table, and they assume you understand what the output means — but if you need them, they are there.
How to select your data correctly before analyzing it
The most common mistake when using data analysis tools is selecting the wrong range. Excel needs to know where your data starts and ends, and it needs to see the header row so it knows what each column represents.
The safest approach: click the first cell of your data (usually the top-left corner, where the first column header is), then press Ctrl+Shift+End. Excel selects from that cell to the last cell with data in the used range. This usually gets it right. If your data has blank rows or columns in the middle, this method may select too much, so you may need to manually select the exact range instead. Click the first cell, hold Shift, and click the last cell of your data. Or type the range directly — if your data is in columns A through D and rows 1 through 500, you can type A1:D500 in the Name Box (the box to the left of the formula bar that usually shows the current cell).
Always include the header row. Excel uses the headers to label the output of your analysis. If you sort without headers, Excel treats the first row of data as headers and sorts it along with everything else, which scrambles your data.
Frequently Asked Questions
Can I undo a sort or filter if I change my mind?
Yes. Sorting and filtering do not change your data permanently. Press Ctrl+Z to undo a sort. To remove a filter, click Data > Filter again to toggle it off, or click the dropdown arrow in a column header and select "Clear Filter". The hidden rows reappear.
What is the difference between a pivot table and Subtotals?
Subtotals keeps your data in place and adds summary rows. A pivot table creates a separate summary table. Use Subtotals if you want to keep the original layout; use a pivot table if you want to reorganize the data completely or if you need to group by multiple columns at once.
Do I have to use the Data Analysis ToolPak, or can I do statistics with formulas?
You can do most statistics with formulas — AVERAGE, STDEV, CORREL, and others exist as functions. The ToolPak is faster if you need multiple statistics at once, but formulas give you more control over what gets calculated and where the result appears.
What happens if my data has blank cells or rows?
Blank cells can confuse sorting and filtering. A blank cell in a column you are sorting by will usually move to the top or bottom. Blank rows in the middle of your data can cause sorting to stop early. Clean your data first — delete truly empty rows, and fill in any cells that should have a value.
Can I create a pivot table from data in multiple sheets?
Not directly. You need to combine the data into a single sheet first, or use a pivot table based on an external data source. For most situations, copying all the data into one sheet is the simplest approach.