Start learning with our library of video tutorials taught by experts. Get started

Excel 2010 Power Shortcuts

Linking and unlinking pictures


From:

Excel 2010 Power Shortcuts

with Dennis Taylor

Video: Linking and unlinking pictures

It maybe appropriate within an Excel worksheet either for presentation purposes of for printing purposes to have an image in one worksheet of data in another worksheet. It might sound a little strange at first. But take a look at this worksheet here. This is 1stQuarter data, and I'm going to jump over to the 2ndQuarter data at the bottom of the screen here. You might have noticed a slight little blip there. I'm going to go to back to 1stQuarter here, 2ndQuarterm, and off to 3rdQuarter to the right, I'm clicking on the sheet tab. Slight little nudge each time. 4thQuarter.
Expand all | Collapse all
  1. 1m 37s
    1. Welcome
      1m 17s
    2. Using the exercise files
      20s
  2. 22m 45s
    1. Entering data or formulas in non-adjacent cells simultaneously
      2m 28s
    2. Converting formulas to values with a simple drag
      2m 34s
    3. Copying data or formulas down a column instantly
      2m 56s
    4. Adjusting all or selected column widths or row heights in a flash
      2m 21s
    5. Instantly displaying all worksheet formulas
      3m 16s
    6. Two quick shortcuts for creating charts
      1m 18s
    7. Print Preview
      1m 7s
    8. Instant date or time entry
      1m 16s
    9. Undo/Redo/Repeat
      3m 19s
    10. Zooming in and out quickly
      2m 10s
  3. 7m 37s
    1. Expanding and collapsing the Ribbon and Full Screen view
      1m 23s
    2. Keyboard command access
      2m 22s
    3. Quick Access toolbar display tips
      3m 52s
  4. 14m 30s
    1. Split screens and frozen titles in a flash
      5m 56s
    2. Repeating title creation and suppression
      6m 17s
    3. Customizing your display of Status Bar totals
      2m 17s
  5. 11m 31s
    1. Navigation shortcuts
      2m 30s
    2. Tips for navigating between workbooks
      3m 48s
    3. Navigating within worksheets
      5m 13s
  6. 11m 12s
    1. Selecting an entire row, column, or worksheet
      3m 20s
    2. Selecting noncontiguous ranges and visible cells only
      4m 39s
    3. Selecting the current region and moving around region corners
      3m 13s
  7. 22m 16s
    1. Accelerating data entry
      6m 27s
    2. Auto-Fill techniques for entering dates
      4m 59s
    3. Auto-Fill techniques for entering times
      2m 37s
    4. Custom lists for rapid entry
      5m 54s
    5. Cell editing tips
      2m 19s
  8. 12m 38s
    1. Copy/Move acceleration tips
      3m 27s
    2. Worksheet Copy/Move shortcuts
      2m 29s
    3. Dragging and inserting variations
      3m 47s
    4. Instantly displaying Paste Special options
      2m 55s
  9. 29m 31s
    1. Rapid formula creation
      3m 48s
    2. Selecting all cells that depend on the active cell
      5m 24s
    3. Selecting all cells that can affect the active cell
      2m 38s
    4. AutoSum shortcuts
      2m 57s
    5. Rounding shortcuts
      5m 14s
    6. Generating random numbers
      3m 16s
    7. Counting unique entries
      3m 11s
    8. Performing calculations without formulas
      3m 3s
  10. 17m 4s
    1. Controlling rows and columns
      5m 50s
    2. Realigning imported text
      2m 27s
    3. Handling blank cells
      4m 20s
    4. Collapsing and expanding detail
      4m 27s
  11. 28m 8s
    1. Formatting numbers
      6m 49s
    2. Aligning data
      3m 49s
    3. Adding background color for readability
      3m 43s
    4. Formatting data conditionally
      1m 54s
    5. Creating custom formats
      6m 23s
    6. Formatting periods over 24 hours
      3m 2s
    7. Applying strikethroughs and borders
      2m 28s
  12. 25m 46s
    1. Sorting shortcuts
      2m 40s
    2. Cleaning up spaces
      4m 47s
    3. Identifying duplicates
      6m 10s
    4. Splitting columns
      3m 57s
    5. Ensuring unique entries
      2m 46s
    6. Forcing dates to be weekdays only
      3m 56s
    7. Displaying unique items from large lists
      1m 30s
  13. 18m 38s
    1. Placing and adjusting charts
      2m 37s
    2. Creating chart titles from cell content
      2m 22s
    3. Creating and manipulating shapes
      5m 31s
    4. Linking and unlinking pictures
      8m 8s
  14. 9s
    1. Goodbye
      9s

Watch this entire course now—plus get access to every course in the library. Each course includes high-quality videos taught by expert instructors.

Become a member
Please wait...
Excel 2010 Power Shortcuts
3h 43m Intermediate Aug 12, 2010

