It’s not unusual to receive an Excel sheet with a list of appointments or tasks. But if you use Outlook to keep up with your obligations, a list in Excel isn’t all that helpful. To make the data more useful, you could manually enter each record as a new appointment into Outlook, or you can import the Excel data directly into Outlook as appointments. For efficiency’s sake, I’d choose the latter!
SEE: The Complete Microsoft Office Master Class Bundle (TechRepublic Academy)
In this tutorial, I’ll show you how to create Outlook appointments from a few Excel records. By saving the source data in the comma-delimited format and then mapping that data to Outlook fields, you can import most data into the appropriate Outlook item with little effort. If you need to move data from Excel to Outlook regularly, you should consider saving time by automating the process.
For this tutorial, I’m using Microsoft 365 Desktop on a Windows 10 64-bit system. You can work with your own Excel data or download the simple demonstration .xlsx file. Please note that Outlook.com does not support this technique.
Use cases for importing Excel data to Outlook
Many users store contact and appointment data in an Excel sheet. There are a few reasons why your data might start out in Excel:
- The person tracking the information may not know how to use or feel comfortable using Outlook.
- Excel offers functions that can calculate end dates, times and other values that users might want.
- An organization’s hierarchy of business processes might require the data to live in Excel first.
- Users might not have access to Outlook outside of basic work parameters
With today’s latest sharing and collaborating features, as well as the growing emphasis on remote workforces, this last Excel data silo is less of a problem than it once was. However, in all of these cases, users who want or need to access this data in Outlook will need to figure out a way to import it from Excel. Fortunately, it’s very easy to move data to Outlook. We’ll cover how to do this throughout the rest of this tutorial guide.
Preparing the Excel data
In older versions of Outlook, you can import directly from an Excel workbook file, but that’s no longer the case in the most recent versions. Before you can import Excel data into Outlook using Microsoft 365, you must save that data to comma-separated (delimited) format, or .csv.
SEE: How to quickly import a .txt or .csv file into Microsoft Excel (TechRepublic)
To illustrate this process, we’ll use the simple data set shown in Figure A. You’ll see that there’s meaningful text to identify the appointment, the appointment date and time values. The dates and times can be strings or Date/Time values because Outlook is very flexible.
The demonstration .xlsx file contains only one sheet because that’s all the .csv feature supports. Excel won’t allow you to save a multiple-sheet workbook to .csv format. If you try, Excel will display an error message.
The demonstration sheet uses a Table object to organize the data, but you could also use a normal data range. Even though the records are sorted by date, Outlook doesn’t require a sorted data set for this technique to work.
Now, let’s save this sheet to .csv format as follows:
1. Click the File tab and choose Save As in the left pane.
2. Change the file name, if necessary, but Excel won’t write over your Excel workbook file; it will instead create a new .csv file.
3. From the File Type dropdown, choose the CSV (Comma delimited) (*.csv) option (Figure B). The import task will fail if the .csv file is open. Use the UTF-8 version if you’re importing special characters.
4. Click Save. You can ignore the possible data loss error if shown. Be sure to note the location of the file if there’s any chance you won’t remember where it is.
5. Close the .csv file you just created. Outlook won’t import the data if the .csv source file is open.
With the Excel data in a .csv file, you can move on to importing that data into Outlook.
Importing the data into an Outlook calendar
Now it’s time to open Outlook and prepare to import the records in the .csv file. You will be importing them into an Outlook calendar using a wizard. During this process, you will map the Excel values to Outlook appointment fields.
By map, I mean to connect one Excel field to one Outlook field. You’re telling Outlook where to store the Excel values within Outlook. An Outlook field is similar to an Excel cell, but it’s much more involved. It has a name, a data type and other identifying features. Outlook even supports user-defined fields.
To launch the wizard, open Outlook if necessary and then click the File tab, choose Open & Export in the left pane and then click Import/Export from the Open section shown in Figure C.
Now, run the wizard as follows:
1. In the wizard’s first pane, choose Import From Another Program Or File (if necessary) and click Next.
2. Choose Comma Separated Values and click Next.
3. Click the Browse button and work through the folder structure to select the .csv file you created earlier. Figure D shows the demonstration file.
4. Click OK after selecting that file, and the wizard returns to the same pane, which also lets you determine how Outlook deals with duplicates in the .csv file. There won’t be any in our example, but when applying this to your own work, you’ll want to choose appropriately. For now, accept the default option, Allow Duplicates To Be Created, and click Next.
5. Select calendar (Figure E) and click Next. If you have more than one calendar, be sure to select the right one.
6. This next step is a bit buggy, so pay extra close attention. The wizard will show the file to import. It’s not obvious, but the square to the left of the file name is a checkbox. You must click it to display the checkmark. If you don’t see this check (Figure F), the wizard won’t go any further. Once you click it correctly, the wizard might open the mapping dialog automatically. If that happens, you don’t need to click Map Custom Fields as described in the next step.
7. Click where it says Map Custom Fields. This step may not be necessary, depending on the outcome you had during step six.
8. At this point, you must match the Excel values to an Outlook appointment field. We mentioned earlier that this process is called mapping. The left pane lists the Excel fields, while the right pane lists the calendar fields. As you can see in Figure G, Outlook maps three of the fields correctly for you: Start Date, Start Time and End Time. That’s because the Excel field names are the same as the Outlook field names.
9. There’s no equivalent to the Task field, so you must map this field manually. To map the Task field, simply drag it from the left pane to the right pane (Figure H) and map it to the Outlook Subject field. You may need to repeat this step for any other fields that did not map automatically.
10. When you’ve mapped all the fields, click OK and then Finish to trigger the import. Figure I shows the newly created appointments.
There are two things worth noting at this point. First, did you notice the phantom character in Task in the mapping dialog? If you see something like this, it will almost always be in the first field. It’s a formatting code that Excel failed to remove while converting to .csv. You can ignore it. If you do have trouble importing after mapping, try opening the .csv file and re-entering the field names.
Second, if you import the same Excel records, Outlook will duplicate the appointment. If you change the duplication option shown in step four to Do Not Import Duplicate Items, it won’t do this.
SEE: Microsoft Excel from Beginner to Advanced: Crash Course (TechRepublic Academy)
Can you link an Outlook calendar to Excel?
Unfortunately, there’s no native process for creating a dynamic link between Excel and Outlook. As you update the Excel file, you must repeat the process outlined in this article. However, if this happens often enough, you can use Microsoft Power Automate to create a flow that will automate the process.
To learn more about Power Automate, you can read the following articles:
This article has a specific goal: To import Excel data into an Outlook appointment. Using .csv data and Outlook’s wizard, you can import lots of different data into different Outlook items. For a list of the most commonly used Outlook appointment field names, take a look at the table below. This will be particularly useful for power users of Microsoft Office products.
This table is not comprehensive by any means, but it is a helpful starting point for users who want to move data from Excel to Outlook. Outlook stores values with a Yes/No data type as -1 and 0, respectively.
Most commonly used Outlook appointment field names
|Field||Data type and meaning|
|All Day Event||Yes/No. If set to Yes, the Duration field is set to 24 hours (1440 minutes).|
|Created||Date/Time. Date and time the calendar item is created.|
|Duration||Duration stored as minutes. 24 hours (1,440 minutes) if the All Day Event field is set to Yes. Otherwise, the difference between the values of the End and Start fields. Saved as minutes.|
|End||Date/Time. End date and time of a calendar item.|
|Modified||Date/Time. Last time the calendar item was modified.|
|Notes||Text. Value of the text box of the appointment.|
|Read||Yes/No. Specifies whether the calendar item is marked as read.|
|Recurrence||The following settings apply: 0 None, 1 Daily, 2 Weekly, 3 Monthly, 4 Yearly|
|Recurrence Pattern||Text. Combination of the values of the Recurrence, Start and End fields.|
|Recurrence Range End||Date/Time. Last date and time of a recurring calendar item.|
|Recurrence Range Start||Date/Time. First date and time of a recurring calendar item.|
|Recurring||Yes/No. Specifies whether the calendar item recurs.|
|Remind Beforehand||Number. Minutes before the reminder runs prior to a meeting or appointment.|
|Reminder||Yes/No. If the start time for the meeting or appointment has already passed, the Reminder field cannot be set.|
|Reminder Sound File||Text. Path of the sound file to play as a reminder.|
|Text. Path of the sound file to play as a reminder.||The following settings apply: 0 Free, 1 Tentative, 2 Busy, 3 Out of Office|
|Start||Date/Time. Start time of a calendar item.|