Excel 2007 Data Tools
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.
The next button is Consolidate. This button is used to merge spreadsheets. In order for the consolidate button to work the data being consolidated needs to be in identical order in the two worksheets. When the data is in the same order click the consolidate button this will open the Consolidate window. In this window use the drop down menu in the Function section. Select the function you would like preformed when the data is merged. In the Reference section you have the option to click the table button just to the right of the text box to select a section of cells in the current workbook or click the Browse button to open a different work book to make you selection. After the selection is made click the add button to add the cells to the All references section of the Consolidate window. Check either top row or left column to tell Excel where your labels are located in the spreadsheets you are going to merge. Click OK when you are finished. The will take all of the data in each selected worksheet and perform the selected function and place the answer in the selected cells on the active worksheet.
The What-If Analysis tool in Microsoft Excel 2007 gives you the option to use different values in one or more formula to view the results. There are three options to choose from. We will go through each one individually.
Click the down arrow on the What-If Analysis button and select Scenario Manager. This will open the Scenario Manager window. Once scenarios have been entered they will appear in the scenarios section of the window.
I am using a simple spreadsheet to calculate a mortgage payment and comparing interest rates to see how it will affect the payment.
If a scenario has not already been created click the add button. This will open the Add Scenario window. Type a name for your scenario in the Scenario name field. In my example I am using the percentage for the name. I typed 4.25% for the first scenario I created then created two more using 5.5% and 6.75%. In the Changing cells field type the cell or cells name(s) that you would like to see how changing them will affect your formula. I changed cell A3 in my example. You can also click the graph icon to the right of the Changing cells text box to select the cells. By clicking the icon the Excel spreadsheet will be activated for you to select any cells you wish without having to type the cell reference. The comment box allows you to type a comment for your reference when making changes to the scenarios. Once you have entered all of the information click OK and the Scenario Values window will open.
In the Scenario values window type the number you would like to replace the current value in the selected cell. Click OK to return to the Scenario manager window. Add a couple more scenarios then click Summary.
The Scenario Summary window will open. In this window select the way you would like your scenarios to be displayed and click OK.
This will open a new tab spreadsheet titled scenario summary. It will look something like this.
The next option in the What if Analysis is Goal Seek. If you know the result that you want from a formula, but are not sure what input value the formula needs to get that result you use the Goal Seek option in the What if Analysis button.
First we need to set up the spreadsheet to use the goal seek option. In your Excel spreadsheet enter the formula you would use to calculate a loan payment. In Cell A1 type Loan Amount, Cell A2 type Term in Months in Cell A3 type Interest Rate and in cell A4 type Payment. In cell B1 type the amount that you want to borrow. In cell B2 type the term you want to pay off the loan. In cell B4 type =PMT(B3/12,B2,B1) Now set the B3 cell to a percentage by clicking % button in the number section of the Home tab.
Click on the down arrow of the What if Analysis button and select Goal Seek. This will open the Goal Seek window. In the Set cell text box enter cell B4. In the To Value text box type the amount that you would like your payment to be. Type this amount as a negative number since it is a payment. In the By changing cell text box type B3. Click the OK button to return to your spreadsheet and see what your interest rate will be on the loan.
The last option in the What If Analysis button is Data Table. A data table is a range of cells that shows how changing one or two variables in your formulas will affect the results of those formulas. It helps you explore a set of possible outcomes in a worksheet. It lets you look at a range of possibilities easily. A data table can only accommodate 2 variables. The rows or columns that the variable data is entered in is important when using the Data Table feature.
As an example I put together a table using a loan calculation formula. For the data table it will calculate the payments for the different interest rates. I entered the interest rates I would like calculated in column C. Highlight the column with the variable data along with the cell that the formula is entered into and the cells adjacent to the variables as shown. Click on the What-If-Analysis button and select Data Table. It will bring up the Data Table window. This is a Column based data table. In the example I would enter cell B3 by either typing it in the Column input cell or clicking on the table button next to the text box and selecting the cell from the worksheet. Once you have selected or entered the cell Click the OK button and you will return to your Excel workbook with the payments for the additional interest rates calculated in the D column.
The next section in the Data tab is Outline. Click the next arrow to continue to the Outline section.