Free Online Microsoft Training

Free tips and tricks for using Microsoft Office and Windows

Free Online Microsoft Training

Free tips and tricks for using Microsoft Office and Windows

How to create appointments in your Outlook Calendar from dates in Excel

Print Friendly, PDF & Email

Recently I was asked about how to automatically create calendar appointments in Outlook using dates in an Excel workbook. This was something I’d not been asked before and it peaked my curiousity. I did some research online around the idea of using Power Automate, and unfortunately found no options. A few weeks later it was still bugging me and I remembered that sometimes we (me) overcomplicate things. We sometimes jump to the latest and greatest functions (Power Automate or AI) to try to achieve things, when if we break it down into simple terms, we may find the answer has been available the whole time.

Part of Microsoft Outlook is the ability to import or export content. I’ve performed this task a thousand times in the past through my IT days of tech support. Importing and exporting PST files in Outlook was something I did every other day. It finally dawned on me, I can just import the Excel data straight into Outlook.

So let’s now look at the steps involved and how you can easily import information, as an appointment, into your Outlook Calendar.

Create the CSV file

In order to make the process of importing the data into Outlook a little smoother, there are some steps you can take within the Excel file. This generally relates to the data format and column headings. Considering we will be creating appointments in Outlook, we need to ensure the date and time formats are correct so that when we import the information into Outlook, it will be able to easily work out what date and time the appointment is.

At a minimum each appointment will need a Subject, Start Date, Start Time, and End Time. This is the basic information we enter when we create an appointment ourselves so it needs to be available in the data we import.

If you are starting with a normal Excel workbook (.xlsx), you will need to save the data as a CSV file to allow Outlook to import it correctly. Outlook cannot directly work with a .xlsx file.

To create the CSV file:

  1. Open Microsoft Excel.
  2. Open the Excel workbook which contains the appointment details you wish to use. If you do not have an existing file, create a simple workbook such as the one below:
  1. If you have an existing workbook but do not have the same headings you will simply need to match up the fields later in the process. If you want to simplify the process, you can rename your columns so that Outlook can easily identify which column contains the information it needs to create each appointment.
  2. To save the Excel data to a different file format, click the File tab and choose Save As or press F12 on the keyboard.
  3. Select a file location to save the file to, make it easy to access later, such as the Desktop.
  4. Give the file a suitable File name.
  5. In the Save as type drop-down menu, change the type to CSV (Comma delimited) (.csv).
  6. Click Save.

The Excel data has now been saved as a CSV file which will allow us to import the data into the Outlook Calendar.

Import Data to the Outlook Calendar

Now that the data file has been created and is in a format that Outlook can use, we can import the data into Outlook and have the appointments automatically created for us. It doesn’t matter if you need it to create 5 appointments or 50, this process will take a few minutes and save endless hours if you were to create these manually.

If you’d like to test this function first, without affecting your main Outlook Calendar, you can create a new Calendar for testing purposes. Simply display your Calendar and then right mouse click on the calendar name. Choose New Calendar. Give the calendar a Name and click OK.

  1. Open Microsoft Outlook.
  2. Open your Calendar.
  3. Select the Calendar you wish to add the appoinemtns to.
  4. Click the File tab and choose Open & Export.
  1. Click the Import/Export button.
  2. The Import and Export Wizard will appear:
  1. The default option is Import from another program or file. Leave this selected and click Next.
  2. The next option is to select the file type, leave it as Comma Separated Values and click Next:
  1. The Import File screen will appear. Click Browse and locate the CSV file you created.
  2. The file path will be displayed:
  1. From the Options section you can choose how you want to manage any duplicate items. After the initial import, you may need to import the data on a regular basis so that any new dates added to the Excel file are created as new appointments. For those instances, ensure that you choose Do not import duplicate items so that any appointments you have already imported are not created multiple times in your calendar.
  2. Click Next.
  3. The Select destination folder screen will appear. Choose the Calendar you wish to import the appointments into.
  4. Click Next.
  5. The next screen will provide a list of actions that will be performed. More specifically it will list the Import filename.csv into folder…
  6. Tick the box to select the action.
  7. Now you need to check that the fields from the Excel file have mapped correctly to the fields required when creating an appointment.
  8. Click Map Custom Fields.
  9. The Map Custom Fields window will appear:
  1. The left column will display the fields (or column headings) from the Excel file.
  2. The right column will display the field names used in an Outlook Appointment.
  3. You will notice in my example, Outlook has not identified the Subject field correctly from the Excel file and has not mapped it to the Appointment. The Subject field in the right column shows nothing in the Mapped from column.
  4. If a value, or field name has not mapped across correctly, simply click it from the left hand column, and drag it onto the corresponding field in the right column to map the fields together.
  5. Once all fields are mapped click OK.
  6. Click Finish to complete the data import.
  7. The appointments will now be displayed in the calendar.

Import more data

What happens when more dates are added to the Excel file in the future? You will need those new dates also added to your Calendar? The import function in Outlook provides several options on how to deal with duplicate records. This means you can tell Outlook to only import the new data from the CSV file.

To ensure you do not import duplicate items:

  1. Open the Excel file which has been updated with new data and new appointments which need to be created.
  2. Repeat the step as above and press F12 to Save As.
  3. Save the file again as a CSV format.
  4. Go back to Outlook.
  5. Repeat the steps and go to the File tab and start the Import/Export process.
  6. Select the CSV file you just created but this time check the setting for the Options section.
  1. Because the CSV file contains all the original data you imported, PLUS new data, we do not want to create the original dates as appointments again.
  2. Select Do not import duplicate items.
  3. Click Next.
  4. Repeat the steps to tick the Import function and then Map Custom Fields.
  5. Complete the import.
  6. The new appointments will be created in the calendar, the original appointments will remain unchanged and will not have multiple copies created.

That’s it! I hope this has helped solve a problem and allowed you to easily create appointments in your Outlook Calendar from a list of details in an Excel workbook. Comment below with any questions.

Facebook
Twitter
LinkedIn
Pinterest

Leave a Reply

Your email address will not be published. Required fields are marked *

3 + 20 =

This site uses Akismet to reduce spam. Learn how your comment data is processed.

.pf-button-img { padding-top: 20px; padding-bottom: 20px; }