OfficeHelp - Office Tutorials & Software Solutions
Search:
 
Advanced Search

 
Get FREE PC TIPS every month:

More Information Here

Most Popular

What's available for:
Calendars
Privacy & Security
Excel
Outlook
Word
PowerPoint
Internet Explorer
Others


Traditional Calendars for Excel

Easily generate annual and monthly Traditional & Planning calendars you can print and distribute, based only in Microsoft® Excel®. Five different formats available. Use special colors for special days or events, like holidays or meetings. Automatic event legend. Configurable background and font colors. Optional calendar background image. Traditional, Academic and Fiscal years. Imports data from Microsoft ® Outlook®.


Other Contents
Design Chart Templates for Microsoft® Excel®
Automatically update hundreds of PowerPoint slides from Excel in minutes
Quickly and easily generate your own Org Charts in Excel
Calendar Templates with Holidays (USA / UK / Australia / Canada) for Excel
Make stylish Planner Calendars - CUSTOM Version
All CALENDARS at once!
Calendar Templates 2007 with Holidays for Excel
Make stylish Planner Calendars - ADVANCED Version
All TEMPLATES at once!
Make stylish Planner Calendars
Make stylish Planner Calendars - BASIC Version
Calendar Plan - Year Planner Template
How long the wait?

How to Buy
How safe it is to buy
Privacy Policy
Contact Us

RSS Feed OfficeHelp.Biz RSS Feed
Home Calendars Bundles Macros PC Tips Templates Newsletter Contact Us

Specializing in Advanced Spreadsheet & Office Macro Solutions.


Content Type Application Author Price
Macro Microsoft Excel OfficeHelp Free

ExMiniTools - Utilities Toolbar for Microsoft® Excel® - Version 2.00

Tested on Microsoft Excel Versions: 2000 (9.0), XP (10.0), 2003 (11.0), 2007 (12.0), 2010 (14.0), 2013, 2016 - should work on any version from Office 97


 

Additional Functions & 1 Click Buttons for Excel® with AUTOMATIC SORTING (data changed? just recalculate to sort), EXACT COPY/PASTE (no formula changes), COMPARE FORMULAS in 2 ranges, COUNT UNIQUE values in ranges, RANK numbers in a range (is it the 1st or 4th?), RESTORE ADDIN FUNCTIONS (automatically remove file path from functions), apply ALTERNATE BACKGROUNDS (Row/Column) for easier table reading, automatically REMOVE #ERRORS and 1 Click access to 9 common features.

Contact Us for Support Main Page FREE DEMO

MANUAL - Toolbar Buttons & Worksheet Functions Reference

Toolbar Buttons


Index

Download FREE Demo

How-To

Copy/Paste  Exact Formulas

Copy/Paste a range of cells without Excel adjusting the cell references in formulas within it. Follow these steps:

1) Select a contiguous range of cells and click on the Memorizes Selected Cells (Toolbar Buttons) Toolbar button.

  • Do not use Excel regular COPY. You need to use this specific toolbar button.

2) Select the Top Left cell on the intended Paste range. Click on the Paste Exact Formulas (Toolbar Buttons) Toolbar button to paste the exact formulas of the original range.

3) The formulas on the new "pasted" range are EXACTLY as the original one. No adjustments will be done the the formulas and cell references.

Copy Exact Formulas

This feature can be tested with our FREE demo. Click here to download.


Create an Auto-Sorted Table

This innovative feature is intended to provide auto-sorted tables in Excel, really useful to create rankings and Rank charts:

Create Rankings (Top Sellers)
Click to Enlarge
Actual to Budget variations
Click to Enlarge

While you can manually sort ranges in Excel, this is a manual and tiresome process, especially if it has to be done regularly (daily, weekly or monthly reports) and on several tables. It would be much easier if you could just update the data and recalculate to reorder the table!

Instead of sorting the original table, we create a new one (linked to the original) that will always sort itself when recalculated: (Row and Column headings shown):

Original Data Table
Sorted Table

On the sorted table, we need a column to indicate each row order number (A) and a cell to indicate the sort order (TRUE = Ascending, FALSE = Descending). For each cell, enter a call to the TopN()  function:

=TopN($C$14:$C$23; A34; "C";$H$8)

The exact syntax of this function is:

= TopN (nRange, TopNVal, ReturnCol, SortType)

  • nRange: Range of cells for the Ranking. Can have only ONE column.
  • TopNVal: Index number in Ranking to be returned (1 for first, 2 for second,…)
  • ReturnCol: Column number were to fetch the return value
  • SortType: Sort Order for the cell values:
    • TRUE: ASCENDING
    • FALSE: Descending

This function will return the Nth member of the original table, sorted by the indicated nRange. So, to retrieve the 3rd largest region in 2007, we need to define the function as:

  • nRange: $C$3:$C$$12 (values for 2007)
  • TopNVal: 3 (as a number, for 3rd) or a reference to the order column on the sorted table (A5)
  • ReturnCol: "B" to return the region name or "C" to return the region value.
  • SortType:  TRUE (ascending)

So to create the Sorted table we use the the same function (as above) and only change the ReturnCol parameter in order to retrieve the region labels and value.

It is also possible to return secondary values based on another column sort order (like returning variances to Budget and Last Year based on the “variance to Budget” sort order – the variances to Last Year will not be retrieved sorted by itself by sorted by the “variance to Budget” values). Creating the new column is as easy as copying one of the existing ones (either labels or values) and changing the ReturnCol parameter (the column to be returned).

In the example above, a 3rd column could be added to the Sorted Table to indicate the variation to 2006, keeping it sorted by 2007 values. The only change would be, again the ReturnCol parameter, that in this case would have to be set to "E", retrieving the Variation column of the Original Data Table. The final Sorted Table would use these formulas on the first row (3):

  • Column "B" - Label  (sorted by 2007 values):                          =TopN($C$3:$C$$12; A5; "B" TRUE)
  • Column "C" - 2007 Values (sort column):                                =TopN($C$3:$C$$12; A5; "C" TRUE)
  • Column "D" - Variation to 2006 (sorted by 2007 values):            =TopN($C$3:$C$$12; A5; "E" TRUE)

These formulas (for the first row), could then be dragged down until the 10 rows were filled.

This feature can be tested with our FREE demo. Click here to download.


Compare Formulas in 2 Ranges

Compare the formulas within 2 cell ranges. Follow these steps:

1) Select a contiguous range of cells and click on the Memorizes Selected Cells (Toolbar Buttons) Toolbar button.

  • Do not use Excel regular COPY. You need to use this specific toolbar button.

2) Select the Top Left cell on the intended comparison range. Click on the Formula Comparison (Toolbar Buttons) Toolbar button.

3) Each cell on the comparison range will be compared with its similar cell on the original range. Cells will be painted according to the comparison results:

  • YELLOW: Same value as reference cell, but formula is different
  • RED: Different value and formula compared to reference
  • NONE: No difference to reference (same value and formula)

Compare Formulas in 2 Ranges

This feature can be tested with our FREE demo. Click here to download.


Apply Backgrounds to Ranges / Tables

Apply alternate backgrounds (Row/Column) in a single click - Much easier to read, specially in print. Just Select & Apply

1) Format cell A1 (on the active worksheet) with the desired background color. This cell will be the reference

2) Apply the desired background pattern. Cell A1 background format will be applied to the selected range:

  • Alternate Rows (Toolbar Buttons) - Applies a background color to alternate rows of the current selection.
  • Alternate Columns (Toolbar Buttons) - Applies a background color to alternate columns of the current selection.
  • Chess Pattern (Toolbar Buttons) - Applies a background color to alternate cells of the current selection, creating a “Chess Pattern”.

3) The selected range will be formatted with the desired pattern (example: Alternate Rows pattern):

Click to Enlarge

This feature can be tested with our FREE demo. Click here to download.


Automatically repair broken function links from AddIns

Excel treats external functions residing in AddIns as links. Therefore, when the file using them is saved, it will add the full path the AddIn to the function call in the cells:

Entered in File: =topn($C$14:$C$23; A32; "C";$H$8)

After Saving: =C:\Program Files\AddIn\ThisAddIn.xla!topn($C$14:$C$23; A32; "C";$H$8)

This is a real problem if the file is later opened in a different computer where the AddIn resides in a different disk location, as Excel will consider the link broken and will not calculate the AddIn functions until the disk path is removed. This can be very frustrating as some files may have thousands of function calls needing cleaning. This is a common scenario for corporate users using Excel Addins to retrieve data from their corporate databases and Business Intelligence or Financial applications.

To use this feature, just open the file needing the restore and click the Toolbar Buttons button. The Toolbar will check ALL cells with functions on the active workbook and restore the AddIn function calls in all cells needing it. There is no need to select ranges or sheets, it will apply to the whole active workbook. Once done, just save the file to disk.

