New Feature: Playlist Center! Pick a topic and let our playlists guide the way.

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

Excel 2010 Essential Training
Illustration by

Recording and using a simple macro


From:

Excel 2010 Essential Training

with Bob Flisser

Video: Recording and using a simple macro

We're going to record a basic macro that take cells from one worksheet and deposits them at the bottom of this worksheet. Now because this is an Essential Training course we're only giving light coverage to macros. If you're interested in learning about macros in depth, check out some of the really great macro courses in the lynda.com library. But before we begin recording there is some housekeeping that we need to do first. To record macros we need to display the Developer tab on the Ribbon bar. Now on my computer here, you see there is no Developer tab. If you have the Developer tab on your computer you don't have to go through this step.
Expand all | Collapse all
  1. 1m 35s
    1. Welcome
      57s
    2. Using the exercise files
      38s
  2. 19m 31s
    1. Exploring three common uses for Excel
      3m 17s
    2. Touring the interface
      3m 38s
    3. Finding the commands you need
      3m 51s
    4. Using Backstage view or the File tab
      3m 25s
    5. Maintaining file compatibility
      5m 20s
  3. 21m 23s
    1. Creating a worksheet
      5m 23s
    2. Techniques for copying and pasting
      3m 57s
    3. Entering data automatically with Auto Fill
      4m 37s
    4. Targeting large data groups
      4m 26s
    5. Changing a worksheet's structure
      3m 0s
  4. 47m 50s
    1. Understanding formulas and functions
      4m 41s
    2. Entering data in a worksheet
      3m 22s
    3. Adding numbers manually
      5m 1s
    4. Adding numbers using Sum and AutoSum
      6m 11s
    5. Adding a whole worksheet
      1m 48s
    6. Working with numbers in columns
      4m 53s
    7. Preventing errors using absolute references
      5m 57s
    8. Working with times and dates
      3m 8s
    9. Using IF
      4m 49s
    10. Using SUMIF and AVERAGEIF
      4m 15s
    11. Naming and using cell ranges
      3m 45s
  5. 33m 57s
    1. Formatting numbers and dates
      7m 6s
    2. Applying fonts, background colors, and borders
      4m 35s
    3. Adjusting columns, rows, and text
      5m 2s
    4. Using conditional formatting
      4m 6s
    5. Using custom conditional formatting
      5m 49s
    6. Adding pictures and shapes
      7m 19s
  6. 25m 27s
    1. Inserting SmartArt
      6m 54s
    2. Coordinating a look using themes
      3m 22s
    3. Applying built-in styles
      3m 16s
    4. Creating and sharing styles
      5m 33s
    5. Using templates
      4m 9s
    6. Creating and using original templates
      2m 13s
  7. 13m 23s
    1. Making the pieces fit
      4m 57s
    2. Inserting headers and footers
      3m 51s
    3. Printing and PDFs
      4m 35s
  8. 34m 3s
    1. Finding and replacing data
      3m 12s
    2. Freezing panes
      3m 0s
    3. Repeating row and column titles
      3m 34s
    4. Creating multiple custom worksheet views
      5m 18s
    5. Hiding or grouping rows and columns
      5m 31s
    6. Managing worksheets
      7m 23s
    7. Calculating formulas across worksheets
      6m 5s
  9. 36m 34s
    1. Importing and exporting data in Excel
      8m 2s
    2. Setting workbook permissions
      6m 44s
    3. Inserting and editing comments
      6m 49s
    4. Sharing a workbook
      1m 25s
    5. Tracking changes
      3m 5s
    6. Saving files in shared locations
      10m 29s
  10. 27m 30s
    1. Splitting cell data into multiple cells
      2m 22s
    2. Joining data from multiple cells
      4m 18s
    3. Basic and multi-field sorting
      6m 30s
    4. Using tables to sort and filter data
      4m 31s
    5. Inserting automatic subtotals
      3m 46s
    6. Creating lookup tables
      6m 3s
  11. 32m 56s
    1. Using auditing to diagram
      6m 3s
    2. Using evaluation in Excel
      2m 2s
    3. Working with Goal Seek
      5m 29s
    4. Using data tables in formulas
      6m 2s
    5. Using scenarios in formulas
      5m 28s
    6. Exploring the Analysis Toolpak
      7m 52s
  12. 18m 1s
    1. Discovering PivotTables
      2m 22s
    2. Creating a basic PivotTable
      2m 46s
    3. Modifying a PivotTable
      6m 57s
    4. Creating and modifying a PivotChart
      5m 56s
  13. 26m 58s
    1. Choosing chart types
      1m 55s
    2. Inserting Sparklines
      3m 54s
    3. Creating a column chart
      3m 23s
    4. Modifying a column chart
      5m 47s
    5. Creating and modifying a pie chart
      6m 45s
    6. Placing Excel charts into other Office applications
      5m 14s
  14. 21m 53s
    1. Understanding macros
      3m 5s
    2. Recording and using a simple macro
      11m 58s
    3. Editing a macro
      6m 50s
  15. 20m 33s
    1. Customizing the Quick Access toolbar
      3m 30s
    2. Customizing the Ribbon bar
      8m 44s
    3. Setting Excel options
      8m 19s
  16. 16s
    1. Goodbye
      16s

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 Essential Training
6h 21m Beginner Jun 09, 2010

