Excel tricks from the daily life of a buyer – part 2 of 3
Status bar, Monitoring window, Recording macros
In the first blog post of this series Excel tricks we have already introduced you to our most popular shortcuts when working with Excel and gave you a tip on the topic of automatic saving in Microsoft Office. Today we would like to continue the series with the topics status bar, monitoring window and the recording of simple macros for taking over recurring commands.
You can find the status bar at the bottom of the program window. It shows you the result of frequently used formulas without using formulas in the spreadsheet itself. For example, if you have selected a range of cells, you can see the amount, sum and average value of these selected cells (with contents) in the status bar at a glance.
You can also customize the status bar display by right-clicking on it and selecting the desired options from the context menu.
When editing a worksheet, input in one area usually affects another. Now, it can happen that the work area is so large that you cannot see the changing area during input because it is at the end of a long table or even on another worksheet. Scrolling back and forth or changing worksheets to control the effects, however, leads to losses in the performance of the processing.
In this case, you have the option of setting up a monitoring window that hovers over the editing area. This gives you direct insight into the monitored area and the effects of your changes. To set up a monitoring window, select the „Monitoring Window“ function in the ribbon „Function“. The window can be resized and moved. It is also possible to monitor multiple cells simultaneously by setting up multiple monitors within the window.
You have not yet dealt with the function macros in Excel, because you think that these are complicated programs, keyword Visual Basic Applications (VBA)? Can, but does not have to. By using macros, recurring steps can be automated and can thus relieve you of work in daily life. Excel offers the possibility to record a sequence of defined commands, which later, when using a defined key combination, can be executed by the software independently as often as you like, without you having to make any clicks. Although the commands are synchronized in the background into VBA, no programming knowledge is required for the simple use of the recordings. A classic example is the uniform formatting of a new worksheet: Font, font size, margins, headers and footers and so on. The corresponding function in Excel for recording macros can be found in the ribbon „View“, „Macros“, „Record Macros“.
Please note the following remarks regarding the use of macro recordings:
- The use of recorded macros is only suitable for simple linear processes and cannot reproduce repetitions / branches in the process – this would require VBA knowledge.
- Especially at the beginning, it is recommended to create several smaller macro recordings than a long (and therefore possibly more error-prone) process.
- It is possible to choose whether a recorded macro should be available only in a certain workbook or in all Excel files.
- When selecting the key combination, make sure that it is not already being used by Excel, otherwise it will be overwritten. It is advisable to choose capital letters.
- Executed macros cannot be undone. When testing newly recorded macros, it is therefore recommended to save them in advance and, if errors occur in the macro, to close the workbook without saving again in order to undo the errors.
We would like to motivate you to test the use of Excel macros to make your daily work easier. In addition to the option already mentioned above to format workbooks automatically according to a certain pattern, there are a number of other possibilities: The automated transfer of data from one place to another, the insertion of buttons to jump between single worksheets, the setting or abandoning of sheet or cell protection, which can be labor-intensive, especially for files with many worksheets, and much more.
Read next time from our Excel tricks:
SUBTOTAL: The solution for formulas in filterable tables
XLOOKUP: Have VLOOKUP, HLOOKUP and INDEX/MATCH become obsolete?
Power Pivot: How to merge and compress large amounts of data
Consultant, ADCONIA GmbH