Step 1: Enter your data
| Region | Salesperson | Sales |
| :----- | :---------- | ----: |
| East | John | 500 |
| West | Mary | 300 |
| East | Alex | 200 |
| West | John | 400 |
| North | Mary | 350 |
| South | Alex | 250 |
| North | John | 450 |
---
Step 2: Select your data
Click and drag to select **all your data including the headers** (A1:C8 in this case).
*Step 3: Create a Pivot Table (for grouping)
1. Go to `Data → Pivot Table → Create…`
2. In the dialog box, choose **“Current Selection”** and click **OK**.
---
Step 4: Arrange the fields
In the Pivot Table layout dialog:
* Drag **`Region`** to the **Row Fields** area.
* Drag **`Sales`** to the **Data Fields** area.
* It will automatically set to **Sum - Sales**.
Click **OK**.
---
Step 5: View results
You’ll now see a Pivot Table showing the **sum of sales per region**:
| Region | Sum - Sales |
| :--------------- | ----------: |
| East | 700 |
| North | 800 |
| South | 250 |
| West | 700 |
| **Total Result** | **2450** |
---
Step 6: Optional — Group by more than one field
If you also want totals per **region and salesperson**:
* Add **Region** to **Row Fields**.
* Add **Salesperson** under **Region** in **Row Fields**.
* Add **Sales** to **Data Fields** again.
Now the Pivot Table will show grouped totals **within each region**.
---
You can also **group rows manually**:
1. Select rows you want to group.
2. Click **Data → Group and Outline → Group**.
3. You can then expand/collapse those grouped rows.
==========================================================================
Find **total sales per region** using the **Subtotal** feature.
---
### **Step 1: Enter your data**
| Region | Salesperson | Sales |
| :----- | :---------- | ----: |
| East | John | 500 |
| West | Mary | 300 |
| East | Alex | 200 |
| West | John | 400 |
| North | Mary | 350 |
| South | Alex | 250 |
| North | John | 450 |
---
Step 2: Sort your data by the group column
Subtotals work only when similar items are **next to each other**.
1. Select your whole table (A1:C8).
2. Go to **`Data → Sort…`**
3. In the dialog box:
* **Sort by:** `Region`
* Click **OK**.
Now your sheet should look like this:
| Region | Salesperson | Sales |
| :----- | :---------- | ----: |
| East | John | 500 |
| East | Alex | 200 |
| North | Mary | 350 |
| North | John | 450 |
| South | Alex | 250 |
| West | Mary | 300 |
| West | John | 400 |
---
### **Step 3: Add Subtotals**
1. Select your sorted data.
2. Go to **`Data → Subtotals…`**
3. In the dialog box:
* **Group by:** `Region`
* **Calculate subtotals for:** `Sales`
* **Use function:** `Sum`
4. Click **OK**.
---
Step 4: View results
LibreOffice Calc will automatically insert subtotal rows and group outlines like this:
| Region | Salesperson | Sales |
| :-------------- | :---------- | -------: |
| East | John | 500 |
| | Alex | 200 |
| **East Total** | | **700** |
| North | Mary | 350 |
| | John | 450 |
| **North Total** | | **800** |
| South | Alex | 250 |
| **South Total** | | **250** |
| West | Mary | 300 |
| | John | 400 |
| **West Total** | | **700** |
| **Grand Total** | | **2450** |
---
Step 5: Collapse or expand groups
You’ll now see small **“+” / “–”** buttons on the left margin.
* Click **–** to collapse a region’s details and see only totals.
* Click **+** to expand them again.
This helps when managing long datasets.
---
Step 6: Customize
If you open **`Data → Subtotals…`** again, you can:
* Change **function** (Average, Count, Max, etc.)
* Add **second-level subtotals** (e.g., by Salesperson inside Region).
* Turn off subtotals (using **Remove** button).
---
### 🔍 Difference from Pivot Tables
| Feature | Subtotals | Pivot Table |
| -------------------------------- | --------- | ----------------------------- |
| Works directly on your sheet | ✅ Yes | ❌ No (creates separate table) |
| Easy to see inline totals | ✅ Yes | ✅ Yes |
| Supports interactive rearranging | ❌ No | ✅ Yes |
| Best for quick summaries | ✅ Yes | ✅ Yes (more flexible) |
===================================================
Using Goal Seek to Find Units Needed for a Target Profit
You now have this setup:
| A | B | C |
| 1 | Unit Price | 50 |
| 2 | Unit Cost | 30 |
| 3 | Units Sold | 100 |
| 4 | Profit | `=(C1 - C2) * C3` |
Currently, Profit = 2000.
Now you want to know:
How many units do I need to sell to make a $5000 profit?
---
Step 1. Select the formula cell
Click on the cell with your **Profit formula** (that’s **C4**).
-----------
Step 2. Open Goal Seek
Go to the menu:
> **Tools → Goal Seek…**
You’ll see a dialog box like this:
| Field | What to enter |
| :---------------- | :--------------------------------------- |
| **Formula cell** | `C4` (the cell with your profit formula) |
| **Target value** | `5000` |
| **Variable cell** | `C3` (the Units Sold cell) |
---
### **Step 3. Run Goal Seek**
Click **OK**.
LibreOffice will think for a moment and then show:
---
### **Step 4. Check your result**
Now your sheet will look like this:
| Label | Value |
| :--------- | -------: |
| Unit Price | 50 |
| Unit Cost | 30 |
| Units Sold | **250** |
| Profit | **5000** |
Because:
> (50 − 30) × 250 = 5000 ✔️
===================================================
Find “Units Sold” for a Target Profit (using Solver)
| A | B | C |
| 1 | Unit Price | 50 |
| 2 | Unit Cost | 30 |
| 3 | Units Sold | 100 |
| 4 | Profit | `=(C1 - C2) * C3` |
Profit = 2,000 currently.
Goal: make **Profit = 5,000**
Variable: **Units Sold (C3)**
---
1. Go to Tools → Solver.
2.A dialog box appears.
3. Fill in the Solver settings**
| Setting | What to Enter |
| :--------------------- | :----------------- |
| **Target Cell** | `C4` (Profit cell) |
| **Optimize Result To** | `Value of` |
| **Value of** | `5000` |
| **By Changing Cells** | `C3` (Units Sold) |
---
4. Add Constraints
C3 <= 400
You could also add:
C3 >= 0 (to prevent negative sales).
5. Run Solver
Click **Solve**.
Solver will adjust **C3** until Profit (C4) is as close as possible to **5000** while respecting constraints.
You’ll then see a result message:
> *Solver found a solution. Do you want to keep it?*
Click Yes
---
6. Check your result**
Your table will now look like this:
| Label | Value |
| :--------- | -------: |
| Unit Price | 50 |
| Unit Cost | 30 |
| Units Sold | **250** |
| Profit | **5000** |
✅ Because (50 − 30) × 250 = 5000
---
Solver vs Goal Seek
| Feature | **Goal Seek** | **Solver** |
| -------------------------------------- | ------------- | ------------ |
| Changes one variable | ✅ Yes | ✅ Yes |
| Handles multiple variables | ❌ No | ✅ Yes |
| Supports constraints | ❌ No | ✅ Yes |
| Solves optimization (max/min problems) | ❌ No | ✅ Yes |
| Simple “what-if” problems | ✅ Perfect | ✅ Also works |
---
Example 2 — Maximize Profit with Constraints
Let’s say you want Solver to **maximize profit** given limits:
* Units Sold ≤ 400
* Unit Price can vary between 40 and 60
Then:
| Target cell | `C4` (Profit) |
| Optimize result to | **Max** |
| By changing cells | `C1, C3` (Price and Units) |
| Constraints | `C1 <= 60`, `C1 >= 40`, `C3 <= 400` |
==========================================================================