Tumblelog by Soup.io
Newer posts are loading.
You are at the newest post.
Click here to check if anything new just came in.

November 09 2013

Play fullscreen
How to Create a Dropdown List in Excel - YouTube
http://vitamincm.com/create-excel-dropdown-list/ Learn How to Create a Dropdown List in Excel. This video tutorial accompanies the how to create a dropdown l...
Play fullscreen
How to Create a Dropdown List in Excel - YouTube
http://vitamincm.com/create-excel-dropdown-list/ Learn How to Create a Dropdown List in Excel. This video tutorial accompanies the how to create a dropdown l...

January 06 2013

Excel Pivot Table Tutorial

Slice and Dice your Excel Data like a Magician

Excel Pivot Table Tutorial

Excel Pivot Table Tutorial


Excel spreadsheets are handy tools for tracking and analyzing data. However, they do have some limitations. If you build a spreadsheet in a certain layout, it will often take a fair amount of time and work to provide alternate views of the data.

That’s where Pivot Tables come to the rescue. Pivot Tables allow you to take a large block of Excel data and slice & dice it in numerous useful ways. The best thing is that you don’t ever have to risk damaging your source data to produce these alternate views.

The spreadsheet below tracks product sales by day for a Fruit stand. We’ll see how a Pivot table can give us lots of different insights into this data.

Excel pivot table tutorial 01

Creating the Pivot Table from Existing Data

Go to the Insert tab and click the Pivot Table button.

Excel pivot table tutorial 02

The Create Pivot Table window will open.

Excel pivot table tutorial 03

You can pull  in data from an external source if you want, but typically you will just use the data in the sheet you are on.

You can also put the pivot table in the existing sheet or in a new one. It’s a little bit cleaner to put the pivot table in its own tab.

Click the OK button.

Your Pivot Table will open in a new tab. There will be no data displayed, but all of the fields in your original tab will be displayed in the Pivot Table Field List on the right side of the screen. You can arrange these fields in numerous useful combinations.

Excel pivot table tutorial 04

Drag the fields into their buckets at the bottom of the Pivot Table Field List.

Your Pivot Table’s data will be displayed based on your settings. The image below shows where the fields appear on the actual table.

Excel pivot table tutorial 05

The values in your table may need to have a particular number formatting. This data is money, so I’m going to set the format to Currency.

Click the Sum of Sales field in the Values box.

A menu will appear.

Excel pivot table tutorial 06

Select Value Field Settings from the menu.

The Value Field Settings menu will open.

Excel pivot table tutorial 07

You can change the Values to other formulas (count, average, etc.) if you need to.

Click the Number Format button.

The Format Cells menu will open.

Excel pivot table tutorial 08

Select Currency from the Category  list and click the OK button.

You will see the pivot table

Excel pivot table tutorial 09

Filtering and Sorting Your Data

The data in all of the fields in your new Pivot Table can be filtered and sorted to display or hide any data. Use the dropdown buttons on any field to pull up the Filter options.

Excel pivot table tutorial 10

You can sort the values of a field A to Z or Z to A or use more advanced options. Let’s see the Filtering options.

You will see all of the values for the selected field. You can check and uncheck any combination of values to show or hide those values. The field below contains all of the products in listed. I only want to see results for Apples and Oranges.

Excel pivot table tutorial 11

Check the values that you want to see and click the OK button.

Now you will only see those values (Apples and Oranges) displayed. You will also see the Filter icon displayed on the dropdown button.

Excel pivot table tutorial 12

Rearranging (Pivoting) Your data

You can completely rearrange the layout of the data on report by dragging the fields around the areas of the Pivot Table Fields List in the lower right corner of the screen. I currently have the report displaying Products down the rows and Days across the columns. I want the switch it so that the Days go down the rows and the Products go across the columns.

Excel pivot table tutorial 13

Drag the Day field into the Row Labels box and drag the Products field into the Column Labels field and click the Updated button.

Now you can see the report updated to the new layout.