Viewers: in countries Watching now:

In Excel 2010 Power Shortcuts, Excel expert Dennis Taylor shares tips and shortcuts to vastly increase efficiency and get the full power out of Excel 2010. There are tips for working with the Ribbon and Quick Access toolbar, navigating workbooks and selecting cells, rapid data entry and editing, working with formulas, formatting data, working with charts, sorting data, and much more. Exercise files accompany the course.

Topics include:
  • Creating charts with keyboard shortcuts
  • Converting formulas to values by dragging
  • Repeating Undo and Redo actions
  • Displaying formulas instantly
  • Navigating quickly through worksheets and across workbooks
  • Formatting numeric, and date/time data in a flash with keystroke shortcuts
  • Inserting dates or times instantly
  • Grouping rows or columns to create collapsible regions
  • Building data-entry shortcuts with Auto-fill
  • Displaying unique items from large lists
Subjects:
Business Productivity
Software:
Excel
Author:
Dennis Taylor

Linking and unlinking pictures

It maybe appropriate within an Excel worksheet either for presentation purposes of for printing purposes to have an image in one worksheet of data in another worksheet. It might sound a little strange at first. But take a look at this worksheet here. This is 1stQuarter data, and I'm going to jump over to the 2ndQuarter data at the bottom of the screen here. You might have noticed a slight little blip there. I'm going to go to back to 1stQuarter here, 2ndQuarterm, and off to 3rdQuarter to the right, I'm clicking on the sheet tab. Slight little nudge each time. 4thQuarter.

The data to the right looks a little strange, and the columns that we see in here don't seem to match up with the column letters. What's happening over here? As I slide the mouse over something else, this is actually a picture. Unlike a snapshot, this is in effect a live picture of data on the Summary sheet. So let's revisit this a little bit. Each of these sheets, 1stQuarter, 2ndQuarter, 3rdQuarter, 4thQuarter, there's data in columns A through E. The Summary sheet has totals that gather data from those respective sheets.

So for example, in cell B3 is a formula that gathers data from the 1stQuarter sheet, cell E3. This is from the 2ndQuarter sheet and so on. So the Summary sheet is pulling together all these totals. The numbers that we're seeing here in effect, all of these numbers here, is effectively been captured and is being displayed on the other sheets as if we're using a monitor, like a TV monitor and like a camera.

So, we go to the 4thQuarter sheet, keep our eye on one or two totals here and say the grand total in $169,000, 4thQuarter total's $46,790, we will jump over to the 4thQuarter sheet. What are we seeing out here? We're just looking at this image on the right on the Summary sheet. It's actually in those cells. Because these formulas are all tied together, each of these sheets here, no matter which one we go to, if we make changes here, we'll see changes in this image on the right, because it's like a live data or live camera viewing the Summary sheet.

So for example here, if I change the data in B3, I'm going to bump it up to $13,000. It's going to have impact on the 1stQuarter Total right here on this worksheet. It's going to bump it over $40,000. Meanwhile on the Summary sheet, which shows this total also, as we look at the image over here. Now remember, it's part of that picture you might say. That picture link, that will change, and it's going to bump up the grand total to just over $170,000. So, as I click on cell B3 here, this is on the 1stQuarter sheet.

I'm typing in $13,000, and as I press Enter, keep your eye if you can on three places, in column E and then on the image off to the right, the 1stQuarter total, and the grand total. Enter. And we see those changes. So, this can be ideal when you've got worksheets that are interlinked and unlike just a solid picture, this is a picture link. So let's get rid of this. I am going to show how to create it. I am just going to click it and press Delete. On the Summary sheet, well, I'll highlight it. Highlight the data.

This is what we'd like to see on that 1stQuarter sheet. So we've highlighted it, we can right- click and copy anywhere. There we go. Go to 1stQuarter, probably click up here, click there. It doesn't make any difference. We can move it around a little bit too. On the Home tab, below the Paste icon, click the arrow, and the two options we would consider would be a Linked Picture possibly, or simply a Picture. A Picture is like saying we're going to take a snapshot of it, stick it out here.

It will never change. A Linked Picture is like putting that TV monitor, that TV camera, right here. Let's choose Linked Picture. Click. There it is. Now we can move it around. It looks a little strange and we see the grid lines in the background, but it is linked. I'll make another change. I'll go maybe do sort of undo what I did before. I put this back at $12,500. It was a different number before that. But as I go this, the changes will take place in column E and on that image to the right.

The grand total for that particular row will go below $170,000. There we are! If you like to spiff this up a little bit, again you can move it around, place it wherever you want, you can make it smaller. I don't think that would be a good idea, because then our rows aren't lining up with the data so well, but you certainly have the right to do that. If you right-click on this, you might well then go to Format Picture and possibly out here fill in say with a solid color in the background, and you can explore this a little bit. For example, maybe I'll use a light blue or something.

