Macros: A Hidden Time Saver in Excel

here we share the much-overlooked Excel Macro feature – and how it can save you considerable time every day.

How do Macros Work?

 

The Macro Recorder in Excel is a useful tool that allows users to automate repetitive tasks by recording the steps. The Macro Recorder generates Visual Basic for Applications (VBA) code. It records the steps taken by the user in any given process and converts them into VBA code. This process can then be replayed whenever needed in the Visual Basic Editor.

Why you should use them.

The Macro Recorder is particularly helpful for users who are not familiar with VBA programming, as it eliminates the need to write code manually, but the Macro Recorder is also very useful for users with VBA programming knowledge.

Next, we will explore the best practice, how to record macros as well as the strengths and limitations of using them.

 

Best Practice:

If you spend 5 hours per month creating an Excel report, good news! You won’t have to again if you follow these steps.

  • Break the report down into several more bitesize macros. This makes it much easier to handle the macros and when something changes in the process, you will only need to re-record the relevant macro.
  • Record the first part. Stop the Macro recorder, and test to make sure that it is working. Record the next part, stop, and test etc. This testing will save you from the frustration of a poorly recorded macro !

 

Recording Macros with the Macro Recorder

– To start recording a macro, go to the Developer tab in the Excel ribbon and click on the “Record Macro” button.

You may have to add the Developer tab to your ribbon. It is done by clicking File -> Options -> Customize Rippon, and tick Developer tab.

Click Record Macro

macros

Give the macro a name, and if you want to be able to execute the macro by using a shortcut, enter the shortcut in the Shortcut Key box.

– Once the recording starts, every step you perform in Excel will be recorded, including typing, clicking on cells or commands, formatting, and importing data.

– The Macro Recorder records the steps in VBA code, which can be viewed and edited in the Visual Basic Editor.

– After you have completed the steps you want to record, you can stop the recording by clicking on the “Stop Recording” button in the Developer tab.

If you want to see the macro code, click Visual Basic in the Developer tab. Excel store the macro code in modules. In the Project explore to the left, you can see all open Excel files. You will need to double click Module 1 to view the code. In this example, we have have recorded two macros.

macros

When you want to execute the macros, click Macros in the Developer tab, select the macro, and click Run.

macros

Saving a file with macros.

It is important that you save a file with macros as a macro enabled workbook(*.xlsm). Otherwise, the macros will be deleted. You can find the option under Save as Type.

 

Strengths of the Macro Recorder

The Macro Recorder is a great tool, especially for those who are new to VBA programming. It provides a starting point and helps users identify the syntax and structure of VBA code.

– It allows users to automate recurring tasks and apply the same actions to multiple cells, rows, or columns without the need to manually perform them each time.

– The Macro Recorder can be a helpful reference for users who want to learn VBA by examining the generated code.

– You can save and reuse the recorded macros in the same workbook. You can also share them with others.

 

Limitations of the Macro Recorder

Whilst the Macro Recorder is a powerful tool, there are certain limitations to be aware of.

– It may generate more code than necessary, which can slow down the execution of the macro.

– There are some complex steps or tasks which you can not directly record using the Macro Recorder. For Example, looping through a range of data.

– The Macro Recorder is not suitable for all scenarios. In some cases, you might have to use manual VBA coding to achieve the desired functionality.

 

Conclusion

Hopefully you agree, you can use the Macro Recorder in Excel to save yourself significant time. By following the steps above, you’ll be able to automate repetitive tasks by recording user steps and actions and generate VBA code. It is especially useful for users who are new to VBA programming and provides a starting point for understanding and utilising VBA in Excel.

Productivity win: Excel MAXIFS & MINIFS functions (4 of 4)

This blog is part 4 of a series that explores the amazingly useful functionality of some of the most popular Office 365 Excel functions. In the previous blog, the focus was on the UNIQUE function. Building on this, we will explore how useful the MAXIFS and MINIFS functions are in finding the highest or lowest value more efficiently.

What are the MAXIFS and MINIFS functions and why are they useful

The MAXIFS and MINIFS functions allow you to find the highest or lowest value based on one or more criteria. For example, an HR officer may need to find the highest or lowest salary from a specific department, e.g. Sales, from the dataset below.

useful

The standard method for getting this result would be to filter the list on the specific criteria i.e. Sales and then select all the cells in the Salaries column and checking the status bar at the bottom of the screen for the highest or lowest salary. This does the job but is extremely manual and unproductive as similar methods would need to be employed for other departments. By using the MAXIFS and MINIFS functions instead, the results are all there in one place to do further analysis, e.g. using other formulas to create % bonuses for highest earners.

How to apply the MAXIFS and MINIFS functions

To calculate the Highest Salary per Department see below:

useful

2. Copy down the MAXIFS formula for the other cells to create highest salaries for all other departments

3. Repeat this formula for the Lowest Salary per Department but replace MAXIFS with MINIFS:

Type =MINIFS($E$2:$E$29, $D$2:$D$29, H2)

In this HR example you may need to apply more criteria to limit the range even further to find the maximum value, e.g. the highest salary per department for a specific status such as status 2:

Conclusion

The MAXIFS and MINIFS functions are extremely useful in monitoring high and low performance without the need for filtering and so improves efficiency and productivity.