Excel pivot table tutorial 14

Nesting Fields of Data

You can “nest” one field inside of another field in a pivot table. If you drag more than one field into one of the report areas the lower field will be grouped inside of the higher one.

Let’s see what it looks like if we move the Origin field above the Products field in the Column Labels area.

Excel pivot table tutorial 15

Drag Origin over and above Products and click the OK button.

You will see the values in the Origin field (Imported and Local) with the Imported or Local products grouped below.

Excel pivot table tutorial 16

You can drag the Products field ABOVE the Origin field to reverse the grouping.

Now you will see any Imported and Local data grouped inside of each Product.

Excel pivot table tutorial 17

This technique works the same way on the Row Labels.

Pulling in New Data

If you go back to the original worksheet where your source data is contained, you can make changes to the underlying data. You can have these changes reflected on your Pivot Table very easily.

Go back to the Pivot Table and right click in one of the cells.

An option menu will open.

Excel pivot table tutorial 18

Select Refresh.

The data in your pivot table will update to reflect any changes made in the underlying source data.

See the Video Tutorial for Using Excel Pivot Tables

The following video shows how to create and manipulate a Pivot Table in Microsoft Excel.

 

Fixing the Blown Capacitor in Samsung TV Yourself
Fixing the Blown Capacitor in Samsung TV Yourself
Title
Fixing the Blown Capacitor in Samsung TV Yourself
Runtime
2:33
Keywords
Category
Howto & Style
View count
2,954
How to Move the Home Directory on a Mac to a Different Location
Title
How to Move the Home Directory on a Mac to a Different Location
Runtime
3:34
Keywords
Category
Howto & Style
View count
1,714
Cloning the Hard Drive on a Mac to an External SSD Drive
Title
Cloning the Hard Drive on a Mac to an External SSD Drive
Runtime
4:05
Keywords
Category
Howto & Style
View count
12,528
iPhone Quick Tip - Shutting Down Background Apps
Title
iPhone Quick Tip - Shutting Down Background Apps
Runtime
1:47
Keywords
Category
Science & Technology
View count
12,550
The Fastest Way to Remove the Trial Crapware from a New PC
Title
The Fastest Way to Remove the Trial Crapware from a New PC
Runtime
4:48
Keywords
Category
People & Blogs
View count
2,163
How to Link Cells in Different Excel Spreadsheets
Title
How to Link Cells in Different Excel Spreadsheets
Runtime
5:05
Keywords
Category
Howto & Style
View count
34,978
How to Create an Interactive YouTube Video - Linking Videos Together
Title
How to Create an Interactive YouTube Video - Linking Videos Together
Runtime
4:09
Keywords
Category
Science & Technology
View count
7,254
How to Create an Interactive YouTube Video - Overview
Title
How to Create an Interactive YouTube Video - Overview
Runtime
2:17
Keywords
Category
Science & Technology
View count
9,458
How to Create a Dropdown List in Excel
Title
How to Create a Dropdown List in Excel
Runtime
2:53
Keywords
Category
Howto & Style
View count
6,046
Controlling Multiple Computers Using Synergy
Title
Controlling Multiple Computers Using Synergy
Runtime
8:39
Keywords
Category
Science & Technology
View count
15,932
Create RSS Feed for Static Web Sites
Title
Create RSS Feed for Static Web Sites
Runtime
5:53
Keywords
Category
Science & Technology
View count
12,550
Fix the Annoying Cell Phone Buzz in your Computer Speakers
Title
Fix the Annoying Cell Phone Buzz in your Computer Speakers
Runtime
1:36
Keywords
Category
Science & Technology
View count
30,204

Did you Like this Article?

Sign up for free updates from VitaminCM.com?

Subscribe to VitaminCM.com via RSS Reader

VitaminCM.com Email Feed

Email Address

VitaminCM on YouTube

VitaminCM on Twitter

See some of the other places where you can connect with me out there in the wilds of the internets.

July 10 2012

