Excel 2007 Easy Learning - Step 16

In this tutorial we will be learning about the Excel 2007 data tab. This tab enables you to import data from other programs, update your spreadsheet when changes are made to the external data sources, sort, filter, and organize your data. Lets' get started with the first section, Get External Data.

You can import spreadsheet information into Excel 2007 from about any program. Excel 2006 gives you a button to import from Access, a Web site, or Text file. Click one of those buttons and a window will open for you to select the location of the data you want to import. If your data is in a source not listed click the down arrow on From Other Sources and make a selection. If you need help please email me and I will walk you through your specific question. The last button is Existing Connections. The benefit of using a data source connection is it can make it less time consuming to analyze data in Excel from other programs. Usually you would have to cut and paste data into Excel with the Data Source connections you don't have to do that.

Let's Move on to the Connections section of the data tab. The first button is Refresh All. If you have your spreadsheet connected to a data source and you make changes to your data source then you will need to update your spreadsheet. Click the Refresh button you will be given the option to update all data sources, update just the work book, check on the status of the refresh, or cancel the refresh.

The Connections button will open a window that gives you a list of all of your data connections a description of the connection, where the connection is located and the last time it was refreshed.

The Properties button will be grayed out until you have a data source connected to your spreadsheet. Properties with tell you how cells that are connected to a data source will be updated, what contents from the source will be displayed, and how changes in the number of rows or columns in the data source will be handled in the workbook.

The Edit Links button will open a window for you to view all other files the spreadsheet is linked to and how they are linked and it will allow you to make changes to the links.

The next section is Sort & Filter. If you have large amounts of data in your spreadsheet it is helpful to be able to organize it and find what you are looking for. To use any of the sort buttons you can click the top of the column or highlight all of the data you want sorted then click the button you would like to organize your data with. The AZ button with the down arrow will sort your data from lowest to highest. The ZA button with the up arrow will sort your data from highest to lowest.

The Sort button opens the Sort window which will give you more options when organizing your data. You can sort on multiple levels if you have several columns of data. For example you could sort my last name, then date, then amount owed. Make you selections then click the OK button to finish and your data will be sorted.

To use the filter button select the cell at the top of the column you want to filter then click the filter button. This will place an arrow in the cell. When you click the down arrow you will get a menu to make your filtering selections. In this example I am filtering a date column. It gives me the option to just show 2010 dates, all dates, etc. You can either check the boxes provided to filter or click the Date Filters option to have more choices such as filter by quarter, month dates past today, etc. Play with the options and when you are finished click ok for the filter to take place. To clear a filter click on the down arrow and select Clear filter From "column name" or click on the clear filter from the sort and filter section of the data tab.

The last button in the sort and filter section is the Advanced tab. This will open a window for you to customize your filtering more.

Next is the Data Tools section. The first button is Text to Columns. This button will separate words into 2 columns. To try this type you first and last name in one cell. Then click the Text to Columns button. It will open the Convert Text to Columns Wizard.

The first step of the wizard has you tell Excel how your words are separated in the cell. I need to click Fixed width because I just used a space. Then click next. In the next window lets you set the column widths. You can adjust the widths by moving the arrows. When finished click next for the third window. The third window has you set the type of cell format, ie: text, currency, etc. make your selection and click finish. It will return you to your spreadsheet with all of the selected cells separated into 2 cells.

The next button is Remove Duplicates. If you have columns in your spreadsheet that may have duplicate data you can click the Remove Duplicates button and the Remove Duplicates window will open.

In the Remove Duplicates window check the boxes next to the columns you would like the duplicate data removed. Then click OK and all duplicates will be deleted from the spreadsheet.

The next button in Data Tools is Data Validation. The Data Validation button prevents invalid data from being entered in cells. Click the down arrow on the Data Validation button to see the options then click on the Data Validation option. This will open the Data Validation window to the settings tab. In this tab you can customize the data that you will allow to be entered into the cells you have selected. The down arrow under Allow will let you make your selections. Some examples are decimal, whole numbers, date, time, text or you can customize. After making a selection The Data selections will no longer be grayed out. Use the drop down arrow to select the type of data allowed. Depending on the selection made will depend on the variables you will be allowed to enter. I selected between. This gave me a minimum and maximum text box to fill in. I typed 3.2 in the mininum and 5.7 in the maximum.

Now click on the Input Message tab. If the check box is marked it will let you customize a message that will appear when the cell with data validation is selected. The message will appear like a note. The title will be bold at the top and the message will appear below. If the check box is not marked there will be no message displayed to tell the user of the data valitation for the cell.

Click on the Error Alert tab. I would recommend always checking the box to show an error when invalid data is entered. It this box is not checked the user will not know why the data will not show up in the cell. Once the box is checked use the drop down arrow in the style section to choose the icon that will appear in a pop up window when invalid data is entered. Then type the title and message in the text boxes. Click Ok once you have entered the title and message. Now type a number that is invalid and press enter. You should get a window similar to this.

If you choose not to have an error alert to prevent invalid data from being entered you can have the data circled. Click the down arrow on the data Validation button in the drop down menu click the Circle invalid data option. You will need to go back and change you settings in the data validation window to allow invalid data to be entered. When the invalid data is entered a red circle will be placed around the cell. To clear the circles use the last option Clear Validation Circles.