facebook google plus twitter
Webucator's Free Advanced Microsoft Excel 2013 Tutorial

Lesson: Data Tools

Welcome to our free Advanced Microsoft Excel 2013 tutorial. This tutorial is based on Webucator's Advanced Microsoft Excel 2013 Training course.

Data Tools in Microsoft Excel are simply tools which make it easy to manipulate data. Some of them are intended to save you time by extracting or joining data and others perform complex calculations. This lesson covers the most commonly used Data Tools.

Lesson Goals

  • Convert text to columns.
  • Use Data Validation to restrict the type of data that can be entered into a cell.
  • Consolidate data from a number of different ranges into one new range.
  • Use Goal Seek to figure out the value to input to obtain a specific result.

Converting Text to Columns

If you have a list of data in which all the information appears in one column, you can use the Text to Columns command to convert the data to multiple columns. This is generally much easier than retyping the data!

To convert text to columns in Microsoft Excel:

  1. Select the column that holds the text you want to convert into multiple columns.
  2. On the DATA tab, in the Data Tools group, click the Text to Columns command:Text to Columns
  3. In the Convert Text to Columns Wizard - Step 1 of 3 dialog box, select either Delimited (if your data is separated by commas, tabs, or spaces) or Fixed Width (if your data contains a certain number of characters in each field). You will almost always select Delimited in this step. Click Next:Delimited
  4. In the Convert Text to Columns Wizard - Step 2 of 3 dialog box, select the Delimiters (assuming you selected Delimited in the prior step). If you aren't sure what to select, you can select and deselect the options and see the results in the Data preview. After making your selection(s), click Next:Data Preview Results
  5. In the Convert Text to Columns Wizard - Step 3 of 3 dialog box, you can select the data format for each column or you can elect not to import a column. Simply select the column under Data preview and then select the Column data format above. In this step, you also specify where you want the data to show up. After you make your selections, click Finish:Finish

    Linking to External Data

    In Excel 2013, you can use the options in the Get External Data group of the DATA tab to link to data in different sources, such as text files, Microsoft Access, and the Internet.

    Get External Data Group

    Controlling Calculation Options

    In Excel, calculation refers to Excel's ability to automatically compute formulas and display the results. You can alter the calculation options.

    To control calculation options:

    1. From the FILE menu tab, select Options.
    2. In the Excel Options dialog box, select Formulas on the left. Formulas
    3. Set options under Calculation options and click OK to save the changes. Calculation Options

Converting Text to Columns

Duration: 10 to 20 minutes.

In this exercise, you will practice converting text to columns.

  1. Open Text to Columns.xlsx from your Excel2013.3/Exercises folder.
  2. In the sheet named "Presidents", convert the text in column A to three columns: first name, last name, dates in office. (No worries if your instructor already used this example in their demo - it will still be good practice!)
  3. In the sheet named "Capitols", convert the text in column A to two columns: city and state. Put the converted data into columns C and D.
  4. In the sheet named "Contact Information", convert the text in column A to five columns: name, address, city, state, and zip code. (Hint: You will need to do this in two steps.)
  5. In the sheet named "Phone Numbers", separate the area codes by converting the text in column A to three columns.

