• Foundation
  • About 16 min
  • 5 practice questions

Sorting, filtering, freeze panes, charts and printing a sheet

Arrange data with sort and filter, keep headings visible with freeze panes, choose the right chart for the data, and print a sheet neatly.

If something looks wrong, please tell us.

Once data is entered and calculated, you need to find things in it, show it to others and put it on paper. Sorting and filtering answer questions such as 'who scored highest?' or 'which bills are unpaid?'. Charts make a pattern visible at a glance. Good print settings stop a table from spilling across ten untidy pages.

Sorting

Click any cell inside the table and choose Data > Sort (the same menu name in both programs). Choose the column to sort by and the order: ascending (A to Z, smallest to largest, oldest date first) or descending (Z to A, largest first). Tell the program your table has a header row so the headings stay on top. You can add a second key, for example sort by class and then, within each class, by marks.

Always sort the whole table, not one selected column. If you select only the Marks column and sort it, the marks move but the names do not, and every row is then wrong.

Worked example 1: sort by marks, highest first

Before (as typed)After sorting Marks descending
Asha 72Esha 91
Bilal 45Charu 88
Charu 88Asha 72
Dev 60Dev 60
Esha 91Bilal 45

Filtering

A filter shows only the rows that meet a condition and hides the others - it does not delete them. In Excel choose Data > Filter; in Calc choose Data > AutoFilter. Drop-down arrows appear on the headings; pick values or a condition such as 'greater than or equal to 60'. In the table above, Marks ≥ 60 shows Asha, Charu, Dev and Esha (4 rows) and hides Bilal. Clearing the filter brings every row back.

Sort or filter?

  • Sort
    • Data > Sort
    • Puts rows in ascending or descending order
    • Sort the whole table, never one column
  • Filter
    • Excel: Data > Filter; Calc: Data > AutoFilter
    • Shows only rows that meet a condition
    • Hides the others, does not delete them

Freeze panes

In a long list the headings scroll out of sight. Freezing keeps chosen rows and columns on screen. Excel: View > Freeze Panes; Calc: View > Freeze Cells. Both offer a quick option for the first row or first column. For anything else, first click the cell just below the rows and just right of the columns you want to keep: to keep row 1 and column A, click B2 and freeze. Rows above and columns left of that cell stay put.

Charts: pick the one that fits the question

ChartBest forExample
Column (vertical bars)Comparing amounts across a few categoriesSales of five branches
Bar (horizontal bars)Comparing categories with long names, or many of themVotes for twelve candidates
LineA trend over timeTemperature for twelve months
PieParts of one whole, a few slicesHow one month's budget is shared

To make a chart, select the data with its labels and choose Insert > Chart (in Excel, the Insert tab offers each chart type as a button). Add a chart title, axis titles and a legend if the chart has more than one series. The chart is linked to its cells: change a number and the chart redraws. A pie chart shows one series only, and it misleads if the parts do not add up to a meaningful whole.

Worked example 2: choosing a chart

A shop wants to see whether monthly sales rose or fell from April to March: time order matters, so a line chart. The same shop wants to show what share of this year's sales came from each of four product groups: parts of one total, so a pie chart. To compare this year's sales of its six branches side by side, a column chart (or a bar chart if the branch names are long).

Which chart?

  • Line chart
    • A trend over time
    • Monthly sales from April to March
  • Pie chart
    • Parts of one whole
    • Share of sales from each product group
  • Column chart
    • Comparing amounts across categories
    • Sales of six branches side by side
  • Bar chart
    • Categories with long names

Printing a sheet

  • Print preview first, to see the page breaks (File > Print in Excel shows a preview beside the settings; Calc has File > Print Preview).
  • Set a print area if only part of the sheet should print (Excel: Page Layout > Print Area; Calc: Format > Print Ranges).
  • Choose landscape orientation for wide tables, and scale to fit all columns on one page width.
  • Repeat the heading row on every page (Excel: Page Layout > Print Titles; Calc: Format > Print Ranges > Edit).
  • Gridlines usually do not print unless you switch them on; add borders to cells that must show lines.

Sort the whole table, never one column. A filter hides rows, it does not delete. Freeze at the cell below and right of what must stay. Line = trend over time; pie = parts of a whole; column/bar = compare categories.

Key takeaways: Data > Sort for order, Data > Filter (Excel) or Data > AutoFilter (Calc) to show only matching rows, freeze panes for headings, the chart type chosen by the question, and print preview plus print area, orientation and repeated headings for neat pages.

Check your understanding

  1. 1. A family wants to show what share of one month's budget goes to rent, food, travel and savings. Which chart suits best?

    1. Line chart
    2. Bar chart with a time axis
    3. Pie chart
    4. Scatter chart
    Show answer

    Answer: C. The data are parts of one whole (one month's budget), with only four parts, which is exactly what a pie chart shows.

  2. 2. Which chart best shows how the temperature of a city changed over twelve months?

    1. Pie chart
    2. Column chart of one month
    3. A table without a chart
    4. Line chart
    Show answer

    Answer: D. A trend over time is shown best by a line chart, where the rise and fall between months is easy to follow.

  3. 3. You want row 1 and column A to stay visible while you scroll. Which cell should be selected before freezing panes?

    1. B2
    2. A1
    3. A2
    4. B1
    Show answer

    Answer: A. Freezing keeps the rows above and the columns to the left of the selected cell. Selecting B2 keeps row 1 (above) and column A (to the left).

  4. 4. You apply a filter that shows only marks of 60 and above. What happens to the rows with lower marks?

    1. They are deleted from the sheet
    2. They are hidden and come back when the filter is cleared
    3. They are moved to a new sheet
    4. Their marks are changed to blank
    Show answer

    Answer: B. A filter only hides the rows that do not match; nothing is deleted or changed, and clearing the filter shows all rows again.

  5. 5. In LibreOffice Calc, which menu command switches on drop-down filter arrows for a table's headings?

    1. Data > AutoFilter
    2. View > Freeze Cells
    3. Format > Print Ranges
    4. Insert > Chart
    Show answer

    Answer: A. Calc names this command Data > AutoFilter (Excel calls it Data > Filter). Freeze Cells keeps headings visible, Print Ranges controls printing, and Insert > Chart makes a chart.

Privacy · Terms · Refunds · Delivery