Viewers: in countries Watching now:

In Excel 2010 Essential Training, Bob Flisser demonstrates the core features and tools in Excel 2010. The course introduces key Excel skills, shows how to utilize these skills with in-depth tutorials on Excel functions and spreadsheet formatting. It also covers prepping documents for printing, working with large worksheets and workbooks, collaborating with others, using Excel as a database, analyzing data, charting, and automating and customizing Excel. Exercise files are included with the course.

Topics include:
  • Copying and pasting techniques
  • Working with formulas and functions
  • Dealing with formula errors
  • Creating lookup tables
  • Naming cell ranges
  • Formatting data and worksheets
  • Finding and replacing data
  • Creating SmartArt diagrams
  • Creating charts and PivotTables
  • Recording macros
  • Sharing workbooks
Subjects:
Business Computer Skills (Windows) Spreadsheets Teacher Tools Education Student Tools
Software:
Excel
Author:
Bob Flisser

Recording and using a simple macro

We're going to record a basic macro that take cells from one worksheet and deposits them at the bottom of this worksheet. Now because this is an Essential Training course we're only giving light coverage to macros. If you're interested in learning about macros in depth, check out some of the really great macro courses in the lynda.com library. But before we begin recording there is some housekeeping that we need to do first. To record macros we need to display the Developer tab on the Ribbon bar. Now on my computer here, you see there is no Developer tab. If you have the Developer tab on your computer you don't have to go through this step.

But I am going to go here to the File tab to go to Backstage View, and down over here let's click Options and we have all the Options categories here on the left and let's click over here where it says Customize Ribbon. Now on the right, we have a list of all the tabs that are visible, Home, Insert, Page Layout and so on, and over here is the Developer tab. So just click there to put a check, click OK, and now you can see there's the Developer tab up there on the Ribbon bar. Click it we have all the Developer tools. And we also need to set security. So click down here on Macro Security and you want to make sure that the second radio button is selected.

It should be selected by default, but I want to make sure that you don't have the first button selected and the idea is that there are nefarious characters writing macro viruses and Excel tries to protect itself against them. If you have at least Disable macros with notification, it means that when Excel sees that there is a workbook that contains macros, it will at least notify you that hey, there are macros here, do you want to enable them or not? If you have that first radio button selected, you won't even get a chance to run the macros. So let's click OK. And since we'll be taking data from another workbook, let's open that workbook now.

So right now we have the recording macro.xlsx, so either press Ctrl+O on your keyboard to open or go to the File tab and Open, and in the Chapter 13 folder of your Exercise Files open up the northeast data file. And you can see the structure is the same as the workbook that we were just looking at. We have Order number, Region, Month, Size and Quantity. Now to switch back and forth between one workbook and another, easiest way I think is to press Ctrl+F6, hold down the Ctrl key and press the F6 key on your keyboard.

That works in just about every single Windows program under the sun, not just Excel. Now for whatever reason you don't like Ctrl+F6, you could always go to go to the View tab and over here Switch Windows, whichever you prefer is fine, and let's go back to the Developer tab. One last thing we need to decide when recording macro is are we recording with relative references or not with relative references? In other words with absolute references. That's what this button lets us decide. Here's the idea. Right now I have Cell B3 selected.