Solution:

  1. To convert the text in column A of the sheet named "Presidents" to three columns:
    1. Select column A.
    2. On the DATA tab, in the Data Tools group, click the Text to Columns command:Text to Columns Command
    3. In the Convert Text to Columns Wizard - Step 1 of 3 dialog box, select Delimited and click Next:Delimited
    4. In the Convert Text to Columns Wizard - Step 2 of 3 dialog box, check Space and click Next:Check Space
    5. In the Convert Text to Columns Wizard - Step 3 of 3 dialog box, click Finish:Wizard
  2. To convert the text in column A of the sheet named "Capitols" to two columns:
    1. Select column A.
    2. On the DATA tab, in the Data Tools group, click the Text to Columns command:Text to Columns Command
    3. In the Convert Text to Columns Wizard - Step 1 of 3 dialog box, select Delimited and click Next:Delimited
    4. In the Convert Text to Columns Wizard - Step 2 of 3 dialog box, select Comma and click Next:Select Comma
    5. In the Convert Text to Columns Wizard - Step 3 of 3 dialog box, set the Destination to cell C1 and click Finish:Set Destination
  3. To convert the text in column A of the sheet named "Contact Information" to five columns:
    1. Select column A.
    2. On the DATA tab, in the Data Tools group, click the Text to Columns command:Text to Columns Command
    3. In the Convert Text to Columns Wizard - Step 1 of 3 dialog box, select Delimited and click Next:Select Delimited
    4. In the Convert Text to Columns Wizard - Step 2 of 3 dialog box, select Comma and click Next:Select Comma
    5. In the Convert Text to Columns Wizard - Step 3 of 3 dialog box, click Finish:Click Finish
    6. Select column D.
    7. Click the Text to Columns command.
    8. In the Convert Text to Columns Wizard - Step 1 of 3 dialog box, select Delimited and click Next.
    9. In the Convert Text to Columns Wizard - Step 2 of 3 dialog box, select Space and click Next.
    10. In the Convert Text to Columns Wizard - Step 3 of 3 dialog box, click Finish.
  4. To convert the text in column A of the sheet named "Phone Numbers" to three columns:
    1. Select column A.
    2. On the DATA tab, in the Data Tools group, click the Text to Columns command:Data Tools Group
    3. In the Convert Text to Columns Wizard - Step 1 of 3 dialog box, select Delimited and click Next:Select Delimited
    4. In the Convert Text to Columns Wizard - Step 2 of 3 dialog box, select Other, enter a dash ("-"), and click Next:Select Other
    5. In the Convert Text to Columns Wizard - Step 3 of 3 dialog box, click Finish:Finish

Controlling Calculation Options

In Excel, calculation refers to Excel's ability to automatically compute formulas and display the results. You can alter the calculation options.

To control calculation options:

  1. From the FILE menu tab, select Options.
  2. In the Excel Options dialog box, select Formulas on the left. Formulas Option
  3. Set options under Calculation options and click OK to save the changes. Calculation Options

Data Validation

To protect against incorrect data entry, you can use data validation to restrict the type of data that can be entered into a cell. You can choose to allow only certain types of data, such as whole numbers or dates, or you can allow only items from a specified list.

To apply data entry restrictions:

  1. Select the cells for which to verify data.
  2. On the DATA tab, in the Data Tools group, click the Data Validation command:Data Validation Command
  3. Click Data Validation:Data Validation
  4. In the Data Validation dialog box:
    1. On the Settings tab, choose which types of values to allow:Values to Allow
    2. Note that when you select a type of value to allow, the Data options change accordingly. Set the criteria (between, greater or less than, equal to, Source, etc.) for those values. You can type the criteria or select it by clicking the red arrows to the right of the data entry boxes. If you are only allowing values from a list, you must click the red arrow and select the source:Select Source
    3. If you want to, you can enter a message telling the person entering the data what the restrictions are. They will see this message when selecting any of the cells for which the restriction has been set. To enter this message, select the Input Message tab, make sure that Show input message when cell is selected is checked, and enter the Title and Input message:Message
    4. You also have the option of entering an error alert that pops up when invalid data is entered. To enter an error alert, select the Error Alert tab, make sure that Show error alert after invalid data is entered is checked, and enter the Style (choose to stop the person from entering the data, warn them that this data does not meet the criteria, or simply let them know), Title, and Input message:Error Alert
    5. Click OK.

Using Data Validation

Duration: 15 to 25 minutes.

In this exercise, you will practice using Data Validation to restrict the data that can be entered in specific cells in a worksheet.

  1. Open Data Validation.xlsx from your Excel2013.3/Exercises folder.
  2. Use the Data Validation command to restrict the data that can be entered into cells C2:C17 to the list of salespeople on the sheet named "Salespeople".
  3. Use Data Validation to restrict the data that can be entered into cells D2:D17 to a date in 2012.
  4. Use Data Validation to restrict the data that can be entered into cells E2:E17 to the list of territories on the sheet named "Territories". Enter an Input Message and an Error Alert to help the person entering data.