Master the Excel VLookup with this Simple Tutorial

Become an Office Hero with the Vlookup Function in Excel

excel vlookup tutoria vlookup tutorial how vlookup works excel vlookup tutorial Excel vlookup excel tutorial

Over the years I have been the Excel tips guy in my office. There are so many amazing things that Excel can do, but the one thing I get asked more than every other feature combined is: “How do I do a Vlookup”. Mastery of the Vlookup seems to be the one Excel trick that people use to separate experts from novices. Well, guess what? It’s really not hard at all.

What Exactly is a Vlookup?

The Vlookup function in Excel is used to see if a value in one cell (or list of cells) is in another group of cells. (“V” is for vertical – you can use th Hlookup function to check horizontally too) Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify. Take a look at the data below. Column A has a list of people who have already paid their bill. Column B has a list of customers who received services. We want to find out which customers have paid and which still owe payments. This is a typical Vlookup scenario: compare two lists and identify the differences.


Naming a Range in Excel

Before you actually create your Vlookup formula, you should create a “Named Range”. Excel can take a range of cells and assign a name to it that can be referenced in functions and formulas throughout the workbook.

Select the range of cells that contain the values that you want to check against.

excel vlookup tutorial 15 vlookup tutorial how vlookup works excel vlookup tutorial Excel vlookup excel tutorial

TIP: If you have a list of values that may be added to, select a range that is bigger than your existing list. This way, when the list grows, the new values will still be included in your named range. After you select your range of cells, you are ready to give it a name. Right click on the highlighted cells and select Name a Range from the menu.

The New Name menu will open. Type a name in the Name field. You can modify the actual range of cells (now or later) in the Refers to field.

excel vlookup tutorial 131 vlookup tutorial how vlookup works excel vlookup tutorial Excel vlookup excel tutorial

Click the OK button.

Now you can refer to this range (Paid) anywhere in this workbook.

Creating the Vlookup Formula

Now that we have a range of data to check against, we are ready to build our formula.

Click to the right of the first value that you want to check.

Click the formula builder button (fx) to search for the Vlookup function.

excel vlookup fx button vlookup tutorial how vlookup works excel vlookup tutorial Excel vlookup excel tutorial

Select the Lookup & Reference category and scroll down to the VLOOKUP function.

Click the OK button.

The Function Arguments wizard will open.

excel vlookup tutorial lookup value vlookup tutorial how vlookup works excel vlookup tutorial Excel vlookup excel tutorial

The Function Arguments wizard lists what the overall function does below the fields. When you click in one of the fields an explanation of what goes into that field is displayed. There are four fields to populate:

  • Lookup Value
  • Table Array
  • Column Index
  • Number Range Lookup

Click in the Lookup Value field and enter the cell for the value that you want to check against the range (B2 – Allan).

excel vlookup tutorial 10 vlookup tutorial how vlookup works excel vlookup tutorial Excel vlookup excel tutorial

Click in the Table Array field and enter the name of the range that we want to search for the Lookup Value.

Type “paid” in the Table Array field to reference the named range of values. Notice how the values in the named range appear to the right of the Table Array field.

Click in the Column Index Number field.

The range that we named contained all of the values in one column (A). If we had a range of data that resided in more than one consecutive column, we could specify which column’s value that we wanted to return to the formula cell. Since we only have one column, type “1” in the field.

excel vlookup tutorial 09 vlookup tutorial how vlookup works excel vlookup tutorial Excel vlookup excel tutorial

Click in the Range Lookup field.

There are two values that you can use in the Range Lookup field: TRUE or FALSE. True will find the “closest” value in the range and False will only find EXACT matches. Typically I want an exact match.

excel vlookup tutorial 08 vlookup tutorial how vlookup works excel vlookup tutorial Excel vlookup excel tutorial

Type “FALSE” in the field and click the OK button.

If the value is in the range the value from column 1 will be displayed in our cell.

