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.
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 .
Here are the FAQs that matched your search "" :
Sorry, there are no matches for your search "" —to search again, type in another word or phrase and click search.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
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.