If you're looking to create a beautifully organized weekly planner with dynamic date, day, and holiday data, then this tutorial is perfect for you! In this guide, I’ll show you how to use the Data Merge feature in Affinity Publisher to automatically populate your planner layout with information from an external data source, such as an Excel file.
Let’s get started!
Step 1: Prepare Your Data in Excel
The first step is to prepare the source data. For this tutorial, I’ve created a simple Excel file with four fields:
- Short Date
- Long Date
- Day
- Holiday
The “Short Date” is the date written in the format “dd/mm/yyyy”, while the “Long Date” shows the full month name. The "Day" field contains the day of the week, and the "Holiday" field is for any special dates, such as national holidays or personal events like birthdays.
If you're working with a weekly planner, you’ll need to make sure your data spans at least seven days, including any necessary spillover from one month into another. For example, if your planner week starts on a Monday and ends on a Sunday, but crosses over into the next month, you’ll need to include those extra days.
Excel Tip
Here’s a quick Excel tip: rather than manually entering all your dates, you can automate it. Simply enter your starting date (e.g. 30th September 2024) and use a formula in the next cell to add +1 to the previous date. This way, if you ever need to change the start date, the rest of the dates will update automatically.
Consistent Date Formatting
To format the “Long Date” and “Day” fields in a way that Affinity Publisher can read consistently, use the TEXT function in Excel. This ensures your dates appear correctly, regardless of regional formatting settings on your computer. It gives you complete control
For example:
- UK Long Date Format:
=TEXT(A3, "d mmmm yyyy") - US Long Date Format:
=TEXT(A3, "mmmm d, yyyy") - Long Day Format:
=TEXT(A3, "dddd") - Short Day Format:
=TEXT(A3, "ddd") - Day Number Format:
=TEXT(A3, "d")
Step 2: Set Up Your Layout in Affinity Publisher
Now that your data is ready, let’s set up the planner layout in Affinity Publisher. If you haven't already created your design, you can start with a simple vertical weekly layout with seven days in a column format.
To connect your data, go to Window > Data Merge Manager. In the lower left-hand corner, click the Add Data Source button and locate your Excel file. Once selected, the file won’t open like a typical document; instead, it becomes available as a Data Source in Affinity Publisher.
Step 3: Add a Data Merge Layout
With your data connected, it’s time to add placeholders for your merged data.
- Select the Data Merge Layout Tool from the tools panel.
- Draw out a frame in your layout where you’d like your data to appear. Since we’re working with a vertical planner, I’ll draw a single row on the left-hand page with three columns for Monday, Tuesday, and Wednesday.
- You’ll notice that Affinity Publisher defaults to two rows and two columns. Adjust this to match your layout by reducing it to one row and increasing it to three columns.
Step 4: Insert Your Data Fields
Next, we’ll insert the actual data into your layout.
- Open the Fields Panel and look for the fields you created in Excel (e.g. Long Date, Day, and Holiday).
- Add the Long Date by double-clicking it. This adds the Long Date to the first column, and it will automatically replicate across the other two columns.
- Centre the text by selecting the text box and clicking the Centre Alignment button.
If your text doesn’t seem to align perfectly within the columns, adjust the Gutter (space between columns) settings. You can adjust this to ensure the data sits neatly within each column.
Step 5: Formatting the Data
After inserting the Long Date, you can tweak the appearance of the text. You might want to reduce the font size for the Holidays, or move elements to fit your design better. For the Holidays, I like to use a smaller font size (e.g. 6pt) and place it just above the long date.
To achieve precise control over your layout, you may prefer to use separate text frames for each data field. This allows you to move and format each field individually, giving you more flexibility in your design.
Step 6: Adding the Day
To make the planner headings stand out, let’s add the day name in white text across the dark header area.
- Draw a new text frame in the header area for each day.
- Centre the text, then add the Day field from your data source.
- Change the text colour to white so it’s visible against the dark header background.
Step 7: Preview Your Data Merge
Before finalising the merge, it’s important to preview how the data will look across all pages.
- Save your layout.
- Open the Data Merge Manager and update the data to make sure you have the most recent version.
- Click Generate to run the merge.
Once generated, you’ll be able to preview the full weekly layout with all dates, days, and holidays in place.
Step 8: Fine-Tuning and Finalizing
After previewing the merged file, you may notice areas that need adjustment. For example, the text size might need increasing, or some elements may need repositioning. Make any necessary changes in the layout and re-run the merge to see the updates.
If you’re happy with the layout, you can now export your planner or make additional changes. One useful feature of Affinity Publisher is that it retains the Symbol functionality, allowing you to make changes across all pages simultaneously by editing just one Symbol instance.
Conclusion
By following these steps, you can easily automate the process of adding dates, days, and holidays to your planner design in Affinity Publisher. Not only does this save time, but it also allows you to quickly update or customise your planner as needed.
And there you have it! You’ve adding the ability to customize a static Vertical Weekly Planner Layout using a Data Merge. If you enjoyed this tutorial and want more tips and tricks, be sure to check out my free training at ElaineGiles.com/VIP. Don’t forget to subscribe and hit the notification bell so you never miss a new tutorial.
If you have any specific tutorial requests, feel free to reach out - I’d love to hear from you!
Thanks for reading and watching, and see you next time!