When I record the macro and I hit the down arrow let's say, no surprise I go one cell down into Cell B4. Well, when you are running the macro when you're running the macro what happens if you're starting on Cell C3? When you hit the down arrow what's going to happen? Are you going to go straight down to Cell C4? Well, that's a relative reference, or are you going to go literally to Cell B4, which is an absolute reference, which is what you recorded. So that's what you have to decide is will your navigation be exactly literally the cells that you are recording or will they be relatively going up or going down, left or right? For the purposes of this macro that we are going to record let's turn Relative References on.

Okay, now we are ready to record, so click the Record Macro button and the Record macro dialog box comes up. So let's give it a name. I will call this getData. Now a macro cannot have spaces in the names. You can have either mixed case or you can use underscores. Let's apply a shortcut key. Because we are going to get data, I want to use a shortcut key of Ctrl+Shift+G. So click in this box here and type Shift +G like you are going to type a capital G, and you see it makes it Ctrl+Shift+G. Then you might wonder, hey Bob, why make it Ctrl+Shift+G, why not just plain old Ctrl+G, why make this more difficult? And the answer is that in Excel almost all of the Ctrl this and Ctrl that shortcuts are already used.

Ctrl+G is the command to go to a particular cell. And if we override that here, then Ctrl+G will no longer be to go to a particular cell. But there are just about no Ctrl+ Shift anything in use in Excel, but I'll leave that up to you. That's personal preference. Now we also need to decide where are we storing this macro. Over here it says where to store the macro, and click that down Aarow and you have three choices, Personal Macro Workbook, New Workbook or This Workbook. Well, let me explain. The Personal Macro Workbook is a specific file that Excel keeps, and if you have macros in it, that is if you record macros and put them in the Personal Macro Workbook, they will always be available when you start Excel, because what Excel will do is it will open the Personal macro Workbook when it starts up, but it will open up hidden, so it doesn't get in your way and there is no danger of you messing it up.

Well, if you don't have a Personal Macro Workbook, Excel will create it the very first time you select it. I think it's pretty cool. Now if you choose to store your macro in a new workbook Excel will literally create a brand new workbook and put your macro in there. You would use that option if you intend on distributing your macro to other people. Well, for the purposes of this exercise, let's keep the default of storing the macro in This Workbook, and we will do that because the macro that we are recording is really meant for yhis workbook, so there is no need to store it anywhere else.

Now we could enter a description, and this is just kind of like a yellow sticky note that's attached to it, and we can type-in something like "Gets data from other sheet and deposits them here." Now when we click OK, then recording is live, so click OK. And how do you know recording is live? Up here you see the Stop Recording button. Now real important. When you are done creating the macro, you need to click that Stop Recording button, because if you don't, everything you do including opening and closing files and switching tabs and all that will be recorded, and eventually the recording session will abort, Excel may crash, you won't be very happy, so you just really need to remember to Stop Recording.

Now since we are going to be navigating through this worksheet, I think it's a lot easier and a lot more accurate if we use some handy shortcuts, and that will give you everything you need to know. In general, when I do something like that, I find it's always best to start in the upper-left corner in Cell A1, so press Ctrl+Home. Now the advantage of that is when you playback the macro, it doesn't matter which cell you have selected. You could be selecting Cell Q4300 and you will immediately go back to Cell A1, so you can start selecting the cells if you want.

Now the data that we bring in, we want that to start in Cell A13. Well, right now we wanted to start in Cell A13 because that's the next row, but once we have data from the workbook that we are bringing in, the next time we bring in data, we wanted to start at the first blank row there so it's not always literally A13. So how do we get there? Well, press Ctrl+Down Arrow. The first time you press Ctrl+Down Arrow, it goes to the starting line there. Press Ctrl+Down Arrow again. You see it goes to the end of that section, so you just press Ctrl+Down Arrow twice.

