Viewers: in countries Watching now:
Whether you're a novice or an expert wanting to refresh your skillset with Microsoft Excel, this course covers all the basics you need to start entering your data and building organized workbooks. Author Dennis Taylor teaches you how to enter and organize data, perform calculations with simple functions, work with multiple worksheets, format the appearance of your data, and build charts and PivotTables. Other lessons cover the powerful IF, VLOOKUP, and COUNTIF family of functions; the Goal Seek, Solver, and other data analysis tools; and how to automate many of these tasks with macros.
When you want to use a Macro, you will hear the term Playback the Macro, Execute it, Run it, Activate it. The whole idea is you've created a macro and you want to make it work; make it do what it's supposed to do. And when you're getting started with Macros, the preferred method tends to be a keystroke shortcut, but there are other ways as well. Let's create a new macro here and also consider the idea that sometimes the macro you want to create and then Run is stored in such a way that it will always be available regardless of which workbook is open.
In this worksheet called PricingSheet, there are a lot of cells with formulas, a lot of cells with just pure numbers. And let's imagine that we want to highlight just the cells with numbers. So let's create a macro here, VIEW tab, drop-arrow or for Macros, choose Record Macro. And we're going to call it something along the lines of HighlightValueCells. Macro names cannot have spaces, this time it will just use upper and lower case value cells.
Keystroke shortcut, V is the key letter perhaps, we're thinking of value, how about Ctrl+Shift, I'm holding down the Shift key as I type the letter V. If we want our macro to be available in any workbook, in other words if we create this macro and then close the current workbook, we still want to be able to use this macro. So we don't want to store it in this workbook. The other term that might surprise you, Personal Macro Workbook. If it were called Global Macro Workbook, perhaps it might be a little more pertinent in terms of what it means.
If you store a macro in this location, it means that in the future, regardless of which workbook is open, you will be able to use Run, Activate, Execute, whatever the term you're using this particular macro. Personal Macro Workbook might not even exist right now. Ultimately simply by clicking OK and recording this macro, you are in effect creating this if you haven't already created it. And the name of that will be Personal.XLSB, its complete name of file extension.
That's perhaps a little bit of trivia but at the same time; I want to emphasize the idea that the Personal Macro Workbook is a separate workbook. And once you create it and we can simply do as we're about to do it here by a recording a macro and placing it there, this workbook is always available in the future regardless of which file is open. It is stored on the current computer you're using. And so what happens sometimes is you will want to copy certain macros elsewhere, but we simply want to record this macro so that it's available to all workbooks.
And all we want to do in this macro is to highlight the cells that have values, so we'll click OK. And the process begins for this command sequence; go to the HOME tab in the ribbon and the extreme right button, Find & Select. This is a feature that many people might use, not everybody, so we always want to be reminded that macros although we might think of them as being ideal for us, aren't necessarily for everybody. Go To Special, choose the Constants button and if we're only concerned with numbers here or values, let's uncheck the box for Text, and Logical, and Errors.
And as we click OK, we see the cells that are highlighted. If we want to make sure that they stay highlighted by a way of a color, then we'll go to the HOME Tab, the Fill Color Font, the arrow to the right and choose the color that we think will work best in this particular example. And we like the light green, maybe a little bit lighter, something like that. Recognize now that all these cells are selected and they have color, a slight difference and is not critical. If I click in cell A1, it will no longer be highlighted, but the color will be there.
So that's all we want our macro to do. Every time we execute this macro, we want to select all the cells that have numbers and apply a light green background, and then go to cell A1, although we could go to any cell. So we finished recording the macro. We can stop by pointing to the box just to the left to the word READY in the lower left corner of the screen in the status bar, Stop Recording. Let's try this in a different worksheet. We got the active cell anywhere here; we want to highlight those value cells and the keystroke shortcut I used, Ctrl+Shift+V. It works great.
What happens if we start to record a few more macros and a few more and a few more? We probably run out of meaningful keystroke shortcuts, if the letters were that meaningful, but we might want to be able to get to our macros in a different way. So I'll also confront this idea. What if we didn't want the macro to run? We ran it by mistake, maybe I did that here. Can we undo a macro? No we can't.Now the Undo arrow might look active, and you might click the drop arrow here, and if you've just recorded the action here, you might say, oh, I'll just undo it. Well that takes us back to here.
And we can't really undo what happened here. What we did here ultimately was just to apply color, so we certainly manually could do that. But the idea that you can't undo a macro is a critical thought. What if your macro deleted data? And I would strongly suggest that at least for a while, you don't write macros that delete data. You can't just casually say, well I'll come back and undo it. What you might need to do is close the file and not save it. What if you've done a lot of other good things in the meantime? Well you're going to loose all those too. So you want to be really careful with the idea that you can't undo what a macro does.
In other words, you can't reverse the steps Now in this case, lets imagine that I don't want the color there, we'll just get rid of the color. So I'll highlight the cells and easily by way of the HOME tab, go to the Fill Color bucket, choose No Fill. Now after doing some other things and coming back here, now I do want to run my macro. The one that highlights the number cells, maybe I forgot the keystroke shortcuts. So what do we do, if we forget a keystroke shortcut? Go to the VIEW tab, choose the drop arrow for Macros, go to View Macros.
I have only one macro here; there it is. I'll click it and we have the option to the right Run. It certainly isn't fast, but it's our fallback method. If it's one of those longer macros that manipulates a lot of data and takes two or three minutes, well we've saved a good deal of time, no question about it. Here's another thought. What if we want to change the keystroke shortcut? Once again go back to the same location on the VIEW tab click the drop arrow for Macros, View macros, here's the macro we're working with. We go to Options and change the keystroke shortcut.
And this is also where we go, if we didn't initially have a keystroke shortcut, we can assign it now, or if we want to delete the keystroke shortcut. Maybe we want to create another macro that's going to use this shortcut key. And so we see, we can easily change the keystroke shortcut or add it or delete it. In this case, we don't want to do any of those things. For certain macros, perhaps like this one that we think we might want to use often, we can also get to this in a different way. We can add a button to the Quick Access toolbar.
This is the set of buttons typically above the ribbon in the upper left-hand corner of your screen. It possibly is below the ribbon. No matter, where it is, if you Right-Click it, you can then choose Customize Quick Access Toolbar. And then in this dialog box called Excel Options, Choose commands from, click the drop arrow and choose Macros. Now you might or might not be seeing what I'm seeing here on the screen. Those are system type macros, but somewhere in here, you should see if you have created a macro, the name of the macro that you created.
There's the one I just created, let's add this to the Quick Access Toolbar. Maybe you're a little picky and you say, I don't like that icon. Well, you can come down here and modify, you've got 181 choices and I don't think anyone of them suggest exactly what we're trying to do here but I'll just choose a green box. There we go, OK. And as I click OK, look in the upper left corner of the screen, we now have that icon. We might leave it here for a long, long time, maybe forever if we use this often. It's going to be there no matter which workbook is open, provided the next exit from Excel is a normal one, so it will be there all the time.
And whether this workbook is open, it won't make any difference whatsoever. So we might go over to this worksheet right here and click this button, it highlights the cells that have numbers. It works easily and dates by the way are considered values, that's why they're highlighted here. Once again, can I undo? We'll not really to do. I can certainly remove the colors in other ways here; I can highlight all this data here, I'll go across here and get rid of the colors that way. It's like I also got rid of the colors up here, we won't worry about that.
But again, it brings back the idea that you can't really undo what a macro has done. You can certainly take manual steps to undo the effect. Now there's another possibility here for running macros. Even though this macro that we just saw is ideally designed to work in this workbook, that workbook, there could be situations where you want a macro to run right here by way of a button. And so we could create a button and there are any number of different ways to do this. One way can be, we can go to the INSERT tab and go to the Shapes icon for example and I'll just pick one of these at random, how about a rounded rectangle and I'm just going to draw this on the screen here; and type in Highlight Number Cells.
It doesn't have to match the wordings exactly of the macro, so Highlight Number Cells. And do all the formatting things that you might want to do with this. You can make it bold, bigger, all that sort of thing, not too critical there, but just to show we can do that too. But the key step next would be to Right-Click here and Assign Macro. So we're going to assign a macro to this button. For the moment we have only one, there it is right there. We'll click OK. Now we're still in edit mode, we might want to shrink this, do other things with it, whatever.
As we click away from it, in the future, anytime we slide the mouse over it, we see the pointer finger. This will activate the macro and what's it going to do here? It highlights the value cells and makes them green. And so we see one more way to activate a macro. We can also do this with icons and pictures too; this is just a simple example with a shape. So the way we make a macro work, and again the terms Playback, Run, Execute are all used synonymously is simply to use a keystroke shortcut, probably the most common way when you're getting started.
But ultimately by way of a button in the Quick Access Toolbar as we saw, and most recently by way of an icon or a shape on the worksheet.
There are currently no FAQs about Excel 2013 Essential Training.
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.