Platform Banner

Excel - Work with macros Tutorial

In this video, you will learn how to work with macros in Microsoft 365. The video covers the basics of macros and demonstrates how to create and use them in various Office applications like Word, Excel, and PowerPoint.
This will help you automate repetitive tasks and increase your productivity with Microsoft 365.

4:40
4403 views

Teams - Do I share with OneDrive, SharePoint, or TEAMS?
- 2:24
- Viewed 3870 times
SharePoint (Classic Sites) - Understanding the different types and roles of team sites in SharePoint
- 3:05
- Viewed 3517 times
SharePoint (Classic Sites) - Sharing a folder or document with someone outside the company
- 3:44
- Viewed 3686 times
SharePoint (Classic Sites) - Restricting a document to read-only mode (Extract a document)
- 2:05
- Viewed 3410 times
Societal Implications of Generative AI
- 5:58
- Viewed 2315 times
Outlook - Easily Sort Your Mails
- 01:38
- Viewed 464 times
Outlook - Use Categories
- 02:51
- Viewed 543 times

Available in Microsoft365

Objectifs :

Learn how to automate repetitive tasks in Excel by recording macros, including the importance of using relative references for effective macro functionality.

Chapitres :

Introduction to Macros in Excel
In this section, we will explore how to automate repetitive tasks in Excel using macros. Macros allow you to perform a series of actions with a single click, significantly improving efficiency.
Setting Up the Macro Recorder
To begin, ensure that the macro recorder is available on the ribbon. Access this by clicking the File tab, selecting Options, and then customizing the ribbon. In the code group, you will find the 'Record Macro' button along with other related options.
Recording a Simple Macro
We will record a macro to automate an auto-fill operation. Start by typing 'Sunday' in a cell. Right-click the cell and drag it to the right. As you drag, a tooltip will display the values that will fill each cell, progressing through to 'Saturday'. Release the right mouse button and select 'Fill Series' to complete the action. After recording, clear the cells and name the macro 'FillDays'. Remember, macro names cannot contain spaces.
Testing the Recorded Macro
After stopping the recording, clear the filled cells and select 'Day One' to test the macro. Open the macro dialog box and click 'Run'. If the macro fills the wrong cells, it indicates that the macro recorder was set to absolute references by default.
Understanding Relative References
The failure of the macro to fill the correct cells occurred because the macro recorder was not set to use relative references. To fix this, clear the cells, enable 'Use Relative References', and start recording a new macro named 'FillDaysRelative'. After typing 'Sunday' and performing the auto-fill, stop the recording.
Successful Execution of the Relative Macro
Clear the cells again and switch to a different starting cell, such as C7. Run the 'FillDaysRelative' macro. This time, it should work perfectly, demonstrating the importance of using relative references for macros.
Conclusion and Next Steps
In the next video, we will learn how to manually rewrite the 'FillDaysRelative' macro using the Visual Basic Editor in Excel. Understanding how to create and modify macros will enhance your ability to automate tasks effectively.

FAQ :

What is a macro in Excel?

A macro in Excel is a sequence of instructions that automate repetitive tasks, allowing users to perform complex actions with a single click.

How do I record a macro in Excel?

To record a macro in Excel, go to the 'View' tab, click on 'Macros', and select 'Record Macro'. Perform the actions you want to automate, then click 'Stop Recording' when finished.

What are relative references in Excel macros?

Relative references allow a macro to adjust its actions based on the current position of the active cell, making it more flexible and applicable to different locations in the worksheet.

Why did my macro not work as expected?

If your macro did not work as expected, it may be due to not using relative references. Ensure that the 'Use Relative References' option is enabled before recording the macro.

Can I edit a macro after recording it?

Yes, you can edit a macro after recording it by using the Visual Basic Editor, where you can modify the VBA code to customize its functionality.

Quelques cas d'usages :

Automating Data Entry

In a sales department, a user can create a macro to automate the entry of daily sales data into a spreadsheet, saving time and reducing errors.

Generating Weekly Reports

A project manager can use a macro to automatically generate weekly status reports by filling in predefined templates with data from various sources.

Creating Custom Schedules

An HR professional can record a macro to fill in employee schedules based on a standard template, allowing for quick adjustments and updates.

Data Cleanup

A data analyst can use a macro to automate the process of cleaning up data sets, such as removing duplicates or formatting cells consistently.

Batch Processing of Files

