DMW logo
contact@consultdmw.com

Microsoft® Excel Tips and Techniques

Last updated on 2022-01-27 by David Wallis.

Thanks to everyone who in 2023 has made a donation to this website. Much appreciated.

Here are links to this website’s topics relating to Microsoft Excel and Excel Visual Basic for Applications (VBA) programming, listing the entries in the Excel Tips drop-down menu to the left of this page and providing a summary of what’s covered in the pages to which those links lead.

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

Pidgeon on wall

BODMAS/BIDMASA revision of the arithmetic rules applying to calculations, in particular to when you’re creating formulas in Excel.

Comparison operators How to create formulas using comparison operators “˃”, “˂”; and “=”.

Conditional formatting How to create a conditional format that’s easy and quick to adapt, without the need to edit the format.

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

COUNT functions Use of Excel’s COUNT, COUNTBLANK and COUNTA functions in formulas and how to avoid certain pitfalls in their application.

COUNTIF and COUNTIFS functions How to use the COUNTIF and COUNTIFS functions in formulas, with particular reference to the application of COUNTIFS multiple criteria.

Create data set How to use a variety of Excel’s functions to produce sets of data possibly for testing spreadsheets or use in databases.

Dropdown lists How to create and maintain dropdown lists in Excel cells.

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.

Crow on roof

Formula warnings Aims at understanding of the errors #VALUE!, #REF!, #DIV/0!, #NAME?, #NULL! and #N/A that formulas produce under certain circumstances.

Dates in Excel Understanding how Excel perceives dates and hence how to apply formulas to work on dates.

Extract date from text Presents formulas and a VBA function for isolating a date from a text string that mixes the date with other text in no particular order.

Extract day from date Formulas for extracting, for example, “Saturday” from “20/02/2021”.

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 calculating the end of any month in the future without resort to VBA.

Thanks to KH for comment

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. Putting control of where line breaks occur under your control instead of leaving this to Excel’s Wrap Text.

Owl staring

OFFSET function Understanding the OFFSET function and how to use it in formulas.

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 random 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.

Separating names Develops formulas like these to separate first and last names from full names:

Excel formula for separating names

SUMPRODUCT Illustrates 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.

Tile worksheet windows Helps you create a useful macro to use with any workbook in which you want certain worksheets tiled together on the screen automatically.

Time conversion Demonstration of a way to convert hh:mm times to decimalised hours.

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

VBA introduction An overview of VBA — the language behind macros — and how to approach VBA to extend your recorded macros and how to programme macros from the ground up.

Macro function and sub procedures An introduction to a structured approach to creating and extending VBA macros. This piece expands on the topics covered in the VBA introduction page, listed above, and addresses important issues like error handling.

VBA error trapping Expands on the topic of error management that’s alluded to in other pages and suggests some VBA code for processing errors tripped by VBA procedures during run time. I believe that every macro should contain error trapping.

#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.

VLOOKUP function Understanding the VLOOKUP function and how to use it in formulas.

∑ percentages Looks at columns of percentages for which the totals do not add up.


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.