While managing workbooks in Excel, you may have found some common tasks that are repetitive. You can simplify the methods of performing these tasks in Excel.
As an experienced Excel user, there may be times when you need to automate frequently performed tasks, restrict the type of data entered in cells, or format data based on predefined criteria. Streamlining your workflow by tailoring the Excel environment to your job needs can increase your productivity and improve your efficiency.
- Conditional Formatting
- Conditional Formats
- The Record Macro Dialog
- Macro Naming Rules
- Macro Referencing
This allows you to quickly identify specific information that meets a given criteria to make it easier to identify and differentiate between the data within it.
Definition: Conditional formatting is a formatting technique that applies a specified format to a cell or range of cells based upon a set of predefined criteria. In Excel, the cells to be formatted can contain numeric or textual data. The condition for formatting can be set using default or user-defined rules.
Excel provides different types of conditional formats that can be applied using the Conditional Formatting option in the Styles group on the Home tab.
Highlight Cell Rules
Quickly find specific cells within a range of cells. You can format those cells based on a comparison operator. This format is applied by selecting the desired option displayed in the Highlight Cell Rules submenu.
Find the highest and lowest value in a range of cells based on a cut-off value you specify. This format is applied by selecting the desired option in the Top/Bottom Rules submenu.
View the value of a cell relative to other cells. The length of the data bar represents the value in the cell. This format is applied by selecting a data bar format from the Data Bars gallery. A data bar can be customised if required.
Visually represent data distribution and variation. The shade of the colour in this format represents higher, middle, or lower values. This format is applied by selecting a colour scale format from the Colour Scales gallery. A colour scale can be customised if required.
Annotate and classify data into three or five categories. Each category is represented by an icon. This format is applied by selecting an icon set type from the Icon Sets gallery. An icon set can be customised if required.
Macros automate complex tasks and ensure their precise replication.
Definition: A macro is a task automation tool that executes a set of commands to automate a series of frequently used steps. Each macro is uniquely identified by a name. A macro-enabled Excel workbook has .xlsm as the file extension.
You can use the macro recorder to record a sequence of actions, and then perform the tasks by using the macro name or a simple command assigned to the macro. The set of commands in the recorded macro is converted into a Visual Basic programming code that can be edited if required. Macros can be stored in the Personal Macro Workbook, a new workbook, or in the current workbook. Macros that are stored in the Personal Macro Workbook can be used in any workbook.
The Record Macro Dialog Box
The Record Macro dialog box is used to specify details about a macro and to start recording the macro. You can specify details such as the macro name, shortcut key for running the macro, location in which the macro will be stored, and description of the macro in the Record Macro dialog box. The Record Macro dialog box can be displayed from the Macros drop-down list of the View tab.
Macro Naming Rules
There are certain rules to follow when you create macro names:
- The name must begin with a letter.
- The name must not contain spaces.
- The name can contain letters, numbers, and the underscore character.
- The name should not be in conflict with the name of another object in the worksheet or workbook, or any built-in name.
There are two types of referencing used in macros: absolute and relative. In absolute referencing, actions are recorded by taking the absolute position of cells. There, the macro will perform the actions in the same cell positions, irrespective of the position of the cell pointer.
In relative referencing, actions are recorded relative to cell positions. For instance, if you record a macro that moves the cursor to cell A5, with cell A 1 selected, then if you enter some text, absolute reference will insert the text in cell A5, irrespective of the selected cell. Relative referencing, on the other hand, would place the text in the fourth cell to the right in the same row. By default, macros are recorded using absolute referencing. To use relative referencing, you need to choose the option Use Relative References from the Macros drop-down list before recording a macro.