Start with a small, well-organized list, then use formulas, filters, lookups, and a PivotTable to answer useful questions about it. This hands-on Excel crash course walks through that sequence; exact feature availability can vary by Excel edition and platform.
1. Get oriented: cells, rows, columns, and ranges
Excel worksheets are grids. Columns have letter labels, rows have number labels, and each cell is identified by its column letter followed by its row number. For example, B3 is in column B and row 3. A range such as D2:D20 means the cells from D2 through D20.
As an Amazon Associate I earn from qualifying purchases.
For practice, make a small order list with these headings in row 1: Date, Product, Quantity, Unit Price, Customer ID. Add a few sample records beneath them. Keep one record per row and one kind of information per column.
Select an empty cell beside the first record and enter =C2*D2, assuming Quantity is in column C and Unit Price is in column D. Press Enter. The result is that order’s total. Because the formula refers to cells, changing a quantity or price updates the calculation. Microsoft describes formulas as beginning with = and using references, operators, constants, and functions as needed; see its formula overview.
#1 Best Overall
2. Calculate totals and averages
Functions are built-in formulas for common calculations. Add a Total heading and calculate each row’s total, then use these functions below the list or in a separate summary area:
=SUM(E2:E10)adds the values in E2 through E10.=COUNT(E2:E10)counts cells containing numbers in that range.=AVERAGE(E2:E10)returns the arithmetic mean of the numeric values.
Adjust the range to match your data. A useful habit is to place changeable assumptions—such as a tax rate—in their own cells and reference them in formulas, rather than embedding the value in each formula. For instance, if the tax rate is in H2, a formula can refer to H2; changing that cell then updates formulas that use it.
Microsoft provides a downloadable formula tutorial workbook that practices common formulas, including SUM, COUNT, AVERAGE, and VLOOKUP: Overview of formulas in Excel.
3. Turn the list into a table, then sort and filter
Once each column has a clear heading and each row represents one order, convert the list into an Excel table. Select a cell in the data and use Insert > Table; confirm that the table has headers. The table gives the data a consistent structure that is easier to sort, filter, and use as the source for summaries.
Rank #3
Use the filter arrows in the heading row to show only records for one product or date range. Sort a date or total column to bring older, newer, smaller, or larger values together. Filtering hides records that do not meet the condition; it does not delete them. Microsoft groups tables, sorting, filtering, charts, PivotTables, and related capabilities in its data import and analysis help.
4. Match related information with a lookup
Lookups find a value in one range and return related information from another. A practical example is matching a Customer ID on an order to a separate customer list that contains the customer’s name. Use a unique ID as the match key; names alone may not be unique.
Use XLOOKUP where your Excel version supports it
For an ID in F2, with IDs in J2:J20 and names in K2:K20, try =XLOOKUP(F2,J2:J20,K2:K20,"No match"). It searches for the ID and returns the corresponding name; the fourth argument supplies a readable result when no match is found. Microsoft documents XLOOKUP as able to search in either direction and to use exact matches by default. Its formula overview lists Microsoft 365, Excel 2024, Excel 2021, Excel 2019, and Excel 2016 among applicable products, but feature support can differ by edition and platform. Check the overview for your environment before relying on it.
Recommended Free Tools
Recognize VLOOKUP in older workbooks
VLOOKUP is a common older lookup workflow and is included in Microsoft’s formula tutorial workbook. It searches the first column of a selected table range and returns a value from a column to its right, so the layout matters. If a workbook uses VLOOKUP, understand its lookup range and match settings before replacing it. Microsoft’s Excel Blog also presents formulas and examples in its formula guide.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.5. Summarize the orders with a PivotTable
A PivotTable rearranges and summarizes a list without requiring you to write a separate formula for each group. Start by selecting a cell in the order table, then choose Insert > PivotTable and confirm the table or range. In the field list, drag Product to Rows and Total to Values. Excel will summarize totals by product; check the Values setting if it counts records instead of summing the amounts.
To answer a different question, put Date in Rows and group it by month if that option is available in your version, or use a date field already prepared as a month. Add Customer ID or another field to Filters when you need to focus the summary. PivotTables are most useful when the source list has consistent headings and one record per row.
6. Make a chart from the summary
Select a cell in the PivotTable and use Insert > PivotChart if available, or choose a chart type from Insert > Charts. A column chart can make product totals easy to compare; a line chart is more suitable for a sequence such as monthly totals. Give the chart a descriptive title and check that its categories and values match the question you intended to answer.
Microsoft’s Excel help and learning hub covers charts, PivotTables, and PivotCharts. The broader analysis toolkit also includes data models and Power Query; Microsoft describes Power Query as a way to connect to multiple data sources and shape data in Query Editor. Those are useful next steps, not prerequisites for summarizing a small list.
7. Practice the sequence on your own data
- Create a small list with one record per row, clear headings, and a unique ID where records need to be matched.
- Add a calculated total with a formula that refers to quantity and price cells.
- Use SUM, COUNT, or AVERAGE to answer one simple question about the values.
- Convert the range to a table, then filter it to a product or date range and sort a numeric column.
- Use a lookup to retrieve related information by unique ID, and decide how a missing match should appear.
- Build a PivotTable that answers a specific question, such as total sales by product, and create a chart if it clarifies the comparison.
If you want a longer guided path after these exercises, the Microsoft Excel for Beginners Professional Certificate course listing includes formulas, tables, lookups, PivotTables, charts, and dashboards. It is one optional course route, not a requirement for learning the skills above.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