Solution:

  1. To restrict the data that can be entered into cells C2:C17 to the list of salespeople on the sheet named "Salespeople":
    1. Select cells C2:C17.
    2. On the DATA tab, in the Data Tools group, click the Data Validation command:Data Validation Command
    3. Click Data Validation:Data Validation
    4. In the Data Validation dialog box, on the Settings tab, under Allow, select List:List
    5. Click the red arrow to the right of the Source data entry box to collapse the Data Validation dialog box:Data Validation Dialog Box
    6. Navigate to the sheet named "Salespeople", select cells A2:A6, and then click the red arrow at the right of the collapsed Data Validation dialog box to restore it:Restore
    7. Back in the main Data Validation dialog box, click OK:Click OK
    8. Note that selecting any of cells C2:C17 in the sheet named "Sales" opens a drop-down list from which you can select one of the allowed entries:Drop-Down List
  2. To restrict the data that can be entered into cells D2:D17 to a date in 2012:
    1. Select cells D2:D17.
    2. On the Data tab, in the Data Tools group, click the Data Validation command:Data Validation Command
    3. Click Data Validation:Data Validation
    4. In the Data Validation dialog box, on the Settings tab, under Allow, select Date:Date
    5. Under Data, select between. Under Start date, enter "1/1/12" and under End date, enter "12/31/12". Click OK:Enter Date
    6. Only dates in 2012 are allowed in cells D2:D17 now. Entering another date will result in an error message:Error Message
  3. To restrict the data that can be entered into cells E2:E17 to the list of territories on the sheet named "Territories", and to enter an Input Message and an Error Alert to help the person entering data:
    1. Select cells E2:E17.
    2. On the Data tab, in the Data Tools group, click the Data Validation command:Data Validation Command
    3. Click Data Validation:Data Validation
    4. In the Data Validation dialog box, on the Settings tab, under Allow, select List:List
    5. Click the red arrow to the right of the Source data entry box to collapse the Data Validation dialog box:Data Validation Dialog Box
    6. Navigate to the sheet named "Territories" and select cells A2:A6:Select Cells
    7. Click the red arrow at the right of the Data Validation dialog box to restore it:Restore
    8. Select the Input Message tab and enter a Title and an Input message:Input Message
    9. Select the Error Alert tab, enter a Title and an Error message, and then click OK:Error Alert
    10. Note that selecting any of the cells E2:E17 in the sheet named "Sales" opens a drop-down list from which you can select one of the allowed entries. In addition, your Input Message is displayed and entering an invalid entry results in your Error Message popping up:Error Message

Consolidating Data

Use the Consolidate command to join values from a number of different ranges into one new range. You can use Consolidate to sum the values in other ranges, to get an average of the values, to find the min or max value, and more.

To consolidate data from different ranges:

  1. On the DATA tab, in the Data Tools group, click the Consolidate command:Consolidate Command
  2. In the Consolidate dialog box:
    1. Select the Function to use.
    2. Click the red arrow to the right of the Reference data entry box.
    3. Select the first series of cells you want to consolidate and then click the red arrow in the Consolidate - Reference dialog box to return to the Consolidate dialog box.
    4. Click Add to add the series of cells to the All references box.
    5. Repeat steps B, C, and D until you have selected all series of cells that you want to consolidate.
    6. Click OK:Click OK

Consolidating Data

Duration: 15 to 25 minutes.

In this exercise, you will consolidate students' grades on four different exams to show their average, lowest, and highest grades on a summary sheet.

  1. Open Consolidate.xlsx from your Excel2013.3/Exercises folder.
  2. Students' grades on four exams are stored on the first four sheets in this workbook. Use the Consolidate command to show the average, lowest, and highest grade for each student on the sheet named "Consolidate".

