The link at the end is MrExcel XL Book - 40 Greatest Excel Tricks
Creating a holiday gift tracker in Excel. This covers: (a) pulling a data validation list from a table that automatically expands, (b) Inserting hyperlinks. (c) Inserting pop-up pictures. (d) CountIF and SUMIF formulas. (e) Creating graphs. (f) Hiding your top-secret spreadsheets with drawing objects and using the Selection pane to toggle the visibility of the items, (g) The perfect gift for your accountant.
Creating a holiday gift tracker in Excel. This covers: (a) pulling a data validation list from a table that automatically expands, (b) Inserting hyperlinks. (c) Inserting pop-up pictures. (d) CountIF and SUMIF formulas. (e) Creating graphs. (f) Hiding your top-secret spreadsheets with drawing objects and using the Selection pane to toggle the visibility of the items, (g) The perfect gift for your accountant.
Transcript of the video:
The MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1943.
Holiday Gift Tracker.
Hey, welcome back to MrExcel netcast, I'm Bill Jelen.
Today's question is from Amy.
We were talking to Amy and she said, well, are you getting ready for the holidays, and kind of came up with this idea of a Holiday Gift Tracker.
Right, so you have all these people you have to buy for, and you have the list of gifts, and you want to make sure, especially if you have a lot of kids, that they have equal amounts of presents.
And then you say, well, no, no, it doesn't really work that way, because, you know, I have David, who likes to get a bunch of small things to open, but my daughter Alyssa, she likes to get fewer presents, but more expensive presents.
So today we're going to go through how to set up a Holiday Gift Tracker in Excel and there are going to be a lot of cool Excel tips and tricks here.
So even if you're not in the holiday spirit or if you happen to be watching this after the holiday, still lots of good stuff here.
So, the first thing, we're going to have a couple of worksheets.
The first worksheet is the list of people that, you know, so simple enough to set this up.
And I'm going to create this, I'm going to make this into a table.
So back here on the HOME tab we're going to go to HOME and Format as Table.
And you'll see that I've actually created a custom palette here of different holiday colors.
And so, we'll choose one of these as the table.
And my table has headers, make sure to check that.
See, they didn't somehow they were full there, click OK.
And they give this the interesting name of Table1, which you're allowed to change here on the Table Tools Design tab.
So I'm going to just call this the PeopleTable, how about that.
PeopleTable, all right.
So now this creates a table and as we realize “oh, shoot, we have to get something for, you know, our aunts” and we add, the table automatically extends.
But if I want to use this as the source for data validation, I can't refer to the table name.
But what I can do, is select the list of people that we already have in, and name that.
Create a name range.
So not a table name but here in the name box I want to type People, all right.
So the table is called PeopleTable, but this column is called People, all right.
And it actually goes away, how annoying is that, but trust me, it worked.
Alright, so I’m going to come here to my Gifts worksheet Tab and this is where I’m going to list all the various gifts.
And here, in the Name column, I'll just page down a whole bunch and we'll set up DATA, Data Validation, and where I set this is going to be allowed to add from List.
And the source is going to be =PEOPLE, all right.
Now, in the old days, back in Excel 2003, you weren't allowed to have your source here on a different worksheet.
But it always worked if you had a named range.
It worked out fine, all right.
So we'll click OK and then what we get is a little drop-down when you go into that cell.
You can just choose from the list.
Now the big thing that I'm trying to do here, the big thing that I'm trying to do, is make sure that if I would add a new person.
So let's come back here to the list and we'll add Fred, who is our crazy uncle.
All Right, see, the table automatically extended, but I want to go back and make sure that here, in the drop-down, that Fred appears, all right.
So that's our first thing how to make and expand the whole table, all right.
Now, throughout the holiday season you would fill this in, so I'm going to pause the netcast.
Or I’ll type some data.
Okay, we're back.
So here's a sample gift list.
Same thing here, we want to make this into a table, so we need to use Ctrl+T or come back here to HOME, Cell Styles.
Oh, I'm sorry, Format as Table.
And choose one of the table formats I'll go with one that has banded rows, like that.
My table has headers, right.
That way as we add more data later, it will continue to add.
Now, a couple of cool things that I've set up here and it depends on how much work you want to do on this, is, you see, that I have a note there, on the Brighton sunglasses.
And if you hover, it actually shows you a picture of that.
I have a picture in Excel and there is a hyperlink there, if you click the hyperlink, it will take you out to the webpage.
All right, so how do we set up those?
Let's come down here to our Fred, who's getting the book Power Excel for MrExcel and to set up the hyperlink – it's easy – you just come to the page, click up here in the address bar, you see if the http is missing, that's a thing in Chrome.
But it's actually there, so we do Ctrl+C to copy and then come back to Excel, all right.
You're going to use Ctrl+K. Ctrl+K sets up a new hyperlink and it's already there in the address, just click Ctrl+V and click OK.
Alright, and now a hyperlink is set up.
The picture though, the picture is harder.
So we're going to come back to the webpage and want to right-click and say that we want to save that image as, and save it someplace, we can get it remembered, all right.
So it's downloaded.
Then back in Excel.
Getting that pop-up picture though is tough.
We're going to come back to the REVIEW tab, we run out a new comment and I want it to be completely blank comment, alright.
So backspace through the name and then carefully, you don’t want that comment to disappear, resize the comment so it's about the right size.
And we want to format that comment, so I go to Ctrl+1.
Now, be careful here, if you get only the Font tab, then you didn't resize the comment.
You're in edit but go back to the comment and resize that we get out of the edit mode.
Coming to Colors and Lines, where you can change the color of the comment But even better, we're going to go down to fill effects and say we want a picture.
We're to select the picture, and you know, in 2013 here they try and go offline pictures already on my computer.
PowerExcelBook, click OK, click OK, all right.
And then when we click away, see, now we have a pop-up picture that appears, all right.
So you can continue, as you’re shopping, here adding new items to the list.
But let's come back to our List tab and fill in these columns with a number of items and the value of the item.
So we want to do =COUNTIF, I wanted to count if, and the range and criteria.
Now I'm going to put a comma here in the criteria is this column A2. Close the parentheses, we’ll come back then to the comma, all right.
Now that's on another worksheet, so I'm going to click on the other worksheet and scroll up here and just go through all of the names.
Press Enter and double-click to shoot that down, all right.
So there we go.
That's the number of items and then value of items.
We’re to use =SUMIF, all right, so in this case we have two things on the other end.
Just remember that we're going to use cell A2 there, so we come back to the Range, choose all of the names, comma, let's see if that's equal to A2.
And if it is, add up the Cost from over here.
All right, and double click to copy that down, all right.
So now, for every person we can see how, you know, how much, how many items they have and how much we've spent.
It’ll be interesting to see this is a chart, so to create a chart from non-continuous data, first I choose all those Person names and then I hold down the Ctrl key, and do number of items.
Back here under INSERTS I choose a 2-D Column Charts.
And so there's our # of items chart and then the same thing will do for the dollars.
Hold down the Ctrl key and, maybe make this one into a Pie chart.
And there we go.
You know, okay, so now we have a couple of problems.
One problem is that you don't want someone to accidentally open this file and see everything that's going on.
So I would just come here to INSERT, use the Shape tools, create a nice shape and just cover up the entire screen, like that.
And same thing here on the gifts, will insert a shape and cover up the entire screen.
That way if they open it, you know, they don't see what's going on.
They just think that it's, you know, for some reason there's nothing here.
Ah, there's nothing here.
You know, but then you can always, you know, if you need to see things, you can come here to HOME, Find & Select, Selection Pane and say Hide All.
On the Selection Pane you just say either Hide All or Show All and you'll be able to see those items.
Of course hide the Selection Sane before they come in.
Now, hey, I see there's a problem here.
Your Tom, your CPA poor guy has nothing at all.
What do you get your CPA?
I have the perfect solution for you.
mrx.cl/xlbook40 and I am working on my 40th book.
MrExcel XL book.
XL is 40 in Roman numerals and there’s going to be the 40 greatest Excel tips of all time.
And a very unique present, something you'll never be able to get anywhere else.
Is for twenty-four dollars, you can pre-order the book, it's an autographed book and your CPA’s name will be listed in the book.
Now, of course, the book doesn't come out till April, but you can tell your CPA “Hey, I sponsored you.
You're going to be listed in MrExcel’s book, how unique is that!”.
I guarantee no one else will get that.
Well, there you go, a holiday tracker from my house to yours.
I hope everyone has a great holiday.
We’ll get back after the holiday with another netcast from MrExcel.
Learn Excel from MrExcel podcast, episode 1943.
Holiday Gift Tracker.
Hey, welcome back to MrExcel netcast, I'm Bill Jelen.
Today's question is from Amy.
We were talking to Amy and she said, well, are you getting ready for the holidays, and kind of came up with this idea of a Holiday Gift Tracker.
Right, so you have all these people you have to buy for, and you have the list of gifts, and you want to make sure, especially if you have a lot of kids, that they have equal amounts of presents.
And then you say, well, no, no, it doesn't really work that way, because, you know, I have David, who likes to get a bunch of small things to open, but my daughter Alyssa, she likes to get fewer presents, but more expensive presents.
So today we're going to go through how to set up a Holiday Gift Tracker in Excel and there are going to be a lot of cool Excel tips and tricks here.
So even if you're not in the holiday spirit or if you happen to be watching this after the holiday, still lots of good stuff here.
So, the first thing, we're going to have a couple of worksheets.
The first worksheet is the list of people that, you know, so simple enough to set this up.
And I'm going to create this, I'm going to make this into a table.
So back here on the HOME tab we're going to go to HOME and Format as Table.
And you'll see that I've actually created a custom palette here of different holiday colors.
And so, we'll choose one of these as the table.
And my table has headers, make sure to check that.
See, they didn't somehow they were full there, click OK.
And they give this the interesting name of Table1, which you're allowed to change here on the Table Tools Design tab.
So I'm going to just call this the PeopleTable, how about that.
PeopleTable, all right.
So now this creates a table and as we realize “oh, shoot, we have to get something for, you know, our aunts” and we add, the table automatically extends.
But if I want to use this as the source for data validation, I can't refer to the table name.
But what I can do, is select the list of people that we already have in, and name that.
Create a name range.
So not a table name but here in the name box I want to type People, all right.
So the table is called PeopleTable, but this column is called People, all right.
And it actually goes away, how annoying is that, but trust me, it worked.
Alright, so I’m going to come here to my Gifts worksheet Tab and this is where I’m going to list all the various gifts.
And here, in the Name column, I'll just page down a whole bunch and we'll set up DATA, Data Validation, and where I set this is going to be allowed to add from List.
And the source is going to be =PEOPLE, all right.
Now, in the old days, back in Excel 2003, you weren't allowed to have your source here on a different worksheet.
But it always worked if you had a named range.
It worked out fine, all right.
So we'll click OK and then what we get is a little drop-down when you go into that cell.
You can just choose from the list.
Now the big thing that I'm trying to do here, the big thing that I'm trying to do, is make sure that if I would add a new person.
So let's come back here to the list and we'll add Fred, who is our crazy uncle.
All Right, see, the table automatically extended, but I want to go back and make sure that here, in the drop-down, that Fred appears, all right.
So that's our first thing how to make and expand the whole table, all right.
Now, throughout the holiday season you would fill this in, so I'm going to pause the netcast.
Or I’ll type some data.
Okay, we're back.
So here's a sample gift list.
Same thing here, we want to make this into a table, so we need to use Ctrl+T or come back here to HOME, Cell Styles.
Oh, I'm sorry, Format as Table.
And choose one of the table formats I'll go with one that has banded rows, like that.
My table has headers, right.
That way as we add more data later, it will continue to add.
Now, a couple of cool things that I've set up here and it depends on how much work you want to do on this, is, you see, that I have a note there, on the Brighton sunglasses.
And if you hover, it actually shows you a picture of that.
I have a picture in Excel and there is a hyperlink there, if you click the hyperlink, it will take you out to the webpage.
All right, so how do we set up those?
Let's come down here to our Fred, who's getting the book Power Excel for MrExcel and to set up the hyperlink – it's easy – you just come to the page, click up here in the address bar, you see if the http is missing, that's a thing in Chrome.
But it's actually there, so we do Ctrl+C to copy and then come back to Excel, all right.
You're going to use Ctrl+K. Ctrl+K sets up a new hyperlink and it's already there in the address, just click Ctrl+V and click OK.
Alright, and now a hyperlink is set up.
The picture though, the picture is harder.
So we're going to come back to the webpage and want to right-click and say that we want to save that image as, and save it someplace, we can get it remembered, all right.
So it's downloaded.
Then back in Excel.
Getting that pop-up picture though is tough.
We're going to come back to the REVIEW tab, we run out a new comment and I want it to be completely blank comment, alright.
So backspace through the name and then carefully, you don’t want that comment to disappear, resize the comment so it's about the right size.
And we want to format that comment, so I go to Ctrl+1.
Now, be careful here, if you get only the Font tab, then you didn't resize the comment.
You're in edit but go back to the comment and resize that we get out of the edit mode.
Coming to Colors and Lines, where you can change the color of the comment But even better, we're going to go down to fill effects and say we want a picture.
We're to select the picture, and you know, in 2013 here they try and go offline pictures already on my computer.
PowerExcelBook, click OK, click OK, all right.
And then when we click away, see, now we have a pop-up picture that appears, all right.
So you can continue, as you’re shopping, here adding new items to the list.
But let's come back to our List tab and fill in these columns with a number of items and the value of the item.
So we want to do =COUNTIF, I wanted to count if, and the range and criteria.
Now I'm going to put a comma here in the criteria is this column A2. Close the parentheses, we’ll come back then to the comma, all right.
Now that's on another worksheet, so I'm going to click on the other worksheet and scroll up here and just go through all of the names.
Press Enter and double-click to shoot that down, all right.
So there we go.
That's the number of items and then value of items.
We’re to use =SUMIF, all right, so in this case we have two things on the other end.
Just remember that we're going to use cell A2 there, so we come back to the Range, choose all of the names, comma, let's see if that's equal to A2.
And if it is, add up the Cost from over here.
All right, and double click to copy that down, all right.
So now, for every person we can see how, you know, how much, how many items they have and how much we've spent.
It’ll be interesting to see this is a chart, so to create a chart from non-continuous data, first I choose all those Person names and then I hold down the Ctrl key, and do number of items.
Back here under INSERTS I choose a 2-D Column Charts.
And so there's our # of items chart and then the same thing will do for the dollars.
Hold down the Ctrl key and, maybe make this one into a Pie chart.
And there we go.
You know, okay, so now we have a couple of problems.
One problem is that you don't want someone to accidentally open this file and see everything that's going on.
So I would just come here to INSERT, use the Shape tools, create a nice shape and just cover up the entire screen, like that.
And same thing here on the gifts, will insert a shape and cover up the entire screen.
That way if they open it, you know, they don't see what's going on.
They just think that it's, you know, for some reason there's nothing here.
Ah, there's nothing here.
You know, but then you can always, you know, if you need to see things, you can come here to HOME, Find & Select, Selection Pane and say Hide All.
On the Selection Pane you just say either Hide All or Show All and you'll be able to see those items.
Of course hide the Selection Sane before they come in.
Now, hey, I see there's a problem here.
Your Tom, your CPA poor guy has nothing at all.
What do you get your CPA?
I have the perfect solution for you.
mrx.cl/xlbook40 and I am working on my 40th book.
MrExcel XL book.
XL is 40 in Roman numerals and there’s going to be the 40 greatest Excel tips of all time.
And a very unique present, something you'll never be able to get anywhere else.
Is for twenty-four dollars, you can pre-order the book, it's an autographed book and your CPA’s name will be listed in the book.
Now, of course, the book doesn't come out till April, but you can tell your CPA “Hey, I sponsored you.
You're going to be listed in MrExcel’s book, how unique is that!”.
I guarantee no one else will get that.
Well, there you go, a holiday tracker from my house to yours.
I hope everyone has a great holiday.
We’ll get back after the holiday with another netcast from MrExcel.