This feature can be tested with our FREE demo. Click here to download.


Remove all #Errors from a Range

This feature is intended to clean tables of error cells (like #Div/0) before copying to Word or printing. Cells with errors (shown starting with a # in Excel) will be ERASED permanently, because this errors may cause other cells (like SUMs) to also show errors instead of calculating the remaining values. It is therefore advisable to use it on a copy of the original sheet.

Errors

To use it select an range of cells with the mouse and click on the Toolbar Buttons button. The toolbar will check each cell in the selected range and, if it contains an Excel calculation error, it will be erased. The final range will only have values and empty cells, resulting in a “clean” table for printing purposes.

This feature can be tested with our FREE demo. Click here to download.


1 Click access to Excel commands

SAVE TIME with direct, 1 click access to the "hidden" Excel commands. This buttons will invoke the regular Excel commands performing the same functions.

  • Calculate
    • Calculate Active Sheet (Toolbar Buttons)
    • Calculate Selected Range (Toolbar Buttons)
    • Calculate Active Workbook only  (F9 will calculate all open workbooks) (Toolbar Buttons)
    • Manual / Automatic Calculation mode Toggle (Toolbar Buttons)
  • Paste
    • Paste Values (Toolbar Buttons)
    • Paste Formulas (Toolbar Buttons)
    • Paste Formats (Toolbar Buttons)
This feature can be tested with our FREE demo. Click here to download.

 

Install an Excel AddIn (not necessary for the DEMO, only for the full product)

An Excel AddIn is an extension to Excel and, once installed, will load every time Excel is loaded, becoming “part” of Excel until it is deliberately uninstalled. Learn how to install and manage them on all versions of Excel from 97 to 2007.

  • The free DEMO for this product is intended for testing purposes only and is not an AddIn but a plain Excel file. Just open it with macros enabled and the new Toolbar withh be added to Excel for immediate use.
     
  • The full product is an Excel AddIn and can be installed to be loaded with Excel every time it starts.

Click here for an illustrated article on How to Install Excel AddIns.


TOOLBAR BUTTONS
 
WORKSHEET FUNCTIONS
 
Calculation Group
Toolbar Buttons Calculates Active Sheet (Only)
Calculates the active sheet. Useful to force its calculation when in MANUAL calculation mode. Similar to the "Calc Sheet" button in the Tools / Options menu.
 
Toolbar Buttons Calculates Selected Cells (Only)
Calculates the current selection. Useful to force its calculation when in MANUAL calculation mode. Not available on Excel(R) menus.
 
Toolbar Buttons Calculates All Sheets from Current Workbook
Calculates the current workbook by looping all worksheets twice.
Not available on Excel(R) menus.
 
Toolbar Buttons Toggles Calculation between MANUAL and AUTOMATIC
When clicked, it will change the calculation mode.
If in MANUAL, it will change to AUTOMATIC.
If in AUTOMATIC, it will change to MANUAL
 
Ranges Group
Toolbar Buttons Memorizes Selected Cells
Memorizes the formulas (not values) of all cells on the current selection. For use with the other buttons within this group.
 
Toolbar Buttons Formula Comparison
Compares formulas between the memorized area (button above) and the area starting on the current selected cell. Cells with differences are painted red (background color).
 
Toolbar Buttons Paste Exact Formulas
Works like Paste Formulas but makes NO adjustments to the copied formulas. Pastes the memorized cells to the Range starting with the current selected cell.
WARNING: The original cells have to be MEMORIZED, not COPIED with Copy.
 
Paste Group
Toolbar Buttons Paste Values
Shortcut to the Paste Values option on the Paste Special pop-up. Pastes only the Values of the current Copy source (need a previous Copy).
 
Toolbar Buttons Paste Formulas
Shortcut to the Paste Formulas option on the Paste Special pop-up. Pastes only the Formulas of the current Copy source (need a previous Copy). Relative formulas will be adjusted (cell references change) as usually in Excel.
For an Exact Formula paste (not available in standard Excel), see Paste Exact Formulas above.
 
Toolbar Buttons Paste Formats
Shortcut to the Paste Formats option on the Paste Special pop-up. Pastes only the Formats of the current Copy source (need a previous Copy), applying the source formats to the target cells without changing their formulas.
 
Grid Group
Toolbar Buttons Alternate Rows
Applies a background color to alternate rows of the current selection. Useful to make easier reading of large tables, especially on prints. Uses cell A1 background as reference (copies its background).
 
Toolbar Buttons Alternate Columns
Applies a background color to alternate columns of the current selection. Useful to make easier reading of large tables, especially on prints. Uses cell A1 background as reference (copies its background).
 
Toolbar Buttons Chess Pattern
Applies a background color to alternate cells of the current selection, creating a “Chess Pattern”. Useful to make easier reading of large tables, especially on prints. Uses cell A1 background as reference (copies its background).
 
Other Buttons
Toolbar Buttons Remove disk path to restore AddIn formulas
Checks ALL cells with formulas in ALL sheets of the current workbook and removes disk path links to external formulas (like the ones in this Toolbar). Restores formulas with external AddIn functions allowing them to work again. Excel adds a full disk path to AddIn files when you use formulas in external AddIns, like the one in this Toolbar but also many others, including Microsoft own and the ones from most Enterprise software vendors. This can be highly annoying when someone else opens the file and the AddIn is located in a different location, as the functions WILL NOT WORK.
 
Toolbar Buttons Remove # Errors for Illustration Purposes
Erases formulas with # errors within cells on the current SELECTION. This is definitive so it should be used with care. Very useful when you need to copy large tables for illustration purposes on Word or PowerPoint.
 
Toolbar Buttons About
Open a pop-up windows with additional information about this product. Include links to several related pages on OfficeHelp.Biz website, including Help/Manual, Commercial information and Upgrade/Purchase pages.
 

 

Automatic Sorting & Ranking

IndexInRankingNum (nRange, SearchVal, SortType)

Returns the Index of a given value in a sorted table (Ranking Index)
Sort is numeric (9 comes before 10) and therefore both the range and Search values must be numeric (text is not supported).

  • nRange: Range of cells for the Ranking. Each cell will be ranked by value
  • SearchVal: Number whose order (index) in the Ranking is being searched
  • SortType (TRUE/FALSE): Sort Order for the Ranking:
    • TRUE: Ascending
    • FALSE: Descending

IndexInRankingTxt (nRange, SearchVal, SortType)

As above but for text values. Orders are performed as text, not numeric values, so 10 comes before 9 because it starts with a 1.


TopN (nRange, TopNVal, ReturnCol, SortType)

Returns the Nth value of a ranking (ordered) Range. The Range has to be single column. Can return any column (even outside of the Range), like Label or Value.

  • nRange: Range of cells for the Ranking. Can have only ONE column.
  • TopNVal: Index number in Ranking (1 for first, 2 for second,…)
  • ReturnCol: Column number were to fetch the return value
  • SortType: Sort Order for the cell values:
    • TRUE: ASCENDING
    • FALSE: Descending

CountUnique (nRange)

Returns the number of different unique values in a given range. Like Excel standard Count() function but EXCLUDING repeats.

  • nRange: Range of cells for the Ranking. Each cell will be ranked by value

Click here for a FREE DEMO for the Sheet Functions


Date Functions

GetEasterDate (Year)

Returns the date of the Easter Day for the given year.

Ex: GetEasterDate(2001) will return 15 of April 2001

AddWorkingDays (StartDate, NDays, Holidays)

Adds Working Days to a Start Date, discounting Weekends and Holidays into consideration. Weekend tracking is automatic but Holidays have to be supplied as a comma separated list of dates.

Ex: AddWorkingDays(1/1/2006, 15, “'1/1/2006, 1/4/2006”) will return 1/20/2006

Click here for a FREE DEMO for the Sheet Functions

 

Download FREE Demo
EXCEL ONLY - No setup required. Just add to Excel AddIns and run!


Get FREE PC TIPS every month:

 
Contact Us for Support Main Page FREE DEMO

ExMiniTools - Utilities Toolbar for Microsoft® Excel® - Version 2.00

Tested on Microsoft Excel Versions: 2000 (9.0), XP (10.0), 2003 (11.0), 2007 (12.0), 2010 (14.0), 2013, 2016 - should work on any version from Office 97


Back
Other Contents  

00052- Design Chart Templates for Microsoft® Excel® Microsoft Excel Templates
The easiest way to create design charts. Check the samples, enter your own data and watch as your charts update. Multiple different charts from the same data, with many automatic calculations made for you. No code, macros, formulas or other complicated stuff. Just fill in YOUR data and watch the charts update automatically.
 
00054- Automatic Copy/Paste for Microsoft PowerPoint® Microsoft Excel Macro
Automatically update hundreds of Microsoft PowerPoint® slides, charts and tables from Excel in minutes. With the click on a button. Easily configure the updates you need. Copy from multiple Excel files. Resize and place pasted charts & tables – or just keep their original size & position.
 
00051- Organization Chart Maker for Microsoft® Excel® Microsoft Excel Macro
Quickly and easily generate your own organization charts from a plain list of data with Organization Chart Maker for Microsoft® Excel®. Apply colors and formats. Integrate external data from ERP or HR management systems like or Microsoft® Dynamics Navision, SAP®, PeopleSoft®, Oracle®, etc. Chart Stock & Portfolio listings, team members planning & company ownership charts.
 
00037- Traditional Calendars for Excel Microsoft Excel Macro
Easily generate annual and monthly Traditional & Planning calendars you can print and distribute, based only in Microsoft® Excel®. Five different formats available. Use special colors for special days or events, like holidays or meetings. Automatic event legend. Configurable background and font colors. Optional calendar background image. Traditional, Academic and Fiscal years. Imports data from Microsoft ® Outlook®.
 
00047- Calendar Templates 2014 with Holidays (USA / UK / Australia / Canada) for Excel Microsoft Excel Templates
Yearly, Monthly and Weekly Calendar templates (2 each) for Microsoft® Excel®. Includes versions with Holidays for the USA, UK, Australia and Canada.
 
00044- Custom GANTT Charts for Microsoft® Excel® Microsoft Excel Macro
Generate CUSTOM Project style calendar plans with the EXCLUSIVE option to customize our COPYRIGHT messages to create YOUR own REBRANDED Edition. Includes all ADVANCED Edition features plus the REBRANDING option. Distribute CORPORATE versions to your staff.
 
00027- Get all OfficeHelp.Biz CALENDARS at once! Microsoft Excel Bundles
Interested in more than one of our Microsoft® Excel® Calendars? Don't want the hassle of paying per purchase? Want to SAVE TIME AND MONEY?
 
00046- Calendar Templates 2007 with Holidays for Excel Microsoft Excel Templates
Yearly, Monthly and Weekly Calendar templates (2 each) for 2007 in Excel. Include USA Holidays.
 
00002- Calendar Plan Generator - ADVANCED Edition Microsoft Excel Macro
Generate ADVANCED Project style calendar plans, just by entering the tasks and associated data like its name and the starting and end date. Use different colors and select a deadline terminator for each task. Indent each task to its level or sub-level. Change, save and email the resulting calendar as any other regular Microsoft® Excel® file. Includes ADVANCED features like EXTRA plan types, detail filtering, optional fields, extra information of task bars, full customization and Microsoft® Outlook® integration.
 
00040- Get all OfficeHelp.Biz TEMPLATES at once! Microsoft Excel Bundles
Interested in more than one of our Templates, including all Excel Calendar Templates? Don't want the hassle of paying per purchase? Want to SAVE TIME AND MONEY?
 
00036- Calendar Plan Generator - Source Code Edition Microsoft Excel Macro
Generate Project style calendar plans (weekly only), just by entering the tasks and associated data like it's name and the starting and end date. Use different colors and select a deadline terminator for each task. Indent each task to its level or sub-level. Change, save and email the resulting calendar as any other regular Microsoft® Excel file. SOURCE CODE available for changes for VBA Macro developers.
 
00043- Calendar Plan Generator - BASIC Edition Microsoft Excel Macro
Easily generate Project style calendar plans, just by entering the tasks and associated data like it's name and the starting and end date. Use different colors and select a deadline terminator for each task. Indent each task to its level or sub-level. Change, save and email the resulting calendar as any other regular Microsoft® Excel file. The SIMPLEST edition, just ONE button to use!
 
00028- Calendar Plan - Year Planner Template Microsoft Excel Templates
Create pretty yearlong planning calendars in minutes. Save time and effort. Ready to use calendar template! Really useful to set yearlong event like general meetings, special groups, holidays and all other long-term schedules. Regular Excel file, sample included.
 
00001- Queue Servicing Calculator Microsoft Excel Macro
Calculates the average waiting times and people (or item) processing times based on some input estimates. Can be used to make service capacity simulations or to measure the service throughput on any service or process demanding waiting queues.
 

Privacy Policy How to Buy How safe is it to Buy? RSS Feed Contact Us

(c) 2004 - 2024 OfficeHelp.Biz