Solution:

  1. To show the average grade for each student:
    1. On the sheet named "Consolidate", select cells B2:B11.
    2. On the DATA tab, in the Data Tools group, click the Consolidate command:Consolidate Command
    3. In the Consolidate dialog box, set the Function to Average and then click the red arrow to the right of the Reference data entry box:Reference Data Entry Box
    4. Navigate to the sheet named "Exam 1" and select cells B2:B11 and then click the red arrow at the right of the Consolidate - Reference dialog box:Exam 1 Sheet
    5. Back in the Consolidate dialog box, click Add:Add
    6. Repeat steps C, D, and E, selecting cells B2:B11 on the sheets named "Exam 2", "Exam 3", and "Exam 4".
    7. Now that all four series of data have been added to the All references box, click OK:Four Data Series
  2. To show the lowest grade for each student:
    1. On the sheet named "Consolidate", select cells C2:C11.
    2. On the DATA tab, in the Data Tools group, click the Consolidate command:Consolidate Command
    3. In the Consolidate dialog box, set the Function to Min. Note that the series of data from the prior exercise already appears in the All references box. Were we using different data, you could delete these references and add others. As we are using the same data in this exercise, simply click OK:Exercise
  3. To show the highest grade for each student:
    1. On the sheet named "Consolidate", select cells D2:D11.
    2. On the DATA tab, in the Data Tools group, click the Consolidate command:Consolidate Command
    3. In the Consolidate dialog box, set the Function to Max. Once again, as we are using the same data as in the prior exercises and it already appears in the All references box, simply click OK:All References Box

Goal Seek

If you know the result of a formula but not the value of a specific variable on which the formula depends, you can use Goal Seek to adjust the variable to display the desired value. Goal Seek can only adjust one variable in a formula, so if a formula depends on more than one variable, you will need to decide which variable to adjust.

