DMW logo
tel 01732 833085
tonbridge · kent · UK

Microsoft Excel Tips and Techniques

Last updated on 2019-03-05 by David Wallis.

Here are links to this website’s topics relating to Microsoft Excel and Excel Visual Basic for Applications (VBA) programming, expanding on the entries in the Excel Tips drop-down menu to the left of this page.

These topics have not been vetted by third parties, so please treat them critically. If you have any views or comments, please contact me.

BODMAS/BIDMAS Some revision on the rules applying to applying arithmetic and calculations in using Excel formulas.

Chart legend puts forward one way of making a chart legend respond to a formula.

DSUM criteria explores one way of furnishing DSUM formulas with criteria determined by calculated date ranges.

Error 1004 identifies one cause of the 1004 macro error message.

Formula warnings Understanding the errors #VALUE!, #REF!, #DIV/0!, #NAME?, #NULL! and #N/A that your formulas produce under some circumstances.

Military date/time presents a formula for converting a 'dd/mm/yyyy HH:MM' date and time value to the military equivalent 'ddHHMMZmmmmyy' one.

Month end presents a formula for calulating the end of any month in the future without resort to VBA.

My thanks to one supporter for this, his comment:

“Thanks for publishing these pieces of information that save us fellow IT people a LOT of time.”

New line in cell explores a number of ways of forcing text into a new line in a single cell at the point at which you want the line break.

Random colour fill presents a VBA macro for filling a block of cells with a random range of colours.

Random number fill presents VBA code for filling a block of cells with a random numbers for those occasions on which you need numbers for testing a worksheet.

Random dates fill presents formulas for filling blocks of cells with random dates and sequences of rnadom dates for those occasions on which you need dates for testing your worksheet.

Range naming presents VBA macros for assigning, re-assigning and deleting range names.

Rounding to 5 explains the use of ROUND and MROUND functions to round any number to the nearest five.

SUMPRODUCT seeks to illustrate the versatility of Excel’s SUMPRODUCT function.

Summing time demonstrates a formatting technique that displays the result of adding, or summing, a column of time values.

Time conversion demonstrates a way of converting hh:mm times in decimalised hours.

Time intervals demonstrates ways of calculating and displaying time intervals, including those that extend beyond 24 hours.

VBA introduction is an introduction to a structured approach to creating and extending VBA macros.

VBA function and sub prodedures is an introduction to a structured approach to creating and extending VBA macros.

VBA error trapping suggests some VBA code for processing errors tripped by VBA procedures during run time.

#DIV/0! error presents a simple formula for hiding the #DIV/0! message Excel displays when your formula attempts to divide a number by zero.

∑ percentages looks at colums of percentages for which the totals do not add up.


Your Support for dmw TIPS

If you’re going to make use of Tips in any of your professional or commercial work, then please consider making a payment in recognition of the time spent publishing this website.

To make a contibution by PayPal in GBP (£ sterling) —

To make a contibution by PayPal in USD ($ US) —

To say how much you want to pay and to receive
a tax invoice for a GBP (£ sterling) contribution —

Invoice

Thanks, in anticipation.


Disclaimer

DMW Consultancy Ltd does not accept any liability for loss or damage to data to which any techniques, methods or code included in this website are applied. Back up your data; test thoroughly before using on live data.

“You might be an Excel nerd if: you assess the romantic potential in a person based on their ability to create a pivot table in under 60 seconds.”

Author unknown.