EXCEL
MACROS
FOR MAC
Range Macros
One Utility, +30 Handy Options
Revolutionize your Excel workflow with Range Macros Utility.
No more building formulas or performing time-consuming multi-step operations. With Range Macros, simply select the action you need to make and let the tool do the work for you.
Accelerate your Excel workflow with Range Macros, featuring over 30 useful utilities for working with text, numbers, dates, data, charts, and more. With Range Macros, you can streamline your work, saving time and producing cleaner, error-free results.
Range Macros Utilities
4. Formula Macros
5. Range Macros
1. Find & Select All
Improved Find & Select for Excel on your Mac.
Select Cells with
Same Background Color:
Find & Select all cells with a specific background color.
As a default, the format of the selected cell is offered. Before running this utility, a range of cells must be selected.
Select Cells with
Same Font Color
Use this utility to find and select all cells with specific font color.
The selected cell determines the default formatting. To use this utility, first select the range of cells that contain the font color you want to Find & Select
Select Cells with
Same Font Properties
Find & Select all cells with Same Font Properties besides color.
The selected cell's format is shown by default. You need to first select a range of cells before running this utility.
Improved
Find All
The 'Find All' utility has been enhanced for all Macs! You can find what you're looking for by pressing Control + Shift + F. Formulas and values can be retrieved quickly throughout the entire workbook. This function can be used to find case-sensitive content, entire cell contents, or the whole word. An index containing the entire found set will be created.
Find Value &
Select Cells in Sheet
This functionality finds all occurrences of text in the excel sheet and then selects the found set separately. It can also match content in formula cells or values. This feature works for case-sensitive words, with the whole content of the cell or with the entire word. Applies only to the current sheet.
Select Cells with
Same Number Format
Select cells with the same numerical format: This Utility allows you to locate and select all cells with the same numerical format.
The selected cell format is given by default.
A selected range of cells must be selected before running this utility.
2. Text Macros Utilities
Clean your non-numeric data with ease by selecting a range in Excel. The Range Macros tools allows you to quickly remove unwanted characters, leading or trailing spaces, and other non-numeric elements from your data.
Clean
Text
Trim text in selected cells without using formulas. Removes leading spaces, trailing spaces, extra spaces and non-printing characters. No inputs are required to run this macro.
Change
Case
Change the case of text in selected cells without using formulas: UPPER CASE, lower case, Proper Case, Sentence case. Use the dialog box to pick your choice.
Remove a Character
from Selection
Remove any character or string from selected text. Including special characters like "*". Use the dialog box to type the substring to remove.
Remove Accents
Remove accents and diacritical marks from selected text. Accented characters are replaced by its Unicode version (e.g. áéíóú is changed to aeiou). No inputs are required to run this macro.
Extract Text from Cells
Extract a text string from left, right or middle of Cell. Use the form to specify the position and number of characters to extract. You can extract text using several rules at once.
Add Text to Cell Values:
Add text to selection. Use the form to specify the text to add and its start position. You can add text using several rules at once.
Text Macros Functions
STRIPDIACRITICS FUNCTION:
-
A new function that removes accents and diacritical marks from text, returning an unaccented version of the character." Parameters can be typed using Excel’s “Formula Builder”
PLEASE NOTE:
-
This Macro will not retain individual character formatting in selected cells. Formatting applied to the entire cell is preserved.
-
This Macro will not work on a protected sheet.
HOW TO USE
-
A cell range must be selected before running this utility.
-
Text Macros are available via the 101 Macros Menu on the “Home Tab” or as a contextual menu when right-clicking on a range.
-
Text Macros will only work in cell ranges. Blank cells and cells with formulas are ignored.
-
You can select large ranges, including entire rows or columns.
-
You can re-do this Macro with shortcut CTRL-OPT-Y.
3. Number & Date Macros Utilities
Simplifies and enhances your data cleaning process. Select a range of numerical data and apply formatting functions to make it consistent and easier to analyze.
Convert Text Numbers
to Numbers
Convert numbers stored as text into numbers. No inputs are required to run this macro.
Convert Numbers
to Text:
Store numbers as text numbers by adding a quote (') at the beginning. No inputs are required to run this macro.
Convert 0.000,00
to 0,000.00
Change decimal sign from "," to "." List separators are changed accordingly. No inputs are required to run this macro.
Convert 0,000.00
to 0.000,00
Change decimal sign from "." to "," List separators are changed accordingly. No inputs are required to run this macro.
Fix Negative
Signs
Fix most common problems with imported negative numbers. You will be presented with 4 options:
-
Fix Trailing Negative Signs: "1-" is converted to -1
-
Change Sign: Number multiplied by -1
-
Make All Numbers Positive: abs(number)
-
Make All Numbers Negative: abs(number) * -1
Convert Date
(dd-mm mm-dd)
Extract a text string from left, right or middle of Cell. Use the form to specify the position and number of characters to extract. You can extract text using several rules at once.
Remove Time from
Date and Time
Returns date from a date and time value: trunc(date). No inputs are required to run this macro.
Remove Date from
Date and Time
Fix most common problems with imported negative numbers. You will be presented with 4 options:
-
Fix Trailing Negative Signs: "1-" is converted to -1
-
Change Sign: Number multiplied by -1
-
Make All Numbers Positive: abs(number)
-
Make All Numbers Negative: abs(number) * -1
Number & Date Functions
CHECK WRITER Functions Parameters:
Parameters can be typed using Excel’s “Formula Builder”:
-
SPELLNUMBER: Spell number in English.
-
SPELLNUMBER_ESP: Spell number in English.
-
SPELLNUMBER_FRA: Spell number in English.
-
SPELLNUMBER_POR: Spell number in English.
SPELLNUMBER Functions Parameters
-
Number: Number to spell.
-
Coin: Currency Name (optional). Defaults: [EN} = Dollar, [ESP] = Peso, [POR] = Real, [FRA] = Euro.
-
Coin_plural: Currency Name in Plural (optional). If left empty, will add plural assuming it’s a regular noun.
-
Spell_cents: Optional. Cents are spelled only if True. Default = True.
-
CoinCent: Name for currency hundredths (optional). Default: [EN] = Cent; [ESP] = Centavo; [POR] = Centavo; [FRA] = Cent.
-
CoinCent_Plural = Name for currency hundredths in Plural (optional). If left empty, will add plural assuming it's a regular noun.
-
Prefix: Optional. Type any text to go at the beginning. Default = Empty.
-
Suffix: Optional. Type any text to go at the end.
Default = Empty." -
Text case. Optional. 1= UPPERCASE; 2 = lowercase; 3 = Proper Case; 4 = Sentence case. Default = Proper Case."
ONLY ON MAC:
-
You can also spell numbers on any language installed on your machine with the Spell Number utilities.
HOW TO USE:
-
A cell range must be selected before running this utility.
-
Number Macros are available via the 101 Macros Menu on the “Home Tab” or as a contextual menu when right-clicking on a range.
-
Number Macros will only work in cell ranges. Blank cells and cells with formulas are ignored.
-
You can select large ranges, including entire rows or columns.
-
You can re-do this Macro with shortcut CTRL-OPT-Y.
PLEASE NOTE:
-
This Macro will not retain individual character formatting in selected cells. Formatting applied to the entire cell is preserved.
-
This Macro will not work on a protected sheet.
4. Formula Macros Utilities
Edit your formulas and names fast.
Paste - Exact
Formulas
Paste formulas without changing cell references. Original formulas come from the selected range. Use the dialog box to provide the first cell of the destination range. Multiple selections are not allowed.
Change Formula References Absolute/Relative
Changes formula reference
to any choice of:
-
Relative Row/Absolute column: = $A1
-
Absolute row/Relative column: = A$1
-
Absolute all: = $A$1
-
Relative all: = A1
Convert All Formulas
into Values
Use Shift + Control + V to instantly convert a range into values.
List & Manage
All Names
List all named ranges and table names in Name Manager.
Use the resultant form to easily:
-
Delete invisible names: Delete all hidden names in the currently open Excel file.
-
Apply names on every worksheet: Select a name and apply it everywhere.
-
Delete names and fix dependent formulas: Select a name and delete it. Formulas in dependent cells will be fixed.
-
Edit any name: Select a name and do a quick edit.
Apply Selected
Name
Select a name and apply it everywhere.
All cell references will be replaced by the named range on every sheet.
This utility can also be run from the
Name Manager.
Delete Selected Name
Range and Fix Formulas
Select a name and delete it. Formulas in dependent cells will be fixed. This utility can also be run from the Name Manager.
Count/ Highlight All Cells
with Errors in Range
This utility will count error cells and
highlight them.
How to use Formula Macro Utilities
-
A cell range must be selected before running this utility.
-
Formula Macros are available via the 101 Macros Menu on the “Home Tab” or as a contextual menu when right-clicking on a range. For added convenience, a button is also provided on the “Formulas Tab”.
-
A Name Manager is also provided for easy access to our formula macros.
-
Formula Macros will only work in cell ranges. Blank cells and cells with constants are ignored.
-
You can select large ranges, including entire rows or columns.
-
You can re-do this Macro with the shortcut CTRL-OPT-Y.
PLEASE NOTE:
-
This Macro will not work on a protected sheet.
-
Names starting with _xl (used for Excel formula compatibility) are excluded from listings.
5. Edit Range Macros Utilities
Clean, format, and compare ranges.
Combine Cell
Contents Up
Combine Cell Contents using a delimiter. Text is joined in the uppermost cell.
You can select a range with more than one column at once and the utility will repeat itself on each.
Only single selection areas are allowed. Use the dialog box to pick a delimiter. Your choice of delimiter will be remembered until Excel is closed
Combine Cell
Contents Left:
Combine Cell Contents using a delimiter. Text is joined in the leftmost cell. You can select a range with more than one row at once and the utility will repeat itself on each. Only single selection areas are allowed. Use the dialog box to pick a delimiter. Your choice of delimiter will be remembered until Excel is closed.
Replace Blank Cells
with Zeros:
Fill with zeros all empty cells in a range.
Unmerge and Center
Across Selection
Convert merged cells in selection to "Center Across Selection". Only previously merged cells are centered.
Highlight Alternate
Rows in Range
Format simple ranges as if they were Excel Tables. A dialog will be presented to pick highlight colors.
Multiple selections are not allowed.
Change Border Color/Thickness
in Range
Change Border Colors without affecting thickness/styles. Only existing borders are modified.
Delete Blank Rows / Columns
from Selection
Delete the entire row/column within the selection if the ENTIRE row contains no data. Multiple selections are not allowed.
How to use Edit Range Utilities
-
A cell range must be selected before running this utility.
-
Range Macros are available via the 101 Macros Menu on the “Home Tab” or as a contextual menu when right-clicking on a range.
-
Range Macros will only work in cell ranges. Other objects are ignored.
-
You can select large ranges, including entire rows or columns.
-
You can re-do this Macro with shortcut CTRL-OPT-Y.
NO UNDO IS AVAILABLE
-
If Autosave is turned off, a message to save the file will appear before the utility is run.
-
The message will not show again during the session if “Do not save" is selected.
PLEASE NOTE:
-
This Macro will not work on a protected sheet.
6. Comments Macros Utilities
With 101 Macro Comments Utilities, you can now easily manipulate notes and create new threaded comments - making your conversations smarter and more engaging than ever before.
List All Comments & Notes
in Workbook
This feature allows you to create a clickable index of all comments and notes, including the author and text, and tells you where those notes are located in the worksheet.
Select Cells with
Comments & Notes in Range
This utility allows you to select and organize all notes and new threads' comments quickly in one place for you to find them easily.
Delete Comments & Notes
in Range
Notes and new comment threads are both deleted. This function only applies to the range you've selected. If autosave is turned off, an alert to save the file will show up before the utility runs.
Add Cell Contents
to Comment
Use this function when you need to add the cell text to a new threaded comment. If there is a pre-existing note, this function will remove the note and a new comment will be created in the thread. If a linked comment already exists, the text will be added as a reply to the comment. If you have autosave disabled, an alert message will appear, prompting you to save the file before the utility is run.
Add Cell Contents
to Note:
You can use this function to add the text from a cell to a new note. If there is already a Note or Comment in the thread, the new note will replace it. If autosave is turned off, a message will pop up before the utility is run to save the file.
Reset Position of
Notes in Selection
Use this utility to keep the notes in your spreadsheet visible. When you run the function, the notes will be moved close to the top right corner of the cell.
Reset Size and Position
of Notes in Selection
If you need to resize and readjust the notes in the selection, this utility will
autofit the notes and move them to the top right corner of the cell.
The notes will be sized to be between
100 and 300 points wide.
How to use Comments Macros
Comment Macros are available via:
-
Home tab menu > 101 Range Macros dropdown > Comments
-
Review tab menu > Comments dropdown
-
As a contextual menu when right-clicking on a range.
-
You can select large ranges, including entire rows or columns.
-
You can re-do this Macro with the shortcut CTRL-OPT-Y.
PLEASE NOTE:
-
You can select large ranges, including entire rows or columns.
-
You can RE-DO this action with the shortcut CTRL+CMD+Y
-
This Macro will not work on a protected sheet.
1. Home tab menu > 101 Range Macros dropdown > Comments
2. Review tab menu > Comments dropdown