excel vlookup tutorial 07 vlookup tutorial how vlookup works excel vlookup tutorial Excel vlookup excel tutorial

Extending the formula down the rest of the list

Just copy and paste the formula you built (C2) and paste it down the rest of the list.

excel vlookup tutorial 101 vlookup tutorial how vlookup works excel vlookup tutorial Excel vlookup excel tutorial

All of the remaining values will be checked against the range.

If a value is not found “#NA” will be displayed. In our case, we will know that those people did not pay yet.

Since we made our named range a little bigger that the existing data, we can add more data to the empty cells in the range and have them rechecked.

The example below shows that as long as we type into the range that we created it will be included in the formulas that reference it.

excel vlookup tutorial 121 vlookup tutorial how vlookup works excel vlookup tutorial Excel vlookup excel tutorial

Using a Multi Column Named Range to Extend your Vlookup

We used a single column range in the example above. Multi Column named ranges allow you to do some more interesting things with your Vlookups. I added an extra column to the spreadsheet that lists the amount that each person paid.

Then, I edited the range (Formulas tab> Name Manager> Select Paid> Edit button> Change range from “A29” to B29”)

This included the Amount column as the second column in the range. You can add several columns if you like. Now, when I find someone that already paid, I don’t want to return their name (I already know their name) I want to know how much they actually paid. (Column 2)

Reopen the Function Arguments wizard and click in the Column Index Number field.

Type “2” in the field and click the OK button.

excel vlookup tutorial 132 vlookup tutorial how vlookup works excel vlookup tutorial Excel vlookup excel tutorial

Re-copy the formula down the list.

Now, you’ll see who paid, how much they paid, and who still owes money.

excel vlookup tutorial 14 vlookup tutorial how vlookup works excel vlookup tutorial Excel vlookup excel tutorial

That’s it – you can do the Vlookup with the rest of the Excel gurus out there.

Excel VLOOKUP Video Tutorial

See how to do all of the steps listed above in this video tutorial:

Did you Like this Article?

Sign up for free updates from VitaminCM.com?

rss 1281 vlookup tutorial how vlookup works excel vlookup tutorial Excel vlookup excel tutorial

mail subscribe1 vlookup tutorial how vlookup works excel vlookup tutorial Excel vlookup excel tutorial

Email Address

youtube 1281 vlookup tutorial how vlookup works excel vlookup tutorial Excel vlookup excel tutorial

twitter follow1 vlookup tutorial how vlookup works excel vlookup tutorial Excel vlookup excel tutorial

See some of the other places where you can connect with me out there in the wilds of the internets.

Share and Enjoy

FacebookTwitterStumbleUponRSSPinterestGoogle Plus


November 13 2010

How to Link Cells in Different Excel Spreadsheets

Excel Spreadsheet Linking Tutorial

Overview: Learn how to link data in one Excel Spreadsheet to data in cells of another worksheet or workbook using linking formulas.

How to Link Cells in Different Excel Spreadsheets

Pulling All of your Information Together

Excel allows one worksheet to get information from cells in another worksheet. The other worksheet can be inside of the same workbook (file) or in an entirely different one. This is a nice way to use certain sheets for information input (think transactions, prices, account balances) and other worksheets to summarize and and report on the detailed data (think dashboards).

The process of linking data between Excel worksheets and/or workbooks actually only involves a few simple steps:

  1. Open all of the Excel files that you want to link
  2. Pick the linked data’s Destination cell
  3. Select the Soure Data to be linked
  4. Press the Enter key

Linking Excel Worksheets within the Same Workbook

Open your Excel spreadsheet and go to the worksheet where you want to display the linked data and click in the desired destination cell.

Excel linking between worksheets

Press the “equals” key on your keyboard to begin the linking formula. Then, click on the worksheet (tab) where the source data resides.

Click in the cell where the source data resides.

Excel linking between worksheets

Notice that the Worksheet name, then the cell name appear in the formula bar.

Press the Enter key.

The source data will be displayed in the destination cell.

Excel linking worksheets

