Interested in becoming an Affiliate Trainer? We are now accepting applications for local and international positions. No need for cold calling! Receive referrals of people looking for specialized training in your area and get access to all of the training curriculum I have created and continue to create to use at your appointments.
Data Tools in Microsoft Excel are 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. We will review the most commonly used Data Tools.
If you have a list of data where all the information appears in one column, you can use the Text to Columns command to convert the data to multiple columns. This can save you time by not having to retype the data.
We have a list of first and last names in columns A, shown to the left. To convert the first and last names into two columns, first select Column A.
On the Data tab, in the Data Tools group, click the Text to Columns command.
The Convert Text to Columns Wizard – Step 1 of 3 dialog box will appear as shown below.
In the dialog box, select either Delimited or Fixed width. We are going to select Delimited and then click on Next.
In Step 2 of the dialog box, select the Delimiters (assuming you selected Delimited in the previous step) that meet your criteria. If you aren’t sure what to select, you can select and deselect the options and see the results in the Data Preview (as shown in the image below).
In Step 3 of the dialog box, you can select the data format of each column or you can elect not to import a column. 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.
As you can see in the image to the left, we have quickly separated our first and last names from one column to two.
We applied additional formatting (alignment and borders) to make our information look presentable.
When records are entered into a spreadsheet more than once or you have combined records from multiple sources, there is a good chance that some data has been duplicated. Excel has a feature to quickly Remove Duplicates.
When using the Remove Duplicates command, it permanently deletes data, so it is a good idea to copy the original range of cells or table to another worksheet or workbook before removing duplicate values.
Start by selecting the range of cells and click on the Data tab. In the Data Tools group, select the Remove Duplicates button.
The Remove Duplicates dialog box will appear as shown in the image to the right.
Under Columns, you can select one or more of the columns. To choose all of the columns, click on the Select All button. To quickly remove all of the columns, click on the Unselect All button. Click OK after making your column selections.
Excel will display a message indicating how many duplicate values were removed and how many unique values remain, or if no duplicate values were removed.
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 number or dates, or you can allow only items from a specified list.
To apply data entry restrictions, select the cells that you will be verifying the data. On the Data tab, in the Data Tools group, click the Data Validation command.
The Data Validation dialog box will appear.
On the Settings tab, choose which types of values to allow. In our example, we selected Date and then input a start and end date.
You can also 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, as shown in the image to the left.
Make sure that Show input message when cell is selected is checked, and enter the Title and Input Message. Select OK when finished.
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. 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.
Select OK after making your selections.
You can use Excel’s Consolidate feature to consolidate your worksheets (located in one workbook or multiple workbooks) into one worksheet.
We begin in a blank or new workbook or worksheet. Go to the Data tab and select Consolidate from the Data Tools section. The Consolidate dialog box will open (as shown in the image to the right).
We are going to use the Sum function. It is the default function and the most commonly used.
Next, we select the Reference ranges for our consolidation. Click on Region 1 worksheet and select the data (A1 through E4). Since we are consolidating by category, we want to select the labels in the top row and the left column. To do this, make sure the Top row and Left column boxes are checked, and then click Add. Do the same with the Region 2 and Region 3 information. Once all three Regions have been added to the Consolidate window, click OK.
Excel has a feature called the What-if Analysis. This feature allows you to fill in the blanks based on the information you have. It is a somewhat more advanced feature and you’ll need to be pretty comfortable using formulas and functions before you can really use it.
To show you how it works, we will be using one of the more common functions, Payment or PMT to help determine what interest rate we will need. As you can see in the image to the right, we are looking for a maximum home loan of $250,000. We will have a fixed term mortgage of 30 years or 360 months, which will be 360 monthly payments.
The first thing we will need to do is set up a function in cell B5. Select the Formulas tab and click on Financial in the Function Library group. In the drop-down menu, scroll down and select the PMT function. The Function Arguments dialog box will appear, as shown in the image below. The PMT function calculates the payment for a loan based on constant payments and a constant interest rate.
For the Rate we will enter cell B4/12. We divide it by 12 because the yearly interest rate is divided up into 12 months. The next field, Nper, is the number of payments, and that will be found in cell B3. Next is Pv, the present value, which is the total amount of the loan found in cell B2. We don’t need to worry about the last two arguments. Select OK when finished.
Since our Interest Rate cell is blank (shown in the image to the left), Excel is assuming that the interest rate is 0%, which means the Payment they give us is incorrect. This is where the What-If Analysis comes in.
It will allow us to say, “If the payment was $1,200, then what would the interest rate be?”
Go to the Data tab and select the What-If Analysis command in the Data Tools group. There are three options in the drop-down menu, shown in the image to the right. We are going to select Goal Seek.
The first cell, Set cell, will be the cell that contains the monthly payment or B5. We want a $1,200 payment but the PMT function actually needs to see a negative value here, so type -$1,200. It needs to be negative because the amount is being subtracted from the loan when you make a payment. Where it says By changing cell we will put the blank cell which we are trying to find the value for (B4).
Our answer is 4.04%, which is shown in the image to the left. This is the interest rate I will look for when looking for a home mortgage.
This concludes the Data Tools tutorial.
Click next to continue to the Excel 2010 Outline tutorial.