Sort and filter data, write smarter formulas, build charts, and create pivot tables.
This guide builds on the Excel Basics post. These features are what separate someone who can use Excel from someone who can get things done quickly in it.
A note on versions: Microsoft updates these apps constantly, and the exact wording, button placement and menu options can differ between versions, update channels and license types (perpetual vs Microsoft 365). If something on your screen does not match exactly what you see here, the feature is almost always still there, just in a slightly different spot or under a slightly different name. Use these guides as a map, not a pixel-perfect match.
Sorting and Filtering
Sorting: Select any cell in the column you want to sort by. Go to Data, then Sort A to Z or Sort Z to A. For numbers it is Sort Smallest to Largest or Largest to Smallest.
For multi-column sorting, go to Data, Sort, and add multiple sort levels. Useful when you want to sort by last name, then by first name.
Filtering: Select any cell in your data and go to Data, Filter. Dropdown arrows appear in the header row. Click a dropdown to filter by specific values, by text, or by number conditions.
To clear all filters, go to Data, Clear.
Useful Formulas
IF - return different values based on a condition:
=IF(A1>100,"Over budget","On track")
If A1 is greater than 100, the cell shows “Over budget”. Otherwise it shows “On track”.
COUNTIF - count cells that meet a condition:
=COUNTIF(B2:B20,"Yes")
Counts how many cells in that range contain the word “Yes”.
XLOOKUP - find a value and return something from another column:
=XLOOKUP(E2,A2:A100,B2:B100)
Looks for the value in E2 within column A, and returns the corresponding value from column B.
XLOOKUP is available in Microsoft 365 and Excel 2021+. If you have an older version, use VLOOKUP instead.
Charts
Select the data you want to chart, including headers. Go to Insert, Charts, and pick a chart type. See create a chart from start to finish.
Common types:
- Column or Bar - comparing values across categories
- Line - showing change over time
- Pie - showing parts of a whole (keep slices to 5 or fewer)
- Scatter - showing the relationship between two sets of numbers
After inserting, click the chart to activate Chart Design and Format tabs. Use Chart Design to change the chart type, switch rows and columns, and apply styles. Use the + button that appears next to the chart to add or remove elements like axis titles and data labels.
Conditional Formatting
Conditional formatting changes a cell’s appearance based on its value.
Select a range, go to Home, Conditional Formatting, and pick a rule type:
- Highlight Cell Rules - color cells above a value, below a value, equal to something, containing specific text, or containing duplicate values
- Data Bars - adds a bar inside the cell proportional to the value
- Color Scales - gradient colors from low to high across the range
Pivot Tables
Pivot tables summarize large sets of data without formulas.
Click any cell inside your data. Go to Insert, PivotTable. Accept the defaults and click OK. A new sheet opens with the PivotTable field list on the right.
Drag fields into the four areas:
- Rows - what you want as row labels
- Columns - what you want broken out as columns
- Values - what you want summarized (usually a number, and it defaults to SUM)
- Filters - an optional filter applied to the whole table
To refresh a pivot table after the source data changes, right-click anywhere inside it and choose Refresh.
Named Ranges
Instead of referring to a range as B2:B50, you can name it something like “Sales” and use that name in formulas.
Select the range, click the Name Box (top left, where the cell address shows), type a name, and press Enter.
Then use it in formulas:
=SUM(Sales)
=AVERAGE(Sales)
Named ranges make formulas easier to read and less likely to break when rows are added or removed.
Links
Have questions after working through this? The replies are open.
New to this topic? Start with: Microsoft Excel Basics: Spreadsheets, Formulas, and Formatting