Then you pick this one, just see what's happening to the image. Then as we drag it around, we're not seeing through it to see the lines. So I think you can see there is some real appeal for this sort of thing. Now remember, we could have, and we'll say we will now, show how this might be as a picture. We could have done during the same sequence of actions too. Highlight this, right-click, copy the data. This time we'll go back to 1stQuarter and just for comparison put it off to the right here. And on the Home tab, click that arrow under Paste.

This time, choose Paste to Picture. So, that's a picture off to the far right. Right here we have the actual picture link. So if I change this number again back to $13,000, the image that's more or less on the middle of the screen will change and adjust. The picture off the right will not. It's that sort of snapshot in time. There we go. It's staying the same, and it doesn't reflect the latest totals. You can think of a good use for that too, because sometimes you want to keep an image of the history or how the totals appeared at a certain time while you update and put in the new data.

You want an image of the old data possibly. So we can use it in that sense. You can also do this with charts, although you can't really do a link. This chart off to the right really isn't a chart in the live sense. It's a picture. It's an image. When you click on it, you'll notice that in the Ribbon, Pictures Tools and Format. This might have an idea use. We will use it with this chart here. Suppose these numbers have been finalized, maybe it's for a given year, and you might want to put in new numbers for the next year while keeping an image of this around.

So, we can right-click and copy this chart and then we'll just in this example go below this and on the Home tab, click the arrow for Paste, and we've got fewer choices here, because it's a chart. Just paste this as a Picture. We don't have a Picture Link option here. Paste it as a Picture. Now, that's frozen in time. The chart above it is linked to the data and will react. If I change Domestic here, and I mentioned I will make a typo. I mean to type 85 and I type 850 or something.

The chart on top will reflect, and you see what's happened. The one below was a picture like a snapshot in time. It doesn't change. Let me do an undo here. When you do have images like this too, a quick fix for some people, and this is kind of fun too. Click Format and you've got suddenly a lot of choices here and you just have to try some of these. You didn't even know perhaps that you could tilt the chart and have a reflection underneath it, do all kinds of interesting things here, just by sliding or that one.

Do you see the shadows in the background here and there and that sort of thing? Fun stuff. There we go! You want bigger reflection than that? Once you have clicked on this, go into Picture Effects and choose the reflection type you might want. You can make the case for how critical and important that really is. So, easy capability here for converting charts into pictures and prior to that, the more important perhaps, creating Pictures and Picture Links within worksheets.

Find answers to the most frequently asked questions about Excel 2010 Power Shortcuts.


Expand all | Collapse all
Please wait...
Q: Where can I learn more about Excel formulas?
A: Discover more on this topic by visiting Excel formulas on lynda.com.
Share a link to this course
Please wait... Please wait...
Upgrade to get access to exercise files.

Exercise files video

How to use exercise files.

Learn by watching, listening, and doing, Exercise files are the same files the author uses in the course, so you can download them and follow along Premium memberships include access to all exercise files in the library.
Upgrade now


Exercise files

Exercise files video

How to use exercise files.

For additional information on downloading and using exercise files, watch our instructional video or read the instructions in the FAQ.

This course includes free exercise files, so you can practice while you watch the course. To access all the exercise files in our library, become a Premium Member.

join now

Are you sure you want to mark all the videos in this course as unwatched?

This will not affect your course history, your reports, or your certificates of completion for this course.


Mark all as unwatched Cancel

Congratulations

You have completed Excel 2010 Power Shortcuts.

Return to your organization's learning portal to continue training, or close this page.


OK
Become a member to add this course to a playlist

Join today and get unlimited access to the entire library of video courses—and create as many playlists as you like.

Get started

Already a member?

Become a member to like this course.

Join today and get unlimited access to the entire library of video courses.

Get started

Already a member?

Exercise files

Learn by watching, listening, and doing! Exercise files are the same files the author uses in the course, so you can download them and follow along. Exercise files are available with all Premium memberships. Learn more

Get started

Already a Premium member?

Exercise files video

How to use exercise files.

Ask a question

Thanks for contacting us.
You’ll hear from our Customer Service team within 24 hours.

Please enter the text shown below:

The classic layout automatically defaults to the latest Flash Player.

To choose a different player, hold the cursor over your name at the top right of any lynda.com page and choose Site preferencesfrom the dropdown menu.

Continue to classic layout Stay on new layout
Welcome to the redesigned course page.

We’ve moved some things around, and now you can



Exercise files

Access exercise files from a button right under the course name.

Mark videos as unwatched

Remove icons showing you already watched videos if you want to start over.

Control your viewing experience

Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.

Interactive transcripts

Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.

Thanks for signing up.

We’ll send you a confirmation email shortly.


Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

   
submit Lightbox submit clicked