Excel Sort, Filter, Charts and PivotTables

Once your data is in Excel, these tools help you organise it and find answers quickly.

The Example Data

Monthly sales from a mobile accessories shop:

Sorting

Click a cell in the column, then Data > Sort A to Z (smallest first) or Sort Z to A (largest first). For more levels, use Data > Sort, for example Branch A to Z, then Amount largest to smallest.

In Microsoft 365 and Excel 2021, the SORT function returns a sorted copy and keeps the original unchanged:

=SORT(A2:D7, 4, -1)

The 4 means sort by the fourth column (Amount) and -1 means largest first:

Filtering

Click Data > Filter (or use the table's buttons). Click the arrow in a header and tick only the values you want, such as Branch = Orangi. Rows that do not match are hidden, not deleted.

The FILTER function returns matching rows as a new list:

=FILTER(A2:D7, B2:B7="Orangi")

Filter with two conditions by multiplying them (both must be true):

=FILTER(A2:D7, (B2:B7="Orangi")*(D2:D7>1000), "No match")

Conditional Formatting

Conditional formatting colours cells automatically based on their values. Select D2:D7, then Home > Conditional Formatting:

• Highlight Cells Rules > Greater Than 3000 in green
• Data Bars to show small bars inside the cells
• Color Scales to colour low to high values

You can also use a formula rule. To highlight whole rows for the Orangi branch, select A2:D7, choose New Rule > Use a formula, and enter:

=$B2="Orangi"

Charts

A chart turns numbers into a picture. Select the data (including headers), then Insert and pick a chart:

Use Chart Elements (the + button) to add a title, data labels and axis titles. Keep charts simple: one message per chart.

PivotTables

A PivotTable summarises a big table in a few clicks, without any formulas.

1. Click inside the data and choose Insert > PivotTable
2. Choose New Worksheet and click OK
3. Drag Branch to Rows
4. Drag Amount to Values (it becomes Sum of Amount)
5. Optionally drag Product to Columns

The result:

When new data is added, right-click the PivotTable and choose Refresh. You can also check a PivotTable number with a formula:

=SUMIFS(D2:D7, B2:B7, "Orangi")

Exercise