如何用 Excel 製作 30 天社交媒體內容日曆

Most small shops don't stop posting because they lack ideas. They stop because each day starts from a blank page. A 30-day calendar in Excel fixes that: you plan themes once, and every day already has a prompt waiting. Here's how to build one where you change one start date and everything else follows.

Settings

On a sheet called Settings:

• B1: Start date (e.g. 01-11-2026)
• A4:A10: seven weekly themes, one per weekday (for example: Product spotlight, Behind the scenes, Customer question, Tip, Offer, Story, Weekly recap)

Sheet: Calendar

Headers in row 1: Day, Date, Weekday, Theme, Post idea, Caption no., Caption, Platform, Format, Status, Hashtags.

Day (A2:A31): type 1 in A2, then =A2+1 in A3 and fill down to A31.

Date (B2):

=Settings!$B$1+A2-1

Weekday (C2):

=TEXT(B2,"dddd")

Theme (D2), cycling through your seven themes:

=INDEX(Settings!$A$4:$A$10,MOD(A2-1,7)+1)

Change the start date and every date, weekday and theme moves with it.

A caption bank

On a sheet called Captions, column A = number (1, 2, 3…), column B = theme, column C = caption text with blanks to fill, e.g. "Our [product] is back in stock. Which colour should we restock first?"

On the Calendar, Caption no. (F2) can start as the day number, =A2, and you overwrite it when you want a different one. Then Caption (G2):

=IFERROR(INDEX(Captions!$C:$C,MATCH(F2,Captions!$A:$A,0)),"")

Want the caption to match the day's theme automatically? In Excel 365/2021:

=IFERROR(INDEX(FILTER(Captions!$C$2:$C$500,Captions!$B$2:$B$500=D2),ROUNDUP(A2/7,0)),"")

This picks the 1st caption for that theme in week 1, the 2nd in week 2, and so on.

Dropdowns

• Platform: Instagram, Facebook, WhatsApp Status, YouTube Shorts, Other
• Format: Photo, Carousel, Reel, Story, Text
• Status: Idea, Drafted, Scheduled, Posted, Skipped

Select each column, Data > Data Validation > List, and type the values separated by commas.

Make today stand out

Select A2:K31, Home > Conditional Formatting > New Rule > Use a formula:

=$B2=TODAY()

Pick a light fill. A second rule, =AND($B2<TODAY(),$J2<>"Posted",$J2<>"Skipped"), in light red, shows days you missed.

Track how you're doing

A small summary above or beside the calendar:

Posted:  =COUNTIF(J2:J31,"Posted")
Planned: =COUNTA(B2:B31)
Rate:    =IF(COUNTIF(B2:B31,"<="&TODAY())=0,"",COUNTIF(J2:J31,"Posted")/COUNTIF(B2:B31,"<="&TODAY()))

The rate only counts days that have already arrived, so it doesn't punish you for the future.

If you want to note results, add Reach and Messages columns and fill them a day or two after posting. After a month, sort by Messages to see which themes start conversations.

Batch it

The calendar works best if you spend one hour a week on it: on Sunday, read the next seven rows, take the photos, write the captions, and set Status to Drafted or Scheduled. Then each day is just "post the thing".

Reusing it next month

When the 30 days are up, right-click the Calendar tab, Move or Copy > Create a copy, and rename the copy with the month (for example "Cal 2026-11"). On the new copy, change Settings!B1 to the next start date, clear the Status, Reach and Messages columns, and you're ready. If you keep notes of which caption numbers did well, overwrite the Caption no. column on the new copy to repeat the winners on different days. Old copies stay as a record, so you can see over a few months which themes and formats get replies and which ones you keep skipping.

Writing better fill-in captions

Captions with a question at the end ("Which one would you pick?") tend to start more conversations than plain announcements. Keep each caption short, put the blank where the product name goes, and write two or three versions for each theme so the calendar doesn't repeat itself every week. Twenty captions per theme is plenty to start.

Common mistakes

1. Typing dates by hand. One start date plus formulas means you can reuse the calendar every month.
2. Too many platforms. Start with one or two you actually use.
3. Hashtags pasted the same every day. Keep a few sets per theme in Settings and rotate them.
4. Deleting last month. Copy the sheet first; you can reuse what worked. This article was drafted with AI tools. It's general guidance, not marketing or legal advice.