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.
- 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:
- Select the column that holds the text you want to convert into multiple columns.
- On the Data tab, in the Data Tools group, click the Text to Columns command:
- 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:
- 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:
- 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:
Converting Text to Columns
Duration: 10 to 20 minutes.
In this exercise, you will practice converting text to columns.
- Open Text to Columns.xlsx from your Excel2019.3/Exercises folder.
- 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!)
- 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.
- 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.)
- In the sheet named Phone Numbers, separate the area codes by converting the text in column A to three columns.
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.
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:
- From the File menu tab, select Options.
- In the Excel Options dialog box, select Formulas on the left. Set options under Calculation options and click OK to save the changes.
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:
- Select the cells for which to verify data.
- On the Data tab, in the Data Tools group, click the Data Validation command:
- In the Data Validation dialog box:
- On the Settings tab, choose which types of values to allow:
- 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:
- 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:
- 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:
- 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.
- Open Data Validation.xlsx from your Excel2019.3/Exercises folder.
- 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".
- Use Data Validation to restrict the data that can be entered into cells D2:D17 to a date in 2012.
- 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.
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:
- On the Data tab, in the Data Tools group, click the Consolidate command:
- In the Consolidate dialog box:
- Select the Function to use.
- Click the red arrow to the right of the Reference data entry box.
- 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.
- Click Add to add the series of cells to the All references box.
- Repeat steps B, C, and D until you have selected all series of cells that you want to consolidate.
- Click OK:
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.
- Open Consolidate.xlsx from your Excel2019.3/Exercises folder.
- 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.
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:
- On the Data tab, in the Forecast group (the Data Tools group in Excel 2013), click the What-If-Analysis command:
- Click Goal Seek:
- In the Goal Seek dialog box:
- 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).
- In the To value box, enter the value for the cell above.
- 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.)
- Click OK:
- In the Goal Seek Status dialog box, after the calculation has completed, click OK to accept the change or Cancel to reject it:
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.
- Open Goal Seek.xlsx from your Excel2019.3/Exercises folder.
- The worksheet named Loans contains information on four loans. The Payment is calculated via the PMT formula. Use Goal Seek to determine:
- How many months (periods) it will take to pay off your boat if you can only pay $100 per month.
- How many months (periods) it will take to pay off your first car if you can only pay $250 per month.
- How many months (periods) it will take to pay off your second car if you can pay $200 per month.
- How many months (periods) it will take to pay off your house if you can pay $1,000 per month.
- 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:
- What grade Babe Ruth needs to get on the last exam to raise his final grade to 90.
- What grade Barry Bonds needs to get on the last exam to achieve a passing grade (65).
- What the lowest grade Cal Ripken can get on the last exam to finish with a final grade of 90.