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

Lesson: Data Tools

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

Data Tools in Microsoft Excel are simply tools that 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

  • Learn to convert text to columns.
  • Learn to use Data Validation to restrict the type of data that can be entered into a cell.
  • Learn to consolidate data from a number of different ranges into one new range.
  • Learn to 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 Column
  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:Wizard Step 1
  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:Wizard Step 2
  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:Wizard Step 3

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 Excel2019.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
    3. In the Convert Text to Columns Wizard - Step 1 of 3 dialog box, select Delimited and click Next:Step one
    4. In the Convert Text to Columns Wizard - Step 2 of 3 dialog box, check Space and click Next:Step two
    5. In the Convert Text to Columns Wizard - Step 3 of 3 dialog box, click Finish:Step three
  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 column
    3. In the Convert Text to Columns Wizard - Step 1 of 3 dialog box, select Delimited and click Next:Step one
    4. In the Convert Text to Columns Wizard - Step 2 of 3 dialog box, select Comma and click Next:Step two
    5. In the Convert Text to Columns Wizard - Step 3 of 3 dialog box, set the Destination to cell C1 and click Finish:Step three
  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 column
    3. In the Convert Text to Columns Wizard - Step 1 of 3 dialog box, select Delimited and click Next:Step one
    4. In the Convert Text to Columns Wizard - Step 2 of 3 dialog box, select Comma and click Next:Step two
    5. In the Convert Text to Columns Wizard - Step 3 of 3 dialog box, click Finish:Step three
    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:Text to Column
    3. In the Convert Text to Columns Wizard - Step 1 of 3 dialog box, select Delimited and click Next:Step one
    4. In the Convert Text to Columns Wizard - Step 2 of 3 dialog box, select Other, enter a dash ("-"), and click Next:Step two
    5. In the Convert Text to Columns Wizard - Step 3 of 3 dialog box, click Finish:Step three

Linking to External Data

In Excel, 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.

Data Tab

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. Set options under Calculation options and click OK to save the changes.Formulas 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. In the Data Validation dialog box:
    1. On the Settings tab, choose which types of values to allow:Settings Tab
    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:Data Options
    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:Input Message Tab
    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 Tab
    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 Excel2019.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. Click Data Validation:Data Validation Command
    3. In the Data Validation dialog box, on the Settings tab, under Allow, select List:List Option
    4. Click the red arrow to the right of the Source data entry box to collapse the Data Validation dialog box. 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.Data Validation Dialog Box
    5. Back in the main Data Validation dialog box, click OK:OK Option
    6. 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:Select Entry
  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
    3. In the Data Validation dialog box, on the Settings tab, under Allow, select Date. Under Data, select between. Under Start date, enter "1/1/19" and under End date, enter "12/31/19". Click OK.Date Option
    4. Only dates in 2019 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. In the Data Validation dialog box, on the Settings tab, under Allow, select List:List Option
    4. Click the red arrow to the right of the Source data entry box to collapse the Data Validation dialog box:Data Vaidation Dialog Box
    5. Navigate to the sheet named Territories and select cells A2:A6:Cell Selection
    6. Click the red arrow at the right of the Data Validation dialog box to restore it:Restore
    7. Select the Input Message tab and enter a Title and an Input message:Input Message Tab
    8. Select the Error Alert tab, enter a Title and an Error message, and then click OK:Error Message Tab
    9. 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:Consolidate Dialog Box

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 Excel2019.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:Average
    4. Navigate to the sheet named Exam 1 and select cells B2:B11 and then click the arrow at the right of the Consolidate - Reference dialog box:Consolidate - Reference Dialog Box
    5. Back in the Consolidate dialog box, click Add:Consolidate
    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: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:Consolidate Dialog Box
  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:Consolidate Dialog 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 Forecast group (the Data Tools group in Excel 2013), 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:Goal Seek Dialog Box
  4. In the Goal Seek Status dialog box, after the calculation has completed, click OK to accept the change or Cancel to reject it:Goal Seek Status Dialog Box

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 Excel2019.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 Forecast group (the Data Tools group in Excel 2013), click the What-If-Analysis command and then click Goal Seek:Goal Seek Option
    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:Goal Seek Dialog Box
    3. In the Goal Seek Status dialog box, after the calculation has completed, click OK to accept the change:Goal Seek Status
    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 Forecast group (the Data Tools group in Excel 2013), click the What-If-Analysis command and then click Goal Seek:Goal Seek Option
    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:Goal Seek Dialog Box
    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 Forecast group (the Data Tools group in Excel 2013), click the What-If-Analysis command and then click Goal Seek:Goal Seek Option
    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:Goal Seek Dialog Box
    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 Forecast group (the Data Tools group in Excel 2013), click the What-If-Analysis command and then click Goal Seek:Goal Seek Option
    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:Goal Seek Dialog Box
    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 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 Forecast group (the Data Tools group in Excel 2013), click the What-If-Analysis command and then click Goal Seek:Goal Seek Option
    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:Goal Seek Dialog Box
    3. In the Goal Seek Status dialog box, after the calculation has completed, click OK to accept the change:Accept 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 Forecast group (the Data Tools group in Excel 2013), click the What-If-Analysis command and then click Goal Seek:What-If-Analysis
    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:Goal Seek Dialog Box
    3. In the Goal Seek Status dialog box, after the calculation has completed, click OK to accept the change:Accept 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 Forecast group (the Data Tools group in Excel 2013), 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:Goal Seek Dialog Box
    3. In the Goal Seek Status dialog box, after the calculation has completed, click OK to accept the change:Goal Seek Status Dialog Box
    4. Cal Ripken needs at least a 79 on the final exam to finish with a final grade of 90.