{"id":4005,"date":"2020-01-27T19:56:01","date_gmt":"2020-01-27T19:56:01","guid":{"rendered":"https:\/\/courses.lumenlearning.com\/wm-computerapplicationsmgrs\/?post_type=chapter&#038;p=4005"},"modified":"2025-10-30T16:58:06","modified_gmt":"2025-10-30T16:58:06","slug":"access-data-from-excel-table","status":"publish","type":"chapter","link":"https:\/\/courses.lumenlearning.com\/wm-computerapplicationsmgrs\/chapter\/access-data-from-excel-table\/","title":{"raw":"Access Data from Excel Table","rendered":"Access Data from Excel Table"},"content":{"raw":"<div class=\"textbox learning-objectives\">\r\n<h3>Learning Outcomes<\/h3>\r\n<ul>\r\n \t<li>Create Access data table from Excel data<\/li>\r\n<\/ul>\r\n<\/div>\r\nThere are scenarios where data has been created or stored in Excel and now needs to be added into an Access database. As an example, say that the original company accountant only knew how to create Excel spreadsheets. Now, however all company accounting information needs to be in an Access database. How would you do this?\r\n\r\nWe\u2019ll walk through importing data from Excel into Access so follow these steps.\r\n<ol>\r\n \t<li>Open the Excel workbook and look through the data to:\r\n<ol style=\"list-style-type: lower-alpha;\">\r\n \t<li>Clean up the data if there are errors or gaps in the data fields.<\/li>\r\n \t<li>Use the <strong>Data Tools<\/strong> group, <strong>Remove Duplicates<\/strong> button to eliminate any potential make duplicate information that may being exported.<\/li>\r\n \t<li>Check to see that columns have headings and are consistent data types (e.g. all currency, percentage, etc.) and correct if necessary.\r\n<img class=\"alignnone wp-image-4028 size-full\" src=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/3008\/2020\/01\/27194943\/Module_15_Access_ExportExcel_11.png\" alt=\"Excel spreadsheet screenshot of table data checking for duplicates window.\" width=\"1408\" height=\"820\" \/><\/li>\r\n<\/ol>\r\n<\/li>\r\n \t<li>Save and close the Excel worksheet and open Access.<\/li>\r\n \t<li>In the <strong>External Data<\/strong> tab, click the <strong>New Data Source<\/strong> button, <strong>From File, Excel<\/strong> option.\r\n<img class=\"alignnone wp-image-4029 size-full\" src=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/3008\/2020\/01\/27195005\/Module_15_Access_ExportExcel_1.11.png\" alt=\"Access screenshot of External Data tab, New Data Source, From File, Excel button highlighted.\" width=\"1056\" height=\"656\" \/><\/li>\r\n \t<li>The Get External Data \u2013 Excel Spreadsheet window opens. Find the excel file you wish to import into Access using the Browse button.<\/li>\r\n \t<li>Next is deciding where to store the imported data. The three options are:\r\n<ol style=\"list-style-type: lower-alpha;\">\r\n \t<li>A new table in current database,<\/li>\r\n \t<li>Appending a copy of the records in an existing table, or<\/li>\r\n \t<li>Linking to the data source by creating a linked table.<\/li>\r\n \t<li>For this example choose to create a new table.\r\n<img class=\"alignnone wp-image-4031 size-full\" src=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/3008\/2020\/01\/27195157\/Module_15_Access_ExportExcel_1.22.png\" alt=\"Access screenshot of Get External Data - Excel spreadsheet wizard window open.\" width=\"748\" height=\"549\" \/><\/li>\r\n<\/ol>\r\n<\/li>\r\n \t<li>The Import Spreadsheet Wizard is now opened, and the Excel table is displayed. Make sure the checkbox for First Row Contains Column Headings if that is the case. Click the Next button.\r\n<img class=\"alignnone wp-image-4032 size-full\" src=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/3008\/2020\/01\/27195214\/Module_15_Access_ExportExcel_1.31.png\" alt=\"Access screenshot of Import spreadsheet wizard window open containing data.\" width=\"748\" height=\"542\" \/><\/li>\r\n \t<li>The next wizard window allows you to be specific about the field information being imported by modifying the field information in the <strong>Field Options<\/strong> area. For our purposes the defaults work.\r\n<img class=\"alignnone wp-image-4033 size-full\" src=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/3008\/2020\/01\/27195235\/Module_15_Access_ExportExcel_1.4.png\" alt=\"Access screenshot of Import spreadsheet wizard window open containing data.\" width=\"748\" height=\"549\" \/><\/li>\r\n \t<li>The next wizard window allows you to define a primary key for the new table. Primary keys uniquely identify every record in a table to be able to return data quickly. For this example, we\u2019ll let Access add the primary key.\r\n<img class=\"alignnone wp-image-4034 size-full\" src=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/3008\/2020\/01\/27195301\/Module_15_Access_ExportExcel_1.5.png\" alt=\"Access screenshot of Import spreadsheet wizard window open containing data.\" width=\"748\" height=\"543\" \/><\/li>\r\n \t<li>Access is now ready to import the Excel file. There is a checkbox to select if you would like the table to be analyzed after the data is imported. It will remain unchecked for this example. Now click the <strong>Finish<\/strong> button.\r\n<img class=\"alignnone wp-image-4035 size-full\" src=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/3008\/2020\/01\/27195349\/Module_15_Access_ExportExcel_1.6.png\" alt=\"Access screenshot of Import spreadsheet wizard window open saying where to import to table and finishing wizard.\" width=\"748\" height=\"543\" \/><\/li>\r\n \t<li>Access now asks if you would like so save these import steps just walked through. If so, click the Save import steps checkbox. We\u2019ll leave it unchecked for now.\r\n<img class=\"alignnone wp-image-4036 size-full\" src=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/3008\/2020\/01\/27195429\/Module_15_Access_ExportExcel_1.7.png\" alt=\"Access screenshot of Import spreadsheet wizard window open asking if the steps should be saved for next time.\" width=\"744\" height=\"575\" \/><\/li>\r\n \t<li>The Excel information is now imported into Access and can now be use in Access or even added to as more Excel information is discovered.<\/li>\r\n<\/ol>\r\n<img class=\"alignnone wp-image-4037 size-full\" src=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/3008\/2020\/01\/27195458\/Module_15_Access_ExportExcel_1.8.png\" alt=\"Access screenshot of imported Excel spreadsheet customer contact data in new Access table.\" width=\"1057\" height=\"658\" \/>\r\n<div class=\"textbox tryit\">\r\n<h3>Practice Questions<\/h3>\r\nhttps:\/\/assess.lumenlearning.com\/practice\/414dd8e1-997d-497e-9925-a0edea13d207\r\n\r\nhttps:\/\/assess.lumenlearning.com\/practice\/d4935511-5d32-40a7-a986-0bec02666a98\r\n\r\n<\/div>\r\nNow that you\u2019ve seen how to import Excel data into a new Access table, watch this video that walks through the steps and demonstrates how to add Excel data to an existing Access table. While the video shows an older version of Word (Microsoft Word 2016), the same steps work in other versions of Word, including 2019 and 365.\r\n\r\n<iframe src=\"\/\/plugin.3playmedia.com\/show?mf=4369510&amp;p3sdk_version=1.10.1&amp;p=20361&amp;pt=375&amp;video_id=RelLvfilOnQ&amp;video_target=tpm-plugin-51ae9zk8-RelLvfilOnQ\" width=\"800px\" height=\"450px\" frameborder=\"0\" marginwidth=\"0px\" marginheight=\"0px\"><\/iframe>\r\n\r\nYou can also view a transcript for the video <a href=\"https:\/\/course-building.s3-us-west-2.amazonaws.com\/ComputerApps\/Transcripts\/HowToImportExcelDataIntoAccess_Transcript.txt\" target=\"_blank\" rel=\"noopener\">\u201cHow to Import Excel Data into Access\u201d here (opens in new window)<\/a>.","rendered":"<div class=\"textbox learning-objectives\">\n<h3>Learning Outcomes<\/h3>\n<ul>\n<li>Create Access data table from Excel data<\/li>\n<\/ul>\n<\/div>\n<p>There are scenarios where data has been created or stored in Excel and now needs to be added into an Access database. As an example, say that the original company accountant only knew how to create Excel spreadsheets. Now, however all company accounting information needs to be in an Access database. How would you do this?<\/p>\n<p>We\u2019ll walk through importing data from Excel into Access so follow these steps.<\/p>\n<ol>\n<li>Open the Excel workbook and look through the data to:\n<ol style=\"list-style-type: lower-alpha;\">\n<li>Clean up the data if there are errors or gaps in the data fields.<\/li>\n<li>Use the <strong>Data Tools<\/strong> group, <strong>Remove Duplicates<\/strong> button to eliminate any potential make duplicate information that may being exported.<\/li>\n<li>Check to see that columns have headings and are consistent data types (e.g. all currency, percentage, etc.) and correct if necessary.<br \/>\n<img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-4028 size-full\" src=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/3008\/2020\/01\/27194943\/Module_15_Access_ExportExcel_11.png\" alt=\"Excel spreadsheet screenshot of table data checking for duplicates window.\" width=\"1408\" height=\"820\" \/><\/li>\n<\/ol>\n<\/li>\n<li>Save and close the Excel worksheet and open Access.<\/li>\n<li>In the <strong>External Data<\/strong> tab, click the <strong>New Data Source<\/strong> button, <strong>From File, Excel<\/strong> option.<br \/>\n<img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-4029 size-full\" src=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/3008\/2020\/01\/27195005\/Module_15_Access_ExportExcel_1.11.png\" alt=\"Access screenshot of External Data tab, New Data Source, From File, Excel button highlighted.\" width=\"1056\" height=\"656\" \/><\/li>\n<li>The Get External Data \u2013 Excel Spreadsheet window opens. Find the excel file you wish to import into Access using the Browse button.<\/li>\n<li>Next is deciding where to store the imported data. The three options are:\n<ol style=\"list-style-type: lower-alpha;\">\n<li>A new table in current database,<\/li>\n<li>Appending a copy of the records in an existing table, or<\/li>\n<li>Linking to the data source by creating a linked table.<\/li>\n<li>For this example choose to create a new table.<br \/>\n<img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-4031 size-full\" src=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/3008\/2020\/01\/27195157\/Module_15_Access_ExportExcel_1.22.png\" alt=\"Access screenshot of Get External Data - Excel spreadsheet wizard window open.\" width=\"748\" height=\"549\" \/><\/li>\n<\/ol>\n<\/li>\n<li>The Import Spreadsheet Wizard is now opened, and the Excel table is displayed. Make sure the checkbox for First Row Contains Column Headings if that is the case. Click the Next button.<br \/>\n<img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-4032 size-full\" src=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/3008\/2020\/01\/27195214\/Module_15_Access_ExportExcel_1.31.png\" alt=\"Access screenshot of Import spreadsheet wizard window open containing data.\" width=\"748\" height=\"542\" \/><\/li>\n<li>The next wizard window allows you to be specific about the field information being imported by modifying the field information in the <strong>Field Options<\/strong> area. For our purposes the defaults work.<br \/>\n<img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-4033 size-full\" src=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/3008\/2020\/01\/27195235\/Module_15_Access_ExportExcel_1.4.png\" alt=\"Access screenshot of Import spreadsheet wizard window open containing data.\" width=\"748\" height=\"549\" \/><\/li>\n<li>The next wizard window allows you to define a primary key for the new table. Primary keys uniquely identify every record in a table to be able to return data quickly. For this example, we\u2019ll let Access add the primary key.<br \/>\n<img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-4034 size-full\" src=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/3008\/2020\/01\/27195301\/Module_15_Access_ExportExcel_1.5.png\" alt=\"Access screenshot of Import spreadsheet wizard window open containing data.\" width=\"748\" height=\"543\" \/><\/li>\n<li>Access is now ready to import the Excel file. There is a checkbox to select if you would like the table to be analyzed after the data is imported. It will remain unchecked for this example. Now click the <strong>Finish<\/strong> button.<br \/>\n<img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-4035 size-full\" src=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/3008\/2020\/01\/27195349\/Module_15_Access_ExportExcel_1.6.png\" alt=\"Access screenshot of Import spreadsheet wizard window open saying where to import to table and finishing wizard.\" width=\"748\" height=\"543\" \/><\/li>\n<li>Access now asks if you would like so save these import steps just walked through. If so, click the Save import steps checkbox. We\u2019ll leave it unchecked for now.<br \/>\n<img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-4036 size-full\" src=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/3008\/2020\/01\/27195429\/Module_15_Access_ExportExcel_1.7.png\" alt=\"Access screenshot of Import spreadsheet wizard window open asking if the steps should be saved for next time.\" width=\"744\" height=\"575\" \/><\/li>\n<li>The Excel information is now imported into Access and can now be use in Access or even added to as more Excel information is discovered.<\/li>\n<\/ol>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-4037 size-full\" src=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/3008\/2020\/01\/27195458\/Module_15_Access_ExportExcel_1.8.png\" alt=\"Access screenshot of imported Excel spreadsheet customer contact data in new Access table.\" width=\"1057\" height=\"658\" \/><\/p>\n<div class=\"textbox tryit\">\n<h3>Practice Questions<\/h3>\n<p>\t<iframe id=\"assessment_practice_414dd8e1-997d-497e-9925-a0edea13d207\" class=\"resizable\" src=\"https:\/\/assess.lumenlearning.com\/practice\/414dd8e1-997d-497e-9925-a0edea13d207?iframe_resize_id=assessment_practice_id_414dd8e1-997d-497e-9925-a0edea13d207\" frameborder=\"0\" style=\"border:none;width:100%;height:100%;min-height:300px;\"><br \/>\n\t<\/iframe><\/p>\n<p>\t<iframe id=\"assessment_practice_d4935511-5d32-40a7-a986-0bec02666a98\" class=\"resizable\" src=\"https:\/\/assess.lumenlearning.com\/practice\/d4935511-5d32-40a7-a986-0bec02666a98?iframe_resize_id=assessment_practice_id_d4935511-5d32-40a7-a986-0bec02666a98\" frameborder=\"0\" style=\"border:none;width:100%;height:100%;min-height:300px;\"><br \/>\n\t<\/iframe><\/p>\n<\/div>\n<p>Now that you\u2019ve seen how to import Excel data into a new Access table, watch this video that walks through the steps and demonstrates how to add Excel data to an existing Access table. While the video shows an older version of Word (Microsoft Word 2016), the same steps work in other versions of Word, including 2019 and 365.<\/p>\n<p><iframe loading=\"lazy\" src=\"\/\/plugin.3playmedia.com\/show?mf=4369510&amp;p3sdk_version=1.10.1&amp;p=20361&amp;pt=375&amp;video_id=RelLvfilOnQ&amp;video_target=tpm-plugin-51ae9zk8-RelLvfilOnQ\" width=\"800px\" height=\"450px\" frameborder=\"0\" marginwidth=\"0px\" marginheight=\"0px\"><\/iframe><\/p>\n<p>You can also view a transcript for the video <a href=\"https:\/\/course-building.s3-us-west-2.amazonaws.com\/ComputerApps\/Transcripts\/HowToImportExcelDataIntoAccess_Transcript.txt\" target=\"_blank\" rel=\"noopener\">\u201cHow to Import Excel Data into Access\u201d here (opens in new window)<\/a>.<\/p>\n\n\t\t\t <section class=\"citations-section\" role=\"contentinfo\">\n\t\t\t <h3>Candela Citations<\/h3>\n\t\t\t\t\t <div>\n\t\t\t\t\t\t <div id=\"citation-list-4005\">\n\t\t\t\t\t\t\t <div class=\"licensing\"><div class=\"license-attribution-dropdown-subheading\">CC licensed content, Original<\/div><ul class=\"citation-list\"><li>Access Data from Excel Table. <strong>Authored by<\/strong>: Sherri Pendleton. <strong>Provided by<\/strong>: Lumen Learning. <strong>License<\/strong>: <em><a target=\"_blank\" rel=\"license\" href=\"https:\/\/creativecommons.org\/licenses\/by\/4.0\/\">CC BY: Attribution<\/a><\/em><\/li><\/ul><div class=\"license-attribution-dropdown-subheading\">All rights reserved content<\/div><ul class=\"citation-list\"><li>How to Import Excel Data into Access 2016. <strong>Authored by<\/strong>: Professor Adam Morgan. <strong>Located at<\/strong>: <a target=\"_blank\" href=\"https:\/\/www.youtube.com\/watch?v=RelLvfilOnQ\">https:\/\/www.youtube.com\/watch?v=RelLvfilOnQ<\/a>. <strong>License<\/strong>: <em>All Rights Reserved<\/em>. <strong>License Terms<\/strong>: Standard YouTube License<\/li><\/ul><\/div>\n\t\t\t\t\t\t <\/div>\n\t\t\t\t\t <\/div>\n\t\t\t <\/section>","protected":false},"author":17,"menu_order":13,"template":"","meta":{"_candela_citation":"[{\"type\":\"original\",\"description\":\"Access Data from Excel Table\",\"author\":\"Sherri Pendleton\",\"organization\":\"Lumen Learning\",\"url\":\"\",\"project\":\"\",\"license\":\"cc-by\",\"license_terms\":\"\"},{\"type\":\"copyrighted_video\",\"description\":\"How to Import Excel Data into Access 2016\",\"author\":\"Professor Adam Morgan\",\"organization\":\"\",\"url\":\"https:\/\/www.youtube.com\/watch?v=RelLvfilOnQ\",\"project\":\"\",\"license\":\"arr\",\"license_terms\":\"Standard YouTube License\"}]","CANDELA_OUTCOMES_GUID":"54ae2d4a-9b29-4bed-b6c4-9618d0d6493d, 270efb1d-3b6a-4b4e-9a1d-5b2d47f7ecf8","pb_show_title":"on","pb_short_title":"","pb_subtitle":"","pb_authors":[],"pb_section_license":""},"chapter-type":[],"contributor":[],"license":[],"class_list":["post-4005","chapter","type-chapter","status-publish","hentry"],"part":1965,"_links":{"self":[{"href":"https:\/\/courses.lumenlearning.com\/wm-computerapplicationsmgrs\/wp-json\/pressbooks\/v2\/chapters\/4005","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/courses.lumenlearning.com\/wm-computerapplicationsmgrs\/wp-json\/pressbooks\/v2\/chapters"}],"about":[{"href":"https:\/\/courses.lumenlearning.com\/wm-computerapplicationsmgrs\/wp-json\/wp\/v2\/types\/chapter"}],"author":[{"embeddable":true,"href":"https:\/\/courses.lumenlearning.com\/wm-computerapplicationsmgrs\/wp-json\/wp\/v2\/users\/17"}],"version-history":[{"count":7,"href":"https:\/\/courses.lumenlearning.com\/wm-computerapplicationsmgrs\/wp-json\/pressbooks\/v2\/chapters\/4005\/revisions"}],"predecessor-version":[{"id":5648,"href":"https:\/\/courses.lumenlearning.com\/wm-computerapplicationsmgrs\/wp-json\/pressbooks\/v2\/chapters\/4005\/revisions\/5648"}],"part":[{"href":"https:\/\/courses.lumenlearning.com\/wm-computerapplicationsmgrs\/wp-json\/pressbooks\/v2\/parts\/1965"}],"metadata":[{"href":"https:\/\/courses.lumenlearning.com\/wm-computerapplicationsmgrs\/wp-json\/pressbooks\/v2\/chapters\/4005\/metadata\/"}],"wp:attachment":[{"href":"https:\/\/courses.lumenlearning.com\/wm-computerapplicationsmgrs\/wp-json\/wp\/v2\/media?parent=4005"}],"wp:term":[{"taxonomy":"chapter-type","embeddable":true,"href":"https:\/\/courses.lumenlearning.com\/wm-computerapplicationsmgrs\/wp-json\/pressbooks\/v2\/chapter-type?post=4005"},{"taxonomy":"contributor","embeddable":true,"href":"https:\/\/courses.lumenlearning.com\/wm-computerapplicationsmgrs\/wp-json\/wp\/v2\/contributor?post=4005"},{"taxonomy":"license","embeddable":true,"href":"https:\/\/courses.lumenlearning.com\/wm-computerapplicationsmgrs\/wp-json\/wp\/v2\/license?post=4005"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}