Tag: excel tips

Excel: Convert Numbers from Text

On By Ben Larson Ph.D.In excelLeave a comment

Below is the link to an actual file I was emailed today:

Excel File: Scores Won’t Sum

This is a list of scores of test participants. Obviously all identifying information has been stripped from the file. I just want you to see that this is a real world example, not some silly made up sample.

This file was sent to me asking if I could return a pivot table showing the averages of all the grades. Note that not only are there multiple columns, but notice the ID field. One individual can represent multiple rows depending on how many courses they are taking.

grades

Now figuring this out by hand (especially considering the original file had over 500 rows) would be a pain in the rear, but it should be no issue for a Pivot Table.

But look what happens when I try to average S1 against ID in a Pivot Table

grades1.jpg

So I go back to the main sheet and try performing a SUM function on the first Row. This results in 0 as an answer.

grades2.jpg

I try my hidden value trick: Numbers don’t add up in Excel

But still nothing. Then I remember about Paste Special

To fix the problem, go find an empty cell and type 1. Make sure to take note of the placement of your 1 in the cell. It should hug the right cell wall. If it justifies left, Excel is seeing it as text – try another cell.

Also check that your cell is marked as General in the drop down menu on the Number portion of the Ribbon bar.

grades3

Copy the cell with 1 in it. Highlight the columns you are having trouble with – right click and select Paste Special

grades4.jpg

Click Multiply and Okay – this multiplies all the highlighted cells by 1

grades5.jpg

Notice my Sum function now works

grades6

You will notice that empty fields are now populated with 0. This isn’t an issue with summation, but it will give you bad readings on an average. I chose to get rid of those using an If statement. I inserted a column and ran the following statement.

=if(C2=0,””,C2)

(if you need a primer on if statements – follow this link: Excel: =If() – Conditional Formulas )

This states that if C2 = 0 replace with an empty space “”, else just populate C2

After that is done, I copy my new column, and Paste Special – Values  over the old column.

Now I can repeat for each column. When done, delete your inserted column.

grades7.jpg

Now my Pivot Table looks much better.

grades8

 

 

Power Query: Merge Data Sets

On By Ben Larson Ph.D.In excel, Uncategorized3 Comments

For this tutorial, you will need to have Power Query installed. If you are running Office 2016, Power Query should already be available. For Excel 2010 and 2013, here is a link to the download: Power Query

Here is a link to the practice file for this Lesson: Data Cleaning Power Query

If you open the Excel file, you will see 3 sheets with 3 tables (Work Order, Vehicles, Prices). We want to populate a single sheet combining the three tables into one data set.

datacleanPowerQuery

Open up a New Excel Workbook (A new file. Do not try working from the practice file). From the Ribbon bar select Power Query > From File > From Excel

datacleanpowerquery1

Select the practice file: dataCleaningPQuery.xlsx and when the Navigator pops up, select Work Orders

datacleanpowerquery2

Select Load. Once the work order loads, repeat the process, this time loading Prices.

**Note if the Query Editor window pops up, just click Close and Load. We will be working in the Query Editor window later. 

datacleanpowerquery4

Now go to the sheet with the Work Order table on it. Power Query > Merge

datacleanpowerQuery6

A) The top drop down box should already be populated. If not, select Work Orders for top and then select Prices for the bottom.

B) Now highlight the Service Columns in both tables.

C) Leave the Join Kind at Left Outer.

A Left Outer Join works as seen below. The left table is displayed in full and the the right table adds data based on columns that match up against data in the left table.

datacleanPquery

Note the text below “The selection has matched 29 out of the first 29 rows.” This is because if you look at the two columns we selected, they both have matching text. It is through this matching that we are able to line up the two tables into one.

Click OK and the Query Editor window will pop up.

Select the dual arrow icon in the NewColumn header and deselect Service from the list.

datacleanpowerquery8

Right click the NewColumn and Rename it Price. Click on it and hold down the mouse button. Now drag it so that price sits in between Service and Mech.

Hit Close & Load and your new merged table should look like this:

datacleanpowerquery9

Ok, now to load the Vehicle sheet.

Power Query > From File > From Excel. Select excel practice file and select Vehicles. DO NOT HIT LOAD this time. Instead, select Edit.

datacleanpowerquery10

If you look at the original data set, the Lic Plate column in the Work Order sheet and Licence Plate in Vehicles are the two column we need to match up. Unfortunately, they currently do not match. In the second table, the licence plates are preceded by the letters Lic. We need to remove this.

Right click on the Licence Plate> Replace Values

datacleanpowerquery11

Type Lic  into Value to  Find and leave Replace with blank. **Note — the Value to Find is “Lic ” with a space after it. Make sure you add the space.

datacleanpowerquery12

Click OK, the Licence Plate column will remove the Lic. Click Close & Load

Now it is time to merge. Go back to the Work Order sheet and click Merge

Set the top table to Work Orders. Set the bottom to Vehicles. Highlight Lic Plates and Licence Plate columns. Set Join to Left Outer Join and click okay.

datacleanpowerquery13

Click the double arrows on the new column and un-check column 1.

Rename the new columns Make and Model. Highlight both column and move them in between Lic Plate and Service. Click Close&Load

datacleanpowerquery15

Congratulations. You have now successfully merged 3 tables into 1 using Power Query. Now you can go forth and analyze the data.