A finance team can create a macro to batch process multiple Excel files, applying the same calculations or formatting across all documents efficiently.

Glossaire :

Macro

A macro is a set of instructions that automate repetitive tasks in Excel. It allows users to perform complex sequences of actions with a single command.

Macro Recorder

The macro recorder is a built-in tool in Excel that allows users to record their actions and create a macro without needing to write any code.

Relative References

Relative references in Excel macros allow the macro to adapt to the position of the active cell. This means that the macro will perform actions based on the current cell's location rather than fixed cell addresses.

Auto Fill

Auto Fill is a feature in Excel that allows users to quickly fill a series of cells with data based on a pattern, such as days of the week.

Visual Basic Editor

The Visual Basic Editor (VBE) is a tool in Excel that allows users to write and edit VBA (Visual Basic for Applications) code for more advanced macro creation and customization.

00:00:06

so that you can do the task again with a single click.

00:00:10

The best way to do that, record a macro.

00:00:15

The macro recorder is the easiest way to create a new macro in Excel.

00:00:21

Now before we get started, I want to make sure

00:00:24

the developer tab is available on the ribbon,

00:00:28

that's where all of the macro commands are.

00:00:32

I click the File tab,

00:00:36

options,

00:00:38

customize ribbon,

00:00:41

and over here I select developer and then click OK.

00:00:47

Now I see the developer tab,

00:00:53

and here in the code group are the record macro button

00:00:56

and some other buttons I'll go over later.

00:00:58

So let's get started.

00:01:01

I'll record an auto-fill operation,

00:01:04

where I build a series of days based on typing Sunday in a cell.

00:01:10

After I type Sunday, I right click the cell and drag it to the right.

00:01:17

As I move over here, the Tooltip show what will go in each cell,

00:01:24

all the way through Saturday,

00:01:27

when I get over here, I release the right mouse button

00:01:32

and click fill series,

00:01:36

that's what we want to record, so I'll clear these cells and select day one.

00:01:42

I'll repeat these steps, but this time

00:01:46

I'll turn on the macro recorder,

00:01:49

I click the record macro button,

00:01:52

let's name this macro FillDays,

00:01:56

macro names can't contain spaces,

00:01:59

I click OK to start the recorder.

00:02:03

See how the record macro button turned to stop recording?

00:02:08

When I'm done, I'll click that.

00:02:19

We're done with our auto-fill, and since that's all

00:02:22

we want to record, I'll click stop recording.

00:02:27

Now I clear these filled cells to clean up the worksheet,

00:02:31

and select day one again so we can test our macro.

00:02:36

I'll click the macro's button, to bring up the macro dialog box

00:02:40

and click run: perfect so far.

00:02:45

I'll clear these cells again and this time I'll select

00:02:49

the cell in a different column and row, C3, just to test

00:02:53

the macro a little more, watch what happens.

00:03:02

The macro filled in A1 through G1,

00:03:05

in other words it failed!

00:03:08

So why that happened ?

00:03:10

It happened because, by default,

00:03:13

the macro recorder was using absolute references.

00:03:17

Before I started recording, I should have turned on use relative references.

00:03:24

By using relative references, the macro will build

00:03:27

a series by selecting the active cell, and seeks cells to its right,

00:03:32

instead of building a series using A1 through G1.

00:03:37

Well, I know I need to record a new macro,

00:03:40

so I'll clear these cells and turn on use relative references.

00:03:46

Just to change things up a bit let's start at cell B5.

00:03:51

So I click record macro, give it a new name,

00:03:56

FillDaysRelative and click OK to start recording.

00:04:02

I'll type Sunday,

00:04:04

do my auto-fill

00:04:08

and stop recording.

00:04:11

Now I'll clear the cells and I'll switch to cells C7

00:04:15

and run my macro from there.

00:04:17

This time I choose FillDaysRelative and run,

00:04:22

and now it works perfectly.

00:04:25

So you can see that the relative references setting can be very important.

00:04:30

In the next video, we'll rewrite the FillDaysRelative

00:04:34

macro manually, in an Excel tool called the Visual Basic Editor.

Mandarine AI: WHAT YOU SHOULD KNOW

RELATED COURSES This tutorial is part of the following courses

2729 Views

Video Training Microsoft Excel - Advanced level

With our "advanced" tutorials, discover and master the full potential of Excel, using complex functions, macros and the power of DCTs and Po...

ALL COURSES