Cell PhonesComputersConsumer ElectronicsGraphic Design & Video EditingHome Theater & AudioIndustrial TechnologyInternet

How To Import a Calendar From Excel to Outlook

Updated on April 28, 2016
Outlook Calendar
Outlook Calendar | Source

Do you use the Calendar in Outlook?

See results

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.
  • Sensitivity

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.

Control Tab in Excel
Control Tab in Excel | Source

Control Tab

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.

  1. 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.
  2. Rename one of the tabs “Control” by right-clicking on a tab and selecting “Rename”.
  3. 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”.
  4. 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.
  5. I decided to format my tables by shading them and adding a border around them, but that is totally up to you.

Upload Tab in Excel
Upload Tab in Excel | Source

Upload Tab

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.

  1. In cell A1, title the template as “Calendar Upload Template” and format it as bold.
  2. 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.
  3. 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.
  4. Format the “Start Date” and “End Date” columns as a short date from D5:E27.
  5. Format the “Start Time” and “End Time” columns as time from F5:G27.
  6. 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.
  7. 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.
  8. 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.

Import Steps for Outlook
Import Steps for Outlook | Source
Softball game appointment loaded into Outlook using the Calendar Upload Template
Softball game appointment loaded into Outlook using the Calendar Upload Template | Source

Importing a Calendar into Outlook

Importing a calendar into Outlook from Excel is easy. Here are the step by step instructions:

  1. Click the calendar button in Outlook.
  2. 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