Now just press the Down Arrow by itself. So you are absolutely 100% guaranteed that your cursor is now in the first blank available row. Now we already opened up that other workbook, so let's switch there. Now I am going to press Ctrl+F6. If you prefer, you could go to the View tab and use the Switch Windows command. So I will press Ctrl+F6. Remember this is being recorded by the macro, and like we did before, press Ctrl+Home. So it doesn't matter what cell is selected in this workbook when you open it.

And we will do kind of the same thing. Press Ctrl+Down Arrow once. That gets to the column header. Well we don't want to select the column headers, so just hit the Down Arrow. All right, so we press Ctrl+Home, we then press Ctrl+Down Arrow, then we press the Down Arrow by itself. Now we want to get to the last used row. Now in this case, it's Row 12. In another workbook, it might be a different row. So we want to make sure that the macro gets exactly the data and doesn't leave out any data and doesn't get any blank rows.

So that's why clicking with the mouse really isn't going to work. So press Ctrl+Shift+Down Arrow, so that selects to the end of this row. Now we need to leave these cells selected and also select to the right, so press Ctrl+Shift+Right Arrow. So you are now guaranteed to select all of the data here. Now we need to copy to the Clipboard. I will press Ctrl+C to copy. If you prefer you could go to the Home tab and click the Copy button. Either way will work. Okay, so we have this copied to the Clipboard. We see the marching ants.

Now let's switch back. Again I will press Ctrl+F6, or if you want you could go to the View tab, and Switch Windows. It's your choice. Now our cursor is in the right spot, so all we have do is paste. So I will press Ctrl+V to paste. If you prefer you could go to the Home tab and click Paste. There we go. Now we want to clear off the Clipboard so press the Escape key and let's deselect all of these cells. So just press the Home key. It doesn't really matter which cell you have selected. All right, now we are ready to stop recording.

So go to the Developer tab if you are not already there and click Stop Recording. Okay, great. Well we recorded the macro. How do we know it works? Well, let's bring in data from another region, so right now we have the Central Region, we have the Northeast Region. I am just going to press Ctrl+F6, here is the Northeast data. Let's just close this file, and let's go open another one. So you could press Ctrl+O or go to the File tab and Open. So we have Central, we have Northeast, let's open up Southeast. Double-click it, and you can click anywhere. It doesn't matter.

Maybe go back here to the recording macro. Let's run the macro, press Ctrl+ Shift+G, and boom, there it is. So we have Central, Northeast, Southeast. Let's try it again. Just press Ctrl+F6 and let's close the Southeast data because we already have the data and let's Open and press Ctrl+O or go to the File menu and Open. And let's open up the West data. Okay, so it doesn't matter what cell you have selected, Ctrl+F6, let's switch back here, and again press Ctrl+Shift +G and boom, there's your West data.

And notice there are different numbers of rows for each region. We have Central is a little. Northeast is more. We have a lot more West data than the others. So if you were to try selecting cells by clicking with your mouse you really wouldn't know which cell to select or how many cells to select, but by using those keyboard shortcuts, there is absolutely no chance for error. Well, as long as you do it right. Now one more thing. If we want to save this file with the macro in it, we can't just save with the .xls extension. We have to save this workbook in a macro-enabled format.

So you could either press F12 to Save As or go to the File tab and Save As, and we could keep the name, but for the file type, click that drop-down and up here let's choose Excel Macro-Enabled Workbook, see it's a .xlsm, and then click Save. Now just one more thing. This isn't some exercise I simply dreamed up for this course. This is only a slightly simplified version of a real set of macros I once created for a client that used it every single day. What would happen is in the mornings they would import new data from an order system, using techniques that we covered in the movie on importing and exporting, and they would bring that data into a growing worksheet just like we had here.

So if you completed this exercise, give yourself some congratulations.

There are currently no FAQs about Excel 2010 Essential Training.

 
Share a link to this course

What are exercise files?

Exercise files are the same files the author uses in the course. Save time by downloading the author's files instead of setting up your own files, and learn by following along with the instructor.

Can I take this course without the exercise files?

Yes! If you decide you would like the exercise files later, you can upgrade to a premium account any time.

Become a member Download sample files See plans and pricing

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 Upgrade 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 Essential Training.

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

Are you sure you want to delete this note?

No

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
Terms and conditions of use

We've updated our terms and conditions (now called terms of service).Go
Review and accept our updated terms of service.