Create and use macros in a spreadsheet

In this article, We will discuss Unit 2: Electronic spreadsheet (Advanced), learning outcome 4 – Create and Use macros in spreadsheets for CBSE IT 402 class 10.

What is macro?

The best way to remember and learn something is by listening. So if you want to learn something you can record the contents in your own voice and listen as many times as you want and you can learn anything easily. Similarly, a spreadsheet provides a feature called macro to record the commands, tasks, or the activities performed by the user on a specific worksheet or set of worksheets, and then it can be used to avoid repetition of commands or similar tasks in other worksheets.

A macro is a recorded name of set of tasks or commands in MS Excel which is used to repeat them by single click or shortcut key.

Advantages of Macro

1. It saves user’s time
2. Repeat a number of commands in just one click or using the shortcut key
3. User can record numerous formatting commands or formulas in a single worksheet
4. User can use their names for each macro
5. It reduces the errors occurring with repetitive tasks
6. It is capable to perform any kind of complex calculations easily

Click here to read about more advantages.

The developer tab

MS Excel provides a developer tab to work with macros. By default, it is not activated in excel. Follow these steps to activate the Developer tab.

1. Click on File –> Options.
2. A new dialog box appears, Find the customize ribbon from the dialog box.
3. Find the main tabs then Developer check box.
4. Click on the Developer checkbox.
5. Click on the OK button.

Creating a macro

I have prepared data for salesmen for month wise sales and recording macro to prepare a report. To create a macro follow these steps:

Step 1 Place the cursor in the cell C2.

Step 2. To start the process, click on the Developer –> Use relative references to copy the relevant cell address of the worksheet.

Step 3 Now click on Developer –> Record Macro.

Step 4 Now record macro dialog box will appear as displayed in the following screenshot.

1. Macro Name: Give a name for your macro.
2. Shortcut key: You can assign a shortcut key for the macro.
3. Store macro in: It provides three locations to store your macro as follows:
   • Personal macro workbook
   • New Workbook
   • This Workbook
4. Description: You can write a brief description of your macro.

Step 5 I have selected the option this workbook. Click on OK button. It will display two buttons that indicates recording is started. Observer the following screen shot as given 1 and 2 number.

Step 6 Now type formulas in C13, C14, and C15. Similarly type the formulas for Maximum and Minimum. Apply formatting if you want to apply and click on Stop recording button as displayed in step 5.

Running Macro

Step 1 Place the cursor in D4.

Step 2 Now click on Developer –> Macros option.

Step 3 Macro dialog box will appear with the list of Macros.

Step 4 Select a macro to run from the list and click on Run button.

I hope you understand the concept of creating and using macro, feel free to ask your doubts or query in comments. Give your valuable suggestions as well as feedback in the comment to section to improve more. Support us by sharing this article with your classmates and friends who need these notes. Just click on the share button and help them.

Thank you for reading!!!!