Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.

Dermot's Blog

Sort by: Most Recent | Most Viewed | Most Commented

  • Safer business spreadsheets - a practical approach that works

    If, like me, you've worked in organisations where the quality of many spreadsheets is terrible, you'll want to make them safer to reduce business risk caused by errors or misuse. I believe there is a practical and simple approach which can greatly reduce risk - an approach that is much more human than technical - which came out of research carried out within my previous consulting firm a few years ago, to improve its own spreadsheets. I'd like to stress that this approach is aimed at...

  • More advanced Excel automation & simplification

    In a previous post , I showed a way of automating and/or simplifying Excel by processing many data records through a single sheet using data tables, rather like a merge. If you liked that, you should love this more advanced tool, written in VBA. It is generic enough to be used on a wide range of projects without modification, so I'll call it an automation "engine" below. If you find it useful, it is free to use and modify, providing you don't claim undue credit or sell it. It was...

  • An awesome powertool buried in Excel

    I build complex spreadsheets, and I often have trouble fitting everything into Excel tables - I'd really like a way of simplifying things. At other times, I want to run a set of different scenarios through a spreadsheet, and I'd like to automate them to be self-updating. There is a code-free solution to both these problems which has been there all along, but I didn't see it until a few years ago. It exploits data tables, an almost forgotten feature of Excel. If you have the same problems...

  • Dynamic user interfaces

    When I present spreadsheet results to clients, there are always several variables that I know the clients will want to play with ("What if this was higher, or if that was lower, or if I could see just those 3 items..."). This means I need to give the client choices and options, but I need to make them simple to use and have the model respond immediately. I've put together a collection of some techniques which you may find useful for this. Specifically, how to make charts or lists dynamically...

  • A little library of gems for VBA

    Did you know that VBA can get the user's login name, tell you where My Documents lives (and other folders), talk to the Windows registry, handle regular expressions (eg search for words of 5-8 characters starting with G), create file shortcuts, and more? Nor did I, until I read a forum post showing how to use regular expressions in Excel, by setting a reference to the Microsoft Scripting Runtime in VBA (Tools, References from the menu). I was intrigued, and I had a look at what else this Scripting...

  • How to build an awesome user interface for results

    The best work in the world is undervalued if presented badly. Suppose you've built a big model with lots of results to show. Imagine having a single Results sheet, with a list of charts and tables. Each time you select one, it appears like magic on the screen, replacing the one that was there before. Best of all, it needs no VBA code whatsoever! There are two ways I know of to do it. Both are demonstrated in this file ( Dynamic UI.xls ). 1. Hyperlinks A simple method is simply to create a hyperlink...

  • Creating Word reports from Excel

    If you create regular Word reports from Excel, how would you like to be able to produce each report with just one click, instead of manually copying and pasting all the tables and charts from Excel to Word? I've written an Excel utility to do this automatically. The principle is quite simple - the program looks at all the bookmarks in the Word document. If any of their names start with "tag_", the program looks for the range or chart in Excel with a similar name, and copies it across...

  • Using hyperlinks for navigation in Excel

    So your new workbook needs some navigation links. You may want to use hyperlinks, because most people are familiar with them, thanks to the internet, but they create a couple of little problems we need to solve. The example workbook here ( Hyperlinks.xls ) illustrates these problems and their solutions. First - creating a hyperlink First, to create a hyperlink, select the cell containing the link text, select Insert, Hyperlink, choose "Place in this document" on the leftof the dialog, and...
    Filed under: ,

  • A blog for Excel users rather than developers

    I'm interested in practical techniques and tools which can be used every day to make business users more effective, and that's what this blog will be about. The topics will not just be technical (eg the best use of worksheet functions), but will also cover things like creating a professional looking chart or user interface. Wherever possible, there will be actual examples (in Excel 2003 - business hasn't got to Excel 2007 yet) so you can get started right away. My background is that I've...
    Filed under: ,
Copyright Excel User Group and the relevant contributors, 2010. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.