What is Linking and How Does It Work?

Linking is a feature in Excel that allows you to connect one cell to another, either within the same worksheet, across different sheets in the same workbook, or even across different workbooks entirely. When you link cells, any change in the source cell is automatically reflected in the linked cell. This dynamic connection is established using Excel formulas, typically involving the “=” symbol.

Linking Within the Same Workbook vs. Separate Workbooks

• Same Workbook: Linking cells within the same workbook is straightforward. You use the formula =SheetName!CellReference. For example, =Sheet2!A1 would link to cell A1 in Sheet2 of the same workbook.
• Separate Workbooks: When linking cells across different workbooks, the formula becomes a bit more complex. It includes the full path of the source workbook. For example, ='[SourceWorkbook.xlsx]SheetName'!CellReference.

Step-by-Step Instructions

Linking Cells Within the Same Workbook

1. Select the Target Cell: Choose the cell where you want the linked data to appear.
2. Enter the Formula: Type = followed by the name of the source sheet, an exclamation mark, and the cell reference. For example, =Sheet2!A1.
3. Press Enter: Hit the Enter key to complete the link.

Linking Cells Across Different Workbooks

1. Open Both Workbooks: Make sure both the source and target workbooks are open.
2. Select the Target Cell: Choose the cell in the target workbook where you want the linked data.
3. Enter the Formula: Type = and then navigate to the source workbook and select the cell you want to link.
4. Press Enter: Hit Enter to complete the link.

Nutley’s own Third River Coffee Co.
Elevate your coffee game with our premium blends. Click now and get 15% off your first order!

Advanced Options and Other Use Cases

• Two-Way Linking: This allows changes in either the source or linked cell to update the other. It’s a bit more complex and usually involves using Excel’s VBA (Visual Basic for Applications).
• Linking to Other Spreadsheet Apps: You can also link Excel cells to cells in other spreadsheet apps like Google Sheets, although the process is a bit more involved.
• See how to Use Pivot Tables to Slice and Dice Excel Data in our previous article.

Additional Resources

• Linking Information Between Excel Worksheets and Workbooks
• Microsoft’s Official Guide on Creating Workbook Links
• SuperUser’s Guide on 2-Way Linkages

Questions or Suggestions?

Got questions or suggestions? Feel free to drop them in the comments below. We’d love to hear from you!

The post How to Link Cells Across Different Excel Spreadsheets: A Comprehensive Guide first appeared on Vitamincm.com.

Excel pivot tables allow you to automatically sort, count, total or average selected fields of your raw dataset to gain powerful insights. By dragging and dropping fields, you can interactively change the perspective of your data within seconds. Fields can be positioned into row labels, column labels, or values areas. Multiple fields can also be nested within each other when placed into the row or column labels areas. Filters allow you to instantly select or deselect fields to include or exclude from your analysis with a single click. You can also change layouts by dragging fields between the row and column labels areas to alter how information is grouped or summarized. In this guide, we’ll go through how to easily create a pivot table from an existing data source and customize fields, filters, rows and columns to gain different views into your information.

Creating a Pivot Table

Setting Up Sample Data

For our sample, we’ll use an ecommerce transactions dataset instead of the outdated fruit sales data. This contains fields like Product, Date, Customer, Price, etc.

Inserting a Pivot Table

1. Select any cell within the sample data.
2. Go to the Insert tab and click Pivot Table.
3. Choose a New Worksheet option for the report.

Positioning Fields

Fields from the sample data will appear in the pivot table field list panel. Positions include:

• Row Labels
• Column Labels
• Values

Drag fields to customize the initial view. See Figure 1 for an example layout.

Customizing the Pivot Table

Filtering Data

1. Click the dropdown arrow next to a field label
2. A filter menu appears listing all field values
3. Check/uncheck values to include/exclude

Rearranging Rows and Columns

Simply drag fields between the Row Labels and Column Labels areas to alter layouts.

Using Calculated Fields and Filters

Newer Excel pivots support calculated fields to perform additional analytics on the data. Timeline filtering allows interactive filtering on date fields.

Advanced Techniques

For more advanced learning on pivot charts, crosstabs, and other powerful pivot table features, check out the resources listed here.

Here are some additional ways to customize pivot tables in Excel:

• Format cells/numbers – Change number formats like currency, percentage, decimals, etc. Right click on values cell and select format cells.
• group dates/values – Group dates by month, quarter, year using the group selection under the pivot table options. Group values using intervals.
• add totals/subtotals – Use the pivot table options to add and custom count, sum, average subtotals or grand totals to rows or columns.
• Slicers – Add slicers to allow interactive filtering across multiple pivot tables at once. Slicers work like visual filters.
• Timelines – Filter dates using the built-in timeline instead of a manual list of dates in a filter.
• Calculated fields/items – Use formulas in new or existing fields to perform calculations on the data.
• Report filters – Filter field values before they appear in the pivot table for conditional views.
• Pivot charts – Add pivot charts to visualize trends in the pivot table data interactively.
• Page/timeline filters – Limit data shown to a certain page or time period using these filtering options.
• Formatting/styles – Use pivot table styles, conditional formatting, number formats to visualize and emphasize the data.
• Field settings – Change calculation type, format fields, show/hide buttons under the field options contextual tab.

Adding Slicers to a Pivot Table in Excel

Here are the steps to add slicers to a pivot table in Excel:

1. Have your base pivot table created with appropriate fields represented as rows, columns, or filters.
2. Go to the PivotTable Tools > Analyze tab and click on Slicers.
3. Select the fields you want to slice or filter the pivot table with. You can select multiple fields.
4. Click OK. Slicers will appear on a new Slicer pane on the right side of the sheet.
5. Interact with the slicers by selecting or deselecting values.
6. The pivot table will dynamically update to only show data matching your slicer selections.
7. To move a slicer, click and drag its header bar to reposition on the sheet.
8. Formatting options allow changing colors, filtering behavior, and more.
9. Slicers work across multiple pivot tables simultaneously, providing a powerful dashboard filter.
10. To remove slicers, go back to the Analyze tab and click Slicer and select none.

The key benefit of slicers is the ability to interactively filter pivot tables with visual selections instead of dropdown lists. This improves the exploration of data relationships.

The post How to Slice and Dice Excel Data in Seconds: A Beginner’s Guide to Pivot Tables first appeared on Vitamincm.com.

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.

Excel VLOOKUP Video Tutorial

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

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.

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.

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.

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

Click the OK button.

The Function Arguments wizard will open.

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

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.

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.

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.

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.

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.

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”)

Wanna See More Great Excel Tips and Tricks?

These articles have even more Excel Tutorials and quick tips:

• Linking Cells in Multiple Spreadsheets
• Create a Dropdown List in Excel
• Amazing Free Excel Plugin
• Applying Conditional Formatting to Excel Data

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.

Re-copy the formula down the list.

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

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:

The post Master the Excel VLookup with this Simple Tutorial first appeared on Vitamincm.com.

Note: There is a new Advanced Excel Pivot Table Tutorial here.

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.

Creating the Pivot Table from Existing Data

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

The Create Pivot Table window will open.

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.

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.

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.

Select Value Field Settings from the menu.

The Value Field Settings menu will open.

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.

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

You will see the pivot table

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

Did you Like this Article?

The post Excel Pivot Table Tutorial first appeared on Vitamincm.com.

Overview: Learn how to use PC Decrapifier to remove the trial software that came installed on your new computer.

Hey Dell, HP, Gateway, Sony, Toshiba – Thanks for Nothin’

Getting a new computer is so exciting until you turn it on for the first time and find out that it’s loaded up with a bunch of trial software and useless junk that was placed there by the PC manufacturer. Norton Antivirus “Trial”, Microsoft Office “Trial”. How about the Dell Media Craptactular Bundle or the HP Horrible User Experience Pack or the Gateway Spam-O-Rama Experience. Nothing will kill your buzz faster than having to uninstall 20 or 30 pieces of junk that these jackwagons got paid to put on your machine. Here’s the best part, if you want a Windows disc to reinstall a clean version of the OS, they’ll charge you extra for that. (Yeah, the cost of the license is already included in your purchase price.)

You can’t make them stop putting this garbage on your machine, but I can show  you how to remove all of this crap in a just few mouse clicks and return WIndows to it’s intended condition.

What You Need to Get Started

PC Decrapifier is a free application that does exactly what the name implies. It will quickly and easily remove all of the preinstalled Crapware from your computer.

Send the Garbage to the Curb

Here are the steps that you need to perform to make your computer clean as a whistle:

1. Download PC Decrapifier ()

2. Install the Software

3. Run PC Decrapifier

4. Create a Restore Point

• This will make a backup image of your working computer in case anything goes horribly wrong during the process. It always pays to make a backup. This takes about 10 seconds.

5. Select the Crapware

6. Click the Next button

• This will take a few minutes, depending on how much junk your manufacturer infected you with.

