Tag: excel tutorial

Excel: Copy, Cut, Paste, and Format Painter

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

This is a very basic introduction into Excel. I am going to start with the upper left hand corner of the Ribbon bar: the Clipboard region.

2016-12-18_15-35-08.jpg

While I am aware most of you already know how to use these features, my website is for everyone, including the most base beginner. I remember the frustration of learning the fundamentals of analytics from websites that assumed I already had a PhD and 10 years of work experience in the area.

So feel free to skip this, or take a few minutes to read through, you might be surprised. There may be some tricks in this little corner of Excel you were not aware of.

Cut, Copy, and Past

These 3 features are ambiguous with computer use dating back to days DOS. As a matter of fact, the old keyboard shortcuts used back then still work.

Cut (Ctrl-X) – deletes the highlighted text and stores it in local memory (a clipboard)

Copy (Ctrl-C) – leaves highlighted text as is, but saves a copy of it into local memory

Paste(Ctrl-V) – pastes the contents of the clipboard into the spot you have chosen.

Below are two examples

CUT

Start by highlight the rows A:1 -A:4, click Cut (or Ctrl-X). Now Select Cell C1. Click Paste (or Ctrl-V). Notice column A is now empty.

COPY

Start by highlight the rows A:1 -A:4, click Copy (or Ctrl-C). Now Select Cell C1. Click Paste (or Ctrl-V). Notice Column C is now a Copy of Column A

Clipboard

If you click on the bottom right of the Clipboard box, the clipboard window opens up, showing your the current contents saved to the clipboard

2016-12-18_17-37-39

So now if I add some letters to column B and copy it, that will end up in the Clipboard as well. Notice the original data is still in the clipboard

2016-12-18_17-42-08.jpg

Now when you want to paste, you can choose which item in the clipboard to paste. Without using the clipboard, Excel will paste the most recent item added to the clipboard by default.

2016-12-18_17-40-38.jpg

Copy as Picture

You might notice a drop down arrow next to Copy in the Ribbon Bar. If you click on it, you will see Copy as Picture as an option. This is great when working with Charts. This saves the data as a picture or bitmap so when you paste it elsewhere it will not be affected by changes to the source data.

What I mean by that is, looking at the chart below, column b has a value of 2. If I change that 2 to a 4 in the data table this chart was created from the bar representing b would change to 4 (so would any copied charts). But a chart copied “as picture” would not change. Imagine it to be like a screen shot.

2016-12-18_17-47-52.jpg

Paste Special

Notice the options below Paste when you hit the drop down arrow. Some of the more popularly used are Transpose (turns a vertical list to horizontal list and vise versa). Another popular option is to paste “values”. This is useful when trying to copy a calculated value where all you want is the number (not the formula who made it).

2016-12-18_20-34-34.jpg

Format Painter

Format Painter can help you repeat text and color formatting with just a few clicks

Start by adding color and font bolding to a set of cells. Highlight those cells and click Format Painter

2016-12-18_20-40-38

You will now see your cursor is paint brush. Find a target you want to duplicate your formatting to and click on it.

2016-12-18_20-41-03

And now it looks the same.

2016-12-18_20-41-19

 

Excel: Charts, Customizing Which Columns You Want to Chart

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

Here is the data set if you want to play along: ExcelCharts2

While Excel does a pretty good job of auto-formatting charts based on your data, sometimes a little customization work is in order. Take this data below for example:

What if I want to create a bar chart of the average readings?

chart1.jpg

Well first we need to make an “Average” column

chart2.jpg

chart3.jpg

I really don’t need all the decimal places. Hit the decimal reduce button in the picture below until you have a column full of integers.

chart4.jpg

Chart Method 1

Just like you would normally, select all the data and go to Insert > Column Chart

chart5.jpg

Right Click on your new chart and Select Data…

chart6.jpg

I don’t want my readings on the X axis, I want my Sensor Names, so click Switch Row/Column in the new window.

chart7.jpg

The X Axis is now A,B,C,D

chart8.jpg

Now, we only want to see the Average  column, so uncheck Reading 1,2,3 

chart9.jpg

Here is our chart

chart10

Chart Method 2

Don’t select any data yet. Just go to the Insert and Select Column Chart

chart11

Click Select Data now from the Ribbon (if you don’t see this, click anywhere on your new blank chart and this should appear in the Ribbon)

chart12

Click Add in the low left box.

chart13.jpg

In Series Name, either type a name or select the column header

chart14.jpg

In Series values: put put your data rows from the Average  column

chart15.jpg

Now we have data loaded into our chart, let’s change the data labels for our X Axis. In the Horizontal(Category) Axis Labels –  click Edit

chart16.jpg

Highlight A,B,C,D from the Sensor column

chart17

Now you see the numbers have been replaced by A,B,C,D

chart18

Here is our chart

chart19.jpg

 

 

Excel: Sorting and Filtering

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

Download Exercise File Here: ExcelTable

Sorting and Filtering are two basic functions you will perform quite often in Excel. When dealing with large data sets, it can be difficult to see what you are looking for, especially if the records appear in random order. Using sort and filter, you can adjust the spreadsheet to show what you want to see.

Sort

Sorting is simple in Excel. Just click on the header cell for the column you want to sort (in my example I chose “Name”). From the Ribbon Bar select Data. Now click on the AZ and ZA arrows. The column you selected will sort.

excelsort

Now, try it out on the other columns as well.

Finally, select the box directly above the word sort in the picture above. This will give you an advanced sort menu.

From this menu,  can select the column you wish to sort, the values you want to sort on, and the sort order.

excelsort1.jpg

Filter

To filter, select the Filter icon from the Data tab in the Ribbon Bar

excelsort2

Notice arrows now appear in the column header cells

excelsort3

Click on the arrow for Quarter. Now, uncheck the Select All box and check Quarter 2 > Okay

excelsort4.jpg

Now your sheet only has rows containing Quarter 2. Take note of the row numbers though. Notice the row numbers skip around. This is because the other rows are still there, they are just hidden by the filter.

excelsort5.jpg

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.