How to make a service bill in Excel step by step?
Practical instructions for creating a service bill in Excel. Download the template and learn how to efficiently calculate energy costs.
TL;DR – Quick answer
You can handle invoicing in Excel using a table with costs for individual services, billing keys and advances. Create columns for utilities, total cost, apartment share, deposits, and difference. Use formulas for automatic calculation. For multiple flats, use separate sheets or columns for each flat.
Why Excel (and why not)
Excel is like a Swiss army knife - it can do almost everything, but there are better tools for some things. For billing one or two flats, Excel is absolutely sufficient. For a bigger portfolio... well, let's break it down.
Pros of Excel:
- You (probably) have it on your computer
- It's free (if you have Office)
- Flexible - you can customize it to your liking
- Everyone knows it
Cons of Excel:
- Manual entry = manual errors
- No deadline reminders
- Versioning = chaos ("vyuctovani_final_v3_opraveno.xlsx")
- Confused with multiple apartments
- No connection to the bank or meters
But now to the point - how to do it in Excel.
Excel table structure
Sheet 1: Basic data
Create a sheet with these information:
| Cell | Contents | Example |
|---|---|---|
| A1 | Landlord | Jan Novák |
| A2 | Landlord Address | Hlavní 123, Prague |
| A3 | Accounting period | 1/1/2024 - 31/12/2024 |
| A5 | Apartment address | Secondary 456/12 |
| A6 | Tenant | Marie Svobodová |
| A7 | Floor area (m²) | 65 |
| A8 | Number of people | 2 |
Sheet 2: Total costs of the house
Here you record the costs for the entire house/object:
| And | B | C | D |
|---|---|---|---|
| Service | Total Cost | Unit | Total Units |
| Heat | 180000 | m² | 500 |
| Cold water | 45000 | m³ | 450 |
| Hot water | 35000 | m³ | 280 |
| Common-area electricity | 12000 | m² | 500 |
| Cleaning | 18000 | flats | 12 |
| Waste | 8000 | persons | 24 |
Sheet 3: Apartment billing
Here you make your own calculation:
Columns:
- A: Service
- B: Total costs
- C: Billing method
- D: Apartment share (unit)
- E: Total units
- F: Apartment costs (calculation)
- G: Advances paid
- H: Difference
Step by step: Creating a table
Step 1: Header
In line 1 write the column names:
`` A1: Service B1: Total costs (Kč) C1: Billing D1: Share apartment E1: Total F1: Apartment costs (Kč) G1: Deposits (Kč) H1: Difference (Kč) ``
Step 2: Service data
From line 2, fill in the data:
`` A2: Heat B2: 180000 C2: area m² D2: 65 E2: 500 ``
Step 3: Formula for apartment cost
In cell F2, write the formula:
`` =B2*(D2/E2) ``
This formula takes the total cost (B2), multiplies it by the share of the apartment (D2) divided by the total (E2).
Result for heat: 180000 × (65/500) = Kč 23400
Step 4: Deposits
In column G, write the paid deposits for the given service. If you have one deposit for all services, divide it proportionally or enter the whole amount at the end.
`` G2: 24000 ``
Step 5: Difference
In cell H2, write the formula:
`` =G2-F2 ``
Positive number = overpayment (you return to the tenant) Negative number = underpayment (tenant pays extra)
Result: 24000 - 23400 = Kč 600 (overpayment)
Step 6: Copy the formulas
Mark cells F2:H2 and copy down for all services.
Step 7: Totals
At the end add a line with totals:
`` A10: TOTAL F10: =SUM(F2:F9) G10: =SUM(G2:G9) H10: =SUM(H2:H9) ``
Complete example tables
| Service | Total. costs | Settlement. | Share | Total | Apartment costs | Advances | Difference |
|---|---|---|---|---|---|---|---|
| Heat | 180000 | m² | 65 | 500 | 23400 | 24000 | 600 |
| Cold water | 45000 | m³ | 45 | 450 | 4500 | 4800 | 300 |
| Hot water | 35000 | m³ | 28 | 280 | 3500 | 3600 | 100 |
| Common-area electricity | 12000 | m² | 65 | 500 | 1560 | 1200 | -360 |
| Cleaning | 18000 | flats | 1 | 12 | 1500 | 1200 | -300 |
| Waste | 8000 | persons | 2 | 24 | 667 | 600 | -67 |
| TOTAL | 35127 | 35400 | 273 |
Result: Overpayment of Kč 273
Extension for several apartments
Option A: Separate sheets
Each apartment has its own sheet. Simple, but you have to edit all sheets when you change the structure.
Option B: One sheet, multiple columns
Create columns for each apartment:
| Service | Total. costs | Apartment 1 share | Flat 1 costs | Flat 2 share | Flat 2 costs | ... |
Option C: Database Access
Use a table with all data and a pivot table for billing. The most flexible, but the most complex to set up.
Advanced formulas
Automatic determination of billing method
If you want the formula to decide by type services:
`` =WHEN(C2="area";B2(D2/E2);WHEN(C2="persons";B2(D2/E2);WHEN(C2="meter";D2*B2/E2;B2/E2))) ``
Error checking
To avoid splitting by zero:
`` =IFERROR(B2*(D2/E2);0) ``
Conditional formatting
Mark overpayments in green and underpayments in red:
- Highlight the H
2 column. Home → Conditional Formatting → New Rule
- "Format cells containing" → values greater than 0 → green fill
- Repeat for values less than 0 → red fill
Common Excel errors
Error 1: Typo in the number
You write 18000 instead of 180000. The result is ten times smaller and the tenant celebrates.
Solution: Check the totals. If the total costs do not match the invoices, look for an error.
Error 2: Bad formula
You copy the formula and forget to use an absolute reference. Instead of E2 you need $E$2.
Example of correct formula: `` =B2*(D2/$E$2) ``
Error 3: Cell format
Numbers formatted as text are not counted. The result of the formula is zero or an error.
Solution: Highlight Cells → Format Cells → Number
Error 4: Rounding
Excel calculates with many decimal places but displays rounded numbers. The sums then "do not match".
Solution: Use the ROUND function: `` =ROUND(B2*(D2/E2);0) ``
Error 5: Lost file
"So where do I have last year's bill?" Classic.
Solution:
- Name the files systematically: Vyuctovani_2024_Novakova_Vedlejsi456.xlsx
- Backup to the cloud
- Do not delete old versions
How to improve Excel
Macros
You can create macros for repeated actions. For example:
- Automatic table formatting
- PDF generation
- Copy structure for the new year
Connection with other files
You can load total costs from another file: `` =[NakladyDomu.xlsx]Sheet1!B2 ``
Power Query
For advanced users - loading data from multiple sources, transformations, automatic updates.
When Excel is not enough
Excel stops being practical when:
- You have more than 5 flats - Errors are increasing, taking forever
- You want a bank link - Excel can't do this
- Need to track meters – Manual logging is annoying
- Want reminders - Excel will not remind you of anything
- You work in a team - File versioning is a nightmare
How does Grento handle it
Don't want to mess around with Excel? Grento offers:
- Automatic invoicing – You enter meter statuses, the system calculates
- Invoice import – Uploads them to the system, no rewriting
- Connection with the bank – Advances are matched automatically
- PDF export – Professional invoicing with a click
- History – All invoicing in one place
- No formulas – No division by zero, no errors
Excel is a good start. Grento is the next level.
Download
A sample Excel table for billing services is included in this article. Copy it and modify it according to your needs.
Create a copy of this table as your own spreadsheet template and adapt the rows, services and formulas to your property.
Summary
Accounting in Excel:
- ✅ Create a structure (services, costs, shares, deposits)
- ✅ Use formulas to automatically calculate
- ✅ Check totals with invoices
- ✅ Backup and version files
- ✅ For multiple apartments, consider specialized software
Excel is a tool that can handle billing. But if you have multiple properties or want automation, there are better solutions. Get started with Excel, understand the principles, and then decide if that's enough for you or if you need something more.
> Czech-law scope and editorial note: Legal, tax, accounting, tenancy, service-charge, condominium, metrology, insurance and data-protection statements in this article refer to the Czech Republic and the rules effective for the relevant date. Figures and examples are illustrative, not legal or tax advice. Verify current rules with the relevant Czech authority or a qualified adviser.
> Service-accounting clarification: Under Czech Act No. 67/2013 Coll., distinguish delivery of the settlement within four months after the accounting period, financial settlement generally within four months after delivery, and the separate 30-day periods for supporting documents or objections under § 8. A statutory per-day penalty under § 13 is conditional; verify the current amount and cap rather than applying a universal figure.