7. Finish (Is being done actually a step?)

That’s the entire process! I just bought a new HP Laptop for my wife and had to remove a whopping 29 pieces of junk before she could use it. The entire process took nine minutes and about 10 clicks.

See How to Remove Trial Software on this Video Tutorial

The video below shows all of the steps in this tutorial

Extra Credit

If you want, you “could” stop there and begin using your shiny new computer. But that’s not what we’re about here at VitaminCM.com.

When you install or uninstall software a bunch of data gets moved around in your computer’s Registry. Bearing in mind that the crapware you just uninstalled is such particularly low-grade dung; your registry could use a quick clean-up. Do yourself a favor and download an application called Ccleaner.

Ccleaner is a free application that will clean all types of junk from your computer and straighten out the glitches in your Registry in a matter of seconds. Once you install Ccleaner, run the process on the X, Y, and Z tabs. This will literally take about 60 seconds. Then, after a quick Windows Restart, you will be ready to rock and roll. You should open Ccleaner and run through those three tabs every month or so to keep things clean and smooth.

What’s Your Cleanup Routine?

I would love to hear how you get a new computer cleaned up in the comments below.

Get More Stuff Like this

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

The post The Fastest Way to Remove the Trial Crapware from a New PC first appeared on Vitamincm.com.

OVERVIEW: You will learn how to Create an Interactive Video using YouTube’s Annotation links.

What are Interactive YouTube Videos?

YouTube has a feature called Annotations which allows you to create links from one video to multiple others. You can use this feature to create a sort of interactive navigation path through a series of videos. The whole process allows for a lot of interesting possibilities; such as interactive games, story telling, training sequences, or product overviews.

Creating an interactive video series in YouTube is actually pretty simple. You need to break the process into the key parts:

• Sketch out a Story Board
• Create your videos
• Upload all videos to YouTube
• Create Annotation Links between videos
• Take viewers to your Starting Video

How Do Interactive YouTube Videos Work

Watch the video below to see a quick overview of the entire process.

Planning Everything Out

Before you create or upload any videos, you need to lay your navigation path out. I just start with a piece of paper and list out my individual videos. Then I decide where the Navigation Decision Points are located. For instance, when I get to the end of video 1 do I want to go straight to video 2 or give the viewer multiple choices? Do I want to let the viewer go back and review previous videos? What do I want viewers to do at the end (if there is one)?

Here is a typical Navigation Path drawing that helps me understand how my videos are going to work together.

Notice how I show where I want the viewer go at the end of each video.

Creating the Videos

Now that you have your series all planned out; you’re ready to create your individual videos. Just remember to leave a “Decision Period” or 10 second block of time at the end of each clip. This is where you will place your Annotation Links that direct viewers along your path.

Upload All of your Videos

When you are all done creating your individual clips, just upload them to YouTube. After all of your videos are uploaded and processed, you can see them on your My Videos page. Go to the top, right corner of the screen and click on your User Name. Then, select My Videos from the dropdown list. You will see all of your videos. (Newest to oldest)

I like to open all of the videos in the series in their own browser tabs and arrange them in chronological order. You are going to need the URLs from each of your videos to create the Annotation Links. I find it’s helpful to paste them all into a text file for quick copy and pasting later.

Creating Annotation Links in YouTube

Now you’re finally ready to link up all of your videos in YouTube. Go to your first video and click the Annotations tab.

When you see the Annotations Settings screen, click the Add Highlight icon. This will place the Annotation Link tool on your video. Drag the larger, top box over the “click” area of your video. Then, populate and position the Enter Text area.

There are yellow calipers along the timeline below your video. Adjust these to control the stop and start time of your Annotation Link. You will also need to put the URL of the video that you’re linking to into your Annotation. Click the Link icon on the link toolbar to enter your link.

Paste the URL of your destination video into the URL field and click the Save button. Your annotation has been added. You can add additional Links by repeating the same steps. When you have all of you links added, click the Publish button to make your Annotations live. Now, when your video gets to the point where your link is added, users will be able to click it and jump to the next video.

Just repeat this process to make your videos match up to your Navigation Path and your done. Now you can direct viewers to the first video in the series to let them start their own interactive journey.

See the Process in Action

Launch the following videos to see how to create your own interactive YouTube video.

See the Finished Product

If you would like to see the series of videos that I created while documenting this process, play the beginning video in the series below

Did you Like this Article?

Sign up for free updates from VitaminCM.com?

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

The post How to Create an Interactive YouTube Video first appeared on Vitamincm.com.