LIMIT and OFFSET in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants to see a small, specific part of the sales data to review recent transactions without scrolling through the entire list.

📊 Data: You have a table with sales transactions including Date, Product, Region, and Sales Amount.

🎯 Deliverable: Create a table that shows only 5 sales records starting from the 6th record in the data.

Sample Data

Date	Product	Region	Sales Amount
2024-01-01	Shirt	East	100
2024-01-02	Pants	West	150
2024-01-03	Hat	North	50
2024-01-04	Shoes	South	200
2024-01-05	Shirt	East	120
2024-01-06	Pants	West	180
2024-01-07	Hat	North	70
2024-01-08	Shoes	South	220
2024-01-09	Shirt	East	130
2024-01-10	Pants	West	160

1

Step 1: Select a cell where you want to display the limited data, for example, cell F2.

Expected Result

Cell F2 is selected and ready for formula input.

2

Step 2: Enter the formula to get 5 sales records starting from the 6th record in the sales data table. Use the QUERY function with LIMIT and OFFSET.

=QUERY(A1:D11, "SELECT A, B, C, D LIMIT 5 OFFSET 5", 1)

Expected Result

The table shows data rows 6 to 10 from the original data, including headers.

3

Step 3: Check that the displayed data matches the original data rows 6 to 10 exactly.

Expected Result

Displayed rows: 2024-01-06 Pants West 180; 2024-01-07 Hat North 70; 2024-01-08 Shoes South 220; 2024-01-09 Shirt East 130; 2024-01-10 Pants West 160.

Final Result

Date       | Product | Region | Sales Amount
------------------------------------------
2024-01-06 | Pants   | West   | 180
2024-01-07 | Hat     | North  | 70
2024-01-08 | Shoes   | South  | 220
2024-01-09 | Shirt   | East   | 130
2024-01-10 | Pants   | West   | 160

✓You can use LIMIT and OFFSET in QUERY to show a specific part of your data.

✓This helps focus on recent or specific records without scrolling.

✓The formula =QUERY(A1:D11, "SELECT A, B, C, D LIMIT 5 OFFSET 5", 1) extracts 5 rows starting from the 6th data row.

Bonus Challenge

Modify the formula to show the next 5 rows after the current 5, i.e., rows 11 to 15, and handle the case if there are fewer than 5 rows left.

Show Hint

Change the OFFSET number to 10 and keep LIMIT 5. Use IFERROR to handle fewer rows gracefully.