Reading and writing cell values in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are an office assistant managing a small inventory list.

📋 Request: Your manager wants you to update the stock quantities and check the current price of items quickly.

📊 Data: You have a table with item names, their current stock quantity, and price per unit.

🎯 Deliverable: Create a simple sheet where you can read the price of any item by typing its name and update the stock quantity for items.

Sample Data

Item	Stock	Price
Pen	100	1.20
Notebook	50	2.50
Eraser	75	0.80
Marker	40	1.50
Ruler	60	1.00

1

Step 1: Create a new sheet named 'Inventory'. Enter the sample data starting from cell A1 with headers: Item, Stock, Price.

Manually enter data as shown in sample_data.

Expected Result

Table with 5 items and their stock and price is visible in columns A, B, and C.

2

Step 2: In cell E1, type 'Enter Item Name'. In cell E2, type the name of an item to check its price, for example, 'Pen'.

Manually type 'Enter Item Name' in E1 and 'Pen' in E2.

Expected Result

Cell E2 contains the item name 'Pen' to look up.

3

Step 3: In cell F1, type 'Price'. In cell F2, write a formula to find the price of the item typed in E2 by searching the 'Item' column.

=VLOOKUP(E2, A2:C6, 3, FALSE)

Expected Result

Cell F2 shows 1.20, the price of 'Pen'.

4

Step 4: In cell G1, type 'Update Stock'. In cell G2, enter a new stock quantity for the item typed in E2, for example, 120.

Manually type 120 in G2.

Expected Result

Cell G2 contains the new stock quantity 120.

5

Step 5: To update the stock quantity in the table, select the stock cell corresponding to the item typed in E2 (for example, B2 for Pen) and type the new value from G2 manually or copy-paste it.

Manually replace the stock cell value with 120.

Expected Result

Stock for Pen in the table updates to 120.

6

Step 6: Test by changing the item name in E2 to 'Notebook' and observe the price in F2 updates automatically.

Change E2 to 'Notebook'. The formula in F2 remains =VLOOKUP(E2, A2:C6, 3, FALSE).

Expected Result

F2 shows 2.50, the price of Notebook.

Final Result

Inventory Sheet

| Item     | Stock | Price |
|----------|-------|-------|
| Pen      | 120   | 1.20  |
| Notebook | 50    | 2.50  |
| Eraser   | 75    | 0.80  |
| Marker   | 40    | 1.50  |
| Ruler    | 60    | 1.00  |

Lookup Section:
Enter Item Name: Pen
Price: 1.20
Update Stock: 120

✓You can read a cell value by typing the item name and using VLOOKUP to find its price.

✓You can write or update a cell value by typing a new stock number and manually updating the table.

✓This simple method helps quickly check prices and update stock in a small inventory.

Bonus Challenge

Create a formula that automatically updates the stock quantity in the table when you enter a new stock number without manual copy-pasting.

Show Hint

Use Google Sheets Apps Script or explore the FILTER and ARRAYFORMULA functions for dynamic updates.

Practice

1. What does the cell reference A1 mean in Google Sheets?

easy

A. It is a formula to add values.

B. It refers to the first sheet in the workbook.

C. It refers to the cell in column A and row 1.

D. It is a function to read data.

Solution

1. Step 1: Understand cell references

   Cell references like A1 point to a specific cell by column letter and row number.

2. Step 2: Identify column and row

   A1 means column A and row 1, the top-left cell in the sheet.

3. Final Answer:

   It refers to the cell in column A and row 1. -> Option C

4. Quick Check:

   Cell reference A1 = column A + row 1 [OK]

Hint: Column letter + row number = cell address [OK]

Common Mistakes:

• Thinking A1 is a formula
• Confusing cell reference with sheet name
• Assuming A1 is a function

2. Which of the following is the correct way to write a formula that adds cells A1 and B1 in Google Sheets?

easy

A. SUM(A1+B1)

B. A1+B1

C. =SUM(A1 B1)

D. =A1+B1

Solution

1. Step 1: Recognize formula syntax

   Formulas start with an equal sign (=) in Google Sheets.

2. Step 2: Use correct addition syntax

   To add two cells, use =A1+B1 with plus sign and no extra spaces or missing symbols.

3. Final Answer:

   =A1+B1 -> Option D

4. Quick Check:

   Formula starts with = and uses + for addition [OK]

Hint: Always start formulas with = sign [OK]

Common Mistakes:

• Omitting the = sign
• Using SUM without commas
• Writing formula without operators

3. If cell A1 contains 5 and cell B1 contains 10, what will be the result in cell C1 after entering the formula =A1*B1?

medium

A. 50

B. 15

C. 510

D. Error

Solution

1. Step 1: Identify values in cells

   Cell A1 has 5 and B1 has 10.

2. Step 2: Calculate multiplication

   =A1*B1 multiplies 5 by 10, resulting in 50.

3. Final Answer:

   50 -> Option A

4. Quick Check:

   5 * 10 = 50 [OK]

Hint: Use * for multiplication in formulas [OK]

Common Mistakes:

• Adding instead of multiplying
• Concatenating numbers as text
• Forgetting = sign

4. A user types the formula =SUM(A1 B1) in cell C1. What is wrong with this formula?

medium

A. SUM cannot be used with cell references.

B. Missing comma between cell references.

C. Formula should not start with = sign.

D. Cell references must be lowercase.

Solution

1. Step 1: Check SUM function syntax

   SUM requires arguments separated by commas, like SUM(A1, B1).

2. Step 2: Identify missing comma

   The formula uses a space instead of a comma between A1 and B1, causing an error.

3. Final Answer:

   Missing comma between cell references. -> Option B

4. Quick Check:

   SUM(A1, B1) needs commas [OK]

Hint: Separate arguments with commas in functions [OK]

Common Mistakes:

• Using spaces instead of commas
• Removing = sign
• Using lowercase cell references (not required)

5. You want to write a formula in cell C1 that sums the values from A1 to A5 and then multiplies the result by the value in B1. Which formula should you enter in C1?

hard

A. =SUM(A1:A5)*B1

B. =SUM(A1:A5+B1)

C. =SUM(A1:A5)*SUM(B1)

D. =SUM(A1:A5*B1)

Solution

1. Step 1: Sum the range A1 to A5

   Use SUM(A1:A5) to add all values from A1 through A5.

2. Step 2: Multiply the sum by B1

   Multiply the sum by B1 using *B1 outside the SUM function.

3. Final Answer:

   =SUM(A1:A5)*B1 -> Option A

4. Quick Check:

   Sum range then multiply by B1 [OK]

Hint: Use colon for ranges and * for multiplication [OK]

Common Mistakes:

• Adding B1 inside SUM instead of multiplying
• Multiplying inside SUM causing wrong calculation
• Using multiple SUM functions unnecessarily