EXCELlent Ones Consulting Skip to Content Skip to Search

Print  |   | Download PDF

The Hidden Gems in Microsoft Excel

There is a treasure trove sitting on your desktop. While this statement may invoke images from a bygone era of maritime plundering, it alludes to the vast amount of untouched value housed within the typical users Microsoft Excel. Excel contains over 300 functions and countless tools. A recent poll revealed that the average person is aware of less than 1% of these offerings. In addition Microsoft Office is the software of choice of many institutions. At this point we can surmise that a huge opportunity exists to leverage this program to produce efficiencies previously unknown. One of the most important functions is the ability for one Microsoft Office Suite member to communicate with another through the Visual Basic Application (VBA) programming language. This programming language can be used to carry out redundant manual steps (data entry, repetitive emails etc.) across the different platforms (Excel, Word, Outlook, Access etc.). Using this programming language can amplify the strengths of each application to produce labor savings.

Microsoft Excel gives the user the ability to perform computations, analyze and visualize data. Aside from the common functions of linking cells and basic summary formulas there are a few overlooked capabilities which are capable of creating an immediate impact:

1. Pivot Tables – These are great! This tool simplifies the summarization of complex data quickly and seamlessly. Users simply select the range of data to be analyzed then “drag” and “drop” criteria in various places. Totals, averages and counts can be attained based on the criteria selected.

2. Dynamic Formulas – Using two coupled formulas “Offset” and “CountA” users can create malleable expressions which automatically adjust depending on data size. These statements are flexible and adjust based on needs. They eliminate the need to constantly edit formulas to capture a desired data range.

3. Named Ranges – Specific ranges or cells can be labeled with a name in place of a cell address. For example range B6:D8 can be given the name “Total”. This named range can then be included in all subsequent formulas instead of the cell address. This reduces the time needed to create complex formulas and audit spreadsheets.

4. Solver – This is an optimization tool which gives the user the ability to determine the proper mix of variables to maximize an outcome. For example a company that makes three distinct products with varying contribution margins can determine the ideal mix of sales to produce maximum profit, given various constraints.


Microsoft Excel is a robust tool that can provide innovative solutions to challenges. Employing these tools and countless other capabilities will lead to productivity savings.

Footer

Email: info@excellentones.com | P.O. Box 160493 | Brooklyn, NY 11216 | Phone: (347) 763-1035 | Fax: (718) 228-7859

All rights reserved. Copyright 2014 Excellent Ones Consulting LLC.