- Practice
- About 17 min
- 5 practice questions
Data tasks: series, dates and formats, multi-level sorting, filtering and freeze panes
Fill series and dates quickly, calculate with dates, apply number formats correctly, sort by two keys, filter with conditions and freeze the right rows and columns.
If something looks wrong, please tell us.
Office work is mostly handling lists: attendance by date, fees by class, stock by item. This lesson turns the basic data tools into quick, reliable routines and shows the traps that appear in practical tests.
Series in seconds
- Type Monday, drag the fill handle: Tuesday, Wednesday ... Jan gives Feb, Mar; Q1 gives Q2, Q3.
- Type 5 and 10 in two cells, select both, drag: 15, 20, 25 (same step).
- Type a date and drag: one day is added per cell, and the month changes correctly (31-01-2024 is followed by 01-02-2024).
- For a long series with an exact step and end value, use the Fill Series dialog (Excel: Home > Fill > Series; Calc: Sheet > Fill Cells > Fill Series in recent versions).
Dates are numbers - calculate with them
Because a date is stored as a count of days, adding a number adds days and subtracting two dates gives the days between them. If the result shows as a date when you wanted a count, change its format to Number.
Worked example 1: due dates and days between
- A2 holds 15-03-2024 (an invoice date). =A2+30 gives the due date 14-04-2024: 16 days remain in March (16 to 31), and 14 more reach 14 April - 30 in all.
- A3 holds 10-01-2024 and B3 holds 05-03-2024. =B3-A3 gives 55 days: 21 days left in January (11-31), 29 in February (2024 is a leap year) and 5 in March. In 2023, with a 28-day February, the same dates are 54 days apart.
Number formats that tests ask about
| You do | Stored value | Displayed |
|---|---|---|
| Type 7% | 0.07 | 7% |
| Type 0.07, then apply Percentage | 0.07 | 7% |
| Type 7, then apply Percentage | 7 | 700% |
| Type 2500 with Currency, 2 decimals | 2500 | ₹2,500.00 |
The third row is a common surprise: formatting an existing 7 as a percentage shows 700%, because the format multiplies by 100 for display.
Sorting by two keys
Data > Sort, first key Class (ascending), second key Marks (descending). The second key decides the order only among rows that tie on the first key.
Worked example 2: a two-key sort
| Original order | After Class ↑ then Marks ↓ |
|---|---|
| Ravi, class 10, 81 | Uday, class 9, 88 |
| Sana, class 9, 74 | Sana, class 9, 74 |
| Tara, class 10, 95 | Tara, class 10, 95 |
| Uday, class 9, 88 | Ravi, class 10, 81 |
| Vikas, class 10, 67 | Vikas, class 10, 67 |
Class 9 comes before class 10; inside class 9, Uday (88) is above Sana (74); inside class 10, the order is 95, 81, 67.
Filtering with conditions
Turn on Data > Filter (Excel) or Data > AutoFilter (Calc), open a heading's arrow and choose a number condition such as 'greater than 80' (Excel: Number Filters; Calc: Standard Filter). On the table above, Marks > 80 shows Ravi, Tara and Uday. Row numbers of shown rows change colour (Excel) and the gaps in the numbering reveal hidden rows. Functions such as SUM still include hidden rows; filtering changes only what you see.
Freezing the right cells
Select the cell just below the last row to keep and just right of the last column to keep, then freeze (Excel: View > Freeze Panes; Calc: View > Freeze Cells). Keep rows 1-2 and column A: select B3. Keep only rows 1-3: select A4. Keep only columns A-B: select C1.
Date + number = later date; date − date = days. Two keys: the second breaks ties in the first. Freeze at (first column not kept, first row not kept).
Key takeaways: give the fill handle two values or use Fill Series for exact steps; calculate with dates directly; type % or format before typing; use a second sort key for ties; filters hide but do not delete; and freeze at the cell below-right of what must stay visible.
Check your understanding
-
1. A2 holds the date 15-03-2024. What date does =A2+30 show?
- 15-04-2024
- 13-04-2024
- 14-04-2024
- 14-03-2024
Show answer
Answer: C. From 15 March, 16 days take you to 31 March and 14 more to 14 April: 16 + 14 = 30 days. So the result is 14-04-2024.
-
2. A3 holds 10-01-2024 and B3 holds 05-03-2024. What does =B3-A3 return?
- 55
- 54
- 56
- 2
Show answer
Answer: A. 21 days remain in January after the 10th, February 2024 has 29 days, and then 5 days in March: 21 + 29 + 5 = 55.
-
3. You want rows 1 and 2 and column A to stay visible while scrolling. Which cell do you select before freezing?
- B2
- A3
- B3
- C3
Show answer
Answer: C. Freezing keeps rows above and columns to the left of the selected cell. B3 has rows 1-2 above it and column A to its left.
-
4. The class table is sorted by Class ascending, then Marks descending: Ravi 10/81, Sana 9/74, Tara 10/95, Uday 9/88, Vikas 10/67. Who comes first?
- Tara
- Uday
- Sana
- Ravi
Show answer
Answer: B. Class 9 rows come first. Among them, Uday's 88 is higher than Sana's 74, so Uday is first. Tara has the top marks but is in class 10.
-
5. A cell already holds 7. You then apply the Percentage format with no decimals. What does it show?
- 7%
- 0.07%
- 70%
- 700%
Show answer
Answer: D. The percentage format shows the stored value × 100. The stored value is 7, so it shows 700%. To get 7%, type 7% or enter 0.07.
Sign in to keep track of the lessons you finish.