Comments

    0 of 8192 characters used
    Post Comment

    • M K Paul profile image

      M K Paul 4 years ago from India

      useful information..voted up.

    • Simone Smith profile image

      Simone Haruko Smith 4 years ago from San Francisco

      I've never had to do this before, but I have known folks who have struggled with this process in the past. If only I could have sent them to this guide!

    • profile image

      Devin 4 years ago

      This is awesome! Only issue I have right now is that Reminders are not turned on for any of my calender items. I have searched the internet and have not come up with an easy way to have them all turned on. Can you help me?

    • ercramer36 profile image
      Author

      Eric Cramer 4 years ago from Chicagoland

      To turn the reminders on add the following columns:

      Reminder On/Off

      Reminder Date

      Reminder Time

      Reminder on/off is either True or False. Let me know if you have any questions or need any more help.

    • profile image

      Devin 4 years ago

      I have those columns in my spreadsheet and have reminder set to true and the reminder still remains off in outlook.

    • profile image

      Chuch 4 years ago

      Thanks very much! Three other web guides never mentioned that the "Reminder On/Off" cells had to be set to True or False. I'm finally able to import reminders.

    • profile image

      Lisa 4 years ago

      How caln you set the 'Show As' optin to free or busy?

    • ercramer36 profile image
      Author

      Eric Cramer 4 years ago from Chicagoland

      Add a column called "Show Time As". Outlook offers the following 4 choices: 1. Tenative, 2. Busy, 3. Free, and 4. Out of Office. Enter the corresponding number into each row that you want and it will upload into Outlook. Let me know if you have any more questions.

    • profile image

      Lisa 4 years ago

      Thank you for the great information. Another question, I am a delegate to share several other calendars. Am I able to import into other shared calendars from my Outlook calendar?

    • ercramer36 profile image
      Author

      Eric Cramer 4 years ago from Chicagoland

      I think each user has to upload their own appointments. I will try to research if it is possible.

    • profile image

      Oceanas 4 years ago

      do you know how i change the reminder prompt? currently the reminder prompt is set at 15 min.

      how do i change this?

    • profile image

      Texas Tina 3 years ago

      This was tremendously helpful and I am grateful this site was so easily found. One note is that whenever you place new items into your Excel spreadsheet, if the number of rows is different you need to remove the "Upload" designation in the Name Box via the Formulas tab/Name Manager, and then repeat the instructions to highlight the section and name it "Upload" in the Name Box. Thanks!

    • ercramer36 profile image
      Author

      Eric Cramer 3 years ago from Chicagoland

      Thanks Texas Tina for the great tip!

    • profile image

      cfieser 3 years ago

      Is there a way to add recurrence to the events?

    • ercramer36 profile image
      Author

      Eric Cramer 3 years ago from Chicagoland

      cfieser - unfortunately Microsoft did not create a way to add reocurring events when uploading calendar appointments. The only thing that you can do is to upload the events and then manually edit them to reoccur.

    • profile image

      3 years ago

      Confused

    • profile image

      D54 3 years ago

      I tried this and it worked great. It was however a test and now I don't know how to remove the excel data from my outlook calendar...any help is appreciated.

    • Oscalv profile image

      Oscalv 3 years ago from Munich, Germany

      I have imported a whole year reporting calendar that contains 252 events. All of them are imported correctly but for those which description text is longer than usual the import is not taking the whole text. I have tried doing the import with less items 21 and still is not taking the full text. I tried with 12 and it takes the whole text. Does anybody know what the problem is? and whether is possible to find a solution so I can import as many description text and as many events as necessary without restrictions?

    • ercramer36 profile image
      Author

      Eric Cramer 3 years ago from Chicagoland

      D54 - Thanks for your comments. The only way that I have found to remove the data from Outlook is to manually delete it.

    • profile image

      Sheresa 2 years ago

      Hi there! I just went through this whole process with about 6 entries in my table - and only one of them is not showing up and I can't figure out why. Would love some feedback! Thank you!

    • profile image

      AD 2 years ago

      Thanks :)

    • Kristen Howe profile image

      Kristen Howe 23 months ago from Northeast Ohio

      Great tips, Eric. You make it sound easy to do without any hassles. Voted up for useful!

    • profile image

      Anand 10 months ago

      I can only see the option of csv. format and not Microsoft Excel 97-2003 format. I tried doing it with csv. file as well and completed the process but cant see anything on outlook. Please advice

    • profile image

      Meg 9 months ago

      Hi - I have just followed this process and imported the entries how ever found that none of the description has come up? Anyone able to tell me why that might be? I have checking the mapping and it shows up correctly there but when imported there is nothing. Exported it back out to .csv and it shows up again?

    • profile image

      And 9 months ago

      Very helpful thanks. I wonder if there's a way to import into shared (or team) calendars, as I'm currently only seeing an option to import into my own personal calendar?

    • profile image

      Roger B 8 months ago

      Fantastically clear instructions. However, when I get to “Import from another program or file” I don't get an XLS option - just CSV and PST. Is there another step I could take?

    • profile image

      Mark 8 months ago

      So what you're saying is, MS Office 2013 can't create a recurring business day event? Then if I used the suggested excel to create a virtual calendar, I can't share it either... meaaaaan, dis suks!

    • profile image

      Steve 5 months ago

      It appears Microsoft has removed the .xls import option in 2013

      & 2016 Outlook. Only .csv supported now, which omits the functionality of this awesome template...... thanks microsoft

    • profile image

      Prashant Kumar 3 months ago

      2010 outlook does not have option of excel file upload, they need CSV

    • profile image

      Maureen Galvin 3 months ago

      has anyone had any luck importing into a shared calendar? I have a file I would like to import and I'm listed as a publishing editor for the calendar, but I cannot "find" the calendar when I try to do the import. Thanks.

    • profile image

      Michael L 3 months ago

      Sorry, my Outlook 2016 does not list an Excel import option in the sub menue "import from another program or file".

      It lists only .csv or .pst - and that kills you:

      1. You have to export your Itinary from Excel into .csv.

      2. Then you have to convert with find an replace the semicolons into commas with the editor.

      3. Now you can import the .csv into outlook, BUT only into an .pst file based interim-calendar AND NOT into an exchange mailbox calendar.

      4. You have to open the interim-calendar and change to list view.

      5. ctrl-A and ctrl-C for copying the complete list with all dates.

      6. Now open the target exchange mailbox calendar, change to list view and....

      you might expect to get a PASTE option at the right click menue but there isn't.

      But relax, ctrl-V is working.

      How the f... is spelling of user friendlyness, Mr. Microsoft?????????

    • profile image

      S Adrine 2 months ago

      Unfortunately, this doesn't work in Outlook 2016. It only allows a *.pst or *.csv file to import.

    • profile image

      Mandy 3 weeks ago

      Will this delete previous appointments already on the calendar?

    Click to Rate This Article