Notice how the Worksheet and Cell are displayed in the formula bar.

Linking Excel Worksheets Between Different Workbooks

Open up all the your Excel spreadsheets whose data you would like to link.

Link Excel Spreadsheets

Go to the worksheet where you want the source data to be displayed and click in the destination cell. Press the “equals” key then switch to the Excel Workbook where the source data resides.

Click in destination cell

Click in the cell where the source data resides.

Source Data

Notice that the Workbook (file) Name, the Worksheet name, then the Cell name appear in the formula bar.

Press the Enter key.

The source data will be displayed in the destination cell.

Linking Excel Workbooks

That’s the entire process. Not that hard, right?

Linking Excel Spreadsheets Video Tutorial

See how to link up your spreadsheets in the video tutorial below.

Did you Like this Article?

Sign up for free updates from VitaminCM.com?

Subscribe to VitaminCM.com via RSS Reader





VitaminCM.com Email Feed

Email Address


 

VitaminCM on YouTube

VitaminCM on Twitter

See some of the other places where you can connect with me out there in the wilds of the internets.


July 22 2010

How to Create a Dropdown List in Excel

Why Type when you can Select?

OVERVIEW: Learn how to create a dropdown list that populates cells in Microsoft Excel.

how to create a dropdown list in excel

You know how e-commerce websites have dropdown lists to let you select data that they want to be valid? This is a pretty handy way of making sure that they get the correct data. Well, you can do this in Microsoft Excel too. The best part is that it takes a few seconds and it’s really simple.

Let’s go!

Creating Your Destination Cell

The first thing you need is a cell where the dropdown list will be placed. Here is a spreadsheet that I created where you can log products that were ordered.

excel spreadsheet

Pretty basic stuff, but I want to be able to populate the Product cells from a dropdown list of valid products.

That’s the other half of the equation – the list.

Creating the List of Values to Populate the Dropdown

Now, go to some blank cells or preferably a blank tab in your workbook.

Type in your list of values going down a column.

list of excel values

These are the only selections that I want to appear in the Product cells.

Now, let’s create the dropdown list.

Creating the Dropdown List

You need to go to the cell that you want to populate from the list (B2). Then, click the Data tab on the ribbon.

excel data validation button

Select the Data Validation dropdown list, then the Data Validation option on the list.

This will open the Data Validation menu.

excel data validation menu

You need to determine what values that you will allow in cell B2 using the Allow list.

Select “List” from the Allow list.

excel data validation menu populated

This will produce the Source field. Click in this field, then select the range of cells where you typed your list.

Press the Enter key on your keyboard to finalize the dropdown list creation.

Using the Dropdown List

If you click in the cell with the list (B2), you will see the dropdown arrow to the right of the cell.  When you click on it, the list of valid values that you created will appear.

excel dropdown list

Select your choice and it will populate the cell.

That’s it! Pretty simple, right?

Applying the Finishing Touches

You just added the dropdown list to one cell (B2). Copy that cell and paste it into the rest of the cells in the Product column and the list will work for every row.

The other thing that I would recommend is hiding the cells with your values. You can either Hide the cells or just place them on another worksheet.

See the Video Tutorial for Creating a Dropdown List in Excel

The following video shows the entire process.

Did you Like this Article?

Why not get free updates from VitaminCM.com?

RSS IconSubscribe via RSS Reader

VitaminCM.com Email Feed

Enter your Email Address to Subscribe via Email

VitaminCM on YouTube Subscribe via YouTube

Or just VitaminCM on Twitter Follow me on Twitter, where I am constantly sharing great articles written by people WAY smarter than me. Or, see some of the other places where you can connect with me out there in the wilds of the internets.

Older posts are this way If this message doesn't go away, click anywhere on the page to continue loading posts.
Could not load more posts
Maybe Soup is currently being updated? I'll try again automatically in a few seconds...
Just a second, loading more posts...
You've reached the end.

Don't be the product, buy the product!

Schweinderl