Working With Lists and Sharing Your Work

Start Here

Almost every spreadsheet you will make is really a list: a list of contacts, of stock, of hours worked, of people who registered for something. Once you can sort a list, filter it, keep it tidy, and print it cleanly, you can handle most of the everyday work a job will throw at you.

Throughout this session we will use a small registration list of people and Territory suburbs as our example, the kind of thing a community service or event might keep.

Sorting a List

Sorting rearranges your rows into order: alphabetically by name, smallest to largest by amount, oldest to newest by date. It is one of the fastest ways to make sense of a list.

The golden rule: Excel sorts whole rows, keeping each row's data together. As long as your list has a header row and no fully blank rows running through it, sorting moves each record as a unit, so a person's name stays attached to their suburb and age.

Quick sort, one column

1. Click any single cell in the column you want to sort by. (Do not select the whole column on its own; just click one cell, or Excel may sort only that column and scramble the rows.)
2. On the Data tab, click A to Z (ascending) or Z to A (descending).

Multi-level sort

To sort by more than one thing, say by Suburb, then by Age within each suburb: click a cell in the data, choose Data then Sort, set the first Sort by column and order, click Add Level for the next, and OK.

Filtering a List

Where sorting reorders your list, filtering hides the rows you do not currently care about, so only the matching ones show. The data is not deleted; it is just tucked out of sight until you clear the filter. It is the quickest way to answer questions like "show me only the people from Nightcliff" or "just the ones who did not attend".

1. Click any cell in your list.
2. On the Data tab, click Filter. A small arrow appears on each header. (If your data is a Table, these arrows are already there.)
3. Click a header's arrow, then tick or untick values to choose exactly what shows, or use Text, Number or Date Filters for conditions like "greater than" or "contains".
4. A small funnel icon on the header shows that column is filtered.

To bring everything back, open the column's filter menu and choose Clear Filter, or click Data then Clear to remove all filters at once. You can filter several columns at once and Excel combines them: filter Suburb to "Nightcliff" and Attended to "Yes" to see only the rows meeting both.

Grouping Rows

Grouping lets you fold a block of rows (or columns) up and down, like a drawer you can open and close. It is handy on a long sheet where you want to tuck the detail away and just see the summary, then open it again when you need it.

1. Select the rows you want to group (click and drag down the row numbers).
2. On the Data tab, in the Outline group, click Group.
3. A small bracket and a minus button appear to the left. Click the minus to collapse the rows out of sight, and the plus to bring them back.
4. To undo it, select the rows and click Ungroup.

It does not change or delete anything; it just hides and shows. Think of it as a neat way to keep a long list manageable.

Dropdown Lists (Data Validation)

It is far easier to keep data clean on the way in than to fix it later. Data validation sets rules for what a cell will accept. The star feature is the dropdown list: instead of everyone typing (and mistyping) "Complete", "complete", "Done", "Finished", they all pick the same word from a menu. Your data stays consistent, which makes sorting, filtering and counting reliable.

Make a dropdown list

1. Select the cells that should have the dropdown.
2. Go to Data then Data Validation.
3. Under Allow, choose List.
4. In Source, type the options separated by commas, for example Not started, In progress, Complete, or click and select a range of cells that holds the options.
5. Click OK. The selected cells now show a dropdown arrow.

In the same Allow dropdown you can instead require a Whole number, a Decimal, a Date, or a Text length, and set limits. Two optional tabs make it friendlier: an Input Message tooltip ("Pick a status from the list") and an Error Alert shown if someone enters something invalid.

Printing and Page Setup

Printing a spreadsheet is where many people come unstuck: a tidy sheet on screen prints as fifteen pages with one lonely column on the last. The trick is to use Print Preview to see what will actually print, then a few controls to tame it.

Always start with Print Preview

Press Ctrl + P (or File then Print). The right-hand side shows exactly what will print, and the page count tells you instantly whether things have sprawled. Most of the controls below sit on this screen.

The settings that solve most problems

• Fit it on the page: open the Scaling dropdown and choose Fit All Columns on One Page (so it stays one column wide but runs over several pages tall). This single setting stops the dreaded stray column on its own page.
• Orientation: switch between Portrait and Landscape. Landscape suits wide tables.
• Print only part of a sheet: select the range, then Page Layout then Print Area then Set Print Area.
• Repeat headings on every page: for a long list, use Page Layout then Print Titles then Rows to repeat at top, and pick your header row, so the column titles show on every printed page.
• Headers and footers: add a title, date or page numbers via Insert then Header & Footer. Useful for reports you hand around.

Putting It All Together

Look at how far you have come. In four sessions you have learned everything you need to build a real, useful workplace spreadsheet from scratch:

• From Session 1: open Excel, name what you are looking at, enter text, numbers and dates, AutoFill repetitive entries, and save with a clear name.
• From Session 2: write a formula with + - * /, total a column with SUM, and find the average, biggest and smallest.
• From Session 3: format the sheet clearly, set money as Currency, and chart the numbers.
• From Session 4: sort and filter the list, add a tidy dropdown, and print a clean report.

That is a complete workflow: enter, calculate, format, chart, sort, filter, print. Every one of those skills is something a workplace asks for, and you now have all of them.

Where to Go Next

One foundation course is enough to make you confident with the essentials. When the basics feel comfortable, these are the rewarding things to explore next. You do not need any of them for everyday work; they are just signposts for when you are ready.

Try It at the Keyboard

Open the starter workbook for this session, which has the events register ready to work with.

Knowledge Check