I have data in an Excel spreadsheet that I would like to use in an Access Database. What are the steps?
Importing or linking data from Excel to Access works well if the Excel data is in rows and columns without any subtotal rows, formulas or blank rows or blank columns within the worksheet or range. Column headings should not be wrapped or contain special characters and must be unique names. Then the data should import pretty cleanly by:
- Create a blank database in Access (or an existing database you wish to place it) and select the External Data tab > Excel (from the Import group).
- Locate the Excel file and select “Import the source data into a new table in the current database” or “link to the data source by creating a linked table.”
- Follow the screens until the process is finished.
If you have cells which contain formulas, they will import as values. When importing, the Access table is completely separate from your Excel file. Any updates made in Excel or Access after the import are not updated in both applications. However, if you “Link” the data, you can only update from the Excel file, but the data is automatically updated in Access so that it can be used for filters, queries and reports.
Keep in mind that just because your Excel data imports well into Access does NOT mean that your file is set up ideally to work in Access. You may have to “normalize” your data – add fields, split the data into more tables, add primary keys, add validation properties, etc. In addition, Microsoft Access requires a considerable investment in time to learn how to use it, compared to Excel. But Access works well if you have a lot of data in separate but related tables to analyze. To learn more about database design concepts, come to ITS’ mini-session on Thursday, 11/29 from 2-3 p.m. in the ITS training room, 8011 Wittson Hall. Additional Access training is available in December. Click here for the ITS class schedule and on-line registration.
Question: What do you recommend using for a temporary calendar to keep track of our department’s upcoming vacation schedules during the holidays?
Answer: You could use calendar templates that are available with various applications such as Word or Excel or even PowerPoint. Go to File (Office 2010) or the Office Button (Office 2007) and select New. Under the Microsoft Office templates, you should see a category for Calendars. You can also just search “Calendars” in the search field. You may want to look at various calendars to see what works best for your group. Then you can download the calendar and send the document to your co-workers via email. They can can add their vacation entries and send the document back to you. You can then combine their document with the master using the Review tab and Compare and accept or reject their changes.
Another option would be to create a calendar template in Lotus Notes using your own calendar.
- Click File > Print. But instead of using the typical print calendar option, use PRINT CALENDAR TO A DOCUMENT.
- Select monthly view (or another view if you prefer) and select your preferred print settings for that view (such as font, include end time, truncate subject).
- Click Preview. This creates a nice looking calendar with your existing entries, but you can forward this to an email message, allowing you to edit and delete your entries and add others’ entries.
- You can send to your staff and have them add entries and return to you. However, there is no feature in Notes where you can “route” a message or combine entires together. Someone would have to copy and paste into a master calendar.
If you have questions on these, contact email@example.com or firstname.lastname@example.org
Question: Is there a way to remove duplicate values from a long list of names in Excel?
Answer: Yes, select your range and from the Data tab, click Remove Duplicates. A popup will allow you to select which fields to select for your duplicate values. You can unselect all fields, and selecting just a couple of fields will break down your list more than if you have many fields included. Be careful, as this will delete your data, so you may want to save the results with the different name using “Save As” (you can also click “undo” if the list does not look correct or not save your file at all).
I wanted to share some excellent tools that I’ve recently learned about. One is a free statistical analysis add-in program for Excel. Greg McFadden from Internal Medicine shared his knowledge with us in a mini-session a couple of weeks ago. To use this add-in:
- Select Excel Options (available from the Office button in Excel 2007 or File menu in Excel 2010).
- Click Add-ins and select Go (toward bottom of add-ins window)
- Place a check on the Analysis ToolPak and click OK. You may get a prompt asking to install it, and you may need the installation disk (although I didn’t with Excel 2010).
- After it has been installed, the analysis features are available on the far right of the Data tab. Some of the analysis tools available are: anova, correlation, covariance, descriptive stats (wonderful), F-test, histogram, fourier analysis, regression and more.
This site recently came to my attention from Teri Hartman, from McGoogan Library. Here you will find described a number of on-line programs that can be used to create charts and graphs of all types, including drawing tools, mind mapping, flow charts, organizational charts, iPhone chart application and more! I haven’t tried any of them yet, but I can’t wait to try out the mind mapping tools!
Thanks Greg and Teri for sharing your great finds!
Question: I have an Excel Workbook with several worksheets. On the summary sheet I want to refer to the values on various individual worksheets. How can I do that?
Answer: The easiest way is just to click in the Summary worksheet cell where you want the cell’s value and press = (to indicate a formula) and then click on the source worksheet and cell you’re referring to and press enter (or the formula check box). This creates an automatic link to the contents of that particular cell. If the source worksheet is modified by adding or deleting rows or columns, the summary sheet link will change based on the new cell address (relative cell address). Absolute cell addresses ($B$12) would assure that that exact cell address B12 is always used regardless of modifications to a worksheet.
I would also recommend using Excel Pivot Tables to simply your work even more, and providing some powerful tools. However, your data needs to be set up with contiguous rows and columns, eliminating blank, total and subtotal rows and columns. Pivot Tables can be a tremendous timesaver for budgets and other worksheets with a lot of rows and columns and allows you to create a variety of reports and charts by updating the original set of data. There are several online tutorials at http://office.microsoft.com/training (and select Excel) which can get you started. ITS also teaches sessions occasionally on Pivot Tables.