AZURA SPEED TOOLS (v3.0)
A suite of over 80 productivity tools and utilities that makes working with Excel easier, faster, and better. This set of tools were chosen from our collection of over 800 utilities that we have programmed to make our consultants and clients better at what they do. There is an endless array of features that could have been added, but this collection is a focused set of the best, and most used, time-savers that are continuously used by a wide array of our clients each and every day.
Want to use Azura Speed Tools on more than one computer? No problem! Individual licenses allow for installation on up to 2 devices (i.e. one desktop at work, and one portable laptop), and never expire. Do you need licenses for your entire team? Bulk discounts are available for 10+, 25+, and 50+ devices.
For a full list and preview of all the tools and utilities included with Azura Speed Tools, please refer to animated sections below or download a copy of the PDF documentation and user guide.
Animated Examples & Demonstrations (displaying v1.8 – animations for v3.0 are coming soon):
The following is a series of animated GIFs to provide demonstrations of all the utilities that are included within Azura Speed Tools. The order shown is the same sequence that these utilities appear on the Speed ribbon menu of Excel. Click on any image to view the animations at full size. Alternatively these can be saved to your computer for future reference.
As a supplement to the following animations you can view and/or download the PDF version of the user guide from this Dropbox folder.
Get File Info
The ‘Get File Info” utility is a tool that allows you to quickly retrieve the file name, and/or file path of the folder it is saved to. A pop-up userform is displayed to give you the control of what information you want to retrieve, and in what format you want it to be placed within a cell – text or formula. The benefit of having this information displayed as a formula, is whenever the filename or location that it is saved changes, the information displayed also updates.
Save a backup copy of file with a date/time stamp in the filename
Fast Backup is a utility that allows you to quickly (i.e., immediately) create a full backup copy of the Excel workbook you are currently working on. An exact copy is created and saved to the default folder that you have specified in the general Add-in settings. If no default folder for saving backups has been specified, the tool will prompt you to create one. Follow the prompts to select where you want to save your ‘Fast Backups’.
A beneficial aspect of this utility is that it applies a standardized naming convention to the backup that it creates. Each file is saved with a prefix containing the date and time that the file was saved.
Example: A file named ‘MyWorkbook.xlsx‘ will saved as ‘2018-01-25 at 14-29-53 MyWorkbook.xlsx‘ The date/time format is set as (yyyy-mm-dd) and (hh-mm-ss). This format prevents any duplicate files from being created and allows you to sort and quickly find backups based on when they were created.
Save New Version
Saves your file with an incremented version no. (i.e. v002) in the file name (much faster than using SaveAs)
This utility is a very simple tool that provides an important function along with time savings. It saves the workbook that you are using as a new version, and appends the filename with an incremented version number such as _v002, _v003, …
Versioned files are saved in the same folder as the current file.
This is especially useful if you are developing a file in incremental stages and want to retain a history of all the major steps you have made. Many professional developers are very diligent on creating a new version any time they make a significant change to a file that is difficult or impossible to reverse.
Multi-purpose numerical batch conversion tool
This tool is especially useful for anybody working with financial data and numerical data from multiple sources, where financial figures or units of measure need to be converted. A few examples include converting figures stated in ‘dollars’ to ‘thousands’, from ‘millions’ back into ‘dollars’, or dividing by 2.2 to convert from lbs to kg. It is also capable of switching the positive/negative signs of all values within a selection or removing formulas and leaving only numbers. This really is a multi-purpose tool that is used by the developers of Azura Speed Tools every single time they open Excel. It is the type of time-saving tool that once you start using it, you simply cannot do without.
Professional Tip: The ‘Batch Convert’ control panel will remain open until you close it. The userform will ‘float’ on the screen allowing you to continue using it without opening it from the ribbon after every conversion.
UPPER, lower, Proper
Convert text in selected cells to UPPERCASE, lowercase, and Proper Case text
If you are already familiar with the text functions UPPER(), LOWER(), and PROPER() then you will be familiar with what this utility does. The ‘UPPER’, ‘lower’, and ‘Proper’ utilities convert all of the text in the selected cells to uppercase, lowercase, or proper case. These utilities provide a fast and easy way to clean up text without using any formulas.
Scrub Alpha, Scrub Numbers
Removes letters or numbers from text in selected cells
Scrub Alpha and Scrub Numbers are designed to quickly remove alpha-numeric characters from selected cells. These utilities are commonly used by analysts to quickly cleanse data that contains both numbers and text in the same cells.
Transpose and paste only values (without any formatting, formulas, etc)
This utility allows you to save several clicks of PasteSpecial, by transposing a copied selection from the clipboard and pasting as values only. This is a great utility to save a lot of time when you need to quickly transpose something and don’t want formatting or formulas to be copied.
Convert to Values
Quickly copy & ‘PasteAsValues’ of all selected cells
This simple tool is nothing more than a speedy shortcut to Copy and PasteAsValues a selection of cells or ranges to remove all formulas while leaving only the values behind. The beauty of this tool is that you can select one cell, multiple randomly selected cells, or a large range. That is something you cannot easily do using standard Copy/PasteAsValues. There simply isn’t a faster or more convenient way to convert a selection of cells that contain formulas to only values.
Reverse the order of selected cells
This utility provides the ability to select an array of cells (single column, or single row), and quickly reverse their order. Everything in the cells, including all text, values, and formulas will be moved into a reverse order. It behaves exactly as if you cut/paste one cell at a time into a reverse order. This tool can be used for flipping both Horizontal or Vertical selections.
Removes all extra leading/trailing/duplicated spaces from all selected cells
This utility applies the TRIM() function to every cell in a selection to remove all extra leading and trailing spaces that often cause numerous problems in a spreadsheet. The behavior of this utility is exactly the same as the TRIM() function, where it also removes extra or repeated spaces between words, not only before or after the text in a cell.
Accounting Format and Number Format
Brackets and red font for negative values
This is a fast shortcut to apply custom formatting to financial values. This utility will apply accounting format, with brackets and red font for negative numbers. As well, it defaults to no decimal places to ‘hide the pennies’ from view, but if you prefer a different number of decimal places you can change the settings in the “Add-in Settings”. This is particularly useful for financial statements or other financial reporting where the default negative values in Excel are not obvious to the eye and saves a tremendous amount of time cleaning up the number formatting for financial statements and/or data. The accounting format button will include a dollar sign, and the number format button will omit the dollar sign.
Apply white borders to all cells within selection
This is a fast shortcut to apply white borders to your selection. Simply select a range of cells that you want white borders around, and then press the ‘White Borders’ button. This handy shortcut saves a tremendous amount of mouse clicking and time working with Excel’s default borders formatting dialog when all you want is some basic white borders to polish up the presentation value of your spreadsheet.
Batch find & replace cell fill and/or font colours
The ‘Replace Colours’ utility is a multi-purpose tool for quickly reformatting a large assortment of cells in a worksheet. This is specifically a design (or re-design) tool for when you want to make all those yellow cells with red font into blue cells with white font. To do this manually, especially if it is something you find yourself doing often, is extremely time consuming. PRO-TIP: Use this to quickly try different colour schemes and styles for the purposes of branding or prepping for presenting data.
Remove conditional formatting and delete values, but keep cell fill colours
This utility removes conditional formatting rules, formulas, and values from a range of cells, BUT, keeps all the colours by applying the heat map colours to the cells themselves. This is an excellent way to be able to communicate comparison trends and other observations without disclosing private or confidential numbers. In the example below, a sales manager wanted to brief his team about the upcoming publication of 2017 financials and poor sales performance but could not disclose the actual figures yet. As an alternative, she made a copy of the financial data, applied a conditional formatting heatmap, and then used ‘Flatten HeatMap’ to remove all the data but keep the relative ranking colours that would help visually communicate that 2017 was not good compared to past years. This also helps illustrate the general trend over time in a very effective visual.
Quick-format of a table to your preferred settings (as set in the settings menu)
With a Table selected in a worksheet, this utility will quickly format a table to match your preferences set in the Add-in settings. Cell alignments, text wrap, and a preferred colour theme can all be set to your liking. The default settings will apply the following:
- center text horizontally and vertically
- apply text-wrap
- auto-fit the row height
Data Body Range (all the cells in the table below the header):
- center text vertically
To make customized changes to match your preference, please refer to the settings control panel. There are numerous settings available for you to customize how your ‘Format Table’ utility will function. On the next page is a detailed view of all the settings currently available.
Wrap all formulas in selection with IFERROR() function
This loads a userform that allows you to quickly wrap IFERROR() around all the formulas found within a selection, as well as assign what text or values should appear if an error is returned when evaluated. This is very helpful in quickly applying a clean-up to a spreadsheet before presentations. To an analyst, seeing the errors communicates important information about what is being calculated, but to an audience they are distracting and can be misleading.
Wrap all formulas & values in selection with ROUND() function
This utility allows you to quickly wrap the ROUND() function around all formulas and/or numerical values found within a selection. A pop-up userform control allows you to set the number of decimal places to assign as the second argument of the function. This is especially helpful when there are a variety of formulas throughout a range that need to be rounded off for accounting or presentation purposes – often when you cannot write one formula and copy it down or across – so a batch conversion tool is the way to go!
Fill Down Col
Fill blanks down column with values
How often do you receive a copy/paste of a Pivot Table data set that has repeated labels omitted? For us, it is several times daily – each and every day. This utility allows you to quickly fill the values down columns, filling in the blank spaces, so the data can be re-Pivoted or referenced with formulas. (SUMIFS, INDEX/MATCH, etc).
PRO-TIP: This tool is excellent for transforming exported data into tabular data sets with repeating labels. We use it every time we receive exported QuickBooks data from our clients.
Copy Exact Formulas
Copy / Paste exact formulas
This utility lets you copy and paste cells containing formulas, and when pasted in a new range of cells the formulas will be exact duplicates. Relative, absolute, and structural references will be maintained.
Cond. Format Formulas
Conditionally format to identify and watch for formulas in a range of cells
This function assigns a conditional formatting rule for a selected range of cells that will alert you to the existence of formulas. It is a dynamic version of the default Find/Select tool that can be used to select all cells containing formulas. Where this is beneficial is when you need to actively monitor if certain cells are formulas or values. If a formula is entered in a cell, it will turn grey. If a formula is over-written with a value, the grey highlighting will disappear. VERY useful for also detecting when a value has been entered where a formula should be. (i.e., calculations won’t be updated if the formula is missing!)
Important Note: This tool will insert a named reference within the Name Manager of your file. When you close the file, if it is not already saved as a macro-enabled workbook, it will ask you to do so. This feature requires some options that are only available to macro enables workbooks with the extension “.xlsm”. Save your workbook as a “.xlsm” to have the conditional formatting rules applied when you open the file again later.
Cycle all absolute & relative references within formulas of selected cells
This tool allows you to select an entire range of cells and cycle the formula references within all cells containing formulas between the 4 stages of absolute and relative.
- Press the button once, A1 becomes $A$1.
- Press it again, and $A$1 becomes A$1.
- Press it again and A$1 becomes $A1.
- Press it one more time and $A1 becomes A1.
The main convenience is it converts all references within all formulas in the selected cells at the same time. Note: formulas that contain structured table references or references to other worksheets will not be edited, as they are not compatible with this tool.
PRO-TIP: The best way to familiarize yourself with this tool, is to create a few cells containing sample formulas and then apply this utility to those cells. It will make all formulas the exact same ‘stage’ of referencing, so be sure to select formulas in groups that should match the required type of referencing style.
All Visible, Cur.VeryHidden, All VeryHidden
All Visible: unhide all sheets
This tool quickly makes all the worksheets in the workbook visible. If any sheets were set to hidden, or “very hidden”, they will be made visible. If you have lots of hidden sheets that you want to unhide, it is very slow selecting one at a time and unhiding them. Why not batch-unhide all of them at the same time… that’s where the ‘All Visible’ utility becomes so valuable. This is also an excellent tool to see if there are some “very hidden” sheets in the workbook that you should be aware of.
Cur.VeryHidden: make the current sheet VERY hidden
This tool quickly makes the current sheet ‘Very Hidden’, meaning you need to use VBA to unhide it again. This is especially useful for keeping a control panel sheet safe from accidental corruption or edits by other people, or even yourself. For sheets that contain setup data, or specific information that should not be edited, it is common practice to give it a name such as “Control Panel” and set it to ‘Very Hidden’ for safe keeping. (More information about very hidden sheets can be found online by searching for “xlSheetVeryHidden property”)
Important Note: One caveat of setting a worksheet to ‘very hidden’, there needs to be at least ONE other sheet in the workbook that is visible, so when the current sheet is hidden there is a visible sheet that can take focus to become the ‘active’ sheet. The reason why is Excel requires every workbook to have at least one visible sheet at all times.
All VeryHidden: make all “Other” sheets VERY hidden
This tool quickly makes every non-active worksheet (or, “tab”) in the active workbook ‘very hidden’. The sheet that is visible when you press this button will be the ONLY sheet visible after running this utility. To unhide the sheets again, you will need to use the ‘All Visible’ utility or utilize VBA yourself via the VBE window that can be accessed by pressing ALT+F11.
This tool is excellent for simple but not secure “protection” of all sheets in a workbook except for the one that you want to show. Or, simply to de-clutter the tabs at the bottom of the workbook while you are working on a file with an excessive number of sheets.
Very Important Pro Tip: Be aware when setting sheets to ‘hidden’ or ‘very hidden’ – this does NOT provide any true protection from the information being accessed or seen. It is intended to only de-clutter the worksheet tabs, or to keep honest Excel users from tinkering with what they shouldn’t tinker with. Always remember that other users have the capability of using VBA or tools like these to examine ALL the information contained within an Excel file.
Batch insert multiple rows & columns within a range of selected cells
When you need to insert 2 rows, every other row, it is a very slow process. For many people, especially those that work with financial data and reporting, this batch Insert Row/Column utility provides the ability to apply multiple actions all at once. It is used by selecting a range of cells that represent the Rows/Columns that you want to apply the change to, then enter the numerical values of how many Rows/Columns you want to insert and at what intervals you want them to be inserted.
Misc Tools – Show Named References, Hide Named References
Make named references Visible/Invisible to the user (i.e. within the Name Manager)
Show Named References: Any names assigned in the Name Manager that have been hidden, will be made visible for the active workbook. This utility is to be used in conjunction with the ‘Hide Named References’ utility.
Hide Named References: All names assigned in the Name Manager will be hidden from the user’s view. This utility is useful for people developing files for others to use. If a named reference should not be edited, it can be better safeguarded by making it invisible.
Misc Tools – Delete Shapes
Delete ALL shapes including pictures, charts, labels, icons, etc
Deletes ALL shapes, charts, and objects from the active worksheet. This is an extremely fast way to remove ALL shape objects from a sheet. We personally use this utility often when we receive workbooks of equipment catalog items with 1000’s of images down Column A for every item in a table. We don’t want to keep the images, but it would take days to manually delete them one by one – so we developed this tool to do it in less than 0.25 seconds.
It is also useful for scrubbing all other objects from a worksheet – including charts, labels, shape objects, smart art, and other items Excel classifies as “Shape Objects”.
Misc Tools – Delete Hidden Rows
Find and delete hidden rows within a specified vertical range
This tool is used to find and delete all hidden rows within a range of rows below a selected cell. To use this utility, select any cell within a worksheet, and press the ‘Delete Hidden Rows’ button. A pop-up input box will appear asking to you specify how many rows below your selected cell should be scanned for hidden rows. If you enter a number like 100, the utility will look at every line below your selected cell to determine if that row is hidden or not. If it is hidden, it will be deleted.
This is very useful when ‘cleaning up’ spreadsheets that you want to first experiment with what rows to remove by first hiding them. Once you have the look that you want, use this utility to quickly find and delete all the hidden rows you want to be permanently deleted.
Pro Tip: If there are formulas in your worksheet that reference the hidden rows, it will be necessary to convert those to values before deleting the hidden rows. You can use our ‘Convert to Values’ tool to quickly and easily perform that task.
Misc Tools – Nuke Sheet
Complete RESET of a worksheet
The ‘Nuke Sheet’ utility is used to completely reset the selected worksheet, by deleting ALL objects on the sheet, deleting ALL cells entirely, and reverting row and column heights back to their default size. The result is a blank worksheet that appears the same as a new worksheet.
While many people simply delete the entire sheet and then create a new sheet to start fresh, this utility is more intended for advanced Excel users who are working with sheets that contain VBA code within the ‘sheet module’. Deleting the sheet would also remove the VBA code that may be required for other functions. It is troublesome and time consuming to create a new sheet, copy/paste the VBA code to the new sheet, update the sheet index numbers in the VBA code, etc. Nuke Sheet is also a useful portable utility that can be a procedure call when working with temporary sheets in a workbook – similar to having a temporary ‘scratch pad’ within a workbook that is used to place temporary data or information, then is wiped clean to be used again. However, it is also just as good for performing a quick reset of any sheet, regardless of what your purpose is.
Misc Tools – Random Numbers
Generate random numbers w/ and option to concatenate with text
This utility is for users that need to quickly insert some random numerical numbers for the purposes of filling in some sample data or testing the functionality of their Excel models. A pop-up box will allow you to set the minimum and maximum numbers to generate and provides you the option to append some additional text to the random numbers that will be inserted into the selected cells.
Example: You can insert random numbers between 0 and 1000, and append “ meters” (with a space before the letter ‘m’) to end up with text such as “824 meters” placed in a cell.
Misc Tools – Hyperlink to File
Uses the windows explorer dialog to select file or folder to hyperlink to
This tool allows you to utilize Windows Explorer to reference a file or folder on your computer when inserting a hyperlink into a cell. This is much easier and faster than finding the folder path, copying it to the clipboard, then right-clicking a cell to enter the hyperlink dialog to paste the file path text into. Lots of steps are removed, and this method also prevents many errors that can be encountered by using the manual method.
Misc Tools – Remove All Hyperlinks
Removes all hyperlinks from the active worksheet (restricted to only the active sheet)
This tool does exactly what the name implies – it removes ALL hyperlinks on the active worksheet. This function is great for quickly cleaning up lists of data that include randomly places hyperlinks that cause unwanted behavior or appearances. The removal of the hyperlinks is restricted to one worksheet at a time and will only be performed on the worksheet that is active at the time the utility is used.
Re-position & resize all comments on a sheet
Reset comments is a wonderful utility that repositions and resizes comments that have been skewed out of position or not sized properly in the first place. Over time, when rows and columns are grouped, hidden, unhidden, added and deleted, comments don’t get automatically repositioned and become unreadable. This is another candidate for one of the top basic functions that should be built directly into Excel, but isn’t. One click of a button and ALL COMMENTS on the sheet are automatically adjusted to fit their contents exactly, and appear directly beside the cell they are attached to.
Added benefit: This tool also respects carriage returns within the comment text. If in the text of the comment you press Enter to move to the next line, this utility will display the comment the way you intended. This makes it even more presentable by giving you the control of where the word-wrap should be positioned.
Export all comments in workbook to a list on a separate worksheet
Export Comments searches and catalogs all the comments that exist within a workbook. It finds comments on EVERY sheet, creates a new sheet named “Index of All Comments”, and creates a detailed list showing all information relating to the comments it found as well as the cells that they are attached to.
Added Benefit: As comments are added, updated, or deleted by people working in the workbook, a quick press of the ‘Export Comments’ button will update the index with a refreshed list of the comments that currently exist in the workbook. If the “Index of All Comments” sheet has been deleted, it will just create a new one for you, but will otherwise just refresh the list and show you the current information.
Toggle R1C1 (Row 1 Column 1 Referencing)
Shortcut to toggle between A1 and R1C1 style
To understand what R1C1 means, first you must be aware that there is another type of Row/Column referencing system hidden within Excel. R1C1 stands for “Row 1, Column 1”, and is used a cell referencing system that using only numbers, and not letters such as “A” for Column 1, “B” for Column 2, and so on. For beginner and intermediate users, the R1C1 toggle is a quick and easy way in case they need to know what a column letter translates to in numbers. i.e., Column E is the 5th column from the left margin of the spreadsheet.
R1C1 notation has many benefits for advanced users of Excel, when building ‘dynamic models’ and when writing named formulas that auto-adjust as cells get moved around. Advanced users in VBA also utilize R1C1 notation when writing formulas in VBA.
Settings menu to set user-preferences and defaults
This is where you can set your own preferences and default settings for many of the utilities contained within Azura Speed Tools.
- Fast Backup – set the file path to the folder that you want to save all the backup files you create when using the ‘Fast Backup’ utility. You can also check/uncheck an option to receive notification messages every time a backup file is successfully saved to the default directory.
- Save New Version – Option to check/uncheck an option to receive notification messages every time a new version of a file is successfully created and saved.
- Number Formats – set your preference for how many decimal places the ‘Acct. Format’ and ‘Num. Format’ utilities use as defaults. If no preference is set, the standard default is 0 decimal places.
- Format Table – set all your preferred defaults for formatting Excel Tables. These are the settings that the ‘Format Table’ utility will reference to quickly format an Excel table to match your preferred formatting rules.
- Diagnostics – control if you want the add-in to be recording diagnostics data (a screenshot of the data it collects is provided below). If technical difficulties are encountered, this feature provides the ability to export a data log that can be provided to aid in troubleshooting. If this is left unchecked, the add-in records no activity data. Upon exporting a data log, you will see all the data captured and be able to decide if you want to share that data with somebody for troubleshooting purposes.
- Reset All Settings to Defaults – this clears all your preferences and restores the add-in’s default settings. Note: this will not have any effect on licensing, it only removes any changes you have made to the preferences shown within this control panel.
Information and licensing menu
This is where you can access information about Azura Speed Tools such as the version you have installed, licensing and activation/deactivation information, and additional links to where you can find more information from the developers at Azura Consulting. Please note that an internet connection is required for activating, deactivating (releasing), and checking current statuses of licensing.
We hope you love this suite of productivity tools and utilities. And as the saying goes, if you love it please tell all your friends. If you don’t love it, please tell us so we can make it better.