Interested in becoming an Affiliate Trainer? We are now accepting applications for local and international positions. No need for cold calling! Receive referrals of people looking for specialized training in your area and get access to all of the training curriculum I have created and continue to create to use at your appointments.
In this Microsoft Excel 2010 tutorial, we will review the five groups within the data tab: Get External Data, Connections, Sort & Filter, Data Tools and Outline. We’ll explore useful tools that will assist with solving complex problems, manipulating data, preventing the input of invalid data and reviewing multiple ways of analyzing data.
The Outline and Sort & Filter commands within the data tab will ultimately make the data more understandable; especially, when there is a large amount of data to interpret. Other tools in the tab are intended to save you time by extracting or joining data. We will review the most commonly used tools in the Data tab.
One of the benefits of connecting to external data is that you can periodically analyze this data in Microsoft Office Excel without repeatedly copying the data, which can increase the amount of time and errors.
After connecting to external data, you can also automatically refresh or update your Excel workbooks from the original data source whenever the data source is updated with new information.
Let’s start with getting external data from Access. Access is the database program for Microsoft Office. In order to connect to data that is in an existing Access database, you go into the Excel file where you would like to place it. Select From Access in the Get External Data section of the Data tab. You will then go and find the Access database. Select the database you want to use and click on Open.
When you select the database you want to use, you will have a list of the different objects that are in that database, as shown in the image to the right. We are going to select Students and then click OK.
It is going to ask you how you want to view your data in the workbook, as shown in the image to the left. We are going to bring it in as a table, but it can also be brought in as a PivotTable Report. We are going to place it in the existing worksheet in the cell that is selected and then click OK.
The access table will be brought into your Excel workbook. You will get an additional tab in your ribbon, Table Tools Design. Anytime changes are made in Access, you can select the Refresh button from the External Table Data in the Design tab and the data will be updated in your Excel workbook.
The next external data source we will go over is how to import data from a website. First, go to the file you would like to import into. Then select From Web from the Get External Data section of the Data tab. This will open the New Web Query window (as shown in the image below) and it will go to the home page for your browser.
Double click on the title bar to maximize the window. Then you will scroll to find the data you would like to import.
Any data that has a yellow box next to it with an arrow can be imported into your excel file. Click on the box and it will turn into a green box with a check. Then select Import, located in the bottom-right corner of the window (as shown in the image below).
The Import Data dialog box will appear, as shown in the image to the right. We will place it in the existing worksheet in the A1 cell and select OK.
The data we just saw on the website is now on the excel workbook. As the information updates on the website, you can select Refresh All from the Connections section of the Data tab and the information will update in the Excel workbook as well.
The next import of external data we will go over is getting data from a text file. To do this go into the excel file you want to import into and select From Text from the Get External Data section of the Data tab.
In the Import Text File dialog box you would go find the file that you want to import and double click on it. The Text Import Wizard dialog box will come up with the first of three steps. The first step is going to ask you how the data is separated in your text file: Delimited or Fixed Width (as shown in the image below). We are going to leave it on Delimited and select Next.
The second step is going to ask you how the text is separated. You’ll see the sample of your actual data in the lower half of the dialog box (as shown in the image below). Select the Delimiters that apply to your data and select Next.
The final step is going to ask you for column data format. General is the default, so if it sees anything that is text it will be a text data type, and if it is a date it will convert it into dates, and numbers into numbers. If there is a column that you do not want to import, select the column and click on Do not import column. After making your selections, click Finish.
It will ask you where in this worksheet you would like to place the data.
We will place it in the A1 cell and select OK.
The data has been imported. You can adjust the formatting as needed as well as Refresh All to keep the information current in your worksheet.
Finally, if you have data you want to link to that is not from access data base or from a website or from a text file, you may need to go to the data tab and choose From Other Sources.
From here you can choose to import from a SQL Server site, from an Analysis Services cube, from an XML Data source or you can have Excel run the Data Connection Wizard to help you find the data you want to import. You can also import data using the Microsoft Query wizard.
If you choose any of these options it will either open a wizard or open up a dialog box asking you for more information.
This concludes the Get External Data tutorial.
Click next to continue to the Excel 2010 Connections tutorial.