How To Import a Calendar From Excel to Outlook
Do you use the Calendar in Outlook?
One of the great things about Microsoft Office products is the ease of which data can be moved from product to product. For instance, Excel can house a list of names and addresses that can be mail merged into Word as part of a standardized letter or as mailing labels. Microsoft Access can import from Excel as well as export data to Excel. Another cool way to transfer data from one Office product to another is to import a Calendar from Excel in Outlook.
Reasons to Import a Calendar from Excel to Outlook
Importing a calendar from Excel to Outlook does not make sense in every case. If you are entering one or two appointments, it is easier to enter them directly into Outlook. However, many times a business will send out a list of company-recognized holidays to their employees in an attachment to an email. It makes sense to either copy or retype those dates in an Excel calendar template and upload that information into Outlook. It is quicker to enter appointments into Excel because you can work on more than one appointment at a time. Entering people's birthdays also makes sense this way. Another great example is my oldest son is playing baseball this year and his schedule was emailed to us in Excel spreadsheet. I copied the information into my Excel template and imported it directly into Outlook.
Preparing to Create a Calendar Upload Template in Excel
Before we can create the calendar upload template, we need to know what fields Outlook uses in their calendars. Here is a list of the most commonly used fields that are available in Outlook:
- Subject* – This is what your appointment will appear as on your Outlook calendar.
- Location – This is the location of the appointment.
- Start Time
- Start Date*
- End Time
- End Date*
- All Day Event – This is a yes or no answer.
- Reminder On/Off – This is a yes or no answer.
- Reminder Date
- Reminder Time
- Categories – This is the appointment category.
- Description – This is the large area of text that can be entered into an appointment.
- Private – This is a Yes/No flag to determine if anyone else can see this event.
Any field with an * after is required. Now that we know what Outlook is looking for, we can move to build the calendar upload template in Excel.
How to Create a Calendar Upload Template in Excel
Setting up a template to upload a calendar from Excel to Outlook can be as easy or sophisticated as you like. Personally, I like to add features that require a little more work up front, but will save time in the end.
The purpose of the “Control” tab is to house data that will be used to create drop down menus on the “Update” tab. By entering this data here, it will make uploading calendar appointments easier.
- Open a new workbook in Excel and save it as “Calendar Upload.xls”. It is vitally important to save it as an “Excel 1997 – 2003 Workbook” because Outlook can only update the .xls file extension.
- Rename one of the tabs “Control” by right-clicking on a tab and selecting “Rename”.
- In cell B3, enter “All Day Event”. In cells B4 and B5, enter “Yes” and “No” respectively. Highlight B4:B5, and in the “Name Box” in the upper left hand corner of the screen and name that section “ADE”.
- In cell E3, enter “Categories”. Starting in E4, enter all of the categories that you use in Outlook. They can be found in Outlook by going to the calendar section and opening up a new appointment. Click on the “Categorize” icon and a list of all of the categories that have been set up will appear. The default categories are based on color. I would highly recommend renaming them something useful. You can add as many categories as you like, although, there are only 25 different colors in Outlook. Enter the categories into Excel and sort them alphabetically once you are finished entering them. It is critical that they are spelled the exact same way in Outlook and Excel. Highlight the entire area from E4 through the end of your list. In the “Name Box”, enter “Category” as the name for this selection.
- I decided to format my tables by shading them and adding a border around them, but that is totally up to you.
The “Upload” tab is where the calendar information is entered. It is important to at least to use the following fields: Subject, Start Date, End Date, Start Time, End Time, and All Day Event. Feel free to add whatever other fields that you routinely use.
- In cell A1, title the template as “Calendar Upload Template” and format it as bold.
- Starting in row 4, enter all of the fields that you want to use. Just because you add a non-required field, does not mean that you have to use it every time. I chose Subject, Location, Description, Start Date, End Date, Start Time, End Time, All Day Event, and Categories. Shade all of the fields one color and make them bold.
- Select and shade the area that you want to use to enter your calendar information into. I choose A5:I27. Put a thin border around it.
- Format the “Start Date” and “End Date” columns as a short date from D5:E27.
- Format the “Start Time” and “End Time” columns as time from F5:G27.
- In H5:H27, click on Excel’s Data tab and click on the Data Validation button. This will open a dialogue box. On the Settings tab, change the “Allow:” drop down box to “List”. In the “Source” box, enter “=ADE” (without the quotation marks) and click Ok. This will insert a drop down box that will give you the option of selecting yes or no.
- In I5:I27, click on Excel’s Data tab and click on the Data Validation button again. In the dialogue box that pops up, change “Allow:” to “List”. In the “Source”, enter “=Categories” (without the quotation marks) and click Ok. This will insert a drop down menu into each cell in this row that will allow you to select from the categories that you previously setup on the “Control” tab.
- Highlight A4:I27 and type “Upload” in to the name box.
Using the Calendar Upload Template
Now that the template is complete, enter or copy any appointment, holiday, vacation, or reminders into it. If you need more rows, insert them in the middle of the data entry area. When you are ready to upload the file, exit out of the spreadsheet in Excel.
Importing a Calendar into Outlook
Importing a calendar into Outlook from Excel is easy. Here are the step by step instructions:
- Click the calendar button in Outlook.
- Click on the “File” menu in Outlook and then select “Import and Export.” An Import and Export dialogue box will pop up. Select “Import from another program or file” and click “Next”. Select “Microsoft Excel 97-2003” and click “Next.” Click on the “Browse” button and find the “Calendar Upload Template.xls.” Click “Next” again. Select “Calendar” as the destination folder and click “Next”. Check the box next to “Import ‘Upload’ into folder: Calendar” and then click “Finished.”
Your calendar items should now appear in your Outlook calendar. Once you upload the information into Outlook, you can delete all of the appointment information in the spreadsheet so it will be ready for next time.
Remember to set the upload template up using the exact field names or otherwise you will have to manually map the fields out. If put a lot of things on your Outlook calendar, this is a fast and easy way to save time.
Outlook Calendar Tips and Tricks
© 2012 Eric Cramer