Export to Excel
By Richard Rost
3 years ago
Exporting Data From Access to Excel with Format
In this Microsoft Access tutorial, you will learn how to export data from Access to Excel. I'll show you a simple export, then I'll show you how to export with filters, sorts, and formatting (colors, date formats, etc.) intact.
Sarah from Amarillo, Texas (a Platinum Member) asks: I'm in charge of keeping track of all the product data for the company but I'm the only one with Access and everyone else refuses to use Access. They all have Excel so is there an easy way for me to export a spreadsheet that they can all view and maybe even make changes to and then I can import that back into Access when I need to? It would also be nice if I could keep my formatting (colors, date formats, etc.)
Prerequisites
Links
Recommended Courses
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, Access to Excel, How to Export MS Access Table data to Excel, Exporting Tables Queries and Reports from Access to Excel, export access to excel, export access data to excel with formatting, colors, date formats
Intro In this video, you'll learn how to export your data from Microsoft Access to Microsoft Excel, including how to maintain your formatting such as background colors and date formats. I'll walk you through exporting both tables and queries, show you how to control which fields are shared, and demonstrate how to use continuous forms for better formatting in your export. You'll also see tips for preparing your spreadsheet so others can work with it and suggestions for bringing updated data back into Access. This tutorial is designed for beginners looking to easily share Access data with Excel users.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost.
Today we've got one for the beginners. I'm going to show you how to export data to Excel from Microsoft Access while still maintaining your formatting, like your blue backgrounds, date formats, and whatever else you want.
Today's question comes from Sarah in Amarillo, Texas, one of my Platinum members. Sarah says, I'm in charge of keeping track of all the product data for the company, but I'm the only one with Access and everyone else refuses to use Access. They all have Excel. So is there an easy way for me to export a spreadsheet that they can all view and maybe even make changes to, and then I can import that back into Access when I need to? It would also be nice if I could keep my formatting, colors, date formats, etc.
I'm going to show you a couple of different methods. First, we're going to export some simple table data. Then I'll show you how to do it with a query with a few more options, and then I'll show you how to do it with your formatting intact.
Today's video is for beginners, but if you have not yet watched my free Access Beginner 1 class, go watch this first so you understand all the basics of how databases in Access work.
Also, go watch this free video. It's on continuous forms. A continuous form is where you have multiple records on the form at the same time instead of just a single form where you have one record per time. We're going to be using one of these today. These are all free. They're on my website and my YouTube channel. Go to this link here. You'll find a link you can click on down below or scan a little QR code there if you want to with your phone.
Here I am in my TechHelp free template. This is a free database. You can download a copy off my website if you want to – you'll find a link down below. In this database, I've got a customer table. Let's go ahead and export this customer table to Excel.
With the table open, I'm going to come up here and click on External Data. If you don't know what I just did there, you can double-click on these tabs up top. That will collapse the ribbon if you want it nice and small, which I sometimes do when recording my videos because this ribbon is pretty big and it saves me some space. I've got a small window here.
Once you're on External Data, click on Excel in the Export group right there. You're going to give it a file. It's going to default to something in your Documents folder. This one's CustomerT.xlsx – that's the default file extension for Excel workbooks in 2023, or after 2007 really. You can change the format here if you want to, but I don't recommend it.
Now you'll see an option here that says Export the data with formatting and layout. We'll talk about this more in a second. You really can't do much formatting and layout with a table, and we'll go over this more in a bit. Don't check this box if you're exporting a table or a query because there's really no formatting worth speaking of.
I'm going to hit OK. It's going to export. Now you can optionally save the export steps. Personally, I generally don't bother with this option because as you just saw, when we do it like this from a table, there's really only a couple of clicks anyway, so there's not much to really save. Hit Close.
If I open up my Documents folder using my File Explorer, you can see right there is my CustomerT.xlsx. You can double-click on it, open it right there, and it should open right up in Excel, and there it is. Pretty simple and straightforward. That's the basics of it.
If you want to just export all of the records from a table, do it that way. Now let's close this. Let's close this guy down. Now if you want to query the data first, if you want to limit the records that you're exporting, for example, you can make a query. So, Create, Query Design. Let's say I want to bring in my customer information. This is also handy if you want to link some additional data to it. If you have related tables, you can bring in their contacts or their orders or whatever, so you can do that in a query.
I'm going to skip that for now. We're just going to keep this simple. Let's say you're going to be exporting the data, but you only want to export certain fields. Maybe you don't want everybody seeing some sensitive data that's in this table. You want the CustomerID, the first name, last name, email, maybe their phone number, but you don't want them seeing things like credit limit and all that stuff. So you just bring in the fields that you want down here.
You can do a custom sort. So let's bring last name over in front. Bring last name over in front of first name, and we'll sort by last name and then first name. Let's say we want to limit it to only people from Florida. So I'll put criteria: Florida. I cover all this stuff in my Beginner class.
Save this. Let's call this CustomerFLQ – customers from Florida. There's my query, and I can run it. Once you've got the data in here that you want, guess what: External Data, Excel. Same stuff here. OK. Close.
There's my CustomerFLQ.xlsx and here it is. You can resize this and double-click, and do whatever you want here to make it look pretty.
If this is something that you export on a regular basis, then you might want to set the formatting and stuff ahead of time so you just export it in one shot and it keeps all your formatting. To do that, I strongly recommend using a continuous form. That's why I wanted you to watch that other video.
I already have a continuous form built. It's called my Customer List. I'm not going to reinvent the wheel. If you don't know how to build one of these, go watch that continuous forms video. You can do the same thing. Now, the nice thing about doing it with a form is that you can go to Design View and set your colors in here. Let's say you want the state to be that color.
You can make sure all your fields are justified the order you want. Save this, open it back up again. You can sort this data. Let's say you want to sort by last name. Let's say you want to filter it to see only people from New York. Right-click, equals New York. Now I want to export this.
So, External Data, Excel. It's going to be CustomerListF – Excel workbook. Now down here, notice this option is available now. It says "Open the destination file after the export operation is complete." You can only do this when you export formatted data. Why? I don't know. You can't do it if you don't have a formatted table. It's weird, but now you can check this box on, and then instead of having to hunt for that file, hit OK.
Oh, it already exists. I was playing around with it earlier. You do want to replace the file. Say yes, it'll just make a new one, and there you go. Look at that.
One thing to note – you mentioned specifically your date formats. Look at this. This is the default Excel date format, right? Short date format. Whereas over here in Access, I've got the ISO date format, which I recommend everyone use. ISO – if you don't know what ISO dates are, it's year, month, day. The whole world should use this. I'm on a mission. I'm going to get everyone using this. Watch this video if you want to learn more about it.
I don't have any format set in here. It's using my default short date. Let's see what I've got. I think I have it set to blank, in fact. Yeah, format is nothing in there. So you want to specify a format. I'll put "Short Date" in there and it should export it now in exactly whatever format you specified instead of that Excel format.
So, External Data, Excel. Click that button, box, whatever. Got me. Yep, there we go. See? Now reformatted like that.
You can do whatever filtering and changing you want. If you don't want null values in here, right-click, does not equal blank, sort by state, and then export it.
There you go. Nice and pretty, you keep your colors, you keep your formatting.
Now, could you give this to someone else and have them make changes? They can come in here and change "Tasha Yard" to "Tasha Bye-Bye" or whatever, and then give this back to you and you could import this? Yeah, you could. I have a whole separate video on importing data into Access from different formats, including Excel or text.
What I would recommend is that you import the data into a temporary table. Then you can join them together based on the CustomerID, since you have the CustomerID in your export, and then you can look for any records that have changes and run an update query to make the changes. I have another video on that, on update queries.
That's pretty much it. That's what I recommend you do if you want to export data to Excel and have it look all pretty, and you control the fields, the colors, the layout, the formatting, all that stuff.
Yes, honestly, I would leave these there. There is a way you can change this data up here, but I would leave this because that's the exact field name you want when you import that back into Access. Leave this alone. Tell people not to change that. In fact, you could protect the sheet. After you export it, you can come in here and turn cell protection on so they can't modify this stuff, or any other fields you don't want them to change, like the credit limit. You can protect this row.
A lot of people don't know this about me, but I also have a bunch of Excel lessons too. Access is my forte by far, but I also have a bunch of Excel stuff, and I'm also a published Excel author. In my Excel Expert Level 8 class, I cover worksheet protection, where you can lock cells. We do things like a bank loan calculator. Let's say you want to let your customer put their loan information in – you want to lock the payment field. You let them type in the blue fields here – the amount of loan, number of years, their interest rate (although you probably want to lock that too because you're telling them what the interest rate is), and then everything else on here is locked. So we cover worksheet protection.
Again, that's Excel Expert Level 8. I'll put a link to that down below. You can come in here and lock all this stuff when you export it and give it to whoever, and then when they send it back to you, you can import this data, resting assured that they haven't changed anything you don't want them to change, like the CustomerID. You don't want to come back with a CustomerID that's "cow" or something. That's one of the problems with Excel – you can pretty much do whatever you want in here unless you are vigilant about putting protection in the sheet.
I also cover a lot more with exporting to Excel and Word, mail merge, and a bunch of other things in my Access Expert Level 19 class. If you want to learn more about this stuff, I'll put links to all this down below.
So, that's pretty much how you do it. You can do some basic formatting in tables. I don't recommend it. You can set a background color – for example, if you come over here into text formatting, you can put a background color on like that. You can't do individual fields; if you change it again, you're getting that for the whole thing. This box over here does let you do alternate row color like this, but again, why? I mean, yeah, OK, fine. I'm just not a big fan, so I'll leave those on. Tables are for data and not even for data entry. Tables are for storing your data, and you, the developer, can come in here and modify stuff if you want to, but you don't want your end users working directly with tables. It's a no-no.
I try to keep people out of tables as much as possible. I don't even use tables on my own database. I make forms for all the interaction.
Now, tomorrow, for you developer-level users who stuck around through all this, some news for you. Tomorrow's video is going to be automating an export to Excel with one click. We're going to make a button, we're going to automate that export, and it's just going to create the spreadsheet and then give you a beep and say "I'm done." We're going to use a well, I can't tell you what command we're going to use, but it's going to be a surprise. There's one command that everyone thinks you should use and that's not the one you should use. It's a different one and you'll find out tomorrow which one I want you to use. When I say tomorrow, today is Wednesday, September 6, 2023 – that's when I'm recording the video. This video is going to post on YouTube on Thursday the 7th, so Friday the 8th is when the automated export video will be out. So tune in tomorrow, same bat time, same bat channel, and I'll teach you how to automate this whole process. It requires a little bit of VBA code, a little developer stuff.
All right, so that's going to do it for today. That's how to export to Excel for you beginners. Hope you learned something. Live long and prosper, my friends. I'll see you next time.Quiz Q1. What is the main purpose of exporting data from Access to Excel as described in the video?
A. To allow users without Access to view and work with the data
B. To create backup copies of tables for security purposes
C. To speed up Access database performance
D. To convert all data to a different format for permanent storage
Q2. When exporting a TABLE from Access to Excel, should you check the "Export the data with formatting and layout" option?
A. Yes, to maintain the table formatting in Excel
B. No, because there is little to no formatting in tables worth exporting
C. Yes, to ensure date fields display correctly
D. No, as this will remove all data
Q3. What is a continuous form in Access?
A. A form that displays one record at a time
B. A form that allows users to enter unlimited text in one field
C. A form that displays multiple records at once, similar to a spreadsheet view
D. A form that continuously updates itself with live data
Q4. Why might you want to use a query to export data to Excel instead of exporting directly from a table?
A. To apply password protection to the exported file
B. To select specific fields and filter or sort records before exporting
C. To increase the export speed
D. To merge data from multiple databases automatically
Q5. Which of the following steps allows you to keep your field colors, layout, and formatting when exporting to Excel?
A. Exporting from a table using default settings
B. Exporting from a query without formatting selected
C. Exporting from a continuous form with formatting applied in Design View
D. Exporting using the oldest version of Access
Q6. If you want to limit data visibility in the Excel export (for example, hiding sensitive columns), what is the best approach?
A. Export the entire table and delete the columns in Excel
B. Build a query selecting only the desired fields, then export that query
C. Change field visibility settings in the table
D. Use data macros to automatically hide fields
Q7. What is the recommended way to handle changes made to the Excel file by other users before importing the data back into Access?
A. Reimport the Excel file directly over the existing table
B. Import into a temporary table, compare using CustomerID, and use an update query if needed
C. Ask users not to change any data
D. Manually copy and paste the changed fields one at a time
Q8. Why is it generally not recommended to let end users work directly in Access tables?
A. Tables cannot store any formatting
B. Tables are not secure from accidental changes or deletions
C. Tables are only for advanced users
D. Tables are too slow for large databases
Q9. How can you protect certain fields in Excel after exporting data so users cannot change them?
A. Set field protection in Access before exporting
B. Use worksheet protection in Excel to lock specific cells or fields
C. Restrict file access with a read-only USB stick
D. Compact and repair the Access database
Q10. What happens if you do not specify a date format in Access before exporting date fields to Excel?
A. Excel will refuse to import the date fields
B. The dates will appear only as numbers in Excel
C. The dates will default to Excel's standard date format
D. The dates will be exported as blank fields
Q11. What is an advantage of saving export steps in Access?
A. It allows you to run the same export later with fewer clicks
B. It encrypts the exported Excel file
C. It automatically emails the results to other users
D. It sets up a real-time data sync between Access and Excel
Q12. When setting up a form for export, how can you ensure the exported Excel file preserves the same sorting and filtering as seen on the form?
A. Set the sort and filter in the table before exporting
B. Make the changes in the form (such as filter or sort), then export from the form
C. Sort and filter after opening the Excel file
D. Use VBA code to lock the sort order
Q13. What is the recommended way to handle field names (headers) in the exported Excel file before sharing it with others for possible re-import?
A. Change them to simpler names in Excel
B. Leave them as exported from Access so they match on import
C. Delete any unnecessary headers
D. Add numbers to each field name
Q14. Which of the following is TRUE regarding formatting when exporting directly from an Access table?
A. All cell colors and fonts are exported
B. Only alternate row shading is exported
C. Formatting options are limited and generally not recommended
D. Conditional formatting rules are always exported
Q15. Why does the instructor recommend using forms instead of tables for most user interactions in Access?
A. Forms can have formatting, sorting, filtering, and user controls
B. Forms automatically prevent data entry errors
C. Forms allow for faster database queries
D. Forms can be edited in Excel
Answers: 1-A; 2-B; 3-C; 4-B; 5-C; 6-B; 7-B; 8-B; 9-B; 10-C; 11-A; 12-B; 13-B; 14-C; 15-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone covers exporting data to Excel from Microsoft Access while preserving formatting such as background colors and date formats. This lesson is designed for beginners who want to share Access data with colleagues who prefer working in Excel.
If you are new to Access, I strongly recommend watching my free Access Beginner 1 class first so you are comfortable with the database framework. I also suggest checking out my video on continuous forms, where you can display multiple records at once on a form. We will be using one in this lesson.
Let's start with a simple example. In my free TechHelp template database, which you can download from my website, there is a customer table. Suppose we want to export it to Excel so others can use or edit the data. With the table open, go to the External Data section, then choose Excel as the export format. The resulting file should appear in your Documents folder with a .xlsx extension.
Now, there is an option labeled "Export the data with formatting and layout." When exporting from a table or a query, do not select this option because tables and queries do not support much formatting that is worth preserving in this context. After exporting, you can open the file in Excel, and all the data should be there, appearing as a straightforward spreadsheet.
If you need to export only certain records or fields, create a query first. You can decide which fields to include (to avoid sharing confidential columns like credit limits) and apply sorting or filters as needed—for example, narrowing your export down to customers from Florida and sorting by last name. Once your query only displays the desired data, use the same process: export it to Excel using External Data. Again, do not select the formatting option here since queries also do not offer much formatting.
For those who regularly need to export data with the same appearance each time, I recommend exporting from a continuous form. This approach gives you much more control over formatting: you can set background colors, align columns, and set specific formats in Design View. Once your form looks the way you want, you can also sort or filter your data on the form before exporting. When exporting from a form in this way, you now have the option to "Export the data with formatting and layout." Select this option to preserve your color schemes, field formatting, and any date formats you have applied.
On the topic of date formats, if you want to maintain consistency between Access and Excel, explicitly set the date format you want in Access before exporting. For instance, using the ISO date format (year, month, day) is my recommendation. This will help ensure your dates appear as intended after export.
Next, if you want to share this Excel file with others for them to make changes and then re-import it back into Access, I suggest importing the modified data into a temporary table first. From there, you can compare records by CustomerID and use update queries to apply any changes to your main tables. I have separate tutorials that cover how to import data into Access and how to run update queries.
As far as the column headers in the exported Excel sheet go, keep them as they are. They match the field names in Access, which makes re-importing the data much smoother. If you wish to prevent users from accidentally altering critical fields like CustomerID or other protected data, you can enable worksheet protection in Excel. This means locking particular columns or rows as needed. I teach Excel worksheet protection in detail in my Excel Expert Level 8 course, which you can find on my website.
As a note, you can do minimal formatting in Access tables—such as changing background colors—but these settings are very basic and not field-specific. Forms are far more versatile and appropriate for setting up a visually appealing export template. I advise keeping users out of tables as much as possible, even for data entry; forms remain the preferred tool for user interaction.
For those interested in automating these exports in the future, tomorrow I will be posting a video about creating a one-click automated export button in Access. This will use a particular command in VBA that I believe is the best solution, and I will reveal which one it is in that lesson.
In summary, exporting Access data to Excel while maintaining formatting can be easily achieved, particularly by exporting from a properly designed continuous form. This method offers the best balance of flexibility and control, whether you are simply sharing data or preparing spreadsheets for others to edit and send back.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below.
Live long and prosper, my friends.Topic List Exporting table data from Access to Excel
Exporting query results from Access to Excel
Selecting specific fields to export using a query
Sorting and filtering data before export
Exporting only selected records with criteria in a query
Maintaining column order during export
Exporting data with formatting using a continuous form
Using a continuous form to control field order and formatting
Setting colors and formatting in a continuous form before export
Exporting specific date formats from Access to Excel
Filtering and sorting data in a form prior to exporting
Protecting worksheet data in Excel after export
Preserving field names for importing back into Access
Applying basic formatting to Access tables before export
Understanding limitations of table formatting during export |
