如何用一個 Excel 儀錶板追蹤多個收入來源

Once you have more than one small income source (a digital product, some freelance work, affiliate links, a newsletter) it gets hard to answer simple questions. Which one actually earns? Is this month better than last? Am I near my goal? Payment dashboards each show their own slice. One Excel workbook can pull it together.

Sheet 1: Streams

A short list of your income sources:

Col	Field
A	Stream name (e.g. "Template shop", "Client work")
B	Type (dropdown: Product, Service, Affiliate, Ads, Other)
C	Platform
D	Currency
E	Active? (Yes/No)

Name the stream list range: select A2:A50, type StreamList in the Name Box (left of the formula bar). You'll use it for dropdowns.

Sheet 2: Income log

One row per payout or sale:

Col	Field
A	Date
B	Stream (dropdown from StreamList)
C	Description
D	Currency
E	Gross amount
F	Fees
G	Net
H	Rate to base currency
I	Net in base currency
J	Month

Dropdown on B: Data > Data Validation > List, source =StreamList.

Formulas:

G2: =IF($A2="","",E2-N(F2))
I2: =IF($A2="","",G2*IF(H2="",1,H2))
J2: =IF($A2="","",TEXT(A2,"yyyy-mm"))

Type the rate yourself (what you actually received), and leave it blank when the currency already matches your base. Don't add USD and GBP amounts directly.

Sheet 3: Expenses (optional but honest)

Same shape: Date, Stream, Description, Amount in base currency, Month. Tools, ads and subscriptions often quietly eat a "passive" stream. Tracking them per stream shows real profit.

Sheet 4: Dashboard

Put the month you want to look at in B1 (e.g. 2026-10). List streams in A4 down. Then:

Income this month: =SUMIFS(Log!$I:$I,Log!$B:$B,$A4,Log!$J:$J,$B$1)
Expenses:          =SUMIFS(Exp!$D:$D,Exp!$B:$B,$A4,Exp!$E:$E,$B$1)
Profit:            =B4-C4
Share of income:   =IF(SUM($B$4:$B$20)=0,"",B4/SUM($B$4:$B$20))

Previous month for comparison, from the month in B1:

=TEXT(EDATE(DATEVALUE($B$1&"-01"),-1),"yyyy-mm")

Put that in B2 and copy the income formula pointing at B2 instead of B1. A change column, =IF(E4=0,"",B4/E4-1), formatted as %, shows growth per stream.

Goal tracking

In a Goals cell (say Dashboard!H1) type your monthly target. Then:

Progress: =IF($H$1=0,"",SUM(B4:B20)/$H$1)

Format as %, and add Conditional Formatting > Data Bars for a quick visual.

A chart that's actually useful

Make a pivot table from the Log: Rows = Month, Columns = Stream, Values = Sum of Net in base. Insert a stacked column chart from it. You'll see at a glance whether growth comes from one stream or several, and which months were carried by a single sale.

Year-to-date view

Monthly numbers bounce around, especially for products where one launch week can be most of the year. Add a year-to-date column to the Dashboard with the year in B3 (e.g. 2026):

=SUMIFS(Log!$I:$I,Log!$B:$B,$A4,Log!$A:$A,">="&DATE($B$3,1,1),Log!$A:$A,"<="&DATE($B$3,12,31))

Sort streams by this column at least once a quarter. It's the fairest way to decide where your next hours should go, and which stream has quietly stopped earning.

Keep payout proof

Add a Reference column to the Log for the payout or transaction ID. When a platform total and your bank don't agree, the reference lets you match them one by one instead of guessing.

Common mistakes

Logging gross only. Platform and payment fees can be significant; log them.
Mixing currencies. Always convert in a separate column with a rate you entered.
Calling it passive when it isn't. Add a "Hours spent" column to the log if you want to see income per hour.
Retyping stream names. Dropdowns keep the dashboard sums correct.

A monthly routine

On the 1st: export payouts from each platform, paste them into the Log, check the rates, change B1 to the new month. Fifteen minutes gives you one honest picture of all your income.
This article was drafted with AI tools. It's general spreadsheet guidance, not financial, tax or legal advice.