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.

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.

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.

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.

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.

 

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 the cell where the source data resides.

?

 

?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.

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.

Read More »

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.

 

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.

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.

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.

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

This will open the 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.

 

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.

 

 

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?

Subscribe via RSS Reader

Enter your Email Address to Subscribe via Email

Subscribe via YouTube

Or just 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.

Read More »

Bring Excel Data to Life with Conditional Formatting

Data that Tells a Story

OVERVIEW: Learn how to use Microsoft Excel 2007′s advanced conditional formatting features to take your boring data and make it tell a more interesting story.

Boring Data Be Gone!

Have you ever created a spreadsheet that tracks a lot of important data? This data may have a lot to say, if you help it. Excel 2007 has some advanced Conditional Formatting features that allow you to use the values in cells to create visually informative displays that add more useful context to your information.

Take a look at the spreadsheet below. It lists the amount of fruits sold on each day of the week. There are a lot of numbers there and it might be kind of hard to spot any useful trends.

Let’s take a look at how we can use conditional formatting to identify things like: the biggest selling items on any day, the top, middle, and bottom sellers, use color shadings to indicate where each product ranks.

Accessing the Conditional Formatting Menu

Click the Conditional Formatting icon on the Home tab of the Excel Ribbon.

You will see a menu with a list of Conditional Formatting options.

  

Let’s take a look at what these options can do.

Highlighting Cells with Specific Values

Click Conditional Formatting> Highlight Cell Rules> Greater Than.

A pop-up menu will open allowing you to enter the Greater Than amount and the formatting style.

In the example above, all cells whose value is greater than 500 were turned light red with dark red text. You can modify either of these settings. The other Highlight Cell Rules options work similarly.

Dynamically Highlighting Cells Using Top and Bottom Rules

You can highlight cells with values in the top or bottom “X” percent or “X” values in a range.

Click Conditional Formatting> Top/Bottom Rules> Top 10%.

A pop-up menu will open allowing you to enter the Percentage and the formatting style.

In the example below, the cells whose value is in the top 10% of the range were turned light red with dark red text. You can modify either of these settings. The other Top/Bottom Rules options work similarly.

Using Data Bars to Graphically Display Values

You can fill the cells in a range with data bars that are sized according their values.

Click Conditional Formatting> Data Bars and select a bar color.

Excel will find the highest and lowest values in the range and fill each cell accordingly with a colored bar.

Formatting the Cells in a Range Using Color Scales

You can use scales of color to highlight all of the cells in a range. You have a choice of shades of two or three colors.

Click Conditional Formatting> Color Scales and select your color set.

In the example below, the range of cell values were identified by the top, middle, and bottom thirds. The thirds were colored Red, Yellow, and Blue. Then, the values in each third were made darker or lighter depending on how close to the floor or ceiling of their ranges.

Identifying Cell Values Using Icons

The previous formatting option is often referred to as “stoplight” formatting (red, yellow, green). If you want to take it even more literally, you can place icons next to cell values.

Click Conditional Formatting> Icon Sets and select an icon type.

In the example below, the range of cell values were identified by the top, middle, and bottom thirds. The values in each third were identified by a red, yellow, or green icon.

There are several useful icons that you can use to identify your data set.

The other cool option that you can do is filter values based on their icon (meaning which section of the data range the values belong).

1. Go to theData tab
2. Click the Filter icon
3. Click the Filter Arrow at the top of the column that you want to filter
4. Select Filter by Color
5. Choose the icon that identifies the slice of the range that you want to filter on

You will see all of the values that have icon that you selected.

This is a pretty handy way to see the top sellers or worst performers in a range.

Video Tutorial Showing How to Use Conditional Formatting in Excel

Play the video below to see all of the options in action.

How to Use Conditional Formatting in Excel from Christopher Masiello on Vimeo.

Did you like this article?

Then why not 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.

Read More »