Contextures

Excel Holiday Planner

This Excel Holiday planner will help you stay organized. It has sheets for a gift list, holiday budget, holiday tasks, dinner planning, and many other holiday essentials.

Introduction - Excel Holiday Planner

This year, don't plan your Holiday activities and spending on scraps of paper -- use Excel instead. Download the free workbook below, or follow the instructions to build your own planner

In Excel, it's easy to type a few lists, then sort them, edit them, get a total amount, make changes, and print the final lists. Creating a Holiday Planner in Excel is quick and easy, and you'll love how organized you are!

In an Excel workbook, you can add as many sheets as you need to track gift buying, holiday tasks, your Holiday budget, and other activities. After the sheets are added, you can make a cover sheet with a link to all the planning sheets.

Thanks to Sarah Stoski for contributing to this Holiday Planner.

Step 1: Add the Planning Sheets

Decide what you want to keep track of over the holiday season. For example:

  • Holiday Events
  • Holiday Tasks
  • Gift List
  • Holiday Budget

Then, open a new Excel workbook, and insert a few more sheets, to get the number that you need:

  • one sheet for each thing you want to track
  • one sheet at the start, for a cover.

To insert a sheet, use the keyboard shortcut -- Shift + F11

Or, click the New Sheet command at the far right of the sheet tabs.

insert worksheet

Step 2: Name the Planning Sheets

To make it easy find your lists, change the sheet names from Sheet1, Sheet2, to something meaningful, such as Holiday Tasks.

Note: When you have lots of sheets in a workbook, you might not be able to see all of them. Click the arrows at the far left of the sheet tabs, to scroll through the sheet tabs, or to go to the first or last sheet.

To rename a sheet:

  1. Double-click on the sheet tab.
  2. Type a new name (up to 31 characters long)
  3. Press the Enter key to complete the name.

Step 3: Create a Holiday Gift List Sheet

To help you plan and track your gift spending, you can add a Master Gift List worksheet. This is much easier than keeping track on paper, and adding things up with a calculator!

  1. Click on the sheet tab where you want to create a list of gifts.
  2. Starting in Row 3, type headings for the columns where you can enter people's names, gift ideas, mark the gifts you've sent, and enter the planned cost and actual cost.
  3. In Row 1 you can create a SUM Formula, to show a total of costs for all the gifts in the list.
  4. Format the list as a named Excel table, so it will expand automatically as you add new items.

master gift list sheet

Step 4: Create a Holiday Task List

You could also create a Holiday Task List in a sheet in your Holiday Planner.

  1. Click on the sheet tab where you want to create a list of gifts.
  2. In Row 3, type the headings, such as Weeks Ahead, Done, Category, Task Description.
  3. Format the list as a named Excel table, so it will expand automatically as you add new items.
  4. Then, starting in Row 4, type your tasks: In the Weeks Ahead column, type the number of weeks before Holiday that the task should be started.
  5. In the Done column, type an X when the task is completed.
  6. In the Category column type the category, such as Gifts, or D�cor
  7. In the Task Description column type the name of the task, such as Decorate the tree.

task list

Step 5: Create the Remaining Sheets

Now that you know how to create planner sheets, you can add any other planner sheets that you need. Download the sample Holiday Planner, below, to see other sheet examples.

For example, make a Holiday Card list with columns for Name, Street, City, State, Zip, Sent and Received.

Using the Sample Holiday Planner

Instead of building your own Holiday Planner in Excel, you can use the sample file that I created. Details and instructions for some of the worksheets are shown below.

To learn more about the Excel techniques used in the workbook, see the tutorials on named Excel tables, Conditional Formatting, Data Validation and Sorting.

Custom Ribbon Tab

When you open the Holiday Planner workbook, a custom tab (PLAN) appears on the Excel Ribbon, to the right of the Home tab.

Click the buttons on the PLAN tab, to go to the sheets in the planner workbook.

custom tab on Excel Ribbon

For more information on creating a custom tab, go to the Excel Ribbon Getting Started page

Holiday Calendar

On the Start sheet, enter the year, holiday date, and number of weeks that you want to plan. The Holiday Planner originally started out as a Christmas planner, but now you can use any date as the focus for your planning

holiday planner start sheet

Then, enter your upcoming activities on the Events sheet, and those items will appear on the Calendar sheet -- up to 5 events per date.

Your holiday's date is highlighted with a red fill colour, and the first day of each month is highlighted in green.

holiday calendar shows events

Dinner Planner

There's a worksheet where you can enter your holiday dinner time, and Excel will calculate the start time for each dinner preparation step. For instructions on using this sheet, see the Holiday Dinner Planner page.

Excel Dinner Planer

Holiday Budget

To help you stay on budget over the holidays, there's a Holiday Budget worksheet. Enter the estimated amount that you plan to spend on holiday expenses, such as gifts or travel.

Don't enter the Amount Spent or Amount Remaining -- those are calculated from your entries in the Holiday Spending sheet.

You can see the totals at the top of the worksheet.

holiday budget sheet

Holiday Spending

To help you track your spending (except for gifts, which are entered on a separate sheet), there's a Holiday Spending worksheet. Enter the date, category, item and amount for each expense. Use the same categories that you entered on the Holiday Budget sheet.

The total for each category is calculated, and shown on the Holiday budget sheet, using a SUMIF formula.

You can see the totals at the top of the worksheet.

holiday spending sheet

Holiday Summary

The Home sheet has a summary of your budget (planned and spent), gift purchases (needed and purchased, days until the holiday, and number events in the current week.

holiday summary

Download the Workbook

To see my example file, download the Excel Holiday Planner workbook. The zipped file is in xlsm format, and contains macros that navigate to the worksheets.

Use this workbook to get started, then add your own tasks and information, and other sheets that you might need.

 

 

Get weekly Excel tips from Debra

 

 

30 Excel Functions in 30 Days

 

 

Excel Tools add-in

 

Last updated: January 18, 2018 2:59 PM