To use Goal Seek:

  1. On the DATA tab, in the Data Tools group, click the What-If-Analysis command:What-If-Analysis Command
  2. Click Goal Seek:Goal Seek
  3. In the Goal Seek dialog box:
    1. In the Set cell box, enter the cell for which you know the result (if you selected this cell before clicking Goal Seek, then it is already filled in).
    2. In the To value box, enter the value for the cell above.
    3. In the By changing cell box, enter the cell to adjust in order to obtain the value. (Remember, this cell must be related via a formula to the cell above or else changing one won't impact the other.)
    4. Click OK:OK
  4. In the Goal Seek Status dialog box, after the calculation has completed, click OK to accept the change or Cancel to reject it:Calculation Completed

Using Goal Seek

Duration: 15 to 25 minutes.

In this exercise, you will practice using Goal Seek to adjust the number of periods it will take to pay off a loan based on varied payments and to figure out what grade a student needs on their final exam to achieve a target grade for a course.

  1. Open Goal Seek.xlsx from your Excel2013.3/Exercises folder.
  2. The worksheet named "Loans" contains information on four loans. The Payment is calculated via the PMT formula. Use Goal Seek to determine:
    1. How many months (periods) it will take to pay off your boat if you can only pay $100 per month.
    2. How many months (periods) it will take to pay off your first car if you can only pay $250 per month.
    3. How many months (periods) it will take to pay off your second car if you can pay $200 per month.
    4. How many months (periods) it will take to pay off your house if you can pay $1,000 per month.
  3. The worksheet named "Grades" contains scores for a number of students on four exams, an empty column where the score for the fifth exam will go, and a column showing the average score on all exams. Use Goal Seek to figure out:
    1. What grade Babe Ruth needs to get on the last exam to raise his final grade to 90.
    2. What grade Barry Bonds needs to get on the last exam to achieve a passing grade (65).
    3. What the lowest grade Cal Ripken can get on the last exam to finish with a final grade of 90.

Solution:

  1. To determine how many months it will take to pay off your boat if you can only pay $100 per month:
    1. On the DATA tab, in the Data Tools group, click the What-If-Analysis command and then click Goal Seek:Goal Seek Command
    2. In the Goal Seek dialog box:
      1. In the Set cell box, enter cell B2.
      2. In the To value box, enter -100.
      3. In the By changing cell box, enter D2:
      4. Click OK:Click OK
    3. In the Goal Seek Status dialog box, after the calculation has completed, click OK to accept the change:Accept Change
    4. The correct result is 63 months.
  2. To determine how many months it will take to pay off your first car if you can only pay $250 per month:
    1. On the DATA tab, in the Data Tools group, click the What-If-Analysis command and then click Goal Seek:Goal Seek
    2. In the Goal Seek dialog box:
      1. In the Set cell box, enter cell B3.
      2. In the To value box, enter -250.
      3. In the By changing cell box, enter D3:
      4. Click OK:Click OK
    3. In the Goal Seek Status dialog box, after the calculation has completed, click OK to accept the change:Accept the Change
    4. The correct result is 148 months.
  3. To determine how many months it will take to pay off your second car if you can pay $200 per month:
    1. On the DATA tab, in the Data Tools group, click the What-If-Analysis command and then click Goal Seek:Goal Seek
    2. In the Goal Seek dialog box:
      1. In the Set cell box, enter cell B4.
      2. In the To value box, enter -200.
      3. In the By changing cell box, enter D4:
      4. Click OK:Click OK
    3. In the Goal Seek Status dialog box, after the calculation has completed, click OK to accept the change:Accept Change
    4. The correct result is 42 months.
  4. To determine how many months it will take to pay off your house if you can pay $1,000 per month:
    1. On the DATA tab, in the Data Tools group, click the What-If-Analysis command and then click Goal Seek:Goal Seek
    2. In the Goal Seek dialog box:
      1. In the Set cell box, enter cell B5.
      2. In the To value box, enter -1000.
      3. In the By changing cell box, enter D5:
      4. Click OK:Click OK
    3. In the Goal Seek Status dialog box, after the calculation has completed, click OK to accept the change:Accept the Change
    4. The correct result is 252 months.
  5. To figure out what grade Babe Ruth needs to get to raise his final grade to 90, go to the sheet named "Grades" and then:
    1. On the DATA tab, in the Data Tools group, click the What-If-Analysis command and then click Goal Seek:Goal Seek
    2. In the Goal Seek dialog box:
      1. In the Set cell box, enter cell G2.
      2. In the To value box, enter 90.
      3. In the By changing cell box, enter F2:
      4. Click OK:Click OK
    3. In the Goal Seek Status dialog box, after the calculation has completed, click OK to accept the change:Accept the Change
    4. Babe Ruth needs a 100 on the final exam to raise his average to 90.
  6. To figure out what grade Barry Bonds needs to get on the last exam to achieve a passing grade (65):
    1. On the DATA tab, in the Data Tools group, click the What-If-Analysis command and then click Goal Seek:Goal Seek
    2. In the Goal Seek dialog box:
      1. In the Set cell box, enter cell G8.
      2. In the To value box, enter 65.
      3. In the By changing cell box, enter F8:
      4. Click OK:Click OK
    3. In the Goal Seek Status dialog box, after the calculation has completed, click OK to accept the change:Accept the Change
    4. Barry Bonds needs a 133 on the final exam to achieve a passing grade. Poor Barry Bonds.
  7. To figure out the lowest grade Cal Ripken can get on the last exam to finish with a final grade of 90:
    1. On the DATA tab, in the Data Tools group, click the What-If-Analysis command and then click Goal Seek:Goal Seek
    2. In the Goal Seek dialog box:
      1. In the Set cell box, enter cell G9.
      2. In the To value box, enter 90.
      3. In the By changing cell box, enter F9:
      4. Click OK:Click OK
    3. In the Goal Seek Status dialog box, after the calculation has completed, click OK to accept the change:Accept the Change
    4. Cal Ripken needs at least a 79 on the final exam to finish with a final grade of 90.